MySQL benchmark test and Sysbench tool

Source: Internet
Author: User
Tags benchmark lua rand



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


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.