mysql效能監控工具

標籤：

參考文檔：

http://www.linuxidc.com/Linux/2012-09/70459.htm

 

1.記錄慢查詢SQL

#配置開啟(linux)修改my.cnf:log-slow-queries=/var/log/mysql/slowquery.log (指定記錄檔存放位置，可以為空白，系統會給一個預設的檔案host_name-slow.log)long_query_time=2 (記錄超過的時間，預設為10s)log-queries-not-using-indexes (log下來沒有使用索引的query,可以根據情況決定是否開啟)log-long-format (如果設定了，所有沒有使用索引的查詢也將被記錄)(windows)修改my.ini:log-slow-queries=D:\mysql\log\mysqlslowquery.loglong_query_time=2... (其他參數如上)#查看方式使用mysql內建命令mysqldumpslow查看，常用命令：    -s ORDER what to sort by (t, at, l, al, r, aretc), ‘at’ is default    -t NUM just show the top n queries    -g PATTERN grep: only consider stmts that includethis stringeg:    s，是order的順序，說明寫的不夠詳細，俺用下來，包括看了代碼，主要有 c,t,l,r和ac,at,al,ar，分別是按照query次數，時間，lock的時間和返回的記錄數來排序，前面加了a的時倒序 -t，是top n的意思，即為返回前面多少條的資料 -g，後邊可以寫一個正則匹配模式，大小寫不敏感的mysqldumpslow -s c -t 20 host-slow.logmysqldumpslow -s r -t 20 host-slow.log上述命令可以看出訪問次數最多的20個sql語句和返回記錄集最多的20個sql。mysqldumpslow -t 10 -s t -g “left join” host-slow.log這個是按照時間返回前10條裡面含有左串連的sql語句。

 

2.explain (sql執行計畫解釋命令)

EXPAIN [SQL]

 

3.profile

mysql> select @@profiling;+-------------+| @@profiling |+-------------+|           0 |+-------------+1 row in setmysql> SET profiling = 1;Query OK, 0 rows affectedmysql> select @@profiling;+-------------+| @@profiling |+-------------+|           1 |+-------------+1 row in setmysql> SELECT * FROM t_im_tokens WHERE profileid IN (SELECT profileid FROM t_enduser_customer_service);+-----------+----------------------------------+| profileid | token                            |+-----------+----------------------------------+|  10000152 | a2898efe07380a813efa53e0fd4b3697 ||  10000153 | b37122b662a6a6f7ad0a16e1a03f7f01 ||  10000190 | 7235940f4438dbceeaf288540ba68f19 ||  10000302 | 7ccb852b19562fc296f82f95f8e0d9ef ||  10000363 | a672510b73a34a3d1d6227a53086b248 ||  10000387 | 209a5085acbcd3969a98668d62fbd1a3 ||  10000550 | 52324116187ce5b558b8a082762b657b ||  10000556 | d6b61c83675b373f5b7887c088e6806e |+-----------+----------------------------------+8 rows in setmysql> show profiles;+----------+------------+-------------------------------------------------------------------------------------------------+| Query_ID | Duration   | Query                                                                                           |+----------+------------+-------------------------------------------------------------------------------------------------+|        1 | 0.00018075 | select @@profiling                                                                              ||        2 |   0.000613 | SELECT * FROM t_im_tokens WHERE profileid IN (SELECT profileid FROM t_enduser_customer_service) |+----------+------------+-------------------------------------------------------------------------------------------------+2 rows in setmysql> show profile for query 2;+----------------------+----------+| Status               | Duration |+----------------------+----------+| starting             | 6.3E-5   || checking permissions | 5E-6     || checking permissions | 4E-6     || Opening tables       | 2E-5     || init                 | 2.1E-5   || System lock          | 7E-6     || optimizing           | 2E-5     || statistics           | 0.000236 || preparing            | 1.7E-5   || executing            | 3E-6     || Sending data         | 0.000129 || end                  | 5E-6     || removing tmp table   | 8E-6     || end                  | 3E-6     || query end            | 5E-6     || closing tables       | 8E-6     || freeing items        | 3.7E-5   || cleaning up          | 2.5E-5   |+----------------------+----------+18 rows in setmysql> 

 

mysql效能監控工具