官方壓測工具mysqlslap介紹及使用

神諭丶發表於2015-11-27
MySql
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小時，表的數目不能指定等。
其最明顯的優點是可以對不同的儲存引擎做出對比。

作者公眾號（持續更新）

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29773961/viewspace-1848017/，如需轉載，請註明出處，否則將追究法律責任。

相關文章