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 ] |
| Link |
This issue relates to |
| 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 |