Simple tutorial on Mysqlslap MySQL stress testing tool

Starting with version 5.1.4, MySQL comes with a stress testing tool, mysqlslap, which performs tests by simulating multiple concurrent clients accessing mysql. It is very simple to use. The options available through mysqlslap and help are listed here for the main parameters, and more detailed instructions can be found in the official manual.

 � auto-generate-sql, -a 
 Generate test tables and data automatically  

 � auto-generate-sql-load-type=type 
 The type of the test statement. Values include: read . key . write . update and mixed( The default ) .  

 � number-char-cols=N, -x N 
 Automatically generated test table containing how many character types of columns, default 1 

 � number-int-cols=N, -y N 
 The automatically generated test table contains columns of how many numeric types, by default 1 

 � number-of-queries=N 
 Total number of test queries ( Number of concurrent customers × number of queries per customer ) 

 � query=name,-q 
 Use custom scripts to perform tests, such as calling custom ones 1 Stored procedures or sql Statement to execute the test.  

 � create-schema 
 The test of schema . MySQL In the schema That is database 

 � commint=N 
 How many article DML After submission 1 time  

 � compress, -C 
 If both server and client support are compressed, the information delivery is compressed  

 � concurrency=N, -c N 
 Concurrency, which is how many clients are simulated to execute at the same time select . You can specify more than one value, either a comma or a delimiter The value specified by the parameter is used as the separator  

 � engine=engine_name, -e engine_name 
 The storage engine used to create the test table, you can specify more than one  

 � iterations=N, -i N 
 Number of iterations of test execution  

 � detach=N 
 perform N Disconnects after a statement  

 � debug-info, -T 
 Print memory and CPU The information of  

 � only-print 
 Only test statements are printed without actually executing them 

It USES the following syntax:
shell > /usr/local/mysql/bin/mysqlslap [options]

Common parameters [options]
--concurrency represents the number of concurrent, multiple can be separated by commas. For example: -- concurrency = 50200500
--engines represents the engine to be tested and can be multiple, separated by a separator. For example: - engines = myisam innodb, memory
--iterations represents how many times each test will be run in different concurrent environments.
-- auto-generate-sql stands for testing concurrency stress with SQL scripts generated by the mysqlslap tool itself.
-- auto-generate-sql-add-auto-increment stands for automatically adding the auto_increment column to the generated table, starting with version 5.1.18,
- auto generate - sql load - type representative to test environment is reads or writes, or both (read write, update, mixed)
-- number-of-queries represents the total number of queries to run.
--debug-info stands for the additional output of CPU and memory information.
-- number-int-cols there are several properties that represent the INTEGER type in the sample table.
-- number-char-cols there are several properties that represent the vachar type in the sample table.
-- create-schema stands for custom test library name.
--query stands for custom test SQL script.

Description:

The test process needs to generate test tables and insert test data. This mysqlslap can be generated automatically. By default, 1 mysqlslap schema is generated. You can print the actual test process using -only-print, and the entire test will not leave a trace in the database.


Experimental steps:

Exercise 1:
Single-threaded testing. What the test did.
> ./bin/mysqlslap -a -uroot -p111111
Multithreaded testing. Use --concurrency to simulate concurrent connections.
> ./bin/mysqlslap -a -c 100 -uroot -p111111
Iterative testing. Used for averaging tests that require multiple executions.
> ./bin/mysqlslap -a -i 10 -uroot -p111111

Ex 2:
> ./bin/mysqlslap -auto-generate-sql-add-autoincrement -a -uroot -p111111
> ./bin/mysqlslap -a -auto-generate-sql-load-type=read -uroot -p111111
> ./bin/mysqlslap -a -auto-generate-secondary-indexes=3 -uroot -p111111
> ./bin/mysqlslap -a -auto-generate-sql-write-number=1000 -uroot -p111111

> ./bin/mysqlslap --create-schema world -q "select count(*) from City" -uroot -p111111
> ./bin/mysqlslap -a -e innodb -uroot -p111111
> ./bin/mysqlslap -a --number-of-queries=10 -uroot -p111111


Practice 3:
Execute 1 test, 50 and 100 concurrent, respectively, execute 1000 total queries:
> ./bin/mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --debug-info -uroot -p111111

One test result (Benchmark) was obtained for 50 and 100 concurrency, respectively. The more concurrency, the longer it took to execute all the queries. For accuracy, you can test multiple iterations:
> ./bin/mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --debug-info -uroot -p111111

The performance of different storage engines was tested and compared:
> ./bin/mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info -uroot -p111111