Using sysbench for OLTP workload performance benchmark

Posted by Iceberg on September 7, 2021

Intro to Sysbench

sysbench is a scriptable multi-threaded benchmark tool based on LuaJIT. It is most frequently used for database benchmarks, but can also be used to create arbitrarily complex workloads that do not involve a database server.

sysbench comes with the following bundled benchmarks:

  • oltp_*.lua: a collection of OLTP-like database benchmarks
  • fileio: a filesystem-level benchmark
  • cpu: a simple CPU benchmark
  • memory: a memory access benchmark
  • threads: a thread-based scheduler benchmark
  • mutex: a POSIX mutex benchmark

Below is a description of typical test commands and their purpose:

  • prepare: performs preparative actions for those tests which need them, e.g. creating the necessary files on disk for the fileio test, or filling the test database for database benchmarks.
  • run: runs the actual test specified with the testname argument. This command is provided by all tests.
  • cleanup: removes temporary data after the test run in those tests which create one.
  • help: displays usage information for the test specified with the testname argument. This includes the full list of commands provided by the test, so it should be used to get the available commands.

Install sysbench on CentOS 7.5

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
$ cat /etc/centos-release
CentOS Linux release 7.5.1804 (Core)
$ uname -r
5.7.12-1.el7.elrepo.x86_64

$ curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
$ sudo yum -y install sysbench

$ sysbench --version
sysbench 1.0.20

$ sysbench --help
Usage:
  sysbench [options]... [testname] [command]

Commands implemented by most tests: prepare run cleanup help

General options:
  --threads=N                     number of threads to use [1]
  --events=N                      limit for total number of events [0]
  --time=N                        limit for total execution time in seconds [10]
  --forced-shutdown=STRING        number of seconds to wait after the --time limit before forcing shutdown, or 'off' to disable [off]
  --thread-stack-size=SIZE        size of stack per thread [64K]
  --rate=N                        average transactions rate. 0 for unlimited rate [0]
  --report-interval=N             periodically report intermediate statistics with a specified interval in seconds. 0 disables intermediate reports [0]
  --report-checkpoints=[LIST,...] dump full statistics and reset all counters at specified points in time. The argument is a list of comma-separated values representing the amount of time in seconds elapsed from start of test when report checkpoint(s) must be performed. Report checkpoints are off by default. []
  --debug[=on|off]                print more debugging info [off]
  --validate[=on|off]             perform validation checks where possible [off]
  --help[=on|off]                 print help and exit [off]
  --version[=on|off]              print version and exit [off]
  --config-file=FILENAME          File containing command line options
  --tx-rate=N                     deprecated alias for --rate [0]
  --max-requests=N                deprecated alias for --events [0]
  --max-time=N                    deprecated alias for --time [0]
  --num-threads=N                 deprecated alias for --threads [1]

Pseudo-Random Numbers 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:
  --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:

  --db-driver=STRING  specifies database driver to use ('help' to get list of available drivers) [mysql]
  --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:
  mysql - MySQL driver
  pgsql - PostgreSQL driver

mysql 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:
  --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:
  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.

$ ls -la /usr/share/sysbench/tests/include/oltp_legacy/
total 56
drwxr-xr-x 2 root root  284 Sep  7 20:53 .
drwxr-xr-x 3 root root 4096 Sep  7 20:53 ..
-rw-r--r-- 1 root root 1195 Apr 24  2020 bulk_insert.lua
-rw-r--r-- 1 root root 4696 Apr 24  2020 common.lua
-rw-r--r-- 1 root root  366 Apr 24  2020 delete.lua
-rw-r--r-- 1 root root 1171 Apr 24  2020 insert.lua
-rw-r--r-- 1 root root 3004 Apr 24  2020 oltp.lua
-rw-r--r-- 1 root root  368 Apr 24  2020 oltp_simple.lua
-rw-r--r-- 1 root root  527 Apr 24  2020 parallel_prepare.lua
-rw-r--r-- 1 root root  369 Apr 24  2020 select.lua
-rw-r--r-- 1 root root 1448 Apr 24  2020 select_random_points.lua
-rw-r--r-- 1 root root 1556 Apr 24  2020 select_random_ranges.lua
-rw-r--r-- 1 root root  369 Apr 24  2020 update_index.lua
-rw-r--r-- 1 root root  578 Apr 24  2020 update_non_index.lua

