Mysql Stress Test Tool Mysqlslap

Reprint to article author: Du Yishu

Links: https://www.sdk.cn/news/4512

Source: sdk.cn

Absrtact: Mysqlslap is a stress test tool from Mysql that can simulate a large number of clients operating the database simultaneously, through the result information to understand the database performance status

Mysqlslap is a stress test tool from Mysql that can simulate a large number of clients operating the database at the same time, through the results of information to understand the performance status of the database

A major work scenario for MySQL slap is benchmarking the database server

For example, we get a server, ready to be a database server, then the hardware resources of this server can support how much access pressure? Has the operating system's kernel parameters been optimized to improve performance? How much performance is affected by tuning the MySQL configuration parameters? ......

Through a series of tuning work, with the benchmark test, the server can be adjusted to the best state, but also mastered the health of the performance indicators

Later in the actual operation process, when the monitoring data close to the benchmark, the database server is almost full load, need to analyze the database structure design, SQL statements such usage problems, or hardware resources is not enough, and then the corresponding processing

The database server may also require a hardware upgrade, and benchmark tests will be required after the upgrade, comparing with previous test results to ensure that the upgraded performance is improved to prevent inappropriate upgrades or incorrect configuration resulting in degraded performance

Understand the usefulness of mysqlslap, see below how to use Mysqlslap

Mysqlslap Example

1. Simple usage

Make a simple automated test of the database

--user=root --password=111111 --auto-generate-sql 

--auto-generate-sql function is to generate test SQL automatically

The meanings of the results:

Average Number of ...

Average number of seconds to run all statements

Minimum Number of ...

Minimum number of seconds to run all statements

Maximum Number of ...

Maximum number of seconds to run all statements

Number of clients ...

Number of clients

Average number of queries per client

Average number of queries run per client

2. Add Concurrency

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

--CONCURRENCY=100 specifies that there are 100 client connections at a time

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

3. Automatic generation of complex tables

When automated testing, the table structure created is very simple, only two columns, the actual product environment will certainly be more complex, you can use parameters to specify the number and type of columns, such as

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

--NUMBER-INT-COLS=5 specifies that a column of 5 int types is generated

--NUMBER-CHAR-COLS=20 specifies that a column of 20 char types is generated

4. Use your own test library and test statements

Automated testing can help us understand the hardware level of the situation, for our product-specific situation, or to use their own library to test the better, you can copy a product library, and then to this library testing, such as

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

--create-schema used to specify the test library name

--query is a custom test statement

When actually used, it is usually 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 departments;" > ~/select_query.sql

Writes multiple query statements to a SQL file and then executes the test using this file

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

SQL file specified in--query

--delimiter describes what the delimiter between statements in a SQL file is

The creation script of the Employees test library used above I put on the net disk (Employees_db.zip), have the interest to experience mysqlslap words can download down to try

Resources

5.5.9 mysqlslap-load Emulation Client

How to Measure MySQL Query performance with Mysqlslap

Mysql Stress Test Tool Mysqlslap