官方压测工具mysqlslap介绍及使用

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小时，表的数目不能指定等。
其最明显的优点是可以对不同的存储引擎做出对比。

作者公众号（持续更新）