MySQL stress test tool use

1, Mysqlslap: MySQL's own stress testing tool

mysql slap is a benchmark tool of mysql. It can query data with simple syntax and is flexible and easy to use. It can simulate multiple clients sending query updates to the server at the same time. It provides performance test data and performance ratio of multiple engines. mysql slap provides intuitive verification basis for mysql performance before and after optimization. System operation and maintenance and DBA personnel should master some common stress testing tools, so as to accurately grasp the upper limit of user traffic and its compression resistance supported by online database.

1. Change the default maximum number of connections

Before pressure testing MySQL, you need to change the default maximum number of connections, as follows:

[root@mysql data]# vim /etc/my.cnf      #Edit Master profile
[mysqld]
   ............#Omit part of the content
max_connections=1024

As follows:

mysql> show variables like 'max_connections';     #View maximum connections
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1024  |
+-----------------+-------+
1 row in set (0.00 sec)

Pressure test:

[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.com --verbose

The above command test instructions: the simulation test is performed in two concurrent reads and writes, the first time is 100, the second time is 200, and the SQL script is generated automatically. The test table contains 20 init fields, 30
char fields, 2000 query requests per execution. The test engines are myisam and innodb. (many of the above options are default values, which 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 for the first time, 100 clients of Myisam initiate 0.557/s of additional query at the same time, for the second time, 200 clients initiate 0.522/s of additional query at the same time, InnoDB initiate 0.256/s of additional query at the first time, and for the second time, 200 clients initiate 0.303/s of additional query at the same time.

According to the actual needs, we can slightly increase the number of concurrent stress tests.

2, Stress testing with third-party sysbench tools

1. Install sysbench tools

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

sysbench can perform the following tests:

  • CPU operation performance test
  • Disk IO performance test
  • Scheduler performance test
  • Memory allocation and transmission speed test
  • POSIX thread performance test
  • Database performance test (OLTP benchmark, which needs to be executed through Lua script in / usr/share/sysbench / directory, for example, OLTP read only.lua script)
  • sysbench can also customize tests by specifying its own Lua scripts when running commands.

2. View help options for the sysbench tool

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

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

General options:            # General options
  --threads=N                     Number of threads to use, 1 by default [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 after the time limit arrives and before the forced shutdown. 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 transmission rate. 0 is unlimited [0]
  --report-interval=N             Periodic reports in seconds intermediate statistics with specified interval 0 disable intermediate reports [0]
  --report-checkpoints=[LIST,...] Dumps full statistics and resets all counters at the specified point in time. The parameter is a comma separated list of values indicating that a report checkpoint (in seconds) must be executed when the amount of time elapses from the beginning of the test. Report checkpoints are off by default. []
  --debug[=on|off]                Print more debug information [off]
  --validate[=on|off]             Perform verification checks as much as possible [off]
  --help[=on|off]                 Display help and exit [off]
  --version[=on|off]              Display version information and exit [off]
  --config-file=FILENAME          File containing command line options
  --tx-rate=N                     Discard, replace --rate [0]
  --max-requests=N                Discard, switch to --events [0]
  --max-time=N                    Discard, replace --time [0]
  --num-threads=N                 Discard, replace --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:   # The built-in database driver supports MySQL and PostgreSQL by default
  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 type
  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 testing MySQL database performance

1) Prepare test data

#View the usage of lua script in sysbench
[root@mysql ~]# sysbench /usr/share/sysbench/oltp_common.lua help
#The sbtest library must be created, and the default library name of the sbtest event sysbench
[root@mysql ~]# mysqladmin -uroot -p123.com 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=127.0.0.1 \
> --mysql-port=3306 \
> --mysql-user=root \
> --mysql-password=123.com \
> /usr/share/sysbench/oltp_common.lua \
> --tables=10 \
> --table_size=100000 \
> prepare
#Where -- tables=10 means to create 10 test tables,
#--table_size=100000 indicates that 10W rows of data are inserted into each table,
#prepare means that this is the process of preparing numbers.

2) Confirm test data to exist

[root@mysql ~]# mysql -uroot -p123.com sbtest;      #Log in 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 100000 pieces of data
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.01 sec)

3) Database test and result analysis

Modify the statement that prepared the data before a little, and it can be used for testing.

It should be noted that the Lua script used before is oltp_common.lua, which is a general script and called by other Lua scripts. It cannot be directly used for testing.

So, I use the OLTP ﹣ read ﹣ write.lua script here to do the read and write tests. There are many other types of tests, such as read-only tests, write only tests, delete tests, mass insert tests, and so on. You can find the corresponding Lua script to call.

#Execute the test command as follows:
[root@mysql ~]# sysbench --threads=4 \
> --time=20 \
> --report-interval=5 \
> --mysql-host=127.0.0.1 \
> --mysql-port=3306 \
> --mysql-user=root \
> --mysql-password=123.com \
> /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=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123.com /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:
# Threads, TPS (transactions per second), QPS (queries per second)
# Read / write / other per second, latency, errors per second, reconnections per second
[ 5s ] thds: 4 tps: 675.29 qps: 13513.94 (r/w/o: 9460.62/2701.95/1351.37) lat (ms,95%): 8.58 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 4 tps: 679.05 qps: 13586.79 (r/w/o: 9511.29/2717.40/1358.10) lat (ms,95%): 8.58 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 4 tps: 676.82 qps: 13538.23 (r/w/o: 9476.70/2707.69/1353.84) lat (ms,95%): 8.74 err/s: 0.20 reconn/s: 0.00
[ 20s ] thds: 4 tps: 684.62 qps: 13692.11 (r/w/o: 9585.02/2737.86/1369.23) lat (ms,95%): 8.43 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            190190   # Number of read operations performed
        write:                           54337    # Number of write operations performed
        other:                           27169    # Number of other operations performed
        total:                           271696
    transactions:                        13584  (678.88 per sec.)    # Average rate of transactions executed
    queries:                             271696 (13578.35 per sec.)   # How many queries can be executed per second on average
    ignored errors:                      1      (0.05 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          20.0084s    # Total elapsed time
    total number of events:              13584    # Total requests (read, write, other)

Latency (ms):
         min:                                    2.68
         avg:                                    5.89
         max:                                   38.21
         95th percentile:                        8.58    # Average delay of sampling calculation

         sum:                                79985.65

Threads fairness:
    events (avg/stddev):           3396.0000/60.84
    execution time (avg/stddev):   19.9964/0.00

4. cpu/io / memory and other tests

Several built-in test indicators of sysbench are as follows:

[root@mysql ~]# sysbench --help
      ..........  # Omit part of the 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, for example, fileio test:

[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 5 files, 2G in total, and each file is about 400M.

[root@mysql ~]# sysbench fileio --file-num=5 --file-total-size=2G prepare
[root@mysql ~]# ll -lh test*
-rw------- 1 root root 410M 1 Month 218:47 test_file.0
-rw------- 1 root root 410M 1 Month 218:47 test_file.1
-rw------- 1 root root 410M 1 Month 218:48 test_file.2
-rw------- 1 root root 410M 1 Month 218:48 test_file.3
-rw------- 1 root root 410M 1 Month 218:48 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:

Initializing worker threads...

Threads started!

File operations:
    reads/s:                      6374.88
    writes/s:                     4271.21
    fsyncs/s:                     170.34

Throughput:           #throughput
    read, MiB/s:                  99.61          #Indicates read bandwidth
    written, MiB/s:               66.74             #Indicates the write bandwidth

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

Latency (ms):
         min:                                    0.00
         avg:                                    0.99
         max:                                   50.64
         95th percentile:                        4.74
         sum:                                 4940.23

Threads fairness:
    events (avg/stddev):           312.5000/15.32
    execution time (avg/stddev):   0.3088/0.01

2) Test cpu performance

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

————————Thank you for reading————————

Tags: MySQL Database PostgreSQL SQL

Posted on Mon, 06 Jan 2020 08:01:44 -0800 by mingo