Shulou(Shulou.com)06/01 Report--

Let's learn how to use sysbench to carry out stress testing on MySQL. I believe you will benefit a lot after reading it. There are not many words in the text. I hope you want this short article on how to use sysbench to carry out stress testing on MySQL.

1. Background

From percona, it is a multi-threaded system stress testing tool, which can evaluate the performance of the system according to various factors that affect the performance of the database cloud server. For example, it can be used to test file IO, operating system scheduler, memory allocation and transfer speed, POSIX threads, database cloud servers, etc. Sysbench supports the Lua scripting language, and Lua can be very flexible in setting up various test scenarios. Sysbench supports MySQL, operating system and hardware testing.

two。 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/. When bash_profile# needs to enable DEBUG mode, remove the following three lines of comments # set-u#set-x#set-eBASEDIR= "/ data/sysbench" # create a sysbench file directory if [!-d $BASEDIR] then mkdir $BASEDIR-pficd $BASEDIR # enter the sysbench file directory # record all errors and standard output to sysbench.log # exec 3 > & 14 > & 21 > > sysbench_prepare.log 2 > & 1DBIPbatch 192.168.1.109DBUSERTER data proxysqlDBPASSWDuring 123456Now = `date +% Y% m% d% H% M' DBNAME= "sysbench" TBLCNT=10 # Table quantity WARMUP=300 # warm-up time (seconds) DURING=1800 # duration (seconds) ROWS=10000000 # insert 1KW row data in each table MAXREQ=1000000 # maximum number of requests is 100W# when the duration or the maximum number of requests is reached This round of testing will stop # create a sysbench-specific database echo 'now create db'mysql-h$DBIP-P$DBPORT-u$DBUSER-p$DBPASSWD-e' create database sysbench'echo 'create ok'## data preparation echo' now prepare data' sysbench/ usr/share/sysbench/oltp_read_only.lua\ # you must attach a lua script to initialize the data-mysql-host=$DBIP\-- mysql-port=$DBPORT\-- mysql-user=$DBUSER\-- mysql- Password=$DBPASSWD\-mysql-db=$DBNAME\-db-driver=mysql\-tables=10\-table-size=$ROWS\-time=$DURING prepare

Pressure test begins

#! / bin/bash# Ye Jinrong, co-founder of Zhijitang training, senior MySQL expert, MySQL evangelist, Oracle MySQL ACE#### several precautions: # # 1. Clients running sysbench and MySQL DB CVM should not be on the same host, including the case of starting two virtual machines on one host; # # 2. The number of test tables should not be too small, requiring at least 20 tables. # # 3. The amount of data in each table should not be too small, usually requiring at least 10 million or more. Of course, it should be adjusted appropriately according to the configuration of the DB CVM. # # 4. The duration of each benchmark pressure test should not be too short, usually lasting more than 15 minutes; # # 5. After each round of testing, at least 5 minutes are suspended, or until the system load is fully restored to the idle state. # # 6. If the test DB CVM is dedicated, it cannot be mixed with other businesses, otherwise the test results will be unreliable. # # 7. The rest of the outstanding matters will be added later. # created by yejinrong@zhishutang.com## 2017 Acceler6Accord3According to sysbench Project address: https://github.com/akopytov/sysbench######################### 2018-02-23 Zhang Ruizhi # # teacher Ye's original script is only applicable to sysbench version 0.5 Sysbench cannot be used after upgrading to 1.0, now modify some syntax. Export LD_LIBRARY_PATH=/usr/local/mysql/lib/. When bash_profile# needs to enable DEBUG mode, remove the following three lines of comments # set-u#set-x#set-eBASEDIR= "/ data/sysbench" if [!-d $BASEDIR] then mkdir $BASEDIR-pficd $BASEDIR# to record all errors and standard output to exec 3 > & 14 > & 21 > sysbench.log 2 > & seconds DBIP=192.168 in sysbench.log. 1.109 DBNAME= 3109DBUSERTER data proxysql insert 1KW rows data MAXREQ=1000000 # maximum number of requests is 100W# when duration or maximum number of requests is reached, the maximum number of requests is 100W# when the duration or maximum number of requests is reached This round of testing will stop # and the number of threads of pressure testing will be adjusted according to the actual situation of the machine configuration. THERAD_NUMBER= "8 64 128" # initial times round=0# generally runs at least 3 rounds of testing I normally run more than 10 rounds of while [$round-lt 4] do# each round log location: rounddir=$BASEDIR/logs-round$ {round} mkdir-p ${rounddir} for thread in `echo "${THERAD_NUMBER}" `do# commonly available options: # oltp_read_only # read-only # oltp_read_write # read and write # oltp_update_non_index # No primary key update sysbench / usr/share/sysbench / oltp_read_only.lua\-- mysql-host=$DBIP\-- mysql-port=$DBPORT\-- mysql-user=$DBUSER\-- mysql-password=$DBPASSWD\-- mysql-db=$DBNAME\-- db-driver=mysql\-- tables=$TBLCNT\-- table-size=$ROWS\-- report-interval=$REPORT_INTERVAL\-- threads=$ {thread}\-- rand-type=uniform\ # data random type: uniform Uniform-- time=$DURING run > > ${rounddir} / sysbench_$ {thread} .logsleep 300 # pause 5 minutes between pressure tests for different threads doneround= `expr $round + 1 `sleep 300 # 5 minutes done between each round of pressure tests

