Reported via security@:
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.
On 10.5 innodb_adaptive_hash_index=OFF will be set. We should consider even start testing it on 10.4 and see if it affects in a bad way on any of our workloads and turn it off already.
innodb_adaptive_hash_index¶ Description: If set to 1, the default until MariaDB 10.5, the InnoDB hash index is enabled. Based on performance testing (MDEV-17492), the InnoDB adaptive hash index helps performance in mostly read-only workloads, and could slow down performance in other environments, especially DROP TABLE, TRUNCATE TABLE, ALTER TABLE, or DROP INDEX operations.
- misc_multiinstance (db1117, db2078 - stand by hosts)
- misc masters
- phabricator (both master and multi-instance replicas)
- Sanitarium hosts
- db-inventory (db1115/db2093)