天达云 科技型企业 | 亚太互联网络信息中心(APNIC)成员 | 注册免费体验
天达云首页 > 互联网学习教程 > MySQL数据库 > 官方压测工具mysqlslap介绍及使用

官方压测工具mysqlslap介绍及使用
更新：HHH   时间：2023-1-7

mysqlslap是官方的一款做压力测试的工具，可以模拟多个客户端进行并发测试。
比如可以模拟多少并发，执行多少次，针对某个存储引擎……等等


比如对myisam引擎做并发量为100，查询次数为1000
--concurrency用于指定并发量，--number-of-queries=1000用于指定查询次数，--engine用于指定存储引擎
-a则是自动生成SQL（当未提供file或command时）
  1. [op@sAno1y ~]$ mysqlslap -a --concurrency=100 --number-of-queries=1000 --engine=myisam -uroot -proot
  2. Warning: Using a password on the command line interface can be insecure.
  3. Benchmark
  4. Running for engine myisam
  5. Average number of seconds to run all queries: 0.167 seconds
  6. Minimum number of seconds to run all queries: 0.167 seconds
  7. Maximum number of seconds to run all queries: 0.167 seconds
  8. Number of clients running queries: 100
  9. Average number of queries per client: 10
  11. Benchmark
  12. Running for engine myisam
  13. Average number of seconds to run all queries: 0.170 seconds
  14. Minimum number of seconds to run all queries: 0.170 seconds
  15. Maximum number of seconds to run all queries: 0.170 seconds
  16. Number of clients running queries: 200
  17. Average number of queries per client: 5

当然可以做迭代，在一次测试中输出：
其中--concurrency可以写作--concurrency=128,256,512诸如此类，而--engine也可以迭代写作--engine=innodb,myisam,memory等。
比如：
多加了的--debug-info参数可以使测试结果变得更加详尽。
  1. [op@sAno1y ~]$ mysqlslap -a --concurrency=100,200 --iterations=1 --number-of-queries=1000 --engine=myisam,innodb,memory --debug-info -uroot -proot
  2. Warning: Using a password on the command line interface can be insecure.
  3. Benchmark
  4. Running for engine myisam
  5. Average number of seconds to run all queries: 0.160 seconds
  6. Minimum number of seconds to run all queries: 0.160 seconds
  7. Maximum number of seconds to run all queries: 0.160 seconds
  8. Number of clients running queries: 100
  9. Average number of queries per client: 10
  11. Benchmark
  12. Running for engine myisam
  13. Average number of seconds to run all queries: 0.164 seconds
  14. Minimum number of seconds to run all queries: 0.164 seconds
  15. Maximum number of seconds to run all queries: 0.164 seconds
  16. Number of clients running queries: 200
  17. Average number of queries per client: 5
  19. Benchmark
  20. Running for engine innodb
  21. Average number of seconds to run all queries: 0.787 seconds
  22. Minimum number of seconds to run all queries: 0.787 seconds
  23. Maximum number of seconds to run all queries: 0.787 seconds
  24. Number of clients running queries: 100
  25. Average number of queries per client: 10
  27. Benchmark
  28. Running for engine innodb
  29. Average number of seconds to run all queries: 1.154 seconds
  30. Minimum number of seconds to run all queries: 1.154 seconds
  31. Maximum number of seconds to run all queries: 1.154 seconds
  32. Number of clients running queries: 200
  33. Average number of queries per client: 5
  35. Benchmark
  36. Running for engine memory
  37. Average number of seconds to run all queries: 0.140 seconds
  38. Minimum number of seconds to run all queries: 0.140 seconds
  39. Maximum number of seconds to run all queries: 0.140 seconds
  40. Number of clients running queries: 100
  41. Average number of queries per client: 10
  43. Benchmark
  44. Running for engine memory
  45. Average number of seconds to run all queries: 0.159 seconds
  46. Minimum number of seconds to run all queries: 0.159 seconds
  47. Maximum number of seconds to run all queries: 0.159 seconds
  48. Number of clients running queries: 200
  49. Average number of queries per client: 5
  52. User time 0.23, System time 0.25
  53. Maximum resident set size 12788, Integral resident set size 0
  54. Non-physical pagefaults 11916, Physical pagefaults 0, Swaps 0
  55. Blocks in 0 out 0, Messages in 0 out 0, Signals 0
  56. Voluntary context switches 25055, Involuntary context switches 99
其中Average number of queries per client = number-of-queries / concurrency 
即平均一个client的查询 = 总查询 ÷ 并发

另外--auto-general-sql-load-type这个参数也可以选填，默认为mixed。
其他可选项为：update、write、key、read。

