Change Buffer und Undo Logs: die unsichtbaren InnoDB-Arbeiter erklärt

1. Warum InnoDB überhaupt einen Change Buffer braucht

Ein Primärschlüssel-Index ist bei InnoDB gleichzeitig die Tabelle selbst, weshalb Inserts entlang eines fortlaufenden Primärschlüssels weitgehend sequenziell auf Disk landen. Sekundärindizes dagegen sind eigenständige Strukturen, deren Sortierreihenfolge in der Regel nichts mit der Einfügereihenfolge der Zeilen zu tun hat. Ein Insert in eine Tabelle mit mehreren Sekundärindizes erzeugt damit potenziell mehrere zufällig verteilte Schreibzugriffe, einen pro betroffenem Index.

Liegt die betroffene Sekundärindex-Seite nicht bereits im Buffer Pool, würde ein sofortiger Merge diese Seite erst von der Platte laden müssen, nur um eine einzelne Änderung einzutragen. Der Change Buffer vermeidet genau das: Er puffert die Änderung im Speicher und verschiebt das eigentliche Zusammenführen mit der Indexseite auf einen späteren, günstigeren Zeitpunkt.

2. Wie der Change Buffer Änderungen zusammenführt

Der Merge einer gepufferten Änderung findet statt, sobald die betroffene Seite ohnehin aus einem anderen Grund in den Buffer Pool geladen wird, etwa durch eine Leseanfrage, oder im Rahmen eines Hintergrundprozesses, der periodisch gepufferte Änderungen abarbeitet, sowie spätestens beim geordneten Herunterfahren des Servers. Dadurch werden mehrere gepufferte Änderungen an derselben Seite oft in einem einzigen Merge-Vorgang gebündelt, statt jede Änderung einzeln mit eigenem Seitenzugriff zu verarbeiten.

Gesteuert wird dieses Verhalten über innodb_change_buffering mit den Werten all, inserts, deletes, changes, purges und none, sowie über innodb_change_buffer_max_size, das den maximal für den Change Buffer reservierten Anteil des Buffer Pools in Prozent begrenzt und standardmäßig bei 25 Prozent liegt.

SHOW VARIABLES LIKE 'innodb_change_buffer%';

-- Merge-Aktivitaet im Statusreport beobachten
SHOW ENGINE INNODB STATUS\G
-- Abschnitt "INSERT BUFFER AND ADAPTIVE HASH INDEX" enthaelt
-- die aktuelle Größe und Merge-Zaehler des Change Buffers

3. Wo der Change Buffer an seine Grenzen stößt

Eindeutige Sekundärindizes profitieren kaum vom Change Buffer, weil ein Insert die Eindeutigkeit sofort gegen die tatsächliche Indexseite prüfen muss und diese Seite deshalb ohnehin sofort geladen werden muss. Der Puffereffekt entfaltet sich also vor allem bei nicht eindeutigen Sekundärindizes, wie sie in Magento-Tabellen für Filter- und Sortierspalten häufig vorkommen.

Auch bei einem Working Set, das ohnehin fast vollständig in den Buffer Pool passt, bringt der Change Buffer wenig, weil die betroffenen Seiten meist bereits geladen sind und ein sofortiger Merge genauso günstig wäre wie ein verzögerter. Der eigentliche Nutzen zeigt sich bei Random-I/O-lastigen Workloads mit einem Working Set, das größer als der Buffer Pool ist, etwa bei sehr großen Produktkatalogen mit vielen indizierten Attributspalten.

4. Undo Logs als Grundlage für MVCC-Snapshots

Jede Änderung an einer Zeile erzeugt in InnoDB einen Undo-Log-Eintrag, der die vorherige Version dieser Zeile festhält. Andere Transaktionen, die zu diesem Zeitpunkt bereits einen konsistenten Lese-Snapshot besitzen, können über die Kette dieser Undo-Einträge weiterhin die für sie gültige, ältere Version der Zeile lesen, ohne dass die schreibende Transaktion dafür blockieren müsste. Genau das ist die technische Grundlage für Multi-Version Concurrency Control in InnoDB.

Ohne Undo Logs müsste jede lesende Transaktion entweder auf schreibende Transaktionen warten oder mit potenziell inkonsistenten Zwischenständen arbeiten. Der Preis für diese Nebenläufigkeit ist, dass alte Zeilenversionen so lange aufbewahrt werden müssen, wie irgendeine aktive Transaktion sie theoretisch noch benötigen könnte.

5. Undo Logs als Grundlage für Rollback

Neben MVCC übernehmen Undo Logs eine zweite, ebenso wichtige Aufgabe: Bricht eine Transaktion ab oder wird sie explizit zurückgerollt, liest InnoDB die zugehörigen Undo-Einträge in umgekehrter Reihenfolge und stellt damit den Zustand vor der Transaktion wieder her. Ein Rollback ist also technisch nichts anderes als das kontrollierte Abspielen der Undo-Kette rückwärts.

