Detailed description of MySQL Benchmark Test and sysbench tool, Benchmark Test sysbench

Detailed description of MySQL Benchmark Test and sysbench tool, Benchmark Test sysbench
Preface

As a background developer, it is necessary to benchmark the database to understand the database performance. This article introduces the basic concepts of MySQL benchmark and detailed methods for using sysbench to benchmark MySQL.

The article has some omissions. You are welcome to criticize and correct them.

Directory

I. Introduction to Benchmark Testing

1. What is Benchmark Testing?

2. Role of Benchmark Testing

3. Benchmark Testing indicators

4. Classification of benchmark tests

Ii. sysbench

1. Introduction to sysbench

2. Install sysbench

3. sysbench syntax

4. Example of sysbench

5. Test Results

Iii. Suggestions

I. Introduction to Benchmark Testing 1. What is Benchmark Testing?

Database Benchmark Testing is a quantitative, reproducible, and comparable test of database performance indicators.

Benchmark Test and stress test

The benchmark test can be understood as a stress test for the system. However, the benchmark test does not care about the business logic. It is simpler, more direct, and easier to test. data can be generated by tools and is not required to be authentic. However, stress testing generally considers the business logic (such as the shopping cart business ), real data is required.

2. Role of Benchmark Testing

For most Web applications, the bottleneck of the entire system lies in the database. The reason is simple: Other Factors in Web applications, for example, network bandwidth, Server Load balancer nodes, application servers (including CPU, memory, Hard Drive lights, connections, etc.), and cache are easily expanded horizontally (commonly known as adding machines) to improve the performance. For MySQL, due to data consistency requirements, you cannot increase the number of machines to distribute the pressure on data writing to the database. Although you can use the pre-Cache (Redis, etc) read/write splitting and database/table sharding reduce the pressure, but it is limited by a lot compared with the horizontal expansion of other system components.

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

3. Benchmark Testing indicators

Common Database indicators include:

• TPS/QPS: measure the throughput.
• Response time: includes the average response time, minimum response time, maximum response time, and time percentage. The reference value of time percentage is significant, for example, the maximum response time of the first 95% requests ..
• Concurrency: the number of query requests simultaneously processed.

4. Classification of benchmark tests

There are two ways to benchmark MySQL:

(1) Benchmark Testing for the entire system: tests are carried out through http requests, such as through testing tools such as browsers, apps or postman. The advantage of this solution is that it can better target the entire system and make the test results more accurate. The disadvantage is that it is difficult to implement complicated design.

(2) Benchmark Testing for MySQL only: the advantages and disadvantages are the opposite of testing for the entire system.

Special tools are generally used for MySQL Benchmark Testing, such as mysqlslap and sysbench. Among them, sysbench is more general and powerful than mysqlslap, and more suitable for Innodb (because it simulates many Innodb I/O features). The following describes how to use sysbench for Benchmark Testing.

II. Introduction to sysbenench and sysbench

Sysbench is a cross-platform benchmark testing tool that supports multithreading and multiple databases. It mainly includes the following types of tests:

• Cpu Performance
• Disk io Performance
• Scheduler Performance
• Memory Allocation and transmission speed
• POSIX thread Performance
• Database performance (OLTP benchmark)

This document describes how to test the database performance.

2. Install sysbench

CentOS 6.5 is used in this article. The installation methods on other Linux systems are similar. The MySQL version is 5.6.

(1) download and decompress

wget https://github.com/akopytov/sysbench/archive/1.0.zip -O "sysbench-1.0.zip"unzip sysbench-1.0.zipcd sysbench-1.0

(2) install Dependencies

yum install automake libtool –y

(3) Installation

Before installation, make sure that the files are in the decompressed sysbench directory.

./Autogen. sh./configureexport LD_LIBRARY_PATH =/usr/local/mysql/include # Replace it with includemakemake install under the mysql path in the Machine

(4) Installation successful

[root@test sysbench-1.0]# sysbench --versionsysbench 1.0.9

3. sysbench syntax

Run sysbench-help to view the detailed usage of sysbench.

The basic syntaxes of sysbench are as follows:

Sysbench [options]... [testname] [command]

The following describes commonly used parameters and commands.

(1) command

Command is the command to be executed by sysbench, including prepare, run, and cleanup. As the name suggests, prepare prepares data for the test, and run is the formal test, cleanup cleans up the database after the test is complete.

