There are many components involved in building a database, from the underlying compute and storage to the various system tuning that can be done right up to the database itself. Using database benchmarking can assist in both managing the expectation of how a system will perform under a specific load and identifying any problems before they affect business operations. Here’s how to benchmark MySQL performance.
MySQL benchmark tools
There are a number of tools that can be used to benchmark the performance of a MySQL database:
- DBT2 – DBT (Database Test Suite) is a set of database workload test kits. DBT2 is a specific version of the test suite that mimics an online transaction processing (OLTP) application for a company operating multiple It contains a mix of read and write transactions including New Orders, Order Entry, Order Status, Payment and Stock Handling.
- HammerDB – An open-source and cross platform tool for database load testing. Based on industry standards such as TPC-C and TPC-H.
- Sysbench – Provides scriptable and multithreaded benchmarking capabilities for Linux. There are bundled scenarios for database OLTP, filesystem, CPU, memory thread and mutex benchmarks.
How to Benchmark MySQL performance
In this tutorial sysbench will be used as the tool in which to benchmark a MySQL database. Sysbench is a very versatile and scalable database performance benchmarking tool. It makes use of .lua files to create test scenarios. In this guide the Percona-Lab tpcc .lua workload and sysbench will be combined to showcase how to run the benchmark on a Linux system.
Step 1. Add the sysbench repository to the system and install it
Adding the sysbench repository to the local system allows for the system package manager to install and keep sysbench up to date. Once the repository has been added then the package can be installed.
This step will differ based on the Linux distribution:
Debian/Ubuntu:
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bash
sudo apt -y install sysbench
RHEL/CentOS:
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench
Fedora:
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo dnf -y install sysbench
Arch Linux:
sudo pacman -Suy sysbench
Sysbench itself is accessible using the “sysbench” command. The .lua and test scenarios are installed to: /usr/share/sysbench/
[root@DB-01 ~]# ll /usr/share/sysbench
total 60
-rwxr-xr-x. 1 root root 1448 Apr 24 2020 bulk_insert.lua
-rw-r–r–. 1 root root 14369 Apr 24 2020 oltp_common.lua
-rwxr-xr-x. 1 root root 1286 Apr 24 2020 oltp_delete.lua
-rwxr-xr-x. 1 root root 2411 Apr 24 2020 oltp_insert.lua
-rwxr-xr-x. 1 root root 1261 Apr 24 2020 oltp_point_select.lua
-rwxr-xr-x. 1 root root 1645 Apr 24 2020 oltp_read_only.lua
-rwxr-xr-x. 1 root root 1820 Apr 24 2020 oltp_read_write.lua
-rwxr-xr-x. 1 root root 1114 Apr 24 2020 oltp_update_index.lua
-rwxr-xr-x. 1 root root 1123 Apr 24 2020 oltp_update_non_index.lua
-rwxr-xr-x. 1 root root 1436 Apr 24 2020 oltp_write_only.lua
-rwxr-xr-x. 1 root root 1915 Apr 24 2020 select_random_points.lua
-rwxr-xr-x. 1 root root 2114 Apr 24 2020 select_random_ranges.lua
drwxr-xr-x. 4 root root 49 Sep 20 06:03 tests
Step 2. Clone the Percona TPCC repository from GitHub
The Percona-Lab TPCC workload is a TPCC-like workload for sysbench 1.0.x. It diverges from the standard in a number of ways:
- Does not use fully random text fields. This allows for different compression methods to be evaluated in the various MySQL storage engines.
- Multiple table sets – Allows for more than the standard 9 tables to be used, providing more flexibility in test scenarios. More information on the Percona TPCC-like workload can be found in this blog
On the system where the benchmark is running ensure that the git package is installed using the relevant package manager.
Once the git package is installed the Percona-Lab/sysbench-tpcc repository can be cloned using the following command:
git clone https://github.com/Percona-Lab/sysbench-tpcc /usr/share/sysbench/percona
[root@DB-01 ~]# ll /usr/share/sysbench/percona/
total 92
-rw-r–r–. 1 root root 11342 Sep 20 06:04 LICENSE
-rw-r–r–. 1 root root 984 Sep 20 06:04 README.md
-rw-r–r–. 1 root root 12320 Sep 20 06:04 tpcc_check.lua
-rw-r–r–. 1 root root 21290 Sep 20 06:04 tpcc_common.lua
-rwxr-xr-x. 1 root root 1863 Sep 20 06:04 tpcc.lua
-rwxr-xr-x. 1 root root 28573 Sep 20 06:04 tpcc_run.lua
-rw-r–r–. 1 root root 1369 Sep 20 06:04 tpcc-scm-1.rockspec
Step 3. Copy the Percona TPCC scripts to the sysbench directory
To ensure dependency resolution the .lua scripts for the Percona-Lab/sysbench-tpcc repository need to be copied to the sysbench folder. This can be done using the cp command in Linux to copy the files:
cp /usr/share/sysbench/percona/* /usr/share/sysbench/
[root@DB-01 ~]# ll /usr/share/sysbench
total 152
-rwxr-xr-x. 1 root root 1448 Apr 24 2020 bulk_insert.lua
-rw-r–r–. 1 root root 11342 Sep 20 06:07 LICENSE
-rw-r–r–. 1 root root 14369 Apr 24 2020 oltp_common.lua
-rwxr-xr-x. 1 root root 1286 Apr 24 2020 oltp_delete.lua
-rwxr-xr-x. 1 root root 2411 Apr 24 2020 oltp_insert.lua
-rwxr-xr-x. 1 root root 1261 Apr 24 2020 oltp_point_select.lua
-rwxr-xr-x. 1 root root 1645 Apr 24 2020 oltp_read_only.lua
-rwxr-xr-x. 1 root root 1820 Apr 24 2020 oltp_read_write.lua
-rwxr-xr-x. 1 root root 1114 Apr 24 2020 oltp_update_index.lua
-rwxr-xr-x. 1 root root 1123 Apr 24 2020 oltp_update_non_index.lua
-rwxr-xr-x. 1 root root 1436 Apr 24 2020 oltp_write_only.lua
drwxr-xr-x. 3 root root 158 Sep 20 06:04 percona
-rw-r–r–. 1 root root 984 Sep 20 06:07 README.md
-rwxr-xr-x. 1 root root 1915 Apr 24 2020 select_random_points.lua
-rwxr-xr-x. 1 root root 2114 Apr 24 2020 select_random_ranges.lua
drwxr-xr-x. 4 root root 49 Sep 20 06:03 tests
-rw-r–r–. 1 root root 12320 Sep 20 06:07 tpcc_check.lua
-rw-r–r–. 1 root root 21290 Sep 20 06:07 tpcc_common.lua
-rwxr-xr-x. 1 root root 1863 Sep 20 06:07 tpcc.lua
-rwxr-xr-x. 1 root root 28573 Sep 20 06:07 tpcc_run.lua
-rw-r–r–. 1 root root 1369 Sep 20 06:07 tpcc-scm-1.rockspec
Step 4. Prepare the database for the benchmark
In order to run the benchmark, the database needs to be prepared for it. This essentially means the database needs to have the appropriate objects (tables, constraints, indexes) crated and populated with data prior to the benchmark being run.
Sysbench accepts a number of arguments which allows for the test scenario to scale. Some of these arguments are:
| –threads | The number of threads to run the operation at. This simulates the number of users addressing the database. Some example values are: 8, 16, 24, 32, 48, 64, 96, 128, 256, 512, and 1024. The higher the thread count the more the system resource usage will be. Default is 1. |
| –tables | The number of tables to create in the database/schema. |
| –scale | The scale factor (warehouses) which increases the amount of data to work on overall. Increase –tables and –scale to increase the database size and number of rows operated on. As a rough estimation, 100 warehouses with 1 table set produces about 10GB of data in non-compressed InnoDB tables (so 100 warehouses with 10 table sets gives about 100GB – 50 tables and 500 warehouses offer 2.5TB-3TB of database size.) |
To PREPARE the database, ensure the database is created and run the following (note to substitute the arguments with the required values:
sysbench /usr/share/sysbench/tpcc.lua –threads=48 –tables=10 –scale=100 –db-driver=mysql –mysql-db=sbtest –mysql-user=DBtest –mysql-password=’password’ prepare
Step 5. Run the benchmark
To RUN the benchmark, execute the following, ensuring the –scale and –table values match. Increase –threads to increase stress on the system during the benchmark and –time (in seconds) to increase the time within which the benchmark will run:
sysbench /usr/share/sysbench/tpcc.lua –threads=512 –time=300 –tables=10 –scale=100 –db-driver=mysql –mysql-db=sbtest –mysql-user=DBtest –mysql-password=’password’ run
Once the test has completed a report will be shown with various benchmark statistics. The important statistics for comparison to other systems or tuning parameters are transactions per second, latency and thread fairness:
- Transactions per second indicates how many transactions can be completed in a single second. A higher value is better.
- Latency denotes the amount of time on average it takes to execute events. Events are execution units defined in a particular LUA script. A lower latency value is better.
- Thread fairness is an indication of the average number of executed events on a per thread basis.
Using these measures as comparison points one can identify if tuning or hardware components will result in a change to database performance.
SQL statistics:
queries performed:
read: 30307535
write: 31454182
other: 4675434
total: 66437151
transactions: 2336949 (7780.70 per sec.)
queries: 66437151 (221197.66 per sec.)
ignored errors: 10365 (34.51 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.3504s
total number of events: 2336949
Latency (ms):
min: 0.54
avg: 65.75
max: 18566.21
95th percentile: 240.02
sum: 153648098.61
Threads fairness:
events (avg/stddev): 4564.3535/217.24
execution time (avg/stddev): 300.0939/0.10
Step 6. Cleanup the database
To clean up the database after the benchmark is completed execute the following with the appropriate values for tables entered:
sysbench /usr/share/sysbench/percona/tpcc.lua –threads=48 –tables=10 –scale=100 –db-driver=mysql –mysql-db=sbtest –mysql-user=DBtest –mysql-password=’password’ cleanup
Database benchmarking is a great way in which to compare systems of different design or identify issues which could arise due to performance bottlenecks. While there are a number of benchmarking tools out there and many different ways in which each can be used each user should try to find the tool that best suits their system and requirements.
Written By:
