Use sysbench for MySQL stress testing

Use sysbench for MySQL stress testing

1. Background

Percona is a multi-threaded system stress testing tool that can evaluate the system performance based on various factors that affect the performance of database servers. For example, it can be used to test file IO, operating system scheduler, memory allocation and transmission speed, POSIX Threads, and database servers. Sysbench supports the Lua scripting language. Lua can flexibly set various test scenarios. 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 bash
Sudo yum-y install sysbench

Usage:

Data Preparation

#! /Bin/sh
Export LD_LIBRARY_PATH =/usr/local/mysql/lib/

.~ /. Bash_profile

# Remove the following three lines of comments when you need to enable the DEBUG mode
# Set-u
# Set-x
# Set-e

BASEDIR = "/data/sysbench" # create a sysbench file directory
If [! -D $ BASEDIR]
Then
Mkdir $ BASEDIR-p
Fi
Cd $ BASEDIR # enter the sysbench file directory

# Record all errors and standard output to sysbench. log
# Exec 3> & 1 4> & 2 1> sysbench_prepare.log 2> & 1

DBIP = 192.168.1.109
DBPORT = 3109
DBUSER = 'xysql'
DBPASSWD = '000000'
NOW = 'date + '% Y % m % d % H % m''
DBNAME = "sysbench"
TBLCNT = 10 # number of tables
WARMUP = 300 # push time (seconds)
DURING = 1800 # duration (seconds)
ROWS = 10000000 # insert 1 kW ROWS of data into each table
MAXREQ = 1000000 # the maximum number of requests is 100

# When the duration or maximum number of requests is reached, this round of testing will stop

# Create a database dedicated to sysbench
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 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 started

#! /Bin/bash
##
##
# Ye Jinrong, co-founder of zhishutang training, Senior MySQL expert, MySQL evangelist, and Oracle MySQL ACE
##
# Notes:
#1. Try not to run the sysbench client and MySQL DB server on the same host, or start two virtual machines on one host;
#2. The number of test tables should not be too small. At least 20 tables are required;
#3. The data volume of each table should not be too small. Generally, the data volume must be at least million. Of course, you must adjust the data volume according to the configuration of the DB server;
#4. The duration of each benchmark stress test should not be too short. Generally, it must last for more than 15 minutes;
#5. after each round of testing is completed, pause for at least five minutes in the middle, or confirm that the system load has completely recovered to the dry run status;
#6. If the test DB server is dedicated, it cannot be mixed with other services; otherwise, the test results will be unreliable;
#7. Complete other matters and add them later.
##
# Created by yejinrong@zhishutang.com
# Instances /6/3
##
# Sysbench Project address: https://github.com/akopytov/sysbench
##
####################### 2018/02/23 sheets of record ############## ######################
### The original script of instructor Ye is only applicable to sysbench 0.5 and cannot be used after sysbench is upgraded to 1.0. Some syntaxes are modified.

Export LD_LIBRARY_PATH =/usr/local/mysql/lib/

.~ /. Bash_profile

# Remove the following three lines of comments when you need to enable the DEBUG mode
# Set-u
# Set-x
# Set-e

BASEDIR = "/data/sysbench"
If [! -D $ BASEDIR]
Then
Mkdir $ BASEDIR-p
Fi
Cd $ BASEDIR
# Clear previous legacy records
Rm-rf $ BASEDIR/logs *
# Record all errors and standard output to sysbench. log
Exec 3> & 1 4> & 2 1> sysbench. log 2> & 1

# Time unit: seconds
DBIP = 192.168.1.109
DBPORT = 3109
DBUSER = 'xysql'
DBPASSWD = '000000'
NOW = 'date + '% Y % m % d % H % m''
DBNAME = "sysbench"
REPORT_INTERVAL = 1
TBLCNT = 10 # number of tables
WARMUP = 300 # push time (seconds)
DURING = 1800 # duration (seconds)
ROWS = 10000000 # insert 1 kW ROWS of data into each table
MAXREQ = 1000000 # the maximum number of requests is 100

# When the duration or maximum number of requests is reached, this round of testing will stop

# Adjust the number of threads for concurrent stress testing based on the actual configuration of the machine
THERAD_NUMBER = "8 64 128"

