MySQL starts with a 5.1.4 release with a stress test tool Mysqlslap, which is very simple to use to simulate multiple concurrent clients accessing MySQL to perform the test. Available options can be obtained through mysqlslap–help, with some of the main parameters listed here, and a more detailed description of the official manual.
Copy Code code as follows:
–auto-generate-sql,-A
Automatically generate test tables and data
–auto-generate-sql-load-type=type
The type of the test statement. Values include: Read,key,write,update and mixed (default).
–number-char-cols=n, X-N
The number of character type columns that are included in the automatically generated test table, default 1
–number-int-cols=n, Y-N
How many numeric types of columns are included in the automatically generated test table, default 1
–number-of-queries=n
Total number of test queries (number of concurrent clients x per customer query)
–query=name,-q
Use custom scripts to perform tests, such as a custom stored procedure or SQL statement that can be invoked to execute a test.
–create-schema
In the schema,mysql of the test, the schema is database
–commint=n
How many DML are submitted once
–compress,-C
If both the server and client support are compressed, the message is compressed
–concurrency=n, C-N
concurrency, which is how many clients perform a select at the same time. Multiple values can be specified, with a comma or a value specified by the –delimiter parameter as a separator
–engine=engine_name, E-engine_name
Create the storage engine used by the test table to specify multiple
–iterations=n, I-N
The number of iterations that the test performed
–detach=n
Disconnect a connection after executing n statements
–debug-info, T
Print memory and CPU information
–only-print
Print only test statements without actually executing
Its use syntax is as follows:
Shell>/usr/local/mysql/bin/mysqlslap [Options]
Commonly used parameters "options" Detailed introduction:
--concurrency represents concurrent quantities, and multiple can be separated by commas. For example:--concurrency=50,200,500
--engines represents the engine to be tested, can have multiple, separated by delimiters. For example:--engines=myisam,innodb,memory
The--iterations represents how many times each test is to be run in a different concurrency environment.
--auto-generate-sql represents the SQL script that was generated with the Mysqlslap tool to test concurrent pressure.
--auto-generate-sql-add-auto-increment represents the automatic addition of auto_increment columns to the generated table, starting with the 5.1.18 Version,
--auto-generate-sql-load-type represents whether the environment to test is read or write or mixed (read,write,update,mixed)
--number-of-queries represents the total number of queries to run.
The--debug-info represents information about the extra CPU and memory to be exported.
There are several instances of the integer type in the--number-int-cols representation table.
There are several properties of the Vachar type in the--number-char-cols example table.
--create-schema represents a custom test library name.
--query represents a custom test SQL script.
Description
The process of testing needs to generate a test table, insert test data, this mysqlslap can be automatically generated, default to generate a Mysqlslap schema, if already exist, delete first. You can use-only-print to print the actual test process, and the entire test will not leave traces in the database after completion.
Experiment steps:
Practice One:
Single-thread testing. What the test did.
>./bin/mysqlslap-a-uroot-p111111
Multithreading test. Use--concurrency to simulate concurrent connections.
>/bin/mysqlslap-a-C 100-uroot-p111111
Iterative testing. For averages that require multiple tests to be performed.
>/bin/mysqlslap-a-I 10-uroot-p111111
Exercise two:
>/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
Exercise Three:
Perform a test, 50 and 100 concurrent, and perform a 1000-time total query:
>/bin/mysqlslap-a--concurrency=50,100--number-of-queries 1000--debug-info-uroot-p111111
50 and 100 concurrent results of a test result (Benchmark), the more the number of concurrent, the longer the execution of all queries. For accuracy, multiple iterations can be tested several times:
>/bin/mysqlslap-a--concurrency=50,100--number-of-queries 1000--iterations=5--debug-info-uroot-p111111
Test performance comparisons for different storage engines:
>./bin/mysqlslap-a--concurrency=50,100--number-of-queries 1000--iterations=5--engine=myisam,innodb-- debug-info-uroot-p111111
