MySQL starts with version 5.1.4 with a stress testing toolMysqlslapIt is very easy to use by simulating multiple concurrent clients to access mysql. You can use mysqlslap-help to obtain available options. Here are some of the main parameters. For more information, see the official manual.
Copy codeThe Code is as follows:
-Auto-generate-SQL,-
Automatically generate test tables and data
-Auto-generate-SQL-load-type = type
The type of the test statement. Optional values: read, key, write, update, and mixed (default ).
-Number-char-cols = N,-x N
The number of character columns in the automatically generated test table. The default value is 1.
-Number-int-cols = N,-y N
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 perform the test. For example, you can call a custom stored procedure or SQL statement to perform the test.
-Create-schema
The schema used for testing. The schema in MySQL is also the database.
-Commint = N
How many DML records are submitted once?
-Compress,-C
If both the server and client support compression, the compressed information is transmitted.
-Concurrency = N,-c N
Concurrency, that is, the number of clients that are simulated to execute the select statement at the same time. Multiple values can be specified. Use a comma or the value specified by the-delimiter parameter as the delimiter.
-Engine = engine_name,-e engine_name
The storage engine used to create a test table. You can specify multiple storage engines.
-Iterations = N,-I N
Test execution iterations
-Detach = N
Disconnect after executing N statements
-Debug-info,-T
Print memory and CPU Information
-Only-print
Print only test statements without actually executing them
Its syntax is as follows:
Shell>/usr/local/mysql/bin/mysqlslap [options]
Common Parameters [options:
-- Concurrency indicates the number of concurrent tasks. multiple concurrent tasks can be separated by commas. Example: -- concurrency = 50,200,500
-- Engines indicates the engine to be tested, which can be separated by separators. Example: -- engines = myisam, innodb, memory
-- Iterations indicates the number of times a test is run in different Concurrent Environments.
-- Auto-generate-SQL indicates that the SQL script generated by mysqlslap is used to test the concurrency pressure.
-- Auto-generate-SQL-add-auto-increment indicates that the auto_increment column is automatically added to the generated table, starting with version 5.1.18,
-- Auto-generate-SQL-load-type indicates whether the test environment is a combination of read, write, update, and mixed)
-- Number-of-queries indicates the total number of queries to be run.
-- Debug-info indicates that information about the CPU and memory needs to be output.
-- Number-int-cols indicates the number of INTEGER attributes in the example table.
-- Number-char-cols indicates the vachar type attributes in the example table.
-- Create-schema indicates the name of the custom test database.
-- Query indicates a custom test SQL script.
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.
Tutorial steps:
Exercise 1:
Single-threaded testing. Test what is done.
>./Bin/mysqlslap-a-uroot-p111111
Multi-threaded testing. Use -- concurrency to simulate concurrent connections.
>./Bin/mysqlslap-a-c 100-uroot-p111111
Iterative testing. Used to obtain the average value after multiple tests are performed.
>./Bin/mysqlslap-a-I 10-uroot-p111111
Exercise 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
Exercise 3:
Execute a test, 50 and 100 concurrent queries respectively, and execute 1000 total queries:
>./Bin/mysqlslap-a -- concurrency = 50,100 -- number-of-queries 1000 -- debug-info-uroot-p111111
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:
>./Bin/mysqlslap-a -- concurrency = 50,100 -- number-of-queries 1000 -- iterations = 5 -- debug-info-uroot-p111111
The performance of different storage engines is compared during the test:
>./Bin/mysqlslap-a -- concurrency = 50,100 -- number-of-queries 1000 -- iterations = 5 -- engine = myisam, innodb -- debug-info-uroot-p111111
