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

1. MySQL built‑in stress test tool: mysqlslap

mysqlslap is a simple benchmark utility that can simulate multiple concurrent clients, generate SQL automatically, and compare engine performance. Before testing, increase the server's max_connections setting.

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

[root@mysql ~]# systemctl restart mysqld

Verify the new limit:

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

Run a benchmark that tests both MyISAM and InnoDB engines with mixed read/write queries:

[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 command creates two test runs (100 and 200 concurrent clients), auto‑generates tables with 20 integer and 30 character columns, and executes 2 000 queries per run. Sample results show MyISAM handling ~0.55 queries/s and InnoDB ~0.26 queries/s for 100 clients, with slightly different values for 200 clients.

2. Third‑party stress test tool: sysbench

Install sysbench from the EPEL repository:

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

Sysbench can test CPU, disk I/O, memory, thread scheduling, and database performance. Common options include --threads, --time, and database‑specific flags such as --mysql-host and --mysql-user. Use sysbench --help for a full list.

Prepare a MySQL test database (sbtest) and generate tables using the built‑in oltp_common.lua script:

# Create the 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

Verify that the tables exist and contain the expected number of rows:

mysql> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| sbtest1          |
| ...              |
| sbtest10         |
+------------------+

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 100000   |
+----------+

Run an OLTP read/write benchmark using 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

Sample output (excerpt) shows per‑second transaction rate (tps), query rate (qps), latency, and total counts. In the example, the test achieved about 1 140 tps and 22 800 qps with an average latency of ~3.6 ms.

Sysbench also provides a built‑in file I/O benchmark. Prepare five 2 GB files and run a random read/write test with 16 threads:

# 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

Result highlights: ~9 900 reads/s, ~6 600 writes/s, read throughput ~154 MiB/s, write throughput ~103 MiB/s, and average latency of 0.81 ms.

CPU performance can be measured with the cpu test:

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

Finally, the article reminds readers to perform stress testing cautiously, as high load can affect production systems.