AirJD 焦点
AirJD

没有录音文件
00:00/00:00
加收藏

MySQL压力测试经验-如何避免生产环境性能瓶颈 by 叶金荣@Oracle

发布者 phper
发布于 1441587723153  浏览 2616 关键词 MySQL 
分享到

第1页

MySQL压力测试经验
如何避免生产环境性能瓶颈
个人几年来的测试经验,很乐意拿出来和大家分享

第2页

个人介绍
叶金荣
Oracle ACE
搜狐畅游福州(www.17173.com)
系统部经理
imysql@gmail.com
Weibo: @yejinrong

第3页

为什么要压力测试
采购新设备,评估新设备性能
开发新项目,评估数据库容量
新系统上线前,预估/模拟数据库负载
更换数据库版本,评估性能变化

第4页

关注指标
CPU
%wait,%user,%sys
内存
只内存读写,不产生swap
IO
IOPS、iowait、svctm、%util
数据库
TPS/TpmC、吞吐量(QPS)、响应时长、InnoDB指标
await:从请求磁盘操作到系统完成处理,每次请求的平均消耗时间,包括请求队列等待时间,单位是毫秒(1秒=1000毫秒)
%iowait:显示用于等待I/O操作占用 CPU 总时间的百分比
svctm: 平均每次设备I/O操作的服务时间 (毫秒)%util: 一秒中有百分之多少的时间用于 I/O 操作,或者说一秒中有多少时间 I/O 队列是非空的

InnoDB指标:
buffer pool hit ratio
Data read bytes
Data written bytes
Unpurged/Purged_Trascations
Dirty page

第5页

影响因素
硬件
CPU(省电模式、超线程、多核)
内存(镜像模式、xen内核限制可用总内存大小)
阵列卡(BBU、CACHE、条带、读写策略、FW)
硬盘(SSD/SAS)
系统
内核参数(tcp相关)
文件系统
IO调度器
bios设置中,和cpu/内存相关

内核参数(tcp相关):recycle、reuse、timeout、timestamp
文件系统:xfs、ext4
io调度器: deadline(SAS)、noop(SSD)、cfq

第6页

影响因素
MySQL
TRANSACTION ISOLATION LEVEL
Buffer pool
Concurrency thread
Redo log
Binlog sync
innodb_flush_log_at_trx_commit

第7页

注意事项
只在本地加压
压测数据量小
压测时间过短
压测模式太少
压力负载过大或过小
每轮测试完毕要净化环境
只在本地加压(Over lan)
压测数据量小(500DW)
压测时间过短(1h+)
压测模式太少(读写比例变化、并发数变化,RO、RW)
压力负载过大或过小(并发4-1024,最大请求数100w – 10亿 )
每轮测试完毕要净化环境

第8页

测试环境

第9页

测试工具
sysbench
Primarily for MySQL OLTP benchmarking,By MySQL AB
cpu、threads、mutex、memory、fileio、oltp
tpcc-mysql
Primarily for MySQL OLTP benchmarking,By Percona
tpch
Primarily for OLAP benchmarking
tcpcopy
模拟生产环境真实请求
其他
mysqlslap
sql-bench
 TPC-C测试用到的模型是一个大型的商品批发销售公司,它拥有若干个分布在不同区域的商品仓库。当业务扩展的时候,公司将添加新的仓库。
 每个仓库负责为10个销售点供货,其中每个销售点为3000个客户提供服务,每个客户提交的订单中,平均每个订单有10项产品,
 所有订单中约1%的产品在其直接所属的仓库中没有存货,必须由其他区域的仓库来供货。同时,每个仓库都要维护公司销售的100000种商品的库存记录。
源自:http://baike.baidu.com/view/2776305.htm


tpch是TPC(Transaction Processing Performance Council)组织提供的工具包。用于进行OLAP测试,以评估商业分析中决策支持系统(DSS)的性能。它包含了一整套面向商业的ad-hoc查询和并发数据修改,强调测试的是数据库、平台和I/O性能,关注查询能力。

