Datenbanken

MySQL Performance Schema sinnvoll einsetzen für bessere Performance

Für bessere MySQL Performance nutze ich das Performance Schema, um Laufzeitdaten zu Abfragen, Waits, Locks, Speicher und I/O direkt mit SQL auszuwerten. So erkenne ich Ursachen hinter langsamen Statements schneller und leite gezielte Schritte für Tuning und Monitoring ab [2][3][15].

Zentrale Punkte

Die folgenden Schwerpunkte helfen mir, das Performance Schema effektiv einzusetzen.

Aktivierung und schlanke Konfiguration mit passenden Instrumenten und Verbrauchern
Statement-Digests nutzen, um teure Muster und Hotspots zu erkennen
Wait-Events, Locks und I/O gemeinsam auswerten, um echte Engstellen zu finden
Sys-Schema als Abkürzung für schnelle, handlungsfähige Einblicke
Iterativer Workflow: messen, isolieren, ändern, erneut messen

Performance Schema aktivieren und sinnvoll konfigurieren

Ich prüfe zuerst, ob performance_schema aktiv ist, denn aktuelle MySQL-Versionen liefern es in der Regel eingeschaltet aus [1][12]. Fehlt es, setze ich im [mysqld]-Block der my.cnf die Variable performance_schema=ON und starte den Server neu. Danach richte ich Instrumente und Verbraucher gezielt ein, statt alles dauerhaft aufzudrehen. Ich konzentriere mich auf statement/%, wait/% und relevante I/O-Pfade, damit ich aussagekräftige Daten ohne überflüssigen Overhead sammele [6]. Für eine neue Messreihe leere ich betroffene History-Tabellen und beginne mit einer sauberen Basis.

Schnelle Erfolge mit dem Sys-Schema

Für den schnellen Überblick greife ich oft zum sys-Schema, weil es die Rohdaten des Performance Schemas sinnvoll verdichtet [13]. So finde ich in Minuten die Abfragen, die den größten Anteil an der Laufzeit haben. Ich starte mit Top-Statements, prüfe File-I/O-Sichten und schaue auf Wait-Summaries für Threads. Sobald ich einen Hotspot erkannt habe, springe ich in die Rohtabellen zurück und verfeinere die Analyse. Wer Abfragepläne nachzieht, kann mit passenden Optimizer-Tipps häufig schon in kurzer Zeit spürbare Gewinne erzielen.

Die richtigen Instrumente und Verbraucher wählen

Ich beginne breit, halte die Beobachtung aber steuerbar: Zuerst aktiviere ich die wichtigsten Instrumente für Statements, Waits und I/O, danach schalte ich alles aus, was kein Signal bringt [6]. Verbraucher wie Event-History und Summary-Tabellen müssen die Fragen stützen, die ich beantworten will. Geht es etwa um Latenzspitzen, schaue ich in events_waits_summary_global_by_event_name und vergleiche das mit events_statements_summary_by_digest. Treten I/O-Wartezeiten auf, prüfe ich file_summary_by_event_name und table_io_waits_summary_by_table. Diese gezielte Auswahl hält den Overhead klein und liefert trotzdem belastbare Daten.

Statement-Digests: Muster erkennen, Last senken

Mit Statement-Digests sehe ich, welche Muster dauerhaft teuer sind, auch wenn einzelne Abfragen variierende Literale tragen [17]. Ich sortiere nach Gesamtzeit, Ausführungsanzahl und durchschnittlicher Latenz, um Prioritäten festzulegen. Dabei greife ich ergänzend auf das Slow-Query-Log auswerten zurück, um seltene Ausreißer nicht zu übersehen. Wenn Digests Spitzen zeigen, prüfe ich Indizes, JOIN-Strategien und Filterreihenfolgen mit EXPLAIN. Im Anschluss bestätige ich die Wirkung mit erneuten Messungen im Performance Schema, damit Optimierungen messbar bleiben.

Warteereignisse, Locks und I/O deuten

