MySQL Pressure Test Tool

Mysqlslap: MySQL's own stress testing tool
mysqlslap is the benchmark tool that comes with mysql. It queries data, has simple syntax, is flexible and easy to use. It can simulate multiple clients concurrently sending query updates to the server, gives performance test data, and provides performance comparison of various engines.mysqlslap provides an intuitive validation basis for MySQL performance optimization before and after. System operations and DBA personnel should master some common pressure testing tools to accurately grasp the upper limit of user traffic and its pressure resistance supported by online database.
1. Change its default maximum number of connections
Before you can stress test MySQL, you need to change its default maximum number of connections as follows:

[root@mysql ~]# vim /etc/my.cnf 
[root@mysql ~]# systemctl restart mysqld
#View Maximum Connections
mysql> show variables like 'max_connections';       
| Variable_name   | Value |
| max_connections | 1024  |
1 row in set (0.00 sec)

Conduct stress tests:

[root@mysql ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100,200 --iterations=1 --number-int-cols=20 --number-char-cols=30 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=myisam,innodb --number-of-queries=2000 -uroot -p123 --verbose

The above command test instructions: the simulation test reads and writes concurrently twice, the first 100, the second 200, automatically generates the SQL script, the test table contains 20 init fields, 30
char fields, executing 2000 query requests each time.The test engines are myisam and innodb, respectively.(Many of the above options are default values and can be omitted. If you want to understand the explanation of each option, you can use mysqlslap --help to query.)
The above command returns the following results:

The test results show that the first 100 clients of Myisam initiate an increase of 0.557/s simultaneously, the second 200 clients initiate an increase of 0.522/s Innodb the first 100 clients initiate an increase of 0.256/s simultaneously, and the second 200 clients initiate an increase of 0.303/s simultaneously.

Pressure testing can be done by increasing the number of concurrencies a little bit, depending on your actual needs.
2. Use third-party sysbench tools for pressure testing
1. Install sysbench tools

[root@mysql ~]# yum -y install epel-release     #Install third-party epel source
[root@mysql ~]# yum -y install sysbench            #Install sysbench tool
[root@mysql ~]# sysbench --version           #Make sure the tool is installed
sysbench 1.0.17

sysbench can perform the following tests:

  • CPU Operational Performance Test
  • Disk IO Performance Test
  • Dispatcher Performance Test
  • Memory Allocation and Transfer Speed Test
  • POSIX-ray performance test
  • Database performance test (OLTP benchmark, which needs to be executed through Lua scripts in / usr/share/sysbench/directory, such as oltp_Read_Only.luaScript executes read-only tests)
  • sysbench can also customize tests by specifying its own Lua script when running the command.

2. View help options for sysbench tools

[root@mysql ~]# sysbench --help
  sysbench [options]... [testname] [command]

Commands implemented by most tests: prepare run cleanup help # Available commands, four

General options:            # General Options
  --threads=N                     Number of threads to use, default 1 [1]
  --events=N                      Maximum number of events allowed [0]
  --time=N                        Maximum total execution time in seconds [10]
  --forced-shutdown=STRING        stay --time The number of seconds to wait before forcing the shutdown after the time limit arrives, default " off"Disable ( number of seconds to wait after the --time limit before forcing shutdown, or 'off' to disable) [off]
  --thread-stack-size=SIZE        Stack size per thread [64K]
  --rate=N                        Average transfer rate.0 unrestricted [0]
  --report-interval=N             Periodic report in seconds Intermediate statistics 0 with specified interval Disable Intermediate reporting [0]
  --report-checkpoints=[LIST,...] Dumps complete statistics and resets all counters at a specified point in time.A parameter is a comma-separated list of values indicating that a report checkpoint (in seconds) must be executed when this amount of time elapses from the beginning of the test.Report checkpoints are turned off by default. []
  --debug[=on|off]                Print More debug information [off]
  --validate[=on|off]             Perform validation checks whenever possible [off]
  --help[=on|off]                 Display help information and exit [off]
  --version[=on|off]              Display version information and exit [off]
  --config-file=FILENAME          Files containing command line options
  --tx-rate=N                     Abandon, use instead --rate [0]
  --max-requests=N                Abandon, use instead --events [0]
  --max-time=N                    Abandon, use instead --time [0]
  --num-threads=N                 Abandon, use instead --threads [1]

Pseudo-Random Numbers Generator options:    # Pseudo-Random Number Generator Options
  --rand-type=STRING random numbers distribution {uniform,gaussian,special,pareto} [special]
  --rand-spec-iter=N number of iterations used for numbers generation [12]
  --rand-spec-pct=N  percentage of values to be treated as 'special' (for special distribution) [1]
  --rand-spec-res=N  percentage of 'special' values to use (for special distribution) [75]
  --rand-seed=N      seed for random number generator. When 0, the current time is used as a RNG seed. [0]
  --rand-pareto-h=N  parameter h for pareto distribution [0.2]

Log options:    # log option
  --verbosity=N verbosity level {5 - debug, 0 - only critical messages} [3]

  --percentile=N       percentile to calculate in latency statistics (1-100). Use the special value of 0 to disable percentile calculations [95]
  --histogram[=on|off] print latency histogram in report [off]

General database options:   # Common Database Options

  --db-driver=STRING  Specify the database driver to use ('help' to get list of available drivers)
  --db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto]
  --db-debug[=on|off] print database-specific debug information [off]

Compiled-in database drivers:   # Built-in database driver, default support for MySQL and PostgreSQL
  mysql - MySQL driver
  pgsql - PostgreSQL driver

mysql options:              # MySQL database-specific options
  --mysql-host=[LIST,...]          MySQL server host [localhost]
  --mysql-port=[LIST,...]          MySQL server port [3306]
  --mysql-socket=[LIST,...]        MySQL socket
  --mysql-user=STRING              MySQL user [sbtest]
  --mysql-password=STRING          MySQL password []
  --mysql-db=STRING                MySQL database name [sbtest]
  --mysql-ssl[=on|off]             use SSL connections, if available in the client library [off]
  --mysql-ssl-cipher=STRING        use specific cipher for SSL connections []
  --mysql-compression[=on|off]     use compression, if available in the client library [off]
  --mysql-debug[=on|off]           trace all client library calls [off]
  --mysql-ignore-errors=[LIST,...] list of errors to ignore, or "all" [1213,1020,1205]
  --mysql-dry-run[=on|off]         Dry run, pretend that all MySQL client API calls are successful without executing them [off]

pgsql options:              # PostgreSQL database-specific options
  --pgsql-host=STRING     PostgreSQL server host [localhost]
  --pgsql-port=N          PostgreSQL server port [5432]
  --pgsql-user=STRING     PostgreSQL user [sbtest]
  --pgsql-password=STRING PostgreSQL password []
  --pgsql-db=STRING       PostgreSQL database name [sbtest]

Compiled-in tests:          # Built-in test types
  fileio - File I/O test
  cpu - CPU performance test
  memory - Memory functions speed test
  threads - Threads subsystem performance test
  mutex - Mutex performance test

See 'sysbench <testname> help' for a list of options for each test.

3. sysbench tests MySQL database performance
1) Prepare test data

#View how to use the lua script that comes with sysbench
[root@mysql ~]# sysbench /usr/share/sysbench/oltp_common.lua help
#Sbtest library must be created, the name of the library used by default for sbtest transaction sysbench
[root@mysql ~]# mysqladmin -uroot -p123 create sbtest;
#Then, prepare the tables used for the tests, which are placed in the test library sbtest.The Lua script used here is/usr/share/sysbench/oltp_common.lua
[root@mysql ~]# sysbench --mysql-host= \
> --mysql-port=3306 \
> --mysql-user=root \
> --mysql-password=123 \
> /usr/share/sysbench/oltp_common.lua \
> --tables=10 \
> --table_size=100000 \
> prepare
#Where--tables=10 means ten test tables are created,
#--table_size=100000 means 10W rows of data are inserted into each table.
#prepare indicates that this is the process of preparing numbers.

2) Confirm that test data exists

[root@mysql ~]# mysql -uroot  -p123 sbtest;        #Log on to sbtest Library
mysql> show tables;           #View the corresponding table
| Tables_in_sbtest |
| sbtest1          |
| sbtest10         |
| sbtest2          |
| sbtest3          |
| sbtest4          |
| sbtest5          |
| sbtest6          |
| sbtest7          |
| sbtest8          |
| sbtest9          |
10 rows in set (0.00 sec)

mysql> select count(*) from sbtest1;        #Randomly select a table and confirm that it has 100,000 pieces of data
| count(*) |
|   100000 |
1 row in set (0.01 sec)

3) Database testing and result analysis
Modify the statement that you prepared the data before to take it to the test.

Note that the previously used Lua script is oltp_common.lua, which is a generic script called by other Lua scripts, cannot be directly used for testing.

So I'm using oltp_hereRead_Write.luaScripts do read and write tests.There are many other types of tests, such as read-only tests, write-only tests, delete tests, bulk insert tests, and so on.You can find the corresponding lua script to make the call.

#Execute the following test commands:
[root@mysql ~]# sysbench --threads=4 \
> --time=20 \
>  --report-interval=5 \
>  --mysql-host= \
> --mysql-port=3306 \
> --mysql-user=root \
> --mysql-password=123 \
> /usr/share/sysbench/oltp_read_write.lua \
> --tables=10 \
>  --table_size=100000 \
> run

The results returned by the above command are as follows:

[root@mysql ~]# sysbench --threads=4 --time=20  --report-interval=5  --mysql-host= --mysql-port=3306 --mysql-user=root --mysql-password=123 /usr/share/sysbench/oltp_read_write.lua --tables=10  --table_size=100000 run

sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 4
Report intermediate results every 5 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!
#The following results are returned every 5 seconds, and the statistical indicators include:
# Number of threads, TPS (transactions per second), QPS (queries per second),
# Read/write/other times per second, latency, number of errors per second, number of reconnections per second
[ 5s ] thds: 4 tps: 1040.21 qps: 20815.65 (r/w/o: 14573.17/4161.25/2081.22) lat (ms,95%): 7.17 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 4 tps: 1083.34 qps: 21667.15 (r/w/o: 15165.93/4334.55/2166.68) lat (ms,95%): 6.55 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 4 tps: 1121.57 qps: 22429.09 (r/w/o: 15700.64/4485.30/2243.15) lat (ms,95%): 6.55 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 4 tps: 1141.69 qps: 22831.98 (r/w/o: 15982.65/4566.16/2283.18) lat (ms,95%): 6.09 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            307146         # Number of read operations performed
        write:                           87756            # Number of write operations performed
        other:                           43878          # Number of other operations performed
        total:                           438780
    transactions:                        21939  (1096.57 per sec.)       # Average rate of transactions performed
    queries:                             438780 (21931.37 per sec.)        # How many queries can be executed per second on average
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          20.0055s           # Total consumption time
    total number of events:              21939          # Total number of requests (read, write, other)

Latency (ms):
         min:                                    1.39
         avg:                                    3.64
         max:                                  192.05
         95th percentile:                        6.67          # Average delay in sample calculation
         sum:                                79964.26

Threads fairness:
    events (avg/stddev):           5484.7500/15.12
    execution time (avg/stddev):   19.9911/0.00

4. Testing cpu/io/memory, etc.
Several test metrics built into sysbench are as follows:

[root@mysql ~]# sysbench --help
      ..........  # Omit some content
Compiled-in tests:
  fileio - File I/O test
  cpu - CPU performance test
  memory - Memory functions speed test
  threads - Threads subsystem performance test
  mutex - Mutex performance test

You can directly help output test methods, such as fileio tests:

[root@mysql ~]# sysbench fileio help
sysbench 1.0.17 (using system LuaJIT 2.0.4)

fileio options:
  --file-num=N                  number of files to create [128]
  --file-block-size=N           block size to use in all IO operations [16384]
  --file-total-size=SIZE        total size of files to create [2G]
  --file-test-mode=STRING       test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw}
  --file-io-mode=STRING         file operations mode {sync,async,mmap} [sync]
  --file-async-backlog=N        number of asynchronous operatons to queue per thread [128]
  --file-extra-flags=[LIST,...] list of additional flags to use to open files {sync,dsync,direct} []
  --file-fsync-freq=N           do fsync() after this number of requests (0 - don't use fsync()) [100]
  --file-fsync-all[=on|off]     do fsync() after each write operation [off]
  --file-fsync-end[=on|off]     do fsync() at the end of test [on]
  --file-fsync-mode=STRING      which method to use for synchronization {fsync, fdatasync} [fsync]
  --file-merged-requests=N      merge at most this number of IO requests if possible (0 - don't merge) [0]
  --file-rw-ratio=N             reads/writes ratio for combined test [1.5]

1) Test io performance
For example, create five files, totaling 2G, with each file roughly 400M.

[root@mysql ~]# sysbench fileio --file-num=5 --file-total-size=2G prepare
[root@mysql ~]# ll -lh test*
-rw------- 1 root root 410M May 26 16:05 test_file.0
-rw------- 1 root root 410M May 26 16:05 test_file.1
-rw------- 1 root root 410M May 26 16:05 test_file.2
-rw------- 1 root root 410M May 26 16:05 test_file.3
-rw------- 1 root root 410M May 26 16:05 test_file.4

Then run the test:

[root@mysql ~]# sysbench --events=5000 \
> --threads=16 \
> fileio \
> --file-num=5 \
> --file-total-size=2G \
> --file-test-mode=rndrw \
> --file-fsync-freq=0 \
> --file-block-size=16384 \
> run

The results returned are as follows:

Running the test with following options:
Number of threads: 16
Initializing random number generator from current time

Extra file open flags: (none)
5 files, 409.6MiB each
2GiB total file size
Block size 16KiB
Number of IO requests: 5000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random r/w test
Initializing worker threads...

Threads started!

File operations:
    reads/s:                      9899.03
    writes/s:                     6621.38
    fsyncs/s:                     264.33

Throughput:               # throughput
    read, MiB/s:                  154.66      #Represents read bandwidth
    written, MiB/s:               103.46     #Representation Write Bandwidth

General statistics:
    total time:                          0.3014s
    total number of events:              5000

Latency (ms):
         min:                                    0.00
         avg:                                    0.81
         max:                                   53.56
         95th percentile:                        4.10
         sum:                                 4030.48

Threads fairness:
    events (avg/stddev):           312.5000/27.64
    execution time (avg/stddev):   0.2519/0.02

2) Testing cpu performance

[root@mysql ~]# sysbench cpu --threads=40 --events=10000 --cpu-max-prime=20000 run

Tags: MySQL Database PostgreSQL SQL

Posted on Tue, 26 May 2020 09:29:37 -0700 by Bramme