MySQL benchmark test (ii)--method

MySQL benchmark test (ii)--Method Purpose:

The method is not as high as the higher the better. And should be good at subtraction. To Jane is a kind of wisdom, the first thing to do is to collect MySQL state data. Collected, regardless of the time period of the problem, at least you have the first time of the state data, rather than imagined to do analysis and analyze the problem.

Data Simulation Reference
Simulation of multi-threaded INSERT and query statements, you can refer to the Python simulation data (MySQL database) document

Explanation of the MySQL status command

• Show GLOBAL VARIABLES View the current MySQL parameters for each parameter variable value.
• Show Global Status View the state information for the myssql.
• Show full processlist View the details of the current MySQL current process run.
• Show ENGINE INNODB Status View INNODB current status information

Note:
When MySQL has an instantaneous load increase, the IO load increases. SHOW FULL PROCESSLISTand pager more associated, you can immediately find some problems in the current MySQL thread. It is now possible to SHOW FULL PROCESSLIST encapsulate the package into a script for better performance. Also explains the importance of the collection of MySQL status values!

Script Encapsulation

If you encounter problems, quickly locate the problem and encapsulate the above status commands. Locate the script as follows:

Note:
In the future, we will write an example of MySQL online environment, "xxxxx", to explain the MySQL service intermittent problems, such as: MySQL service jam or slow query.

• exec_mysql_processlist.sh Specific information about the current process run

#!/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' [email protected] '-s/data/mysqldata/3306/mysql.sock-e' SHOW full Processlist\g ' >> $PREFIX-${file}-processlist

• exec_mysql_status.sh The current MySQL status information

#!/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' [email protected] '-s/data/mysqldata/3306/mysql.sock-e' SHOW GLOBAL STATUS ' >> $PREFIX-${file}-status

• exec_mysql_innodbstatus.sh status information for the current MySQL InnoDB storage engine

#!/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' [email protected] '-s/data/mysqldata/3306/mysql.sock-e' SHOW ENGINE INNODB status\g ' >> $PREFIX-${file}-innodbstatus

Script Collection

• collect_mysql.sh collects information about each of the above status commands every 5 seconds and outputs them to a file, which can be used to parse the log files with other formatting commands such as awk, and draw them out.

#!/bin/shinterval=5prefix= $INTERVAL-sec-statusrunfile=/data/mysqldata/running/usr/local/mysql/bin/mysql-uroot-p' [email protected] '-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)}")    Sleep5ts="$(Date+"TS%s.%n%F%T")"    loadavg="$(Uptime)"    Echo "$ts $loadavg" >> $PREFIX-${file}-status/usr/local/mysql/bin/mysql-uroot-p' [email protected] '-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' [email protected] '-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' [email protected] '-s/data/mysqldata/3306/mysql.sock-e' SHOW full Processlist\g ' >> $PREFIX-${file}-processlist&    Echo $ts DoneEchoExiting because$RUNFILEdoes not exist.

Collect the output of the log

[[email protected]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

Three of these different files are specific information for MySQL status,processlist and InnoDB status, respectively.

Script Analysis

• Analyze parses the resulting file, such as: 5-sec-status-2018-06-15_17-status, into a fixed format for picture output

#!/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   }    ‘"[email protected]"

Analyze the output of the log

[[email protected]>>QPS-5-sec-0615

• The log information is as follows:

  #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

  Drawing

  Gnuplot is a cartographic open source tool. Can be yum install gnuplot installed by.

• gnuplot_mysql_status.sh Draw and generate a picture for 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 TerminalPNG small size 480,360 set title "$TITLE"SetXtics 60# SET Mxtics720SetYrange$YRANGESeT GridSet Output "$OUTPUT"$PLOTEof

• The picture is as follows:
  

MySQL benchmark test (ii)--method