MySQL数据库CPU问题一则

浅浅的花香味﹌ 2022-10-24 13:30 77阅读 0赞

> 作者：张政俊，中欧基金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 到磁盘。

![format_png][] image

*  O\_DIRECT
    
    数据文件的写入直接从 buffer pool 刷到磁盘，中间不通过 os buffer 中转。
    
    ![format_png 1][] image

### 三. 问题重现 ###

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

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

### 四. 问题重新定位 ###

#### 1. 系统信息 ####

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

故障期间发生了大量的上下文切换：![format_png 2][]

CPU 响应队列达到了100+，平均负载也到达100+：

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

![format_png 3][] image

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

#### 2. pstack 日志 ####

在pstack日志中，发现了很多 btr\_search\_info\_update & btr\_search\_info\_update\_slow：

![format_png 4][] 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 版本的数据库，看下该版本的源码：![format_png 5][]

![format_png 6][] 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 的具体实现：![format_png 7][]

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 :)

[format_png]: /images/20221024/28854ea5dd0e467eb12559970234e134.png
[format_png 1]: /images/20221024/10fdd1fb8f484fe0949a937a88353159.png
[format_png 2]: /images/20221024/7bc034ebe18d46789b59481019c49f05.png
[format_png 3]: /images/20221024/6a3c3a6528194f38a7c13845f1cea532.png
[format_png 4]: /images/20221024/0d5ce6939bee448098efe7a7572dde15.png
[format_png 5]: /images/20221024/95c1b67765f0437188b73f4ecbf2387a.png
[format_png 6]: /images/20221024/b39ef7fcd92a4e69ad9a174f132e4c3b.png
[format_png 7]: /images/20221024/ad82f999e854432fb6ea4df0edbd7667.png