Detailed description of MySQL performance stress testing tool mysqlslap

The syntax is as follows:
# Mysqlslap [options]

Common Parameters [options:

-- Auto-generate-SQL,-a automatically generates test tables and data, indicating that the SQL script generated by mysqlslap is used to test the concurrency pressure.
-- Auto-generate-SQL-load-type = type test statement type. It indicates whether the environment to be tested is a combination of read operations, write operations, and the two. Optional values: read, key, write, update, and mixed (default ).
-- Auto-generate-SQL-add-auto-increment indicates that the auto_increment column is automatically added to the generated table, which is supported from version 5.1.18.
-- Number-char-cols = N,-x N: The number of columns in the test table that are automatically generated. The default value is 1.
-- Number-int-cols = N,-y N: the number of columns in the automatically generated test table. The default value is 1.
-- Number-of-queries = N total number of test queries (number of concurrent customers × number of queries per customer)
-- Query = name,-q: Use a custom script to execute the test. For example, you can call a custom stored procedure or SQL statement to execute the test.
-- Create-schema indicates the name of the custom test database, the test schema, And the schema in MySQL is also the database.
-- Commint = N number of DML records before submission.
-- Compress,-C if both the server and client support compression, the compressed information is transmitted.
-- Concurrency = N,-c N indicates the concurrency, that is, to simulate how many clients execute select at the same time. You can specify multiple values by using commas (,) or the value specified by the -- delimiter parameter as the delimiter. Example: -- concurrency = 100,200,500.
-- Engine = engine_name,-e engine_name indicates the engine to be tested. Multiple engines can be separated by separators. For example, -- engines = myisam and innodb.
-- Iterations = N,-I N indicates the number of iterations of the test execution, which indicates the number of times the test is run in different Concurrent Environments.
-- Only-print: only print test statements without actual execution.
-- Detach = N after N statements are executed, the reconnection is closed.
-- Debug-info,-T prints information about memory and CPU.

Note:
During the test, you need to generate a test table and insert the test data. This mysqlslap can be automatically generated. By default, a schema of mysqlslap is generated. if it already exists, delete it first. You can use -- only-print to print the actual test process. After the test is completed, no trace is left in the database.

Various test parameter instances (-p is followed by the mysql root Password ):

Single-threaded testing. Test what is done.
# Mysqlslap-a-uroot-p123456
Multi-threaded testing. Use-concurrency to simulate concurrent connections.
# Mysqlslap-a-c 100-uroot-p123456
Iterative testing. Used to obtain the average value after multiple tests are performed.
# Mysqlslap-a-I 10-uroot-p123456

# Mysqlslap --- auto-generate-SQL-add-autoincrement-a-uroot-p123456
# Mysqlslap-a -- auto-generate-SQL-load-type = read-uroot-p123456
# Mysqlslap-a -- auto-generate-secondary-indexes = 3-uroot-p123456
# Mysqlslap-a -- auto-generate-SQL-write-number = 1000-uroot-p123456
# Mysqlslap -- create-schema world-q "select count (*) from City"-uroot-p123456
# Mysqlslap-a-e innodb-uroot-p123456
# Mysqlslap-a -- number-of-queries = 10-uroot-p123456

The performance of different storage engines is compared during the test:
# Mysqlslap-a -- concurrency = 50,100 -- number-of-queries 1000 -- iterations = 5 -- engine = myisam, innodb -- debug-info-uroot-p123456

Execute a test, 50 and 100 concurrent queries respectively, and execute 1000 total queries:
# Mysqlslap-a -- concurrency = 50,100 -- number-of-queries 1000 -- debug-info-uroot-p123456

50 and 100 concurrency get a test result (Benchmark) respectively. The more concurrency, the longer the query execution time. For accuracy, you can perform multiple iteration tests:
# Mysqlslap-a -- concurrency = 50,100 -- number-of-queries 1000 -- iterations = 5 -- debug-info-uroot-p123456