使用sysbench进行mysql压测

Myth丶恋晨 2022-06-18 00:51 230阅读 0赞

## 1. sysbench介绍 ##

sysbench是一个模块化的、跨平台、多线程基准测试工具，主要还是用来做mysql数据库OLTP性能测试。详细介绍及安装包下载请访问官方github:[https://github.com/akopytov/sysbench][https_github.com_akopytov_sysbench]

除了做数据库性能压测，它还具备其他测试功能:

*  cpu性能
 *  磁盘io性能
 *  调度程序性能
 *  内存分配及传输速度
 *  POSIX线程性能

关于sysbench的版本一般选择稳定版本0.5，从0.5版本开始，对于数据库的测试，采用lua脚本的方式进行，用户可以通过定制lua脚本的方式来实现自己特定的测试需求。

> 业界对mysql数据库的压测通常采用默认提供的脚本测试

syebench主要用于mysql性能压测，因此，这篇文章以sysbench0.5在CentOS6.5+的环境上为基础进行讲解，讲解如何对mysql进行压测

## 2. 安装步骤 ##

通过源码编译方式进行安装

### 2.1 获取sysbench-0.5源码包 ###

通过[https://github.com/akopytov/sysbench/tree/0.5][https_github.com_akopytov_sysbench_tree_0.5]下载对应的源码包，点击**【Clone or download】**按钮，点击**【Download ZIP】**即可下载到源码包。

### 2.2 编译安装 ###

确保系统安装以下模块:

*  gcc
 *  gcc-c++
 *  automake
 *  make
 *  libtool
 *  mysql-community-devel

如果没有，请用以下命令安装:

yum install gcc gcc-c++ automake make  libtool mysql-community-devel

将sysbench0.5源码包解压到服务器上某目录，假定为:

/dba/app/sysbench-0.5

进入该目录，执行以下命令进行编译安装:

./autogen.sh
    ./configure --prefix=/dba/app/sysbench-0.5
    make && make install

编译成功后，对应的目录是:

/dba/app/sysbench-0.5/sysbench

## 3. 测试步骤 ##

测试主要通过sysbench.sh脚本进行，执行以下命令查看sysbench.sh具体使用方法:

./sysbench.sh --help

测试分为3步:

*  准备数据 -> prepare
 *  执行测试 -> run
 *  清理测试数据 -> cleanup

对于mysql压测，主要采用oltp.lua脚本进行测试，下面我们以oltp.lua为示例讲解测试步骤:

*pwd -> /dba/app/sysbench-0.5/sysbench*

### 3.1 准备数据 ###

参数说明如下:

*  *–test* : 指定测试所用的lua脚本
 *  *–mysql-host* : 指定连接的数据库域名或者IP
 *  *–mysql-port* : 指定连接的数据库端口
 *  *–mysql-user* : 指定连接的数据库登陆用户名
 *  *–mysql-password* : 登陆用户对应的密码
 *  *–mysql-db* : 指定测试在哪个database上进行
 *  *–oltp-tables-count* : 测试表数量
 *  *–oltp-table-size=100000* : 每个测试表的记录行数

上面的命令完成后，我们在对应的数据库 *10.202.44.214:24801/test* 中创建了10个测试表，每个表中有10W条记录，表名从sbtest1到sbtest10，创建的表结构如下所示:

mysql> show create table sbtest1;
    +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table   | Create Table | +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | sbtest1 | CREATE TABLE `sbtest1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=32005332 DEFAULT CHARSET=utf8mb4 MAX_ROWS=1000000 | +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

### 3.2 执行测试 ###

当数据准备完成后，我们就可以通过以下命令执行测试:

./sysbench --test=tests/db/oltp.lua \
    --mysql-host=10.202.44.214 --mysql-port=24801 \
    --mysql-user=sysbench --mysql-password=sysbench \
    --mysql-db=test \
    --oltp-tables-count=10 --oltp-table-size=100000 \
    --mysql-ignore-errors=1062,1213 \
    --oltp-read-only=off --oltp_range_selects=off \
    --max-time=120 --max-requests=0 --report-interval=10 --num-threads=32 run

参数说明:

*  *–mysql-ignore-errors=1062,1213* : 指定测试过程中忽略mysql error 1062(主键重复冲突)和 error 1213(死锁)
 *  *–oltp-read-only=off* : 表示进行读写测试，如果取值为on，表示只进行只读测试
 *  *–oltp\_range\_selects=off* : 忽略range\_selects语句的执行，具体参考 **oltp.lua详解** 章节
 *  *–max-time* : 测试时间，单位为秒
 *  *–max-requests=0* : 测试最大请求次数，注意，指定了测试时间，则该参数需要设置为0
 *  *–report-interval* : 指定每隔多少秒输出压测统计信息
 *  *–num-threads* : 指定压测并发线程数**【Important!!!】**

### 3.3 清理测试数据 ###

当压测结束后，清理之前产生的数据，回收空间:

## 4. 测试结果解读 ##

测试结束后，输出报告如下 ***OLTP test statistics*** 部分所示:

sysbench 0.5:  multi-threaded system evaluation benchmark
    
    Running the test with following options:
    Number of threads: 32
    Report intermediate results every 10 second(s)
    Random number generator seed is 0 and will be ignored
    
    
    Initializing worker threads...
    
    Threads started!
    
    [  10s] threads: 32, tps: 4643.57, reads: 46456.67, writes: 18576.77, response time: 8.62ms (95%), errors: 0.00, reconnects:  0.00
    [  20s] threads: 32, tps: 4700.98, reads: 47010.98, writes: 18805.81, response time: 8.35ms (95%), errors: 0.00, reconnects:  0.00
    [  30s] threads: 32, tps: 3986.91, reads: 39878.93, writes: 15955.85, response time: 8.67ms (95%), errors: 0.00, reconnects:  0.00
    [  40s] threads: 32, tps: 4061.90, reads: 40608.70, writes: 16239.30, response time: 8.31ms (95%), errors: 0.00, reconnects:  0.00
    [  50s] threads: 32, tps: 4378.40, reads: 43786.60, writes: 17512.20, response time: 8.23ms (95%), errors: 0.00, reconnects:  0.00
    [  60s] threads: 32, tps: 4254.68, reads: 42542.68, writes: 17020.71, response time: 8.25ms (95%), errors: 0.00, reconnects:  0.00
    [  70s] threads: 32, tps: 4772.10, reads: 47726.61, writes: 19088.31, response time: 8.09ms (95%), errors: 0.00, reconnects:  0.00
    [  80s] threads: 32, tps: 4856.82, reads: 48563.93, writes: 19425.69, response time: 8.09ms (95%), errors: 0.00, reconnects:  0.00
    [  90s] threads: 32, tps: 4762.68, reads: 47624.51, writes: 19050.82, response time: 8.46ms (95%), errors: 0.00, reconnects:  0.00
    [ 100s] threads: 32, tps: 4779.91, reads: 47800.80, writes: 19119.54, response time: 8.10ms (95%), errors: 0.00, reconnects:  0.00
    [ 110s] threads: 32, tps: 4869.00, reads: 48690.02, writes: 19477.41, response time: 8.14ms (95%), errors: 0.00, reconnects:  0.00
    [ 120s] threads: 32, tps: 4925.81, reads: 49259.20, writes: 19703.44, response time: 8.06ms (95%), errors: 0.00, reconnects:  0.00
    OLTP test statistics:
        queries performed:
            read:                            5499580 //总select数量
            write:                           2199832 //总update、insert、delete语句数量
            other:                           1099916 //commit、unlock tables以及其他mutex的数量
            total:                           8799328
        transactions:                        549958 (4582.78 per sec.) //通常需要关注的数字(TPS)
        read/write requests:                 7699412 (64158.98 per sec.)
        other operations:                    1099916 (9165.57 per sec.)
        ignored errors:                      0      (0.00 per sec.) //忽略的错误数
        reconnects:                          0      (0.00 per sec.)
    
    General statistics:
        total time:                          120.0052s
        total number of events:              549958 //总的事件数，一般与transactions相同
        total time taken by event execution: 3838.9062s
        response time:
             min:                                  4.07ms
             avg:                                  6.98ms 
             max:                               1103.68ms
             approx.  95 percentile:               8.25ms //95%的语句的平均响应时间
    
    Threads fairness:
        events (avg/stddev):           17186.1875/622.35
        execution time (avg/stddev):   119.9658/0.00

一般压测中我们关注的点有三个: ***QPS***、 ***TPS*** 和 ***95%平均响应时间***，其中TPS和95%的平均响应时间可以从报告中直接获取，而QPS的计算方式如下:

QPS = 总select数量 / 测试总时长(秒)

如上结果所示，在客户端并发为32线程时候，MySQL的TPS、QPS和95%平均响应时间为:

*  *TPS* : 4582
 *  *QPS* : 45830 (5499580 / 120)
 *  *95% resp time* : 8.25ms

## 5. oltp.lua详解 ##

oltp.lua默认一次事务内会发18个sql语句，14个为读sql(select)，4个为写sql(update、insert、delete)，即读写比例为14:4

select语句分为2大类:

*  基于主键id的查询(共10个)

语句类似:

SELECT c FROM ${rand_table_name} where id=${rand_id};

可以通过指定`--oltp_point_selects=${num}`来改变语句个数

*  基于范围查询(共4个)

有4种(每种1个):

SELECT c FROM ${rand_table_name} WHERE id BETWEEN ${rand_id_start} AND ${rand_id_end}; // simple-range
    SELECT SUM(K) FROM ${rand_table_name} WHERE id BETWEEN ${rand_id_start} AND ${rand_id_end}; // sum-range
    SELECT c FROM ${rand_table_name} WHERE id BETWEEN ${rand_id_start} AND ${rand_id_end} ORDER BY c; // order-range
    SELECT DISTINCT c FROM ${rand_table_name} WHERE id BETWEEN ${rand_id_start} AND ${rand_id_end} ORDER BY c; // distinct-range

可以通过`--oltp_range_selects=off`来忽略这4个范围查询，当`--oltp_range_selects=off=on`，还可以单独指定这几个范围查询的个数:

*  *–oltp\_simple\_ranges* : 指定simple-range类型查询语句个数
 *  *–oltp\_sum\_ranges* ： 指定sum-range类型查询语句个数
 *  *–oltp\_order\_ranges* ： 指定order-range类型查询语句个数
 *  *–oltp\_distinct\_ranges* : 指定distinct-range类型查询语句个数

## 6. FAQs ##

*待补充*

## 7. 参考 ##

*  [http://seanlook.com/2016/03/28/mysql-sysbench/][http_seanlook.com_2016_03_28_mysql-sysbench]
 *  [https://www.percona.com/docs/wiki/benchmark\_sysbench\_oltp.html][https_www.percona.com_docs_wiki_benchmark_sysbench_oltp.html]

[https_github.com_akopytov_sysbench]: https://github.com/akopytov/sysbench
[https_github.com_akopytov_sysbench_tree_0.5]: https://github.com/akopytov/sysbench/tree/0.5
[http_seanlook.com_2016_03_28_mysql-sysbench]: http://seanlook.com/2016/03/28/mysql-sysbench/
[https_www.percona.com_docs_wiki_benchmark_sysbench_oltp.html]: https://www.percona.com/docs/wiki/benchmark_sysbench_oltp.html