Innodb_adaptive_hash_index and partition management performance, MySQL 8.0 (8.0.33-25)

1

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

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

2

Hello @egel,

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

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

This is usually one of our recommendations. In general, the adaptive hash index has been shown to be a bottleneck for InnoDB.

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

3

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.

4

Turning it off is dynamic, though.