MySQL性能分析、及調(diào)優(yōu)工具使用詳解

本文匯總了MySQL DBA日常工作中用到的些工具，方便初學(xué)者，也便于自己查閱。

先介紹下基礎(chǔ)設(shè)施（CPU、IO、網(wǎng)絡(luò)等）檢查的工具：

vmstat、sar（sysstat工具包）、mpstat、oprofile、nicstat、dstat、iotop、tsar、iostat 掌握幾個(gè)即可，功能大同小異（個(gè)人常用的是 dstat、sar）。

CPU：

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

    上圖可以看到CPU調(diào)度不均衡。因此我們可以嘗試將pid為700的進(jìn)程綁定到第四顆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

內(nèi)存：

    vmtouch  【很cool的工具】  參考：http://blog.yufeng.info/archives/1903

    作用：

       查看一個(gè)文件（或者目錄）哪些部分在內(nèi)存中；

       把文件調(diào)入內(nèi)存；

       把文件清除出內(nèi)存；

       把文件鎖住在內(nèi)存中而不被換出到磁盤上；

1. 看看/bin目錄有多少內(nèi)容在內(nèi)存中

$ vmtouch /bin/
           Files: 92
     Directories: 1
  Resident Pages: 348/1307  1M/5M 26.6%
         Elapsed: 0.003426 seconds

2. 看看某文件（big-dataset.txt）有多少在內(nèi)存中

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開(kāi)始時(shí)沒(méi)有數(shù)據(jù)在內(nèi)存中，在用tail命令讀入了部分?jǐn)?shù)據(jù)后，有19MB的數(shù)據(jù)進(jìn)入了內(nèi)存。

3. 把文件（a.txt）清除出內(nèi)存

$ vmtouch -vea.txt
Evicting a.txt

           Files: 1
     Directories: 0
   Evicted Pages: 42116 (164M)
         Elapsed: 0.076824 seconds

vmtouch主要作用是做數(shù)據(jù)的warm-up，即對(duì)于將要用到的數(shù)據(jù)，通過(guò)vmtouch把它們事先讀入內(nèi)存，而不是在需要時(shí)再?gòu)挠脖P上讀入，這樣可以提高系統(tǒng)效率。

網(wǎng)絡(luò)：

    nicstat -z 1 （需要參照網(wǎng)上自己編譯安裝）

全面的工具：

    dstat  -tclmdny 1

   oprofile  

    【這個(gè)工具太厲強(qiáng)大，但是需要我們先安裝帶debug的kernel才行，具體請(qǐng)自行查閱文檔】

基礎(chǔ)設(shè)施沒(méi)問(wèn)題后，我們才可以去考慮MySQL的參數(shù)調(diào)優(yōu)及性能優(yōu)化。

常用的性能分析工具有：

mytop、innotop、orzdba、dodba、mysqltuner.pl。（這幾個(gè)工具都很好用）

此外，常用的帶web界面的監(jiān)控工具有：

zabbix

lepus 天兔

官網(wǎng)：http://www.lepus.cc/page/opensource

專業(yè)做mysql監(jiān)控的軟件。

可直觀監(jiān)控主從狀態(tài)，界面比較炫。支持分析慢查詢（基于pt-query-digest實(shí)現(xiàn)）

PMM監(jiān)控的部署（采用docker容器的部署方法很簡(jiǎn)單，界面也很炫，但是感覺(jué)還是不夠?qū)嵱谩?span >）

命令行的pt工具包套件：

percona-toolkit家族的系列工具，功能非常多，已經(jīng)在我博客里面拆分成好幾篇基本上都羅列了一遍用法了。

此外， 比較推薦的有如下2個(gè)命令行的工具：

innotop 【mytop的加強(qiáng)版】

參考://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   可以查看到支持的參數(shù)。不過(guò)我們不在命令行輸入密碼，所有這些參數(shù)一般都用不到的。

./innotop --write   進(jìn)入交互式界面后，輸入@ 根據(jù)提示輸入相關(guān)的參數(shù)，然后退出命令行。會(huì)在當(dāng)前目錄下生成一個(gè).innotop/innotop.conf里面記錄了數(shù)據(jù)的密碼信息。【我們只要在最后一步，提示是否保存密碼到文本中時(shí)候，選擇n即可只保存配置信息，不記錄密碼】

輸入? ，顯示所有支持的命令，如下圖：

按q鍵，可以退出inntop的控制臺(tái)界面。

在當(dāng)前目錄下，自動(dòng)生成一個(gè).innotop的隱藏文件夾。innotop.conf里面保存了數(shù)據(jù)庫(kù)的連接方式。

如果我們不退出剛才的Dashboard界面，直接輸入I ，可以看到IO相關(guān)狀態(tài)，如下圖：

輸入Q，可以查看query list

然后按e并輸入thread ID顯示執(zhí)行計(jì)劃或者按f顯示完整sql語(yǔ)句，或者按o顯示系統(tǒng)優(yōu)化過(guò)的語(yǔ)句（需要MySQL的版本支持EXPLAIN EXTENDED）

innotop是通過(guò)information_schema.processlist來(lái)獲得完整的sql語(yǔ)句，并且根據(jù)COMMAND來(lái)過(guò)濾掉空閑線程的。

輸入S，然后選擇我們上面的test連接配置，可查看 Variables & Status。

輸入B，顯示 Buffer Pool的狀態(tài)：

輸入M，可以查看主從復(fù)制的狀態(tài)。下圖是master上的截圖：

mytop

比較簡(jiǎn)單。

mytop  --user root --pass root

mysqltuner

一個(gè)pl腳本，mysqltuner 根據(jù)提示輸入用戶名和密碼即可分析并給出優(yōu)化的建議。直接使用yum install mysqltuner也能安裝。

