澳门新浦京娱乐场网站-www.146.net-新浦京娱乐场官网
做最好的网站

慢查询日志分析工具,mysql慢查询日志解析工具

mysql数据库的慢查询日志是相当的重大的一项调优协助日志,但是mysql默许记录的日志格式阅读时非常不够自身,那是由mysql日志记录准则所调整的,捕获一条就记下一条,虽说记录的音讯足够详尽,但假诺将浏览慢查询日志做为一项普通专业,间接阅读mysql生成的慢查询日志就有不小可能率异常低效了。

author:skate
time:2012/02/17

mysqlsla是hackmysql.com推出的一款日志深入分析工具,全部来讲,功用拾贰分庞大.数据报表,非常便利剖判慢查询的原由,包蕴实行功能,数据量,查询消耗等.

MySQL慢查询剖判

而外操作系统命令直接查看slowlog外,mysql本身也提供了二个读书slowlog的命令行工具:mysqldumpslow,该命令行提供了一定的剖析汇总功能,能够将五个类似的SQL语句空洞展现成四个,可是成效照旧稍微简陋,除了那个之外,还应该有众多的第三方工具,可用于深入分析mysql慢查询日志,当中,三思用了片刻mysqlsla,感到轻松又易用。

mysqlsla的设置与利用

安装mysqlsla:

 

mysqlsla不仅可用来管理慢查询日志,也得以用来分析任何日志比方二进制日志,普通查询日志等等,其对sql语句的架空作用特别实用,参数设定简练易用,很好上手。

1.下载 mysqlsla
[[email protected] tmp]# wget
--19:45:45-- 
Resolving hackmysql.com... 64.13.232.157
Connecting to hackmysql.com|64.13.232.157|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 33674 (33K) [application/x-tar]
Saving to: `mysqlsla-2.03.tar.gz.2'

1、获取mysqlsla.zip安装包

在我们做系统质量调优的时候,数据库的慢查询语句的优化是须求的,非常是电子商务类型的重度MYSQL应用项目。

此时此刻mysqlsla的流行版本为2.03,能够下拉到官方网址下载,地址如下:
http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz

100%[===========================================================================================>] 33,674      50.2K/s   in 0.7s   

2、安装须求的扶助包:yuminstall perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker –y

上边我们一道来探视如何做好MYSQL的慢查询深入分析吧。

mysqlsla是perl编写的本子,运营mysqlsla要求perl-DBI和per-DBD-Mysql两模块的协助,因而在运维mysqlsla前供给首先安装DBI模块和对应的数据库DBD驱动,而暗中认可情形下linux不设置那五个模块,须要活动下载安装,下载地址如下:

19:45:47 (50.2 KB/s) - `mysqlsla-2.03.tar.gz.2' saved [33674/33674]

3、安装DBI

1,开启MYSQL的慢查询日志

DBI的编写翻译安装步骤如下:
# tar xvfz DBI-1.608.tar.gz
# cd DBI-1.608
# perl Makefile.PL
# make
# make test
# make install

2.解压
[[email protected] tmp]# tar -zxvf mysqlsla-2.03.tar.gz
mysqlsla-2.03/
mysqlsla-2.03/Changes
mysqlsla-2.03/INSTALL
mysqlsla-2.03/README
mysqlsla-2.03/Makefile.PL
mysqlsla-2.03/bin/
mysqlsla-2.03/bin/mysqlsla
mysqlsla-2.03/META.yml
mysqlsla-2.03/lib/
mysqlsla-2.03/lib/mysqlsla.pm
mysqlsla-2.03/MANIFEST
[[email protected] tmp]# cd mysqlsla-2.03
[[email protected] mysqlsla-2.03]# ls
bin  Changes  INSTALL  lib  Makefile.PL  MANIFEST  META.yml  README

wget

  www.2cto.com  

DBD-mysql驱动模块的编写翻译安装步骤如下:
# tar xvfz DBD-mysql-4.011.tar.gz
# cd DBD-mysql-4.011
# perl Makefile.PL
# make
# make install

