Benchmark performance of MySQL using SysBench

Before installing MySQL instance, we must evaluate the CPU, Memory, Disk IO performance, Disk size, network of the server, also the maximal QPS(Query Per Second), concurrent threads and so on. Sysbench is a benchmark tool, it can be used to benchmark many items including the system and database. This article will introduce how to benchmark MySQL and disk IO performance.

1. Check the server information:

System:

CPU: 64 core(Intel(R) Xeon(R) Gold 6130 CPU @ 2.10GHz)

MEMORY: 128GB

MySQL instance:

Version: 5.7.23

innodb_buffer_pool_size: 96GB

max_connections: 2000

innodb_flush_log_at_trx_commit: 1

sync_binlog: 1

2. Installing sysbench

(1) Quick install instructions

(2) Building and Installing From Source

We can also download sysbench package on the Github(https://github.com/akopytov/sysbench), and the README.md there shows how to install and use it.

3. File IO Benchmark

(1)Create files

To avoid the RAM tampers the benchmark result, we should create files bigger than the RAM size. So i create 150GB files at the data directory:

This command will create a total of 500GB of files(default: 128 files)

To avoid the influence of RAM caching, we need to set ‘–file-total-size‘ to be larger than RAM.

(2)IO benchmark

Result:
The result shows that 16121.01 reads/s, 10747.29 writes/s, and 251.89 MiB/s for reading, 167.93 for writing.

4. MySQL OLTP(On-Line Transaction Processing) Benchmark

(1)Prepare tables and rows

This command means that creating 3 tables, and each table contains 10 million rows.

(2)Run benchmark

There are many LUA scripts in the /usr/share/sysbench directory, and you can choose a suitable one to have the benchmark.

Number of threads: 128

Report intermediate results every 10 second(s)

Running time: 600 seconds

While running sysbench, we can check some status. Look at the top status, the mysqld process uses almost 48 cores of CPU and 14% of memory(about 18 GB).

You can get the TPS(18183.90)/QPS(363684.99), total transactions, total queries(including read and write), how many errors happened from the report. Also you can find the latency metrics(95%: 10.65 ms) and the query distribution.

If you write a lot, then you can increase the INSERT/UPDATE/DELDETE queries per transaction, then set different number of queries per transaction, depend on the usage cases. For example,

The report shows that there are more writes per transaction.

Get more information by:

Then we can roughly estimate if this system and MySQL instance is enough for our usage, it must be much better than our needs, because the queries are more complicated at the production environment, and these queries require more system resources to execute.