性能文章>终于做了一把MySQL调参boy>

终于做了一把MySQL调参boy原创

5月前
2910013

本文通过笔者经历的一个真实案例来介绍一个MySQL中的重要参数innodb_buffer_pool_size,希望能给大家带来些许收获,当遇到类似性能问题时可以多一种思考方式。

问题背景

有个广西的客户,之前系统一直用的很流畅,最近反馈系统响应极慢,卡顿严重,希望我们尽快解决。

收到反馈以后我立马去查看服务器各项指标,微服务所在机器各项指标正常,但是数据库服务器已不堪重负,看一下top的输出:


数据库服务器的硬件配置为8核16G内存,最繁忙的时候系统负载已经达到了16,cpu利用率700%,可想而知系统当前服务质量有多糟糕。

 


自我思考

使用量突然上来了吗?

观察了使用人数的趋势图,是多了一些,但还在同一个数量级(toB的客户,一般都在一个可控的量)。

数据量突然上来了吗?

确实是多了一些,有几张表,之前一年多才30多万数据,最近一个月已经长到1百万级别,到底是业务上量了还是程序bug呢,还需要持续排查。

慢查多了吗?

是的,慢查很多,而且最近几天猛涨,怀疑是不是增加了新功能,根据慢SQL去查业务代码,确实增加了七八张报表,我尝试点了一下,确实很慢,高峰期前端几乎全是超时,太慢了。

toB系统业务复杂,OLTP和OLAP都兼备,MySQL应付OLTP很擅长,但是OLAP就有点捉襟见肘了,也许有人会说为什么不用大数据的那一套东西来解决OLAP的问题呢?

成本、成本、成本太高了,toB业务,大多数都部署在客户的自建机房中,最好能少引入或者不引入其他组件,最大限度降低各项成本,包括和客户的沟通成本,客户要付出的财力成本,后期的运维成本等等。

 


解决过程

铺垫了这么多,具体怎么解决呢?短期来看就是先采用垂直扩容的方式抵御一波,俗称升配置,长期来看的话需要梳理&优化慢查,采用读写分离等手段逐步优化。

话不多说,先联系客户升配置,8核16G变16核32G,希望第二天早高峰能给客户带来丝滑般的用户体验。

第二天我盯着数据库观察负载,确实好了一些,但终归还是凉凉了,只是来的比以往稍晚一些,一时间不知道怎么处理了,客户严重质疑我们的能力。

问题究竟出在哪里了呢?现在这个硬件配置已经很强悍了,感觉完全没有发挥出来,cpu最高才跑到800%。过往的工作中,有专门的DBA负责数据库的运维,遇到过一次升配的场景,升完以后有很明显的性能提升,那感觉刷刷的。

穷途末路的时候我拨通了老板的电话,和他沟通了目前的境遇,希望老同志能指点迷津,快速帮我捅破窗户纸,他有句话提醒了我,“为什么浙江客户的数据库没这个问题呢,配置一样,浙江客户的业务量还要更大一些,区别就是他们用的阿里云RDS,广西客户是自建MySQL,会不会有什么数据库的参数咱没有意识到”。

跟他聊完我立马去对比了阿里云RDS和自建MySQL的相关参数,有一个值非常可疑“innodb_buffer_pool_size“,浙江客户RDS中这个值为12G,而广西客户自建MySQL这个值才为128M,相差接近百倍,好奇心驱使着我快速一探究竟。

看下官方文档对于innodb_buffer_pool的解释:

  • The buffer pool is a memory area that holds cached data for tables, indexes, and other auxiliary buffers. For efficiency of high-volume read operations, the buffer pool is divided into pages that can potentially hold multiple rows. For efficiency of cache management, the buffer pool is implemented as a linked list of pages; data that is rarely used is aged out of the cache, using a variation of the LRU algorithm. For more information, see Section 15.5.1, “Buffer Pool”. InnoDBInnoDB

    The size of the buffer pool is important for system performance:

    • InnoDB allocates memory for the entire buffer pool at server startup, using operations. The innodb_buffer_pool_size system variable defines the buffer pool size. Typically, a recommended innodb_buffer_pool_size value is 50 to 75 percent of system memoryinnodb_buffer_pool_size can be configured dynamically, while the server is running. For more information, see Section 15.8.3.1, “Configuring InnoDB Buffer Pool Size”. malloc()

    • On systems with a large amount of memory, you can improve concurrency by dividing the buffer pool into multiple buffer pool instances. The innodb_buffer_pool_instances system variable defines the number of buffer pool instances.

    • A buffer pool that is too **all may cause excessive churning as pages are flushed from the buffer pool only to be required again a short time later.

    • A buffer pool that is too large may cause swapping due to competition for memory.

提取出这么几个重要信息:

1.缓冲池是保存表、索引和其他辅助缓冲区的缓存数据的内存区域;

