Detailed explanation of the mysqlslap command and syntax for the built-in stress test in MySQL 5.7

Preface

mysqlslap is a diagnostic program designed to simulate client load to a MySQL server and report the times for each stage. It works just like multiple clients are accessing the server.

1. Syntax used:

mysqlslap [options]

--auto-generate-sql, -a: Automatically generate test tables and data, indicating that the SQL script generated by mysqlslap itself is used to test concurrent pressure.
--auto-generate-sql-load-type=type : The type of test statement. Possible values ​​include: read, key, write, update, and mixed (default).
--auto-generate-sql-add-auto-increment : Automatically add an auto_increment column to the generated table.
--create-schema : Custom test library name.
--commint=N : Commit once after setting N DMLs.
--compress, -C : Compress information if both the server and the client support compression.
--concurrency=N, -c N: Indicates the concurrency, that is, how many clients are simulated to execute select at the same time. Multiple values ​​can be specified, for example: --concurrency=100,200,500.
--detach=N : Disconnect and reconnect after executing N statements.
--debug-info, -T : Print information about memory and CPU.
--engine=engine_name, -e engine_name : The engines to be tested. There can be multiple engines, separated by separators. For example: --engines=myisam,innodb.
--iterations=N, -i N : The number of iterations of the test execution, indicating how many times to run the test in different concurrent environments.
--number-char-cols=N, -x N : The automatically generated test table contains N character columns. The default is 1.
--number-int-cols=N, -y N : The automatically generated test table contains N numeric columns. The default is 1.
--number-of-queries=N : Total number of test queries (number of concurrent clients × number of queries per client).
--only-print : Only print test statements without actually executing them.
--query=name,-q: Use a custom script to execute the test. For example, you can customize a stored procedure or SQL statement to execute the test.

2. Case

Test 100 concurrent queries, automatically generate SQL test scripts, and execute 1000 total queries:

root# mysqlslap -uroot -p123456 -a --concurrency=100 --number-of-queries 1000
 Benchmark
 Average number of seconds to run all queries: 0.725 seconds
 Minimum number of seconds to run all queries: 0.725 seconds
 Maximum number of seconds to run all queries: 0.725 seconds
 Number of clients running queries: 100
 Average number of queries per client: 10

Test 100 concurrent threads, 5 tests, automatically generate SQL test scripts, read, write, and update mixed tests, auto-increment fields, test engine is innodb, execute 5000 total queries

root# mysqlslap -uroot -p123456--concurrency=100 --iterations=5 --auto-generate-sql --auto-generate-sql-load-type=mixed --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=5000

Benchmark
 Running for engine innodb
 Average number of seconds to run all queries: 1.264 seconds
 Minimum number of seconds to run all queries: 1.161 seconds
 Maximum number of seconds to run all queries: 1.404 seconds
 Number of clients running queries: 100
 Average number of queries per client: 50

Summarize

The above is the stress test command mysqlslap and usage syntax of MySQL 5.7 introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!