MySQL基准测试(二)--方法

目的：

方法不是越高级越好。而应该善于做减法。至简是一种智慧，首先要做的是收集MySQL的各状态数据。收集到了，不管各个时间段出现的问题，至少你手上有第一时间的状态数据，而不是凭空想象的去做剖析和分析问题。

数据模拟参考
仿真多线程插入和查询语句，可以参考Python模拟数据(MySQL数据库)文档

MySQL状态命令的解释

SHOW GLOBAL VARIABLES 查看目前的MySQL的各个参数变量值。
SHOW GLOBAL STATUS 查看全局的MySSQL的状态信息。
SHOW FULL PROCESSLIST 查看当前MySQL当前进程运行的具体信息。
SHOW ENGINE INNODB STATUS 查看InnoDB当前的状态信息

备注:
当MySQL出现瞬时负载升高，IO负载增大的时候。SHOW FULL PROCESSLIST 和pager more联用，可以立马查到当前MySQL线程中的一些问题。现在可以把SHOW FULL PROCESSLIST 封装成一个脚本来执行，效果更佳。也说明MySQL的状态值的收集的重要性！

脚本封装

碰到问题，快速定位问题，把上述的状态命令进行封装。定位查找，脚本如下:

备注:
这里将来会写一个MySQL线上环境的一个实例《xxxxx》，来解释MySQL服务间歇性问题，如:MySQL服务卡壳或者慢查询。

exec_mysql_processlist.sh 打出当前进程运行的具体信息

#!/bin/sh
file=$(date +%F_%H_%M_%S)
PREFIX=ZMySQL-edition
loadavg="$(uptime)"
ts="$(date +"TS %s.%N %F %T")"
echo "$ts $loadavg" >> $PREFIX-${file}-processlist
/usr/local/mysql/bin/mysql -uroot -p'zsd@7101' -S /data/mysqldata/3306/mysql.sock -e 'SHOW FULL PROCESSLISTG' >> $PREFIX-${file}-processlist

exec_mysql_status.sh 打出当前MySQL的状态信息

#!/bin/sh
file=$(date +%F_%H_%M_%S)
PREFIX=ZMySQL-edition
loadavg="$(uptime)"
ts="$(date +"TS %s.%N %F %T")"
echo "$ts $loadavg" >> $PREFIX-${file}-status
/usr/local/mysql/bin/mysql -uroot -p'zsd@7101' -S /data/mysqldata/3306/mysql.sock -e 'SHOW GLOBAL STATUS' >> $PREFIX-${file}-status

exec_mysql_innodbstatus.sh 打出当前MySQL Innodb存储引擎的状态信息

#!/bin/sh
file=$(date +%F_%H_%M_%S)
PREFIX=ZMySQL-edition
loadavg="$(uptime)"
ts="$(date +"TS %s.%N %F %T")"
echo "$ts $loadavg" >> $PREFIX-${file}-innodbstatus
/usr/local/mysql/bin/mysql -uroot -p'zsd@7101' -S /data/mysqldata/3306/mysql.sock -e 'SHOW ENGINE INNODB STATUSG' >> $PREFIX-${file}-innodbstatus

脚本收集

collect_mysql.sh 每5秒收集上述各个状态命令的相关信息，并输出到文件中，后续可以用awk等其他格式化命令来分析日志文件，并画图变现出来。