2.缓冲池的大小对MySQL的性能很重要;

3.推荐的大小是物理内存的50%到75%;

4.inndb_buffer_pool_size可以在MySQL运行时动态调整。

5."Configuring InnoDB Buffer Pool Size"这个配置手册中提到默认值为128兆。

再看看《MySQL技术内幕InnoDB存储引擎》这本书中关于缓冲池的介绍:

缓冲池简单来说就是一块内存区域,通过内存的速度来弥补磁盘速度较慢对数据库性能的影响。在数据库中进行读取页的操作,首先将从磁盘读到的页放在缓冲池中,这个过程称为将页“FIX“到缓冲池中。下一次再读相同的页时,首先判断该页是否在缓冲池中,若在缓冲池中,称该页在缓冲池中被命中,直接读取该页,否则,读取磁盘上的页。

做了一定的了解之后我决定动手去改一下这个值看看效果,之前提到客户已经把数据库服务器的配置扩到了16核32G,那我就把inndb_buffer_pool_size设置为32G*0.75=24G。

--注意:

--1.这里的单位是字节

--2.这是动态调整,长远来看这个配置应该放到配置文件中,防止重启丢失

set global innodb_buffer_pool_size = 25769803776

调完以后效果显著,我拿了几个耗时严重的SQL做了对比,查询时间减少一半以上,慢查数量明显下降,看看此时的数据库负载情况

 

调整缓冲池大小之前cpu和负载都很高,但是内存占用很低,调整完以后cpu和负载明显下降,内存占用高了,这正是我们所期待的效果。

看看客户给我们的反馈

 

 


总结

问题暂时告一段落,虽说没有100%的解决问题,但也解决了70%-80%的问题,那些剩余的20%-30%需要开发人员去优化代码,硬件扩容并不是银弹,庆幸的是,经过这次调整应该能给开发人员争取一些时间来优化代码。

另一方面也折射出自己对数据库的认识只是停留在开发视角的一些知识,比如SQL优化、索引的匹配、慢查优化等等,对于底层原理性的东西欠缺太多,有人会说这类东西应该交给DBA负责,这么说倒也没什么问题,但现实情况是有些团队就是没有配备DBA的,只能开发顶,再者说,作为开发而言,对于这类知识不一定要熟练掌握,但起码应该有一些初步的认识。

 

请先登录,感受更多精彩内容
快去登录吧,你将获得
  • 浏览更多精彩评论
  • 和开发者讨论交流,共同进步

为你推荐

MySQL之KEY分区引发的血案
需求背景业务表tb_image部分数据如下所示,其中id唯一,image_no不唯一。image_no表示每个文件的编号,每个文件在业务系统中会生成若干个文件,每个文件的唯一ID就是字段id:业务表t
MySQL 死锁套路:一次诡异的批量插入死锁问题分析
线上最近出现了批量insert的死锁,百思不得解。死锁记录如下:```2018-10-26T11:04:41.759589Z 8530809 [Note] InnoDB: (1) TRANSACTI
MySQL 死锁套路:唯一索引下批量插入顺序不一致
死锁的本质是资源竞争,批量插入如果顺序不一致很容易导致死锁,我们来分析一下这个情况。为了方便演示,把批量插入改写为了多条 insert。先来做几个小实验,简化的表结构如下:```CREATE TABL
MySQL 死锁套路:再来看一例走不同索引更新的例子
前面有文章介绍了利用调试MySQL源码的方式来调试锁相关的信息,这里利用这个工具来解决一个比较简单的问题,线上的表字段较多,这里简单成为了一个表:```CREATE TABLE `t3` ( `id
如何在 Mac 下用 Clion 调试 MySQL 源码
前面写了几篇文章来通过调试 MySQL 源码来分析死锁问题,有读者问如何用 IDE 调试源码,这篇文章简单介绍一下如何在 Mac 下调试。之所以使用调试的方式来分析死锁问题是因为在解决 MySQL 死
掉坑了!GROUP_CONCAT函数引发的线上问题
本文分享一篇在工作遇到的一个问题,关于MySQL GROUP_CONCAT函数导致的问题。希望能帮忙到你。 业务场景在说遇到的坑之前,先描述一下大致的业务场景。系统有一个排班的功能,一个医生一天可以排
空中楼阁之纸上谈兵 mysql的dbcp的配置
maxWait=-1 最大等待时间:当没有可用连接时,连接池等待连接被归还的最大时间(以毫秒计数),超过时间则抛出异常,如果设置为-1表示无限等待testOnBorrow=true指明是否在从池中取出
一个诡异的MySQL查询超时问题,居然隐藏着存在了两年的BUG
这一周线上碰到一个诡异的BUG。线上有个定时任务,这个任务需要查询一个表几天范围内的一些数据做一些处理,每隔十分钟执行一次,直至成功。通过日志发现,从凌晨5:26分开始到5:56任务执行了三次,三次都