DB-Tuning-Konzepte — Sperren, Cache, Index, Checkpoint, Speicher

Überblick — was diese Seite leistet

Service ▸ Datenbank verbindet sich direkt mit den registrierten DB-Collectors (access=database) und stellt Live-Kennzahlen zu Last und Struktur dar. Statt nur Zahlen aufzulisten, hängt die Seite an jede Schlüsselkennzahl (Sperrwartezeit, Cache-Trefferquote, Indexnutzung, Checkpoint, Dirty Pages) ein eigens gestaltetes didaktisches Hover-Popover, damit Sie genau dort, wo Sie den Wert sehen, lernen: „Warum ist dieser Wert schlecht / wie behebe ich ihn?"

Dieser Artikel fasst die Konzepte dieser Popover zusammen. Das vollständige Lexikon liegt als einzige Quelle in den Live-Popovern selbst (resources/lang/{ko,en}/dbmon.php, Schlüssel edu); im Ernstfall fahren Sie also über die Kennzahl, um deren kontextspezifisches Detail zu lesen.

Der Bildschirm ist der Monitor (was ist gerade schlecht), das Popover ist die Ursachenschulung (warum / wie), und der tatsächliche Konfigurations-Snapshot liegt in den Instanzparametern ([param] · nginx / my.cnf / sysctl). Lesen Sie alle drei zusammen, um von der Diagnose → zum Nachweis → zur Maßnahme zu kommen.

Wann verwenden

Eine DB-Karte zeigt Degraded oder Down und Sie wollen den Grund verstehen (Verbindungssättigung, hohe Nebenläufigkeit, Query-Rückstau, steigende Slow-Query-Rate).
Der Wert für Cache-Trefferquote oder Indexnutzung ist niedrig und Sie suchen die Grundursache sowie die Stellhebel zur Verbesserung (Buffer-Pool-Größe, Indizes ergänzen, abdeckende Indizes).
Die Zeilensperren-Wartezeit schnellt hoch und Sie müssen eingrenzen, welche Query bzw. welches Muster die Konkurrenz erzeugt.
Das Detail Speicherverwaltung zeigt, wie die Kurven von Dirty Pages / Checkpoint in den Risikobereich kriechen, und Sie wollen eine Tuning-Richtung (größerer Buffer Pool / größeres Redo-Log).
Einarbeitung von DBA / DevOps — die InnoDB-Interna (Sperren, Buffer Pool, Redo) an echten Daten lernen.

Aufbau

Wo die didaktischen Popover ansetzen:

Ort	Kennzahl	Was das Popover vermittelt
Kartenfuß rechts · Badge `Cache`	Cache-Trefferquote (Buffer Pool)	Die Herleitungsformel + 4 Wege, sie zu erhöhen
Kartenfuß rechts · Badge `Index`	Indexnutzung	Die Herleitungsformel + 5 Wege, Indextreffer zu erhöhen
Nebenläufigkeitslast · `lock`	Mittlere Zeilensperren-Wartezeit (ms)	Die 7 Ursachen steigender Sperrwartezeit (nach Häufigkeit)
Kopfzeile / Speicherdetail · `Checkpoint … % of redo`	Checkpoint-Alter	Was Redo-Log-Druck bedeutet und wie Anstieg und Rückgang zu lesen sind
Detail (einklappbar) · Speicherverwaltung	Dirty Pages · Checkpoint-Zeitreihe	Überlagerung der LWM-/MAX-Schwellen + Anmerkungen zu Flush-Episoden

Der Kartenkörper selbst fasst zwei Lastachsen zusammen:

Nebenläufigkeitslast — Verbindungssättigung (Ringanzeige) · Running (threads_running) · Backlog (waiting) · lock.
Durchsatz — Sparkline Total QPS · Slow rate (slow_qps) · Slow (total).

Die Schema-Analyse ganz unten (einklappbar) zeigt Gesamtgröße, die größten Schemata, die größten Tabellen und die Engine-Verteilung (unabhängig von den Tuning-Konzepten, aus information_schema).