第10页

测试工具 – sysbench
项目地址
http://sysbench.sourceforge.net/

分支版本
https://code.launchpad.net/sysbench
特点:支持lua,增加选项 oltp-tables-count

第11页

测试工具 – sysbench
安装
./configure --with-mysql-includes=path --with-mysql-libs=path && make && make install

支持其他数据库
with-pgsql
with-oracle

运行
sysbench --test=[mode] [other_options] prepare
sysbench --test=[mode] [other_options] run
sysbench --test=[mode] [other_options] cleanup

第12页

测试工具 – sysbench
通用基准
最大请求数:5,000,000
并发线程数:8 ~ 512

基准 – OLTP
mode=complex
engine=innodb
oltp-table-size=100,000,000

第13页

测试工具 – sysbench
测试用例

sysbench --test=oltp --mysql-host=host \
--mysql-user=user --mysql-password=passwd \
--oltp-test-mode=complex --mysql-table-engine=innodb \--oltp-table-size=100000000 --mysql-db=db \
--oltp-table-name=test_tbl --num-threads=128 \
--max-requests=5000000 run

--oltp-test-mode 测试模式:complex/simple/nontrx
1亿条记录,128线程,每线程500万次请求

第14页

测试工具 – sysbench
测试结果
1亿条记录,128线程,每线程500万次请求

第15页

测试工具 – sysbench
不足
只能模拟简单OLTP
测试表列数少
测试表数据类型少
标准版本只能支持一个测试表
1亿条记录,128线程,每线程500万次请求

第16页

测试工具 – tpcc mysql
安装
下载 bzr branch lp:~percona-dev/perconatools/tpcc-mysql
直接make即可
create_table.sql - 创建数据表
add_fkey_idx.sql – 创建索引及外键

初始化加载数据
tpcc_load db_host db_name db_user db_passwd db_warehouse_num
例如:tpcc_load localhost tpcc1000 user passwd 1000

运行OLTP测试
./tpcc_start -h localhost -d tpcc1000 -u root -p ' xx' -w 1000 -c 32 -r 120 -l 3600 -f ./tpcc_mysql_20120314

第17页

测试工具 – tpcc mysql
基准 - OLTP
warehouse = 1000
max connection = 8 ~ 512
warm up = 120(s)
run time/duration = 3600(s)

第18页

测试工具 – tpcc mysql
测试用例

./tpcc_start -h host -d db -u user -p passwd \
-w 500 -c 128 -r 120 -l 3600

-w warehose/仓库数
-c concurrent threads/并发线程
-r warmup/数据预热时长
-l during time/持续加压时长

第19页

测试工具 – tpcc mysql
测试结果

第20页

测试工具 – tpcc mysql
不足
测试表模式设计未优化
存在外键
部分索引不合理
1亿条记录,128线程,每线程500万次请求

第21页

测试工具 – tpch
安装
下载 http://www.tpc.org/tpch/spec/tpch_2_16_0.zip
cp makefile.suite makefile
编辑 makefile 文件
CC = gcc
DATABASE = MYSQL
MACHINE = LINUX
WORKLOAD = TPCH
编辑tpcd.h,增加宏定义
#ifdef MYSQL
#define GEN_QUERY_PLAN ""
#define START_TRAN "START TRANSACTION"
#define END_TRAN "COMMIT"
#define SET_OUTPUT ""
#define SET_ROWCOUNT "limit %d;\n"
#define SET_DBASE "use %s;\n"
#endif

第22页

测试工具 – tpch
初始化
初始化测试表数据: ./dbgen -s 100
生成测试数据:mysql -f tpch < dss.ddl
默认的初始化模式无主键、无索引
LOAD DATA INFILE导入数据
注意max_binlog_cache_size限制,需要切分文件导入
执行修改主键/外键/额外索引脚本
数据表名全部改成小写,适应TPC-H测试SQL脚本
运行qgen生成测试SQL
修改部分SQL语句
拆分完成测试SQL脚本成23个测试SQL