3.实践perl脚本检查包正视关系
[[email protected] mysqlsla-2.03]# perl Makefile.PL 
Checking if your kit is complete...
Looks good
Writing Makefile for mysqlsla

tar xfDBI-1.636.tar.gz

第一在my.cnf配置内部参加慢查询配置,然后创立慢查询的日记文件,并把用户和组修改为mysql,最后重启mysqld。

亟需专注,在设置DBD-mysql时要求使用mysql_config,该命令包罗在MySQL-devel安装包中,要是当前系统中一贯不安装该软件,要求首先安装MySQL-devel,不然DBD-mysql在编写翻译进度中会出现错误。

4.安装
[[email protected] mysqlsla-2.03]# make && make install;
cp lib/mysqlsla.pm blib/lib/mysqlsla.pm
cp bin/mysqlsla blib/script/mysqlsla
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysqlsla
Manifying blib/man3/mysqlsla.3pm
Installing /usr/lib/perl5/site_perl/5.8.8/mysqlsla.pm
Installing /usr/share/man/man3/mysqlsla.3pm
Installing /usr/bin/mysqlsla
Writing /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/mysqlsla/.packlist
Appending installation info to /usr/lib/perl5/5.8.8/i386-linux-thread-multi/perllocal.pod
[[email protected] mysqlsla-2.03]# 

cdDBI-1.636

 

策画干活完全,就能够安装mysqlsla了,编写翻译安装步骤如下:
# tar xvfz mysqlsla-2.03.tar.gz 
# cd mysqlsla-2.03
# perl Makefile.PL
# make
# make install

5.粗略利用

perlMakefile.PL

vim /etc/my .cnf

mysqlsla命令暗中认可会保存在/usr/bin路线下,平日可在随机路线下直接施行。对慢查询日志文件的分析,最简化的调用格局如下:
# mysqlsla -lt slow [SlowLogFilePath] > [ResultFilePath]

语法:
 Slow log: mysqlsla -lt slow slow.log
 General log: mysqlsla -lt general general.log
 Binary log: mysqlbinlog bin.log | mysqlsla -lt binary - 

make && make install

# 在配置文件的[mysqld]上面进入以下几行

应用办法:

这里以slow log为例:

4、安装mysqlsla

log-slow-queries= /var/log/mysql-slow .log

选择mysqlsla剖判MySQL慢查询日志

[[email protected] mysqlsla-2.03]# mysqlsla -lt slow /tmp/127_slow.log | more
Report for slow logs: /tmp/127_slow.log
24 queries total, 6 unique
Sorted by 't_sum'
Grand Totals: Time 16 s, Lock 1 s, Rows sent 18, Rows Examined 2.10M

unzip mysqlsla.zip

long_query_time=0.01 #意味着查询时间超越10ms的都认为是慢查询

澳门新浦京娱乐场网站 1

______________________________________________________________________ 001 ___
Count         : 18  (75.00%)
Time          : 15 s total, 833.333 ms avg, 0 to 8 s max  (93.75%)
  95% of Time : 7 s total, 411.765 ms avg, 0 to 4 s 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 : 116.51k avg, 8 to 1.05M max  (99.99%)
Database      : 
Users         : 
        [email protected]  : 100.00% (18) of query, 100.00% (24) of all users

cd mysqlsla

log-queries-not-using-indexes #意味着没有使用索引的询问也记录日志

#查询记录最多的20个sql语句,并写到select.log中去
mysqlsla -lt slow --sort t_sum --top 20  /data/mysql/127-slow.log >/tmp/select.log
#统计慢查询文件为/data/mysql/127-slow.log的所有select的慢查询sql,并显示执行时间最长的100条sql,并写到sql_select.log中去
mysqlsla -lt slow  -sf " select" -top 100  /data/mysql/127-slow.log >/tmp/sql_select.log
#统计慢查询文件为/data/mysql/127-slow.log的数据库为mydata的所有select和update的慢查询sql,并查询次数最多的100条sql,并写到sql_num.sql中去
mysqlsla -lt slow  -sf " select,update" -top 100 -sort c_sum  -db mydata /data/mysql/127-slow.log >/tmp/sql_num.log

