MySQL 性能
mysqlslap
mysqlslap is a diagnostic program designed to emulate client load for a MySQL server and to report the timing of each stage.
It works as if multiple clients are accessing the server.
通过模拟多个并发的客户端对 MySQL 服务器进行诊断,并报告每一个阶段的耗时
$ mysqlslap -a --concurrency=50 --number-of-queries 100 -i 5 -uroot -p
Enter password:
Benchmark
Average number of seconds to run all queries: 0.091 seconds
Minimum number of seconds to run all queries: 0.067 seconds
Maximum number of seconds to run all queries: 0.146 seconds
Number of clients running queries: 50
Average number of queries per client: 2
测试案例(使用自动造数的数据)
可通过私有的建表语句 or 造数语句进行测试,或者直接使用默认的造数形式
# 私有的建表语句和造数内容,模拟50个用户进行200次的查询
$ mysqlslap --delimiter=";" \
--create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)" \
--query="SELECT * FROM a" --concurrency=50 --iterations=200 -uroot -p
# 制造有2个int columns 和3个varchar的字段用于压测
$ mysqlslap --delimiter=";" \
--create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)" \
--query="SELECT * FROM a" --concurrency=50 --iterations=200 -uroot -p
Enter password:
Benchmark
Average number of seconds to run all queries: 0.027 seconds
Minimum number of seconds to run all queries: 0.020 seconds
Maximum number of seconds to run all queries: 0.052 seconds
Number of clients running queries: 50
Average number of queries per client: 1
测试案例(使用自己的测试数据)
# 对现有数据库查询性能状况如何
# --only-print 只打印会干点啥,不会实际执行
$ mysqlslap --delimiter=";" \
--create-schema="jarvis" \
--query="SELECT count(*) from test_student" --concurrency=1 --iterations=1 --only-print -uroot -p
Enter password:
Benchmark
Average number of seconds to run all queries: 0.060 seconds
Minimum number of seconds to run all queries: 0.060 seconds
Maximum number of seconds to run all queries: 0.060 seconds
Number of clients running queries: 1
Average number of queries per client: 1