运行
执行23个测试脚本,记录运行时长

第23页

测试工具 – tpch
基准 - tpch
warehouse = 1000
单进程
tpch侧重OLAP模型,而MySQL并不适合OLAP,因此warehouse设定较小

第24页

测试工具 – tpch
测试用例

生成测试数据:./dbgen -s 1000
执行OLAP查询:time mysql -f tpch < ./queries/tpch_${NN}.sql

-s warehose/仓库数
默认的初始化模式无主键、无索引
注意max_binlog_cache_size限制,需要切分文件导入
执行修改主键/外键/额外索引脚本
数据表名全部改成小写,适应TPC-H测试SQL脚本
运行qgen生成测试SQL
修改部分SQL语句,由于源码包中自带的tpch初始化库表脚本并不能完全适用MySQL,需要修改部分代码

第25页

测试工具 – tpch
不足
初始化比较麻烦
部分索引不合理
MySQL本身不擅长做OLAP,测试模式有局限
1亿条记录,128线程,每线程500万次请求

第26页

测试工具 – tcpcopy
安装
项目地址:https://github.com/wangbin579/tcpcopy
下载,执行 sh autogen.sh 后直接编译安装即可
按照文档配置server和client端,即可启动压测

第27页

测试工具 – tcpcopy
测试用例

第28页

测试工具 – tcpcopy
不足
测试MySQL时,测试机需要开启 skip-grant-tables,否则无法正常进行,因为MySQL需要进行认证;在线服务器无需调整
测试过程中不能执行flush privileges,否则上述选项会失效
每次启用tcpcopy时,都需要重启mysqld,不能在线直接应用,否则不能转发包
不支持prepare语义
可能需要调整部分内核tcp参数,避免出现queue dropped
无法100%保证生产环境与测试环境数据一致性
1亿条记录,128线程,每线程500万次请求

第29页

测试报告 – sysbench

第30页

测试报告 – sysbench
TPS
threads

第31页

测试报告 – tpcc mysql

第32页

测试报告 – tpcc mysql
TpmC
threads

第33页

测试报告 – tpch

第34页

测试报告 – tpch
耗时

第35页

可靠性测试
模拟意外事件
断电(硬件冷重启)
RESET(硬件热重启)
阵列卡掉线
磁盘掉线
REBOOT(系统重启)
正常关闭服务(kill -TERM)
异常关闭服务(kill -9)
磁盘空间满
删除文件
破坏性修改已打开文件
磁盘空间满了之后,不断写入,可能会导致硬件故障或者文件系统异常

第36页

可靠性测试
长期极限高压
持续数小时、数天、数周运行高负载计算、IO任务
考验服务器在高压下的性能波动情况
考验硬件设备在高压下的稳定性表现

模拟恶劣环境
供电不稳
通风冷却不好
湿气大、灰尘多

第37页

参考
http://baike.baidu.com/view/2776305.htm
https://code.google.com/p/tcpcopy
http://imysql.com/2012/12/21/tpch-for-mysql-manual.html
http://imysql.com/2012/08/04/tpcc-for-mysql-manual.html
http://imysql.com/node/312
http://imysql.com/2009/04/24/using_mysqlslap_for_load_testing.html
http://imysql.com/2008_07_25_innodb_vs_pbxt
http://imysql.com/2011/10/22/using-tcpcopy-for-request-replication-or-testing.html

第38页

附件
整合sysbench测试脚本 [下载]
整合tpcc-mysql测试脚本 [下载]
整合tpch测试脚本 [下载]
汇总下载 [下载]
测试结果表格模板 [下载]

第39页

Thanks
支持文件格式:*.ppt, *.pptx, *.pdf
上传最后阶段需要进行在线转换,可能需要1~2分钟,请耐心等待。