mysql自带的性能测试工具mysqlslap执行压力测试
袁奇文
对于按照配置的数据库，需要对其基本的性能有个基本的了解，当系统整体性能下降或者不能提升的时候，就需要考虑，是否由于数据库的性能限制导致的结果，此时最好有数据库的性能测试结果作为参考。
mysqlslap [options]
-?, --help Display this help and exit.
-a, --auto-generate-sql #自动生成测试表和数据
--auto-generate-sql-add-autoincrement #对生成的表自动添加auto_increment列
--auto-generate-sql-execute-number=#
Set this number to generate a set number of queries to
run.
--auto-generate-sql-guid-primary
Add GUID based primary keys to auto-generated tables.
--auto-generate-sql-load-type=name #测试语句的类型: mixed, update, write, key, or
read; default is mixed.
--auto-generate-sql-secondary-indexes=#
Number of secondary indexes to add to auto-generated
tables.
--auto-generate-sql-unique-query-number=#
Number of unique queries to generate for automatic tests.
--auto-generate-sql-unique-write-number=#
Number of unique queries to generate for
auto-generate-sql-write-number.
--auto-generate-sql-write-number=#
Number of row inserts to perform for each thread (default
is 100).
--commit=# Commit records every X number of statements.
-C, --compress Use compression in server/client protocol.
-c, --concurrency=name
Number of clients to simulate for query to run.
--create=name File or string to use create tables.
--create-schema=name
Schema to run tests in.
--csv[=name] Generate CSV output to named file or to stdout if no file
is named.
-#, --debug[=#] This is a non-debug version. Catch this and exit.
--debug-check This is a non-debug version. Catch this and exit.
-T, --debug-info This is a non-debug version. Catch this and exit.
--default-auth=name Default authentication client-side plugin to use.
-F, --delimiter=name
Delimiter to use in SQL statements supplied in file or
command line.
--detach=# Detach (close and reopen) connections after X number of
requests.
--enable-cleartext-plugin
Enable/disable the clear text authentication plugin.
-e, --engine=name Storage engine to use for creating the table.
-h, --host=name Connect to host.
-i, --iterations=# Number of times to run the tests.
--no-drop Do not drop the schema after the test.
-x, --number-char-cols=name
Number of VARCHAR columns to create in table if
specifying --auto-generate-sql.
-y, --number-int-cols=name
Number of INT columns to create in table if specifying
--auto-generate-sql.
--number-of-queries=#
Limit each client to this number of queries (this is not
exact).
--only-print Do not connect to the databases, but instead print out
what would have been done.
-p, --password[=name]
Password to use when connecting to server. If password is
not given it's asked from the tty.
--plugin-dir=name Directory for client-side plugins.
-P, --port=# Port number to use for connection.
--post-query=name Query to run or file containing query to execute after
tests have completed.
--post-system=name system() string to execute after tests have completed.
--pre-query=name Query to run or file containing query to execute before
running tests.
--pre-system=name system() string to execute before running tests.
--protocol=name The protocol to use for connection (tcp, socket, pipe,
memory).
-q, --query=name Query to run or file containing query to run.
--secure-auth Refuse client connecting to server if it uses old
(pre-4.1.1) protocol. Deprecated. Always TRUE
-s, --silent Run program in silent mode - no output.
-S, --socket=name The socket file to use for connection.
--sql-mode=name Specify sql-mode to run mysqlslap tool.
--ssl-mode=name SSL connection mode.
--ssl Deprecated. Use --ssl-mode instead.
(Defaults to on; use --skip-ssl to disable.)
--ssl-verify-server-cert
Deprecated. Use --ssl-mode=VERIFY_IDENTITY instead.
--ssl-ca=name CA file in PEM format.
--ssl-capath=name CA directory.
--ssl-cert=name X509 cert in PEM format.
--ssl-cipher=name SSL cipher to use.
--ssl-key=name X509 key in PEM format.
--ssl-crl=name Certificate revocation list.
--ssl-crlpath=name Certificate revocation list path.
--tls-version=name TLS version to use, permitted values are: TLSv1, TLSv1.1,
TLSv1.2
--server-public-key-path=name
File path to the server public RSA key in PEM format.
--get-server-public-key
Get server public key
-u, --user=name User for login if not current user.
-v, --verbose More verbose output; you can use this multiple times to
get even more verbose output.
-V, --version Output version information and exit.1. 单线程测试
# ./mysqlslap -a -uroot -p
Enter password:
Benchmark
Average number of seconds to run all queries: 0.012 seconds
Minimum number of seconds to run all queries: 0.012 seconds
Maximum number of seconds to run all queries: 0.012 seconds
Number of clients running queries: 1
Average number of queries per client: 02. 多线程测试
# ./mysqlslap -a -c 100 -uroot -p
Enter password:
Benchmark
Average number of seconds to run all queries: 1.608 seconds
Minimum number of seconds to run all queries: 1.608 seconds
Maximum number of seconds to run all queries: 1.608 seconds
Number of clients running queries: 100
Average number of queries per client: 0可以看出100个并发测试时，响应时间明显比单个线程测试时的响应时间长。
3. 迭代测试
# ./mysqlslap -a -i 10 -uroot -p
Enter password:
Benchmark
Average number of seconds to run all queries: 0.012 seconds
Minimum number of seconds to run all queries: 0.012 seconds
Maximum number of seconds to run all queries: 0.013 seconds
Number of clients running queries: 1
Average number of queries per client: 04. 多线程迭代测试
# ./mysqlslap -a -c 100 -i 10 -uroot -p
Enter password: 5. 不同并发用户数的测试
# ./mysqlslap -a --concurrency=50,100 --number-of-queries 1000 -uroot -p
Enter password:
Benchmark
Average number of seconds to run all queries: 1.312 seconds
Minimum number of seconds to run all queries: 1.312 seconds
Maximum number of seconds to run all queries: 1.312 seconds
Number of clients running queries: 50
Average number of queries per client: 20
Benchmark
Average number of seconds to run all queries: 1.581 seconds
Minimum number of seconds to run all queries: 1.581 seconds
Maximum number of seconds to run all queries: 1.581 seconds
Number of clients running queries: 100
Average number of queries per client: 106. 不同引擎的测试对比
# ./mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb,ndbcluster -uroot -p
Enter password:
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.065 seconds
Minimum number of seconds to run all queries: 0.063 seconds
Maximum number of seconds to run all queries: 0.074 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.072 seconds
Minimum number of seconds to run all queries: 0.071 seconds
Maximum number of seconds to run all queries: 0.073 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.744 seconds
Minimum number of seconds to run all queries: 0.641 seconds
Maximum number of seconds to run all queries: 0.849 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.389 seconds
Minimum number of seconds to run all queries: 0.318 seconds
Maximum number of seconds to run all queries: 0.473 seconds
Number of clients running queries: 100
Average number of queries per client: 10
Benchmark
Running for engine ndbcluster
Average number of seconds to run all queries: 1.314 seconds
Minimum number of seconds to run all queries: 1.313 seconds
Maximum number of seconds to run all queries: 1.318 seconds
Number of clients running queries: 50
Average number of queries per client: 20
Benchmark
Running for engine ndbcluster
Average number of seconds to run all queries: 1.581 seconds
Minimum number of seconds to run all queries: 1.564 seconds
Maximum number of seconds to run all queries: 1.615 seconds
Number of clients running queries: 100
Average number of queries per client: 10可以看出，平均响应速度最快的是myisam引擎，其次是innodb，最慢的是ndbcluster。ndbcluster引擎在cluster环境中，受网络环境的影响很大。
7. 查询性能测试（select）
ndbcluster引擎测试：
./mysqlslap --engine=ndbcluster -uroot -p --auto-generate-sql-guid-primary --concurrency=50,100 --number-of-queries=10000 --iterations=2 --number-char-cols=10 --number-int-cols=10 --auto-generate-sql --create-schema=rdb10 --auto-generate-sql-load-type=read
Enter password:
Benchmark
Running for engine ndbcluster
Average number of seconds to run all queries: 54.835 seconds
Minimum number of seconds to run all queries: 47.827 seconds
Maximum number of seconds to run all queries: 61.844 seconds
Number of clients running queries: 50
Average number of queries per client: 200
Benchmark
Running for engine ndbcluster
Average number of seconds to run all queries: 59.451 seconds
Minimum number of seconds to run all queries: 57.080 seconds
Maximum number of seconds to run all queries: 61.822 seconds
Number of clients running queries: 100
Average number of queries per client: 100innodb引擎测试：
# ./mysqlslap --engine=innodb -uroot -p --auto-generate-sql-guid-primary --concurrency=50,100 --number-of-queries=10000 --iterations=2 --number-char-cols=10 --number-int-cols=10 --auto-generate-sql --create-schema=rdb10 --auto-generate-sql-load-type=read
Enter password:
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 1.886 seconds
Minimum number of seconds to run all queries: 1.877 seconds
Maximum number of seconds to run all queries: 1.895 seconds
Number of clients running queries: 50
Average number of queries per client: 200
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 2.022 seconds
Minimum number of seconds to run all queries: 1.878 seconds
Maximum number of seconds to run all queries: 2.167 seconds
Number of clients running queries: 100
Average number of queries per client: 100可以看出，innodb引擎的查询效率更高。
8. 写库测试（insert）
ndbcluster引擎测试：
# ./mysqlslap --engine=ndbcluster -uroot -p --auto-generate-sql-guid-primary --concurrency=50,100 --number-of-queries=10000 --iterations=2 --number-char-cols=10 --number-int-cols=10 --auto-generate-sql --create-schema=rdb10 --auto-generate-sql-load-type=write
Enter password:
Benchmark
Running for engine ndbcluster
Average number of seconds to run all queries: 1.529 seconds
Minimum number of seconds to run all queries: 1.528 seconds
Maximum number of seconds to run all queries: 1.531 seconds
Number of clients running queries: 50
Average number of queries per client: 200
Benchmark
Running for engine ndbcluster
Average number of seconds to run all queries: 1.475 seconds
Minimum number of seconds to run all queries: 1.469 seconds
Maximum number of seconds to run all queries: 1.482 seconds
Number of clients running queries: 100
Average number of queries per client: 100innodb写入测试：
# ./mysqlslap --engine=innodb -uroot -p --auto-generate-sql-guid-primary --concurrency=50,100 --number-of-queries=10000 --iterations=2 --number-char-cols=10 --number-int-cols=10 --auto-generate-sql --create-schema=rdb10 --auto-generate-sql-load-type=write
Enter password:
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 17.523 seconds
Minimum number of seconds to run all queries: 17.409 seconds
Maximum number of seconds to run all queries: 17.637 seconds
Number of clients running queries: 50
Average number of queries per client: 200
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 15.521 seconds
Minimum number of seconds to run all queries: 15.106 seconds
Maximum number of seconds to run all queries: 15.937 seconds
Number of clients running queries: 100
Average number of queries per client: 100可以看出ndbcluster引擎的写入效率更高。
9. 更新性能测试（update）
ndbcluster引擎测试：
# ./mysqlslap --engine=ndbcluster -uroot -p --auto-generate-sql-guid-primary --concurrency=50,100 --number-of-queries=10000 --iterations=2 --number-char-cols=10 --number-int-cols=10 --auto-generate-sql --create-schema=rdb10 --auto-generate-sql-load-type=update
Enter password:
Benchmark
Running for engine ndbcluster
Average number of seconds to run all queries: 1.474 seconds
Minimum number of seconds to run all queries: 1.474 seconds
Maximum number of seconds to run all queries: 1.475 seconds
Number of clients running queries: 50
Average number of queries per client: 200
Benchmark
Running for engine ndbcluster
Average number of seconds to run all queries: 1.426 seconds
Minimum number of seconds to run all queries: 1.424 seconds
Maximum number of seconds to run all queries: 1.428 seconds
Number of clients running queries: 100
Average number of queries per client: 100innodb引擎测试：
# ./mysqlslap --engine=innodb -uroot -p --auto-generate-sql-guid-primary --concurrency=50,100 --number-of-queries=10000 --iterations=2 --number-char-cols=10 --number-int-cols=10 --auto-generate-sql --create-schema=rdb10 --auto-generate-sql-load-type=update
Enter password:
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 17.469 seconds
Minimum number of seconds to run all queries: 17.227 seconds
Maximum number of seconds to run all queries: 17.712 seconds
Number of clients running queries: 50
Average number of queries per client: 200
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 15.241 seconds
Minimum number of seconds to run all queries: 15.137 seconds
Maximum number of seconds to run all queries: 15.346 seconds
Number of clients running queries: 100
Average number of queries per client: 100可以看出ndbcluster引擎性能比innodb更好。
类似资料：