Verwendung

Suchen Sie auf einer DB-Karte die problematische Kennzahl (rotes/gelbes Badge oder ein hoher Wert).
Fahren Sie mit der Maus über Beschriftung oder Badge dieser Kennzahl, um das Kontext-Popover zu öffnen — Ursache, Formel und Vorgehen erscheinen.
Klappen Sie Details auf, um die Zeitreihe der Speicherverwaltung mit den Kurven für Dirty Pages / Checkpoint und den Schwellenlinien zu prüfen.
Wenden Sie den vom Popover benannten Konfigurationshebel (z. B. innodb_buffer_pool_size, einen Index ergänzen) auf der Ziel-DB an oder gleichen Sie ihn mit dem aktuellen Wert in den Instanzparametern ab.

Nachfolgend eine Kurzreferenz zu jedem Konzept (maßgeblich ist das Live-Popover).

Zeilensperren-Wartezeit — 7 Ursachen

Durchschnittliche Zeit, die InnoDB auf den Erwerb einer Zeilensperre gewartet hat (ms). Höher = mehr Konkurrenz. Nach Häufigkeit:

#	Ursache	Kern
1	Transaktionen halten Sperren zu lange	Nach `BEGIN … UPDATE …` stellt ein später Commit (externe API, Bedenkzeit des Benutzers, eine langsame Query) alle Transaktionen in eine Warteschlange, die auf diese Zeile warten. Der häufigste Fall.
2	Konkurrenz um heiße Zeilen	Viele Transaktionen aktualisieren DIESELBE Zeile (Zähler, Bestand, Summen-/Rollup-Zeile, Sequenz) → sie serialisieren sich, die Wartezeiten häufen sich.
3	UPDATE/DELETE ohne Index	Kann das WHERE keinen Index nutzen, sperrt InnoDB weit mehr Zeilen bzw. Bereiche als nötig → blockiert dadurch auch unbeteiligte Transaktionen. In der Praxis die verborgene Nummer 1.
4	Gap-/Next-Key-Sperren	Unter REPEATABLE READ sperren Bereichssperren auch die Lücken zwischen Zeilen und blockieren damit INSERTs. Muster: Bereichs-UPDATE + gleichzeitiges INSERT.
5	Große Batch-DML	Ein einzelnes UPDATE/DELETE über Zehntausende Zeilen hält viele Sperren über lange Zeit.
6	Langsamer Sperrhalter	Ist die sperrhaltende Query bei E/A oder CPU langsam, hält sie die Sperren länger — steigende Slow-Query-Rate und steigende Sperrwartezeiten treten gemeinsam auf.
7	Fremdschlüsselprüfungen	Das Aktualisieren einer Tabelle mit FK-Verweis nimmt geteilte Sperren auf den Elternzeilen und verlängert die Wartezeit zusätzlich.

Auf dieser Seite ist Ursache 3 (UPDATE ohne Index) der verborgene Haupttreiber von Spitzen bei der Sperrwartezeit. Lesen Sie den Abschnitt TRANSACTIONS von SHOW ENGINE INNODB STATUS zusammen mit dem Wert für Indexnutzung.

Cache-Trefferquote (InnoDB Buffer Pool)

Hoch, wenn die meisten Lesezugriffe aus dem Speicher (dem Buffer Pool) statt von der Festplatte bedient werden.

hit ratio = 1 − Δinnodb_buffer_pool_reads / Δinnodb_buffer_pool_read_requests

Bewertung	Schwelle
Ausgezeichnet	≥ 99,5 %
Gut	≥ 98 %
Ausreichend	≥ 90 %

So erhöhen Sie sie (Kurzfassung): innodb_buffer_pool_size vergrößern (Faustregel: 50–75 % des RAM) · Full Scans reduzieren, damit der Buffer Pool nicht verschmutzt wird · das Working Set verkleinern (ungenutzte Spalten vermeiden, große BLOBs auslagern, archivieren) · mit innodb_buffer_pool_instances die Konkurrenz senken und den Buffer Pool beim Neustart dumpen bzw. neu laden.

