Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
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.
Attachments
Issue Links
- relates to
-
MDEV-22646 Assertion `table2->cached' failed in dict_table_t::add_to_cache
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Stalled
-
-
-
- Closed
-
-
-
- Confirmed
-
-
-
- Closed
-
- mentioned in
-
Page Loading...
Activity
| Field | Original Value | New Value |
|---|---|---|
| Link | This issue relates to MDEV-17492 [ MDEV-17492 ] |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link | This issue relates to MDEV-20203 [ MDEV-20203 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Summary | Remove the InnoDB adaptive hash index | Set innodb_adaptive_hash_index=OFF by default |
| 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 Given all this, it is best to remove the adaptive hash index. For now, we will change the default value of the build option {{WITH_INNODB_AHI}} to {{OFF}} and adjust tests. |
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 Given all this, it is best to disable the adaptive hash index. |
| issue.field.resolutiondate | 2019-10-23 20:01:32.0 | 2019-10-23 20:01:32.163 |
| Fix Version/s | 10.5.0 [ 23709 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Attachment | MDEV-20487-Remove-the-InnoDB-adaptive-hash-index.patch [ 49319 ] |
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.
MDEV-22646 could cause corruption of the buffer pool after the buffer pool has been resized.
| Link |
This issue relates to |
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.
Put these without – prefix in your confiugraiton file under section read by mariadb and they will be re-read on restart.
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
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.
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.
| Link |
This issue relates to |
| Workflow | MariaDB v3 [ 99397 ] | MariaDB v4 [ 134082 ] |
| Link |
This issue relates to |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 36148 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 36709 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 36709 ] |
| Link |
This issue relates to |
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.