Der Adaptive Hash Index: automatische InnoDB-Optimierung verstehen und gezielt tunen

1. Was der Adaptive Hash Index eigentlich tut

InnoDB organisiert seine Indizes grundsätzlich als B-Bäume, bei denen ein Equality-Lookup mehrere Seiten von der Wurzel bis zum Blatt durchqueren muss. Der Adaptive Hash Index, kurz AHI, beobachtet fortlaufend, welche Suchmuster auf welchen Seiten im Buffer Pool wiederholt auftreten, und baut für besonders häufig gleichartig angefragte Seiten zusätzlich eine Hash-Struktur direkt im Speicher auf, die vom Suchschlüssel direkt auf den Datensatzzeiger verweist.

Ist eine Hash-Struktur für ein bestimmtes Zugriffsmuster einmal aufgebaut, kann InnoDB den B-Tree für genau diese Art von Anfrage überspringen und den Datensatz direkt über den Hash finden. Der AHI ist dabei vollautomatisch: Es gibt keine manuelle Auswahl, welche Indizes gehasht werden, InnoDB entscheidet allein anhand beobachteter Zugriffshäufigkeit auf Basis eines internen Schwellenwerts.

2. Wie InnoDB entscheidet, wann eine Hash-Struktur entsteht

InnoDB verfolgt für jede Seite, wie oft sie mit demselben Präfix an Suchspalten angefragt wird. Erst wenn eine Seite über eine bestimmte Anzahl aufeinanderfolgender Zugriffe hinweg konsistent mit demselben Spaltenpräfix gesucht wird, legt InnoDB Hash-Einträge für diese Seite an. Reine Bereichsabfragen oder stark wechselnde Suchmuster erreichen diesen Schwellenwert selten oder nie, weshalb der AHI faktisch nur für Equality-Lookups auf punktuell heiße Seiten relevant wird.

Seit MySQL 5.7 ist der AHI intern partitioniert, standardmäßig in acht Partitionen über innodb_adaptive_hash_index_parts. Vor dieser Partitionierung schützte ein einziges globales Latch die gesamte Hash-Struktur, was bei hoher Parallelität selbst zum Engpass wurde. Die Aufteilung auf mehrere unabhängige Partitionen reduziert diese Contention deutlich, beseitigt sie aber nicht vollständig.

-- Aktuelle Partitionierung und Aktivierungsstatus pruefen
SHOW VARIABLES LIKE 'innodb_adaptive_hash_index%';

-- Hash-Aktivitaet im InnoDB-Statusreport einsehen
SHOW ENGINE INNODB STATUS\G
-- Abschnitt "INSERT BUFFER AND ADAPTIVE HASH INDEX" beachten

3. Wann der AHI tatsächlich einen messbaren Vorteil bringt

Der klassische Idealfall für den AHI ist eine Workload mit vielen wiederholten Punktabfragen auf denselben, gut zwischengespeicherten Seiten, etwa Lookups über einen Primärschlüssel oder einen eindeutigen Sekundärindex in einer OLTP-Anwendung mit überschaubarem Working Set. Hier spart die Hash-Struktur mehrere Ebenen an B-Tree-Traversierung pro Anfrage und die Ersparnis summiert sich bei sehr hoher Anfragefrequenz spürbar.

In einem Magento-Kontext betrifft das am ehesten Lookups auf gut zwischengespeicherte, kleine Referenztabellen mit hoher Wiederholrate, etwa wiederholte Store-Config- oder EAV-Attribut-Metadaten-Zugriffe während eines Seitenaufbaus. Für große, sich ständig ändernde Tabellen mit vielfältigen Zugriffsmustern, wie den Produktkatalog während einer Layered-Navigation-Suche, ist der Effekt dagegen deutlich geringer.

4. Szenarien, in denen der AHI kontraproduktiv wird

Kontraproduktiv wird der AHI vor allem dann, wenn das Working Set größer ist als das, was sinnvoll gehasht werden kann, oder wenn sich Zugriffsmuster ständig ändern, etwa bei vielfältigen Report- und Suchabfragen. In diesem Fall baut InnoDB fortlaufend neue Hash-Einträge auf und verwirft alte wieder, was reinen Verwaltungsaufwand ohne nennenswerten Nutzen erzeugt.

Noch schwerwiegender ist die Situation bei hoher gleichzeitiger Schreiblast: Jede Änderung an einer Zeile, die Teil einer Hash-Struktur ist, muss auch den zugehörigen Hash-Eintrag aktualisieren, was zusätzliche Latch-Zugriffe auf die betroffene AHI-Partition erfordert. Bei stark parallelen Insert- oder Update-lastigen Workloads, etwa während eines Massenimports oder einer Reindexierung, kann diese zusätzliche Synchronisation den Durchsatz messbar senken statt ihn zu erhöhen.

5. AHI-Wirkung messen: Hash-Suchen vs. B-Tree-Fallback