Indexnutzung

Verhältnis der Lesezugriffe, die Zeilen über einen Index gefunden haben, zu jenen, die bis zur nächsten Zeile gescannt haben.

index usage = Δhandler_read_key / (Δhandler_read_key + Δhandler_read_rnd_next)

Bewertung	Schwelle
Ausgezeichnet	≥ 90 %
Gut	≥ 70 %
Ausreichend	≥ 40 %

So erhöhen Sie sie (Kurzfassung): mit EXPLAIN Full Scans (type=ALL) und Filesorts finden und dann die Spalten aus WHERE/JOIN/ORDER BY indizieren · zusammengesetzte Indizes mit der selektivsten Spalte zuerst aufbauen (Regel des linken Präfixes) · Indizes nicht durch Funktionen oder Casts auf der Spalte aushebeln (WHERE DATE(col)=… → einen Bereich verwenden) · abdeckende Indizes einsetzen · doppelte oder ungenutzte Indizes entfernen.

Checkpoint-Alter (Redo-Log-Druck)

Wie weit das Redo-Log über den letzten Checkpoint hinaus vorgerückt ist (Bytes) = die noch nicht in die Datendateien geschriebenen Änderungen und zugleich die Arbeit, die InnoDB nach einem Absturz wiederholen müsste. Das % of redo in der Kopfzeile ist der Abstand zur Klippe des Flush-Sturms.

Zustand	% of redo	Bedeutung
Sicher	< 50 %	Änderungen werden rechtzeitig geschrieben
Beobachten	50 – 75 %	Schreibvorgänge übertreffen das Flushen; der erzwungene Flush rückt näher
Risiko	≥ 75 %	Nahe an der asynchronen/synchronen Flush-Schwelle — erzwungenes Flushen birgt Latenz- und E/A-Stürme

So lesen Sie es: steigend = Schreiben > Flushen (das Redo füllt sich) · fallend = das Flushen hat aufgeholt (gesund) · Sägezahn = Ansammeln ↔ Flushen, ein normaler Rhythmus. Nahe 75 % sollten Sie einen größeren Buffer Pool bzw. ein größeres innodb_log_file_size erwägen.

Dirty-Page-Flush (Speicherverwaltung)

Seiten im Buffer Pool, die geändert, aber noch nicht auf die Festplatte geschrieben wurden. Der angemessene Wert sind die eigenen Flush-Schwellen des Servers (innodb_max_dirty_pages_pct = MAX, innodb_max_dirty_pages_pct_lwm = LWM). Fachleute sagen: Dirty Pages sollten unterhalb des LWM schwingen.

Zustand	Bedingung	Bedeutung
Gesund (good)	Schwingt unterhalb der Schwelle (gesunder Sägezahn)	Die DB leidet während der Flushes nicht
Vorsicht (warn)	Oberhalb der unteren Wassermarke (Vor-Flush-Zone)	Schreibdruck und Checkpoint-Rückstand beobachten
Gefährdet (crit)	MAX-Schwelle erreicht	Aggressives Flushen → Latenzspitzen / E/A-Stürme (Buffer Pool oder Redo-Log vergrößern)

Die Zeitreihe der Speicherverwaltung legt die LWM-/MAX-Schwellenlinien über die Dirty Pages und markiert Verdrängungs- und Flush-Episoden. Die Kennzahlen rechts zeigen zusätzlich Pending writes und Row-lock avg.

Datenquellen

Die realen Zähler und Speicherorte, auf die sich die Lehrkonzepte beziehen:

Konzept	Quellzähler / -status	Hinweis
Sperrwartezeit	`SHOW GLOBAL STATUS` · `information_schema.processlist`	Berechnet den Rückstau; benötigt das PROCESS-Recht
Cache-Trefferquote	`Innodb_buffer_pool_reads` / `_read_requests`	Delta zwischen zwei Messungen
Indexnutzung	`Handler_read_key` / `Handler_read_rnd_next`	Delta zwischen zwei Messungen
Checkpoint · Dirty	InnoDB-Status (Checkpoint-Alter · Dirty Pages · ausstehend)	Zeitreihe der Speicherverwaltung
QPS · Slow	`Questions` · `Slow_queries` (kumuliert)	Delta → daraus abgeleitet QPS / Slow-Rate

