Using MySQL’s mysqlslap and Sysbench for Database Stress Testing

The author introduces two popular tools for MySQL stress testing: the native mysqlslap utility and the external sysbench framework, and explains why DBAs should be familiar with them to assess database scalability and resilience.

1. mysqlslap – a simple benchmark that can simulate concurrent clients. Before running it, the maximum connection limit is increased:

[root@mysql ~]# vim /etc/my.cnf
...
[mysqld]
max_connections=1024
[root@mysql ~]# systemctl restart mysqld

The new limit can be verified:

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

Running a typical test with mixed read/write queries on both MyISAM and InnoDB engines:

[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 output shows query rates for each engine (e.g., MyISAM 0.557 /s at 100 clients, 0.522 /s at 200 clients; InnoDB 0.256 /s at 100 clients, 0.303 /s at 200 clients), allowing incremental increase of concurrency to find the system’s limits.

2. Sysbench – a versatile benchmarking suite that can test CPU, I/O, memory, and database workloads.

Installation on a CentOS host:

[root@mysql ~]# yum -y install epel-release   # third‑party repo
[root@mysql ~]# yum -y install sysbench       # install sysbench
[root@mysql ~]# sysbench --version
sysbench 1.0.17

Sysbench provides many built‑in tests; the help output lists options for each test (CPU, file I/O, memory, threads, mutex, etc.). For database benchmarking, the oltp_common.lua script is used to prepare data:

# Show usage of the bundled Lua script
[root@mysql ~]# sysbench /usr/share/sysbench/oltp_common.lua help
# Create the default test database
[root@mysql ~]# mysqladmin -uroot -p123 create sbtest;
# Prepare tables (10 tables, 100 000 rows each)
[root@mysql ~]# sysbench --mysql-host=127.0.0.1 \
    --mysql-port=3306 --mysql-user=root --mysql-password=123 \
    /usr/share/sysbench/oltp_common.lua \
    --tables=10 --table_size=100000 prepare

Verification of the prepared data:

[root@mysql ~]# mysql -uroot -p123 sbtest;
mysql> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| sbtest1          |
| ...              |
+------------------+
10 rows in set (0.00 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 100000   |
+----------+
1 row in set (0.01 sec)

Running a read/write OLTP test with the oltp_read_write.lua script:

[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 \
    /usr/share/sysbench/oltp_read_write.lua \
    --tables=10 --table_size=100000 run

The result (truncated) shows per‑second transaction rates (tps), query rates (qps), read/write counts, latency percentiles, and overall statistics such as total queries (438 780), average latency (≈3.6 ms), and fairness metrics.

Additional sysbench tests demonstrate CPU and I/O benchmarking. A CPU prime‑number test:

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

File‑I/O testing includes preparing five 2 GB files and running a random read/write workload:

# Prepare files
[root@mysql ~]# sysbench fileio --file-num=5 --file-total-size=2G prepare
# 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 output reports reads/s, writes/s, throughput in MiB/s, total execution time (0.30 s), and latency statistics (average 0.81 ms, 95th percentile 4.10 ms).

Finally, the author warns readers to perform stress tests carefully to avoid unintended impact on production systems.