A brief analysis of MySQL database OLTP benchmark test_PHP tutorial

A brief analysis of the MySQL database OLTP benchmark

sysbench is a very excellent benchmarking tool that can accurately simulate the disk I/O mode of the MySQL database storage engine InnoDB. . Therefore, based on this feature of sysbench, this tool will be used below to benchmark and verify the MySQL database's support from simple to complex transaction processing workloads.

Test scenario 1, OLTP read benchmark test

1) Prepare 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 lasts for 60 seconds to randomize a table with 100,000 pieces of data in a single table data set Read operation, the test results are as follows:

By simulating 8 concurrent users to perform consistent read operations on a table with 100,000 data items and combining it with the above figure, the following conclusions are drawn from the analysis :

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

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

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

Basic configuration information of the running environment for the above test results:

1) Virtual machine 2 CPUs, 2G memory, 20G storage space;

2) MySQL database parameters are not optimized, Adjustment.

Test scenario two, OLTP mixed type load test

Simulate OLTP random reading and writing (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 simulated a complex mixed workload test on a table with 100,000 data items and combined with the above figure, the analysis came to the following conclusions:

1) A total of 233268 read requests and 83260 write requests were executed in 60 seconds. times, other types of requests: 33309, QPS: 5273;

2) A total of 16647 transaction operations were executed within 60s, 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 time is 1299.78ms, and the average execution time is 28.82ms.

Basic configuration information of the running environment for the above test results:

1) Virtual machine 2 CPUs, 2G memory, 20G storage space;

2) MySQL database parameters are not optimized, Adjustment.

http://www.bkjia.com/PHPjc/1092985.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/1092985.htmlTechArticleA brief analysis of MySQL database OLTP benchmark test sysbench is a very excellent benchmark test tool, which can accurately simulate MySQL The disk I/O mode of the database storage engine InnoDB. Therefore, base...