MySQL benchmark test and Sysbench tool

I. Introduction of benchmark Testing

1, what is the benchmark test

Benchmarking a database is a quantitative, reproducible, and comparable test of a database's performance metrics.

Benchmarking and stress Testing

Benchmarks can be understood as a stress test for the system. But benchmarking does not care about business logic, simpler, straightforward, easy to test, data can be generated by tools, does not require authenticity, and stress tests generally consider business logic (such as shopping cart business) and require real data.

2, the role of the benchmark test

The bottleneck for most Web applications is the database; The reason is simple: other factors in Web applications, such as network bandwidth, load Balancer nodes, application servers (including CPU, memory, hard drive lights, connections, etc.), and caching, are easily scaled by horizontal extensions (commonly known as machines) to improve performance. For MySQL, due to data consistency, it is not possible to increase the machine to spread the pressure to write data to the database, although the pre-cache (Redis, etc.), read-write separation, library sub-table to alleviate the pressure, but compared with the other components of the system's horizontal expansion, there are too many restrictions.

The benchmark of the database is to analyze the performance of the database in the current configuration (including hardware configuration, OS, database settings, etc.), so as to find the performance threshold of MySQL and adjust the configuration according to the requirements of the actual system.

3. Benchmark Test indicators

Common database metrics include the following:

• TPS/QPS: Measure throughput.
• Response time: Includes average response time, minimum response time, maximum response time, percentage of time, and so on, where the percentage of time reference is significant, such as the maximum response time for the first 95% requests.
• Concurrency: The number of query requests processed at the same time.

Second, Sysbench

1. Sysbench installation (Ubuntu system)

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh |sudo bash
sudo apt -y install sysbench

2. OLTP testing

 
find / -name oltp.lua
sysbench --test = tests / db / oltp.lua (fill in according to the specific location) --mysql-host = databasehost --mysql-port = 3306 --mysql-user = user --mysql-password = password --mysql- db = databasename --oltp-test-mode = complex --oltp-tables-count = 10 --oltp-table-size = 10000000 --rand-init = on prepare / run / cleanup

Detailed parameters:

--test=tests/db/oltp.lua The script that specifies the test

--oltp-test-mode=complex execution mode, including simple, nontrx, and complex, is the most comprehensive test in Complex,complex mode by default, tests additions and deletions, and uses transactions. You can choose the test mode according to your own needs.

--oltp-tables-count=10 the number of tables tested, according to the actual situation to choose

--oltp-table-size the size of the table tested

--rand-init=on means that each test table is populated with random data.

Prepare, run, and cleanup, as the name implies, prepare prepares the data for testing, and run performs a formal test, and cleanup cleans the database after the test is complete.

3. Test results

sysbench 0.5: multi-threaded system evaluation benchmark
 
Running the test with following options:
Number of threads: 256
Report intermediate results every 10 second (s)
Random number generator seed is 0 and will be ignored
 
-Thread start
Threads started!
 
-Report test results every 10 seconds, tps, reads per second, writes per second, 99% response time statistics
[10s] threads: 256, tps: 524.19, reads / s: 7697.05, writes / s: 2143.56, response time: 1879.46ms (99%)
[20s] threads: 256, tps: 96.50, reads / s: 1351.01, writes / s: 373.30, response time: 9853.49ms (99%)
[30s] threads: 256, tps: 235.50, reads / s: 3297.01, writes / s: 946.90, response time: 2150.47ms (99%)
[40s] threads: 256, tps: 115.50, reads / s: 1617.00, writes / s: 491.40, response time: 4562.75ms (99%)
[50s] threads: 256, tps: 262.10, reads / s: 3669.41, writes / s: 1016.10, response time: 2049.90ms (99%)
[60s] threads: 256, tps: 121.50, reads / s: 1701.00, writes / s: 499.10, response time: 3666.03ms (99%)
[70s] threads: 256, tps: 201.40, reads / s: 2735.10, writes / s: 769.50, response time: 3867.82ms (99%)
[80s] threads: 256, tps: 204.70, reads / s: 2950.29, writes / s: 838.10, response time: 2724.99ms (99%)
[90s] threads: 256, tps: 118.40, reads / s: 1657.61, writes / s: 490.00, response time: 3835.53ms (99%)
 
 
OLTP test statistics:
    queries performed:
        read: 8823206-total reads
        write: 2520916-total writes
        other: 1260458-Total number of other operations (SELECT, INSERT, UPDATE, DELETE operations, such as COMMIT, etc.)
        total: 12604580-total total number of queries and qps
    transactions: 630229 (174.94 per sec.)-total transactions (transactions per second) total number of transactions and tps
    deadlocks: 0 (0.00 per sec.)-total deadlocks
    read / write requests: 11344122 (3148.86 per sec.)-total number of reads and writes (reads and writes per second)
    other operations: 1260458 (349.87 per sec.)-total number of other operations (other operations per second)
 
 
General statistics:-some statistics
    total time: 3602.6152s-total time
    total number of events: 630229-how many transactions occurred
    total time taken by event execution: 921887.7227s-the total time taken by all transactions (without consideration of parallelism)
    response time:-response time
         min: 6.52ms-minimum time
         avg: 1462.78ms-average time
         max: 9918.51ms-longest time
         approx. 99 percentile: 3265.01ms-more than 99% average time
 
 
Threads fairness:-thread stability
    events (avg / stddev): 2461.8320 / 34.60-events (average / deviation)
    execution time (avg / stddev): 3601.1239 / 0.63-execution time (average / deviation)

4. Clean Up test data

Sysbench--test=tests/db/oltp.lua (fill in according to specific location)--mysql-host=databasehost--mysql-port=3306 --mysql-user=user --mysql-password=password--mysql-db=databasename--oltp-test-mode=complex--oltp-tables-count= -- oltp-table-size=10000000 --rand-init=on cleanup

MySQL benchmark test and Sysbench tool