(2) testname

Testname specifies the test to be performed. In the old version of sysbench, you can use the -- test parameter to specify the test script. In the new version, the -- test parameter has been declared as obsolete, instead of using -- test, you can directly specify the script.

For example, the following two methods have the same effect:

sysbench --test=./tests/include/oltp_legacy/oltp.luasysbench ./tests/include/oltp_legacy/oltp.lua

The lua script is used for testing. You can use the built-in sysbench script or develop it yourself. For most applications, it is enough to use the script that comes with sysbench. In different versions of sysbench, The lua script may have different locations. You can use the find command to search for oltp. lua in the sysbench path. P.S.: most data services are of the oltp type. If you do not know what oltp is, your data service is of the oltp type.

(3) options

Sysbench has many parameters, including:

MySQLConnection information Parameters

• -- Mysql-host: The host Name of the MySQL server. The default value is localhost. If an error is reported when localhost is used on the local machine, the system prompts that the MySQL server cannot be connected. You can change it to the IP address of the local machine.
• -- Mysql-port: MySQL server port. The default value is 3306.
• -- Mysql-user: user Name
• -- Mysql-password: password

MySQLExecution Parameters

• -- Oltp-test-mode: Execution mode, including simple, nontrx, and complex. The default value is complex. In simple mode, you can only test simple queries. nontrx not only tests queries, but also inserts updates, but does not use transactions. In complex mode, the test is the most comprehensive and will test addition, deletion, modification, and query, transactions are also used. You can select the test mode as needed.
• -- Oltp-tables-count: number of tables to be tested. Select
• -- Oltp-table-size: the size of the test table, which is selected based on the actual situation.
• -- Threads: number of concurrent connections on the client
• -- Time: The test execution time, in seconds. The value cannot be too short. You can select 120.
• -- Report-interval: the time interval for generating a report. The unit is seconds, for example, 10.

4. Example of sysbench

Note the following when executing sysbench:

(1) Try not to perform tests on machines running on the MySQL server. On the one hand, it may not reflect the impact of the network (even the LAN), on the other hand, the running of sysbench (especially when the set concurrency is high) will affect the performance of the MySQL server.

(2) You can gradually increase the number of concurrent connections on the client (-- thread parameter), and observe the performance of the MySQL server when the number of connections is different, such as set to 10, 20, 50,100, and so on.

(3) Select complex for the general execution mode. If you need to specifically test the server's read-only performance or do not use the transaction performance, you can select simple mode or nontrx mode.

(4) If multiple tests are performed consecutively, make sure that the data in the previous tests has been cleaned up.

The following is an example of sysbench:

(1) prepare data

sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=192.168.65.66 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=120 --report-interval=10 prepare

The execution mode is complex and 10 tables are used. Each table has 0.1 million data records, the number of concurrent threads on the client is 10, and the execution time is 120 seconds. A report is generated every 10 seconds.

 

(2) perform the test

Export the test results to the file for subsequent analysis.

sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=192.168.65.66 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=120 --report-interval=10 run >> /home/test/mysysbench.log

(3) Clean up data

After the test is executed, clear the data. Otherwise, subsequent tests will be affected.

sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=192.168.65.66 --mysql-port=3306 --mysql-user=root --mysql-password=123456 cleanup

5. Test Results

After the test is complete, view the output file as follows:

Important information includes:

Queries: Total number of queries and qps

Transactions: Total number of transactions and tps

Latency-95th percentile: the maximum response time of the first 95% of requests, which is 344 milliseconds in this example, this delay is very large because I am using a MySQL server with poor performance; in the formal environment, this value is absolutely unacceptable.

Iii. Suggestions

Below are some suggestions for using sysbench.

1. Before starting the test, we should first make it clear: we should adopt the benchmark test for the entire system, or the Benchmark Test for MySQL, or both.

2. If you need a benchmark test for MySQL, you also need to specify the precision requirement: whether to use real data in the production environment or use tools to generate the data; the implementation of the former is more complicated. If you want to use real data, try to use all data instead of some data.

3. the benchmark test must be performed multiple times to make sense.

4. Pay attention to the status of master-slave synchronization during testing.

5. the test must simulate multiple threads. A single thread cannot simulate both real efficiency and congestion or deadlock.

References

Http://blog.csdn.net/oahz4699092zhao/article/details/53332105