MySQL数据库CPU问题一则

Wesley13
• 阅读 514

作者:张政俊,中欧基金DBA

Mysql 一般出现 CPU 负载过高问题的时候,我们都会去看下故障期间的慢sql日志,然后找出全表扫描、索引不合理、函数运算过多的sql,让开发同学优化下。实在不行的话,那就升级CPU硬件,替换更高频率的CPU,1路的升级成2路,2路的升级成四路。

这次出现的问题因为关乎到每天的业务处理,所以很多措施无法第一时间到位,比如硬件采购,所以只能先从数据库端考虑,定位具体的瓶颈,看看有没有参数优化空间。

排查期间走了不少弯路,但好在最终解决了问题。现在开始回顾下整个问题处理的过程:

一. 问题出现

晚上业务高峰期有一套数据库集群的主库出现 threads running 过多的告警,应用也频繁出现响应速度慢的告警。这时候processlist列表中非sleep的sql已达到1000+,数据库已经没法处理,后续进来的sql全部堆积在里面。1分钟后,处于running状态的sql达到了2700+。

此时使用kill命令杀sql,都会处于killed状态,sql进程并不会正真的结束。

top命令看到CPU有压力,但是没打满,iowait不高。

free下看到内存尚有20G余量,且基本没用到swap。

iostat下看到磁盘读写并不高,没有压力。

二. 问题处理

第一次处理,主要从三个角度去思考:

1. 慢sql优化

把故障期间的慢sql日志拿了出来,分析了下TOP30的慢sql。部分sql确实扫了全表(表中数据量不多),然后很多sql都有count、order by逻辑,这些操作确实会开销cpu。

这些sql单独拿出来执行,其实并不是很慢,大约在1,200毫秒左右,其实是可以接受的。而且我们开发资源比较紧张,如果要去优化这些业务核心sql,没有两三周是完不成的。

因为每天都会有这个任务,所以sql优化的优先级先往后推推。

2. 业务降低并发

直接降低业务的并发数,这个措施看起来最简单有效,我们也确实这么了,直接砍了一半的并发量。部分应用服务器jdbc配置中的Maxactive也减半。

3. 数据库参数修改

把 innodb_flush_method 参数从默认的 fdatasync 改为 O_DIRECT。

目的是直接将 write 操作从 innodb buffer 刷到磁盘,中间不通过 os 缓存去中转。这样写入操作(insert,update,delte)响应时间会变很长,但是可少减少了io和cpu的开销,物理内存也可以降低使用率。

  • fdatasync

    数据库默认配置,写数据的时候只要写入 os buffer 就算完成,之后由操作系统去 flush 到磁盘。

MySQL数据库CPU问题一则

image

  • O_DIRECT

    数据文件的写入直接从 buffer pool 刷到磁盘,中间不通过 os buffer 中转。

    MySQL数据库CPU问题一则

    image

三. 问题重现

准备工作完成,但是当下一次任务跑的时候,数据库又出现了和上一次一样的情况。

不过,这一次在重启数据库前打了pstack日志。

四. 问题重新定位

1. 系统信息

为了更深入地定位问题,这次分析了 sar 日志:

故障期间发生了大量的上下文切换:MySQL数据库CPU问题一则

CPU 响应队列达到了100+,平均负载也到达100+:

runq-sz:Run queue length (number of tasks waiting for run time).

MySQL数据库CPU问题一则

image

至此,可以肯定确定问题在 CPU 层了。

2. pstack 日志

在pstack日志中,发现了很多 btr_search_info_update & btr_search_info_update_slow:

MySQL数据库CPU问题一则

image

圈出来的都是用来建立 AHI 的函数。

看下 btr_search_info_update 的调用链:

btr_cur_search_to_nth_level→ btr_search_info_update→ btr_search_info_update_slow→ btr_search_build_page_hash_index

在 btr_search_info_update_slow 中,根据统计信息作出决定,调用 btr_search_build_page_hash_index 把当前页面的记录加入AHI的hash table。

3. AHI 自适应哈希索引

现在追踪定位到了 AHI ,那么接下来深入了解下什么是 AHI。

innoDB存储引擎会监控对表上辅助索引页的查询。如果观察到建立hash索引可以提升性能,就会在缓冲池建立hash索引,称之为自适应哈希索引(Adaptive Hash Index,AHI)

AHI是通过缓冲池的B+ Tree构造而来,使用索引键的前缀来构建哈希索引,前缀可以是任意长度。因此构建速度很快,而且不需要对整张表构建hash索引。InnoDB存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立hash索引。

