Analysis of MySQL database OLTP Benchmark Test

Analysis of MySQL database OLTP Benchmark Test

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

Test scenario 1: OLTP read Benchmark

1) prepare data

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

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

2) test and verify the OLTP random read consistency service Scenario

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 of 10000 requests per second. the random read operation is performed on tables with 10 million data records in a single table dataset for 60 seconds. The test results are as follows:

By simulating 8 concurrent users to perform consistent read operations on tables with 10 million data records, the following conclusions are obtained:

1) A total of 766094 read requests were executed within 60 seconds, and 109442 update and other types of requests were performed. QPS: 12765;

2) A total of 54721 transaction operations were performed within 60 s, TPS: 911, and success rate of 100%;

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

The basic configuration of the running environment of the test result is as follows:

1) two CPUs, 2 GB memory, and 20 GB storage space of the VM;

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

Test Scenario 2: OLTP hybrid Load Testing

Simulate random OLTP read and write (Update, delete, and 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 complex mixed workload tests on tables with 10 million data records and combining them, the following conclusions are obtained:

1) A total of 233268 read requests and 83260 write requests are executed within 60 seconds. Other types of requests: 33309, QPS: 5273;

2) A total of 16647 transaction operations were performed within 60 s, TPS: 277, 15 deadlocks caused by lock conflicts, and the success rate of the transaction was 99.91%;

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

The basic configuration of the running environment of the test result is as follows:

1) two CPUs, 2 GB memory, and 20 GB storage space of the VM;

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