RDS MySQL Adaptive Hash Index (AHI)最佳实践

背景

很多客户在使用MySQL时大部分只要语法执行没问题 一般来讲是不太会关注更多细节的。像MySQL的AHI就是其中一个细节。内存的延时是120ns 访问内存里的数据是比较快的。InnoDB监视对每个索引的使用情况 如果它注意到某些索引页经常被访问 并可以从建立AHI中受益 那它会自动为该索引在Buffer Pool里构建hash table。基于InnoDB观察到的搜索模式 它使用索引键的前缀来构建AHI。键的前缀可以是任何长度 而且可能只有B树中的一些值出现在AHI中。

是否使用AHI可以通过innodb_adaptive_hash_index控制。尽管InnoDB可以监控索引的访问 但直到现在也没有对AHI自己的稳定性监控 以至于在生产上遇到某些不适合的场景 需要人为判断要不要禁用AHI。从数据结构上来说 AHI并不适用于所有访问场景 本篇文章就来聊聊它的使用场景。

AHI的关键节点 MySQL从5.5版本AHI诞生

但只有一个hash table 只有一把锁 有时保护对AHI的读/写锁可能会在高负载(如多个并发连接)下成为瓶颈。

通过show engine innodb status看到的信息是:

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 50999503, node heap has 1 buffer(s)
300220.95 hash searches/s, 364993.48 non-hash searches/s

MySQL5.7里增加了innodb_adaptive_hash_index_parts

为了解决上述瓶颈问题 从5.7开始(RDS MySQL从5.6支持)对AHI特征进行了分区 分区由innodb_adaptive_hash_index_parts变量控制。MySQL通过取模将AHI锁打散到多个hash table上 也就意味着打散后的hash table越多 AHI锁就打得更散 锁的粒度就更细 并发查询时构建AHI的性能就更好。

通过show engine innodb status看到的信息是:

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 19164 merges
merged operations:
 insert 135, delete mark 19087, delete 33
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 6375037, node heap has 1432 buffer(s)
Hash table size 6375037, node heap has 2884 buffer(s)
Hash table size 6375037, node heap has 1 buffer(s)
Hash table size 6375037, node heap has 1 buffer(s)
Hash table size 6375037, node heap has 1443 buffer(s)
Hash table size 6375037, node heap has 1440 buffer(s)
Hash table size 6375037, node heap has 1 buffer(s)
Hash table size 6375037, node heap has 18606 buffer(s)
1085.85 hash searches/s, 0.24 non-hash searches/s

从上面的信息可以看到如果node heap has * buffer(s)不均衡的话 说明AHI有数据倾斜。hash searches/s 命中hash索引的次数 non-hash searches未命中hash索引的次数。?

适合的场景只支持 和in不适合的场景

AHI的工作流程参考MySQL内核月报的“AHI介绍”。在下面的访问不能使用AHI

drop table、truncate table、alter table 、drop index分区等like、regexp、 等任何范围查询有spatial索引排序

在DDL时打开AHI容易引起数据库卡顿的原因如下

在删除表时 需要先通过扫描LRU链表找到该表在AHI使用的数据页 将这些数据从AHI中删除。如果为MySQL实例配置较多的物理内存 扫描LRU链表可能会导致数据库性能异常甚至数据库Crash。删除AHI的操作正在执行 而InnoDB数据字典缓存dict_sys被独占锁定 进而影响正常业务获取MDL锁 阻塞正常业务。关闭AHI

在实际的生产环境场景比较复杂 又很难事先预测AHI是否适合实际生产需要 对于不能从AHI受益的场景 通过SHOW ENGINE INNODB STATUS中SEMAPHORES可以部分监控AHI的使用情况 当看到许多线程在等待btr0sea.c中创建的RW-latch 可以考虑关闭AHI。

鉴于阿里云用户反馈的SQL执行慢或卡顿问题有很多跟打开AHI有关 所以阿里云RDS MySQL 5.6 5.7实例从2020-12-02 20:00之后 / 8.0版本从2021-05-22 17:05之后的新购实例默认关闭AHI。对于旧有版本还需要用户自行关闭。

具体操作可以参考下图

总结

虽然在MySQL官方版本中每个版本都是把AHI默认打开 但根据实际生产中的表现来看却差强人意 InnoDB AHI只能在特定的、几乎是只读的场景中才能提高性能 而对于诸如 drop table、truncate table、alter table或drop index等场景表现不佳 甚至导致MySQL hung或极端场景下的数据损坏。此外 AHI会消耗Buffer Pool的空间 这可能会影响性能。当有上述语句执行时可以在执行之前把AHI关掉 执行完后再打开。但这一系列操作比较麻烦 稍有疏忽可能会影响业务 考虑打开AHI弊大于利 最好禁用AHI。

参考

https://bugs.mysql.com/bug.php?id 100512

https://bugs.mysql.com/bug.php?id 101667

https://bugs.mysql.com/bug.php?id 62018

https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_adaptive_hash_index

https://jira.mariadb.org/browse/MDEV-20487

本文转自网络，原文链接：https://developer.aliyun.com/article/786124
本站部分内容转载于网络，版权归原作者所有，转载之目的在于传播更多优秀技术内容，如有侵权请联系QQ/微信：153890879删除，谢谢！