mysql 慢查询说明

hdfdtr

1.工具说明
mysqlsla是hackmysql.com推出的一款MySQL的日志分析工具，功能非常强大. 数据报表,非常有利于分析慢查询的原因, 包括执行频率, 数据量, 查询消耗等。


2.安装mysqlsla
下载地址：http://hackmysql.com/mysqlsla


2.1.安装
# tar -zxvf mysqlsla-2.03.tar.gz
# cd mysqlsla-2.03
# make && make install


安装出错：
Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at Makefile.PL line 2.
BEGIN failed--compilation aborted at Makefile.PL line 2.


需要安装perl 相关包：
# yum -y install perl-devel
# yum -y install perl-CPAN


3.使用mysqlsla
3.1.使用命令
mysqlsla -lt slow mysql-slow.log
或者
mysqlsla -lt slow mysql-slow.log -sf “+SELECT” -db dbName -top 10 -sort t_sum  –top=10


3.2.参数说明
参数意义：
lt：表示日志类型，有slow, general, binary, msl, udl。
sf：[+-][TYPE]，[TYPE]有SELECT, CREATE, DROP, UPDATE, INSERT，例如”+SELECT,INSERT”，不出现的默认是-，即不包括。
db：要处理哪个库的日志。
top：表示取按规则排序的前多少条。
sort：按某种规则排序，t_sum按总时间排序， c_sum按总次数排序。


3.3.分析结果
总查询次数 (queries total), 去重后的sql数量 (unique)
输出报表的内容排序(sorted by)
最重大的慢sql统计信息, 包括 平均执行时间, 等待锁时间, 结果行的总数, 扫描的行总数.
Count, sql的执行次数及占总的slow log数量的百分比.
Time, 执行时间, 包括总时间, 平均时间, 最小, 最大时间, 时间占到总慢sql时间的百分比.
95% of Time, 去除最快和最慢的sql, 覆盖率占95%的sql的执行时间.
Lock Time, 等待锁的时间.
95% of Lock , 95%的慢sql等待锁时间.
Rows sent, 结果行统计数量, 包括平均, 最小, 最大数量.
Rows examined, 扫描的行数量.
Database, 属于哪个数据库
Users, 哪个用户,IP, 占到所有用户执行的sql百分比
Query abstract, 抽象后的sql语句
Query sample, sql语句


4.执行命令
# mysqlsla -lt slow slow.log
Report for slow logs: slow.log
200.98k queries total, 896 unique
Sorted by 't_sum'  #按照总时间排序
Grand Totals: Time 9.11k s, Lock 11 s, Rows sent 322.59M, Rows Examined 819.93M




______________________________________________________________________ 001 ___
Count         : 1  (0.00%)  #sql执行次数占总slow log的百分比0.00%
Time          : 2862.478802 s total, 2862.478802 s avg, 2862.478802 s to 2862.478802 s max  (31.43%) #执行时间占总慢sql的31.43%
Lock Time (s) : 267 otal, 267 vg, 267 o 267 ax  (0.00%)  #去除最快和最慢的sql, 覆盖率占0%的sql的执行时间.
Rows sent     : 951.83k avg, 951.83k to 951.83k max  (0.30%)  #结果行统计数量, 包括平均, 最小, 最大数量.
Rows examined : 18.08k avg, 18.08k to 18.08k max  (0.00%)     #扫描行数量
Database      : uupay     #使用的数据库
Users         :
        dawagame@ 112.64.173.78 : 100.00% (1) of query, 0.30% (603) of all users  #用户、IP、占到执行sql的0.30%


Query abstract:
SET timestamp=N; SELECT * FROM (SELECT * FROM uu_paylog WHERE pay_time BETWEEN N AND N) AS pa,(SELECT * FROM uu_paylog_history WHERE pay_time BETWEEN N AND N) AS ps;  #查询语句


Query sample:
SET timestamp=1395808846;
SELECT * FROM (SELECT * FROM `uu_paylog` WHERE pay_time between 1392393600 AND 1395802751) as pa,(SELECT * FROM `uu_paylog_history` WHERE pay_time between 1392393600 AND 1395802751) as ps;  #查询语句