Wenn Anfragen hängen, befrage ich die Wait- und Lock-Tabellen, um die eigentliche Ursache zu finden [3]. Laufen viele Threads auf dieselben Tabellen, deuten table_lock-Waits auf Konkurrenz hin. Zeigen File-I/O-Events hohe Latenzen, prüfe ich Storage und Cashing sowie Abfragemuster mit großen Scans. Sehe ich InnoDB-Row-Locks, analysiere ich Hot-Records, Transaktionsdauer und Indexabdeckung. Erst wenn diese Puzzleteile zusammenpassen, fasse ich Server-Parameter, Schema oder Code an.

Speicherbeobachtung: Memory und Buffer-Pool

Speicherprobleme bremse ich, indem ich Memory-Tabellen und InnoDB-Puffer-Auslastung zusammenlese. Steigt der Speicherbedarf einzelner Komponenten, justiere ich Limits und prüfe, ob Caches die falschen Daten binden. Reicht der InnoDB-Cache nicht, erhöhe ich den Anteil oder verbessere ich die Abfrage-Lokalität. Wer tiefer einsteigt, kann über Buffer-Pool optimieren deutliche Latenzgewinne realisieren. Ich bestätige die Wirkung mit den Summary-Tabellen und verfolge, ob LRU-Hits und I/O-Wartezeiten in die richtige Richtung laufen.

Iterativer Diagnose-Workflow für den Alltag

Ich arbeite immer in klaren Schleifen, damit ich keine Zeit verliere und Veränderungen messbar bleiben [3]. Zuerst reproduziere ich das Problem unter kontrollierter Last. Danach sammle ich Messwerte in wenigen, gezielten Tabellen und isoliere die auffälligsten Kandidaten. Anschließend ändere ich das, was den größten Nutzen verspricht: Index, Query, Parameter oder Code. Zum Schluss messe ich erneut und dokumentiere kurze Vorher/Nachher-Tabellen, damit das Team die Wirkung sofort sieht.

Abfragebeispiele: Von Rohdaten zu Entscheidungen

Für typische Fragen habe ich mir kompakte SQL-Snippets notiert, die ich direkt im Alltag einsetze. Die Tabelle zeigt Beispiele, die ich häufig nutze, und wofür sie stehen. Ich passe Filter wie LIMIT oder ORDER BY je nach Anwendungsfall an. Wichtig bleibt: erst Hypothese, dann gezielte Auswertung und eine klare Entscheidung. So halte ich die Analyse fokussiert und vermeide überflüssige Last.

Performance-Schema-Tabelle(n)	Ziel	Wichtige Spalten	Beispiel-Query
`events_statements_summary_by_digest`	Teure Muster finden	`digest_text`, `count_star`, `sum_timer_wait`	`SELECT digest_text, count_star, sum_timer_wait/1e12 AS sec_total FROM performance_schema.events_statements_summary_by_digest ORDER BY sec_total DESC LIMIT 10;`
`events_waits_summary_global_by_event_name`	Wait-Hotspots	`event_name`, `sum_timer_wait`	`SELECT event_name, sum_timer_wait/1e12 AS sec_total FROM performance_schema.events_waits_summary_global_by_event_name ORDER BY sec_total DESC LIMIT 10;`
`table_io_waits_summary_by_table`	Tabellen-I/O prüfen	`object_schema`, `object_name`, `read_timer_wait`	`SELECT object_schema, object_name, (read_timer_wait+write_timer_wait)/1e12 AS sec_total FROM performance_schema.table_io_waits_summary_by_table ORDER BY sec_total DESC LIMIT 10;`
`memory_summary_global_by_event_name`	Speicherfresser finden	`event_name`, `current_alloc`	`SELECT event_name, current_alloc/1024/1024 AS mb FROM performance_schema.memory_summary_global_by_event_name ORDER BY mb DESC LIMIT 10;`

Produktionsbetrieb: Overhead minimieren, Wirkung maximieren

