MySQL's own database stress test tool mysqlslap usage notes

basic introduction

mysqlslap is a stress testing tool that MySQL officially comes with since version 5.1.4. It performs stress testing by simulating multiple concurrent clients accessing MySQL, and we can understand the performance of the database through the result information.

Involving parameters

--concurrency: Concurrency, that is, how many clients can execute commands at the same time. Multiple values can be specified, or comma –delimiterparameter specified as delimiters
--iterations: test the number of iterations performed.
--number-int-cols: How many numeric columns are included in the automatically generated test table, the default is 1. The reason for setting it to 1 here is because we have generated an int type field in sysbench above.
--number-char-cols: How many character type columns are included in the automatically generated test table, the default is 1. The reason for setting it to 2 here is because we generated a char type field in sysbench above.
--auto-generate-sql: Automatically generate test tables and data. The effect of this command is similar to the prepare command of the sysbench command.
--auto-generate-sql-add-autoincrement: Add auto_incrementone.
If you want to see specific script generation, you can use –only-printcommands, only print statements without actually perform the test.
--engine: The storage engine used to create the test table can be specified more than one.
--number-of-queries: The total number of test queries (number of concurrent customers × number of queries per customer).
--create-schema: The tested schema. The schema in MySQL is the name of the database.
-uroot -pMyNewPass4!: Set the MySQL account and password.

Simple usage

1. Initial use

We execute the following command to do a simple automatic test on the database, the function of -auto-generate-sql is to automatically generate test SQL.
Note: If we do not specify a test database, the default is mysqlslap (the database will be automatically created and automatically deleted after the test).

mysqlslap -uroot -p123456 --auto-generate-sql

2. Operation result :

Average number of seconds to run all queries: The average
number of seconds to run all statements Minimum number of seconds to run all queries: The minimum
number of seconds to run all statements Maximum number of seconds to run all queries: The maximum number of seconds to run all statements
Number of clients running queries:
Average number of queries per client: Average number of queries per client

3. Print the actual test process :

The test process needs to generate test tables and insert test data. We can add --only-print to print the actual test process (not actually executed):

mysqlslap -uroot -p123456 --auto-generate-sql --only-print

After the test is completed, the test library will be deleted automatically.

4. Add concurrency :

--concurrency=100: Specify 100 client connections at the same time
--number-of-queries=1000: Specify the total number of test queries (the number of concurrent clients * the number of queries per client), so this example averages 10 queries per client

mysqlslap -uroot -p123456 --concurrency=100 --number-of-queries=1000 --auto-generate-sql

5. Repeat :

Use --iterationsparameter can set the number of iterations performed, i.e., the number of repetitions (N times the same test, seeking an average value). The number of times refers to the number of repetitions of the entire step, including data preparation, test load, and cleanup.

mysqlslap -uroot -p123456 --iterations=3 --concurrency=100 --number-of-queries=1000 --auto-generate-sql

6. Automatically generate complex tables :

--number-int-cols=5: Specify to generate 5 columns of type int
--number-char-cols=20: Specify to generate 20 columns of type char

mysqlslap -uroot -pHangge_123 --number-int-cols=5 --number-char-cols=20 --auto-generate-sql

7. Write tables and statements by yourself :

--create-schema=agent: The specified test library is agent (it will not be automatically deleted after the test is completed)
--query="SELECT * FROM t_call where id='123455';": Use custom test statements

mysqlslap -uroot -p123456 --concurrency=10 --number-of-queries=100 --create-schema=agent --query="SELECT * FROM t_call where id='123455';"

You can also write multiple query statements, separated by a title.

mysqlslap -uroot -pHangge_123 --concurrency=10 --number-of-queries=100 --create-schema=hangge --query="SELECT * FROM t_call where id='123455';SELECT * FROM t_call"

To test multiple complex statements, we can also write multiple query statements into a sql file first, and then use this file to perform the test
--query="select_query.sql":: specify the test file is select_query.sql
--delimiter=";": specify the separator between statements in the sql file Yes;

mysqlslap -uroot -p123456 --concurrency=10 --number-of-queries=100 --create-schema=agent --query="select_query.sql" --delimiter=";"

When inserting tests with your own library, there will be insufficient data, so how to use this tool to quickly generate a large number of tools?

We execute the following commands to implement data insertion. In order to improve efficiency, 10 client threads are opened at the same time to insert, and a total of 1 million operations are performed:

mysqlslap -uroot -p123456 --concurrency=10 --number-of-queries=1000000 --create-schema=agent --query="
INSERT INTO 
t_call_test(id, call_id, SESSIONID, TYPE, PGS, ROLE, TXT, BG, ED, SPEED, AGENT_ID, CREATE_TIME, MODIFY_TIME) 
VALUES(uuid(), uuid(), uuid(), 1, 1,1, '你好,今天天气怎么样', 10, 20, 50, '100001', now(), now())"

After the execution was completed, the server took 68.97 seconds, and the database was found to be available.