MySQL stress testing tool mysqlslap

MySQL stress testing tool mysqlslap

Mysqlslap is a stress testing tool provided by MySQL. It can simulate a large number of clients operating on the database at the same time, and learn about the database performance through the result information.

One of the main scenarios of mysqlslap is to benchmark the database server.

For example, if we get a server and prepare it as a database server, how much access pressure does the hardware resources of this server support? Has the kernel parameters of the operating system been optimized to improve performance? After adjusting the Mysql configuration parameters, how much will the performance be affected ?......

Through a series of optimization work and benchmarking, You can adjust this server to the best state and master the performance indicators in the healthy state.

In the future, when the monitoring data is close to the benchmark indicators, it indicates that the database server is about to be fully loaded. It is necessary to analyze the usage problems such as database structure design and SQL statements, or the hardware resources are indeed insufficient, and then the corresponding processing

The database server may also require hardware upgrades. After the upgrade, you also need to perform a benchmark test. Compared with the previous test results, this ensures that the performance after the upgrade is improved, prevent improper upgrades or incorrect configurations from causing performance degradation

MySQL Performance Testing Tool-mysqlslap

Use mysqlsla to analyze MySQL logs

A good tool for analyzing MySQL slow query logs-mysqlsla

After learning about the usage of mysqlslap, let's take a look at how to use mysqlslap.

Mysqlslap example

01 simple usage

Perform a simple automatic test on the database

Mysqlslap-user = root-password = 111111-auto-generate-SQL

-Auto-generate-SQL is used to automatically generate test SQL

Meaning in the result:

Average number...

Average number of seconds for running all statements

Minimum number...

Minimum number of seconds for running all statements

Maximum number...

Maximum number of seconds for running all statements

Number of clients...

Number of clients

Average number of queries per client

Average number of queries run on each client

02 add concurrency

Mysqlslap-user = root-password = 111111-concurrency = 100-number-of-queries = 1000-auto-generate-SQL

-Concurrency = 100 indicates that 100 clients are connected at the same time.

-Number-of-queries = 1000 indicates the total number of test queries (number of concurrent clients * number of queries per client)

03 automatically generate complex tables

During automatic testing, the table structure is very simple and there are only two columns. The actual product environment will be more complex. You can use parameters to specify the number and type of columns, for example

Mysqlslap-user = root-password = 111111-concurrency = 50-number-int-cols = 5-number-char-cols = 20-auto-generate-SQL

-Number-int-cols = 5: Specifies to generate five int columns.

-Number-char-cols = 20 specify to generate 20 columns of the char type

04 use your own test database and test statement

Automatic Testing can help us understand the hardware status. For specific product situations, we should use our own database for testing. We can copy a product library and test the library, for example

Mysqlslap-user = root-password = 111111-concurrency = 50-create-schema = employees-query = "SELECT * FROM dept_emp ;"

-Create-schema is used to specify the name of the test database.

-Query is a custom test statement.

In actual use, it is generally used to test multiple complex statements. You can define a script file, for example

Echo "SELECT * FROM employees; SELECT * FROM titles; SELECT * FROM dept_emp; SELECT * FROM dept_manager; SELECT * FROM orders;"> ~ /Select_query. SQL

Write multiple query statements into an SQL file and use this file for testing.

Mysqlslap-user = root-password = 111111-concurrency = 20-number-of-queries = 1000-create-schema = employees-query = "select_query. SQL"-delimiter = ";"

-The SQL file is specified in the query.

-Delimiter: Description of the delimiter between statements in the SQL File

The above uses the creation script of the employees Test Library I put to the Network Disk (http://pan.baidu.com/s/1c23GioK), if you are interested in experience mysqlslap, you can download it and try it

Or go to the FTP server No. 1 of the customer's house to download

------------------------------------------ Split line ------------------------------------------

FTP address: ftp://ftp1.bkjia.com

Username: ftp1.bkjia.com

Password: www.bkjia.com

In 2016, LinuxIDC.com \ August \ MySQL stress testing tool mysqlslap \

For the download method, see

------------------------------------------ Split line ------------------------------------------

This article permanently updates the link address: