sysbench压测MySQL数据库

最新推荐文章于 2025-05-10 15:57:00 发布

转载最新推荐文章于 2025-05-10 15:57:00 发布 · 473 阅读

1 ·

CC 4.0 BY-SA版权

原文链接：https://blog.csdn.net/qq_28203555/article/details/105219530

本文介绍如何使用sysbench工具对数据库进行压力测试，包括配置测试环境、执行不同类型的测试（如读写混合、只读等）及解读测试结果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一般情况下，在企业中我们申请了一台数据库服务器，不是直接开发使用，而是先进行压测，这样就能心里有数，这台数据库服务器的负载压力如何，性能表现如何，每秒能抗住多少个请求。

1、测试机器

2 vCPU 4 GiB (I/O优化)

2、数据库压测工具

sysbench，开源项目，去github下载就行。

3、建立测试库

create database test_db 
character set utf8mb4 collate utf8mb4_bin;

4、准备测试表

sysbench 
//基于mysql的驱动去连接mysql数据库
--db-driver=mysql 
//连续访问300秒
--time=300 
//10个线程模拟并发访问
--threads=10 
//每隔1秒输出一下压测情况
--report-interval=1 
//本机
--mysql-host=127.0.0.1 
//端口号：3306
--mysql-port=3306 
//测试用户
--mysql-user=root 
//测试密码
--mysql-password=******* 
//测试数据库
--mysql-db=test_db 
//模拟新建20个表
--tables=20 
//100万条数据 执行oltp数据库的读写测试
--table_size=1000000 oltp_read_write 
//参照这个命令的设置去构造出来我们需要的数据库里的数据
//自动创建20个测试表，每个表里创建100万条测试数据
--db-ps-mode=disable prepare

执行结果：

Initializing worker threads...

Creating table 'sbtest2'...
Creating table 'sbtest3'...
Creating table 'sbtest7'...
Creating table 'sbtest5'...
Creating table 'sbtest9'...Creating table 'sbtest6'...

Creating table 'sbtest1'...
Creating table 'sbtest4'...
Creating table 'sbtest8'...
Creating table 'sbtest10'...
Inserting 1000000 records into 'sbtest1'
Inserting 1000000 records into 'sbtest8'
Inserting 1000000 records into 'sbtest3'
Inserting 1000000 records into 'sbtest6'
Inserting 1000000 records into 'sbtest5'
Inserting 1000000 records into 'sbtest9'
Inserting 1000000 records into 'sbtest2'
Inserting 1000000 records into 'sbtest7'
Inserting 1000000 records into 'sbtest10'
Inserting 1000000 records into 'sbtest4'
Creating a secondary index on 'sbtest8'...
Creating a secondary index on 'sbtest9'...
Creating a secondary index on 'sbtest7'...
Creating a secondary index on 'sbtest5'...
Creating a secondary index on 'sbtest4'...
Creating a secondary index on 'sbtest1'...
Creating a secondary index on 'sbtest6'...
Creating a secondary index on 'sbtest10'...
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest3'...
Creating table 'sbtest18'...
Inserting 1000000 records into 'sbtest18'
Creating table 'sbtest19'...
Inserting 1000000 records into 'sbtest19'
Creating table 'sbtest17'...
Inserting 1000000 records into 'sbtest17'
Creating table 'sbtest15'...
Inserting 1000000 records into 'sbtest15'
Creating table 'sbtest12'...
Creating table 'sbtest14'...
Inserting 1000000 records into 'sbtest12'
Inserting 1000000 records into 'sbtest14'
Creating table 'sbtest11'...
Inserting 1000000 records into 'sbtest11'
Creating table 'sbtest16'...
Creating table 'sbtest20'...
Inserting 1000000 records into 'sbtest16'
Inserting 1000000 records into 'sbtest20'
Creating table 'sbtest13'...
Inserting 1000000 records into 'sbtest13'
Creating a secondary index on 'sbtest18'...
Creating a secondary index on 'sbtest15'...
Creating a secondary index on 'sbtest17'...
Creating a secondary index on 'sbtest19'...
Creating a secondary index on 'sbtest11'...
Creating a secondary index on 'sbtest14'...
Creating a secondary index on 'sbtest12'...
Creating a secondary index on 'sbtest20'...
Creating a secondary index on 'sbtest13'...
Creating a secondary index on 'sbtest16'...

5、测试指标

[ 22s ] thds: 10 tps: 380.99 qps: 7312.66 (r/w/o: 5132.99/1155.86/1321.35) lat (ms, 95%): 21.33 err/s: 0.00 reconn/s: 0.00

这是第22s输出的一段压测统计报告，然后是其他的一些统计字段：

thds: 10，这个意思就是有10个线程在压测
tps: 380.99，这个意思就是每秒执行了380.99个事务
qps: 7610.20，这个意思就是每秒可以执行7610.20个请求
(r/w/o: 5132.99/1155.86/1321.35)，这个意思就是说，在每秒7610.20个请求中，有5132.99个请求是读请求，1155.86个请求是写请求，1321.35个请求是其他的请求，就是对QPS进行了拆解。
lat (ms, 95%): 21.33，这个意思就是说，95%的请求的延迟都在21.33毫秒以下。
err/s: 0.00 reconn/s: 0.00，这两个的意思就是说，每秒有0个请求是失败的，发生了0次网络重连。

6、测试

