sysbench测试MySQL数据库结果分析 sysbench mysql

转载

mob64ca13f9e726 2023-11-11 10:40:38

文章标签 sysbench mysql 压测 lua 文章分类 MySQL 数据库

1. sysbench介绍

sysbench是一个模块化的、跨平台、多线程基准测试工具，主要还是用来做mysql数据库OLTP性能测试。详细介绍及安装包下载请访问官方github: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下载对应的源码包，点击【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 准备数据

./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 prepare

参数说明如下:

–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 清理测试数据

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

./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 cleanup

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，还可以单独指定这几个范围查询的个数: