anemometer mysql_MySQL慢日志简介及Anemometer工具介绍 | | For DBA
原標題:MySQL慢日志簡介及Anemometer工具介紹 | | For DBA
作者:王航威 - fordba.com
來源:http://fordba.com/box-anemometer-visual-mysql-slow.html,葉師傅對原文內(nèi)容略有調(diào)整
備注:王航威是知數(shù)堂第8期同學,現(xiàn)任職有贊DBA
MySQL 慢日志簡介
MySQL慢日志(slow query log)想必大家都有聽說,它是用來記錄MySQL中的慢SQL(執(zhí)行耗時超過 long_query_time 預設(shè)時間的SQL),并且掃描的行數(shù)超過min_examined_row_limit(如果也設(shè)置了這個選項的話),或者需要全表/全索引掃描的SQL(如果設(shè)置了 log_queries_not_using_indexes 選項的話,并不是指所有沒使用索引的SQL),這些SQL效率通常較低,是可能影響MySQL的性能,DBA需要定期優(yōu)化這些SQL。
在MySQL中,如果一個SQL需要長時間等待獲取鎖資源,那么這段獲取鎖的等待時間并不算執(zhí)行時間,當SQL執(zhí)行完成,釋放相應的鎖,才會記錄到慢日志中,所以MySQL的慢日志中記錄的順序和實際的執(zhí)行順序可能不一樣。
在默認情況下,MySQL的慢日志記錄是關(guān)閉的,我們可以通過將設(shè)置slow_query_log=1來打開MySQL的慢查詢?nèi)罩?#xff0c;通過slow_query_log_file=file_name來設(shè)置慢查詢的文件名,如果文件名沒有設(shè)置,他的默認名字為 host_name-slow.log。同時,我們也可以設(shè)置 log-output={FILE|TABLE}來指定慢日志是寫到文件還是數(shù)據(jù)庫里面(如果設(shè)置log-output=NONE,將不進行慢日志記錄,即使slow_query_log=1)。
默認地,MySQL的管理維護命令的慢SQL并不會被記錄到MySQL慢日志中。常見的管理維護命令包括ALTER TABLE,ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, 和REPAIR TABLE。如果希望MySQL的慢日志記錄這類長時間執(zhí)行的命令,可以設(shè)置log_slow_admin_statements = 1。
通過設(shè)置log_queries_not_using_indexes=1,MySQL的慢日志也能記錄那些沒使用索引的SQL(并不需要超過long_query_time,兩者條件滿足一個即可)。但打開該選項的時候,如果你的數(shù)據(jù)庫中存在大量沒有使用索引的SQL,那么MySQL慢日志的記錄量將非常大,所以通常還需要設(shè)置參數(shù)log_throttle_queries_not_using_indexes 。默認情況下,該參數(shù)為0,表示不限制,當設(shè)置改參數(shù)為大于0的值的時候,表示MySQL在一分鐘內(nèi)記錄的沒使用索引的SQL的數(shù)量,來避免慢日志記錄過多的該類SQL。
在MySQL 5.7.2 之后,如果設(shè)置了慢日志是寫到文件里,需要設(shè)置log_timestamps 來控制寫入到慢日志文件里面的時區(qū)(該參數(shù)同時影響general日志和err日志),不設(shè)置的話,log文件中的記錄采用UTC時間,而非本地時間。如果設(shè)置慢日志是寫入到數(shù)據(jù)庫中,該參數(shù)將不產(chǎn)生作用。
所以,總結(jié)下哪些SQL能被MySQL慢日志記錄:
不會記錄MySQL中的管理維護命令,除非明確設(shè)置log_slow_admin_statements=1;
SQL執(zhí)行時間必須超過long_query_time,(不包括鎖等待時間)
參數(shù)log_queries_not_using_indexes設(shè)置為1,且SQL沒有用到索引,同時沒有超過log_throttle_queries_not_using_indexes 參數(shù)的設(shè)定。
查詢examine的行數(shù)必須超過min_examined_row_limit
注1:如果表沒有數(shù)據(jù)或者只有1條數(shù)據(jù),優(yōu)化器覺得即便走索引對效率并沒幫助,哪怕 log_queries_not_using_indexes=1 也不會記錄到慢日志中。
注2:如果SQL在QC命中了,也不會記錄到慢日志中。
注3:修改密碼之類的維護操作,密碼部分將會被星號代替,避免明文顯示。
Anemometer 簡介
項目地址:https://github.com/box/Anemometer
演示地址:http://lab.fordba.com/anemometer/
Anemometer 是一個圖形化顯示從MySQL慢日志的工具。結(jié)合pt-query-digest,Anemometer可以很輕松的幫你去分析慢查詢?nèi)罩?#xff0c;讓你很容易就能找到哪些SQL需要優(yōu)化。
如果你想要使用Anemometer這個工具,那么你需要準備以下環(huán)境:
一個用來存儲分析數(shù)據(jù)的MySQL數(shù)據(jù)庫
pt-query-digest. (doc: Percona Toolkit )
MySQL數(shù)據(jù)庫的慢查詢?nèi)罩?(doc: The Slow Query Log )
PHP版本為 5.5+,apache或者nginx等web服務(wù)器均可。
安裝
下載Anemometer
git clone git://github.com/box/Anemometer.git anemometer
載入數(shù)據(jù)
首先創(chuàng)建表結(jié)構(gòu),將global_query_review 以及global_query_review_history 創(chuàng)建出來。由于表定義中存在0000-00-00 00:00:00 的日期默認值,需要修改sql_mode,將其zero_date的sql_mode 關(guān)閉,同時關(guān)閉only_full_group_by
cd /www/lab/anemometermysql -f < ./install.sql
現(xiàn)在需要使用pt-query-digest 抓取MySQL的慢查詢?nèi)罩?#xff0c;然后將數(shù)據(jù)插入到slow_query_log 數(shù)據(jù)庫的相應表中。
使用如下方式載入數(shù)據(jù),h表示主機名或者ip地址,D表示database,t表示表名,再最后面跟上慢日志路徑。
如果 pt-query-digest version > 2.2:
$ pt-query-digest --user=anemometer --password=superSecurePass --review h=127.0.0.1,D=slow_query_log,t=global_query_review --review-history h=127.0.0.1,D=slow_query_log,t=global_query_review_history --no-report --limit=0% --filter=" $event->{Bytes} = length($event->{arg}) and $event->{hostname}="$HOSTNAME"" /data/mysql/slow-query.log
如果 pt-query-digest version <= 2.2
$ pt-query-digest --user=root --password=root --review h=127.0.0.1,D=slow_query_log,t=global_query_review --history h=127.0.0.1,D=slow_query_log,t=global_query_review_history --no-report --limit=0% --filter=" $event->{Bytes} = length($event->{arg}) and $event->{hostname}="$HOSTNAME"" /data/mysql/slow-query.logPipeline process 11 (aggregate fingerprint) caused an error: Argument "57A" isn't numeric in numeric gt (>) at (eval 40) line 6, <>; line 27.Pipeline process 11 (aggregate fingerprint) caused an error: Argument "57B" isn't numeric in numeric gt (>) at (eval 40) line 6, <> line 28.Pipeline process 11 (aggregate fingerprint) caused an error: Argument "57C" isn't numeric in numeric gt (>) at (eval 40) line 6, <> line 29.
如果你看到一些報錯如上面例子所示,腳本并沒有出現(xiàn)問題,他只是輸出當前的操作。
配置Anemometer
修改Anemometer配置文件
$ cd anemometer/conf$ cp sample.config.inc.php config.inc.php
示例的配置文件中,你需要進行部分修改,用來連接數(shù)據(jù)庫獲取慢查詢的分析數(shù)據(jù)。
修改 datasource_localhost.inc.php 文件中的配置,主要為主機
$conf['datasources']['localhost'] = array( 'host' => '127.0.0.1', 'port' => 3306, 'db' => 'slow_query_log', 'user' => 'root', 'password' => 'root', 'tables' => array( 'global_query_review' => 'fact', 'global_query_review_history' => 'dimension' ), 'source_type' => 'slow_query_log');
然后訪問127.0.0.1/anemometer 的時候出現(xiàn)
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'slow_query_log.dimension.sample' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (1055)
需要將sql_mode 中only_full_group_by 關(guān)閉。
如果你想利用Anemometer 的explain功能來獲取執(zhí)行計劃,修改配置文件的以下部分。
$conf['plugins'] = array( 'visual_explain' => '/usr/local/bin/pt-visual-explain', --這里需要修改為正確的路徑 ... 'explain' => function ($sample) { $conn['user'] = 'anemometer'; $conn['password'] = 'superSecurePass'; return $conn; },);
結(jié)果展示
在5.7中,默認SQL_MODE是啟用ONLY_FULL_GROUP_BY的,需要將其關(guān)閉,否則Anemometer將報錯。
選擇相應的列,然后點擊search,就可以顯示結(jié)果
sql執(zhí)行計劃查看以及歷史
當我們選擇一個sql的hash值的時候,能看到他的一個具體的執(zhí)行計劃,同時也能看到匹配該sql的歷史sql,消耗,表的統(tǒng)計信息,建表語句等。
同時也能針對sql進行評論,為sql優(yōu)化提交建議等。
創(chuàng)建自動收集慢日志腳本
在anemometer下面的文件中有個收集腳本,可以通過crontab進行定時收集慢日志,語法如下:
Usage: ./s/anemometer_collect.sh --interval Options: --socket -S The mysql socket to use --defaults-file The defaults file to use for the client --interval -i The collection duration --rate Set log_slow_rate_limit (For Percona MySQL Only) --history-db-host Hostname of anemometer database server --history-db-port Port of anemometer database server --history-db-name Database name of anemometer database server (Default slow_query_log) --history-defaults-file Defaults file to pass to pt-query-digest for connecting to the remote anemometer database
示例腳本:
cd anemometer mkdir etccd etcvi anemometer.local.cnf --這里創(chuàng)建配置文件,添加用戶名密碼[client]user=anemometer_localpassword=superSecurePass./s/anemometer_collect.sh --interval 30 --history-db-host=127.0.0.1
葉師傅補充
我以前的Anemometer玩法是這樣的。
把多個主機的slow log匯聚到一起,調(diào)用pt-query-digest解析并寫入db,方便slow log的統(tǒng)一管理、解析、展示,而不是每個實例都單獨部署一套;
每個主機上可能會跑多實例,其slow log的文件命名,會至少體現(xiàn)IP、端口、業(yè)務(wù)名,比如:slowquery-192.168.0.1:3306-yejr.log;
調(diào)用pt-query-digest解析時,傳遞給 hostname 參數(shù)的值為 "192.168.0.1:3306",這樣一來,每個實例都可以被單獨處理列出,更加直觀;
通常,每個實例都有統(tǒng)一的監(jiān)控賬號,Anemometer可以利用這個賬號來連接遠程實例,查看解析SQL執(zhí)行計劃。
您可能感興趣的文章:返回搜狐,查看更多
責任編輯:
總結(jié)
以上是生活随笔為你收集整理的anemometer mysql_MySQL慢日志简介及Anemometer工具介绍 | | For DBA的全部內(nèi)容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: access无法 dolby_如何解决w
- 下一篇: raft算法mysql主从复制_Raft