Im Live-Betrieb schalte ich Instrumente nicht blind an, sondern wähle nur das, was meine Frage beantwortet [6]. Events mit hoher Frequenz behandle ich vorsichtig und halte History-Fenster kurz. Für längere Beobachtungen ziehe ich komprimierte Summaries vor und speichere Momentaufnahmen extern. Ich achte auf den Eintrag in performance_schema_setup_consumers, damit ich Sammlungen steuere statt sie laufen zu lassen. Dieser Fokus hält die Analyse effizient und schützt den Server.

Feinjustierung: Setup-Instrumente und -Consumer in der Praxis

Damit ich schnell zu belastbaren Ergebnissen komme, konfiguriere ich Instrumente und Verbraucher gezielt. Besonders wichtig sind statement/%, wait/%, wait/io/% und – bei Bedarf – ausgewählte memory/%-Pfade. Ich aktiviere zunächst nur das Nötigste und erweitere dann, wenn mir konkrete Fragen noch unbeantwortet bleiben. Die Timer im Performance Schema messen in Pikoskeunden; für Sekunden teile ich Latenzspalten durch 1e12.

Typischer Startpunkt zur Laufzeit:

-- Schlüssel-Instrumente anschalten
UPDATE performance_schema.setup_instruments
  SET ENABLED='YES', TIMED='YES'
  WHERE NAME LIKE 'statement/%'
     OR NAME LIKE 'wait/io/%'
     OR NAME LIKE 'wait/lock/%';

-- Wichtige Consumer wählen
UPDATE performance_schema.setup_consumers
  SET ENABLED='YES'
  WHERE NAME IN ('global_instrumentation',
                 'thread_instrumentation',
                 'statements_digest',
                 'events_statements_current',
                 'events_statements_history',
                 'events_waits_current',
                 'events_waits_history');

-- Für eine frische Messreihe Summaries leeren
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
TRUNCATE TABLE performance_schema.events_waits_summary_global_by_event_name;
TRUNCATE TABLE performance_schema.table_io_waits_summary_by_table;

Wenn ich Speicher-Analysen brauche, aktiviere ich selektiv memory/%-Instrumente. Das kostet mehr Overhead, lohnt sich aber bei Leaks oder starkem Druck auf den Allocator.

Dimensionen: Benutzer, Host und Schema verstehen

Leistungsspitzen sind oft nicht global, sondern auf bestimmte Benutzer, Hosts oder ein Schema begrenzt. Das Performance Schema liefert dazu Summaries je Account und Host. Zusätzlich enthalte ich im Digest die Spalte schema_name, um Hotspots pro Datenbank einzugrenzen.

Beispiele, die ich häufig nutze:

Top-Schemata nach Gesamtlaufzeit: SELECT schema_name, SUM(sum_timer_wait)/1e12 AS sec_total FROM performance_schema.events_statements_summary_by_digest GROUP BY schema_name ORDER BY sec_total DESC LIMIT 10;
Benutzer/Host, die die meiste Latenz verursachen (über Accounts): SELECT user, host, SUM(sum_timer_wait)/1e12 AS sec_total FROM performance_schema.events_statements_summary_by_account_by_event_name GROUP BY user, host ORDER BY sec_total DESC LIMIT 10;
Threads mit höchster Wartezeit: SELECT thread_id, SUM(sum_timer_wait)/1e12 AS sec_total FROM performance_schema.events_waits_summary_by_thread_by_event_name GROUP BY thread_id ORDER BY sec_total DESC LIMIT 10;

Mit diesen Sichten trenne ich gezielt Trafficsegmente und kann throttlen, cachen oder Query-Varianten je Mandant einführen.

Lange Transaktionen und Metadata-Locks sichtbar machen

Lange laufende oder inaktive Transaktionen blockieren Checkpoints, Purge und konkurrierende DML. Ich prüfe deshalb regelmäßig die Transaktionssicht und MDL-Waits:

Aktive Transaktionen: SELECT thread_id, timer_wait/1e12 AS sec_running, state FROM performance_schema.events_transactions_current ORDER BY sec_running DESC LIMIT 10;
Metadata-Locks (DDL/DML-Konkurrenz) erkennen: SELECT event_name, SUM(sum_timer_wait)/1e12 AS sec_total FROM performance_schema.events_waits_summary_global_by_event_name WHERE event_name LIKE 'wait/lock/metadata/sql/mdl%' GROUP BY event_name ORDER BY sec_total DESC;

