How to Benchmark MySQL Performance with mysqlslap and Sysbench

mysqlslap is MySQL's built‑in benchmark tool that can simulate multiple concurrent clients, execute queries, and compare engine performance, providing a clear verification basis for before‑and‑after optimizations.

Change the default maximum connections

Before stress testing, edit /etc/my.cnf to set max_connections=1024 and restart MySQL.

Verify the new setting:

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

Run mysqlslap stress test

Execute a test that runs two rounds of read/write concurrency (100 and 200 clients), automatically generates SQL, uses 20 init columns and 30 char columns, and tests both MyISAM and InnoDB engines:

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 results show MyISAM handling 100 clients at 0.557 queries/s and 200 clients at 0.522 queries/s, while InnoDB handles the same loads at 0.256 queries/s and 0.303 queries/s respectively.

Use third‑party sysbench tool

Install sysbench

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

sysbench test MySQL database performance

1. Prepare test data

# View sysbench Lua scripts help
sysbench /usr/share/sysbench/oltp_common.lua help
# Create sbtest database
mysqladmin -uroot -p123 create sbtest
# Prepare tables (10 tables, 100000 rows each)
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

2. Verify data existence

mysql -uroot -p123 sbtest
show tables;
select count(*) from sbtest1;

3. Run read/write test

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, QPS, read/write counts, latency, errors, and reconnects, followed by detailed SQL statistics and overall latency percentiles.

CPU/IO/Memory tests

Sysbench provides built‑in tests for file I/O, CPU, memory, threads, and mutex performance. Use sysbench --help to list them.

File I/O test 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

Results show reads/writes per second, fsync rate, throughput in MiB/s, total time, and latency statistics.

CPU performance test

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

Analyze the returned CPU benchmark numbers.

Original source: https://blog.51cto.com/14227204/2498644?source=drh Author: 张九冫