文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL性能分析、及调优工具使用详解

2024-04-02 19:55

关注

本文汇总了MySQL DBA日常工作中用到的些工具,方便初学者,也便于自己查阅。


先介绍下基础设施(CPU、IO、网络等)检查的工具:

vmstat、sar(sysstat工具包)、mpstat、oprofile、nicstat、dstat、iotop、tsar、iostat 掌握几个即可,功能大同小异(个人常用的是 dstat、sar)


CPU

    mpstat -P ALL 1  每隔1s显示一次

MySQL性能分析、及调优工具使用详解

    上图可以看到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

MySQL性能分析、及调优工具使用详解

 

内存:

    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 (需要参照网上自己编译安装)

MySQL性能分析、及调优工具使用详解


全面的工具:

    dstat  -tclmdny 1

MySQL性能分析、及调优工具使用详解



   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即可只保存配置信息,不记录密码】

 

输入? ,显示所有支持的命令,如下图:

MySQL性能分析、及调优工具使用详解

MySQL性能分析、及调优工具使用详解

MySQL性能分析、及调优工具使用详解

MySQL性能分析、及调优工具使用详解

MySQL性能分析、及调优工具使用详解

MySQL性能分析、及调优工具使用详解


按q键,可以退出inntop的控制台界面。

在当前目录下,自动生成一个.innotop的隐藏文件夹。innotop.conf里面保存了数据库的连接方式。

MySQL性能分析、及调优工具使用详解


如果我们不退出刚才的Dashboard界面,直接输入I ,可以看到IO相关状态,如下图:

MySQL性能分析、及调优工具使用详解


输入Q,可以查看query list

MySQL性能分析、及调优工具使用详解

然后按e并输入thread ID显示执行计划或者按f显示完整sql语句,或者按o显示系统优化过的语句(需要MySQL的版本支持EXPLAIN EXTENDED)

MySQL性能分析、及调优工具使用详解

innotop是通过information_schema.processlist来获得完整的sql语句,并且根据COMMAND来过滤掉空闲线程的。



输入S,然后选择我们上面的test连接配置,可查看 Variables & Status。

MySQL性能分析、及调优工具使用详解


输入B,显示 Buffer Pool的状态:

MySQL性能分析、及调优工具使用详解


输入M,可以查看主从复制的状态。下图是master上的截图:

MySQL性能分析、及调优工具使用详解




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

MySQL性能分析、及调优工具使用详解


./orzdba -innodb -C 10 -i 1

MySQL性能分析、及调优工具使用详解


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

 

#注意部分机器的网卡是eth2或多网卡,脚本里面需要做修改

 

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性能分析、及调优工具使用详解

 

 

搜集MySQL性能数据

./doDBA-h=127.0.0.1 -mysql

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性能分析、及调优工具使用详解

 

 

搜集MySQLLinux性能数据

./doDBA-h=127.0.0.1 -myall

MySQL性能分析、及调优工具使用详解

 

类似Mytop的功能

./doDBA-h=127.0.0.1 -mytop

MySQL性能分析、及调优工具使用详解

 

 

搜集到日志文件

./doDBA-h=127.0.0.1 -mytop -log

MySQL性能分析、及调优工具使用详解 

 

开启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。









阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