Wenn MDL dominiert, plane ich DDL-Fenster neu, minimiere Lock-Haltezeiten im Code (kürzere Transaktionen) und prüfe, ob überflüssige AUTOCOMMIT=0-Blöcke Sessions unnötig lange offen halten.

Replikation, Backups und Nebenwirkungen im Blick

Replikations- und Backup-Prozesse tauchen in Waits und I/O-Sichten auf. Verzögerungen lassen sich über Worker-Status und Dateiwaits eingrenzen. Ich schaue auf Applier-Worker, SQL-Thread und File-I/O-Events:

Applier-Worker mit hoher Latenz: SELECT worker_id, THREAD_ID, APPLYING_TRANSACTION, APPLYING_STATE FROM performance_schema.replication_applier_status_by_worker;
File-I/O-Hotspots während Backups: SELECT event_name, (sum_timer_read+sum_timer_write)/1e12 AS sec_total FROM performance_schema.file_summary_by_event_name ORDER BY sec_total DESC LIMIT 10;

Sehe ich hier Engpässe, entkopple ich I/O-Phasen (z. B. Windowing, I/O-Scheduler, Backup-Throttling) oder erhöhe parallele Applier-Worker, sofern die Workload skaliert.

Zeitfenster, Snapshots und Reset-Strategien

Messungen brauchen klare Zeitfenster. Für „vorher/nachher“ arbeite ich mit gezielten Resets und Snapshots:

Summaries zurücksetzen, um frische Intervalle zu erhalten: TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
Momentaufnahme extern sichern: CREATE TABLE IF NOT EXISTS perf_snapshot_digest AS SELECT NOW() AS captured_at, * FROM performance_schema.events_statements_summary_by_digest;
Kurze Historyfenster halten (Consumer), lange Trends extern sammeln.

So kann ich Optimierungen über Deployments, Parameterwechsel oder Schemaänderungen hinweg sicher vergleichen und dokumentieren.

Overhead und Speicherbedarf steuern

Ein häufiges Vorurteil ist, dass das Performance Schema „zu teuer“ sei. In der Praxis halte ich den Overhead durch drei Maßnahmen klein: nur relevante Instrumente aktivieren, stark frequentierte History-Consumer kurz halten und die Speicherparameter passend wählen. Bei hoher Digest-Varianz erhöhe ich gezielt performance_schema_digests_size sowie – wenn nötig – performance_schema_max_sql_text_length, damit Identitäten stabil bleiben. Werden Memory-Instrumente benötigt, begrenze ich sie auf problematische Subsysteme.

Typische Stellschrauben in my.cnf:

[mysqld]
performance_schema=ON
performance-schema-instrument='statement/%=ON'
performance-schema-instrument='wait/io/%=ON'
performance-schema-instrument='wait/lock/%=ON'
performance-schema-consumer-events-statements-history=ON
performance-schema-consumer-events-waits-history=ON
# Optional, wenn viele Muster:
performance_schema_digests_size=10000
performance_schema_max_sql_text_length=4096

Ich messe bei jeder Änderung gegen, ob CPU, Latenz und Speichernutzung stabil bleiben. Sobald die Diagnose abgeschlossen ist, fahre ich die Konfiguration wieder auf das „Betriebsminimum“ zurück.

Häufige Muster und schnelle Abhilfen

