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

2021-08-03 4898

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

RDS MySQL DuckDB 分析主实例，基础系列 4核8GB

RDS MySQL DuckDB 分析主实例，集群系列 4核8GB

RDS AI 助手，专业版

简介： 本文介绍了AHI (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:insert0,delete mark 0,delete0discarded operations:insert0,delete mark 0,delete0Hash 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:insert135,delete mark 19087,delete33discarded operations:insert0,delete mark 0,delete0Hash 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。