#!/bin/sh
INTERVAL=5
PREFIX=$INTERVAL-sec-status
RUNFILE=/data/mysqldata/running
/usr/local/mysql/bin/mysql -uroot -p'zsd@7101' -S /data/mysqldata/3306/mysql.sock -e 'SHOW GLOBAL VARIABLES' >> mysql-variables
while test -e $RUNFILE; do
    file=$(date +%F_%H)
    sleep=$(date +%s.%N | awk "{print $INTERVAL -($1 % $INTERVAL)}")
    sleep 5
    ts="$(date +"TS %s.%N %F %T")"
    loadavg="$(uptime)"
    echo "$ts $loadavg" >> $PREFIX-${file}-status
    /usr/local/mysql/bin/mysql -uroot -p'zsd@7101' -S /data/mysqldata/3306/mysql.sock -e 'SHOW GLOBAL STATUS' >> $PREFIX-${file}-status &
    echo "$ts $loadavg" >> $PREFIX-${file}-innodbstatus
    /usr/local/mysql/bin/mysql -uroot -p'zsd@7101' -S /data/mysqldata/3306/mysql.sock -e 'SHOW ENGINE INNODB STATUSG' >> $PREFIX-${file}-innodbstatus &
    echo "$ts $loadavg" >> $PREFIX-${file}-processlist
    /usr/local/mysql/bin/mysql -uroot -p'zsd@7101' -S /data/mysqldata/3306/mysql.sock -e 'SHOW FULL PROCESSLISTG' >> $PREFIX-${file}-processlist &
    echo $ts
done
echo Exiting because $RUNFILE does not exist.

收集日志的输出结果

[root@racnode1 scripts]# ls -l 
total 63192
-rw-rw-r-- 1 mysql mysql 5504846 Jun 15 18:00 5-sec-status-2018-06-15_17-innodbstatus
-rw-rw-r-- 1 mysql mysql  436374 Jun 15 18:00 5-sec-status-2018-06-15_17-processlist
-rw-rw-r-- 1 mysql mysql 7169459 Jun 15 18:00 5-sec-status-2018-06-15_17-status
-rw-rw-r-- 1 mysql mysql 2695460 Jun 15 18:26 5-sec-status-2018-06-15_18-innodbstatus
-rw-rw-r-- 1 mysql mysql  212897 Jun 15 18:26 5-sec-status-2018-06-15_18-processlist
-rw-rw-r-- 1 mysql mysql 3512080 Jun 15 18:26 5-sec-status-2018-06-15_18-status

其中三个不同的文件分别为MySQL status，processlist和innodb status的具体信息。

脚本分析

analyze 分析上述生成的文件如:5-sec-status-2018-06-15_17-status,变成一个固定格式，用于图片输出

#!/bin/sh

awk '
   BEGIN{
     printf "#ts date time load QPS";
	 fmt = " %.2f";
   }
   /^TS/{
     ts      = substr($2,1,index($2,".")-1);
     load    = NF - 2;
     diff    = ts -	prev_ts;
     prev_ts = ts;
     printf "
%s %s %s %s",ts,$3,$4,substr($load,1,length($load)-1);	 
   }
   /Queries/{
     printf fmt,($2-Queries)/diff;
	 Queries=$2
   }
    ' "$@"

分析日志的输出结果

[root@racnode1 scripts]# ./analyze 5-sec-status-2018-06-15_17-status >>QPS-5-sec-0615

日志信息如下:

#ts date time load QPS
1529053574 2018-06-15 17:06:14 0.49 0.00
1529053579 2018-06-15 17:06:19 0.53 1.00
1529053584 2018-06-15 17:06:24 0.49 1.20
1529053622 2018-06-15 17:07:02 0.52 0.39
1529053627 2018-06-15 17:07:07 0.48 1.20
1529053632 2018-06-15 17:07:12 0.44 1.20

画图

gnuplot为一个制图开源工具。可以通过yum install gnuplot安装。

gnuplot_mysql_status.sh 画图，并生成一个为MySQL_STATUS.png的图片。

#!/bin/bash
TITLE="MySQL_GLOBAL_STATUS"
OUTPUT="MySQL_STATUS.png"
PLOT="plot 'QPS-5-sec-06161735' using 4 title'LOAD' w lines,
           'QPS-5-sec-06161735' using 5 title 'QPS' w lines"
YRANGE="[0:]"
gnuplot << EOF
   set terminal png small size 480,360
   set title "$TITLE"
   set xtics 60
#   set mxtics 720
   set yrange $YRANGE
   set grid
   set output "$OUTPUT"
   $PLOT
EOF

图片如下: