Database performance test: Sysbench usage

1. Introduction and Installation

Sysbench is a very good database performance testing tool.

Official site: https://github.com/akopytov/sysbench/

RPM Package Download: https://packagecloud.io/akopytov/sysbench/packages/el/7/sysbench-1.0.15-1.el7.centos.x86_64.rpm

Source code package Download: https://github.com/akopytov/sysbench/archive/1.0.15.tar.gz

If you are compiling the installation, you will need to install the MySQL development package first (although the missing MySQL library file is prompted when compiling the error).

yum -y install mysql-community-develtar xf 1.0.15.tar.gzcd sysbench-1.0.15./autogen.sh./configuremake -jmake install

After installation, there is only one binary file Sysbench, and a number of LUA scripts are available.

[[email protected] ~]# rpm-ql sysbench | grep ' Bin\|lua '/usr/bin/sysbench/usr/share/sysbench/bulk_insert.lua/usr/share/sysbench/oltp_common.lua/usr/ Share/sysbench/oltp_delete.lua/usr/share/sysbench/oltp_insert.lua/usr/share/sysbench/oltp_point_select.lua/usr /share/sysbench/oltp_read_only.lua/usr/share/sysbench/oltp_read_write.lua/usr/share/sysbench/oltp_update_ Index.lua/usr/share/sysbench/oltp_update_non_index.lua/usr/share/sysbench/oltp_write_only.lua/usr/share/ Sysbench/select_random_points.lua/usr/share/sysbench/select_random_ranges.lua/usr/share/sysbench/tests/include /inspect.lua/usr/share/sysbench/tests/include/oltp_legacy/bulk_insert.lua/usr/share/sysbench/tests/include/ oltp_legacy/common.lua/usr/share/sysbench/tests/include/oltp_legacy/delete.lua/usr/share/sysbench/tests/ include/oltp_legacy/insert.lua/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua/usr/share/sysbench/tests/ Include/oltp_legacy/oltp_simple.lua/usr/share/sysbench/tests/include/oltp_legacy/parallel_prepare.lua/usr/share/sysbench/tests/include/oltp_legacy/select.lua/usr/share/sysbench/tests/include/oltp_legacy/ Select_random_points.lua/usr/share/sysbench/tests/include/oltp_legacy/select_random_ranges.lua/usr/share/ Sysbench/tests/include/oltp_legacy/update_index.lua/usr/share/sysbench/tests/include/oltp_legacy/update_non_ Index.lua

