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/shfile=$(date +%F_%H_%M_%S)PREFIX=ZMySQL-editionloadavg="$(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 PROCESSLIST\G‘ >> $PREFIX-${file}-processlist

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

#!/bin/shfile=$(date +%F_%H_%M_%S)PREFIX=ZMySQL-editionloadavg="$(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/shfile=$(date +%F_%H_%M_%S)PREFIX=ZMySQL-editionloadavg="$(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 STATUS\G‘ >> $PREFIX-${file}-innodbstatus

脚本收集

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

#!/bin/shINTERVAL=5PREFIX=$INTERVAL-sec-statusRUNFILE=/data/mysqldata/running/usr/local/mysql/bin/mysql -uroot -p‘zsd@7101‘ -S /data/mysqldata/3306/mysql.sock -e ‘SHOW GLOBAL VARIABLES‘ >> mysql-variableswhile 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 STATUS\G‘ >> $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 PROCESSLIST\G‘ >> $PREFIX-${file}-processlist & echo $tsdoneecho 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/shawk ‘ 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 "\n%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 QPS1529053574 2018-06-15 17:06:14 0.49 0.001529053579 2018-06-15 17:06:19 0.53 1.001529053584 2018-06-15 17:06:24 0.49 1.201529053622 2018-06-15 17:07:02 0.52 0.391529053627 2018-06-15 17:07:07 0.48 1.201529053632 2018-06-15 17:07:12 0.44 1.20

  画图

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

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

  #!/bin/bashTITLE="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" $PLOTEOF

• 图片如下: