Introduction to the use of mysqlslap

mysqlslap is a stress testing tool officially provided by MySQL starting from version 5.1.4. Stress testing is performed by simulating multiple concurrent clients accessing MySQL, and can well compare the concurrent stress performance differences of multiple storage engines in the same environment. The operation of

mysqlslaphas the following three steps:
1. Create schema, table, test data, etc., using a single connection (in MySQL, schema It is the database);
2. To run the load test, you can use multiple concurrent client connections;
3. To clean the test environment (delete the created data, tables, etc., disconnect), use a single connection.

The usage syntax is as follows:
# mysqlslap [options]

Common parameters [options] Detailed description:

--auto-generate-sql, -a automatically generates test tables and data, indicating that the SQL script generated by the mysqlslap tool itself is used to test concurrency pressure.
--auto-generate-sql-load-type=type Type of test statement. Indicates whether the environment to be tested is a read operation, a write operation, or a mixture of the two. Values include: read, key, write, update and mixed (default).
--auto-generate-sql-add-auto-increment means automatically adding the auto_increment column to the generated table. It is supported starting from version 5.1.18.
--number-char-cols=N, -x N How many character type columns are included in the automatically generated test table, default 1
--number-int-cols=N, -y N Automatically generated How many numeric columns are included in the test table, default 1
--number-of-queries=N Total number of test queries (number of concurrent customers × number of queries per customer)
--query="sql statement",-q Use a custom script to execute the test. For example, you can call a custom stored procedure or SQL statement to execute the test.
--create="sql statement" Create the SQL statement or file of the table
--concurrency=N, -c N represents the amount of concurrency, that is, how many clients are simulated to execute select at the same time. Multiple values can be specified, using commas or the value specified by the --delimiter parameter as the separator. For example: --concurrency=100,200,500.
--engine=engine_name, -e engine_name represents the engine to be tested. There can be multiple engines, separated by separators. For example: --engines=myisam,innodb.
--iterations=N, -i N The number of iterations of test execution, which represents how many times the test needs to be run in different concurrent environments.
--only-print only prints the test statement without actually executing it.
--debug-info, -T Print memory and CPU related information.


For more detailed parameters, please refer to the official website documentation:

Description:
The testing process requires generating a test table and inserting test data. This mysqlslap can be automatically generated. A mysqlslap schema is generated by default. If it already exists, it will be deleted first. You can use--only-printto print the actual test process. No trace will be left in the database after the entire test is completed.

Examples of various test parameters (-p is followed by the root password of mysql):

Demo 1:

Single-threaded test. What the test did.
# mysqlslap -a -uroot -p123456
Multi-threaded testing. Use –concurrency to simulate the number of concurrent client connections. For example, 100 clients connect to the database concurrently.
# mysqlslap -a -c 100 -uroot -p123456
Iterative test. Used to perform tests multiple times to get the average. For example, iterate 10 times and take the average.
# mysqlslap -a -i 10 -uroot -p123456

Demo 2:


Tests are different at the same time Compare the performance of storage engines:
# mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb -uroot -p123456

respectively 500 concurrency, each concurrent client executes one query, for a total of 500 queries, and 1000 concurrency, each concurrent client executes one query, for a total of 1000 queries. For the sake of accuracy, you can iterate the test several times:


Both tests automatically generate SQL test scripts, and each iterates 10 times to take the average. The test environment includes mixed tests of reading, writing, and updating. The auto-increasing field and the 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 return result is as follows:

As can be seen from the above two tests, when the concurrency changes from 500 to 1000, The query time is probably nearly doubled.

The above is the detailed content of Introduction to the use of mysqlslap. For more information, please follow other related articles on the PHP Chinese website!