This article describes the use of the new version of the Sysbench OLTP Lua script ( /usr/share/sysbench/*.lua ), so it doesn't involve traditional Lua ( tests/include/oltp_legacy/*.lua ), and if you want to learn about the usage of these traditional LUA scripts, look it up online.

2.sysbench How to use

The following is a list of the options commonly used by Sysbench for testing MySQL.

[[email protected] ~]# sysbench--helpusage:sysbench [options] ... [Test_lua] [Lua_options] [Command]  Commands implemented by the Tests:prepare run Cleanup help Universal option: The values in brackets below indicate the default value--threads=n the specified number of threads [1]                    --events=n limit maximum number of requests, 0 means no limit [0]--time=n maximum execution time, 0 means no limit [10] --events and--time Select one to--forced-shutdown=string the maximum execution time and wait for how long to close sysbench off means disabling the function [off]--thread-stack-size=size the amount of stack space used per thread [64K]--rate=n average transaction rate, 0 means no limit [0]--report-i                     Nterval=n report results every few seconds, 0 means disable interval report [0]--config-file=filename Read command-line options from a file--tx-rate=n Deprecated, is--rate alias [0]--max-requests=n deprecated, is--events alias [0]--max-time=n obsolete, is--time alias [0          ]--num-threads=n obsolete, is--threads alias [1]mysql related 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-ignore-errors=[list,...] The error code to ignore, the value can be "all" [1213,1020 , 1205]compiled-in tests:fileio-file I/O test cpu-cpu performance test memory-memory functions Speed test thread S-threads Subsystem Performance Test Mutex-mutex performance test

Of these, the command section has 4 classes: Prepare run cleanup and help:

• prepare: The command to prepare the data. For example, before sysbench a stress test, you need to prepare the test library, the test table, and the data in the test table. See the following article for specific usage.
  
• run: Indicates a stress test.
  
• cleanup: Clears the data generated when testing.
  
• help: Outputs the help information for a given LUA script.

Test_lua is the Lua script you want to use, and if it's the RPM package installed Sysbench, these scripts are/usr/share/sysbench directories. For general database testing, it is sufficient to use only the LUA scripts associated with OLTP.

Options and lua_options are different, option is Sysbench, Lua_options is the Lua script option, lua_options should be placed behind Test_lua (not required, but recommended).

For example, to see the use of Oltp_common.lua, you can:

sysbench /usr/share/sysbench/oltp_common.lua help

3. Prepare test data

First create the required database sbtest for Sysbench (this is the library name that Sysbench uses by default and you must create a test library).

mysqladmin -h127.0.0.1 -uroot [email protected]! -P3306 create sbtest;

Then, prepare the tables used for the test, which are placed in the test library sbtest. The Lua script used here is /usr/share/sysbench/oltp_common.lua .

sysbench --mysql-host=127.0.0.1          --mysql-port=3306          --mysql-user=root          [email protected]!          /usr/share/sysbench/oltp_common.lua          --tables=10          --table_size=100000          prepare

--tables=10it represents the creation of 10 test tables, which represent the --table_size=100000 process of inserting 10W rows of data into each table, prepare indicating that this is the preparation number.

mysql> show tables from sbtest;+------------------+| Tables_in_sbtest |+------------------+| sbtest1          || sbtest10         || sbtest2          || sbtest3          || sbtest4          || sbtest5          || sbtest6          || sbtest7          || sbtest8          || sbtest9          |+------------------+mysql> select count(*) from sbtest.sbtest1;+----------+| count(*) |+----------+|   100000 |+----------+

If you want to clear these 10 tables, you can use the cleanup command.

sysbench --mysql-host=127.0.0.1          --mysql-port=3306          --mysql-user=root          [email protected]!          /usr/share/sysbench/oltp_common.lua          --tables=10          cleanup

4. Database Testing and Results analysis

A slightly modified statement that prepares the data before it can be tested.

It is important to note that the Lua script used earlier is oltp_common.lua a generic script that is called by other LUA scripts and cannot be tested directly.

So, I use the oltp_read_write.lua script to do the reading, writing 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.

sysbench --threads=4          --time=20          --report-interval=5          --mysql-host=127.0.0.1          --mysql-port=3306          --mysql-user=root          [email protected]!          /usr/share/sysbench/oltp_read_write.lua          --tables=10          --table_size=100000          run

The following are the results returned by the test:

Initializing Worker Threads ... Threads started!### #以下是每5秒返回一次的结果, statistics include: # # # threads, TPS (number of transactions per second), QPS (number of queries per second), # # of Reads/writes/other times per second, delay, errors per second, number of re-connects per second [5s] Thds  : 4 tps:130.16 qps:2606.30 (r/w/o:1824.51/520.66/261.13) Lat (ms,95%): 104.84 err/s: 0.00 RECONN/S: 0.00[10s] Thds:4 tps:126.74 qps:2539.17 (r/w/o:1778.17/507.52/253.47) Lat (ms,95%): 108.68 err/s: 0.00 RECONN/S: 0.00[15s] thds:4 TP s:136.54 qps:2736.34 (r/w/o:1915.25/548.01/273.07) Lat (ms,95%): 102.97 err/s: 0.00 RECONN/S: 0.00[20s] thds:4 TPS: 107.44 qps:2148.65 (r/w/o:1505.60/428.17/214.89) Lat (ms,95%): 132.49 err/s: 0.00 RECONN/S: 0.00SQL statistics:queri     Es performed:read:35098 # Number of read operations performed write:10028 # Number of write operations performed other:5014    # Number of other operations performed total:50140 transactions:2507 (124.29 per sec.)   # Average rate of execution transactions queries:50140 (2485.82 per sec.)    # The average number of queries per second that can be executed ignored errors:0 (0.00 per Sec.)     reconnects:0 (0.00 per Sec.) General Statistics:total Time:20.1694s # Overall consumption time total number of events:2507 # always please         Quantity (read, write, other) Latency (ms): min:2.32 avg:32.13                        max:575.78 95th percentile:118.92 # Sampling calculates the average delay sum: 80554.96Threads fairness:events (Avg/stddev): 626.7500/2.49 Execution Time (Avg/stddev): 20 .1387/0.04

5.cpu/io/Memory and other tests

Several test indicators are built into the sysbench.

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

Can direct help output test method. For example, FileIO test.

[[email protected] ~]# sysbench fileio helpsysbench 1.0.15 (using bundled Luajit 2.1.0-beta2) FileIO options:--file- Num=n number of files to create [+]--file-block-size=n block size to use with all IO operatio NS [16384]--file-total-size=size total size of files to create [2G]--file-test-mode=string test mode {SEQ WR, SEQREWR, Seqrd, RNDRD, Rndwr, RNDRW}--file-io-mode=string file operations mode {SYNC,ASYNC,MMAP} [sync]--f Ile-async-backlog=n number of asynchronous operatons to queue per thread [+]--file-extra-flags=[list,...] LIST of additional flags to use to open files {sync,dsync,direct} []--file-fsync-freq=n does Fsync () after this Numbe R of requests (0-don ' t use Fsync ()) [+]--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 sync hronization {fsync, FdatAsync} [Fsync]--file-merged-requests=n merge at more this number of IO requests if possible (0-don ' t merge) [0] --file-rw-ratio=n reads/writes ratio for combined test [1.5]

For example, create 5 files, a total of 1G, each file about 200M.

sysbench fileio --file-num=5 --file-total-size=1G prepare[[email protected] ~]# ls -lh test*-rw------- 1 root root 205M Jul  8 12:15 test_file.0-rw------- 1 root root 205M Jul  8 12:15 test_file.1-rw------- 1 root root 205M Jul  8 12:15 test_file.2-rw------- 1 root root 205M Jul  8 12:15 test_file.3-rw------- 1 root root 205M Jul  8 12:15 test_file.4

Then, run the test.

sysbench --events=5000          --threads=16          fileio          --file-num=5          --file-total-size=1G          --file-test-mode=rndrw          --file-fsync-freq=0          --file-block-size=16384          run

Results:

File operations:    reads/s:                      98.67    writes/s:                     66.85    fsyncs/s:                     6.26Throughput:     # 吞吐量    read, MiB/s:                  1.54  # 表示读的带宽    written, MiB/s:               1.04  # 表示读的带宽General statistics:    total time:                   12.7426s    total number of events:       2117Latency (ms):         min:                          0.00         avg:                         86.66         max:                       2919.41         95th percentile:            646.19         sum:                     183460.80Threads fairness:    events (avg/stddev):          132.3125/24.19    execution time (avg/stddev):  11.4663/1.09

Scale CPU Performance test:

[[email protected] ~]# sysbench cpu --threads=40 --events=10000 --cpu-max-prime=20000 runsysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)Running the test with following options:Number of threads: 40Initializing random number generator from current timePrime numbers limit: 20000Initializing worker threads...Threads started!CPU speed:    events per second:  2127.81General statistics:    total time:                          4.6986s    total number of events:              10000Latency (ms):         min:                                    1.72         avg:                                   18.16         max:                                  302.17         95th percentile:                      110.66         sum:                               181628.49Threads fairness:    events (avg/stddev):           250.0000/30.81    execution time (avg/stddev):   4.5407/0.10

Database performance test: Sysbench usage