Introduction and use of MySQL stress testing tool mysqlslap_MySQL

1. Introduction to Mysqlslap

mysqlslap is the benchmark benchmarking tool that comes with MySQL 5.1. It is similar to the Apache Bench load generation tool. It generates schema, loads data, executes benchmarks and queries data. The syntax is simple, flexible and easy to use. This tool can simulate multiple clients concurrently issuing query updates to the server, provides performance test data and provides performance comparisons of multiple engines. mysqlslap provides an intuitive verification basis for mysql performance optimization before and after. The author recommends that system operation and maintenance personnel should master some common stress testing tools, so that they can more accurately grasp the upper limit of user traffic that the online system can support and its pressure resistance and other issues. .

2. Parameter introduction

--auto-generate-sql  -a           自动生成MySQL测试语句
--auto-generate-sql-add-autoincrement     添加AUTO_INCREMENT字段
--auto-generate-sql-execute-number=#     查询的个数
--auto-generate-sql-guid-primary       添加GUID字段
--auto-generate-sql-load-type=name      mixed：混合、update：更新、write：写入、key：读主键、read：查询
--auto-generate-sql-secondary-indexes=#    设置索引字段个数
--auto-generate-sql-unique-query-number=#   生成N条不同的query sql语句
--auto-generate-sql-unique-write-number=#   生成N条不同的write sql语句
--auto-generate-sql-write-number=#      每个线程write sql语句数量
--commit=#     　　　　　　　　　　　　　　　 设置每多少条sql语句提交一次
--compress  -C  　　　　　　　　　　　　　　 启用压缩协议
--concurrency=#  -c  　　　　　　　　　　  客户端数量(并发量)
--create=name    　　　　　　　　 　　    测试的SQL语句或文件
--create-schema=name     　　　　　　　　  测试的数据名
--csv=name                  生产CSV格式数据文件
--debug  -#  　　　　　　　　　　      写调试日志(windows不可用) 如：mysqlslap -a -#"d:t:o,c:/debug.txt"
--debug-check     　　　　　　　　　　　　  测试后打印调试信息(windows不可用)
--debug-info  -T              打印内存和CPU的相关信息
--default-auth=plugin             验证插件
--delimiter=str  -F             语句分隔符默认是 ;
--detach=#                  执行N条语句后断开重连
--enable-cleartext-plugin           启用明文身份验证插件
--engine=engine_name  -e          测试引擎 如：mysqlslap -e"myisam"或-e"myisam,innodb"
--help                    帮助信息
--host=host_name  -h            数据库host
--iterations=#  -i             重复运行的次数
--login-path=name               登陆配置文件
--no-drop                   测试后不删除schema
--number-char-cols=#  -x          设置VARCHAR类型字段数量
--number-int-cols=#  -y           设置INT类型字段数量
--number-of-queries=#             每个客户端运行sql语句数量
--only-print                 仅显示将被运行的SQL语句--delimiter选项影响
--password=password  -p           数据库密码
--pipe                    启用管道
--plugin-dir=path               插件目录
--port=port_num  -P             数据库端口
--post-query=value              测试后执行的SQL语句
--post-system=str               测试后执行的系统语句
--pre-query=value               测试前执行的SQL语句
--pre-system=str               测试前执行的系统语句
--protocol=type                链接协议 tcp, socket, pipe
--query=value  -q              测试的SQL语句 如：mysqlslap --query="SELECT * FROM t1"
--secure-auth                 不要发送密码到服务器
--silent  -s                不显示测试（静音测试）
--socket=path  -S              连接服务器的socket
--ssl-ca=file_name     
--ssl-capath=dir_name     
--ssl-cert=file_name     
--ssl-cipher=cipher_list     
--ssl-crl=file_name     
--ssl-crlpath=dir_name     
--ssl-key=file_name     
--ssl-verify-server-cert     
--user=user_name  -u            数据库用户名
--verbose  -v                输出更多的信息
--version  -V                版本信息

3. Simple example

1. Test 100, 200, and 400 concurrent executions of 100,000 SQL statements respectively.

mysqlslap -uroot -proot -h192.168.10.43 -P3306 --create-schema=chenmh --delimiter=";" --query="call pro_auto_select_pri" --concurrency=100,200,400 --number-of-queries=100000 

Note: The report output time is mainly reflected in the time it takes to execute the query specified by –number-of-queries. Here are the time it takes to execute 100,000 calls in 100 concurrent, 200 concurrent, and 400 concurrent calls. By default, -iterations is not specified, which means that the operation is executed repeatedly. If N is executed repeatedly, the output time also reflects the time spent in a single loop.

2. Generate csv

mysqlslap -a -e"myisam,innodb" --csv="c:/a.csv"

4. Summary

mysqlslap is mainly for database performance testing, and the automatic testing function is limited, and the production reports are not detailed enough, so it is recommended to use sysbench for benchmark testing. The above is the complete introduction about mysqlslap. I hope it will be helpful to everyone's study or work. If you have any questions, you can leave a message to communicate.