浅谈MySQL慢查询日志及microslow patch(msl patch)

mysql(包括所有5.*系列,5.1.21之前)的慢查询日志默认是以秒为单位的,可以通过set [session|global] long_query_time=1这个最低也就是可以设置到1秒,对于0.5,0.005之类就无能为力了,这时候可以有microslow patch(msl patch)补丁来完成这一工作,这个功能的好处自然不言而喻.

在mysql5.1.21及以后版本可以通过set [session|global] long_query_time=0.01等来设置记录0.01秒以上的sql语句.

在5.1.6之后系列产品中还有--log-output=[FILE|TABLE|NONE]特性,这个是选择general_log和slow_log的存储方式,文件,数据表,还是不记录,5.1.6-5.1.20默认是数据表(mysql.slow_log表),其余默认是文件.

--log-queries-not-using-indexes也是5.1的新选项,可以没有使用index的sql语句记录到慢查询日志中

--log-slow-admin-statements该命令选项可以将OPTIMIZE TABLE, ANALYZE TABLE, 以及ALTER TABLE 等管理操作写道慢查询日志中.

在5.0系列产品可以通过安装microslow patch(msl patch)来改进慢查询日志.

这些选项可以加在启动命令上也可以记录在my.cnf里面

至于查看日志,可以直接查看也可以使用工具,对于比较多的日志还是日志方便些.常见的工具有mysql自带的mysqldumpslow以及第三方工具mysqlsla等.

msl patch是通过修改mysql源代码的方式来增强mysql的功能,所以只能是源码安装才可以扩展这个功能,其在www.mysqlperformanceblog.com上有详细介绍(其实英语很简单,嘿嘿)

有篇文章详细说明了microslow patch的安装和使用摘几个选项放在这方便以后查看

Configuration

There are several parameters related to slow log you can set with patch applied. All filter-type options work in conjunction meaning that in order for query to be logged it must match long_query_time AND min_examined_row_limit AND log_slow_filter.


log-slow-queries[=name]


Log slow queries to this log file. Defaults logging to hostname-slow.log file. Must be enabled to activate other slow log options.

This is the most important one as it enables the logging. If you don’t specify it in my.cnf file, the remaining part of the configuration will not matter, because the log file won’t be created. This is also the only option which you cannot change at runtime from MySQL console wit SET or SET GLOBAL command.

log_slow_filter=name


Log only the queries that followed certain execution plan. Multiple flags allowed in a comma-separated string. [qc_miss, full_scan, full_join, tmp_table, tmp_table_on_disk, filesort, filesort_on_disk]

It allows you to filter queries logged by execution plan. For example to log only queries doing full table scans you would need to set this to “full_scan”, while in order to get only those which use on-disk temporary storage for intermediate results “tmp_table_on_disk,filesort_on_disk” would be a proper flags set. To clear the filter just assign an empty string “” to this option.

Note: you should put double quotes around the entire string of comma-separated flags.

Can be changed at run time with both SET SESSION and SET GLOBAL.

log_slow_rate_limit=#


Rate limit statement writes to slow log to only those from every (1/log_slow_rate_limit) session.

With high traffic coming to your database, the slow logging may consume a lot of IO bandwidth and the file may grow huge very quickly when logging all the queries. This parameter allows you to get the full sessions logged while doing it only for every n-th of them thus limiting the number of writes to the log.

Note: this feature will fail to work well if your application uses some kind of connection pooling. Rate limiting is disabled for the replication thread.

Can be changed at run time with both SET SESSION and SET GLOBAL.

log_slow_verbosity=name


Choose how verbose the messages to your slow log will be. Multiple flags allowed in a comma-separated string. [microtime, query_plan, innodb]

msl patch currently can log three types of information: query timings, execution plan details and InnoDB engine per-query statistics. With this option you may choose which of those you want to have in your slow log. For example to have microsecond query timing and InnoDB statistics you would need to set this option to “microtime,innodb”.

Note: You should put double quotes around the entire string of comma-separated flags. Currently “microtime” is mandatory meaning you cannot disable it. “innodb” is only available with the patch supporting that feature.

Can be changed at run time with both SET SESSION and SET GLOBAL.

long_query_time=#


Log all queries that have taken more than long_query_time microseconds to execute to file.

This option is standard MySQL, however after you apply the patch, it will no longer take time in seconds. Instead it will want you to specify the number of microseconds.

Can be changed at run time with both SET SESSION and SET GLOBAL.

min_examined_row_limit=#


Don’t log queries which examine less than min_examined_row_limit rows to file.

If you are not interested in queries which scan no more than N rows, you can set this to the desired value.

Can be changed at run time with both SET SESSION and SET GLOBAL.