Query abstract:
INSERT INTO t2 SELECT * FROM t2;

perl Makefile.PL

 

澳门新浦京娱乐场网站 2

Query sample:
insert into t2 select * from t2;
........

make &&make install

touch /var/log/mysql-slow .log

举个例子说,原始慢日志中有一批的下列语句:
# Time: 110417  0:00:09
# User@Host: junsansi[junsansi] @  [192.168.1.27]
# Query_time: 3  Lock_time: 0  Rows_sent: 1  Rows_examined: 17600
select min(DOC_HIS_ID) AS DOC_HIS_ID from t_******** where DOC_HIS_ISTEAMMATE=1 and DOC_HIS_EDITOR_USER_ID_ENCRYPT='nfEACAwQEW1MICAN2';
# User@Host: junsansi[junsansi] @  [192.168.1.27]
#慢查询日志分析工具,mysql慢查询日志解析工具。 Query_time: 4  Lock_time: 0  Rows_sent: 1  Rows_examined: 17600
select min(DOC_HIS_ID) AS DOC_HIS_ID from t_******** where DOC_HIS_ISTEAMMATE=1 and DOC_HIS_EDITOR_USER_ID_ENCRYPT='nfEACAwQEW2MICAN2';
# User@Host: jss[junsansi] @  [192.168.1.26]
# Query_time: 4  Lock_time: 0  Rows_sent: 1  Rows_examined: 17600
select min(DOC_HIS_ID) AS DOC_HIS_ID from t_******** where DOC_HIS_ISTEAMMATE=1 and DOC_HIS_EDITOR_USER_ID_ENCRYPT='nfEACAwQEW3MICAN2';
# User@Host: junsansi[junsansi] @  [192.168.1.27]
# Query_time: 3  Lock_time: 0  Rows_sent: 1  Rows_examined: 17600
select min(DOC_HIS_ID) AS DOC_HIS_ID from t_******** where DOC_HIS_ISTEAMMATE=1 and DOC_HIS_EDITOR_USER_ID_ENCRYPT='nfEACAwQEW4MICAN2';
# User@Host: jss[junsansi] @  [192.168.1.26]
# Query_time: 5  Lock_time: 0  Rows_sent: 1  Rows_examined: 17600
select min(DOC_HIS_ID) AS DOC_HIS_ID from t_******** where DOC_HIS_ISTEAMMATE=1 and DOC_HIS_EDITOR_USER_ID_ENCRYPT='nfEACAwQEW5MICAN2';
....................
....................

采取表达:
总查询次数 (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语句

应用mysqlsla工具深入分析慢查询日志

chown mysql.mysql /var/log/mysql-slow .log

直接阅读的操作经验相当不好,使用mysqlsla管理后,结果表现如下:
Count         : 23  (8.52%)
Time          : 102 s total, 4.434783 s avg, 3 s to 7 s max  (6.79%)
  95% of Time : 88 s total, 4.190476 s avg, 3 s to 6 s 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     : 1 avg, 1 to 1 max  (0.02%)
Rows examined : 11.53k avg, 5.70k to 17.60k max  (1.07%)
Database      : jssdb
Users         : 
        junsansi@ 192.168.1.27 : 86.96% (20) of query, 11.11% (30) of all users
        jss@ 192.168.1.26 : 13.04% (3) of query, 2.96% (8) of all users

mysqlsla常用参数表明:

[root@test-db01 DBI-1.636]# /usr/local/bin/mysqlsla/application/mysql/data/mysql-slow.log

/etc/init .d /mysqld restart

Query abstract:
SELECT MIN(doc_his_id) AS doc_his_id FROM t_******** WHERE doc_his_isteammate=N AND doc_his_editor_user_id_encrypt='S';

1) -log-type (-lt) type logs:
透过这么些参数来制定log的种类,首要有slow, general, binary, msl, udl,剖判slow log时通过制订为slow.