MariaDB vs. MySQL

MariaDB is a community-developed, commercially supported fork of the MySQL relational database management system (RDBMS), intended to remain free and open-source software under the GNU General Public License. Development is led by some of the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle Corporation in 2009. Refer to wiki for more information.

Create the MariaDB database

Provision the MariaDB docker instance

In this example, we use Portworx to manage the disk storage. A volume testVol is created to store MariaDB data.

1
2
3
4
5
6
7
$ pxctl v create testVol --size 1024 --repl 1

$ docker run --name mariadbtest -v testVol:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=password -p 3306:3306 -d docker.io/library/mariadb:latest

$ docker ps | egrep "CONTAINER|mariadbtest"
CONTAINER ID   IMAGE                        COMMAND                  CREATED          STATUS          PORTS                                       NAMES
2e5fe8ca177d   mariadb:latest               "docker-entrypoint.s…"   39 seconds ago   Up 37 seconds   0.0.0.0:3306->3306/tcp, :::3306->3306/tcp   mariadbtest

Create a database

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
$ docker exec -it mariadbtest bash

root@2e5fe8ca177d:/# ip a | grep eth
139: eth0@if140: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue state UP group default
    link/ether 02:42:ac:11:00:05 brd ff:ff:ff:ff:ff:ff link-netnsid 0
    inet 172.17.0.5/16 brd 172.17.255.255 scope global eth0

root@2e5fe8ca177d:/# df -h 
Filesystem                       Size  Used Avail Use% Mounted on
overlay                           50G   23G   28G  45% /
tmpfs                             64M     0   64M   0% /dev
tmpfs                            126G     0  126G   0% /sys/fs/cgroup
shm                               64M     0   64M   0% /dev/shm
/dev/mapper/centos-root           50G   23G   28G  45% /etc/hosts
/dev/pxd/pxd1020609855122786711 1007G  209M  956G   1% /var/lib/mysql
tmpfs                            126G     0  126G   0% /proc/acpi
tmpfs                            126G     0  126G   0% /proc/scsi
tmpfs                            126G     0  126G   0% /sys/firmware

root@2e5fe8ca177d:/# ls -la /var/lib/mysql
total 123332
drwxr-xr-x. 5 mysql mysql      4096 Sep  7 21:02 .
drwxr-xr-x  1 root  root         68 Aug 31 03:44 ..
-rw-rw----  1 mysql mysql    417792 Sep  7 21:02 aria_log.00000001
-rw-rw----  1 mysql mysql        52 Sep  7 21:02 aria_log_control
-rw-rw----  1 mysql mysql         9 Sep  7 21:02 ddl_recovery.log
-rw-rw----  1 mysql mysql       946 Sep  7 21:02 ib_buffer_pool
-rw-rw----  1 mysql mysql 100663296 Sep  7 21:02 ib_logfile0
-rw-rw----  1 mysql mysql  12582912 Sep  7 21:02 ibdata1
-rw-rw----  1 mysql mysql  12582912 Sep  7 21:02 ibtmp1
-rw-rw----  1 mysql mysql         0 Sep  7 21:00 multi-master.info
drwx------  2 mysql mysql      4096 Sep  7 21:00 mysql
drwx------  2 mysql mysql      4096 Sep  7 21:00 performance_schema
drwx------  2 mysql mysql     12288 Sep  7 21:00 sys

root@2e5fe8ca177d:/#  mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.6.4-MariaDB-1:10.6.4+maria~focal mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW VARIABLES LIKE "%version%";
+-----------------------------------+------------------------------------------+
| Variable_name                     | Value                                    |
+-----------------------------------+------------------------------------------+
| in_predicate_conversion_threshold | 1000                                     |
| innodb_version                    | 10.6.4                                   |
| protocol_version                  | 10                                       |
| slave_type_conversions            |                                          |
| system_versioning_alter_history   | ERROR                                    |
| system_versioning_asof            | DEFAULT                                  |
| tls_version                       | TLSv1.1,TLSv1.2,TLSv1.3                  |
| version                           | 10.6.4-MariaDB-1:10.6.4+maria~focal      |
| version_comment                   | mariadb.org binary distribution          |
| version_compile_machine           | x86_64                                   |
| version_compile_os                | debian-linux-gnu                         |
| version_malloc_library            | system                                   |
| version_source_revision           | 2db692f5b4d6bb31a331dab44544171c455f6aca |
| version_ssl_library               | OpenSSL 1.1.1f  31 Mar 2020              |
| wsrep_patch_version               | wsrep_26.22                              |
+-----------------------------------+------------------------------------------+
15 rows in set (0.002 sec)

