"MySQL" "Pressure test" using Sysbench to perform stress testing on MySQL

1. Background

? From Percona, a multi-threading system pressure measurement tool that evaluates the performance of a system based on a variety of factors that affect database server performance. For example, it can be used to test file IO, operating system scheduler, memory allocation and transfer speed, POSIX threads, and database servers. Sysbench supports the LUA scripting language, and Lua's settings for various test scenarios can be very flexible. Sysbench supports MySQL, operating system and hardware testing.

2. Installation and use

Installation:

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bashsudo yum -y install sysbench

Use:

? Data preparation

#!/bin/shexport ld_library_path=/usr/local/mysql/lib/.  ~/.bash_profile# the following three lines of comments are removed when you need to enable debug mode #set-u#set-x#set-ebasedir= "/data/sysbench" #创建sysbench文件目录if [!-D $BASEDIR ]then mkdir $BASEDIR-pficd $BASEDIR #进入sysbench文件目录 # Record all errors and standard output to Sysbench.log #exec 3>&1 4&gt ; &2 1>> sysbench_prepare.log 2>&1dbip=192.168.1.109dbport=3109dbuser= ' proxysql ' DBPASSWD= ' 123456 ' now= ' date + '%y%m%d%h%m ' dbname= "Sysbench" tblcnt=10 #表数量WARMUP =300 #预热时间 (seconds) during=1800 #持续时间 (seconds) R ows=10000000 #每个表中插入1KW行数据MAXREQ =1000000 #最大请求数为100W # When the duration or maximum number of requests is reached, the test on this round stops # Create a sysbench dedicated database echo ' now creat E db ' mysql-h$dbip-p$dbport-u$dbuser-p$dbpasswd-e ' Create Database Sysbench ' echo ' Create OK ' # # Data preparation echo ' now prepare D ATA ' Sysbench/usr/share/sysbench/oltp_read_only.lua \ #必须附加lua脚本才可以初始化数据--mysql-host= $DBIP--mysql-port= $DBPORT--m ysql-user= $DBUSER--mysql-password= $DBPASSWD--mysql-db= $DBNAME--db-driver=mysql--tables=10--table-size= $ROWS--time= $DURING Prepare 

Start of pressure measurement

#!/bin/bash###### Ye Jinlong, co-founder, veteran MySQL expert, MySQL evangelist, Oracle MySQL ace#### several caveats: # 1, client running Sysbench and MySQL The DB server should try not to be on the same host, also includes a host on the two virtual machine on the case; # 2, the number of test tables should not be too small, at least 20 tables required above; # # 3, each table of data volume should not be too small, usually at least 10 million, of course, but also according to the configuration of DB server ; # # 4, each time the reference pressure measurement should not be too short, usually requires more than 15 minutes; # 5, after each round of testing, the middle of at least 5 minutes, or confirm that the system load completely restore the state of running empty; # 6, Test DB Server if dedicated, can not be mixed with other business, Otherwise, the test results will not be reliable; # 7, the rest of the matter, follow-up to add. # # # Created by [email protected]## 2017/6/3#### sysbench Project address: https://github.com/akopytov/sysbench############ ############# 2018/02/23 Rui ###################################### #叶老师原脚本仅适用于sysbench version 0.5, Sysbench is not available after upgrading to 1.0, it now modifies some syntax. Export ld_library_path=/usr/local/mysql/lib/. ~/.bash_profile# the following three lines of comments are removed when you need to enable debug mode #set-u#set-x#set-ebasedir= "/data/sysbench" if [!-D $BASEDIR]then mkdir $BAS Edir-pficd $BASEDIR # Before cleaning up the legacy Records RM-RF $BASEDIR/logs*# record all errors and standard output to Sysbench.log exec 3>&1 4>&2 1>> sy Sbench.log 2>&1# time unit seconds dbip=192.168.1.109dbport=3109dbuser= ' proxysql ' dbpasswd= ' 123456 ' now= ' date + '%Y%m%d%H% M 'Dbname= "Sysbench" report_interval=1tblcnt=10 #表数量WARMUP =300 #预热时间 (seconds) during=1800 #持续时间 (seconds) rows=100 00000 #每个表中插入1KW行数据MAXREQ =1000000 #最大请求数为100W # When the duration or the maximum number of requests is reached, this test will stop the number of threads that are being measured concurrently and adjust according to the actual machine configuration therad_number= "8" #初始次数round =0# generally run at least 3 rounds of testing, I normally run 10 rounds above while [$round-lt 4]do# per turn log location: rounddir= $BASEDIR/logs-round${round}            Mkdir-p ${rounddir}for thread in ' echo ' ${therad_number} ' do# commonly available options: #oltp_read_only #只读 #oltp_read_write #读写兼有 #oltp_update_non_index #无主键更新情形sysbench/usr/share/sysbench/oltp_read_only.lua--mysql-host= $DBIP--mys ql-port= $DBPORT--mysql-user= $DBUSER--mysql-password= $DBPASSWD--mysql-db= $DBNAME--db-driver=mysql--tables= $T                 blcnt--table-size= $ROWS--report-interval= $REPORT _interval--threads=${thread} \--rand-type=uniform \ #数据随机类型: Uniform, uniform--time= $DURING run >> ${rounddir}/sysbench_${thread}.logsleep #不同的线 5 minutes of doneround= ' expr $rounD + 1 ' sleep #每轮压测之间停顿5分钟done 

