MySQL性能优化最佳实践 - 02 MySQL数据库性能衡量
测试服务器(或虚拟机)的QPS峰值
利用sysbench压测工具模拟SELECT操作
# 已有test库的话先drop掉
drop database test;
create database test;
# prepare准备阶段,构建压测环境
sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/select.lua \
--oltp-table-size=20000 --mysql-table-engine=innodb --db-driver=mysql \
--mysql-user=root --mysql-password=root123 --mysql-port=3306 \
--mysql-host=10.245.231.202 --mysql-db=test \
--events=0 --time=60 --oltp-tables-count=20 --report-interval=10 --threads=2 prepare
# 开始压测
sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/select.lua \
--oltp-table-size=20000 --mysql-table-engine=innodb --db-driver=mysql \
--mysql-user=root --mysql-password=root123 --mysql-port=3306 \
--mysql-host=10.245.231.202 --mysql-db=test \
--events=0 --time=60 --oltp-tables-count=20 --report-interval=10 --threads=2 run
sysbench 1.0.5 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 2
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 2 tps: 8087.29 qps: 8087.29 (r/w/o: 8087.29/0.00/0.00) lat (ms,95%): 0.34 err/ s: 0.00 reconn/s: 0.00
[ 20s ] thds: 2 tps: 6949.28 qps: 6949.28 (r/w/o: 6949.28/0.00/0.00) lat (ms,95%): 0.35 err/ s: 0.00 reconn/s: 0.00
[ 30s ] thds: 2 tps: 7251.71 qps: 7251.71 (r/w/o: 7251.71/0.00/0.00) lat (ms,95%): 0.34 err/ s: 0.00 reconn/s: 0.00
[ 40s ] thds: 2 tps: 6927.19 qps: 6927.19 (r/w/o: 6927.19/0.00/0.00) lat (ms,95%): 0.35 err/ s: 0.00 reconn/s: 0.00
[ 50s ] thds: 2 tps: 7387.64 qps: 7387.64 (r/w/o: 7387.64/0.00/0.00) lat (ms,95%): 0.32 err/ s: 0.00 reconn/s: 0.00
[ 60s ] thds: 2 tps: 10171.21 qps: 10171.21 (r/w/o: 10171.21/0.00/0.00) lat (ms,95%): 0.26 err/ s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 467780
write: 0
other: 0
total: 467780
transactions: 467780 (7795.29 per sec.)
queries: 467780 (7795.29 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.0008s
total number of events: 467780
Latency (ms):
min: 0.11
avg: 0.25
max: 27.63
95th percentile: 0.34
sum: 119092.82
Threads fairness:
events (avg/stddev): 233890.0000/242.00
execution time (avg/stddev): |