Die Zeitreihe wird in infr_db_metric gespeichert (Migration 000681). App\Services\Database\DbCollectorService verbindet sich direkt mit der registrierten DB, liest SHOW GLOBAL STATUS + @@max_connections + processlist und berechnet die abgeleiteten Kennzahlen als Delta zur vorigen Messung.
Die Erfassung läuft über den Befehl infr:collect-db (standardmäßig minütlich geplant, beendet sich nach einem Durchlauf). Beim Betreten der Seite nimmt ensureFresh() sofort eine einmalige Messung, falls die letzte veraltet ist.
Abfrageintervall der Seite: 30 s. Um Verbindungslecks zu vermeiden, nutzt der Collector DB::purge in finally sowie ein PDO-Timeout.

Schwellen für den Kartenstatus (degraded) (Standardwerte aus OpsPolicy::dbThresholds() — anpassbar in der Betriebsrichtlinie):

Bedingung	Standardschwelle	Grundbezeichnung
Verbindungssättigung `conn_pct` ≥	80 %	Verbindungssättigung
`threads_running` ≥	40	hohe Nebenläufigkeit
`waiting` (Rückstau) ≥	5	Query-Rückstau
`slow_qps` ≥	1/s	Slow-Query-Rate

Zugriff

Lesen (Seite und Popover ansehen): demo (Level 1) und höher. Die Popover sind reine Lehrinhalte — keine zusätzliche Zugriffsprüfung, kein Audit.
DB-Statusschwellen anpassen: verwaltet in der Betriebsrichtlinie — Administrator (Level 5/10 · infr.level:N).
DB-Collectors anlegen/bearbeiten: Collectors, Administrator (Level 5/10). Passwörter werden verschlüsselt (Crypt) im Collector gespeichert, oder Sie verwenden über cred_ref = env:PREFIX einen Zugangsdatensatz aus .env erneut (niemals fest im Code).

Fehlerbehebung und FAQ

F. Backlog zeigt „own connections only". Um serverweit zurückgestaute Queries zu zählen, benötigt das DB-Konto das PROCESS-Recht. Ohne dieses werden nur Ihre eigenen Verbindungen gezählt. Siehe sql/infr_db_monitor_grant.sql (ein eigenes Monitoring-Konto wird empfohlen).

F. Die Cache-/Index-Werte sind leer (N/A). Abgeleitete Kennzahlen werden als Delta zwischen zwei Messungen berechnet. Ist der Collector gerade erst gestartet oder wurden die Zähler zurückgesetzt (ein Neustart), liefert die erste Messung keinen Wert — warten Sie den nächsten Zyklus ab.

F. Eine Karte zeigt stale (keine aktuelle Messung). Im Economy-Modus wird die Erfassung übersprungen, wenn kein Konsolenbenutzer anwesend ist. Beim Betreten nimmt ensureFresh(45s) sofort eine Messung; ist der Scheduler jedoch im Rückstand, kann die Karte kurzzeitig auf pending zurückfallen. Für dauerhafte Erfassung setzen Sie den DB-Erfassungsmodus in der Betriebsrichtlinie auf always.

F. Wie übersetze oder bearbeite ich den Popover-Text? Das Lehr-Lexikon liegt an einer einzigen Stelle: im Schlüssel edu von resources/lang/{ko,en}/dbmon.php (einzige Quelle). Andere Sprachen fallen auf en zurück.

F. Wie grenze ich eine Sperrwartezeit-Ursache tatsächlich ein? Prüfen Sie auf der Ziel-DB SHOW ENGINE INNODB STATUS (TRANSACTIONS · LATEST DETECTED DEADLOCK) sowie information_schema.INNODB_TRX / data_locks und gleichen Sie das Ergebnis mit der obigen Tabelle der 7 Ursachen ab.