[MDEV-20487] Set innodb_adaptive_hash_index=OFF by default Created: 2019-09-04  Updated: 2023-10-12  Resolved: 2019-10-23

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Fix Version/s: 10.5.0

Type: Task Priority: Major
Reporter: Marko Mäkelä Assignee: Marko Mäkelä
Resolution: Fixed Votes: 0
Labels: corruption, performance

Attachments: File MDEV-20487-Remove-the-InnoDB-adaptive-hash-index.patch    
Issue Links:
Relates
relates to MDEV-22646 Assertion `table2->cached' failed in ... Closed
relates to MDEV-24505 innodb.innodb-ucs2 test failed, asse... Closed
relates to MDEV-12121 Introduce build option WITH_INNODB_AH... Closed
relates to MDEV-17492 Benchmark AHI to find cases where it'... Closed
relates to MDEV-18815 Server crashes on TRUNCATE TABLE Closed
relates to MDEV-20203 InnoDB: Failing assertion: (block)->i... Confirmed
relates to MDEV-31668 With certain queries, 10.6 is slower ... Closed

 Description   

Based on the performance testing that was conducted in MDEV-17492, the InnoDB adaptive hash index could only help performance in specific, almost-read-only workloads. It could slow down all kinds of workloads (especially DROP TABLE, TRUNCATE TABLE, ALTER TABLE, or DROP INDEX operations), and it can become corrupted, causing crashes (such as MDEV-18815, MDEV-20203) and possibly data corruption. Furthermore, the adaptive hash index consumes space from the InnoDB buffer pool, which could hurt performance when the working set would almost fit in the buffer pool.

Given all this, it is best to disable the adaptive hash index.

 Comments   
Comment by Marko Mäkelä [ 2019-10-23 ]

As observed in MDEV-19613, the adaptive hash index can significantly improve read performance in a scenario where there are relatively short bursts of writes interleaved with long periods of read-mostly access. For this reason, we probably should keep the adaptive hash index.

However, according to MDEV-17492, in cases where reads and writes are interleaved more evenly, the adaptive hash index does not help. That (and the known open bugs) is why we will disable the adaptive hash index by default in MariaDB Server 10.5.

Comment by Marko Mäkelä [ 2019-10-28 ]

MDEV-20487-Remove-the-InnoDB-adaptive-hash-index.patch is based on 10.5 1c022aaf584e69531765ec6be97012ecf1252f5c and would change the build-time option to WITH_INNODB_AHI=OFF, to stop building the adaptive hash index by default. We might choose to apply it at a later time.

Comment by Marko Mäkelä [ 2020-05-26 ]

MDEV-22646 could cause corruption of the buffer pool after the buffer pool has been resized.

Comment by sushma k [ 2020-07-17 ]

Hi Marko,

I understand to enable adaptive hash index the option is --innodb_adaptive_hash_index and to disable them the
option is --skip-innodb_adaptive_hash_index.

Can you please confirm if these settings will be persistent across boot and if not, is there any other way to achieve
the same.

Comment by Daniel Black [ 2020-07-18 ]

Put these without – prefix in your confiugraiton file under section read by mariadb and they will be re-read on restart.

Comment by sushma k [ 2020-07-20 ]

Thank you Daniel Black for the reply. So we need to place it in my.cfg file the below lines:
skip-innodb_adaptive_hash_index

Comment by Daniel Black [ 2020-07-21 ]

yes

Comment by sushma k [ 2020-07-22 ]

Hi Daniel,

Is there any way that i can check if the innodb_adaptive_hash_index is enabled or not ? by using any cli command.

Comment by Daniel Black [ 2020-07-22 ]

yes. `show global variables like 'innodb_adaptive_hash_index'`.

Please use https://dba.stackexchange.com/ or https://mariadb.zulipchat.com for further questions. This site isn't a support forum.

Comment by sushma k [ 2020-07-27 ]

Okay Thank you .

Generated at Thu Feb 08 08:59:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.