Auto-detected logs as slow logs

随之测验一下慢查询是不是见效,能够访问一下phpmyadmin大概跑一条select sleep(1),然后再cat一下/var/log/mysql-slow.log,假设见到有记录就表示设置成功了。可是,生成慢查询日志只是忠贞的 记录了每一条慢查询,对于大家做解析并不便于。

Query sample:
select min(DOC_HIS_ID) AS DOC_HIS_ID from t_******** where DOC_HIS_ISTEAMMATE=1 and DOC_HIS_EDITOR_USER_ID_ENCRYPT='nfEACAwQEW2MICAN2';

2) -sort:
拟定使用什么参数来对深入分析结果开始展览排序,私下认可是根据t_sum来进展排序。
t_sum:按总时间排序
c_sum:按总次数排序
c_sum_p: sql语句施行次数占总实行次数的百分比。

Report for slow logs:/application/mysql/data/mysql-slow.log

 

在上述结果中,语句的履市场价格况(执行次数,对象音信,查询记录量,时间支出,来源计算)等新闻可想而知,相比便于DBA进一步分析了。

3) -top:
显示sql的数目,暗中认可是10,表示按准则取排序的前有个别条

39.83k queries total, 81 unique

2,安装mysqlsla慢查询剖析工具

 

4) –statement-filter (-sf) [ -][TYPE]:
过滤sql语句的品种,举例select、update、drop.
[TYPE]有SELECT, CREATE, DROP, UPDATE, INSERT,比方" SELECT,INSERT",不出新的暗许是-,即不包蕴。

澳门新浦京娱乐场网站,Sorted by 't_sum'

wget http: //hackmysql .com /scripts/mysqlsla-2 .03. tar .gz

原文:

5) db:要拍卖哪个库的日志:

Grand Totals: Time 4.43k s, Lock 4 s, Rows sent 32.27M,Rows Examined 95.33M

tar xzf mysqlsla-2.03. tar .gz

例如,只取backup库的select语句、按c_sum_p排序的前2条记下

______________________________________________________________________001 ___

cd mysqlsla-2.03

[[email protected] mysqlsla-2.03]# mysqlsla -lt slow -sort c_sum_p  -sf  " select" -db backup -top 2  /tmp/127_slow.log 
Report for slow logs: /tmp/127_slow.log
4 queries total, 3 unique
Sorted by 'c_sum_p'
Grand Totals: Time 1 s, Lock 1 s, Rows sent 18, Rows Examined 195

Count:16.58k(41.63%)

   www.2cto.com  

______________________________________________________________________ 001 ___
Count         : 2  (50.00%)
Time          : 0 total, 0 avg, 0 to 0 max  (0.00%)
Lock Time (s) : 0 total, 0 avg, 0 to 0 max  (0.00%)
Rows sent     : 1 avg, 1 to 1 max  (11.11%)
Rows examined : 86 avg, 77 to 94 max  (87.69%)
Database      : 
Users         : 
        [email protected]  : 100.00% (2) of query, 100.00% (4) of all users

Time:4310.320218 s total, 259.955 ms avg, 480 ?s to 1.644234 s max(97.25%)

perl Makefile.PL

Query abstract:
SELECT SUM(format(duration,N)) AS duration FROM information_schema.profiling WHERE query_id=N;

95% of Time :3798.395814 s total, 241.153 ms avg, 480 ?s to 544.42 ms max

make

Query sample:
select sum(format(duration,6)) as duration from information_schema.profiling where query_id=7;

Lock Time (s) : 222.879 ms total, 13 ?s avg, 8 ?s to 142?s max(5.87%)

make install

______________________________________________________________________ 002 ___
Count         : 1  (25.00%)
Time          : 1 s total, 1 s avg, 1 s to 1 s max  (100.00%)
Lock Time (s) : 1 s total, 1 s avg, 1 s to 1 s max  (100.00%)
Rows sent     : 4 avg, 4 to 4 max  (22.22%)
Rows examined : 12 avg, 12 to 12 max  (6.15%)
Database      : 
Users         : 
        [email protected]  : 100.00% (1) of query, 100.00% (4) of all users