Das erklärt auch, warum eine sehr große Transaktion, etwa ein massiver Bulk-Update während eines Datenimports, bei einem Abbruch spürbar lange für den Rollback braucht: Jede einzelne Änderung muss über ihren Undo-Eintrag einzeln rückgängig gemacht werden, und diese Arbeit lässt sich nicht überspringen oder parallelisieren.

6. Undo-Tablespaces, Rollback-Segmente und Purge-Threads

Physisch liegen Undo Logs in Rollback-Segmenten, die wiederum in dedizierten Undo-Tablespaces organisiert sind, deren Anzahl über innodb_undo_tablespaces konfigurierbar ist. Sobald ein Undo-Eintrag von keiner aktiven Transaktion mehr benötigt wird, weil kein Lese-Snapshot mehr existiert, der die ältere Zeilenversion sehen könnte, entfernen die Purge-Threads diesen Eintrag asynchron im Hintergrund und geben den belegten Platz wieder frei.

Die Anzahl dieser Hintergrund-Threads lässt sich über innodb_purge_threads einstellen. Bei einer Workload mit hoher Schreibfrequenz und entsprechend hoher Undo-Erzeugungsrate kann eine zu niedrige Anzahl an Purge-Threads dazu führen, dass die Aufräumarbeit dauerhaft hinter der Erzeugung neuer Undo-Einträge zurückbleibt.

SHOW VARIABLES LIKE 'innodb_undo_tablespaces';
SHOW VARIABLES LIKE 'innodb_purge_threads';

-- Aktuelle History-List-Laenge als direkter Indikator
-- für unaufgeraeumte Undo-Eintraege
SELECT NAME, COUNT FROM information_schema.INNODB_METRICS
WHERE NAME = 'trx_rseg_history_len';

7. Wie lang laufende Transaktionen Undo Logs anschwellen lassen

Solange irgendeine Transaktion einen alten Lese-Snapshot offen hält, dürfen die zugehörigen Undo-Einträge nicht gepurged werden, selbst wenn die zugrunde liegenden Zeilen längst mehrfach überschrieben wurden. Eine einzelne, vergessen offen gebliebene Transaktion, etwa eine hängende Admin-Session oder ein Backup-Prozess mit langer Snapshot-Dauer, kann deshalb dazu führen, dass sich Undo-Einträge über Stunden oder Tage hinweg anhäufen, während der reguläre Betrieb völlig normal weiterläuft.

In einem Magento-Kontext ist das besonders bei lange laufenden Batch-Prozessen relevant, etwa einem umfangreichen Reindex oder einem Datenimport, der eine einzelne, sehr lange offene Transaktion verwendet. Während dieser Prozess läuft, wächst die History List kontinuierlich, und erst nach dessen Abschluss kann der Purge-Thread die aufgestaute Arbeit nachholen.

8. Undo-Wachstum überwachen, bevor es zum Problem wird

Die zentrale Kennzahl für unaufgeräumte Undo-Einträge ist die History List Length, abrufbar über die Metrik trx_rseg_history_len oder im Abschnitt TRANSACTIONS von SHOW ENGINE INNODB STATUS. Ein stetig wachsender Wert über einen längeren Zeitraum ist ein zuverlässiges Frühwarnsignal für eine vergessene, offen gebliebene Transaktion, lange bevor Speicherplatz auf der Platte tatsächlich knapp wird.

Ergänzend lohnt sich ein regelmäßiger Blick auf information_schema.INNODB_TRX, um Transaktionen mit ungewöhnlich hoher trx_started-Dauer gezielt zu identifizieren und im Zweifel abzubrechen, bevor sie unnötig viele Undo-Einträge blockieren und damit indirekt den Plattenplatzverbrauch der Undo-Tablespaces in die Höhe treiben.

SELECT trx_id, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started ASC
LIMIT 10;

9. Praxisrelevanz für Magento-Datenbanken

Für Magento-Shops mit hoher Schreiblast auf Sekundärindizes, etwa während großer Preis- oder Lagerbestandsimporte, reduziert ein sinnvoll konfigurierter Change Buffer die Anzahl teurer Random-I/O-Zugriffe spürbar. Gleichzeitig lohnt sich vor jedem größeren, lange laufenden Batch-Job ein Blick auf offene Transaktionen und die History List Length, damit ein einzelner vergessener Prozess nicht das Undo-Log-Wachstum für den gesamten Zeitraum des Imports unnötig in die Höhe treibt.

Beide Mechanismen zeigen ihre Wirkung meist erst im Zusammenspiel: Ein gut funktionierender Change Buffer entlastet die Schreibpfade, während sauber gemanagte, kurze Transaktionen dafür sorgen, dass die dabei erzeugten Undo-Einträge zügig wieder gepurged werden, statt sich unbemerkt über Tage hinweg aufzustauen.

10. Zusammenfassung

11. FAQ: Change Buffer und Undo Logs: Das Wichtigste auf einen Blick