首页 > 数据库 >AHI对MySQL性能的影响

AHI对MySQL性能的影响

时间:2024-03-22 12:29:30浏览次数:25  
标签:-- 性能 AHI db mysql MySQL sysbench

MySQL中出现很多latch锁,而这个很大程度上和MySQL自适应hash索引有关。

在这里插入图片描述

AHI概述

MySQL InnoDB存储引擎的自适应哈希(Adaptive Hash Index,下简称AHI)功能

若用户的访问模式基本都是类似KV操作的点查询(point select),则InnoDB存储引擎会自动创建哈希索引。

在有了哈希索引后,查询无需走B+树搜索,而直接通过hash就能直接定位查询的数据。

因此,通过AHI功能,MySQL的查询性能就能得到大幅提升。

查询能提升多少呢?对于写入操作又是否有影响呢?AHI有什么副作用么?

AHI对读写性能影响
查询压测

[root@VM-16-10-centos ~]# sysbench /usr/share/sysbench/oltp_point_select.lua --mysql-host=10.0.16.10 --mysql-port=3307 --mysql-user=cjr --mysql-password=cjr --mysql-db=sysbench --db-driver=mysql --tables=1 --table-size=10000000 --report-interval=1 --threads=2 --rand-type=uniform --max-requests=1000000 run >sysbench_off.log
[root@VM-16-10-centos ~]# sysbench /usr/share/sysbench/oltp_point_select.lua --mysql-host=10.0.16.10 --mysql-port=3307 --mysql-user=cjr --mysql-password=cjr --mysql-db=sysbench --db-driver=mysql --tables=1 --table-size=10000000 --report-interval=1 --threads=2 --rand-type=uniform --max-requests=1000000 run >sysbench_on.log

从下图可以看到,在AHI的加持下,主键查询可以达到2540的QPS:
在这里插入图片描述

但若当我们通过参数 innodb_adaptive_hash_index 关闭AHI功能后,主键查询的性能下跌了最高只达到2371QPS:
在这里插入图片描述

set global innodb_adaptive_hash_index =off;

DML压测

对于写入操作,开启和关闭AHI功能对性能的影响都没有太大的区别。

这是因为当DML测试时, MySQL 的瓶颈主要是在磁盘写入。

具体可以通过 sysbench 的 oltp_update_index.lua 基准测试进行观察。

sysbench /usr/share/sysbench/oltp_update_index.lua --mysql-host=10.0.16.10 --mysql-port=3307 --mysql-user=cjr --mysql-password=cjr --mysql-db=sysbench --db-driver=mysql --tables=1 --table-size=10000000 --report-interval=1 --threads=2 --rand-type=uniform --max-requests=1000000 run >sysbench_off.log
sysbench /usr/share/sysbench/oltp_update_index.lua --mysql-host=10.0.16.10 --mysql-port=3307 --mysql-user=cjr --mysql-password=cjr --mysql-db=sysbench --db-driver=mysql --tables=1 --table-size=10000000 --report-interval=1 --threads=2 --rand-type=uniform --max-requests=1000000 run >sysbench_on.log

打开AHI的情况
在这里插入图片描述

关闭AHI的情况
在这里插入图片描述

可以观察不管是否打开AHI,TPS都差不多

AHI的副作用

作为一个几乎透明的功能,其实一般用户无需关心,基本可以认为AHI是即开即用的功能。

默认AHI参数的设置也是比较合理的,例如参数 innodb_adaptive_hash_index_parts 设置为 8 。

然而,AHI存在一个副作用:当删除大表,且缓冲池(Buffer Pool,下简称BP)比较大,如超过32G,则MySQL数据库可能会有短暂被hang住的情况发生。

这时会对业务线程造成一定影响,从而导致业务系统的抖动。

产生这个问题的原因是在删除表的时候,InnoDB存储引擎会将该表在BP中的内存都淘汰掉,释放可用空间。

这其中包括数据页、索引页、自适应哈希页等。

当BP比较大是,扫描BP中flush_list链表需要比较长的时间,因此会产生系统的抖动。

因此在海量的互联网并发业务中,删除表操作需要做精细的逻辑控制,如:

  1. 业务低峰期删除大表;

  2. 删除表前禁用AHI功能;

  3. 控制脏页链表长度,只有长度小于一定阈值,才发起删除操作;

  4. 删除表后启用AHI功能;

不过呢,所有这么麻烦的处理在 MySQL 8.0.23 版本之后,就都不再需要了。

因为官方已经彻底修复了这个问题:

注意,截至目前最新的 MySQL 5.7.34 版本并没有进行修复,所以 5.7 还需要用户自己做控制。

总结

AHI真的是一个InnoDB的好功能,这才是真正的AI数据库该有的样子。

但InnoDB存储引擎在2001年发布时,就支持了这个功能,创始人Heikki Tuuri真可谓具有卓越的远见。

