Fork me on GitHub

使用Sysbench进行MySQL压力测试

发表于 | 分类于 ， ， | | 阅读次数
| 字数统计 1,404 | 阅读时长预计 8

本文主要介绍了如何使用Sysbench进行MySQL压力测试。

安装完sysbench后,/usr/share/sysbench下对数据库压力测试的lua文件：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
1. bulk_insert.lua  批量写入操作
2. oltp_delete.lua 写入和删除并行操作
3. oltp_insert.lua  纯写入操作
4. oltp_point_select.lua  只读操作，条件为唯一索引列
5. oltp_read_only.lua  只读操作，包含聚合，去重等操作
6. oltp_read_write.lua 读写混合操作，最常用的脚本
7. oltp_update_index.lua 更新操作，通过主键进行更新
8. oltp_update_non_index.lua 更新操作，不通过索引列
9. oltp_write_only.lua 纯写操作，常用脚本，包括insert update delete
10. select_random_points.lua 随机集合只读操作，常用脚本，聚集索引列的selete in操作
11. select_random_ranges.lua 随机范围只读操作，常用脚本，聚集索引列的selete between操作

数据库测试分为3步：prepare(准备测试数据)，run(开始测试),cleanup(清除测试数据)

​ 参数解析：

​ –db-driver：用到的数据库类型

​ –mysql-host：数据库的IP

​ –mysql-port：数据库的端口

​ –mysql-socket：socket的路径

​ –mysql-user：数据库用户名

​ –mysql-password：用户密码

​ –mysql-db：数据库名字，默认为sysbench，需要提前创建创好

​ –tables：生成表的个数

​ –table-size：每个表的行数

​ –report-interval：每隔多久在屏幕打印一次信息

​ –time：压测时间

​ –threads：启动多少个线程，即模拟多少个用户

创建测试数据库

1
2
3
CREATE DATABASE sample DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
GRANT ALL PRIVILEGES ON  sample.*  TO 'app_user'@'%' IDENTIFIED BY 'admin_123'

准备测试数据： 在本地数据库的dba_test库中，初始化10张表（sbtest1~sbtest10），存储引擎是innodb，每张表50万数据。

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
[root@localhost sysbench]# sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-host=10.10.14.10 --mysql-port=3306  --mysql-user=app_user --mysql-password=admin_123 --mysql-db=sample --tables=10 --table-size=500000 --time=120 --report-interval=10 --threads=100 prepare
sysbench 1.0.7 (using bundled LuaJIT 2.1.0-beta2)
Initializing worker threads...
Creating table 'sbtest4'...
Creating table 'sbtest6'...
Creating table 'sbtest5'...
Creating table 'sbtest8'...
Creating table 'sbtest1'...
Creating table 'sbtest2'...
Creating table 'sbtest9'...
Creating table 'sbtest3'...
Creating table 'sbtest10'...
Creating table 'sbtest7'...
Inserting 500000 records into 'sbtest5'
Inserting 500000 records into 'sbtest6'
Inserting 500000 records into 'sbtest8'
Inserting 500000 records into 'sbtest9'
Inserting 500000 records into 'sbtest4'
Inserting 500000 records into 'sbtest10'
Inserting 500000 records into 'sbtest1'
Inserting 500000 records into 'sbtest3'
Inserting 500000 records into 'sbtest2'
Inserting 500000 records into 'sbtest7'
Creating a secondary index on 'sbtest9'...
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest3'...
Creating a secondary index on 'sbtest6'...
Creating a secondary index on 'sbtest10'...
Creating a secondary index on 'sbtest5'...
Creating a secondary index on 'sbtest8'...
Creating a secondary index on 'sbtest4'...
Creating a secondary index on 'sbtest1'...
Creating a secondary index on 'sbtest7'...

