Buffer Pool, Redo Log, Flush-Verhalten: InnoDB-Tuning ohne Mythologie

Der InnoDB Buffer Pool: Grundlagen und Dimensionierung

Der InnoDB Buffer Pool ist der wichtigste Speicherbereich in MySQL. Er fungiert als In-Memory-Cache für InnoDB-Datenpages und Indexseiten. Jedes Mal, wenn MySQL eine Zeile liest oder schreibt, sucht es zunächst im Buffer Pool — wird die Seite gefunden (Cache Hit), entfällt der teure Disk-I/O. Ein gut dimensionierter Buffer Pool ist das effektivste InnoDB-Tuning, das es gibt, weil er sämtliche Lese- und Schreiboperationen beschleunigt.

Die viel zitierte Faustregel lautet: innodb_buffer_pool_size auf 70–80% des verfügbaren RAM setzen. Das stimmt als Startpunkt, aber es gibt wichtige Nuancen. Auf einem Server mit 32 GB RAM und mehreren Diensten (PHP-FPM, Redis, Elasticsearch/OpenSearch) ist 70–80% zu hoch — dort würde MySQL dem OS zu wenig Speicher für Dateisystem-Cache und Prozesse lassen. Bei einem dedizierten Datenbankserver mit 64 GB RAM und nur MySQL ist 80% hingegen ein guter Ausgangspunkt. Das InnoDB-Tuning-Ziel: Der Buffer Pool soll groß genug sein, um den Working Set (häufig zugegriffene Pages) vollständig zu halten.

-- Check current buffer pool configuration
SHOW VARIABLES LIKE 'innodb_buffer_pool%';

-- Check buffer pool hit rate (should be > 99% for well-tuned systems)
SELECT
  (1 - (
    SELECT VARIABLE_VALUE FROM performance_schema.global_status
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
  ) / (
    SELECT VARIABLE_VALUE FROM performance_schema.global_status
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
  )) * 100 AS buffer_pool_hit_rate_pct;

-- Show buffer pool memory usage
SELECT
  pool_id,
  pool_size,
  free_buffers,
  database_pages,
  old_database_pages,
  modified_database_pages AS dirty_pages,
  pending_reads,
  pending_writes_lru
FROM information_schema.INNODB_BUFFER_POOL_STATS;

Wenn die Buffer Pool Hit Rate dauerhaft unter 99% liegt, ist der Buffer Pool zu klein. In diesem Fall erhöhe innodb_buffer_pool_size schrittweise und beobachte den Effekt. Ab MySQL 5.7 kann der Buffer Pool online ohne Neustart geändert werden, allerdings dauert die Änderung je nach Pool-Größe einige Minuten, da InnoDB den Speicher neu aufteilt.

Buffer Pool Instances: Warum mehr nicht immer mehr ist

Seit MySQL 5.5 kann der Buffer Pool in mehrere Instanzen aufgeteilt werden — gesteuert durch innodb_buffer_pool_instances. Jede Instanz hat ihren eigenen Mutex, was Contention bei hochparallelemen Workloads reduziert. Die Empfehlung: Eine Instanz pro 1 GB Buffer Pool, maximal 8–16 Instanzen. Ab MySQL 8.0 mit einem Pool unter 1 GB ist immer nur eine Instanz sinnvoll.

-- Recommended InnoDB buffer pool configuration for a 32 GB RAM server
-- (16 GB for MySQL, 16 GB for OS, PHP-FPM, Redis)

-- In /etc/mysql/mysql.conf.d/mysqld.cnf:
-- [mysqld]
-- innodb_buffer_pool_size = 12G
-- innodb_buffer_pool_instances = 8
-- innodb_buffer_pool_chunk_size = 128M

-- Verify the actual allocated size (may differ slightly due to chunk size rounding)
SELECT
  @@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS pool_gb,
  @@innodb_buffer_pool_instances AS instances,
  @@innodb_buffer_pool_chunk_size / 1024 / 1024 AS chunk_mb;

-- Check for buffer pool mutex contention
SELECT EVENT_NAME, COUNT_STAR, SUM_WAIT_SECS
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE '%buf_pool%'
ORDER BY SUM_WAIT_SECS DESC
LIMIT 10;

Redo Log: Kapazität, Rotation und innodb_log_file_size

Das Redo Log ist das Write-Ahead-Log von InnoDB. Jede Änderung wird zunächst ins Redo Log geschrieben (sequenzieller I/O, sehr schnell), bevor sie auf die eigentlichen Datenpages angewendet wird. Bei einem Crash liest InnoDB beim nächsten Start das Redo Log und stellt alle noch nicht in die Datendateien geschriebenen Änderungen wieder her. Das Redo Log ist damit fundamental für die ACID-Eigenschaft Durability.

In MySQL bis 8.0.30 wird das Redo Log durch innodb_log_file_size und innodb_log_files_in_group konfiguriert. Die gesamte Redo Log Kapazität ergibt sich aus dem Produkt beider Werte. In MySQL 8.0.30+ gibt es stattdessen innodb_redo_log_capacity, das den Gesamtspeicher direkt angibt. Für InnoDB-Tuning gilt: Ein zu kleines Redo Log erzwingt häufige Checkpoints, was zu erhöhtem I/O und sogenannten "furious flushing" führt — einem Phänomen, bei dem MySQL alle anderen Operationen für massives Dirty-Page-Flushing pausiert.

-- MySQL 8.0.30+: Check redo log capacity
SHOW VARIABLES LIKE 'innodb_redo_log_capacity';

-- MySQL < 8.0.30: Check redo log file configuration
SHOW VARIABLES LIKE 'innodb_log_file%';

-- Monitor redo log usage (how full is the redo log?)
SELECT
  VARIABLE_NAME,
  VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
  'Innodb_redo_log_capacity_resized',
  'Innodb_os_log_written',
  'Innodb_log_waits'
);

-- Calculate writes per second to estimate needed redo log size
-- Rule of thumb: redo log should hold ~1 hour of writes
SELECT
  VARIABLE_VALUE / 1024 / 1024 AS os_log_written_mb
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_os_log_written';

Als Faustregel für das InnoDB-Tuning: Das Redo Log sollte groß genug sein, um mindestens 30–60 Minuten an Schreibvolumen zu puffern. Für Magento-Shops mit intensiver Bestellaktivität empfehle ich mindestens 4–8 GB Redo Log Kapazität. Zu groß ist ebenfalls problematisch: Ein sehr großes Redo Log verlängert die Crash-Recovery-Zeit nach einem unerwarteten Neustart.

innodb_flush_log_at_trx_commit: 0, 1 oder 2?

Diese Variable ist einer der meistdiskutierten Knöpfe im InnoDB-Tuning — und einer der wenigen, bei dem Datenverlust gegen Performance abgewogen wird. Sie kontrolliert, wann das Redo Log vom OS-Buffer auf die Festplatte geschrieben (geflusht) wird.

Wert 1 (Standard): Bei jedem COMMIT wird das Log sofort auf die Festplatte geflusht. Maximale Sicherheit, kein Datenverlust bei Crash, aber höchste Schreiblast. Für Produktionssysteme mit kritischen Transaktionen (Bestellungen, Zahlungen) ist dies der einzig akzeptable Wert.

Wert 2: Das Log wird bei jedem COMMIT in den OS-Buffer geschrieben, aber nur einmal pro Sekunde auf Festplatte. Bei einem MySQL-Crash gehen keine Transaktionen verloren, wohl aber bei einem OS-Crash oder Stromausfall. Performance-Vorteil: ca. 10–30% mehr Durchsatz. Für Dev- und Staging-Umgebungen geeignet.

Wert 0: Weder COMMIT noch der 1-Sekunden-Flush sind garantiert — alles liegt im Buffer. Maximale Performance, maximales Verlustrisiko. Nur für nicht-kritische Test-Umgebungen.

-- Check current flush setting
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

-- Check how many log writes are happening
SELECT
  VARIABLE_VALUE AS log_writes
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_log_writes';

-- For Magento production: always use value 1
-- For staging (accept up to 1 second data loss):
SET GLOBAL innodb_flush_log_at_trx_commit = 2;

-- Persist in my.cnf:
-- [mysqld]
-- innodb_flush_log_at_trx_commit = 1  -- production
-- innodb_flush_log_at_trx_commit = 2  -- staging/dev

innodb_flush_method: O_DIRECT und warum es wichtig ist

Die innodb_flush_method-Variable kontrolliert, wie InnoDB Daten in die Datendateien schreibt. Der Standard unter Linux ist fsync, was bedeutet: InnoDB schreibt in den OS-Seiten-Cache und bittet dann den OS, die Daten auf Disk zu synchronisieren. Das führt zu doppeltem Buffering: Die Daten befinden sich sowohl im InnoDB Buffer Pool als auch im OS Page Cache — eine Verschwendung von RAM.

Mit O_DIRECT schreibt InnoDB direkt auf das Block-Device, ohne den OS Page Cache zu durchlaufen. Das spart RAM (keine doppelte Buffering) und reduziert I/O-Overhead. Für fast alle Linux-Produktionssysteme mit dediziertem Storage ist O_DIRECT die bessere Wahl. Eine Ausnahme: NFS-Mounts und manche SAN-Systeme verstehen O_DIRECT nicht korrekt — dort bleibt man bei fsync.

-- Check current flush method
SHOW VARIABLES LIKE 'innodb_flush_method';

-- Recommended settings in /etc/mysql/mysql.conf.d/mysqld.cnf
-- [mysqld]
-- innodb_flush_method = O_DIRECT         -- Linux with direct-attached storage
-- innodb_use_native_aio = ON             -- use Linux AIO (usually default)

-- Monitor I/O operations
SELECT
  VARIABLE_NAME,
  VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
  'Innodb_data_reads',
  'Innodb_data_writes',
  'Innodb_data_fsyncs',
  'Innodb_data_pending_reads',
  'Innodb_data_pending_writes'
);

Dirty Pages und innodb_io_capacity

Dirty Pages sind Buffer Pool-Seiten, die im Speicher modifiziert wurden, aber noch nicht auf Disk geschrieben wurden. InnoDB flusht Dirty Pages kontinuierlich im Hintergrund. Die Rate, mit der dieser Background-Flush arbeitet, wird durch innodb_io_capacity und innodb_io_capacity_max gesteuert. Diese Parameter beschreiben, wie viele I/O-Operationen pro Sekunde InnoDB für den Hintergrund-Flush nutzen darf.

Ein zu niedriger innodb_io_capacity-Wert bedeutet, dass Dirty Pages zu langsam geleert werden. Wenn das Redo Log dadurch zu voll wird, muss InnoDB einen Checkpoint erzwingen — das führt zu den berüchtigten "furious flushing"-Spitzen, bei denen MySQL für Sekunden fast einfriert. Für SSDs sollte innodb_io_capacity auf 2000–4000, für NVMe-SSDs auf 8000–20000 gesetzt werden. Für HDDs sind 200–400 typische Werte.

-- Check dirty page percentage
SELECT
  dirty_pages,
  database_pages,
  ROUND(dirty_pages / database_pages * 100, 2) AS dirty_pct
FROM information_schema.INNODB_BUFFER_POOL_STATS;

-- If dirty_pct is consistently > 25%, increase io_capacity
-- Recommended io_capacity settings:
-- HDD:     innodb_io_capacity = 200,   innodb_io_capacity_max = 400
-- SSD:     innodb_io_capacity = 2000,  innodb_io_capacity_max = 4000
-- NVMe:    innodb_io_capacity = 10000, innodb_io_capacity_max = 20000

