MySQL Built-in Stress Testing with mysqlslap

MySQL ships with a native benchmarking utility called mysqlslap that simluates client load without requiring additional installation.

Basic Usage

mysqlslap --auto-generate-sql \
  --concurrency=100,200 \
  --iterations=5 \
  --number-char-cols=30 \
  --number-int-cols=20 \
  --query-count=2000 \
  --engine=myisam,innodb

The command above executes two test iterations: first with 100 concurrent clients, then 200 clients. The tool automatically generates SQL queries, creates test tables with 20 integer columns and 30 character columns, and runs 2000 queries per iteration. Both MyISAM and InnoDB engines are tested.

Interpreting Results

Engine	Clients	Queries per Second
MyISAM	100	0.557
MyISAM	200	0.522
InnoDB	100	0.256
InnoDB	200	0.303

Gradually increase the concurrency level to identify performance bottlenecks and determine the saturation point of your database server.

Third-Party Tool: sysbench

sysbench is a versatile benchmarking suite that supports CPU, memory, I/O, and database performance testing.

Installation

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

The output should display the installed version, for example: sysbench 1.0.17

Testing Capabilities

sysbench provides benchmarks for:

• CPU computation performance
• Disk I/O throughput
• Scheduler efficiency
• Memory allocation and transfer speed
• POSIX thread performance
• Database OLTP benchmarks via Lua scripts in /usr/share/sysbench/

Command Reference

sysbench [options]... [testname] [command]

Available Commands: prepare, run, cleanup, help

Core Options:

• --threads=N — Number of worker threads (default: 1)
• --events=N — Maximum number of request events (default: 0 = unlimited)
• --time=N — Total test duration in seconds (default: 10)
• --rate=N — Target transactions per second (0 = unlimited)
• --report-interval=N — Intermediate reporting interval in seconds
• --percentile=N — Latency percentile for reporting (1-100)

Database Connection Options:

• --mysql-host=HOST — MySQL server hostname
• --mysql-port=PORT — MySQL server port (default: 3306)
• --mysql-user=USER — MySQL username (default: sbtest)
• --mysql-password=PASS — MySQL password
• --mysql-db=NAME — Database name (default: sbtest)

Database Performance Testing

Step 1: Prepare Test Data

mysqladmin -uroot -p'password' create sbtest

sysbench --mysql-host=127.0.0.1 \
--mysql-port=3306 \
--mysql-user=root \
--mysql-password='password' \
/usr/share/sysbench/oltp_common.lua \
--tables=10 \
--table-size=100000 \
prepare

This creates the sbtest database with 10 tables, each containing 100,000 rows.

Step 2: Verify Data

mysql -uroot -p'password' sbtest -e "SELECT COUNT(*) FROM sbtest1;"

Expected output confirms 100,000 rows per table.

Step 3: Execute Tests

The oltp_common.lua script is a shared library and cannot run tests directly. Use oltp_read_write.lua for read/write benchmarks:

sysbench --threads=4 \
--time=20 \
--report-interval=5 \
--mysql-host=127.0.0.1 \
--mysql-port=3306 \
--mysql-user=root \
--mysql-password='password' \
/usr/share/sysbench/oltp_read_write.lua \
--tables=10 \
--table-size=100000 \
run

Sample Output:

[ 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 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 4 tps: 1121.57 qps: 22429.09 (r/w/o: 15700.64/4485.30/2243.15) lat (ms,95%): 6.55 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 4 tps: 1141.69 qps: 22831.98 (r/w/o: 15982.65/4566.16/2283.18) lat (ms,95%): 6.09 err/s: 0.00 reconn/s: 0.00

SQL statistics:
queries performed:
    read: 307146
    write: 87756
    other: 43878
    total: 438780
transactions: 21939 (1096.57 per sec.)
queries: 438780 (21931.37 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
    total time: 20.0055s
    total number of events: 21939

Latency (ms):
    min: 1.39
    avg: 3.64
    max: 192.05
    95th percentile: 6.67
    sum: 79964.26

Threads fairness:
    events (avg/stddev): 5484.7500/15.12
    execution time (avg/stddev): 19.9911/0.00

Key Metrics Explained:

Metric	Description
tps	Transactions per second
qps	Queries per second
r/w/o	Read/Write/Other operations per second
95th percentile	95% of requests complete within this latency

System Benchmarks

File I/O Testing

sysbench fileio --file-num=5 --file-total-size=2G prepare

This creates five files totaling 2GB (approximately 400MB per file).

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

Sample I/O Results:

File operations:
    reads/s: 9899.03
    writes/s: 6621.38
    fsyncs/s: 264.33

Throughput:
    read, MiB/s: 154.66
    written, MiB/s: 103.46

CPU Benchmark

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

Other Available Scripts

Script	Purpose
oltp_read_only.lua	Read-only transactions
oltp_write_only.lua	Write-only transactions
oltp_delete.lua	Delete operations
oltp_insert.lua	Bulk insert operations
oltp_point_select.lua	Point queries
oltp_range_select.lua	Range queries

Custom Lua scripts can be written to simulate application-specific workloads.