Analysis of MySQL database OLTP Benchmark Test

Source: Internet
Author: User

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.


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.