MySQL database implements OLTP benchmark test based on sysbench

Sysbench is an excellent benchmark tool that can accurately simulate the disk I/O mode of the MySQL database storage engine InnoDB. Therefore, based on this feature of sysbench, we will use this tool to benchmark and verify the MySQL database's support for simple to complex transaction processing workloads.

Test scenario 1: OLTP read benchmark test

1) Prepare the data

sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=100000

--mysql-db=cbusdb --mysql-user=root prepare

2) OLTP random read consistency business scenario test verification

time sysbench --test=oltp --oltp-table-size=100000 --mysql-db=cbusdb

--mysql-user=root --max-time=60 --oltp-read-only --max-requests=0 --num-threads=8 run

The above test simulates 8 concurrent threads, with a maximum request of 10,000 times/second, and performs random read operations on a single table data set with 100,000 data records for 60 seconds. The test results are as follows:

By simulating 8 concurrent users performing consistent read operations on a table with 100,000 data records and combining the above figure, we can draw the following conclusions:

1) A total of 766,094 read requests, 109,442 update and other types of requests were executed within 60 seconds, and QPS: 12,765;

2) A total of 54721 transaction operations were executed within 60 seconds, TPS: 911, and the success rate was 100%;

3) The minimum execution time of each transaction is 2.98ms, the maximum execution time is 184.5ms, and the average execution time is 8.76ms.

The basic configuration information of the operating environment of the above test results is:

1) The virtual machine has 2 CPUs, 2G memory, and 20G storage space;

2) The MySQL database parameters are not optimized or adjusted.

Test scenario 2: OLTP mixed type load test

Simulate OLTP random read and write (update, delete, insert):

sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=10000

--mysql-db=cbusdb --mysql-user=root --max-time=60

--oltp-nontrx-mode=update_key --max-requests=0 --num-threads=8 run

The test results are as follows:

By simulating 8 concurrent users to simulate a complex mixed workload test on a table with 100,000 data records and combining it with the above figure, the following conclusions are drawn:

1) Within 60 seconds, a total of 233,268 read requests, 83,260 write requests, 33,309 other types of requests, and 5,273 QPS;

2) A total of 16,647 transaction operations were executed within 60 seconds, TPS: 277, the number of deadlocks caused by lock conflicts was 15, and the transaction success rate was 99.91%;

3) The minimum execution time of each transaction is 8.19ms, the maximum execution time is 1299.78ms, and the average execution time is 28.82ms.

The basic configuration information of the operating environment of the above test results is:

1) The virtual machine has 2 CPUs, 2G memory, and 20G storage space;

2) The MySQL database parameters are not optimized or adjusted.

sysbench detailed introduction

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.