mysqlslap是一个诊断程序,旨在模拟MySQL服务器的客户端负载并报告每个阶段的时间。就像多个客户端正在访问服务器一样。一些选项,例如–create 或–query使您能够指定包含SQL语句的字符串或包含语句的文件。如果指定文件,则默认情况下该文件每行必须包含一个语句。(也就是说,隐式语句定界符是换行符。)使用该 --delimiter选项可以指定其他定界符,这使您可以指定跨越多行的语句或将多条语句放在一行上。您不能在文件中包含注释; mysqlslap无法理解它们。
mysqlslap运行在三个阶段:
[root@test2 ~]# mysqlslap -a -uroot -p
Enter password:
Benchmark
Average number of seconds to run all queries: 0.004 seconds
Minimum number of seconds to run all queries: 0.004 seconds
Maximum number of seconds to run all queries: 0.004 seconds
Number of clients running queries: 1
Average number of queries per client: 0
[root@test2 ~]# mysqlslap -a -c 100 -uroot -p
Enter password:
Benchmark
Average number of seconds to run all queries: 0.340 seconds
Minimum number of seconds to run all queries: 0.340 seconds
Maximum number of seconds to run all queries: 0.340 seconds
Number of clients running queries: 100
Average number of queries per client: 0
[root@test2 ~]# mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb -uroot -p
Enter password:
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.167 seconds
Minimum number of seconds to run all queries: 0.146 seconds
Maximum number of seconds to run all queries: 0.204 seconds
Number of clients running queries: 50
Average number of queries per client: 20
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.168 seconds
Minimum number of seconds to run all queries: 0.166 seconds
Maximum number of seconds to run all queries: 0.173 seconds
Number of clients running queries: 100
Average number of queries per client: 10
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.241 seconds
Minimum number of seconds to run all queries: 0.217 seconds
Maximum number of seconds to run all queries: 0.285 seconds
Number of clients running queries: 50
Average number of queries per client: 20
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.265 seconds
Minimum number of seconds to run all queries: 0.251 seconds
Maximum number of seconds to run all queries: 0.288 seconds
Number of clients running queries: 100
Average number of queries per client: 10
[root@test2 ~]# mysqlslap -a --concurrency=100,200 --number-of-queries 5000 --iterations=5 -uroot -p
Enter password:
Benchmark
Average number of seconds to run all queries: 3.035 seconds
Minimum number of seconds to run all queries: 3.004 seconds
Maximum number of seconds to run all queries: 3.104 seconds
Number of clients running queries: 100
Average number of queries per client: 50
mysqlslap: Error when connecting to server: 1040 Too many connections
…
mysqlslap: Error when connecting to server: 1040 Too many connections
Benchmark
Average number of seconds to run all queries: 2.017 seconds
Minimum number of seconds to run all queries: 1.957 seconds
Maximum number of seconds to run all queries: 2.202 seconds
Number of clients running queries: 200
Average number of queries per client: 25
测试结果有报错,超过了连接数,查询参数最大连接数为151
mysql> show variables like ‘%connections%’;
±---------------------±------+
| Variable_name | Value |
±---------------------±------+
| max_connections | 151 |
| max_user_connections | 0 |
±---------------------±------+
2 rows in set (0.00 sec)
修改最大连接数为250
mysql> set global max_connections=250;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like ‘%connections%’;
±---------------------±------+
| Variable_name | Value |
±---------------------±------+
| max_connections | 250 |
| max_user_connections | 0 |
±---------------------±------+
2 rows in set (0.00 sec)
执行测试步骤
[root@test2 ~]# mysqlslap -h127.0.0.1 -uroot -p --concurrency=200 --iterations=1 --auto-generate-sql --auto-generate-sql-load-type=mixed --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=5000
Enter password:
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 3.224 seconds
Minimum number of seconds to run all queries: 3.224 seconds
Maximum number of seconds to run all queries: 3.224 seconds
Number of clients running queries: 200
Average number of queries per client: 25
自定义sql脚本
[root@test2 ~]# cat /tmp/q.sql
select * from test1.tb_9001;
执行测试
[root@test2 ~]# mysqlslap -h127.0.0.1 -uroot -p --concurrency=100 --iterations=1 --create-schema=test1 --query=/tmp/q.sql --engine=innodb --number-of-queries=5000
Enter password:
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 14.167 seconds
Minimum number of seconds to run all queries: 14.167 seconds
Maximum number of seconds to run all queries: 14.167 seconds
Number of clients running queries: 100
Average number of queries per client: 50
语法: mysqlslap [OPTIONS]
选项名称 | 参数说明 | 备注 | 弃用 |
---|---|---|---|
-a,–auto-generate-sql | 文件中未提供或使用命令选项时自动生成SQL语句 | ||
–auto-generate-sql-add-autoincrement | 将AUTO_INCREMENT列添加到自动生成的表中 | ||
–auto-generate-sql-execute-number | 指定自动生成多少查询 | ||
–auto-generate-sql-guid-primary | 将基于GUID的主键添加到自动生成的表中 | ||
–auto-generate-sql-load-type | 指定测试负载类型 | ||
–auto-generate-sql-secondary-indexes | 指定要添加到自动生成的表中的二级索引的数量 | ||
–auto-generate-sql-unique-query-number | 自动测试生成多少个不同的查询 | ||
–auto-generate-sql-unique-write-number | 为–auto-generate-sql-write-number生成多少个不同的查询 | ||
–auto-generate-sql-write-number | 每个线程要执行多少行插入 | ||
–commit | 提交前要执行多少条语句 | ||
-C,–compress | 压缩客户端和服务器之间发送的所有信息 | ||
-c,–concurrency | 发出SELECT语句时要模拟的客户端数量 | ||
–create | 包含用于创建表的语句的文件或字符串 | ||
–create-schema | 运行测试的架构 | ||
–csv | 生成以逗号分隔的值格式的输出 | ||
–debug | 编写调试日志 | ||
–debug-check | 程序退出时打印调试信息 | ||
-T,–debug-info | 程序退出时打印调试信息,内存和CPU统计信息 | ||
–default-auth | 身份验证插件使用 | ||
–defaults-extra-file | 除常规选项文件外,还读取命名的选项文件 | ||
–defaults-file | 只读命名的选项文件 | ||
–defaults-group-suffix | 选项组后缀值 | ||
-F,–delimiter | 在SQL语句中使用的定界符 | ||
–detach | 在每条N条语句后分离(关闭并重新打开)每个连接 | ||
–enable-cleartext-plugin | 启用明文身份验证插件 | ||
-e,–engine | 用于创建表的存储引擎 | ||
–get-server-public-key | 从服务器请求RSA公钥 | 5.7.23 | |
–help | 显示帮助信息并退出 | ||
-h,–host | MySQL服务器所在的主机 | ||
-i,–iterations | 运行测试的次数 | ||
–login-path | 从.mylogin.cnf中读取登录路径选项 | ||
–no-defaults | 不读取选项文件 | ||
–no-drop | 不要删除测试运行期间创建的任何架构 | ||
-x,–number-char-cols | 如果指定了–auto-generate-sql,则要使用的VARCHAR列数 | ||
-y,–number-int-cols | 如果指定了–auto-generate-sql,则要使用的INT列数 | ||
–number-of-queries | 将每个客户端限制为大约此查询数量 | ||
–only-print | 不要连接到数据库。mysqlslap只打印它会做的事情 | ||
-p,–password | 连接服务器时使用的密码 | ||
–pipe | 使用命名管道连接到服务器(仅Windows) | ||
–plugin-dir | 安装插件的目录 | ||
-P,–port | 用于连接的TCP / IP端口号 | ||
–post-query | 包含要在测试完成后执行的语句的文件或字符串 | ||
–post-system | 测试完成后使用system()执行的字符串 | ||
–pre-query | 包含要在运行测试之前执行的语句的文件或字符串 | ||
–pre-system | 在运行测试之前使用system()执行的字符串 | ||
–print-defaults | 打印默认选项 | ||
–protocol | 使用的传输协议 | ||
-q,–query | 包含用于检索数据的SELECT语句的文件或字符串 | ||
–secure-auth | 不要以旧(4.1之前)格式向服务器发送密码 | 是 | |
–server-public-key-path | 包含RSA公钥的文件的路径名 | 5.7.23 | |
–shared-memory-base-name | 共享内存连接的共享内存名称(仅Windows) | ||
-s,–silent | 静音模式 | ||
-S,–socket | Unix套接字文件或Windows命名管道使用 | ||
–sql-mode | 设置客户端会话的SQL模式 | ||
–ssl | 启用连接加密 | ||
–ssl-ca | 包含受信任的SSL证书颁发机构列表的文件 | ||
–ssl-capath | 包含受信任的SSL证书颁发机构证书文件的目录 | ||
–ssl-cert | 包含X.509证书的文件 | ||
–ssl-cipher | 连接加密的允许密码 | ||
–ssl-crl | 包含证书吊销列表的文件 | ||
–ssl-crlpath | 包含证书吊销列表文件的目录 | ||
–ssl-key | 包含X.509密钥的文件 | ||
–ssl-mode | 与服务器连接的所需安全状态 | 5.7.11 | |
–ssl-verify-server-cert | 根据服务器证书的通用名身份验证主机名 | ||
–tls-version | 允许的TLS协议进行加密连接 | 5.7.10 | |
-u,–user | 连接服务器时使用的MySQL用户名 | ||
-v,–verbose | 详细模式 | ||
-V,–version | 显示版本信息并退出 |