RDS MySQL Adaptive Hash Index (AHI)最佳实践-阿里云开发者社区

开发者社区> 阿里云数据库> 正文
登录阅读全文

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

简介: 本文介绍了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:
 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。对于旧有版本还需要用户自行关闭。

具体操作可以参考下图:

image

总结

虽然在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


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

分享:
阿里云数据库
使用钉钉扫一扫加入圈子
+ 订阅

帮用户承担一切数据库风险,给您何止是安心!

官方博客
最新文章
相关文章
链接