Page MenuHomePhabricator

Consider setting innodb_adaptive_hash_index=OFF by default
Closed, ResolvedPublic

Description

Reported via security@:
https://jira.mariadb.org/browse/MDEV-20487

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.

From the doc (https://mariadb.com/kb/en/innodb-system-variables/#innodb_adaptive_hash_index):

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.

Roles done:

  • parsercache
  • misc_multiinstance (db1117, db2078 - stand by hosts)
  • misc masters
  • phabricator (both master and multi-instance replicas)
  • analytics_multiinstance
  • dbstore_multiinstance
  • Sanitarium hosts
  • db-inventory (db1115/db2093)
  • core_multiinstance
  • core

Event Timeline

Marostegui triaged this task as Medium priority.Nov 27 2020, 6:13 AM
Marostegui moved this task from Triage to Ready on the DBA board.

Related to Etherpad.

https://github.com/ether/etherpad-lite/issues/4522 is our link back.

You guys have better DBAs than us so will allow your DBAs to feed in.

I am going to start testing this after end of year holidays. I am mostly interested in seeing if this has any performance impact on our RO ES hosts first.

Marostegui moved this task from Ready to In progress on the DBA board.

I am going to first set this to 0 on the following ES read-only codfw hosts, just to see if stability-wise there're issues (I won't expect them though):

es1: es2028
es2: es2026
es3: es2029

Mentioned in SAL (#wikimedia-operations) [2022-01-31T08:21:05Z] <marostegui> Set innodb_adaptive_hash_index=OFF on es2028, es2029, es2026 T268869

Extended it to es4 and es5 (RW) hosts:
es4: es2020
es5: es2024

Mentioned in SAL (#wikimedia-operations) [2022-01-31T08:22:15Z] <marostegui> Set innodb_adaptive_hash_index=OFF on es2020, es2024 T268869

Ran innodb_adaptive_hash_index=OFF on:

db1129 (s2 core host)
es1029 (es1)
es1030 (es2)
es1028 (es3)
es1020 (es4)
es1023 (es5)

Let's see how that goes.

Mentioned in SAL (#wikimedia-operations) [2022-02-01T12:56:09Z] <marostegui> Set innodb_adaptive_hash_index=OFF on: db1129 es1029 es1030 es1028 es1020 es1023 T268869

Deployed this on the active pc hosts: pc1011, pc1012 and pc1013 so we can test a different workload.

Change 759226 had a related patch set uploaded (by Marostegui; author: Marostegui):

[operations/puppet@production] parsercache.my.cnf: innodb_adaptive_hash_index=OFF

https://gerrit.wikimedia.org/r/759226

Change 759226 merged by Marostegui:

[operations/puppet@production] parsercache.my.cnf: innodb_adaptive_hash_index=OFF

https://gerrit.wikimedia.org/r/759226

Deployed this change on the parsercache puppet role for now.

Change 760815 had a related patch set uploaded (by Marostegui; author: Marostegui):

[operations/puppet@production] misc_multiinstance.my.cnf: innodb_adaptive_hash_index=OFF

https://gerrit.wikimedia.org/r/760815

Change 760815 merged by Marostegui:

[operations/puppet@production] misc_multiinstance.my.cnf: innodb_adaptive_hash_index=OFF

https://gerrit.wikimedia.org/r/760815

After deploying this on db1117 and db2078 (3321, 3322, 3323 and 3325) I have made this change on the puppet role.

I have changed db1115 and db2093 live, to see if this affects them. If not I will change their puppet role.

Change 761791 had a related patch set uploaded (by Marostegui; author: Marostegui):

[operations/puppet@production] db_inventory.my.cnf.erb: innodb_adaptive_hash_index=OFF

https://gerrit.wikimedia.org/r/761791

Change 761791 merged by Marostegui:

[operations/puppet@production] db_inventory.my.cnf.erb: innodb_adaptive_hash_index=OFF

https://gerrit.wikimedia.org/r/761791

I have changed db1115 and db2093 live, to see if this affects them. If not I will change their puppet role.

Pushed it to the puppet repo.

I have deployed this on sanitarium db2094 (s1, s3, s5 and s8) for now.

Change 762744 had a related patch set uploaded (by Marostegui; author: Marostegui):

[operations/puppet@production] sanitarium_multiinstance.my.cnf: innodb_adaptive_hash_index=OFF

https://gerrit.wikimedia.org/r/762744

Change 762744 merged by Marostegui:

[operations/puppet@production] sanitarium_multiinstance.my.cnf: innodb_adaptive_hash_index=OFF

https://gerrit.wikimedia.org/r/762744

I have deployed this on sanitarium db2094 (s1, s3, s5 and s8) for now.

Deployed also on db1154 (same sections).
And changed it on puppet too so the other two hosts will pick it up once rebooted.

Change 762792 had a related patch set uploaded (by Marostegui; author: Marostegui):

[operations/puppet@production] analytics_multiinstance.my.cnf: innodb_adaptive_hash_index=OFF

https://gerrit.wikimedia.org/r/762792

Change 762792 merged by Marostegui:

[operations/puppet@production] analytics_multiinstance.my.cnf: innodb_adaptive_hash_index=OFF

https://gerrit.wikimedia.org/r/762792

Change 763213 had a related patch set uploaded (by Marostegui; author: Marostegui):

[operations/puppet@production] phabricator_instance.my.cnf.erb: innodb_adaptive_hash_index=OFF

https://gerrit.wikimedia.org/r/763213

Change 763213 merged by Marostegui:

[operations/puppet@production] phabricator_instance.my.cnf.erb: innodb_adaptive_hash_index=OFF

https://gerrit.wikimedia.org/r/763213

Change 764292 had a related patch set uploaded (by Marostegui; author: Marostegui):

[operations/puppet@production] dbstore_multiinstance.my.cnf.erb: innodb_adaptive_hash_index=OFF

https://gerrit.wikimedia.org/r/764292

Change 764292 merged by Marostegui:

[operations/puppet@production] dbstore_multiinstance.my.cnf.erb: innodb_adaptive_hash_index=OFF

https://gerrit.wikimedia.org/r/764292

Change 764294 had a related patch set uploaded (by Marostegui; author: Marostegui):

[operations/puppet@production] misc.my.cnf.erb: innodb_adaptive_hash_index=OFF

https://gerrit.wikimedia.org/r/764294

Change 764294 merged by Marostegui:

[operations/puppet@production] misc.my.cnf.erb: innodb_adaptive_hash_index=OFF

https://gerrit.wikimedia.org/r/764294

Deployed on misc masters and dbstore_multiinstance

Change 768855 had a related patch set uploaded (by Marostegui; author: Marostegui):

[operations/puppet@production] core_multiinstance.my.cnf: innodb_adaptive_hash_index = OFF

https://gerrit.wikimedia.org/r/768855

Change 768855 merged by Marostegui:

[operations/puppet@production] core_multiinstance.my.cnf: innodb_adaptive_hash_index = OFF

https://gerrit.wikimedia.org/r/768855

Deployed on multi-instance core hosts.

Change 768959 had a related patch set uploaded (by Marostegui; author: Marostegui):

[operations/puppet@production] production.my.cnf: innodb_adaptive_hash_index = OFF

https://gerrit.wikimedia.org/r/768959

Change 768959 merged by Marostegui:

[operations/puppet@production] production.my.cnf: innodb_adaptive_hash_index = OFF

https://gerrit.wikimedia.org/r/768959

Marostegui updated the task description. (Show Details)

This has been deployed everywhere. It only took a year and a half :-)