监测mysqld中SQL的性能状况

mysqld可以对执行的每一条SQL查询语句的性能进行跟踪。不过默认情况下，这个开关没有打开。

修改mysqld配置文件：/etc/mysql/my.cnf，添加以下几项：

 # 是否记录每一条执行效率差的SQL查询语句，以及记录到哪个日志文件中
2 log-slow-queries            = /var/log/mysql/slow.log
3 # 执行时间超过若干秒的SQL，则被认为是性能差的SQL。下例设为1秒。
4 long-query-time             = 1
5 # 对没有利用索引进行检索的SQL也将被记录进日志
6 log-queries-not-using-indexes

配置好上述参数后，重启mysqld服务，开始对性能差的查询SQL进行监测。执行若干时间之后（一个中等负载的系统，个把小时，产生几十万条记录，很正常），就可以对日志文件进行分析了。这段时间内执行过的查询SQL，只要被认定为性能差，就会被原封不动地记录进日志文件。日志有类似如下的格式：

 1 /usr/sbin/mysqld, Version: 5.0.70-log (Gentoo Linux mysql-5.0.70-r1). started with:
 2 Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
 3 Time                 Id Command    Argument
 4 # Time: 091116 23:48:53
 5 # User@Host: usr1[usr1] @ dbserver [192.168.1.201]
 6 # Query_time: 0  Lock_time: 0  Rows_sent: 16  Rows_examined: 32
 7 select f1, f2, f3 from t1;
 8 # User@Host: usr1[usr1] @ dbserver [192.168.1.201]
 9 # Query_time: 0  Lock_time: 0  Rows_sent: 16  Rows_examined: 32
10 select f4, f5 from t2;

因为日志记录很多，手工去一条一条地分析太不现实。通常是利用辅助工具对日志进行统计分析，找出其中问题最严重的SQL。这样的工具有很多中，个人认为mysql自带的msyqldumpslow工具就已经好用了。该命令有很多选项，以下是常用的：

 # 找出平均执行时间最长的10条SQL
2 mysqldumpslow -t 10 -s at /var/log/mysql/slow.log > /var/log/mysql/slow_at.log
3 # 找出总执行时间最长的10条SQL
4 mysqldumpslow -t 10 -s t /var/log/mysql/slow.log > /var/log/mysql/slow_t.log
5 # 找出执行次数最多的10条SQL
6 mysqldumpslow -t 10 -s c /var/log/mysql/slow.log > /var/log/mysql/slow_c.log
7 # 找出返回记录数最多的10条SQL
8 mysqldumpslow -t 10 -s r /var/log/mysql/slow.log > /var/log/mysql/slow_r.log

分析的结果如下：

 Reading mysql slow query log from /var/log/mysql/slow.log
2 Count: 323  Time=1.00s (1s)  Lock=0.00s (0s)  Rows=1.0 (1), usr1[usr1]@dbserver
3 select 
4 Count: 23  Time=1.00s (1s)  Lock=0.00s (0s)  Rows=0.0 (0), usr1[usr1]@dbserver
5 select 
6 Count: 执行次数  Time=平均执行时间 (总执行时间)  Lock=平均锁时间 (总锁时间)  Rows=平均返回记录数 (总返回记录数), usr1[usr1]@dbserver
7 SQL查询语句

有了这些数据，性能调优的工作就变得很简单了吧！