MariaDB [(none)]> SHOW VARIABLES WHERE Variable_Name LIKE "%dir";
+---------------------------+----------------------------+
| Variable_name             | Value                      |
+---------------------------+----------------------------+
| aria_sync_log_dir         | NEWFILE                    |
| basedir                   | /usr                       |
| character_sets_dir        | /usr/share/mysql/charsets/ |
| datadir                   | /var/lib/mysql/            |
| innodb_data_home_dir      |                            |
| innodb_log_group_home_dir | ./                         |
| innodb_tmpdir             |                            |
| lc_messages_dir           | /usr/share/mysql           |
| plugin_dir                | /usr/lib/mysql/plugin/     |
| slave_load_tmpdir         | /tmp                       |
| tmpdir                    | /tmp                       |
| wsrep_data_home_dir       | /var/lib/mysql/            |
+---------------------------+----------------------------+
12 rows in set (0.002 sec)

MariaDB [(none)]> CREATE DATABASE sbtest;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> CREATE USER sbtest@localhost;
Query OK, 0 rows affected (0.004 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON sbtest.* TO sbtest@localhost;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> use sbtest;
Database changed

MariaDB [sbtest]> select database();
+------------+
| database() |
+------------+
| sbtest     |
+------------+
1 row in set (0.000 sec)

MariaDB [sbtest]> show tables;
Empty set (0.000 sec)

MariaDB [(none)]>  exit
Bye
root@2e5fe8ca177d:/# exit
exit

root@2e5fe8ca177d:/# ls -la /var/lib/mysql
total 123336
drwxr-xr-x. 6 mysql mysql      4096 Sep  7 21:07 .
drwxr-xr-x  1 root  root         68 Aug 31 03:44 ..
-rw-rw----  1 mysql mysql    417792 Sep  7 21:07 aria_log.00000001
-rw-rw----  1 mysql mysql        52 Sep  7 21:02 aria_log_control
-rw-rw----  1 mysql mysql         9 Sep  7 21:02 ddl_recovery.log
-rw-rw----  1 mysql mysql       946 Sep  7 21:02 ib_buffer_pool
-rw-rw----  1 mysql mysql 100663296 Sep  7 21:02 ib_logfile0
-rw-rw----  1 mysql mysql  12582912 Sep  7 21:02 ibdata1
-rw-rw----  1 mysql mysql  12582912 Sep  7 21:02 ibtmp1
-rw-rw----  1 mysql mysql         0 Sep  7 21:00 multi-master.info
drwx------  2 mysql mysql      4096 Sep  7 21:00 mysql
drwx------  2 mysql mysql      4096 Sep  7 21:00 performance_schema
drwx------  2 mysql mysql      4096 Sep  7 21:07 sbtest
drwx------  2 mysql mysql     12288 Sep  7 21:00 sys
root@2e5fe8ca177d:/# ls -la /var/lib/mysql/sbtest/
total 12
drwx------  2 mysql mysql 4096 Sep  7 21:07 .
drwxr-xr-x. 6 mysql mysql 4096 Sep  7 21:07 ..
-rw-rw----  1 mysql mysql   67 Sep  7 21:07 db.opt

Build the database

On the host, using sysbench to create tables and insert data rows in the database. We need know how much data should be created in the database. 1 million rows will result in ~240MB of data. So, 32 tables, 2 millions rows each create 15GB data.

1
2
3
4
5
6
7
$ sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --threads=1 --mysql-host=172.17.0.5 --mysql-password=password  --mysql-user=root --mysql-db=sbtest --oltp-tables-count=32 --oltp-table-size=2000000 prepare
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Creating table 'sbtest1'...
Inserting 2000000 records into 'sbtest1'
Creating secondary indexes on 'sbtest1'...
[omitted...]

In the MariaDB container, we can check the created data and table size.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
root@2e5fe8ca177d:/#  mysql -u root -p
MariaDB [sbtest]> select * from sbtest1 limit 6;
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k       | c                                                                                                                       | pad                                                         |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
|  1 |  998567 | 83868641912-28773972837-60736120486-75162659906-27563526494-20381887404-41576422241-93426793964-56405065102-33518432330 | 67847967377-48000963322-62604785301-91415491898-96926520291 |
|  2 | 1003937 | 38014276128-25250245652-62722561801-27818678124-24890218270-18312424692-92565570600-36243745486-21199862476-38576014630 | 23183251411-36241541236-31706421314-92007079971-60663066966 |
|  3 | 1008521 | 33973744704-80540844748-72700647445-87330233173-87249600839-07301471459-22846777364-58808996678-64607045326-48799346817 | 38615512647-91458489257-90681424432-95014675832-60408598704 |
|  4 | 1004027 | 37002370280-58842166667-00026392672-77506866252-09658311935-56926959306-83464667271-94685475868-28264244556-14550208498 | 63947013338-98809887124-59806726763-79831528812-45582457048 |
|  5 |  999625 | 44257470806-17967007152-32809666989-26174672567-29883439075-95767161284-94957565003-35708767253-53935174705-16168070783 | 34551750492-67990399350-81179284955-79299808058-21257255869 |
|  6 | 1001169 | 37216201353-39109531021-11197415756-87798784755-02463049870-83329763120-57551308766-61100580113-80090253566-30971527105 | 05161542529-00085727016-35134775864-52531204064-98744439797 |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
6 rows in set (0.004 sec)

MariaDB [sbtest]> SELECT   TABLE_NAME AS `Table`,   ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)` FROM   information_schema.TABLES WHERE   TABLE_SCHEMA = "sbtest" ORDER BY   (DATA_LENGTH + INDEX_LENGTH) DESC;
+---------+-----------+
| Table   | Size (MB) |
+---------+-----------+
| sbtest3 |       459 |
| sbtest1 |       459 |
| sbtest4 |       459 |
| sbtest2 |       459 |
| sbtest5 |       459 |
| sbtest6 |       146 |
+---------+-----------+
6 rows in set (0.002 sec)

MariaDB [sbtest]> SELECT table_schema "DB Name", ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"  FROM information_schema.tables  GROUP BY table_schema;
+--------------------+---------------+
| DB Name            | DB Size in MB |
+--------------------+---------------+
| information_schema |           0.2 |
| mysql              |          10.5 |
| performance_schema |           0.0 |
| sbtest             |       14702.0 |
| sys                |           0.0 |
+--------------------+---------------+
5 rows in set (0.033 sec)

We also can check the running process in the MariaDB.

1
2
3
4
5
6
7
8
MariaDB [sbtest]> show processlist;
+----+------+------------------+--------+---------+------+----------
| Id | User | Host             | db     | Command | Time | State    | Info                                                                                                 | Progress |
+----+------+------------------+--------+---------+------+----------
|  7 | root | 172.17.0.1:55000 | sbtest | Query   |    0 | Update   | INSERT INTO sbtest13(k, c, pad) VALUES(1185731, '26498931212-26730519067-66264645428-09623019003-787' |    0.000 |
| 11 | root | localhost        | sbtest | Query   |    0 | starting | show processlist                                                                                     |    0.000 |
+----+------+------------------+--------+---------+------+----------
2 rows in set (0.000 sec)

Run sysbench benchmark

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
$ threads=1; seconds=1800; interval=60
$ sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --threads=$threads --mysql-host=172.17.0.5 --mysql-password=password  --mysql-user=root  --oltp-tables-count=32 --oltp-table-size=2000000 --events=0 --time=$seconds --report-interval=$interval --delete_inserts=10 --index_updates=10 --non_index_updates=10 --db-ps-mode=disable run

SQL statistics:
    queries performed:
        read:                            1315888
        write:                           375968
        other:                           187984
        total:                           1879840
    transactions:                        93992  (52.22 per sec.)
    queries:                             1879840 (1044.35 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          1800.0066s
    total number of events:              93992

Latency (ms):
         min:                                    6.52
         avg:                                   19.14
         max:                                 1018.82
         95th percentile:                       25.28
         sum:                              1799473.52

Threads fairness:
    events (avg/stddev):           93992.0000/0.00
    execution time (avg/stddev):   1799.4735/0.00

Reference