Using sysbench for OLTP workload performance benchmark
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
$ 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.
$ 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
$ 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.
$ 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.
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.
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
$ 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