# Innodb\_adaptive\_hash\_index and partition management performance, MySQL 8.0 (8.0.33-25)

**URL:** <https://forums.percona.com/t/innodb-adaptive-hash-index-and-partition-management-performance-mysql-8-0-8-0-33-25/24007>\
**Category:** Uncategorized\
**Created:** [July 28, 2023, 6:55pm UTC](https://forums.percona.com/t/innodb-adaptive-hash-index-and-partition-management-performance-mysql-8-0-8-0-33-25/24007 "2023-07-28T18:55:13Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![egel](https://avatars.discourse-cdn.com/v4/letter/e/e36b37/32.png) [@egel](https://forums.percona.com/u/egel)\
**Post date:** [July 28, 2023, 6:55pm UTC](https://forums.percona.com/t/innodb-adaptive-hash-index-and-partition-management-performance-mysql-8-0-8-0-33-25/24007/1 "2023-07-28T18:55:13Z")

</div>

Hello,

I noticed a bug in version 8.0 (specifically, I am using 8.0.33-25) that is easily replicable. The bug can be found at the following link: [https://bugs.mysql.com/bug.php?id=101900](https://bugs.mysql.com/bug.php?id=101900)

To replicate the bug, you can follow these steps:

1. Create a partitioned table with 52 partitions. Here is an example table structure:

CREATE TABLE `prttest` ( `id1` int unsigned NOT NULL DEFAULT ‘0’, `id2` int unsigned NOT NULL DEFAULT ‘0’, `id3` smallint unsigned NOT NULL DEFAULT ‘0’, `id4` smallint unsigned NOT NULL DEFAULT ‘0’, `date1` datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’, `id5` int unsigned DEFAULT NULL, `id6` int unsigned DEFAULT NULL, PRIMARY KEY (id1, id2, id3, id4, date1), KEY `idd` (date1, id5, id6) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 – PARTITION BY RANGE (to\_days(`date1`))

1. Insert 5M rows (I inserted 4.8M in my case).
2. Set global innodb\_adaptive\_hash\_index=1.
3. Add a new partition using the “alter table add partition” command. The time taken to add the partition is significant, around 30 seconds in my case.

However, when adding the next partition, the time is reasonable, under a second.

After recreating the table and setting global innodb\_adaptive\_hash\_index=0, adding a new partition runs under a second from the first time.

I have a few questions regarding this issue:

1. Has anyone else encountered this bug?
2. In which version is it fixed or planned to be fixed?
3. How critical is it to set innodb\_adaptive\_hash\_index to 1 compared to setting it to 0?

Best regards,  
Evgeni

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [July 28, 2023, 9:27pm UTC](https://forums.percona.com/t/innodb-adaptive-hash-index-and-partition-management-performance-mysql-8-0-8-0-33-25/24007/2 "2023-07-28T21:27:46Z")

</div>

Hello @egel,

> [@egel](#):
>
> [https://bugs.mysql.com/bug.php?id=101900](https://bugs.mysql.com/bug.php?id=101900)

This is good that MySQL is able to verify and reproduce it as well. That increases the likelihood they will fix it soon.

> [@egel](#):
>
> - In which version is it fixed or planned to be fixed?

Only Oracle MySQL knows this. Maybe it will be fixed in .34, or .35. We have no idea.

> [@egel](#):
>
> innodb\_adaptive\_hash\_index=0,

This is usually one of our recommendations. In general, the adaptive hash index has been shown to be a [bottleneck for InnoDB](https://www.percona.com/blog/is-adaptive-hash-index-in-innodb-right-for-my-workload/).

Have you tried increasing the number of AHI partitions and seeing if that helps?

---

<div class="post-metadata">

**Author:** ![egel](https://avatars.discourse-cdn.com/v4/letter/e/e36b37/32.png) [@egel](https://forums.percona.com/u/egel)\
**Post date:** [July 28, 2023, 10:15pm UTC](https://forums.percona.com/t/innodb-adaptive-hash-index-and-partition-management-performance-mysql-8-0-8-0-33-25/24007/3 "2023-07-28T22:15:21Z")

</div>

Unfortunately, I managed to replicate it only on the production system, where I have a large memory allocation. I believe that the innodb\_buffer\_pool\_size needs to be large enough to trigger the bug (I can’t provide an exact number, but it’s definitely greater than 4GB; the production system has over 100GB).

The innodb\_adaptive\_hash\_index\_parts is not dynamic and therefore I can’t play with it.

Thanks.

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [July 28, 2023, 10:21pm UTC](https://forums.percona.com/t/innodb-adaptive-hash-index-and-partition-management-performance-mysql-8-0-8-0-33-25/24007/4 "2023-07-28T22:21:10Z")

</div>

> [@egel](#):
>
> The innodb\_adaptive\_hash\_index\_parts is not dynamic and therefore I can’t play with it.

Turning it off is dynamic, though.