Detailed introduction to mysqlslap stress testing mysql

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

mysqlslap has the following three steps: <br>1. Create schema, table, test data, etc., using a single connection (in MySQL, schema It’s the database); <br>2. Run the load test, you can use multiple concurrent client connections; <br>3. Clean up the test environment (delete the data, tables, etc. created by , and disconnect), Use a single connection.

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

Common parameters [options] Detailed description: <br> <br>--auto-generate-sql, -a automatically generates test tables and data, which means using the SQL script generated by the mysqlslap tool to test concurrency pressure. <br>--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, <a href="http://www.php.cn/wiki/1051.html" target="_blank">key</a>, write, up<a href="http://www.php.cn/wiki/1255.html" target="_blank">date</a> and mixed (default). <br>--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. <br>--number-char-cols=N, -x N How many columns of <a href="http://www.php.cn/code/6739.html" target="_blank"> character type </a> are included in the automatically generated test table, the default is 1<br>--number-int-cols=N , -y N How many numeric columns are included in the automatically generated test table, default 1<br>--number-of-queries=N The total number of test <a href="http://www.php.cn/php/php-tp-demand.html" target="_blank"> queries</a> (number of concurrent customers × each customer Number of queries)<br>--query="sql statement",-q Use a custom script to execute the test. For example, you can call a custom <a href="http://www.php.cn/code/12179.html" target="_blank"> stored procedure</a> or sql statement to execute the test. <br>--create="sql statement" Create the SQL statement or file of the table <br>--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. <br>--engine=engine_name, -e engine_name represents the engine to be tested, there can be multiple, separated by separators. For example: --engines=myisam,innodb. <br>--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. <br>--only-<a href="http://www.php.cn/wiki/1362.html" target="_blank">print</a> Only prints the test statement without actually executing it. <br>--debug-info, -T Print memory and CPU related information.

Note: <br>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-print to 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-thread test. What the test did. <br># mysqlslap -a -uroot -p123456<br>Multi-threaded testing. Use –concurrency to simulate the number of concurrent client connections. For example, 100 clients concurrentlyconnect to the database. <br># mysqlslap -a -c 100 -uroot -p123456<br>Iterative test. Used to perform tests multiple times to get the average. For example, iterate 10 times and take the average. <br># mysqlslap -a -i 10 -uroot -p123456<br>

Demo 2:<br>

Tests are different at the same time Compare the performance of storage engines: <br># 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: <br>

Both tests automatically generate SQL test scripts, and each iterates 10 times to take the average. The test environment includes read, write, update mixed tests, self-increasing fields, and the test engine is 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:

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

The above is the detailed content of Detailed introduction to mysqlslap stress testing mysql. For more information, please follow other related articles on the PHP Chinese website!