文档章节

MySQL参数优化案例

小致dad
 小致dad
发布于 07/19 18:29
字数 1323
阅读 30
收藏 0

环境介绍

硬件配置

cpu核心数 内存大小 磁盘空间
16核 256G 3T

软件环境

操作系统版本 mysql版本 表数目 单表行数
centos-7.4 mysql-5.7.22 128张表 2kw行

优化层级与指导思想

优化层级

MySQL数据库优化可以在多个不同的层级进行,层级的常见分法有1):SQL优化 2):参数优化 3):架构优化;本文重点关注第2层,并通过一次完整的优化案例来讲解参数优化的内在逻辑。

指导思想

1、日志先行 -- 一个事务能否成功提交的关键是与它相关的日志是否成功落盘,与数据没有太大的关系;也就是说对写的优化可以表述为各方面的资源向写操作倾斜。

2、瓶颈分析 -- 通过show global status 的各个计数器的值基本上就能分析出当前瓶颈所在,再结合一些简单的系统层面的监控工具如top iostat 等通常就能明确瓶颈。

3、整体性能是“读”&“写”之间的再平衡。

优化过程

最小化安装情况下的性能表现

my.cnf中的内容

 

tuning01图像地址: http://www.sqlpy.com/mysqlz/tuninglog/result/cm16c256g4096ssd/0/

监控数据

 

分析&优化思路

对监控数据有两种可能的解释:1): 由于最小化的安装的buffer_pool_size比较小,所以会频繁的触发innodb_buffer_pool的最大脏页的限制,使得innodb进入爆力刷盘的模式,这种情况下io使用率会明显上升。2): redo日志重用。 最终的影响可能是两者的叠加,这里先从buffer_pool开始优化。

优化innodb_buffer_pool_size

my.cnf中的内容

 

tuning02图像地址:http://www.sqlpy.com/mysqlz/tuninglog/result/cm16c256g4096ssd/1/

监控数据

 

调整innodb_buffer_pool_size前后的性能对比 tuning01vs02性能大概提高3倍 图像地址:http://www.sqlpy.com/mysqlz/tuninglog/compare/cm16c256g4096ssd/0/1/

分析&优化思路

1、针对innob_buffer_pool_size的调整取得了一定的收获,下面将要调整的就是针对redo重用的情况了,也就是说我们要增大innodb_log_files_in_group和innodb_log_file_size到一个合适的值。

2、innob_buffer_pool_size的调整取得了一定的收获还可以更进一步,那就是增大innodb_buffer_pool_instances的值。

优化innodb_log_files_in_group&innodb_log_file_size

根据对之前测试的记录每完成一组测试LSN增大4.5G、持续时间大概是5分钟;理论上把redo文件增大到5G可以做到整个测试的过程中不发生日志重用、这样的话测试的跑分会更高,不过这个会影响数据库宕机恢复的时间。MySQL在默认配置下innodb_log_files_in_group=2,innodb_log_file_size=48M也就是说跑完一组测试redo日志要刷新48轮(1024*4.5/96 ==48) 先看一下把日志刷新调整到9轮的情况。

my.cnf中的内容

 

tuning03图像地址:http://www.sqlpy.com/mysqlz/tuninglog/result/cm16c256g4096ssd/2/

调整innodb_log_files_in_group&innodb_log_file_size前后的性能对比 tuning02vs03性能大概提高2倍 图像地址:http://www.sqlpy.com/mysqlz/tuninglog/compare/cm16c256g4096ssd/1/2/

现在看一下日志重用控制在一轮(5G)之内的性能表现

my.cnf中的内容

 

tuning04调整innodb_log_files_in_group&innodb_log_file_size前后的性能对比 tuning03vs04性能大概提高2倍图像地址:http://www.sqlpy.com/mysqlz/tuninglog/compare/cm16c256g4096ssd/2/3/

分析&优化思路

1、增大redo到5G的情况下由于整个测试过程中几乎没有日志文件重用的问题,这样也就规避由些引发的大量数据刷盘行为,所以性能曲线也就更平滑了。

2、通过show global status 发现Table_open_cache_overflows=200W+、Thread_created=2k+

3、%Cpus : 80.5 us, 13.8 sy, 0.0 ni, 5.4 id, 0.0 wa, 0.0 hi, 0.3 si, 0.0 st 95%的使用率cpu资源成了大问题,这个使用率下能调整的参数不多了

3、对磁盘的监控数据表明util的峰值已经下降到14%、磁盘已经不在是问题;所以针对innodb_buffer_pool_size、innodb_log_files_in_group&innodb_log_file_size 这两次优化的进入一步优化innodb_buffer_pool_instances、innodb_log_buffer_size 先不进行;在些采用“抓大放小”的方式先调整表缓存。

