mysql学习笔记——使用sysbench对mysql进行基准性能测试
1、sysbench介绍
sysbench 是业内最常用的数据库多线程基准测试工具,官方 1.0 版本发布以后,
能够基于 lua 脚本进行定制测试,十分方便。
本文档主要描述 sysbench1.0 版本以后安装部署及基准测试方法,以 mysql 的
oltp 基准测试为例进行说明。
2、sysbench的安装
2.1 二进制安装(推荐)
二进制安装非常简便,只需下载 https://packagecloud.io/安装下载的 yum 源,就
可以直接进行 yum 安装(会自动区分不同版本的安装包):
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench
2.2 编译安装
访问 https://github.com/akopytov/sysbench/releases 下载最新的 sysbench 源码文
件,然后上传到基准测试主机。
先安装相关依赖关系:
yum -y install make automake libtool pkgconfig libaio-devel vim-common
# For MySQL support,
rpm -ivh MySQL-devel-5.6.35-1.el6.x86_64.rpm (centos6)
或
yum –y install mariadb-devel(centos7)
# For PostgreSQL support
yum -y install postgresql-devel
解压 sysbench 源码文件,进行编译安装:
./autogen.sh
# Add --with-pgsql to build with PostgreSQL support
./configure --prefix=/usr/local/sysbench
make
make install
注意:如果 MySQL 安装在非标准目录(而且在 PATH 里面找不到 mysql_config),
你可以显式的指定--with-mysql-includes 和--with-mysql-libs 选项在./configure 后面,
例如:
./configure --prefix=/usr/local/sysbench --with-mysql
includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib
3、sysbench 参数说明
sysbench 安装完成后,可以查看 sysbench 安装的版本:
sysbench 使用语法如下:
sysbench [options]... [testname] [command]
其中:
options 为参数选项
testname 指定基准测试 oltp*.lua 脚本
command 是要执行什么样的操作
下面对这三部分分别进行说明
3.1 command 命令
sysbench 的 command 是控制命令,包含以下几个选项:
prepare, prewarm, run, cleanup, help
其中:
prepare 表示测试前准备,主要创建表,生成初始测试数据
prewarm 表示测试前进行预热
run 表示执行测试,根据各种测试场景进行测试
cleanup 表示测试完后清理测试数据
help 获取帮助文档
3.2 testname 测试选项
sysbench 的 testname 是表示要测试的场景,目前 sysbench 自带的针对 mysql
的 oltp 测试场景有如下几种:
oltp_delete.lua 删除测试
oltp_insert.lua 插入测试
oltp_point_select.lua 主键查询测试
oltp_read_only.lua 只读测试
oltp_read_write.lua 读写测试
oltp_update_index.lua 更新索引测试
oltp_update_non_index.lua 更新非索引测试
oltp_write_only.lua 只写测试
select_random_points.lua 随机多个查询(in 里面多个查询条件)
select_random_ranges.lua 随机范围查询(between … and …)
3.3 options 选项
sysbench 的 options 里面包含测试需要的各种参数,包括初始化、预热、运行
以及不同测试场景中需要的参数,具体如下:
3.3.1 通用选项
选项 描述 默认值
--threads=N 线程数 默认为1
--events=N 请求数限制,0 代表无限制 默认为0
--time=N 测试执行时长(单位:秒)限制 默认为10
--forced-shutdown=STRING 超过--time 后,强制中断测试(off 代表不启用,on代表启用)默认为off
--thread-stack-size=SIZE 每个线程的堆栈大小(k、M、G 单位都可)默认为64K
--report-interval=N 每 N 秒输出一份中间统计结果, 0 表示不输出中间统计结果 默认为0
--tx-rate=N 已经废弃,被--rate 取代 0
--max-requests=N 已经废弃,被--events 取代 0
--max-time=N 已经废弃,被--time 取代 0
--num-threads=N 已经废弃,被--threads 取代 1
3.3.2 伪随机数发生器
选项 描述 默认值
--rand-type=STRING 随机数分布(uniform、gaussian、special、pareto)默认为special
--rand-spec-iter=N 用于数字生成的迭代次数 默认为12
--rand-spec-pct 被当作 special 值所占百分比 默认1
--rand-spec-res=N 多少比例的 special 值被使用 默认为75
--rand-seed=N 随机发生器种子,为 0 时,使用当前时间作为随机种子 默认为0
--rand-pareto-h=N 是用 pareto 分布时的 h 参数 默认为0.2
3.3.3 日志选项
日志选项
选项 描述 默认值
--verbosity=N 信息级别(5-debug、0-关键信息) 默认为3
--percentile=N 取百分比段位延迟进行统计计算(1-100) 默认为95
--histogram[=on|off] 在报告中打印延迟直方图 off
3.3.4 数据库选项
通用数据库选项
选项 描述 默认值
--db-driver=STRING
指定数据库驱动,默认安装支持:
mysql - MySQL driver
pgsql - PostgreSQL driver
--db-ps-mode=STRING prepare 语句使用(auto、disable) 默认为auto
--db-debug[=on|off] 是否打印数据库特有的调试信息 默认为off
mysql 选项
选项 描述 默认值
--mysql-host=[LIST,...] mysql 主机 localhost
--mysql-port=[LIST,...] mysql 端口 3306
--mysql-socket=[LIST,...] mysql 本地 socket 路径
--mysql-user=STRING mysql 测试用户 sbtest
--mysql-password=STRING mysql 测试用户密码
--mysql-db=STRING mysql 测试库名 sbtest
--mysql-ssl[=on|off] 如果客户端支持,是否使用 SSL 连接 Off
--mysql-ssl-cipher=STRING 使用 SSL 连接的特定密码
--mysql-compression[=on|off] 如果客户端支持,是否使用压缩 Off
--mysql-debug[=on|off] 追踪所有客户端的调用 Off
--mysql-ignore-errors=[LIST,...] 忽略错误列表(比如 1213,1020,1205,或者 all)
--mysql-dry-run[=on|off] 只运行,假装所有的客户端调用都执行成功,实际并没有执行它们 默认为off
oltp 选项
选项 描述 默认值
--test=STRING 指定 oltp 测试的 lua 脚本:
--table-size 单表记录数,取代--oltp-table-size 10000
--range-size 范围查询时的范围 100
--tables 表数量,取代--oltp-tables-count 1
--point-selects 主键查询测试时,每事务查询语句数 10
--simple-ranges 简单范围查询,每事务查询语句数 1
--sum-ranges 每个事务中 sum()查询语句数 1
--order-ranges 每个事务中 order by 查询语句数 1
--distinct-ranges 每个事务中 distinct 查询语句数 1
--index-updates 每个事务中更新索引语句数 1
--non-index-updates 每个事务中更新非索引字段语句数 1
--delete-inserts 每个事务中 delete/insert 语句数 1
--range-selects 是否开启所有的范围查询 true
--auto-inc 是否使用自增列 true
--skip-trx 不显式开启事务,所有查询使用autocommit 模式 false
--secondary 是否使用二级索引代替主键索引 false
--create-secondary 除了主键索引外,是否创建二级索引 True
--mysql-storage-engine mysql 存储引擎 innodb
4、MySQL 的 OLTP 测试
了解了 sysbench 的使用语法及各个参数以后,就可以根据需要进行基准测试了,
测试之前,需要进行基准值进行规划,比如表数量,记录数,请求次数、查询时
间等等,关于基准值标准可参考叶金荣博文:老叶倡议:MySQL 压力测试基准值
其中关于 sysbench 的部分,里面的参数值现在已经改名,具体可参考上面的
options 选项。
下面根据测试的过程简单描述下每个阶段需要做的事情:
首先,需要进行测试数据的初始化工作,这时候我们需要创建表,决定测试表数
量,表大小,以及表上的索引情况等等,即前面所讲的 prepare 过程。
举例如下:
sysbench --test=/usr/share/sysbench/oltp_read_write.lua --mysql-host=127.0.0.1 --mysql-user=lzy001 --mysql-port=3306 --mysql-password=123456 --mysql-db=db1 --table-size=10000000 --tables=10 --db-driver=mysql --mysql_storage_engine=innodb --threads=10 --rand-type=uniform prepare
其中--test 必须指定一个,可随意选一个测试场景,对 prepare 没有实际影响。
需要先在 MySQL 中创建一个 database
另 外 需 要 注 意 的 是 你 要 进 行 测 试 的 数 据 量 最 好 大 于 MySQL 的innodb_buffer_pool_size 的大小,避免全内存访问,测试不出实际的性能。
可看出有如下输出:
Initializing worker threads...
Creating table 'sbtest1'...
Creating table 'sbtest2'...
Inserting 1000000 records into 'sbtest2'
Inserting 1000000 records into 'sbtest1'
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest1'...
然后即可对创建的表进行 oltp 基准测试了:
举例如下:
sysbench --test=/usr/share/sysbench/oltp_read_write.lua --mysql-host=192.168.220.78 --mysql-user=lzy001 --mysql-port=3306 --mysql-password=123456 --mysql-db=db1 --table-size=10000000 --tables=10 --db-driver=mysql --mysql_storage_engine=innodb --percentile=99 --report-interval=2 --db-ps-mode=disable --time=900 --threads=1024 run
其中--test 里面的测试场景可以为 testname 测试选项其他场景,--threads 也
可以设置其他的线程,一般会测试 64/128/256/512/1024/2048 等线程数。
然后可得到统计报告如下:
其中:
SQL statistics:
queries performed:
read: //总 select 数
write: //总 dml 数
other: //begin,commit,lock tables 以及 mutex 数
total: //总查询量
transactions: //TPS
queries: //QPS
ignored errors: //忽略错误数
reconnects: 0 (0.00 per sec.)
General statistics:
total time: --time 指定的压测时间
total number of events: //总的请求次数
Latency (ms):
min: //最小响应时间
avg: //平均响应时间
max: //最大响应时间
99th percentile: //99%的语句相应时间
sum: //总查询时间
其中比较重要的几个性能指标:
transactions,即我们说的 TPS
queries 即我们说的 QPS
99th percentile: 语句的响应时间
这是衡量我们数据库性能的重要参数。
除此,还需要监控系统 CPU、内存、IO 等系统层面的指标,从而对总体的数据库
服务器性能做一个评估。
每次基准测试完毕,建议执行以下操作:
1. 重启 MySQL 实例
2. 清空系统 cache,释放 swap(如果用到了 swap 的话)
shell>sync --将脏数据刷新到磁盘
shell>echo 3 > /proc/sys/vm/drop_caches --清除 OS Cache
shell>swapoff -a && swapon -a
3. 如果可以,重启整个 MySQL 服务器
最后基准测试完毕,清空测试表和测试数据。举例如下:
sysbench --test=/usr/share/sysbench/oltp_read_write.lua --mysql-host=127.0.0.1 --mysql-user=lzy001 --mysql-port=3306 --mysql-password=123456 --mysql-db=db1 --table-size=10000000 --tables=10 --db-driver=mysql --mysql_storage_engine=innodb --threads=10 --cleanup