______________________________________________________________________ 002 ___
Count         : 23  (0.01%)
Time          : 1744.759876 s total, 75.859125 s avg, 68.960445 s to 90.404704 s max  (19.16%)
  95% of Time : 1570.735116 s total, 74.79691 s avg, 68.960445 s to 80.307138 s max
Lock Time (s) : 995 otal, 43 vg, 36 o 86 ax  (0.01%)
  95% of Lock : 845 otal, 40 vg, 36 o 59 ax
Rows sent     : 1.54M avg, 1.54M to 1.54M max  (10.99%)
Rows examined : 1.54M avg, 1.54M to 1.54M max  (4.32%)
Database      : datacenter
Users         :
        root@localhost  : 100.00% (23) of query, 7.40% (14880) of all users


Query abstract:
SET timestamp=N; SELECT /*!N SQL_NO_CACHE */ * FROM droleselect;


Query sample:
SET timestamp=1395781352;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `dRoleSelect`;


______________________________________________________________________ 003 ___
Count         : 23  (0.01%)
Time          : 1141.456968 s total, 49.628564 s avg, 41.787475 s to 54.329336 s max  (12.53%)
  95% of Time : 1032.950757 s total, 49.188131 s avg, 41.787475 s to 54.003767 s max
Lock Time (s) : 946 otal, 41 vg, 28 o 104 ax  (0.01%)
  95% of Lock : 752 otal, 36 vg, 28 o 53 ax
Rows sent     : 926.18k avg, 926.18k to 926.18k max  (6.60%)
Rows examined : 926.18k avg, 926.18k to 926.18k max  (2.60%)
Database      : datacenter
Users         :
        root@localhost  : 100.00% (23) of query, 7.40% (14880) of all users


Query abstract:
SET timestamp=N; SELECT /*!N SQL_NO_CACHE */ * FROM dgamelogin;


Query sample:
SET timestamp=1395781267;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `dGameLogin`;


______________________________________________________________________ 004 ___
Count         : 3.91k  (1.94%)
Time          : 1136.448932 s total, 290.875 ms avg, 101.145 ms to 1.241743 s max  (12.48%)
  95% of Time : 1026.760535 s total, 276.68 ms avg, 101.145 ms to 481.652 ms max
Lock Time (s) : 0 total, 0 avg, 0 to 0 max  (0.00%)
  95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent     : 0 avg, 0 to 0 max  (0.00%)
Rows examined : 0 avg, 0 to 0 max  (0.00%)
Database      :
Users         :
        root@localhost  : 100.00% (3907) of query, 7.40% (14880) of all users


Query abstract:
SET timestamp=N;


Query sample:
SET timestamp=1395770401;


______________________________________________________________________ 005 ___
Count         : 627  (0.31%)
Time          : 820.093928 s total, 1.307965 s avg, 1.129821 s to 2.762843 s max  (9.00%)
  95% of Time : 752.770582 s total, 1.265161 s avg, 1.129821 s to 1.676759 s max
Lock Time (s) : 93.863 ms total, 150 vg, 93 o 1.228 ms max  (0.84%)
  95% of Lock : 83.639 ms total, 141 vg, 93 o 225 ax
Rows sent     : 0 avg, 0 to 0 max  (0.00%)
Rows examined : 754.64k avg, 754.64k to 754.64k max  (57.71%)
Database      : iyingke
Users         :
        iyingke_db@localhost  : 100.00% (627) of query, 0.59% (1193) of all users


Query abstract:
SET timestamp=N; SELECT cid,mid,COUNT(*) FROM cinemamovie WHERE playtime BETWEEN 'S' AND 'S' GROUP BY cid,mid;


Query sample:
SET timestamp=1395775395;
select cid,mid,count(*) FROM cinemamovie where playtime between '2014-03-26 04:03:14' AND '2014-03-26 23:59:59' GROUP BY cid,mid;


______________________________________________________________________ 006 ___
Count         : 23  (0.01%)
Time          : 161.369311 s total, 7.016057 s avg, 5.696684 s to 12.72534 s max  (1.77%)
  95% of Time : 138.779739 s total, 6.608559 s avg, 5.696684 s to 9.190991 s max
Lock Time (s) : 905 otal, 39 vg, 31 o 52 ax  (0.01%)
  95% of Lock : 802 otal, 38 vg, 31 o 49 ax
Rows sent     : 1.36M avg, 1.36M to 1.36M max  (9.67%)
Rows examined : 1.36M avg, 1.36M to 1.36M max  (3.80%)
Database      :
Users         :
        root@localhost  : 100.00% (23) of query, 7.40% (14880) of all users


Query abstract:
SET timestamp=N; SELECT /*!N SQL_NO_CACHE */ * FROM showlog;


Query sample:
SET timestamp=1395781210;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `showLog`;


______________________________________________________________________ 007 ___
Count         : 23  (0.01%)
Time          : 158.490591 s total, 6.890895 s avg, 6.073215 s to 8.692146 s max  (1.74%)
  95% of Time : 141.553816 s total, 6.740658 s avg, 6.073215 s to 8.028118 s max
Lock Time (s) : 1.155 ms total, 50 vg, 37 o 138 ax  (0.01%)
  95% of Lock : 947 otal, 45 vg, 37 o 63 ax
Rows sent     : 133.52k avg, 133.52k to 133.52k max  (0.95%)
Rows examined : 133.52k avg, 133.52k to 133.52k max  (0.37%)
Database      :
Users         :
        root@localhost  : 100.00% (23) of query, 7.40% (14880) of all users


Query abstract:
SET timestamp=N; SELECT /*!N SQL_NO_CACHE */ * FROM dreg;


Query sample:
SET timestamp=1395781275;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `dReg`;


______________________________________________________________________ 008 ___
Count         : 23  (0.01%)
Time          : 127.179138 s total, 5.529528 s avg, 5.053959 s to 6.666312 s max  (1.40%)
  95% of Time : 114.415055 s total, 5.448336 s avg, 5.053959 s to 6.033416 s max
Lock Time (s) : 1.171 ms total, 51 vg, 39 o 87 ax  (0.01%)
  95% of Lock : 999 otal, 48 vg, 39 o 69 ax
Rows sent     : 3.73M avg, 3.73M to 3.73M max  (26.60%)
Rows examined : 3.73M avg, 3.73M to 3.73M max  (10.46%)
Database      :
Users         :
        root@localhost  : 100.00% (23) of query, 7.40% (14880) of all users


Query abstract:
SET timestamp=N; SELECT /*!N SQL_NO_CACHE */ * FROM osspayuser;


Query sample:
SET timestamp=1395781502;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `ossPayUser`;


______________________________________________________________________ 009 ___
Count         : 823  (0.41%)
Time          : 109.827912 s total, 133.448 ms avg, 100.121 ms to 292.359 ms max  (1.21%)
  95% of Time : 100.6995 s total, 128.937 ms avg, 100.121 ms to 186.794 ms max
Lock Time (s) : 0 total, 0 avg, 0 to 0 max  (0.00%)
  95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent     : 0 avg, 0 to 0 max  (0.00%)
Rows examined : 0 avg, 0 to 0 max  (0.00%)
Database      :
Users         :
        root@localhost  : 100.00% (823) of query, 7.40% (14880) of all users


Query abstract:
SET timestamp=N; PURGE BINARY logs TO 'S';


Query sample:
SET timestamp=1395771601;
purge binary logs to 'mysql-bin.016583';


______________________________________________________________________ 010 ___
Count         : 23  (0.01%)
Time          : 73.43714 s total, 3.192919 s avg, 3.015631 s to 3.397369 s max  (0.81%)
  95% of Time : 66.686371 s total, 3.175541 s avg, 3.015631 s to 3.306015 s max
Lock Time (s) : 868 otal, 38 vg, 30 o 67 ax  (0.01%)
  95% of Lock : 740 otal, 35 vg, 30 o 51 ax
Rows sent     : 362.86k avg, 362.86k to 362.86k max  (2.59%)
Rows examined : 362.86k avg, 362.86k to 362.86k max  (1.02%)
Database      :
Users         :
        root@localhost  : 100.00% (23) of query, 7.40% (14880) of all users


Query abstract:
SET timestamp=N; SELECT /*!N SQL_NO_CACHE */ * FROM uu_logs;


Query sample:
SET timestamp=1395781531;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `uu_logs`;