95% of Lock :199.801 ms total, 13 ?s avg, 8 ?s to 21 ?s max

#安装音信

Query abstract:
SELECT * FROM tt WHERE a=N;

Rows sent:1.94k avg, 1.01k to 3.10k max(99.62%)

#Installing /usr/local/share/perl5/mysqlsla.pm

Query sample:
select * from tt where a=2;
[[email protected] mysqlsla-2.03]# 

Rows examined : 1.94k avg, 1.01k to 3.10k max(33.72%)

#Installing /usr/local/share/man/man3/mysqlsla.3pm

[[email protected] mysqlsla-2.03]# 

Database:mysqlslap

#Installing /usr/local/bin/mysqlsla

 

Users:

#Appending installation info to /usr/lib/perl5/perllocal.pod

 

root@localhost: 100.00% (16581) of query, 41.65% (16590) ofall users

 

 

Query abstract:

file /usr/local/bin/mysqlsla

 

SET timestamp=N; SELECT intcol1,charcol1 FROM t1;

#实在是三个perl脚本

--------end-------  

Query sample:

#/usr/local/bin/mysqlsla: a /usr/bin/perl -w script text executable

time:二〇一二/02/17 mysqlsla的装置与应用 1.下载 mysqlsla [[email protected] tmp]# wget --19:45:45-- ...

SET timestamp=1492597033;

3,慢查询总结

SELECT intcol1,charcol1 FROM t1;

 

______________________________________________________________________002 ___

#计算出现次数最多的前10条慢查询

Count:10.64k(26.71%)

mysqlsla -lt slow /var/log/mysql-slow .log - top 10 - sort c_sum > top10_count_sum.log

Time:42.264367 s total, 3.973 ms avg, 2.601 ms to 12.175 ms max(0.95%)

#总计实践时间的总的数量前10条慢查询

95% of Time :37.86066 s total, 3.746 ms avg, 2.601 ms to 8.007 ms max

mysqlsla -lt slow /var/log/mysql-slow .log - top 10 - sort t_sum > top10_time_sum.log

Lock Time (s) : 724.429 ms total, 68 ?s avg, 25 ?s to 243?s max(19.09%)

#总括平均实施时间最长的前10条慢查询(常用)

95% of Lock :650.909 ms total, 64 ?s avg, 25 ?s to 122 ?s max

mysqlsla -lt slow /var/log/mysql-slow .log - top 10 - sort t_avg > top10_time_avg.log

Rows sent: 0avg, 0 to 1 max(0.01%)

张开个中贰个log总结文件,你拜候到:

Rows examined : 4.20k avg, 4.20k to 4.20k max(46.85%)

  www.2cto.com  

Database:union_common

Report for slow logs: /var/log/mysql-slow.log 被深入分析的慢查询日志文件 

Users:

40 queries total, 12 unique 40条查询;除了重新的,有12条查询 

lwl-com[lwl-com]@192.168.10.33 : 99.98% (10636) of query, 28.13% (11203) of all users

Sorted by ‘t_avg’ 按平均查询时间排序 

guest@192.168.10.230 : 0.02% (2) of query, 0.02% (9) of all users

Grand Totals: Time 4 s, Lock 0 s, Rows sent 236, Rows Examined 8.63k

Query abstract:

______________________________________________________________________ 001 ___

SET timestamp=N; SELECT dictionaryid, parentid, code,name, description, url, sort, addopenid, adddate, updateopenid, updatedate,dictionarytype FROM dictionary WHERE ( code = 'S' );

Count : 1 (2.50%)这条SQL出现了1次,占SQL总数的2.5%

Query sample:

Time : 588.994 ms total实施时间总和, 588.994 ms avg平均每便查询的日子, 588.994 ms最短期 to 588.994 ms max最长日子 (13.78%)

SET timestamp=1489399770;

