MySQL自带的性能压力测试工具mysqlslap

1. MySQL自带的性能压力测试工具mysqlslap

目录

• 1. MySQL自带的性能压力测试工具mysqlslap
  • 1.1. 概述
  • 1.2. 常用参数 [options] 详解
  • 1.3. 测试范例：
    • 1.3.1. 实例1
    • 1.3.2. 实例2
    • 1.3.3. 实例3(自定义sql语句)
    • 1.3.4. 实例4(指定sql脚本)
    • 1.3.5. 实际测试中的复杂情况。（指定表字段）
  • 1.4. 测试结果说明：

1.1. 概述

mysqlslap是从MySQL的5.1.4版开始就开始官方提供的压力测试工具。
通过模拟多个并发客户端并发访问MySQL来执行压力测试，同时提供了较详细的SQL执行数据性能报告，并且能很好的对比多个存储引擎（MyISAM，InnoDB等）在相同环境下的相同并发压力下的性能差别。

mysqlslap 官方介绍：http://dev.mysql.com/doc/refman/5.6/en/mysqlslap.html

1.2. 常用参数 [options] 详解

--host=host_name, -h host_name 连接到的MySQL服务器的主机名（或IP地址），默认为本机localhost
--user=user_name, -u user_name 连接MySQL服务时用的用户名
--password[=password], -p[password] 连接MySQL服务时用的密码
--create-schema 代表自定义的测试库名称，测试的schema，MySQL中schema也就是database。
(没指定使用哪个数据库时，可能会遇到错误mysqlslap: Error when connecting to server: 1049 Unknown database 'mysqlslap')

--query=name,-q 使用自定义脚本执行测试（可以是SQL字符串或脚本），例如可以调用自定义的一个存储过程或者sql语句来执行测试。

--create 创建表所需的SQL（可以是SQL字符串或脚本）

--concurrency=N, -c N 表示并发量，也就是模拟多少个客户端同时执行query。可指定多个值，以逗号或者--delimiter参数指定的值做为分隔符。例如：--concurrency=100,200,500（分别执行100、200、500个并发）。

--iterations=N, -i N 测试执行的迭代次数，代表要在不同的并发环境中，各自运行测试多少次；多次运行以便让结果更加准确。

--number-of-queries=N 总的测试查询次数(并发客户数×每客户查询次数)

--engine=engine_name, -e engine_name 代表要测试的引擎，可以有多个，用分隔符隔开。例如：--engines=myisam,innodb,memory。

--auto-generate-sql, -a 自动生成测试表和数据，表示用mysqlslap工具自己生成的SQL脚本来测试并发压力。

--auto-generate-sql-load-type=type 测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括：read (scan tables), write (insert into tables), key (read primary keys), update (update primary keys), or mixed (half inserts, half scanning selects). 默认值是：mixed.

--auto-generate-sql-add-auto-increment 代表对生成的表自动添加auto_increment列，从5.1.18版本开始支持。
--number-char-cols=N, -x N 自动生成的测试表中包含多少个字符类型的列，默认1
--number-int-cols=N, -y N 自动生成的测试表中包含多少个数字类型的列，默认1
--commint=N 多少条DML后提交一次。
--compress, -C 如果服务器和客户端支持都压缩，则压缩信息传递。
--only-print 只打印测试语句而不实际执行。
--detach=N 执行N条语句后断开重连。
--debug-info, -T 打印内存和CPU的相关信息。






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

1.3. 测试范例：

mysqlslap --no-defaults -uroot -p --socket /tmp/mysql3306.sock --concurrency=1 --iterations=1 --create-schema='test' --query='SELECT id,unionid,current_num,total_num FROM invite_join WHERE unionid="Cmo" AND active_id="3" AND is_deleted =0 ORDER BY id DESC LIMIT 1;' --number-of-queries=1000000


mysqlslap --no-defaults -hlocalhost -uroot -proot --socket /data/mysql/data3307/mysql3307.sock --concurrency=500,1000 --iterations=1 --auto-generate-sql --auto-generate-sql-load-type=mixed --auto-generate-sql-add-autoincrement --engine=innodb --number-char-cols=10 --number-int-cols=5 --number-of-queries=5000

结果中各项含义：

Average number of …
运行所有语句的平均秒数

Minimum number of …
运行所有语句的最小秒数

Maximum number of …
运行所有语句的最大秒数

Number of clients …
客户端数量

Average number of queries per client
每个客户端运行查询的平均数

各种测试参数实例（-p后面跟的是mysql的root密码）：

1.执行测试并打印测试的详细过程（只打印测试语句而不实际执行。）

mysqlslap -a -uroot -proot  --only-print

多线程测试。使用–concurrency来模拟并发连接。

`# mysqlslap -a -c 100 -uroot -p123456`

迭代测试。用于需要多次执行测试得到平均值。

`# 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`

测试同时不同的存储引擎的性能进行对比：

`# mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info -uroot -p123456`

执行一次测试，分别50和100个并发，执行1000次总查询：

`# mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --debug-info -uroot -p123456`

50和100个并发分别得到一次测试结果(Benchmark)，并发数越多，执行完所有查询的时间越长。为了准确起见，可以多迭代测试几次:

# mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --debug-info -uroot -p123456

1.3.1. 实例1

说明:测试100个并发线程，测试次数1次，自动生成SQL测试脚本，读、写、更新混合测试，自增长字段，测试引擎为innodb，共运行5000次查询

#mysqlslap -h127.0.0.1 -uroot -p123456789 --concurrency=100 --iterations=1 --auto-generate-sql --auto-generate-sql-load-type=mixed --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=5000

Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.351 seconds 　　　　 100个客户端（并发）同时运行这些SQL语句平均要花0.351秒
Minimum number of seconds to run all queries: 0.351 seconds
Maximum number of seconds to run all queries: 0.351 seconds
Number of clients running queries: 100 　　　　　　　　　　　　  总共100个客户端（并发）运行这些sql查询
Average number of queries per client:50 　　　　　　　　　　　　每个客户端（并发）平均运行50次查询（对应--concurrency=100，--number-of-queries=5000；5000/100=50）

1.3.2. 实例2

分别测试100,500,1000个并发线程，测试1次，共5000次查询。

#mysqlslap -h127.0.0.1 -uroot -p123456789 --concurrency=100,500,1000 --iterations=1 --auto-generate-sql --auto-generate-sql-load-type=mixed --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=5000 --debug-info

User time 0.21, System time 0.78
Maximum resident set size 21520, Integral resident set size 0
Non-physical pagefaults 12332, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 36771, Involuntary context switches 1396

1.3.3. 实例3(自定义sql语句)

#mysqlslap -h127.0.0.1 -uroot -p123456789 --concurrency=100 --iterations=1 --create-schema=rudao --query='select * from serverlist;' --engine=innodb --number-of-queries=5000 --debug-info

1.3.4. 实例4(指定sql脚本)

#mysqlslap -h127.0.0.1 -uroot -p123456789 --concurrency=100 --iterations=1 --create-schema=rudao --query=/tmp/query.sql --engine=innodb --number-of-queries=5000 --debug-info

1.3.5. 实际测试中的复杂情况。（指定表字段）

• 使用 --number-int-cols 选项，指定表中会包含 4 个 int 型的列。
• 使用 --number-char-cols 选项，指定表中会包含 35 个 char 型的列。
• 使用 --engine 选项，指定针对何种存储引擎进行测试。

mysqlslap –uroot -proot --concurrency=50 --iterations=1 --number-int-cols=2--number-char-cols=1 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=myisam,innodb --number-of-queries=200 --only-print

1.4. 测试结果说明：

注意：通过mysqlslap工具对mysql server进行压力测试，可以通过--concurrency、--number-of-queries等选项的值查看每次测试的结果，通过反复测试、优化得出mysql server的最大并发数。

如果mysqlslap工具输出结果为Segmentation fault (core dumped)基本表示超出mysql server的负载。

常见问题说明：
在用mysqlslap对mysql进行压力测试遇到mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket

解决方法一：

由于linux上连接过多，所以增加linux上打开文件数，，前提不能超过系统的最大限制cat /proc/sys/fs/file-max
[root@dbtest ~]# vim /etc/security/limits.conf 添加
root hard nofile 20000
root soft nofile 20000

目前用这个方法还是没有用，正在继续查找原因