? After running, the Sysbench output log can be found in the preset data directory.

3. Results Analysis and Mapping

? You can directly read the summary of the Sysbench log, or the data of individual items can be plotted to observe the trend.

SQL statistics:queries performed:read:142870 Write:                        0 other:20410 total:163280 transactions:    10205 (5.66 per sec.)    queries:163280 (90.53 per sec.)    Ignored errors:0 (0.00 per Sec.) Reconnects:0 (0.00 per Sec.) General Statistics:total time:1803.6625s Total number of Events:10205latenc                               Y (ms): min:3113.18 avg:11303.55 Max:                            24222.47 95th percentile:16819.24 sum: 115352747.29Threads fairness:events (Avg/stddev): 159.4531/1.51 Execution Time (Avg/stddev): 18 02.3867/1.03# I use a very limited number of hardware resources of virtual machines, the results of the test is a bit heart. 

? Installing Gnuplot for drawing, gnuplot requires a graphical environment, you can choose to install on Windows, or you can install a graphical interface on a pressure client. Here you choose to install the graphical interface on the Linux pressure client.

dnf -y install @xfce-desktop #安装图形界面yum -y install gnuplot #安装gnuplotgnuplot  #进入gnuplot终端gnuplot>plot ‘output/sysbench_8.log‘ using  9 w lines title ‘QPS‘        #using 5 表示使用第5列数据作图        #with lines 定义图中的趋势使用线来表示        #title ‘QPS‘ 定义线的名称        #使用,（逗号）分割，进行多列数据的绘制

The graphs are as follows:

Other scripts to observe system information and database information (from "high-availability MySQL") in the process of sysbench pressure measurement

#!/bin/sh# get global configuration parameters before start # Get CPU Load,mysql Global information every five seconds, InnoDB engine related information, thread information interval=5prefix= $INTERVAL-sec-statusrunfile=/ ROOT/RUNNINGMYSQL-E ' Show global Variables ' >>mysql-variables# by detecting the existence of/root/running files as a basis for obtaining information, You can delete this file at the end of the test to stop collecting while test-e $RUNFILE;        Do file=$ (date +%f_%h) sleep=$ (date +%s.%n |awk "{print $INTERVAL-(\$1% $INTERVAL)}") Sleep $sleep        Ts= "$ (date +" TS%s.%n%F%T ")" loadavg= "$ (uptime)" #通过uptime命令获取cpu load echo "$ts $loadavg" >> $PREFIX-${file}-status mysql-e "show global Status" >> $PREFIX-${file}-statu  S & #获取MySQL全局信息 echo "$ts $loadavg" >> $PREFIX-${file}-innodbstatus mysql-e "show engine InnoDB Status\g ">> $PREFIX-${file}-innodbstatus & #获取引擎信息 echo" $ts $loadavg ">> $PREFIX-${file}-proce Sslist mysql-e "show full processlist\g" >> $PREFIX-${file}-processlist & #获取线程信息 Echo $tsdoneech o ExitinG because $RUNFILE not exist     

Analyze the global information that was collected in the previous step.

#!/bin/shawk ‘        BEGIN{                printf "#ts date time load QPS";                fmt = " %.2f";                }                /^TS/ { # The timestamp lines begin with 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]"

Run as: sh hi_anaylyze.sh 5-sec-status-2018-02-22_14_status >>4plot.log (the results of the analysis are recorded in 4plot.log)

Also use Gnupot for plotting analysis:

gunplot>plot ‘4plot‘ using 5 with lines title ‘QPS‘, 4 with lines title ‘load‘        #using 5 表示使用第5列数据作图        #with lines 定义图中的趋势使用线来表示        #title ‘QPS‘ 定义线的名称        #使用,（逗号）分割，进行多列数据的绘制

? (Two graphs have different data sources and are used only as examples.) ）

RELATED links:

Ye Jinlong Teacher's: Sysbench installation, use, results interpretation
Sysbench Project Address

"MySQL" "Pressure test" using Sysbench to perform stress testing on MySQL