Lock Time (s) : 91 µs total, 91 µs avg, 91 µs to 91 µs max (2.34%)

select

Rows sent : 30 avg, 30 to 30 max (12.71%)

DictionaryId,ParentId, Code, Name, Description, Url, Sort, AddOpenId, AddDate, UpdateOpenId,

Rows examined : 899 avg, 899 to 899 max (10.41%)

UpdateDate,DictionaryType

Database :

from dictionary

Users :

WHERE (Code = 'L1_VEHICLEINFO' );

[email protected] : 100.00% (1) of query, 100.00% (40) of all users

格式表明如下:

 

总查询次数(queries total),去重后的sql数量(unique)

Query abstract:

输出报表的源委排序(sortedby)

SET timestamp=N; SELECT * FROM ecm_goods WHERE goods_name LIKE ‘S’ ORDER BY ecm_goods.brand_id ASC LIMIT N, N;

最重大的慢sql计算新闻,满含平均实施时间,等待锁时间,结果行的总量,扫描的行总的数量.

 

Count, sql的实践次数及占总的slow log数量的百分比.

Query sample:

Time,实施时间,包罗总时间,平均时间,最小,最大日子,时间占到总慢sql时间的百分比.

SET timestamp=1341467496;

95% of 提姆e,去除最快和最慢的sql,覆盖率占95%的sql的施行时间.

SELECT * FROM `ecm_goods` WHERE goods_name like ‘冰箱’ ORDER BY `ecm_goods`.`brand_id` ASC

Lock Time,等待锁的时间.

LIMIT 0, 30;

95% of Lock , 95%的慢sql等待锁时间.

______________________________________________________________________ 002 ___

Rows sent,结果行总计数据,包含平均,最小,最大数量.

Count : 2 (5.00%) 这条SQL出现了2次,占SQL总数的5%

Rows examined,扫描的行数量.

Time : 57.38 ms total 实行时间总和, 28.69 ms avg 平均每一遍查询的岁月, 27.503 ms 最短时间 to 29.877 ms max 最长日子 (1.34%)

Database,属于哪个数据库

Lock Time (s) : 134 µs total, 67 µs avg, 64 µs to 70 µs max (3.44%)

Users,哪个用户,IP,占到全数用户实施的sql百分比

Rows sent : 3 avg, 3 to 3 max (2.54%)

Query abstract,抽象后的sql语句

Rows examined : 3 avg, 3 to 3 max (0.07%)

Query sample, sql语句

Database :

mysqlsla常用参数

Users :  www.2cto.com  

澳门新浦京娱乐场网站 3

[email protected] : 100.00% (2) of query, 100.00% (40) of all usersQuery abstract:

广大的用法:

SET timestamp=N; SELECT * FROM documents LIMIT N, N;

mysqldumpslow -sc -t10/var/run/mysqld/mysqld-slow.log#抽取使用最多的10条慢查询

Query sample:

mysqldumpslow -st -t3/var/run/mysqld/mysqld-slow.log#抽取查询时间最慢的3条慢查询

SET timestamp=1341399487;

mysqldumpslow -st -t10-g “leftjoin”

SELECT * FROM `documents` LIMIT 0, 30;

/database/mysql/slow-log#得到遵照时间排序的前10条里面满含左连接的查询语句

 

mysqldumpslow -sr -t10-g'left

…别的省略…

join'/var/run/mysqld/mysqld-slow.log#依据扫描行数最多的

 

只要要求做更复杂的计算,能够参见官方文书档案:

设若期待每隔一段时间,举例一天,出叁遍慢查询计算来讲,能够写多少个shell脚本,然后放到/etc/crontab里面。那样的话,就足以定期做询问优化。

 

 

作者 alex.wu

在咱们做系统性能调优的时候,数据库的慢查询语句的优化是至关重要的,极其是电子商务类型的重度MYSQL应用项目。 下边...

本文由澳门新浦京娱乐场网站发布于数据库,转载请注明出处:慢查询日志分析工具,mysql慢查询日志解析工具