Mastering MySQL Benchmarking with Sysbench: A Complete Guide

What Is Benchmarking

Database benchmarking is a quantitative, reproducible, and comparable test of database performance metrics. It can be seen as a type of stress test that focuses on the system rather than business logic, using synthetic data generated by tools.

Purpose of Benchmarking

For most web applications, the bottleneck lies in the database because other components (network, load balancers, application servers, caches) can be scaled horizontally. Benchmarking a database helps analyze its performance under the current hardware, OS, and configuration, revealing MySQL’s performance limits and guiding configuration adjustments.

Benchmarking Metrics

Common database metrics include TPS/QPS (throughput), response time (average, min, max, percentile), and concurrency (number of simultaneous queries).

Types of Benchmarking

Two approaches for MySQL benchmarking:

Benchmark the entire system via HTTP requests (browser, app, Postman). This gives more accurate results but is complex to set up.

Benchmark MySQL alone. Simpler to implement but focuses only on the database.

When testing MySQL directly, tools such as

wget https://github.com/akopytov/sysbench/archive/1.0.zip -O "sysbench-1.0.zip"

, unzip sysbench-1.0.zip, cd sysbench-1.0, yum install automake libtool -y, and the following build steps are typical:

./autogen.sh
./configure
export LD_LIBRARY_PATH=/usr/local/mysql/include
make
make install

Sysbench Overview

Sysbench is a cross‑platform, multi‑threaded benchmarking tool that supports various databases. It can test:

CPU performance

Disk I/O performance

Scheduler performance

Memory allocation and transfer speed

POSIX thread performance

Database performance (OLTP benchmark)

Sysbench Syntax

The basic command format is: sysbench [options]... [testname] [command] Key options include MySQL connection parameters ( --mysql-host, --mysql-port, --mysql-user, --mysql-password) and OLTP test parameters such as --oltp-test-mode, --oltp-tables-count, --oltp-table-size, --threads, --time, and --report-interval.

Example Usage of Sysbench

1. Prepare data:

sysbench ./tests/include/oltp_legacy/oltp.lua \
  --mysql-host=192.168.10.10 \
  --mysql-port=3306 \
  --mysql-user=root \
  --mysql-password=123456 \
  --oltp-test-mode=complex \
  --oltp-tables-count=10 \
  --oltp-table-size=100000 \
  --threads=10 \
  --time=120 \
  --report-interval=10 \
  prepare

2. Run the test and redirect output to a log file:

sysbench ./tests/include/oltp_legacy/oltp.lua \
  --mysql-host=192.168.10.10 \
  --mysql-port=3306 \
  --mysql-user=root \
  --mysql-password=123456 \
  --oltp-test-mode=complex \
  --oltp-tables-count=10 \
  --oltp-table-size=100000 \
  --threads=10 \
  --time=120 \
  --report-interval=10 \
  run >> /home/test/mysysbench.log

3. Clean up data after the test:

sysbench ./tests/include/oltp_legacy/oltp.lua \
  --mysql-host=192.168.10.10 \
  --mysql-port=3306 \
  --mysql-user=root \
  --mysql-password=123456 \
  cleanup

Test Results

After the test, examine the output file. Important metrics include total queries and QPS, total transactions and TPS, and the 95th‑percentile latency (e.g., 344 ms in the example, which is unacceptably high for production).

Recommendations

Decide whether to benchmark the whole system, MySQL alone, or both.

If realistic data is required, use production‑scale data rather than a subset.

Run benchmarks multiple times for reliable results.

Ensure master‑slave replication is in a stable state during testing.

Simulate multi‑threaded workloads; single‑thread tests cannot reveal contention or deadlocks.

Original source: https://www.cnblogs.com/kismetv/archive/2017/09/30/7615738.html (author: 编程迷思)