Mysql sysbench 压测

  • 目录
  • 工具安装
  • 执行压测
  • 针对优化
  • 示例结果

数据库指标

对于mysql压力测试,大多用sysbench/tpcc-mysql工具。主要针对数据库OLTP(在线事务处理)指标。

1
2
qps : query per second 查询量
tps: transaction per second 事务量

工具安装

1
2
3
yum install sysbench -y
[root@mysql sysbench]# sysbench --version
sysbench 1.0.9

sysbench压测时会调用内置lua脚本, 这些脚本为于/etc/share/sysbench目录下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/usr/share/sysbench
[root@mysql sysbench]# ll
total 64
-rwxr-xr-x 1 root root 1452 Sep 18 12:29 bulk_insert.lua
-rw-r--r-- 1 root root 13762 Sep 18 12:29 oltp_common.lua
-rwxr-xr-x 1 root root 1290 Sep 18 12:29 oltp_delete.lua
-rwxr-xr-x 1 root root 2018 Sep 18 12:29 oltp_insert.lua
-rwxr-xr-x 1 root root 1265 Sep 18 12:29 oltp_point_select.lua
-rwxr-xr-x 1 root root 1649 Sep 18 12:29 oltp_read_only.lua
-rwxr-xr-x 1 root root 1824 Sep 18 12:29 oltp_read_write.lua
-rwxr-xr-x 1 root root 1118 Sep 18 12:29 oltp_update_index.lua
-rwxr-xr-x 1 root root 1127 Sep 18 12:29 oltp_update_non_index.lua
-rwxr-xr-x 1 root root 1440 Sep 18 12:29 oltp_write_only.lua
-rwxr-xr-x 1 root root 1919 Sep 18 12:29 select_random_points.lua
-rwxr-xr-x 1 root root 2118 Sep 18 12:29 select_random_ranges.lua
drwxr-xr-x 4 root root 4096 Nov 10 02:57 tests

执行压测

方便压测时找到内置lua脚本,以下命令都在/etc/share/sysbench目录下执行

在远端连接数据库时,授权

1
grant all on test.* to root@'%' identified by '123';

生成压测数据

1
2
cd /usr/share/sysbench/
sysbench oltp_read_write.lua --mysql-host=127.0.0.1 --mysql-port=3306 --db-driver=mysql --mysql-db=test --mysql-user=root --mysql-password=MyNewPass4! --table_size=50000 --tables=10 --threads=300 --time=60 --report-interval=10 prepare

执行压测

1
sysbench oltp_read_write.lua --mysql-host=127.0.0.1 --mysql-port=3306 --db-driver=mysql --mysql-db=test --mysql-user=root --mysql-password=MyNewPass4! --table_size=50000 --tables=10 --threads=300 --time=60 --report-interval=10  run

清理压测数据

1
sysbench oltp_read_write.lua --mysql-host=127.0.0.1 --mysql-port=3306 --db-driver=mysql --mysql-db=test --mysql-user=root --mysql-password=MyNewPass4! --table_size=50000 --tables=10 --threads=300 --time=60 --report-interval=10  cleanup

优化数据库

在压测过程中可能出现一些错误,连接数不够,max_prepared_stmt_count不够,可优化/etc/my.cnf 做适当调整

1
2
3
4
5
6
7
8
9
10
11
12
max_connections = 4096
max_prepared_stmt_count=100000 # 默认16384压测时需调大
query_cache_type = 1
query_cache_size = 10M

wsrep_slave_threads = 4
#wsrep_notify_cmd = /usr/local/bin/wsrep-notify.sh
max_connections = 10000
key_buffer_size = 64M
max_heap_table_size = 64M
tmp_table_size = 64M
innodb_buffer_pool_size = 8192M

示例结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
Initializing worker threads...

Threads started!

[ 10s ] thds: 300 tps: 198.33 qps: 5741.67 (r/w/o: 4335.25/830.02/576.41) lat (ms,95%): 3706.08 err/s: 81.77 reconn/s: 0.00
[ 20s ] thds: 300 tps: 222.61 qps: 6095.90 (r/w/o: 4553.90/924.16/617.84) lat (ms,95%): 4203.93 err/s: 102.31 reconn/s: 0.00
[ 30s ] thds: 300 tps: 221.01 qps: 5899.58 (r/w/o: 4399.84/899.83/599.92) lat (ms,95%): 4055.23 err/s: 93.20 reconn/s: 0.00
[ 40s ] thds: 300 tps: 198.06 qps: 5400.91 (r/w/o: 4032.79/827.03/541.09) lat (ms,95%): 4683.57 err/s: 89.98 reconn/s: 0.00
[ 50s ] thds: 300 tps: 186.14 qps: 5162.76 (r/w/o: 3866.39/783.36/513.00) lat (ms,95%): 5312.73 err/s: 90.12 reconn/s: 0.00
[ 60s ] thds: 300 tps: 158.49 qps: 4433.94 (r/w/o: 3325.93/670.45/437.56) lat (ms,95%): 5507.54 err/s: 78.99 reconn/s: 0.00
SQL statistics:
queries performed:
read: 246736
write: 50340
other: 33317
total: 330393
transactions: 12147 (195.34 per sec.)
queries: 330393 (5313.17 per sec.)
ignored errors: 5477 (88.08 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 62.1815s
total number of events: 12147

Latency (ms):
min: 16.10
avg: 1503.84
max: 14267.54
95th percentile: 4683.57
sum: 18267086.41

Threads fairness:
events (avg/stddev): 40.4900/6.29
execution time (avg/stddev): 60.8903/0.59