Mysql的慢查询分析

黄振国

原文地址：http://aibangjuxin.blog.163.com/blog/static/1973080732010820113129361/

最基本的是mysql自带的mysqlslowdump

用起来很简单：
mysqldumpslow -s c -t 20 host-slow.log
参数：

-s,排序，c,t,l,r以及ac,at,al,ar分别是按照query次数，时间，lock时间，返回记录排序。加a就是倒序。
-t,top n，跟上数字就是算出top多少条
-g，跟正则表达式。

另一个是mysqlsla,也是一个perl脚本，

网址：http://hackmysql.com/mysqlsla
mysqlsla
mysqlsla parses, filters, analyzes and sorts MySQL slow, general, binary and microslow patched logs in order to create a customizable report of the queries and their meta-property values.
218  wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz
219  tar zxvf mysqlsla-2.03.tar.gz 
220  cd mysqlsla-2.03
221  perl Makefile.PL 
222  make
223  make install
224  sudo make
225  sudo make install
$ whereis mysqlsla
mysqlsla: /usr/bin/mysqlsla /usr/share/man/man3/mysqlsla.3pm
sudo yum install perl-DBI
sudo yum install perl-Time-HiRes

$ mysqlsla -lt slow /home/lxu/slow.log 
Can't locate DBI.pm in @INC (@INC contains: /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl /usr/lib/perl5/5.8.8/i386-linux-thread-multi /usr/lib/perl5/5.8.8 .) at /usr/bin/mysqlsla line 2098.
BEGIN failed--compilation aborted at /usr/bin/mysqlsla line 2098.
Since these reports are customizable, they can be used for human consumption or be fed into other scripts to further analyze the queries. For example, to profile with mk-query-profiler (a script from Baron Schwartz's Maatkit) every unique SELECT statement using database foo from a slow log:

mysqlsla -lt slow slow.log -R print-unique -mf "db=foo" -sf "+SELECT" | \
mk-query-profiler -separate -database foo

In brief, mysqlsla is a liaison allowing other scripts easy access to queries from a MySQL log. For a quick introduction to what mysqlsla is capable of doing, take a glance at the guide.