orzdba

http://code.taobao.org/p/orzdba/src/trunk/

chmod +x orzdba即可。

我個(gè)人常用下面的2個(gè)即可，其他的狀態(tài)使用dstat、sar等命令來(lái)獲取。

注意：下面的2個(gè)參數(shù)的調(diào)用默認(rèn)是沒(méi)有地方輸入用戶名和密碼的，因此建議將密碼寫(xiě)到/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

上面的命令參數(shù)含義：

    -s     #snaplen, 也就是數(shù)據(jù)包的截取長(zhǎng)度

    -nn    #將地址、端口顯示為數(shù)字格式【經(jīng)常用】

    -q     #快速輸出。只輸出較少的協(xié)議信息?！窘?jīng)常用】

    -tttt # 在每行打印的時(shí)間戳之前添加日期的打印【經(jīng)常用】

    -c     #在收到指定的數(shù)量的分組后，tcpdump就會(huì)停止。

step2、

pt-query-digest --type tcpdump/root/dump.txt  即可分析出結(jié)果

#!/bin/bash

#實(shí)時(shí)采集并分析1000個(gè)分組的sql包數(shù)據(jù)

#Date:2017/05/02

#注意部分機(jī)器的網(wǎng)卡是eth2或多網(wǎng)卡,腳本里面需要做修改

source/etc/profile

[! -e /usr/sbin/tcpdump ] &&  yum  install  tcpdump -y > /dev/null 2>&1

#如果抓取的數(shù)據(jù)太少，可以修改-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 "統(tǒng)計(jì)結(jié)果輸出到/root/pt_result.log" && rm -f /root/dump.txt

else

  echo "程序運(yùn)行出錯(cuò)"

fi

dodba 【推薦】

doDBA tools是一個(gè)基于控制臺(tái)的遠(yuǎn)程監(jiān)控工具，它不需要在本地/遠(yuǎn)程系統(tǒng)上安裝任何軟件，它可以實(shí)時(shí)收集操作系統(tǒng)、MySQL、InnoDB的實(shí)時(shí)性能狀態(tài)數(shù)據(jù)，并可以生成Doing日志文件，來(lái)幫助你快速了解/優(yōu)化系統(tǒng)及MySQL數(shù)據(jù)庫(kù)。

遠(yuǎn)程收集系統(tǒng)信息是通過(guò)ssh（用戶名密碼或建立信任）的方式連接到遠(yuǎn)程服務(wù)器上收集，收集的方法都是通過(guò)讀取Linux的proc下的等meminfo，diskstats，uptime，net，vmstat ，cpuinfo ，loadavg等文件，這和pmm，zabbix收集方式一致。

遠(yuǎn)程收集MySQL信息是通過(guò) MySQL tcp連接到MySQL數(shù)據(jù)庫(kù)上收集，只需要授予連接用戶PROCESS、SELECT權(quán)限即可。

系統(tǒng)信息和MySQL信息的收集可以分離，如果只想收集系統(tǒng)信息，只需要提供系統(tǒng)用戶名密碼即可，如果只收集MySQL可以只提供MySQL連接信息，如果是rds用戶，可以使用-rds參數(shù)，在使用mytop時(shí)會(huì)自動(dòng)忽略系統(tǒng)信息的收集。

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

功能非常強(qiáng)大，官方地址：https://github.com/dblucyne/dodba_tools 介紹的很詳細(xì)

具體如何使用在運(yùn)維幫的公眾號(hào)上面有比較全面的介紹，下面是我摘錄的部分內(nèi)容：

./doDBA-help   查看幫助

收集Linux性能數(shù)據(jù)

./doDBA-h=192.168.2.11 -sys  # 通過(guò)ssh遠(yuǎn)程連接去獲取信息

搜集MySQL性能數(shù)據(jù)

./doDBA-h=127.0.0.1 -mysql

執(zhí)行前需要先去修改 doDBA.conf  配置上mysql的用戶名密碼和端口：

{

"Host":"",

"Huser":"root",

"Hport":"22",

"Hpwd":  "",

"Muser": "root",

"Mpwd": "Abcd@1234",

"Mport":"3306"

}

### 這里需要注意一點(diǎn)就是：搜集遠(yuǎn)程mysql時(shí)候，必須要先在目標(biāo)MySQL上對(duì)本機(jī)IP做授權(quán)，不然會(huì)出現(xiàn)下圖類似的報(bào)錯(cuò)。

搜集InnoDB性能數(shù)據(jù)

./doDBA-h=127.0.0.1 -innodb

搜集MySQL及Linux性能數(shù)據(jù)

./doDBA-h=127.0.0.1 -myall

類似Mytop的功能

./doDBA-h=127.0.0.1 -mytop

搜集到日志文件

./doDBA-h=127.0.0.1 -mytop -log

開(kāi)啟Doing功能

./doDBA -h=127.0.0.1-myall -t=3   #　當(dāng)有超過(guò)3個(gè)活躍線程時(shí)就自動(dòng)執(zhí)行show processlist 和show engine innodb status\G 并把采集到的信息存到dbdba.log

配合shell批量采集

cat ip.txt 內(nèi)容如下：

192.168.2.11

192.168.2.12

cat ip.txt | while read ip;  do  echo $ip;  ./doDBA -h=$ip -mysql -log

重磅推薦：my.cnf 參數(shù)計(jì)算器（推薦）：

http://www.mysqlcalculator.com/

不同物理硬件的服務(wù)器，my.cnf 的部分參數(shù)需要調(diào)整，如果參數(shù)設(shè)置的太大，可能導(dǎo)致OOM。因此有必要用這個(gè)計(jì)算器算一下。

上文中部分內(nèi)容參考自網(wǎng)易王洪權(quán)的ppt。