使用sysbench进行mysql压测

时间:2021-08-16 04:01:43

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

一般压测中我们关注的点有三个: QPSTPS95%平均响应时间,其中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. 参考