MySQL Stress Performance Testing (Mysqlslap) Tool

This article introduces an official mysql Mysqlslap tool to test mysql performance. If you need it, you can refer to this article to test your mysql performance and load.

Mysqlslap is a stress testing tool officially provided by MySQL since version 5.1.4. Simulate multiple concurrent clients to access MySQL for stress testing, and provide detailed data performance reports for "high-load attack MySQL. In addition, it can compare the performance difference of concurrent pressure of multiple storage engines in the same environment.

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