Detailed MySQL benchmark and sysbench tools

Objective

As a background development, it is necessary to benchmark the database to master the performance of the database. This article describes the basic concepts of MySQL benchmarking and the detailed methods for benchmarking MySQL using Sysbench.

There are omissions in the article, welcome to criticize.

Directory

I. INTRODUCTION of benchmark Testing

1, what is the benchmark test

2, the role of the benchmark test

3. Benchmark Test indicators

4, the classification of the benchmark test

Second, Sysbench

1, Sysbench Introduction

2, Sysbench installation

3. Sysbench syntax

4, Sysbench use example

5. Test results

III. recommendations

I. Benchmark test INTRODUCTION 1, what is a 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.

4, the classification of the benchmark test

The baseline test for MySQL is like the following two ways of thinking:

(1) Benchmarking for the entire system: testing via HTTP requests, such as through a browser, app, or postman Test tool. The advantage of this scheme is that it is better for the whole system, the test result is more accurate, the disadvantage is that the design is difficult to implement.

(2) Benchmarks for MySQL only: Pros and cons are the opposite of testing for the entire system.

When benchmarking against MySQL, it is common to use specialized tools such as Mysqlslap, Sysbench, and so on. Where Sysbench is more versatile, stronger and more suitable for innodb than MYSQLSLAP (because it simulates many of the InnoDB's IO features), here's how to use Sysbench for benchmark testing.

Ii. Introduction to Sysbench1 and Sysbench

Sysbench is a cross-platform benchmarking tool that supports multiple threads, supports multiple databases, and includes several types of tests, including the following:

• CPU Performance
• Disk IO Performance
• Scheduler performance
• memory allocation and transfer speed
• POSIX threading Performance
• Database performance (OLTP benchmark test)

This article mainly introduces the test of database performance.

2, Sysbench installation

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

(1) Download unzip

wget Https://github.com/akopytov/sysbench/archive/1.0.zip-O "Sysbench-1.0.zip" Unzip SYSBENCH-1.0.ZIPCD sysbench-1.0

(2) Installation dependency

Yum Install Automake Libtool–y

(3) Installation

Before installing, make sure you are in the previously unzipped Sysbench directory.

./autogen.sh./configureexport ld_library_path=/usr/local/mysql/include #这里换成机器中mysql路径下的includemakemake Install

(4) Successful Installation

[Email protected] sysbench-1.0]# sysbench--versionsysbench 1.0.9

3. Sysbench syntax

You can see the detailed use of sysbench by executing sysbench–help.

The basic syntax for Sysbench is as follows:

sysbench [Options] ... [TestName] [Command]

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

(1) command

command is sysbench to execute commands, including prepare, run, and cleanup, as the name implies, prepare prepares the data for testing in advance, run performs a formal test, and cleanup cleans up the database after the test is complete.

(2) TestName

TESTNAME Specifies the test to be performed, in the older version of Sysbench, the test script can be specified by the--test parameter, whereas in the new version, the--test parameter is declared obsolete, and the script is specified without the use of--test.

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 scripts used in the test are LUA scripts, which can be scripted using sysbench, or can be developed on your own. For most applications, it is sufficient to use a script that comes with Sysbench. In different versions of Sysbench, LUA scripts may have different locations, and you can use the Find command to search for Oltp.lua under the sysbench path yourself. P.S.: Most data services are OLTP-type, and if you don't know what OLTP is, then the big probability is that your data service is OLTP type.

(3) Options

Sysbench has a number of parameters, among which the more commonly used include:

MySQL Connection Information Parameters

• --MYSQL-HOST:MYSQL server hostname, default localhost, if you use localhost error on this computer, prompt cannot connect MySQL server, change the IP address of the machine should be able.
• --mysql-port:mysql server port, default 3306
• --mysql-user: User Name
• --mysql-password: Password

MySQL Execution Parameters

• --oltp-test-mode: Execution modes, including simple, nontrx, and complex, are complex by default. In simple mode, only test the query, Nontrx not only test the query, but also test the insert update, but do not use transactions, complex mode test the most comprehensive, will test additions and deletions, and will use transactions. You can choose the test mode according to your own needs.
• --oltp-tables-count: Test the number of tables, according to the actual situation to choose
• --oltp-table-size: The size of the test table, according to the actual situation to choose
• --threads: Number of concurrent connections for clients
• --time: The time of the test execution, in seconds, the value is not too short, you can choose 120
• --report-interval: The time interval to generate a report, in seconds, such as 10

4, Sysbench use example

When executing sysbench, it should be noted that:

(1) Try not to test on the MySQL server running machine, on the one hand may not reflect the impact of the network (even the LAN), on the other hand, the operation of Sysbench (especially when the number of concurrent sets is high) will affect the performance of the MySQL server.

(2) You can incrementally increase the number of concurrent connections (--thread parameters) of the client, and observe the performance of the MySQL server in different cases, such as 10,20,50,100, respectively.

(3) General execution mode Select Complex, if you need to specifically test server read-only performance, or do not use transaction performance, you can choose Simple mode or Nontrx mode.

(4) If multiple tests are performed continuously, be sure to ensure that the previously tested data has been cleaned up.

Here is an example of the use of Sysbench:

(1) Prepare the 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= --report-interval=10 Prepare

Where the execution mode is complex, 10 tables are used, each table has 100,000 data, the number of concurrent threads for the client is 10, the execution time is 120 seconds, and the report is generated every 10 seconds.

(2) Perform the test

Export 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= --report-interval=10 Run >>/home/test/mysysbench.log

(3) Cleaning up the data

After you finish the test, clean up the data, or the 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 finished, review the output file as follows:

Among them, information that is important to us includes:

Queries: Total Queries and QPS

Transactions: Total Transactions and TPS

latency-95th percentile: The maximum response time for the first 95% requests, in this case 344 milliseconds, is very large because the MySQL server I'm using is poorly performing, and in a formal environment this value is absolutely unacceptable.

III. recommendations

Here are some suggestions for using Sysbench.

1. Before starting the test, it should be clear whether you should use a benchmark for the entire system, a baseline test for MySQL, or both.

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

3, the benchmark test to be carried out several times to make sense.

4, the test needs to pay attention to master-slave synchronization state.

5, testing must simulate the situation of multi-threaded, single-threaded case not only can not simulate the real efficiency, can not simulate blocking or even deadlock situation.

Reference documents

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

Detailed MySQL benchmarks and sysbench tools