慢日志：mysqlsla

Linux服务器收集到的慢日志文件拿到本地(Windows7)的虚拟机(CentOS6.5)中去分析。首先使用Samba工具配置CentOS和Windows文件共享，然后使用mysqlsla分析慢查询日志。
环境：CentOS6.5源码安装多个MySQL实例及复制搭建
一、CentOS和Windows文件共享
1.1、安装Samba

[root@VMUest ~]# yum install samba samba-client samba-swat
[root@VMUest ~]# rpm -qa |grep samba
[root@VMUest ~]# service smb start

1.2、修改配置文件

#备份原始配置文件
[root@VMUest ~]# cp /etc/samba/smb.conf /etc/samba/smb.conf.`date +%Y%m%d.%H%M%S` 
#配置文件添加
[root@VMUest ~]# vim /etc/samba/smb.conf
#============================ Share Definitions ==============================
[myshare]
        comment = myshare files
        path = /myshare
        public = yes
        writable = yes

1.3、创建共享目录分配权限

#创建共享目录分配权限
[root@VMUest ~]# mkdir /myshare
[root@VMUest ~]# cd myshare/
[root@VMUest myshare]# touch samba.txt
[root@VMUest myshare]# ll
total 0
-rw-r--r--. 1 root root 0 Mar 29 15:22 samba.txt
[root@VMUest myshare]# chown -R nobody:nobody /myshare/
[root@VMUest myshare]# ll
total 0
-rw-r--r--. 1 nobody nobody 0 Mar 29 15:22 samba.txt

由于我的--Standalone Server Options--没有设置成security = share，需配置Samba用户

#配置Samba用户
[root@VMUest myshare]# smbpasswd -a root
New SMB password:
Retype new SMB password:
Added user root.
[root@VMUest myshare]# service smb restart
[root@VMUest myshare]# testparm

1.4、防火墙

#暂时停掉SELinux
[root@VMUest myshare]# setenforce 0
[root@VMUest myshare]# getenforce
#防火墙
[root@VMUest myshare]# netstat -apn |grep smb
[root@VMUest myshare]# iptables -I INPUT -p tcp --dport 139 -j ACCEPT
[root@VMUest myshare]# service iptables save
[root@VMUest myshare]# service iptables restart
[root@VMUest myshare]# iptables -L -n
[root@VMUest myshare]# cat /etc/sysconfig/iptables

Windows 我的电脑->地址栏中输入 \\192.168.85.129 (我的虚拟机IP)。如果报错"不允许一个用户使用一个以上用户名与服务器或共享资源的多重连接..."，使用 net use * /del /y 命令删除之前的连接。这样就可以在Windows下访问Linux共享的文件(夹)，并且可以将Windows下的文件拷贝到共享文件夹中

另外也可以修改/etc/samba/smb.conf的两处地方，将setsebool -P samba_enable_home_dirs on打开，将homes模块下valid users =%s语句前面的";"去掉。
二、mysqlsla
Hack MySQL has been retired.The tools are archived at https://github.com/daniel-nichter/hackmysql.com.These are retired, deprecated tools from HackMySQL.com. Do not use these tools. They are not supported. Use Percona Toolkit instead.推荐使用 Percona Toolkit代替。
2.1、下载

#下载mysqlsla
[root@VMUest ~]# cd /tools
[root@VMUest tools]# git clone https://github.com/daniel-nichter/hackmysql.com.git
#拷贝文件夹
[root@VMUest tools]# cp -Rf /tools/hackmysql.com/mysqlsla /tools/

2.2、配置编译安装

#编译安装(中间反复报错，逐个安装所需文件)
[root@VMUest tools]# cd mysqlsla/
[root@VMUest mysqlsla]# perl Makefile.PL
Can not locate ExtUtils/MakeMaker.pm in @INC (@INC contains: /usr/local/lib/perl5 /usr/local/share/perl5 /usr/lib/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib/perl5 /usr/share/perl5 .) at Makefile.PL line 2.
BEGIN failed--compilation aborted at Makefile.PL line 2.
[root@VMUest mysqlsla]#
#安装perl-ExtUtils-CBuilder时会同时安装其依赖文件perl-ExtUtils-MakeMaker，remove时并不会移除其依赖的文件
[root@VMUest mysqlsla]# yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker

[root@VMUest mysqlsla]# perl Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for mysqlsla
[root@VMUest mysqlsla]# 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
Appending installation info to /usr/lib/perl5/perllocal.pod
[root@VMUest mysqlsla]# 

[root@VMUest mysqlsla]# mysqlsla --help
Can not locate Time/HiRes.pm in @INC (@INC contains: /usr/local/lib/perl5 /usr/local/share/perl5 /usr/lib/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib/perl5 /usr/share/perl5 .) at /usr/local/bin/mysqlsla line 2095.
BEGIN failed--compilation aborted at /usr/local/bin/mysqlsla line 2095.
[root@VMUest mysqlsla]#
#安装perl-Time-HiRes
[root@VMUest mysqlsla]# yum install perl-Time-HiRes

[root@VMUest mysqlsla]# mysqlsla --help
Can nott locate DBI.pm in @INC (@INC contains: /usr/local/lib/perl5 /usr/local/share/perl5 /usr/lib/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib/perl5 /usr/share/perl5 .) at /usr/local/bin/mysqlsla line 2098.
BEGIN failed--compilation aborted at /usr/local/bin/mysqlsla line 2098.
[root@VMUest mysqlsla]#
#安装perl-DBI
[root@VMUest mysqlsla]# yum install perl-DBI

[root@VMUest mysqlsla]# mysqlsla --help
man mysqlsla for help or visit http://hackmysql.com/mysqlsla

