How to Benchmark MySQL Performance with Sysbench: A Step‑by‑Step Guide

Introduction

As a backend developer, benchmarking a database is essential to understand its performance. This article explains the basic concepts of MySQL benchmarking and provides a detailed method using the sysbench tool.

What Is a Benchmark?

Database benchmarking is a quantitative, reproducible, and comparable test of performance metrics. Unlike full‑scale stress testing, it ignores business logic, uses synthetic data, and focuses on raw throughput and latency.

Why Benchmark MySQL?

In most web applications the database is the performance bottleneck. Because MySQL’s consistency requirements limit horizontal scaling, benchmarking helps identify the current configuration’s limits and guides tuning, caching, or sharding decisions.

Key Metrics

Typical metrics include TPS/QPS (throughput), response‑time statistics (average, min, max, percentile), and concurrency (simultaneous queries).

Benchmark Scope

Two common approaches are:

Full‑system benchmark via HTTP requests (browser, app, Postman). Provides realistic end‑to‑end results but is complex to design.

MySQL‑only benchmark. Simpler to set up; results focus on the database layer.

Sysbench Overview

Sysbench is a cross‑platform, multi‑threaded benchmark tool that supports various tests, including:

CPU performance

Disk I/O performance

Scheduler performance

Memory allocation and transfer speed

POSIX thread performance

Database performance (OLTP benchmark)

Installation (CentOS 6.5, MySQL 5.6)

Download and unzip sysbench source:

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

Install build dependencies: yum install -y automake libtool Compile and install:

./autogen.sh
./configure
export LD_LIBRARY_PATH=/usr/local/mysql/include   # adjust to your MySQL include path
make
make install

Verify installation:

sysbench --version
# Expected output: sysbench 1.0.9

Sysbench Syntax

The basic command format is: sysbench [options]... testname [command] Typical commands are prepare (create test data), run (execute the benchmark), and cleanup (remove test data).

Important Options

MySQL connection parameters:

--mysql-host=HOST   # default: localhost
--mysql-port=PORT   # default: 3306
--mysql-user=USER
--mysql-password=PASS

OLTP test options:

--oltp-test-mode=MODE      # simple, nontrx, complex (default)
--oltp-tables-count=N      # number of tables
--oltp-table-size=SIZE     # rows per table
--threads=NUM             # client threads
--time=SECONDS            # test duration (e.g., 120)
--report-interval=SEC     # interval between reports

Example: Preparing 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

Running the Benchmark

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

Cleaning Up

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

Result Analysis

After the run, examine the generated log (or the screenshot below). Key figures include:

queries : total queries and QPS

transactions : total transactions and TPS

Latency‑95th percentile : maximum response time for the slowest 5% of requests (e.g., 344 ms in the example, which is unacceptable for production).

Best‑Practice Recommendations

Decide early whether you need a full‑system benchmark, a MySQL‑only benchmark, or both.

If realism matters, use production‑scale data; otherwise synthetic data is sufficient.

Run each test multiple times to obtain stable averages.

Ensure master‑slave replication is in a consistent state before testing.

Always simulate multi‑threaded workloads; single‑thread tests miss contention, deadlocks, and realistic throughput.