根据InnoDB官方文档说明,启用了AHI后,读写的速度会提升2倍,辅助索引的连接操作性能可以提高5倍。

查询语句使用 AHI 的时候有以下优点:

  • 可以直接通过从查询条件直接定位到叶子结点,减少一次定位所需要的时间;

  • 在 buffer pool 不足的情况下,可以只针对热点数据页建立缓存,从而避免数据页频繁的 LRU;

但 AHI 也有它的问题,有这么一段说明:

With some workloads, the speedup from hash index lookups greatly outweighs the extra work to monitor index lookups and maintain the hash index structure. Access to the adaptive hash index can sometimes become a source of contention under heavy workloads, such as multiple concurrent joins.

在多并发连接的场景下,哈希索引的使用频率大大超过了监视哈希索引和维护哈希结构的频率,从而导致资源竞争,也就会消耗额外的CPU。

  • AHI监控:包括 AHI 方式查询的次数(adaptive_hash_searches)和使用 bree 查询的次数(adaptive_hash_searches_btree)

  • AHI维护:当物理记录的位置或者所在 block 的地址发生变化时,AHI 也需要对其进行相应的维护,可参考函数 btr_search_update_hash_ref() & btr_search_drop_page_hash_index() & buf_LRU_drop_page_hash_for_tablespace()

4. 查看5.6.26源码

我们当前使用的是 5.6.26 版本的数据库,看下该版本的源码:MySQL数据库CPU问题一则

MySQL数据库CPU问题一则

image

可以看到 rw_lock_s_lock(&btr_search_latch) 其中 btr_search_latch 是把全局锁。

五. AHI的问题解决

Mysql 5.6.26 版本是通过 innodb_adaptive_hash_index 参数来控制 AHI 的开关。

到这里不能说直接 OFF 以下这个参数就可以了,还有两个问题需要确认下:

  1. 这个全局锁之后优化了吗?

  2. 5.6.26下,这个参数到底有没有作用?

1. AHI 全局锁的优化

AHI 使用全局读写锁会导致资源竞争,这个bug显示,该问题已在5.7.7版本中修复:https://bugs.mysql.com/bug.php?id=62018

Fix bug https://bugs.mysql.com/bug.php?id=62018 (innodb adaptive hash index mutex contention) by partitioning btr_search_latch into a latch array indexed by index id. Add system variable innodb_adaptive_hash_index_partitions to control the number of partitions.

解决的方案就是对AHI进行锁拆分。把它设计成分区的形式,每个分区都有独立的latch来保护。分区大小是由 innodb_adaptive_hash_index_parts 参数配置,默认为8,最大为512。

这样看来,高版本确实已经做了优化。

2. 5.6版本里关闭参数是否有效

我们目前使用的是 5.6.26 版本,会不会存在关闭 innodb_adaptive_hash_index 但是 btr_search_latch 还存在的情况。

https://bugs.mysql.com/bug.php?id=50461

这个bug显示,在5.5版本下还是存在这个问题的,many uses of btr_search_latch with innodb_adaptive_hash_index is OFF

在最后的官方回复,该问题已经修复,merge vers: 5.6.1-m4,所以5.6.26版本下关闭 innodb_adaptive_hash_index 参数是有效的。

再去源码中看下关闭 AHI 的具体实现:MySQL数据库CPU问题一则

btr_search_disable 会释放 AHI 所使用的 buffer_pool 的内存。

六. 总结

这个问题总结下,就是在开启 AHI 后,遇到了大量并发连接,因为 AHI 会持有全局锁,导致了数据库大量锁自旋,消耗了大量的CPU。

目前数据库已关闭 AHI,该业务跑了两天,数据库一切正常。等年后还需要把数据库版本升上去,5.6确实有点落后了。

数据库遇到类似 CPU 问题,可以完成以下操作,对追溯问题根源是很有帮助的:

  • vmstat 1 1000

  • top -Hu mysql

  • perf top -a -g

  • show engine innodb status \G

  • show processlist

  • 重启前打pstack 日志(确定重启前才能打,其他时候不能乱打)

最后要特别感谢下八怪(高鹏老师)的帮助与指导,想更深入了解Mysql主从原理和学习源码的朋友,可以看下八怪的专栏https://www.jianshu.com/nb/43148932 ,看完之后对数据库理解肯定能更上一层楼。

全文完。

Enjoy MySQL :)

本文分享自微信公众号 - 老叶茶馆(iMySQL_WX)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
待兔 待兔
2星期前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Stella981 Stella981
2年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Easter79 Easter79
2年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
2年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
6个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这