#本次安装的所有文件
[root@VMUest mysqlsla]# yum list perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-Time-HiRes perl-DBI

到此说明mysqlsla安装完成并能正常使用
2.3、使用

#执行时间最长的20条sql语句(默认按时长)
[root@VMUest mysqlsla]# mysqlsla -lt slow --sort t_sum --top 20 /myshare/118mysqldumpslow-20161227.log
#统计慢查询文件中所有select的慢查询sql，并显示执行时间最长的20条sql语句
[root@VMUest mysqlsla]# mysqlsla -lt slow -sf "+select" -top 20 /myshare/118mysqldumpslow-20161227.log
#统计慢查询文件中的数据库为ZiPai的所有select和update的慢查询sql，并查询次数最多的20条sql语句，并写到sql_num.log中
[root@VMUest mysqlsla]# mysqlsla -lt slow -sf "+select,update" -top 20 -sort c_sum -db ZiPai /myshare/118mysqldumpslow-20161227.log >/tmp/sql_num.log

对比下mysqlsla和mysqldumpslow的返回结果

[root@VMUest mysqlsla]# mysqlsla -lt slow --sort c_sum --top 3 /myshare/118mysqldumpslow-20161227.log
Report for slow logs: /myshare/118mysqldumpslow-20161227.log
30.88k queries total, 303 unique
Sorted by 'c_sum'
Grand Totals: Time 4.15k s, Lock 11 s, Rows sent 23.70M, Rows Examined 4.82G


______________________________________________________________________ 001 ___
Count         : 4.29k  (13.88%)
Time          : 1.161066 s total, 271 vg, 116 o 21.539 ms max  (0.03%)
  95% of Time : 1.006049 s total, 247 vg, 116 o 426 ax
Lock Time (s) : 158.546 ms total, 37 vg, 15 o 20.339 ms max  (1.45%)
  95% of Lock : 108.351 ms total, 27 vg, 15 o 57 ax
Rows sent     : 9 avg, 0 to 10 max  (0.16%)
Rows examined : 59 avg, 10 to 99 max  (0.01%)
Database      : 
Users         : 
        root@ 192.168.80.225 : 53.30% (2284) of query, 29.80% (9204) of all users
        root@ 192.168.80.223 : 46.70% (2001) of query, 28.95% (8940) of all users

Query abstract:
SET timestamp=N; SELECT * FROM goods LIMIT N,N;

Query sample:
SET timestamp=1482746012;
select * from Goods limit 0,10;

______________________________________________________________________ 002 ___
Count         : 4.24k  (13.73%)
Time          : 2964.21 s total, 698.941 ms avg, 628.36 ms to 1.116798 s max  (71.50%)
  95% of Time : 2768.061991 s total, 687.205 ms avg, 628.36 ms to 862.66 ms max
Lock Time (s) : 146.727 ms total, 35 vg, 17 o 537 ax  (1.35%)
  95% of Lock : 120.535 ms total, 30 vg, 17 o 68 ax
Rows sent     : 0 avg, 0 to 88 max  (0.01%)
Rows examined : 514.07k avg, 512.57k to 515.57k max  (45.22%)
Database      : Zipai
Users         : 
        root@ 192.168.80.223 : 51.76% (2195) of query, 28.95% (8940) of all users
        root@ 192.168.80.225 : 48.24% (2046) of query, 29.80% (9204) of all users

Query abstract:
SET timestamp=N; SELECT * FROM user WHERE nickname='S';

Query sample:
SET timestamp=1482745880;
select * from User where nickName='鸿运当头';

______________________________________________________________________ 003 ___
Count         : 3.37k  (10.91%)
Time          : 3.290222 s total, 976 vg, 76 o 4.719 ms max  (0.08%)
  95% of Time : 2.857869 s total, 893 vg, 76 o 2.265 ms max
Lock Time (s) : 103.824 ms total, 31 vg, 16 o 2.682 ms max  (0.95%)
  95% of Lock : 83.589 ms total, 26 vg, 16 o 52 ax
Rows sent     : 21 avg, 6 to 100 max  (0.30%)
Rows examined : 1.71k avg, 6 to 4.64k max  (0.12%)
Database      : Zipai
Users         : 
        root@ 192.168.80.225 : 50.80% (1712) of query, 29.80% (9204) of all users
        root@ 192.168.80.223 : 49.20% (1658) of query, 28.95% (8940) of all users

Query abstract:
SET timestamp=N; SELECT * FROM ai LIMIT N,N;

Query sample:
SET timestamp=1482745934;
select * from AI limit 0,6;

mysqlsla的返回结果

[root@VMUest mysqlsla]# mysqldumpslow -s c -t 3 /myshare/118mysqldumpslow-20161227.log

Reading mysql slow query log from /myshare/118mysqldumpslow-20161227.log
Count: 4285  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@2hosts
  select * from Goods limit N,N

Count: 4241  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@2hosts
  select * from User where nickName='S'

Count: 3370  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@2hosts
  select * from AI limit N,N

mysqldumpslow的返回结果

通过对比可以看出mysqlsla返回的信息比较全面，包括平均执行时间、等待锁时间、结果行的总数、扫描的行总数、样例语句等。mysqldumpslow由于版本问题Time、Lock、Rows全为0，可以参考之前在Windows下分析慢查询日志的文章：MySQL慢查询日志
mysqlsla输出格式说明如下

#mysqlsla输出格式说明:
queries total: 总查询次数,unique: 去重后的sql数量
Sorted by: 输出报表的内容排序
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样例语句

总结：具体环境具体分析，依赖文件根据需求去安装。

posted @ 2017-03-30 16:14 醒嘞阅读(1731) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

醒嘞

学习要深入，知其所以然

慢日志：mysqlsla