MySQL性能分析、及调优工具使用详解
本文汇总了MySQL DBA日常工作中用到的些工具，方便初学者，也便于自己查阅。
先介绍下基础设施（CPU、IO、网络等）检查的工具：
vmstat、sar（sysstat工具包）、mpstat、oprofile、nicstat、dstat、iotop、tsar、iostat 掌握几个即可，功能大同小异（个人常用的是 dstat、sar）。
CPU：
mpstat -P ALL 1 每隔1s显示一次
上图可以看到CPU调度不均衡。因此我们可以尝试将pid为700的进程绑定到第四颗CPU上。taskset -p 03 700
磁盘：
vmstat -S m 1 10
iostat -xkdz 1
iotop
pt-ioprofile --profile-pid=$(pidof mysqld) --cell=sizes --run-time=30
内存：
vmtouch 【很cool的工具】 参考：http://blog.yufeng.info/archives/1903
作用：
查看一个文件（或者目录）哪些部分在内存中；
把文件调入内存；
把文件清除出内存；
把文件锁住在内存中而不被换出到磁盘上；
1. 看看/bin目录有多少内容在内存中
$ vmtouch /bin/
Files: 92
Directories: 1
Resident Pages: 348/1307 1M/5M 26.6%
Elapsed: 0.003426 seconds
2. 看看某文件（big-dataset.txt）有多少在内存中
How much ofbig-dataset.txt is currently in memory?
$ vmtouch -v big-dataset.txt
big-dataset.txt
[ ] 0/42116
Files: 1
Directories: 0
Resident Pages: 0/42116 0/164M 0%
Elapsed: 0.005182 seconds
然后读入部分文件，
$ tail -n 10000big-dataset.txt > /dev/null
然后在用vmtouch查看一下：
$ vmtouch -vbig-dataset.txt
big-dataset.txt
[ oOOOOOOO] 4950/42116
Files: 1
Directories: 0
Resident Pages: 4950/42116 19M/164M 11.8%
Elapsed: 0.006706 seconds
我们可以看出big-datset.txt开始时没有数据在内存中，在用tail命令读入了部分数据后，有19MB的数据进入了内存。
3. 把文件（a.txt）清除出内存
$ vmtouch -vea.txt
Evicting a.txt
Files: 1
Directories: 0
Evicted Pages: 42116 (164M)
Elapsed: 0.076824 seconds
vmtouch主要作用是做数据的warm-up，即对于将要用到的数据，通过vmtouch把它们事先读入内存，而不是在需要时再从硬盘上读入，这样可以提高系统效率。
网络：
nicstat -z 1 （需要参照网上自己编译安装）
全面的工具：
dstat -tclmdny 1
oprofile
【这个工具太厉强大，但是需要我们先安装带debug的kernel才行，具体请自行查阅文档】
基础设施没问题后，我们才可以去考虑MySQL的参数调优及性能优化。
常用的性能分析工具有：
mytop、innotop、orzdba、dodba、mysqltuner.pl。（这几个工具都很好用）
此外，常用的带web界面的监控工具有：
zabbix
lepus 天兔
官网：http://www.lepus.cc/page/opensource
专业做mysql监控的软件。
可直观监控主从状态，界面比较炫。支持分析慢查询（基于pt-query-digest实现）
PMM监控的部署（采用docker容器的部署方法很简单，界面也很炫，但是感觉还是不够实用。）
命令行的pt工具包套件：
percona-toolkit家族的系列工具，功能非常多，已经在我博客里面拆分成好几篇基本上都罗列了一遍用法了。
此外， 比较推荐的有如下2个命令行的工具：
innotop 【mytop的加强版】
参考://www.percona.com/blog/2013/10/14/innotop-real-time-advanced-investigation-tool-mysql/
下载地址：git clone https://github.com/innotop/innotop.git 【epel源里面也可以直接yum方式安装】
配置方法：
yum install perl-TermReadKey perl-Time-HiRes perl-DBD-MySQL perl-DBI
./innotop --help 可以查看到支持的参数。不过我们不在命令行输入密码，所有这些参数一般都用不到的。
./innotop --write 进入交互式界面后，输入@ 根据提示输入相关的参数，然后退出命令行。会在当前目录下生成一个.innotop/innotop.conf里面记录了数据的密码信息。【我们只要在最后一步，提示是否保存密码到文本中时候，选择n即可只保存配置信息，不记录密码】
输入? ，显示所有支持的命令，如下图：
按q键，可以退出inntop的控制台界面。
在当前目录下，自动生成一个.innotop的隐藏文件夹。innotop.conf里面保存了数据库的连接方式。
如果我们不退出刚才的Dashboard界面，直接输入I ，可以看到IO相关状态，如下图：
输入Q，可以查看query list
然后按e并输入thread ID显示执行计划或者按f显示完整sql语句，或者按o显示系统优化过的语句（需要MySQL的版本支持EXPLAIN EXTENDED）
innotop是通过information_schema.processlist来获得完整的sql语句，并且根据COMMAND来过滤掉空闲线程的。
输入S，然后选择我们上面的test连接配置，可查看 Variables & Status。
输入B，显示 Buffer Pool的状态：
输入M，可以查看主从复制的状态。下图是master上的截图：
mytop
比较简单。
mytop --user root --pass root
mysqltuner
一个pl脚本，mysqltuner 根据提示输入用户名和密码即可分析并给出优化的建议。直接使用yum install mysqltuner也能安装。
orzdba
http://code.taobao.org/p/orzdba/src/trunk/
chmod +x orzdba即可。
我个人常用下面的2个即可，其他的状态使用dstat、sar等命令来获取。
注意：下面的2个参数的调用默认是没有地方输入用户名和密码的，因此建议将密码写到/etc/my.cnf 的client段里面。
./orzdba -mysql -C 10 -i 1
./orzdba -innodb -C 10 -i 1
tcpdump抓包捕获瞬间的SQL
step1、
tcpdump -s 65535 -x -nn -q -tttt -i eth0 -c 5000 port 3306 > /root/dump.txt
上面的命令参数含义：
-s #snaplen, 也就是数据包的截取长度
-nn #将地址、端口显示为数字格式【经常用】
-q #快速输出。只输出较少的协议信息。【经常用】
-tttt # 在每行打印的时间戳之前添加日期的打印【经常用】
-c #在收到指定的数量的分组后，tcpdump就会停止。
step2、
pt-query-digest --type tcpdump/root/dump.txt 即可分析出结果
#!/bin/bash
#实时采集并分析1000个分组的sql包数据
#Date:2017/05/02
#注意部分机器的网卡是eth1或多网卡,脚本里面需要做修改
source/etc/profile
[! -e /usr/sbin/tcpdump ] && yum install tcpdump -y > /dev/null 2>&1
#如果抓取的数据太少，可以修改-c 5000 多抓取些用于分析
tcpdump -s 65535 -x -nn -q -tttt -i eth0 -c 5000 port 3306 > /root/dump.txt && pt-query-digest --type tcpdump /root/dump.txt > /root/pt_result.log
if[ $? -eq 0 ] ; then
echo "统计结果输出到/root/pt_result.log" && rm -f /root/dump.txt
else
echo "程序运行出错"
fi
dodba 【推荐】
doDBA tools是一个基于控制台的远程监控工具，它不需要在本地/远程系统上安装任何软件，它可以实时收集操作系统、MySQL、InnoDB的实时性能状态数据，并可以生成Doing日志文件，来帮助你快速了解/优化系统及MySQL数据库。
远程收集系统信息是通过ssh（用户名密码或建立信任）的方式连接到远程服务器上收集，收集的方法都是通过读取Linux的proc下的等meminfo，diskstats，uptime，net，vmstat ，cpuinfo ，loadavg等文件，这和pmm，zabbix收集方式一致。
远程收集MySQL信息是通过 MySQL tcp连接到MySQL数据库上收集，只需要授予连接用户PROCESS、SELECT权限即可。
系统信息和MySQL信息的收集可以分离，如果只想收集系统信息，只需要提供系统用户名密码即可，如果只收集MySQL可以只提供MySQL连接信息，如果是rds用户，可以使用-rds参数，在使用mytop时会自动忽略系统信息的收集。
http://www.ywnds.com/?p=7499
下载地址：https://github.com/dblucyne/dodba_tools
wget https://raw.githubusercontent.com/dblucyne/dodba_tools/master/doDBA--no-check-certificate
wget https://raw.githubusercontent.com/dblucyne/dodba_tools/master/doDBA.conf--no-check-certificate
chmod +x doDBA
功能非常强大，官方地址：https://github.com/dblucyne/dodba_tools 介绍的很详细
具体如何使用在运维帮的公众号上面有比较全面的介绍，下面是我摘录的部分内容：
./doDBA-help 查看帮助
收集Linux性能数据
./doDBA-h=192.168.2.11 -sys # 通过ssh远程连接去获取信息
搜集MySQL性能数据
./doDBA-h=127.0.0.1 -mysql
执行前需要先去修改 doDBA.conf 配置上mysql的用户名密码和端口：
{
"Host":"",
"Huser":"root",
"Hport":"22",
"Hpwd": "",
"Muser": "root",
"Mpwd": "Abcd@1234",
"Mport":"3306"
}
### 这里需要注意一点就是：搜集远程mysql时候，必须要先在目标MySQL上对本机IP做授权，不然会出现下图类似的报错。
搜集InnoDB性能数据
./doDBA-h=127.0.0.1 -innodb
搜集MySQL及Linux性能数据
./doDBA-h=127.0.0.1 -myall
类似Mytop的功能
./doDBA-h=127.0.0.1 -mytop
搜集到日志文件
./doDBA-h=127.0.0.1 -mytop -log
开启Doing功能
./doDBA -h=127.0.0.1-myall -t=3 # 当有超过3个活跃线程时就自动执行show processlist 和show engine innodb status\G 并把采集到的信息存到dbdba.log
配合shell批量采集
cat ip.txt 内容如下：
192.168.2.11
192.168.2.12
cat ip.txt | while read ip; do echo $ip; ./doDBA -h=$ip -mysql -log
重磅推荐：my.cnf 参数计算器（推荐）：
http://www.mysqlcalculator.com/
不同物理硬件的服务器，my.cnf 的部分参数需要调整，如果参数设置的太大，可能导致OOM。因此有必要用这个计算器算一下。
上文中部分内容参考自网易王洪权的ppt。
四、 Tcp抓包
yum install tcpdump-4.9.2-3.el7.x86_64 -y
- 1
1、通过tcpdump抓包
tcpdump -i any port 3306 -l -s 0 -w - |strings |grep -A 5 select|less
- 1
2、tcpdump+pt-query-digest
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt> slow_report9.log
pt-ioprofile定位负载来源文件，通过ps找出负载较高的进程。
- 1
pt-ioprofile --profile-pid=12036 --cell=sizes
- 1
参数说明：
–profile-pid：mysql进程的id
–cell-sizes：该参数将结果已 B/s 的方式展示出来
通过响应时间判断数据库运行状况
如果是在64位操作系统中使用，可以直接下载二进制文件使用。步骤如下：
1）下载文件 http://github.com/downloads/Lowercases/tcprstat/tcprstat-static.v0.3.1.x86_64
2）把下载的文件移动到 /usr/bin
3）把文件名修改为 tcprstat
4）修改文件权限，增加执行权限 chmod +x /usr/bin/tcprstat 如果你想在32位操作系统中使用，那你只能自己编译了。
代码下载地址 https://github.com/Lowercases/tcprstat https://launchpad.net/tcprstat
[root@localhost ~]# tcprstat --p 3306 -t 1 -n 10
timestamp count max min avg med stddev 95_max 95_avg 95_std 99_max 99_avg 99_std
1539760803 1 103 103 103 103 0 0 0 0 0 0 0
1539760804 1 108 108 108 108 0 0 0 0 0 0 0
1539760805 1 124 124 124 124 0 0 0 0 0 0 0
1539760806 1 115 115 115 115 0 0 0 0 0 0 0
1539760807 1 112 112 112 112 0 0 0 0 0 0 0
每个请求的时间在0.1ms~0.124ms
参数说明：
–p：数据库端口号
-t：刷新间隔时间
-n：输出次数
nicstat网络利器，充分了解你的网卡运行状况)
yum install http://rpmfind.net/linux/fedora/linux/releases/28/Everything/x8664/os/Packages/n/nicstat-1.95-7.fc27.x8664.rpm
- 1
[root@lkjtest ~]# nicstat -z 1
Time Int rKB/s wKB/s rPk/s wPk/s rAvs wAvs %Util Sat
15:29:14 ens160 4.03 0.91 43.18 1.60 95.61 581.8 0.00 0.00
15:29:15 ens160 3.09 0.73 35.95 2.00 88.11 375.5 0.00 0.00
15:29:16 ens160 3.93 0.66 43.99 2.00 91.52 335.5 0.00 0.00
15:29:17 ens160 3.99 0.66 45.00 2.00 90.71 335.5 0.00 0.00
15:29:18 ens160 4.04 0.66 46.99 2.00 88.04 335.5 0.00 0.00
15:29:19 ens160 3.64 0.66 42.00 2.00 88.76 335.5 0.00 0.00
参数说明：
-z：跳过0行
输出参数说明：
wKB/s,OutKB #每秒写的千字节数(transmitted)
rMbps,RdMbps #每秒读的百万字节数K(received)
%Util #接口的利用率百分比
Sat #每秒的错误数，接口接近饱和的一个指标
yum install dstat -y
[root@localhost ~]# dstat -tclmndy 1
参数说明：
-t：enable time/date output
-c：enable cpu stats
-l：enable load stats
-m：enable memory stats
-n：enable network stats
-d：enable disk stats
-y：enable system stats
vmtouch是一个学习和控制unix和类unix系统的文件系统缓存的工具。
$ git clone https://github.com/hoytech/vmtouch.git
$ cd vmtouch
$ make
$ sudo make install
显示
$ vmtouch -v big-dataset.txt
回收
vmtouch -ve a.txt
Oprofile 是一个开源的profiling工具，通过取样来工作，是一个全局的抽样统计工具。cpu无端占用高？应用程序响应慢？苦于没有分析的工具？找它就对了！通过计数采样，帮助我们从进程、函数、代码层面找出占用cpu的"罪魁祸首"。
###### 10.1 安装
yum install http://www.rpmfind.net/linux/centos/7.5.1804/os/x86_64/Packages/oprofile-0.9.9-25.el7.x86_64.rpm -y
1、使用
#加载oprofile内核模块 opcontrol --init
#我们对内核的取样没兴趣 opcontrol --setup --no-vmlinux
#在开始收集采样数据前回顾下我们的设置 opcontrol --status
#清除上一次采样到的数据 opcontrol --reset
#运行我们的程序 opcontrol --start
#收集采样数据 opcontrol --dump
#关闭守护程序, 同时准备好采样的数据 opcontrol --shutdown
注意：如报Cannot find event CPUCLKUNHALTED，解决方法如下：
#解决步骤
$sudo opcontrol --deinit
Daemon not running
Unloading oprofile module
$sudo modprobe oprofile timer=1
$dmesg|grep oprofile|tail -n 1
oprofile: using timer interrupt.
如果你看到上面的字说明你成功了。
我们再试验下：
$sudo opcontrol --init && sudo opcontrol --reset && sudo opcontrol --start
Using 2.6+ OProfile kernel interface.
Using log file /var/lib/oprofile/samples/oprofiled.log
Daemon started.
Profiler running.
2、获取采样信息
#系统级别的 opreport --long-filenames
#模块级别的 opreport image:foo -l
#源码级别的 opannotate image:foo -s
3、使用样例
opcontrol --deinit
modprobe oprofile timer=1
$dmesg|grep oprofile|tail -n 1
(oprofile: using timer interrupt.)
opcontrol --reset
pcontrol --separate=lib --no-vmlinux
--start --image=/home/mysql_user/mysqlhome/bin/mysqld
opcontrol --dump
opcontrol --shutdown
opreport -l /home/mysql_user/mysqlhome/bin/mysqld
142
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