Hohe Summe in events_statements_summary_by_digest, viele Scans: Prüfe Indizes, Filterreihenfolge und Sargability; bestätige mit EXPLAIN und wiederhole die Messung (Digest-Zeit muss sichtbar sinken).
Dominante table_io_waits auf wenigen Tabellen: I/O-Lokalisierung verbessern (Clustered Index-Zugriffe, Covering Indizes), Datenmenge pro Statement reduzieren, ggf. Batching statt Full-Table-Work.
Wartezeiten auf wait/lock/innodb/%: Hot-Records identifizieren, Schreibkonflikte durch kleinere Transaktionen, passende Indizes oder Queueing entschärfen.
Viele wait/lock/metadata/sql/mdl: DDL-Fenster planen, ONLINE-fähige Operationen bevorzugen, Reader/Writer durch kürzere Transaktionen entkoppeln.
Speicheranstieg in memory_summary_global_by_event_name: Limits nachschärfen, Query-Caches gezielt begrenzen, Problemkomponenten mit memory/% detailliert aufschlüsseln.
„Spiky“ Latenz bei ansonsten unauffälligen Mittelwerten: Sys-Sichten mit Perzentilen einsetzen und bei Bedarf Lastspitzen separat messen (engeres Fenster, kurze History, fokussierte Instrumente).

Korrelieren: Von Thread zu Statement und Wait

Um Ursachen schnell zu korrelieren, verknüpfe ich performance_schema.threads mit den Current-/History-Tabellen für Statements und Waits. So sehe ich, was ein betroffener Thread zuletzt tat und worauf er wartet. Ein kompakter Ablauf:

Betroffenen PROCESSLIST_ID bzw. THREAD_ID aus performance_schema.threads holen.
Letztes Statement via events_statements_history ermitteln (nach THREAD_ID und Zeit sortieren).
Parallele Waits aus events_waits_history prüfen, um Lock- oder I/O-Wartegründe zu sehen.

Dieses „Drilldown & Join“-Muster ist mein Standard, wenn einzelne Sessions oder Web-Requests aus dem Takt geraten.

Quality Gates und Continuous Performance

Damit Optimierungen nicht verpuffen, etabliere ich schlanke Quality Gates: definierte Queries aus dem Performance Schema laufen vor und nach jedem Release. Ich sichere Snapshots weg, vergleiche Kennzahlen (Top-Digests, Top-Waits, I/O pro Tabelle) und dokumentiere Abweichungen. In CI/CD füge ich repräsentative Lastprofile und Grenzwerte für 95. Perzentile hinzu. Fällt eine Metrik aus dem Rahmen, gibt es einen klaren Rückkanal: Hypothese prüfen, Instrumente fokussieren, Fix deployen, erneut messen.

Fehlerquellen vermeiden

Zu viele Instrumente auf Dauer: Diagnose ist temporär; im Regelbetrieb nur Minimalset aktiv lassen.
Gemischte Messzeiträume: Summaries vor neuen Tests leeren, sonst verwässern alte Daten die Aussagekraft.
Falsche Zeiteinheit: Timer sind in Pikoskeunden; konsequent durch 1e12 teilen.
Digest-Flut: Variierende Literale können Muster sprengen; SQL normalisieren und performance_schema_max_sql_text_length prüfen.
History zu lang: Hohe Eventfrequenzen + lange History erzeugen Druck; Historyfenster kurz halten, Snapshots extern.

Praxis-Checkliste

Frage definieren, Hypothese festhalten.
Passende Instrumente/Consumer aktivieren, Overhead klein halten.
Summaries leeren, kurzes Messfenster wählen.
Top-Digests, Waits, I/O prüfen; Hotspots bestätigen.
Index/Query/Code/Parameter gezielt anpassen.
Erneut messen, Snapshots sichern, Entscheidung dokumentieren.
Konfiguration auf Betriebsminimum zurückfahren.

Kurz zusammengefasst: Mein Vorgehen in der Praxis

Ich aktiviere das Performance Schema zielgerichtet, starte breit, und reduziere dann auf die nützlichsten Instrumente [1][2][12]. Für einen schnellen Überblick ziehe ich das Sys-Schema heran und steige bei Bedarf in die Rohdaten hinab [13]. Hotspots adressiere ich zuerst bei Digests und Wait-Events, bevor ich an Parametern drehe [3][15][17]. Danach bestätige ich jede Änderung mit neuen Messungen, damit Fortschritte sichtbar und reproduzierbar bleiben. So sichere ich dauerhaft verlässliche Antwortzeiten und spare unnötige Arbeit.