mysql的SQL性能監控

1、慢查詢

1.1、慢查詢的作用

慢查詢的主要作用是将執行超過一定時間的sql語句記錄到慢查詢日志檔案，友善找出有性能問題的sql，然後我們可以針對這些sql進行分析調優。

和慢查詢相關的參數有三個：

long_query_time參數設定時間，執行時間大于該參數設定時間的sql都會被記錄下來，支援小于1秒的設定，不過一般設定為1秒，主要原因時小于1秒的sql太多了，而且執行計劃在大資料量情況下小于1秒的一般是沒全表掃描的，而小資料量小于1秒的，即便全表掃描也問題不大，除非是執行頻率非常高。

slow_query_log參數設定是否打開慢查詢日志的開關。

slow_query_log_file參數設定慢查詢日志檔案。

1.2、慢查詢的設定

兩種配置方式，一種是通過指令配置，可以即時生效，但重新開機服務後失效。示例如下：

root@laojiang:~> mysql -uroot -proot

set global long_query_time=1;

set global slow_query_log=on;

set global slow_query_log_file='/home/root/data/data/mysql-slow_1.log';

Exit

show variables like '%_query_%';

第二種方式是修改配置檔案，然後重新開機服務，如下：

root@laojiang:~/etc> vi mysql.ini

[mysqld]

long_query_time=1

slow_query_log=ON

slow_query_log_file=/home/root/data/data/mysql-slow_2.log

root@laojiang:~/etc> service mysql stop

root@laojiang:~/etc> service mysql start

1.3、慢查詢示例

進入指令視窗執行查詢語句：

mysql> select count(*) from performance.order_line where ol_dist_info like '%abcdefg%' or ol_dist_info like '%hijk%';

檢視慢查詢日志檔案：

root@laojiang:~/data/data> tail -f mysql-slow_2.log

1.4、系統慢查詢狀态檢查

mysql> show global status like '%slow%';

+---------------------+-------+

| Variable_name    | Value |

| Slow_launch_threads | 0     |

| Slow_queries        | 4148 |

打開慢查詢日志可能會對系統性能有一點點影響，如果MySQL是主－從結構，可以考慮打開其中一台從伺服器的慢查詢日志，這樣既可以監控慢查詢，對系統性能影響又小。

1.5、mysqldumpslow指令

慢查詢日志有可能會有很多重複的sql語句，我們如何過濾呢？Mysql有自帶的指令mysqldumpslow可進行查詢，例下列指令可以查出通路次數最多的20個sql語句

mysqldumpslow -s c -t 20 host-slow.log

2、目前系統正在執行的sql

show processlist以及show full processlist可以檢視mysql目前正在執行的sql語句，以便找到目前消耗資源的sql。第2個指令可以檢視完整的正在執行的sql語句。

3、sql執行各階段所花費的時間

通過mysql自帶profiling(性能分析)工具可以診斷某個sql各個執行階段消耗的時間，每個執行階段在cpu  disk io等方面的消耗情況。

mysql> show variables like '%profiling%';

+------------------------+-------+

| Variable_name          | Value |

| have_profiling         | YES   |

| profiling              | OFF   |

| profiling_history_size | 15    |

mysql> set global profiling=1;

| profiling              | ON    |

mysql> select count(*) from test_info;

+----------+

| count(*) |

| 10000000 |

+----------+ 

mysql> show  profiles;

+----------+------------+--------------------------------------+

| Query_ID | Duration   | Query                                |

|        1 | 0.00014900 | 1                                    |

|        2 | 0.00007725 | set global profiling=1               |

|        3 | 0.00059175 | show variables like '%profiling%'    |

|        4 | 2.83227700 | select count(*) from test_info |

+----------+------------+--------------------------------------+ 

mysql> show profile for query 4;

+----------------------+----------+

| Status               | Duration |

| starting             | 0.000067 |

| checking permissions | 0.000007 |

| Opening tables       | 0.000021 |

| init                 | 0.000017 |

| System lock          | 0.000008 |

| optimizing           | 0.000007 |

| statistics           | 0.000016 |

| preparing            | 0.000015 |

| executing            | 0.000003 |

| Sending data         | 2.831913 |

| end                  | 0.000015 |

| query end            | 0.000009 |

| closing tables       | 0.000015 |

| freeing items        | 0.000141 |

| cleaning up          | 0.000024 |

15 rows in set, 1 warning (0.00 sec)

4、sql執行狀态

這些狀态在show processlist和show profile for query n中會出現，所謂sql執行狀态，就是隻sql執行的步驟，可以查到目前sql正在做什麼，這個在oracle中對應就是sql的等待事件。

     本文轉自aaron428 51CTO部落格，原文連結http://blog.51cto.com/aaronsa/1728263：，如需轉載請自行聯系原作者