SHOW VARIABLES LIKE 'innodb_io_capacity%';

-- Check checkpoint age (how close to log capacity are we?)
SELECT
  VARIABLE_VALUE AS checkpoint_age_bytes
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_checkpoint_age';

Monitoring mit INFORMATION_SCHEMA und Performance Schema

Gutes InnoDB-Tuning ist ohne kontinuierliches Monitoring wertlos. MySQL bietet zwei primäre Monitoring-Schnittstellen: INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS für Buffer Pool-Statistiken und das Performance Schema für feingranulare Laufzeitdaten. Beide liefern wichtige Kennzahlen für das InnoDB-Tuning.

-- Comprehensive InnoDB health check query
SELECT
  'Buffer Pool Hit Rate' AS metric,
  CONCAT(ROUND(
    (1 - gs_reads.val / gs_reqs.val) * 100, 2
  ), '%') AS value
FROM
  (SELECT VARIABLE_VALUE AS val FROM performance_schema.global_status
   WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') gs_reads,
  (SELECT VARIABLE_VALUE AS val FROM performance_schema.global_status
   WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') gs_reqs

UNION ALL

SELECT
  'Dirty Pages %',
  CONCAT(ROUND(modified_database_pages / database_pages * 100, 1), '%')
FROM information_schema.INNODB_BUFFER_POOL_STATS

UNION ALL

SELECT
  'Log Waits (should be 0)',
  VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_log_waits';

InnoDB-Tuning konkret für Magento 2

Magento 2 hat spezifische Datenbankcharakteristika: Das EAV-Schema erzeugt viele kleine Tabellen mit häufigen JOINs, die Bestellverarbeitung ist write-intensive, und die Indexer-Operationen führen zu kurzen Bursts mit massivstem Schreibvolumen. Ein auf Magento optimiertes InnoDB-Tuning berücksichtigt diese Muster.

Für einen typischen Magento-2-Server mit 32 GB RAM empfehle ich folgende Konfiguration als Ausgangspunkt. Diese sollte durch Monitoring angepasst werden — blindes Kopieren von Konfigurationen ohne Monitoring ist genau der "Mythologie"-Teil, den dieser Artikel zu vermeiden sucht.

-- Recommended Magento 2 InnoDB configuration (32 GB RAM server)
-- File: /etc/mysql/mysql.conf.d/99-magento-innodb.cnf

-- [mysqld]
-- innodb_buffer_pool_size = 20G
-- innodb_buffer_pool_instances = 8
-- innodb_buffer_pool_chunk_size = 256M

-- innodb_redo_log_capacity = 4G         -- MySQL 8.0.30+
-- innodb_flush_log_at_trx_commit = 1    -- production: no data loss
-- innodb_flush_method = O_DIRECT        -- avoid double buffering on Linux

-- innodb_io_capacity = 2000             -- SSD assumed
-- innodb_io_capacity_max = 4000

-- innodb_file_per_table = ON            -- required for XtraBackup
-- innodb_stats_on_metadata = OFF        -- prevent stats updates on SHOW TABLE STATUS

-- Verify applied settings
SELECT @@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS pool_gb,
       @@innodb_flush_log_at_trx_commit AS flush_trx,
       @@innodb_flush_method AS flush_method,
       @@innodb_io_capacity AS io_cap;

Ein häufiger Mythos im InnoDB-Tuning: "Je mehr Buffer Pool, desto besser." Das stimmt nur bis zu einem Punkt. Wenn der Buffer Pool so groß ist, dass das OS wenig RAM für den eigenen Cache hat, kommt es zu erhöhtem Swapping — und Swap ist hundertfach langsamer als RAM. Lass immer mindestens 2–4 GB RAM für das OS übrig, mehr wenn PHP-FPM und Redis auf demselben Server laufen.

FAQ: InnoDB-Tuning