Shulou(Shulou.com)05/31 Report--

How to understand MySQL benchmarking and sysbench tools, for this problem, this article introduces the corresponding analysis and answers in detail, hoping to help more partners who want to solve this problem to find a simpler and easier way.

Preface

As a background developer, it is necessary to benchmark the database in order to grasp the performance of the database. The editor introduces the basic concepts of MySQL benchmarking and the detailed methods of benchmarking MySQL using sysbench.

A brief introduction to benchmark testing

1. What is a benchmark

The benchmark test of the database is a quantitative, reproducible and comparable test of the performance index of the database.

Benchmark test and stress test

Benchmarking can be understood as a stress test for the system. However, benchmarking does not care about business logic, so it is more simple, direct and easy to test, and the data can be generated by tools without requiring authenticity, while stress testing generally considers business logic (such as shopping cart business) and requires real data.

2. The role of benchmarking

For most Web applications, the bottleneck of the whole system lies in 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, connections, etc.), cache, are easy to improve performance through horizontal expansion (commonly known as plus machines). As for MySQL, due to the requirement of data consistency, it is impossible to increase the machine to disperse the pressure of writing data to the database; although the pressure can be reduced by pre-cache (Redis, etc.), read-write separation and sub-database table, it is too limited compared with the horizontal expansion of other components of the system.

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

3. Indicators of benchmark test

Common database metrics include:

TPS/QPS: measures throughput.

Response time: including average response time, minimum response time, * * response time, percentage of time, etc., in which the percentage of time is of great significance, such as the * response time of the first 95% of requests.

Concurrency: the number of query requests processed simultaneously.

4. Classification of benchmark tests

There are two ways to benchmark MySQL:

(1) benchmark testing for the entire system: testing through http requests, such as browsers, APP, or postman testing tools. The advantage of this scheme is that it can better aim at the whole system, and the test results are more accurate; the disadvantage is that the design is complex and difficult to implement.

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

When benchmarking for MySQL, special tools are generally used, such as mysqlslap, sysbench and so on. Among them, sysbench is more generic, more powerful, and more suitable for Innodb than mysqlslap (because many of the IO features of Innodb are simulated). Here's how to benchmark using sysbench.

II. Sysbench

1. Introduction to sysbench

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

Cpu performance

Disk io performance

Scheduler performance

Memory allocation and transfer speed

POSIX thread performance

Database performance (OLTP benchmark)

This paper mainly introduces the testing of database performance.

2. Sysbench installation

The environment used in this article is CentOS 6.5; the installation method is more or less the same on other Linux systems. 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.zip cd sysbench-1.0

(2) installation dependency

Yum install automake libtool-y

(3) installation

Before installation, make sure you are in the sysbench directory that you unzipped earlier.

. / autogen.sh. / configure export LD_LIBRARY_PATH=/usr/local/mysql/include # here replace it with the include make make install under the mysql path in the machine

(4) installation is successful

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

3. Sysbench syntax

Execute sysbench-help, and you can see the detailed usage of sysbench.

The basic syntax of sysbench is as follows:

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

The following describes the parameters and commands that are commonly used in practice.

(1) command

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

(2) testname

Testname specifies the test to be done, and in the old version of sysbench, you can specify the script to test with the-test parameter; in the new version, the-test parameter has been declared obsolete and you can specify the script directly instead of 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 in the test is the lua script, which can be used with sysbench's own script or developed by 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, and you can use the find command to search for oltp.lua under the sysbench path. P.S.: most data services are of type oltp. If you don't know what oltp is, chances are that your data service is of type oltp.

(3) options

There are many parameters for sysbench, among which the more commonly used ones are:

MySQL connection information parameters

-the hostname of the mysql-host:MySQL server. By default, if localhost; uses localhost to report an error on the local machine, it indicates that the MySQL server cannot be connected. It should be changed to the local IP address.

-mysql-port:MySQL server port. Default is 3306.

-mysql-user: user name

-mysql-password: password

MySQL execution parameters

-oltp-test-mode: execution mode, including simple, nontrx, and complex. Default is complex. In simple mode, only simple queries are tested; nontrx not only tests queries, but also inserts updates, but does not use transactions; in complex mode, testing is the most comprehensive, testing additions, deletions, changes and queries, and transactions are used. You can choose the test mode according to your needs.

-oltp-tables-count: the number of tables tested, selected according to the actual situation

-oltp-table-size: the size of the table to be tested, selected according to the actual situation

-threads: number of concurrent connections on the client

-time: test execution time (in seconds). This value should not be too short. You can choose 120.

-report-interval: the interval between generating reports in seconds, such as 10

4. Examples of using sysbench

When performing a sysbench, you should pay attention to:

(1) try not to test on the machine running on the MySQL server, on the one hand, it may not be able to reflect the impact of the network (even the local area network), on the other hand, the operation of sysbench (especially when the number of 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 under different connections, such as 10, 10, 20, 50, 100, etc.

(3) the general execution mode is complex. If you need to specially test the read-only performance of the server, or if you do not use transactions, you can choose simple mode or nontrx mode.

(4) if multiple tests are carried out in a row, make sure that the previously tested data has been cleaned up.

Here is an example of the use 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

Among them, the execution mode is complex, 10 tables are used, each table has 100000 pieces of data, the number of concurrent threads on the client is 10, the execution time is 120 seconds, and a report is generated every 10 seconds.

(2) perform the test

Export the test results to a 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, clean up 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

At the end of the test, look at the output file, as follows:

Among them, the more important information for us includes:

Queries: total number of queries and qps

Transactions: total number of transactions and tps

Latency-95th percentile: the * response time of the first 95% of requests. In this case, it is 344ms. This delay is very large because the performance of the MySQL server I use is very poor. This number is absolutely unacceptable in a formal environment.

III. Suggestions

Here are some suggestions for using sysbench.

1. Before you start testing, you should first make it clear whether you should use a benchmark for the entire system, a benchmark for MySQL, or both.

2. If you need a benchmark test for MySQL, you also need to clarify the accuracy requirements: whether you need to use real data from the production environment, or whether you can 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, not some of it.

3. Benchmarking needs to be done many times before it makes sense.

4. When testing, we should pay attention to the state of master-slave synchronization.

5. The test must simulate the situation of multithreading. The case of single thread can not only simulate the real efficiency, but also cannot simulate the blocking or even deadlock situation.

This is the answer to the question on how to understand the MySQL benchmark and sysbench tools. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.