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.log
long_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 string
eg:
    s，是order的顺序，说明写的不够详细，俺用下来，包括看了代码，主要有 c,t,l,r和ac,at,al,ar，分别是按照query次数，时间，lock的时间和返回的记录数来排序，前面加了a的时倒序 -t，是top n的意思，即为返回前面多少条的数据 -g，后边可以写一个正则匹配模式，大小写不敏感的

mysqldumpslow -s c -t 20 host-slow.log
mysqldumpslow -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 set

mysql> SET profiling = 1;
Query OK, 0 rows affected

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set

mysql> 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 set

mysql> 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 set

mysql> 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 set

mysql>