Der InnoDB-Statusreport zeigt im Abschnitt zum Adaptive Hash Index zwei zentrale Zahlen: die Anzahl der Hash-Suchen pro Sekunde und die Anzahl der Suchen, die stattdessen auf den regulären B-Tree zurückgefallen sind. Ein hoher Anteil an Hash-Suchen bei niedrigem B-Tree-Fallback deutet auf eine Workload hin, die tatsächlich vom AHI profitiert.

Ergänzend liefert information_schema.INNODB_METRICS die Zähler adaptive_hash_searches und adaptive_hash_searches_btree als kumulative, abfragbare Werte, die sich über die Zeit beobachten und in ein Monitoring-Dashboard einbinden lassen, statt jedes Mal den vollständigen Statustext parsen zu müssen.

SELECT NAME, COUNT
FROM information_schema.INNODB_METRICS
WHERE NAME IN ('adaptive_hash_searches', 'adaptive_hash_searches_btree');

6. AHI gezielt deaktivieren und den Effekt kontrolliert testen

innodb_adaptive_hash_index lässt sich zur Laufzeit über SET GLOBAL umschalten, ohne dass ein Neustart nötig ist. Das erlaubt einen direkten A/B-Vergleich unter realistischer Last: den AHI für ein definiertes Zeitfenster deaktivieren, Durchsatz und Latenz messen, dann wieder aktivieren und dieselbe Messung wiederholen.

Wichtig ist, diesen Test nicht auf einem leeren oder frisch gestarteten System durchzuführen, da der Buffer Pool und damit auch die Hash-Struktur erst warmlaufen müssen. Ein belastbarer Vergleich setzt voraus, dass beide Messfenster unter vergleichbarer, eingeschwungener Last stattfinden, sonst verzerrt der Warmlauf-Effekt das Ergebnis.

-- AHI zur Laufzeit deaktivieren, kein Neustart noetig
SET GLOBAL innodb_adaptive_hash_index = OFF;

-- Nach dem Messfenster wieder aktivieren
SET GLOBAL innodb_adaptive_hash_index = ON;

7. Persistente Konfiguration und Partitionierung anpassen

Zeigt ein Test, dass der AHI für eine bestimmte Workload dauerhaft mehr schadet als nutzt, sollte die Einstellung auch in der Konfigurationsdatei persistiert werden, damit sie einen Neustart übersteht. Umgekehrt kann bei einer Workload, die klar vom AHI profitiert, aber unter Latch-Contention leidet, eine höhere Anzahl an Partitionen über innodb_adaptive_hash_index_parts helfen, allerdings nur beim Serverstart konfigurierbar, nicht zur Laufzeit.

In der Praxis lohnt sich diese Feinjustierung nur bei Systemen mit sehr hoher, gemessener Contention auf AHI-Partitionen. Für die meisten Magento-Shops reicht die Standardkonfiguration aus acht Partitionen völlig aus, und die wichtigere Entscheidung bleibt, ob der AHI für die konkrete Workload überhaupt angeschaltet bleiben sollte.

8. Invalidierung bei Buffer-Pool-Eviction und Schemaänderungen

Der AHI ist eng an den Lebenszyklus der zugrunde liegenden Buffer-Pool-Seite gekoppelt. Wird eine Seite aus dem Buffer Pool verdrängt, weil der verfügbare Speicher für andere, gerade aktivere Daten benötigt wird, verwirft InnoDB automatisch auch die zugehörigen Hash-Einträge. Ein Hash-Eintrag existiert also nie unabhängig von seiner Quellseite, sondern nur so lange, wie diese Seite tatsächlich im Speicher gehalten wird, was den AHI implizit selbstregulierend macht.

Auch strukturelle Änderungen wirken sich direkt aus: Wird ein Index verändert, neu aufgebaut oder eine Tabelle mit ALTER TABLE umgeschrieben, verwirft InnoDB die betroffenen Hash-Strukturen vollständig und baut sie erst durch neue, wiederholte Zugriffe wieder auf. Nach einem größeren Schema-Deployment, das viele Indizes gleichzeitig verändert, ist der AHI deshalb für eine gewisse Zeit wirkungslos, bis sich stabile Zugriffsmuster erneut eingespielt haben.

9. Praxisempfehlung für Magento-Datenbanken

Für einen typischen Magento-Shop mit gemischter Last aus Storefront-Lesezugriffen, gelegentlichen Bestellschreibvorgängen und periodischen Indexer-Läufen lohnt sich ein bewusster Test statt einer pauschalen Empfehlung. Während normaler Storefront-Last kann der AHI durchaus messbar helfen, während eines vollständigen Reindex-Laufs mit hoher, paralleler Schreiblast überwiegt in vielen gemessenen Fällen der zusätzliche Synchronisationsaufwand.

Ein praktikabler Ansatz ist, den AHI während geplanter, besonders schreiblastiger Wartungsfenster gezielt zu deaktivieren und für den normalen Storefront-Betrieb aktiviert zu lassen, sofern die Messung tatsächlich einen Vorteil zeigt. Ohne eigene Messung auf dem konkreten System bleibt jede pauschale Empfehlung, den AHI grundsätzlich an- oder auszuschalten, reine Spekulation.

10. Zusammenfassung

11. FAQ: Adaptive Hash Index: Das Wichtigste auf einen Blick