但在使用时,要特别注意删除大表可能产生的hang住问题。

这会对业务产生一定的影响,对于较高要求的业务来说,或许是不能接受的。

不过,若升级到 MySQL 8.0.23 版本后,这些问题将不复存在。

本文参考https://zhuanlan.zhihu.com/p/379683912

标签:--,性能,AHI,db,mysql,MySQL,sysbench
From: https://blog.csdn.net/DBA_ChenJR/article/details/136857122

相关文章

  • mysql for update是锁表还是锁行
    转载至我的博客https://www.infrastack.cn,公众号:架构成长指南在并发一致性控制场景中,我们常常用forupdate悲观锁来进行一致性的保证,但是如果不了解它的机制,就进行使用,很容易出现事故,比如forupdate进行了锁表导致其他请求只能等待,从而拖垮系统,因此了解它的原理是非常必要的......
  • Linux hdparm命令教程:优化硬盘性能和读写速度(附实例详解和注意事项)
    Linuxhdparm命令介绍hdparm是一个用于控制和配置硬盘驱动器的命令行工具。它允许您查看和修改硬盘的参数,包括缓存设置、高级电源管理、硬盘性能等。通过hdparm,您可以优化硬盘的读写速度和性能。Linuxhdparm命令适用的Linux版本hdparm在大多数Linux发行版中都可用,......
  • 良心干货,常见的MySQL索引类型枚举!!!
    在MySQL中,索引是一种用于快速查询和检索数据的数据结构。它们类似于书籍的目录,可以帮助数据库系统更快地定位到特定的数据行,而不需要扫描整个数据表。索引可以显著提高查询性能,特别是在处理大量数据时。MySQL支持多种类型的索引,每种索引都有其特定的使用场景和优势。以下是......
  • 【测试开发学习历程】MySQL分组查询与子查询 + MySQL表的联结操作
    目录1 MySQL分组查询与子查询1.1数据分组查询1.2过滤分组1.3分组结果排序1.4select语句中子句的执行顺序1.5子查询2 MySQL表的联结操作2.1关系表2.2表联结2.3笛卡尔积2.4内部联结2.5外联结2.6自联结2.7组合查询1 MySQL分组查询与子查询1.1......
  • 浅谈MySQL中的外键、索引和性能优化
    当我们讨论MySQL中的外键、索引和优化时,我们通常指的是为了提高数据库查询效率、数据完整性和整体性能而采取的一系列措施。1.外键(ForeignKeys)定义:外键是一个字段,它在一个表中引用另一个表的主键。外键用于确保数据引用完整性和在两个表之间建立关系。举例:假设......
  • 【测试开发学习历程】MySQL增删改操作 + 备份与还原 + 索引、视图、存储过程
    前言:SQL内容的连载,到这里就是最后一期啦!如果有小伙伴要其他内容的话,我会追加内容的。(前提是我有学过,或者能学会)接下来,我们就要开始python内容的学习了~~ 目录1 MySQL增删改操作1.1数据添加操作1.1.1插入完整的行1.1.2插入多行1.2数据更新操作1.3数据删除操......
  • PHP+MySQL开发组合:智慧同城便民信息小程序源码系统 带完整的安装代码包以及安装部署教
    当前,城市生活的节奏日益加快,人们对各类便民信息的需求也愈发迫切。无论是寻找家政服务、二手交易,还是发布租房、求职信息,一个高效、便捷的信息平台显得尤为重要。传统的信息发布方式往往存在信息更新不及时、查找困难等问题,无法满足现代都市人的需求。罗峰给大家分享一款智慧同......
  • MySQL必知必会
    #Mysql##Mysql事务###原则1.只有使用了INNODB引擎的数据库或表才支持事务2.原子性:具备回滚机制,只有全部完成和全部不完成两种状态。3.一致性:事务开启前和结束后数据库完整性没有被破坏。4.隔离性:数据库允许多个并发事务同时对其数据进行读写和修改的能力。防止事务同时执行......
  • 毕业设计课题:实验室课程管理系统,基于java+SSM+mysql
          一、前言介绍     如今互联网发展迅猛,大量的信息都是通过网络这一渠道来传播,所以利用网络渠道来传播知识是非常有前景的。线上管理系统的主要目的是对实验室课程信息进行更有效的管理,光靠现有的管理方式是远远不够的,因此开发实验室课程管理系统是有必要的......
  • 毕业设计课题:少儿编程管理系统,基于java+SSM+mysql
          一、前言介绍     21世纪,我国早在上世纪就已普及互联网信息,互联网对人们生活中带来了无限的便利。像大部分的企事业单位都有自己的系统,由从今传统的管理模式向互联网发展,如今开发自己的系统是理所当然的。那么开发少儿编程管理系统意义和用处有哪些呢? ......