压测数据库：

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
[root@localhost sysbench]# sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-host=10.10.14.10 --mysql-port=3306  --mysql-user=app_user --mysql-password=admin_123 --mysql-db=sample --tables=10 --table-size=500000 --time=120 --report-interval=10 --threads=100 prepare
sysbench 1.0.7 (using bundled LuaJIT 2.1.0-beta2)
Initializing worker threads...
Creating table 'sbtest4'...
Creating table 'sbtest6'...
Creating table 'sbtest5'...
Creating table 'sbtest8'...
Creating table 'sbtest1'...
Creating table 'sbtest2'...
Creating table 'sbtest9'...
Creating table 'sbtest3'...
Creating table 'sbtest10'...
Creating table 'sbtest7'...
Inserting 500000 records into 'sbtest5'
Inserting 500000 records into 'sbtest6'
Inserting 500000 records into 'sbtest8'
Inserting 500000 records into 'sbtest9'
Inserting 500000 records into 'sbtest4'
Inserting 500000 records into 'sbtest10'
Inserting 500000 records into 'sbtest1'
Inserting 500000 records into 'sbtest3'
Inserting 500000 records into 'sbtest2'
Inserting 500000 records into 'sbtest7'
Creating a secondary index on 'sbtest9'...
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest3'...
Creating a secondary index on 'sbtest6'...
Creating a secondary index on 'sbtest10'...
Creating a secondary index on 'sbtest5'...
Creating a secondary index on 'sbtest8'...
Creating a secondary index on 'sbtest4'...
Creating a secondary index on 'sbtest1'...
Creating a secondary index on 'sbtest7'...
[root@localhost sysbench]# sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-host=10.10.14.10 --mysql-port=3306  --mysql-user=app_user --mysql-password=admin_123 --mysql-db=sample --tables=10 --table-size=500000 --time=120 --report-interval=10 --threads=100 run
sysbench 1.0.7 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 100
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 100 tps: 1463.35 qps: 29420.80 (r/w/o: 20611.00/5872.80/2937.00) lat (ms,95%): 118.92 err/s: 0.40 reconn/s: 0.00
[ 20s ] thds: 100 tps: 1606.45 qps: 32128.43 (r/w/o: 22493.22/6422.31/3212.90) lat (ms,95%): 102.97 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 1621.80 qps: 32395.26 (r/w/o: 22665.67/6486.49/3243.10) lat (ms,95%): 104.84 err/s: 0.20 reconn/s: 0.00
[ 40s ] thds: 100 tps: 1587.60 qps: 31800.89 (r/w/o: 22270.26/6354.62/3176.01) lat (ms,95%): 106.75 err/s: 0.30 reconn/s: 0.00
[ 50s ] thds: 100 tps: 1621.99 qps: 32449.93 (r/w/o: 22716.28/6489.07/3244.58) lat (ms,95%): 101.13 err/s: 0.40 reconn/s: 0.00
[ 60s ] thds: 100 tps: 1668.01 qps: 33364.65 (r/w/o: 23354.81/6673.73/3336.12) lat (ms,95%): 97.55 err/s: 0.30 reconn/s: 0.00
[ 70s ] thds: 100 tps: 1639.89 qps: 32784.79 (r/w/o: 22947.13/6557.88/3279.79) lat (ms,95%): 101.13 err/s: 0.10 reconn/s: 0.00
[ 80s ] thds: 100 tps: 1658.80 qps: 33172.77 (r/w/o: 23223.95/6630.91/3317.91) lat (ms,95%): 99.33 err/s: 0.10 reconn/s: 0.00
[ 90s ] thds: 100 tps: 1592.40 qps: 31900.02 (r/w/o: 22325.71/6388.90/3185.40) lat (ms,95%): 102.97 err/s: 0.40 reconn/s: 0.00
[ 100s ] thds: 100 tps: 1621.90 qps: 32414.24 (r/w/o: 22690.53/6480.01/3243.70) lat (ms,95%): 101.13 err/s: 0.20 reconn/s: 0.00
[ 110s ] thds: 100 tps: 1646.10 qps: 32945.10 (r/w/o: 23069.93/6582.08/3293.09) lat (ms,95%): 99.33 err/s: 0.60 reconn/s: 0.00
[ 120s ] thds: 100 tps: 1594.00 qps: 31879.15 (r/w/o: 22311.53/6379.41/3188.20) lat (ms,95%): 104.84 err/s: 0.20 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            2707040
        write:                           773344
        other:                           386688
        total:                           3867072
    transactions:                        193328 (1610.51 per sec.)
    queries:                             3867072 (32214.55 per sec.)
    ignored errors:                      32     (0.27 per sec.)
    reconnects:                          0      (0.00 per sec.)
General statistics:
    total time:                          120.0398s
    total number of events:              193328
Latency (ms):
         min:                                 11.97
         avg:                                 62.08
         max:                                360.82
         95th percentile:                    102.97
         sum:                            12001678.41
Threads fairness:
    events (avg/stddev):           1933.2800/32.79
    execution time (avg/stddev):   120.0168/0.01

删除测试数据：

1
sysbench /usr/share/sysbench/oltp_read_only.lua --db-driver=mysql --mysql-host=10.10.14.10 --mysql-port=3306  --mysql-user=app_user --mysql-password=admin_123 --mysql-db=sample --tables=10 --table-size=500000 --time=120 --report-interval=10 --threads=100 cleanup

参考

sysbench 安装、使用和测试

好记性不如烂笔头，生命不息，学习不止！

分享