6.1、测试数据库的综合读写TPS，使用oltp_read_write模式

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable run

测试结果：

SQL statistics:
    queries performed:
        read:                            1224314  // 这就是说在300s的压测期间执行了122万多次的读请求
        write:                           349804  // 这是说在压测期间执行了34万多次的写请求
        other:                           174902  // 这是说在压测期间执行了17万多次的其他请求
        total:                           1749020   // 这是说一共执行了174万多次的请求
    transactions:                        87451  (291.42 per sec.) // 这是说一共执行了8万多个事务，每秒执行291多个事务
    queries:                             1749020 (5828.46 per sec.) // 这是说一共执行了174万多次的请求，每秒执行5828请求
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)
// 下面就是说，一共执行了300s的压测，执行了8万+的事务
General statistics:
    total time:                          300.0811s 
    total number of events:              87451

Latency (ms):
         min:                                    2.17 // 请求中延迟最小的是2.17ms
         avg:                                   34.31 // 所有请求平均延迟是34.31ms
         max:                                 1392.50 // 延迟最大的请求是1392ms
         95th percentile:                      116.80 // 95%的请求延迟都在116.8ms以内
         sum:                              3000481.07

Threads fairness:
    events (avg/stddev):           8745.1000/44.26
    execution time (avg/stddev):   300.0481/0.01

6.2、测试数据库的只读性能，使用oltp_read_only模式

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_only --db-ps-mode=disable run

测试结果：

SQL statistics:
    queries performed:
        read:                            4938346
        write:                           0
        other:                           705478
        total:                           5643824
    transactions:                        352739 (1175.77 per sec.)
    queries:                             5643824 (18812.26 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          300.0065s
    total number of events:              352739

Latency (ms):
         min:                                    1.25
         avg:                                    8.50
         max:                                  235.05
         95th percentile:                       10.27
         sum:                              2999388.48

Threads fairness:
    events (avg/stddev):           35273.9000/275.06
    execution time (avg/stddev):   299.9388/0.00

6.3、测试数据库的删除性能，使用oltp_delete模式

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=test_db --tables=20 --table_size=1000000 oltp_delete --db-ps-mode=disable run

测试数据：

SQL statistics:
    queries performed:
        read:                            0
        write:                           291058
        other:                           856277
        total:                           1147335
    transactions:                        1147335 (3824.38 per sec.)
    queries:                             1147335 (3824.38 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          300.0038s
    total number of events:              1147335

Latency (ms):
         min:                                    0.03
         avg:                                    2.61
         max:                                 2114.86
         95th percentile:                        5.18
         sum:                              2999247.54
Threads fairness:
    events (avg/stddev):           114733.5000/470.77
    execution time (avg/stddev):   299.9248/0.00

6.4、测试数据库的更新索引字段的性能，使用oltp_update_index模式

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=test_db --tables=20 --table_size=1000000 oltp_update_index --db-ps-mode=disable run

测试结果：

SQL statistics:
    queries performed:
        read:                            0
        write:                           246840
        other:                           840545
        total:                           1087385
    transactions:                        1087385 (3624.46 per sec.)
    queries:                             1087385 (3624.46 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          300.0115s
    total number of events:              1087385

Latency (ms):
         min:                                    0.03
         avg:                                    2.76
         max:                                  999.91
         95th percentile:                        7.70
         sum:                              2999310.61

Threads fairness:
    events (avg/stddev):           108738.5000/827.44
    execution time (avg/stddev):   299.9311/0.00

6.5、测试数据库的插入性能，使用oltp_insert模式

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=test_db --tables=20 --table_size=1000000 oltp_insert --db-ps-mode=disable run

测试数据：

SQL statistics:
    queries performed:
        read:                            0
        write:                           837466
        other:                           0
        total:                           837466
    transactions:                        837466 (2791.42 per sec.)
    queries:                             837466 (2791.42 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          300.0126s
    total number of events:              837466

Latency (ms):
         min:                                    0.54
         avg:                                    3.58
         max:                                  294.86
         95th percentile:                       11.65
         sum:                              2998915.52

Threads fairness:
    events (avg/stddev):           83746.6000/144.17
    execution time (avg/stddev):   299.8916/0.00

6.6、测试数据库的写入性能，使用的是oltp_write_only模式：

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=test_db --tables=20 --table_size=1000000 oltp_write_only --db-ps-mode=disable run

测试数据：

SQL statistics:
    queries performed:
        read:                            0
        write:                           642227
        other:                           814255
        total:                           1456482
    transactions:                        242743 (808.75 per sec.)
    queries:                             1456482 (4852.59 per sec.)
    ignored errors:                      6      (0.02 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          300.1441s
    total number of events:              242743

Latency (ms):
         min:                                    0.74
         avg:                                   12.36
         max:                                  329.55
         95th percentile:                       68.05
         sum:                              3000839.57

Threads fairness:
    events (avg/stddev):           24274.3000/114.41
    execution time (avg/stddev):   300.0840/0.03

6.7、测试完成

使用上面的命令，sysbench工具会根据你的指令构造出各种各样的SQL语句去更新或者查询你的20张测试表里的数据，同时监测出你的数据库的压测性能指标，最后完成压测之后，可以执行下面的cleanup命令，清理数据。

  sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=test_db --tables=20 --table_size=1000000 oltp_write_only --db-ps-mode=disable cleanup