Detailed explanation of MySQL benchmark testing and sysbench tools

Related learning recommendations: mysql tutorial

##What is a benchmark test

Database benchmark testing is a quantitative, reproducible, and comparable test of database performance indicators. Benchmark testing and stress testing Benchmark testing can be understood as a stress test for the system. But benchmark testing does not care about business logic and is simpler, more direct, and easier to test. The data can be generated by tools and does not require real data; while stress testing generally considers business logic (such as shopping cart business) and requires real data.

The role of benchmark testing

For most web applications, the bottleneck of the entire system is the database; the reason is simple: other factors in web applications, such as network bandwidth, load balancing nodes, application servers (including CPU, memory, hard disk lights, number of connections, etc.), cache, etc. It is easy to achieve performance improvements through horizontal expansion (commonly known as adding machines). For MySQL, due to data consistency requirements, the pressure of writing data to the database cannot be dispersed by adding more machines; although the pressure can be reduced through pre-caching (Redis, etc.), read-write separation, and sub-databases and tables, but Compared with the horizontal expansion of other components of the system, it is too restricted.

The role of database benchmark testing is to analyze the performance of the database under the current configuration (including hardware configuration, OS, database settings, etc.), thereby finding out the performance threshold of MySQL and adjusting the configuration according to the actual system requirements.

Benchmark indicators## Common database indicators include:

TPS/QPS: measures throughput. Response time: including average response time, minimum response time, maximum response time, time percentage, etc. The time percentage reference is of greater significance, such as the maximum response time of the first 95% of requests. . Concurrency: The number of query requests processed simultaneously.

Classification of Benchmark Tests There are two ideas for benchmarking MySQL:

Benchmark testing for the entire system: testing through http requests, such as through browsers, APPs or postman and other testing tools. The advantage of this solution is that it can better target the entire system and the test results are more accurate; the disadvantage is that the design is complex and difficult to implement.
•    
Benchmark testing only for MySQL: The advantages and disadvantages are exactly the opposite of testing for the entire system.
When benchmarking MySQL, special tools are generally used, such as mysqlslap, sysbench, etc. Among them, sysbench is more versatile and powerful than mysqlslap, and more suitable for Innodb (because it simulates many Innodb IO features). The following describes how to use sysbench for benchmark testing.

sysbench introduction

sysbench is a cross-platform benchmark testing tool that supports multi-threading and multiple databases; it mainly includes the following tests:

1. CPU performance 2. Disk IO performance 3. Scheduler performance 4. Memory allocation and transmission speed 5. POSIX thread performance 6. Database performance (OLTP benchmark test) 7. This article mainly introduces the test of database performance.

sysbench installation

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

1.下载解压
wget https://github.com/akopytov/sysbench/archive/1.0.zip -O "sysbench-1.0.zip"
unzip sysbench-1.0.zip
cd sysbench-1.0

2.安装依赖
yum install automake libtool –y

3.安装 安装之前，确保位于之前解压的sysbench目录中。
./autogen.sh
./configure
export LD_LIBRARY_PATH=/usr/local/mysql/include 
#这里换成机器中mysql路径下的include
make
make install

4.安装成功
[[email protected] sysbench-1.0]# sysbench --version
sysbench 1.0.9

sysbench syntax

Execute sysbench –help to see the detailed usage of sysbench. The basic syntax of sysbench is as follows:

sysbench [options]... [testname] [command]

The following describes the commonly used parameters and commands in actual use.

1.command command is the command to be executed by sysbench, including prepare, run and cleanup. As the name suggests, prepare is to prepare data in advance for testing, run is to execute formal tests, and cleanup is to Clean the database after the test is completed.

2.testname testname specifies the test to be performed. In the old version of sysbench, you can specify the test script through the --test parameter; in the new version, --test The parameter has been declared obsolete. You can specify the script directly without using --test.

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

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

The script used during testing is a Lua script. You can use the script that comes with sysbench or develop it yourself. For most applications, using the scripts that come with sysbench is sufficient. In different versions of sysbench, the location of the lua script may be different. You can use the find command in the sysbench path to search for oltp.lua. P.S.: Most data services are of oltp type. If you don’t understand what oltp is, then there is a high probability that your data service is of oltp type.

3.options There are many parameters for sysbench, the more commonly used ones include: MySQL connection information parameters:

1.--mysql-host: MySQL server host name, defaults to localhost; if you use localhost on this machine and an error is reported, indicating that you cannot connect to the MySQL server, you should be able to change the IP address of your machine. 2.--mysql-port: MySQL server port, default 33063.--mysql-user: User name 4.--mysql-password: Password

MySQL execution parameters:

    1.--oltp-test-mode：执行模式，包括simple、nontrx和complex，默认是complex。simple模式下只测试简单的查询；nontrx不仅测试查询，还测试插入更新等，但是不使用事务；complex模式下测试最全面，会测试增删改查，而且会使用事务。可以根据自己的需要选择测试模式。2.--oltp-tables-count：测试的表数量，根据实际情况选择3.--oltp-table-size：测试的表的大小，根据实际情况选择4.--threads：客户端的并发连接数5.--time：测试执行的时间，单位是秒，该值不要太短，可以选择1206.--report-interval：生成报告的时间间隔，单位是秒，如10

   sysbench使用举例

    在执行sysbench时，应该注意：

    1.尽量不要在MySQL服务器运行的机器上进行测试，一方面可能无法体现网络（哪怕是局域网）的影响，另一方面，sysbench的运行（尤其是设置的并发数较高时）会影响MySQL服务器的表现。2.可以逐步增加客户端的并发连接数（--thread参数），观察在连接数不同情况下，MySQL服务器的表现；如分别设置为10,20,50,100等。3.一般执行模式选择complex即可，如果需要特别测试服务器只读性能，或不使用事务时的性能，可以选择simple模式或nontrx模式。4.如果连续进行多次测试，注意确保之前测试的数据已经被清理干净。

   下面是sysbench使用的一个例子：

   1.准备数据

sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=192.168.10.10 --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

    其中，执行模式为complex，使用了10个表，每个表有10万条数据，客户端的并发线程数为10，执行时间为120秒，每10秒生成一次报告。 

   2.执行测试 将测试结果导出到文件中，便于后续分析。

sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=192.168.10.10 --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.清理数据 执行完测试后，清理数据，否则后面的测试会受到影响。

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

   测试结果

    测试结束后，查看输出文件，如下所示：   

Among them, the information that is more important to us 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, in this case it is 344 milliseconds, this delay is very large, it is Because the performance of the MySQL server I use is very poor; this value is absolutely unacceptable in a formal environment.

suggestion

Here are some suggestions for using sysbench.

1. Before starting the test, you should first clarify: should you use a benchmark test for the entire system, a benchmark test for MySQL, or both. 2. If you need to benchmark MySQL, you also need to clarify the accuracy requirements: whether you need to use real data from the production environment, or use tools to generate it; the former is more cumbersome to implement. If you want to use real data, try to use all the data instead of part of the data. 3. Benchmark testing must be conducted multiple times to be meaningful. 4. When testing, you need to pay attention to the status of master-slave synchronization. 5. The test must simulate multi-threaded situations. Single-threaded situations not only cannot simulate real efficiency, but also cannot simulate blocking or even deadlock situations.

If you want to learn more about programming, please pay attention to the php training column!

The above is the detailed content of Detailed explanation of MySQL benchmark testing and sysbench tools. For more information, please follow other related articles on the PHP Chinese website!