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

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



mysqlslap的缺點還是挺明顯的,比如,不可以設定持續時間,比如24小時,表的數目不能指定等。
其最明顯的優點是可以對不同的儲存引擎做出對比。

作者公眾號(持續更新)

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

相關文章