abstract
mysql slap is mysql's own benchmark tool. The tool queries data with simple syntax, flexibility and ease of use The tool can simulate multiple clients to send query updates to the server simultaneously, give the performance test data, and provide the performance comparison of multiple engines. mysql slap provides an intuitive verification basis before and after mysql performance optimization. System operation and maintenance and DBA personnel should master some common stress testing tools in order to accurately grasp the user flow limit and pressure resistance supported by online databases.
1, Syntax of mysqlslap
mysqlslap [options] --auto-generate-sql, -a Automatically generate test tables and data, expressed by mysqlslap Generated by the tool itself SQL Script to test concurrency stress. --auto-generate-sql-load-type=type Type of test statement. Represents 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 Represents the automatic addition of the generated table auto_increment Column, from 5.1.18 Version is supported. --number-char-cols=N, -x N How many character type columns are included in the automatically generated test table? The default is 1 --number-int-cols=N, -y N How many numeric columns are included in the automatically generated test table? The default is 1 --number-of-queries=N Total test queries(Number of concurrent customers×Query times per customer) --query=name,-q Use custom scripts to execute tests. For example, you can call a custom stored procedure or sql Statement to execute the test. --create-schema Represents the name of the user-defined test library schema，MySQL in schema that is database. --commint=N How many DML Submit once after. --compress, -C If both the server and the client support compression, compress the message delivery. --concurrency=N, -c N Indicates the amount of concurrency, that is, how many clients are simulated to execute at the same time select. Multiple values can be specified, with commas or--delimiter The value specified by the parameter is used 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 represents the number of tests to be run in different concurrent environments. --only-print Print only test statements without actually executing them. --detach=N implement N Disconnect and reconnect after a statement. --debug-info, -T Print memory and CPU Information about.
During the test process, you need to generate a test table and insert test data. This mysqlslap can be generated automatically. By default, a mysqlslap schema is generated. If it already exists, it will be deleted first. You can use -- only print to print the actual test process, and there will be no trace in the database after the whole test is completed.
2, MySQL slap testing practice
# Single threaded test. Test what you did. mysqlslap -a -uroot -p123456 # Multithreaded testing. Use – concurrency to simulate concurrent connections. mysqlslap -a -c 100 -uroot -p123456 # Iterative testing. Used to get the average value when multiple tests are required. mysqlslap -a -i 10 -uroot -p123456 mysqlslap ---auto-generate-sql-add-autoincrement -a -uroot -p123456 mysqlslap -a --auto-generate-sql-load-type=read -uroot -p123456 mysqlslap -a --auto-generate-secondary-indexes=3 -uroot -p123456 mysqlslap -a --auto-generate-sql-write-number=1000 -uroot -p123456 mysqlslap --create-schema world -q "select count(*) from City" -uroot -p123456 mysqlslap -a -e innodb -uroot -p123456 mysqlslap -a --number-of-queries=10 -uroot -p123456 # Test and compare the performance of different storage engines: mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info -uroot -p123456 # Execute one test, 50 and 100 concurrent queries respectively, and execute 1000 total queries: mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --debug-info -uroot -p123456 # 50 and 100 concurrent get a test result (Benchmark) respectively. The more concurrent, the longer it takes to execute all queries. For the sake of accuracy, the test can be repeated several times: mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --debug-info -uroot -p123456
3, mysqlslap pressure test steps
3.1 change the default maximum number of connections
Before stress testing MySQL, you need to change its default maximum number of connections, as follows:
[root@mysql ~]# vim /etc/my.cnf ................ [mysqld] max_connections=1024 [root@mysql ~]# systemctl restart mysqld ` View maximum connections `mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 1024 | +-----------------+--------+ 1 row in set (0.00 sec)
[root@mysql ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100,200 --iterations=1 --number-int-cols=20 --number-char-cols=30 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=myisam,innodb --number-of-queries=2000 -uroot -proot --verbose
Description of the above command test: the simulation test is read and written concurrently twice, 100 for the first time and 200 for the second time. The SQL script is automatically generated. The test table contains 20 init fields and 30 char fields, and 2000 query requests are executed each time. The test engines are myisam and innodb. (many of the above options are default values and can be omitted. If you want to understand the explanation of each option, you can use mysqlslap --help to query).
Description of test results:
- Myisam initiated 0.557/s for the first time at 100 clients and 0.522/s for the second time at 200 clients
- Innodb initiated 0.256/s for the first time at 100 clients and 0.303/s for the second time at 200 clients
According to the actual needs, the number of concurrent can be increased little by little for stress testing.
mysqlslap pressure measurement results