After running, you can find the log of sysbench output in the default data directory.

3. Result analysis and drawing

You can directly read the summary given by the sysbench log, or you can graph the data of individual items 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: 10205Latency (ms): min: 3113.18 avg: 11303.55 Max: 24222.47 95th percentile: 16819.24 sum: 115352747.29Threads fairness: events (avg/stddev): 159.4531 execution time 1.51 execution time (avg/stddev): 1802.3867 Universe 1.0 I am using a virtual machine with very limited hardware resources The results of the stress test are a little poignant.

Install gnuplot for drawing, gnuplot requires a graphical environment, you can choose to install it on windows, or you can install a graphical interface on a pressurized client. Here you choose to install the graphical interface on the linux pressurized client.

Dnf-y install @ xfce-desktop # install graphical interface yum-y install gnuplot # install gnuplotgnuplot # enter the gnuplot terminal gnuplot > plot 'output/sysbench_8.log' using 9 w lines title' QPS' # using 5 represents the use of column 5 data to draw # the trend in the with lines definition diagram uses lines to represent the names of # title 'QPS' definition lines # use (comma) segmentation to draw multi-column data

The figure is as follows:

Observe the system information and database information during sysbench stress testing through other scripts (from "highly available MySQL")

#! / bin/sh# get global configuration parameters before starting # get cpu load,MySQL global information, InnoDB engine related information, thread information INTERVAL=5PREFIX=$INTERVAL-sec-statusRUNFILE=/root/runningmysql-e 'show global variables' > > mysql-variables# every five seconds by detecting the existence of / root/running file as a basis for obtaining information, you can delete this file at the end of the stress test to stop collecting while test-e $RUNFILE Do file=$ (date +% funding% H) sleep=$ (date +% s.% N | awk "{print $INTERVAL-(\ $1% $INTERVAL)}") sleep $sleep ts= "$(date +" TS% s% N% F% T ")" loadavg= "$(uptime)" # obtain cpu load echo "$ts $loadavg" > > $PREFIX- through the uptime command ${file}-status mysql-e "show global status" > > $PREFIX-$ {file}-status & # get MySQL global information echo "$ts $loadavg" > > $PREFIX-$ {file}-innodbstatus mysql-e "show engine innodbstatus\ G" > > $PREFIX-$ {file}-innodbstatus & # get engine information echo "$ts $loadavg" > > $PREFIX-$ {file}-processlist mysql-e "show full processlist\ G" > > $PREFIX -${file}-processlist & # get thread information echo $tsdoneecho Exiting because $RUNFILE not exist

Analyze the global information 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", ts,$3,$4,substr ($load, 1, length ($load)-1) } / Queries/ {printf fmt, ($2-Queries) / diff; Queries=$2}'"$@"

Operation mode: sh hi_anaylyze.sh 5-sec-status-2018-02-22_14_status > > 4plot.log (record the analyzed results in 4plot.log)

Also use gnupot for drawing analysis:

Gunplot > plot' 4plot 'using 5 with lines title' QPS', 4 with lines title 'load' # using 5 means to use the fifth column of data to map # the trend in the with lines definition chart uses lines to represent the names of # title' QPS' definition lines # use (comma) segmentation to draw multi-column data

(the two drawings have different data sources and are used only as examples. )

After reading this article on how to use sysbench to perform stress tests on MySQL, many readers will want to know more about it. For more industry information, you can follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.