Mysqlslap performs stress testing on mysql and mysqlslap

Mysqlslap performs stress testing on mysql and mysqlslap

 

Mysqlslap is a stress testing tool officially provided by MySQL since version 5.1.4. Simulate multiple concurrent clients to access MySQL to perform stress testing, and compare the performance difference of the concurrent pressure of multiple storage engines in the same environment.

 

MysqlslapThe following three steps are involved:
1. Create a schema, table, and test data, and use a single connection (in MySQL, schema is the database );
2. Run the load test. multiple concurrent clients can be used for connection;
3. Clean up the test environment (delete the created data and tables, and disconnect) and use a single connection.

 

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 = "SQL statement",-q uses a custom script to perform the test. For example, you can call a custom stored procedure or SQL statement to perform the test.
-- Create = "SQL statement": SQL statement or file used to create a table
-- 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.
-- Debug-info,-T prints information about memory and CPU.


For more detailed parameters, please refer to the official documentation: https://dev.mysql.com/doc/refman/5.7/en/mysqlslap.html#option_mysqlslap_debug-info

 

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. Available--only-printTo 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 ):

Demo 1:

Single-threaded testing. Test what is done.
# mysqlslap -a -uroot -p123456
Multi-threaded testing. Use-concurrency to simulate the number of concurrent connections on the client. For example, 100 clients concurrently connect to the database.
# mysqlslap -a -c 100 -uroot -p123456
Iterative testing. Used to obtain the average value after multiple tests are performed. For example, the average value is obtained for 10 iterations.
# mysqlslap -a -i 10 -uroot -p123456

Demo 2:


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 -uroot -p123456

Each concurrent client executes a query for 500 concurrent queries and 500 concurrent queries. Each concurrent client executes a query for 1000 concurrent queries. For accuracy, you can perform multiple iteration tests:


The SQL test script is automatically generated for both tests, and the average value is obtained for each iteration for 10 times. The test environment includes a hybrid test of reading, writing, and updating. The self-increasing field and test engine are innodb.

# Mysqlslap-uroot-p-concurrency = 500-I 10-a-auto-generate-SQL-type = mixed-auto-generate-SQL-add-autoincrement-engine = innodb- number-of-queries = 500

# Mysqlslap-uroot-p-concurrency = 1000-I 10-a-auto-generate-SQL-type = mixed-auto-generate-SQL-add-autoincrement-engine = innodb- number-of-queries = 1000

The returned results are as follows:

From the two tests above, we can see that the query time is about twice the original query time when the concurrency is changed from 500 to 1000.

 

Reference: http://www.ha97.com/5182.html

Http://www.jb51.net/article/28621.htm