# Initial times
Round = 0
# Generally, I run at least three rounds of tests. I normally run more than 10 rounds of tests.
While [$ round-lt 4]
Do
# Log location of each round:
Rounddir = $ BASEDIR/logs-round $ {round}
Mkdir-p $ {rounddir}

For thread in 'echo "$ {THERAD_NUMBER }"'
Do
# Common options:
# Oltp_read_only # Read-Only
# Oltp_read_write # both read and write
# Oltp_update_non_index # non-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 \ # random data type: uniform, uniform
-- Time = $ DURING run >$ {rounddir}/sysbench _ $ {thread}. log

Sleep 300 # Pause for 5 minutes between stress tests of different threads
Done

Round = 'expr $ round + 1'
Sleep 300 # Pause for 5 minutes during each stress test
Done

After running, you can find the logs output by sysbench In the preset data directory.

3. Result Analysis and drawing

You can directly read the summary provided by the sysbench log, or plot and observe the trend of the data of individual items.

SQL statistics:
Queries saved med:
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.6625 s
Total number of events: 10205

Latency (MS ):
Min: 3113.18
Avg: 11303.55.
Max: 24222.47
95th percentile: 16819.24
Sum: 115352747.29

Threads fairness:
Events (avg/stddev): 159.4531/1.51
Execution time (avg/stddev): 1802.3867/1.03
# I am using a virtual machine with limited hardware resources, and the stress test results are a bit confusing.

Install gnuplot for plotting. gnuplot requires a graphical environment. You can choose to install it on windows, or install a graphical interface on the client. Install the graphic interface on the linux pressure client.

Dnf-y install @ xfce-desktop # install graphical interface

Yum-y install gnuplot # install gnuplot

Gnuplot # enter the gnuplot Terminal

Gnuplot> plot 'output/sysbench_8.log 'using 9 w lines title 'qps'
# Using 5 indicates that 5th columns of data are used for plotting.
# With lines define the trend in the graph to be represented by a line
# Title 'qps 'defines the line name
# Use commas (,) to separate multiple columns of data

The figure is as follows: Read-Only pressure test QPS graphics

Observe the system information and database information during the sysbench stress test using other scripts (from high availability MySQL)

#! /Bin/sh
# Obtain global configuration parameters before starting
# Obtain cpu load, MySQL global information, InnoDB Engine information, and thread information every five seconds
INTERVAL = 5
PREFIX = $ INTERVAL-sec-status
RUNFILE =/root/running
Mysql-e 'show global variables '> mysql-variables

# By checking whether the/root/running file exists as the basis for obtaining information, you can delete the file at the end of the stress test to stop collection.
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)" # obtain the cpu load using the uptime command
Echo "$ ts $ loadavg" >>$ PREFIX-$ {file}-status
Mysql-e "show global status" >>$ PREFIX-$ {file}-status & # obtain MySQL global Information
Echo "$ ts $ loadavg" >>$ PREFIX-$ {file}-innodbstatus
Mysql-e "show engine innodb status \ G"> $ PREFIX-$ {file}-innodbstatus & # obtain engine information
Echo "$ ts $ loadavg" >>$ PREFIX-$ {file}-processlist
Mysql-e "show full processlist \ G"> $ PREFIX-$ {file}-processlist & # obtain thread Information
Echo $ ts
Done
Echo Exiting because $ RUNFILE not exist

Analyze the global information collected in the previous step.

#! /Bin/sh
Awk'
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", ts, $3, $4, substr ($ load, 1, length ($ load)-1 );
}
/Queries /{
Printf fmt, ($2-Queries)/diff;
Queries = $2
}
'"$ @"

Running Mode: sh hi_anaylyze.sh 5-sec-status-2012-2-22_14_status & gt; 4plot. log (record the analysis result to 4plot. log)

Similarly, gnupot is used for graph analysis:

Gunplot> plot '4plot' using 5 with lines title 'qpss ', 4 with lines title 'load'
# Using 5 indicates that 5th columns of data are used for plotting.
# With lines define the trend in the graph to be represented by a line
# Title 'qps 'defines the line name
# Use commas (,) to separate multiple columns of data

Gnuplot plot example

(The data sources of the two images are different and used only as an example .)

This article permanently updates link: https://www.bkjia.com/Linux/2018-02/151053.htm