Sysbench is a scriptable, multi-threaded benchmark tool based on LuaJIT. While Sysbench can help you create arbitrarily complex workloads that don’t involve a database server, it’s most commonly used to perform database benchmarks. You can use Sysbench to evaluate any MySQL database to determine the associated reads, writes, queries/second (QPS), transactions/second (TPS), and latency. Using Sysbench to monitor MySQL performance is especially important when running a database under an intensive load.
In this post, I’ll describe how to use Sysbench to benchmark a database in Azure Database for MySQL – Flexible Server.
Download and install Sysbench
To download and install Sysbench on a Linux machine, run the following commands:
sudo apt-get update
sudo apt-get install sysbench
Important: It is recommended to size your Linux machine to ensure that is has the same number of CPUs as does you Azure Database for MySQL flexible server.
Note: To avoid capturing network latencies associated with traffic travelling across data centers, be sure to install Sysbench on a Linux machine located in the same datacenter and availability zone as your Azure Database for MySQL flexible server.
Create a test database for the benchmark
After you install Sysbench, you’ll need to create a test database in your Azure Database for MySQL flexible server. For the purposes of this post, we’ll use a test database names testdb.
To create a test database, perform the following steps.
- On your Linux machine, connect to your Azure Database for MySQL flexible server by running the following command:
mysql -h servername.mysql.database.azure.com -u username -p - To create the test database, run the following command:
create database testdb - In the Azure portal, navigate to server parameters blade for your Azure Database for MySQL flexible server, and then set the value of the max_prepared_stmt_count parameter to 655350.
- On your Linux machine, navigate to the directory in which the Sysbench binaries reside (the default directory location is /usr/share/sysbench).
- To prepare your database for the data load, run the following command:
cd /usr/share/sysbench date ; sysbench oltp_write_only.lua --tables=10 --table-size=1000000 --threads=10 --thread-init-timeout=120 --time=720000 --mysql-host= servername.mysql.database.azure.com --mysql-db=testdb --mysql-user= username --mysql-password='password' --mysql-port=3306 --report-interval=10 --percentile=95 --mysql-ssl=required prepare ; date
This command will use ten threads to create ten tables, each containing one million records, and will then create indexes for all the tables.
Note: Ensuring that the number of threads equals the NUM_OF_TABLES in the import Sysbench command line will significantly reduce the time required for the import process.
Benchmark a flexible server database
After you’ve created the tables and indexes, you need to run the workload so you can benchmark the database server. While Sysbench offers many OLTP workload options, the most commonly used workloads are the:
- 70% read/30% write workload
- Read_only workload
- Write_only workload
Regardless of the type of workload you test for, it’s important to consider a couple of specific server parameters.
- The value of the ‘max_connection‘ parameter specifies the maximum number of threads that can concurrently connect to your Azure Database for MySQL flexible server. The number of threads can’t exceed the threshold set for the max number of connections.
- The value of the ‘innodb_buffer_pool_size‘ parameter determines whether your entire dataset will fit in the buffer pool.
- If your entire dataset fits in the buffer pool, then your benchmark results will not account for the latency of disk reads.
- If your entire database doesn’t fit in the buffer pool, then your benchmark results will account for a combination of memory and disk performance.
To check the buffer pool hit rate (the ratio of memory hits/misses), run the following command:show engine innodb status\G
- Before you run the Sysbench test, configure the value of the ulimit parameter on your Linux VM to set resource limits for users.
You should set the value of the ulimit parameter to two times the maximum number of threads to be tested. For example, if you're running a Sysbench test with 310 threads, you should set the value of the ulimit parameter to 620:ulimit -n 620
Based on the type of workload you are running and want to benchmark, use the appropriate command below.
- To use the 70% read/30% write workload, run the following script:
sysbench oltp_read_write.lua --tables=10 --table-size=1000000 --db-driver=mysql --threads=310 --time=360 --mysql-host=servername.mysql.database.azure.com --mysql-db=testdb --mysql-user=username --mysql-password='password' --mysql-port=3306 --report-interval=1 --percentile=95 --mysql-ssl=required run | tee ~/test_read_write.log - To use the read_only workload, run the following script:
sysbench oltp_read_only.lua --tables=10 --table-size=1000000 --db-driver=mysql --threads=310 --time=360 --mysql-host=servername.mysql.database.azure.com --mysql-db=testdb --mysql-user=username --mysql-password='password' --mysql-port=3306 --report-interval=1 --percentile=95 --mysql-ssl=required run | tee ~/test_readOnly.log - To use the write_only workload, run the following script:
sysbench oltp_write_only.lua --tables=10 --table-size=1000000 --db-driver=mysql --threads=310 --time=360 --mysql-host=servername.mysql.database.azure.com --mysql-db=testdb --mysql-user=username --mysql-password='password' --mysql-port=3306 --report-interval=1 --percentile=95 --mysql-ssl=required run | tee ~/test_writeOnly.log
After the test completes, you’ll find the results stored in an associated log file. For example, the results of running the read_write benchmark test are stored in a file named test_read_write.log.
Note: It's critical to size your Azure Database for MySQL flexible server adequately to run the Sysbench test, as holding the Sysbench dataset in the buffer pool is a best practice to reduce latency. To estimate memory consumption, use the following:
Memory consumption estimation: 10 tables x 10000000 table size will consume 20GB of the innodb buffer pool.
The Sysbench results for a benchmark test that ran for 600 seconds with ~86K per second QPS and an average latency of 236.75 ms is shown in the following graphic:
Conclusion
You now have the information necessary to Sysbench to evaluate your MySQL databases to determine the associated reads, writes, QPS, TPS, and latency. Be sure to leverage this valuable detail to monitor MySQL parameters when running a database under an intensive load.
If you have any feedback or questions, please leave a comment below or email our team at AskAzureDBforMySQL@service.microsoft.com. Thank you!