How to Stress Test MySQL with mysqlslap and Sysbench: A Step‑by‑Step Guide

Using MySQL’s Built‑in mysqlslap Tool

mysqlslap is a simple benchmark utility that can simulate multiple concurrent clients issuing queries and updates, allowing you to compare the performance of different storage engines. Before running tests, increase the server’s max_connections setting, for example to 1024, and restart MySQL.

# vim /etc/my.cnf
[mysqld]
max_connections=1024
# systemctl restart mysqld

Verify the new limit:

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1024  |
+-----------------+-------+

Run a basic stress test that generates SQL automatically, uses 20 integer columns and 30 character columns, and executes 2000 queries on both MyISAM and InnoDB engines with concurrency levels of 100 and 200:

# 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

Typical results show MyISAM handling about 0.55‑0.52 queries per second and InnoDB about 0.26‑0.30 queries per second at the two concurrency levels; you can increase the concurrency gradually to find the system’s limits.

Benchmarking with the Third‑Party sysbench Tool

Install sysbench from the EPEL repository:

# yum -y install epel-release
# yum -y install sysbench
# sysbench --version   # should output 1.0.17

sysbench can test CPU, disk I/O, scheduler, memory, POSIX threads, and database OLTP workloads. Use the built‑in --help to explore options.

Preparing MySQL Data for OLTP Tests

Create a test database and populate it with tables using the supplied Lua script oltp_common.lua:

# sysbench /usr/share/sysbench/oltp_common.lua help
# mysqladmin -uroot -p123 create sbtest;
# 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

Verify the tables and row counts:

# mysql -uroot -p123 sbtest
mysql> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| sbtest1          |
| ...              |
+------------------+
mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 100000   |
+----------+

Running a Read/Write OLTP Benchmark

Execute the test with four threads for 20 seconds, reporting intermediate results every 5 seconds:

# 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 output includes TPS (transactions per second), QPS (queries per second), read/write counts, latency percentiles, and error/reconnect rates. Example excerpt:

[ 5s ] thds: 4 tps: 1040.21 qps: 20815.65 (r/w/o: 14573.17/4161.25/2081.22) lat (ms,95%): 7.17 err/s: 0.00 reconn/s: 0.00
[10s ] thds: 4 tps: 1083.34 qps: 21667.15 (r/w/o: 15165.93/4334.55/2166.68) lat (ms,95%): 6.55 ...

Final statistics show total transactions, average query rate, total time, and latency distribution (min, avg, max, 95th percentile).

Additional sysbench Tests

File I/O test: # sysbench fileio --file-num=5 --file-total-size=2G prepare then

# 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

CPU test:

# sysbench cpu --threads=40 --events=10000 --cpu-max-prime=20000 run

When performing stress tests, always monitor system resources and avoid running benchmarks on production environments without proper safeguards.