优化table_open_cache&table_open_cache_instances&innodb_sync_spin_loops&thread_cache_size

由于cpu使用率达到了95%看到这个数值有一种发自内心的无力感,所以打算所目前status中能明确的一些问题直接一起调整了;增大table_open_cache&table_open_cache_instances用于优化表缓存、增大thread_cache_size使用cpu不用频繁的创建消毁线程、增大innodb_sync_spin_loops是希望尽可能的避免上下文切换(由于目前的监控粒度不是特别细所以无法给出13.8%中有多少是上下文切换)也就是说增大innodb_sync_spin_loops更多的是出于职业判断

my.cnf中的内容

 

tuning06调整前后的比较 tuning03vs06

总结

考虑到cpu使用率已经达到95%且增加物理cpu不现实的情况下,决定MySQL参数优化到些为止了;最后来看一眼这次优化成果。 image

作者:

作者: 蒋乐兴

时间: 2018-05-08

本文转载自:https://www.cnblogs.com/JiangLe/p/9008979.html

共有 人打赏支持
小致dad
粉丝 144
博文 536
码字总数 580295
作品 0
济南
技术主管
私信 提问
深入理解MySQL 5.7 GTID系列(九):实际案例一

导 读 作者:高鹏(重庆八怪) 原文地址: https://www.jianshu.com/p/2c25842d58d3 深入理解MySQL 5.7 GTID系列文章共十篇,本文为第四篇,点击查看: 第一篇:深入理解MySQL 5.7 GTID系列(...

老叶茶馆
04/13
0
0
MYSQL数据库服务CPU高问题分析与优化

MYSQL数据库服务CPU高问题分析与优化 MySQL服务性能监控分析与优化是永恒的主题,做为性能测试人员有时也要站在DBA角度出发进行适当分析与优化,这也是性能测试人员能长期生存发展存在意义。...

郭柏雅
03/22
0
0
公开课发布《从50分到0.59秒,SQL优化让不可能变成可能》by松华

特邀嘉宾 主讲嘉宾:郑松华 知数堂《SQL优化开发》课程讲师微信号:qidan3500 江湖传言: 《SQL优化开发》第3期课程于12月22日发车 还没上车的同学抓紧买票哦 跟松华老师学优化,快速参透“S...

n88lpo
2017/12/15
0
0
MySQL数据库的“十宗罪”(附10大经典错误案例)

笔者在刚开始学习数据库的时候,没少走弯路。经常会遇到各种稀奇古怪的 error 信息,遇到报错会很慌张,急需一个解决问题的办法。跟无头苍蝇一样,会不加思索地把错误粘到百度上,希望赶紧查...

张甦
2017/08/03
0
0
深入理解MySQL 5.7 GTID系列(十):实际案例二

导 读 作者:高鹏(重庆八怪) 原文地址: https://www.jianshu.com/p/2c25842d58d3 深入理解MySQL 5.7 GTID系列文章共十篇,本文为第四篇,点击查看: 第一篇:深入理解MySQL 5.7 GTID系列(...

老叶茶馆
04/25
0
0

没有更多内容

加载失败,请刷新页面

加载更多

MyBaties分页插件PageHelper的简单使用

抛出问题: 如果想要将现有的select语句改为支持分页功能的查询语句该怎么做呢? 最简单的一种做法就是将所有的select语句都加上limit来实现分页,这种做法有什么问题呢? 有没有一种简便方法...

嘴角轻扬30
28分钟前
2
0
创业公司如何快速构建高效的监控系统?

12 月 7 日,在 2018 ArchSummit 全球架构师峰会·运维与监控专场,七牛云资深运维开发工程师贺强带来了主题为《如何快速构建高效的监控系统》的内容分享。 本文是对演讲内容的实录整理。 大...

七牛云
28分钟前
0
0
Docker与自动化测试及其测试实践

Docker 与自动化测试 对于重复枯燥的手动测试任务,可以考虑将其进行自动化改造。自动化的成本在于自动化程序的编写和维护,而收益在于节省了手动执行用例的时间。简而言之,如果收益大于成本...

微笑向暖wx
29分钟前
1
0
Spring Cloud 分布式链路跟踪 Sleuth + Zipkin + Elasticsearch

随着业务越来越复杂,系统也随之进行各种拆分,特别是随着微服务架构的兴起,看似一个简单的应用,后台可能很多服务在支撑;一个请求可能需要多个服务的调用;当请求迟缓或不可用时,无法得知...

编程SHA
31分钟前
1
0
Swift-清除缓存

func removeCache (){ // 取出cache文件夹路径.如果清除其他位子的可以将cachesDirectory换成对应的文件夹 let cachePath = NSSearchPathForDirectoriesInDomains(FileMan...

west_zll
32分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部