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

1. MySQL’s Built‑in Stress Test Tool: mysqlslap

mysqlslap is MySQL’s built‑in benchmark utility. It can simulate multiple concurrent clients issuing queries or updates, generate performance data, and compare different storage engines. It helps DBAs verify the impact of optimizations and determine the maximum traffic a production MySQL instance can handle.

Change the default max connections

Before testing, increase the server’s max_connections setting, e.g. to 1024, and restart MySQL.

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

Verify the new value:

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

Run a mysqlslap test

# 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 command simulates two runs (100 and 200 concurrent clients), auto‑generates SQL with 20 integer columns and 30 character columns, executes 2000 queries, and tests both MyISAM and InnoDB engines.

MyISAM: 0.557 /s (100 clients) → 0.522 /s (200 clients)

InnoDB: 0.256 /s (100 clients) → 0.303 /s (200 clients)

Increase the concurrency gradually to find the system’s pressure limits.

2. Using the Third‑Party Tool sysbench

Install sysbench

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

What sysbench can test

CPU performance

Disk I/O performance

Scheduler performance

Memory allocation and transfer speed

POSIX thread performance

It also provides OLTP benchmarks via Lua scripts such as oltp_read_write.lua.

Prepare test data for MySQL

# 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> show tables;
... sbtest1 ... sbtest10 ...
mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 100000   |
+----------+

Run an OLTP read/write benchmark

# 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

Sample output (excerpt):

[ 5s ] thds: 4 tps: 1040.21 qps: 20815.65 (r/w/o: 14573.17/4161.25/2081.22) lat (ms,95%): 7.17
[10s] thds: 4 tps: 1083.34 qps: 21667.15 (r/w/o: 15165.93/4334.55/2166.68) lat (ms,95%): 6.55
queries performed:
read: 307146
write: 87756
other: 43878
total: 438780
transactions: 21939 (1096.57 per sec.)
queries: 438780 (21931.37 per sec.)
Latency (ms): min 1.39 avg 3.64 max 192.05 95th percentile 6.67

sysbench built‑in tests (CPU, file I/O, etc.)

File I/O example:

# sysbench fileio --file-num=5 --file-total-size=2G prepare
# 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

Result excerpt:

reads/s: 9899.03
writes/s: 6621.38
read MiB/s: 154.66
written MiB/s: 103.46
Latency (ms): min 0.00 avg 0.81 max 53.56 95th percentile 4.10

CPU test example:

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

When performing stress tests, proceed with caution.