How to Accelerate Database Testing with Sysbench: Insights and Extensions

Introduction

Database testing provides a quick entry point to understand database and OS behavior, forming a foundation for deeper performance tuning. Meituan‑Dianping uses the open‑source benchmark sysbench ( https://github.com/akopytov/sysbench) to obtain repeatable, comparable results and to explore advanced features such as JSON testing.

Why Choose Sysbench

Widely known among DBAs, facilitating knowledge transfer.

Major MySQL vendors (Oracle, Percona, etc.) publish performance data with sysbench, making result reproduction easy.

Supports MySQL 8.0 and benefits from active community development, reducing maintenance cost.

Embedded Lua scripting allows new test scenarios without modifying core C code.

Unified Test Parameters

To avoid unnecessary detours for newcomers, Meituan‑Dianping standardizes key sysbench options (table count, rows per table, warm‑up time) and enforces MySQL configuration parameters such as sync_binlog=1, innodb_flush_log_at_trx_commit=2, innodb_io_capacity=2000. The most important parameters are illustrated in the following image.

Parameter Optimization Example

To demonstrate sysbench‑driven configuration tuning, the impact of MySQL 5.7 slave_parallel_workers on replication throughput was measured. Using the oltp_write_only.lua script to generate load (≈33,336 TPS) on the master, the slave TPS curve was observed. The optimal worker count was eight threads, where replication performance peaked.

Extensibility of Sysbench

All test scenarios are written in Lua; adding support for a new database requires implementing about ten provided interfaces. The core engine delegates workload control to Lua scripts, enabling rapid customization. For example, to evaluate MySQL 5.7 JSON performance against MongoDB, only a few Lua files were modified to create a dedicated JSON benchmark without writing new C code.

Performance Data Collection

Sysbench records detailed latency statistics using a logarithmic scale, providing high precision for short response times while using modest memory for long tails. The conversion formula is:

k = floor((log(response_time) + 6.908) * 55.35 + 0.5)
response_time = exp((k / 55.535) - 6.908)

Hotspot Mitigation

When many threads update a shared counter, contention becomes a scalability hotspot. Sysbench mitigates this by giving each thread a local counter and aggregating them only when a global value is needed. The relevant source resides in sb_counter.c, which can be inspected for the implementation details.

Conclusion

Sysbench enables repeatable MySQL performance testing, systematic parameter tuning, and custom extensions such as JSON benchmarks. Studying its source reveals a modular design and efficient data‑collection mechanisms, providing a solid basis for further internal tooling and service‑oriented testing platforms.

Thought Question: The article moves from basic performance testing to deeper optimization and finally to source‑level extensions. Share the auxiliary technologies you rely on most in your work and how they help you solve technical problems.