附录：
mysqlslap option：

  1. mysqlslap Ver 1.0 Distrib 5.6.26, for Linux (x86_64)
  2. Copyright (c) 2005, 2015, Oracle and/or its affiliates. All rights reserved.
  5. Oracle is a registered trademark of Oracle Corporation and/or its
  6. affiliates. Other names may be trademarks of their respective
  7. owners.
  10. Run a query multiple times against the server.
  13. Usage: mysqlslap [OPTIONS]
  16. Default options are read from the following files in the given order:
  17. /etc/my.cnf /etc/mysql/my.cnf /home/op/softwares/mysql/etc/my.cnf ~/.my.cnf
  18. The following groups are read: mysqlslap client
  19. The following options may be given as the first argument:
  20. --print-defaults Print the program argument list and exit.
  21. --no-defaults Don't read default options from any option file,
  22. except for login file.
  23. --defaults-file=# Only read default options from the given file #.
  24. --defaults-extra-file=# Read this file after the global files are read.
  25. --defaults-group-suffix=#
  26. Also read groups with concat(group, suffix)
  27. --login-path=# Read this path from the login file.
  30. -?, --help Display this help and exit.
  31. -a, --auto-generate-sql
  32. Generate SQL where not supplied by file or command line.
  33. --auto-generate-sql-add-autoincrement
  34. Add an AUTO_INCREMENT column to auto-generated tables.
  35. --auto-generate-sql-execute-number=#
  36. Set this number to generate a set number of queries to
  37. run.
  38. --auto-generate-sql-guid-primary
  39. Add GUID based primary keys to auto-generated tables.
  40. --auto-generate-sql-load-type=name
  41. Specify test load type: mixed, update, write, key, or
  42. read; default is mixed.
  43. --auto-generate-sql-secondary-indexes=#
  44. Number of secondary indexes to add to auto-generated
  45. tables.
  46. --auto-generate-sql-unique-query-number=#
  47. Number of unique queries to generate for automatic tests.
  48. --auto-generate-sql-unique-write-number=#
  49. Number of unique queries to generate for
  50. auto-generate-sql-write-number.
  51. --auto-generate-sql-write-number=#
  52. Number of row inserts to perform for each thread (default
  53. is 100).
  54. --commit=# Commit records every X number of statements.
  55. -C, --compress Use compression in server/client protocol.
  56. -c, --concurrency=name
  57. Number of clients to simulate for query to run.
  58. --create=name File or string to use create tables.
  59. --create-schema=name
  60. Schema to run tests in.
  61. --csv[=name] Generate CSV output to named file or to stdout if no file
  62. is named.
  63. -#, --debug[=#] This is a non-debug version. Catch this and exit.
  64. --debug-check Check memory and open file usage at exit.
  65. -T, --debug-info Print some debug info at exit.
  66. --default-auth=name Default authentication client-side plugin to use.
  67. -F, --delimiter=name
  68. Delimiter to use in SQL statements supplied in file or
  69. command line.
  70. --detach=# Detach (close and reopen) connections after X number of
  71. requests.
  72. --enable-cleartext-plugin
  73. Enable/disable the clear text authentication plugin.
  74. -e, --engine=name Storage engine to use for creating the table.
  75. -h, --host=name Connect to host.
  76. -i, --iterations=# Number of times to run the tests.
  77. --no-drop Do not drop the schema after the test.
  78. -x, --number-char-cols=name
  79. Number of VARCHAR columns to create in table if
  80. specifying --auto-generate-sql.
  81. -y, --number-int-cols=name
  82. Number of INT columns to create in table if specifying
  83. --auto-generate-sql.
  84. --number-of-queries=#
  85. Limit each client to this number of queries (this is not
  86. exact).
  87. --only-print Do not connect to the databases, but instead print out
  88. what would have been done.
  89. -p, --password[=name]
  90. Password to use when connecting to server. If password is
  91. not given it's asked from the tty.
  92. --plugin-dir=name Directory for client-side plugins.
  93. -P, --port=# Port number to use for connection.
  94. --post-query=name Query to run or file containing query to execute after
  95. tests have completed.
  96. --post-system=name system() string to execute after tests have completed.
  97. --pre-query=name Query to run or file containing query to execute before
  98. running tests.
  99. --pre-system=name system() string to execute before running tests.
  100. --protocol=name The protocol to use for connection (tcp, socket, pipe,
  101. memory).
  102. -q, --query=name Query to run or file containing query to run.
  103. --secure-auth Refuse client connecting to server if it uses old
  104. (pre-4.1.1) protocol.
  105. (Defaults to on; use --skip-secure-auth to disable.)
  106. -s, --silent Run program in silent mode - no output.
  107. -S, --socket=name The socket file to use for connection.
  108. --ssl Enable SSL for connection (automatically enabled with
  109. other flags).
  110. --ssl-ca=name CA file in PEM format (check OpenSSL docs, implies
  111. --ssl).
  112. --ssl-capath=name CA directory (check OpenSSL docs, implies --ssl).
  113. --ssl-cert=name X509 cert in PEM format (implies --ssl).
  114. --ssl-cipher=name SSL cipher to use (implies --ssl).
  115. --ssl-key=name X509 key in PEM format (implies --ssl).
  116. --ssl-crl=name Certificate revocation list (implies --ssl).
  117. --ssl-crlpath=name Certificate revocation list path (implies --ssl).
  118. --ssl-verify-server-cert
  119. Verify server's "Common Name" in its cert against
  120. hostname used when connecting. This option is disabled by
  121. default.
  122. -u, --user=name User for login if not current user.
  123. -v, --verbose More verbose output; you can use this multiple times to
  124. get even more verbose output.
  125. -V, --version Output version information and exit.



mysqlslap的缺点还是挺明显的，比如，不可以设置持续时间，比如24小时，表的数目不能指定等。
其最明显的优点是可以对不同的存储引擎做出对比。

作者公众号（持续更新）
返回MySQL数据库教程...
400-805-1963
7 * 24小时全天全国服务热线400电话
天达云·专业的云计算服务平台 全国400服务电话：400-805-1963
Copyright © 2011-2026 Tdyun.com. All Rights Reserved. 天达云 版权所有 粤ICP备18083267号-7