Explain the stress test command mysqlslap and its syntax in Mysql5.7 in detail

Preface

mysqlslap is a diagnostics program designed to simulate the client load of an MySQL server and report the time of each phase. It works as if multiple clients are accessing Server 1.

1. Syntax used:

mysqlslap [options]

--auto-generate-sql,-a: Automatically generate test tables and data, which means testing concurrency stress with SQL scripts generated by mysqlslap itself.
--auto-generate-sql-load-type=type: Type of test statement, including read, key, write, update, and mixed (default).
--auto-generate-sql-add-auto-increment: Automatically adds the auto_increment column to the generated table.
--create-schema: Custom test library name.
--commint=N: Set N and submit it once after DML.
--compress,--C: Compress message delivery if both server and client support compression.
--concurrency = N,-c N: Indicates concurrency, that is, how many clients are simulated executing select at the same time. You can specify multiple values, for example: --concurrency=100, 200, 500.
--detach=N: Disconnect reconnection after N statement is executed.
--debug-info,-T: Print memory and information about CPU.
--engine=engine_name,-e engine_name: There can be multiple engines to be tested, separated by separators. For example: --engines = myisam, innodb.
--iterations = N,-i N: Number of iterations performed by the test, indicating how many times to run the test in different concurrent environments.
--number-char-cols=N,-x N: The automatically generated test table contains columns of N character type, defaulting to 1.
--number-int-cols=N,-y N: The automatically generated test table contains columns of N numeric type, defaulting to 1.
--number-of-queries=N: Total number of test queries (number of concurrent customers × number of queries per customer).
--only-print: Only test statements are printed and not actually executed.
--query=name,-q: Use custom scripts to execute tests, such as customizing a stored procedure or sql statements to execute tests.

2. Cases

Test 100 concurrent, auto-generated SQL test scripts, 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, test times 5 times, automatically generate SQL test script, read, write, update mixed test, self-growing field, 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