MySQL Performance Schema für tiefe Query-Analyse nutzen

1. Wie die Performance Schema MySQL instrumentiert

Die performance_schema-Datenbank wird zur Laufzeit von Messpunkten befüllt, die direkt im Server-Code eingebaut sind, nicht von einem extern angehängten Profiler. Jede dieser Messstellen heißt Instrument und deckt eine klar abgegrenzte Aktivität ab: ein Statement, ein Warten auf einen Mutex, einen Dateizugriff oder eine Speicherzuweisung. Instrumente sind hierarchisch benannt, etwa statement/sql/select oder wait/synch/mutex/innodb/buf_pool_mutex, sodass sich ganze Gruppen über Wildcards gezielt ein- oder ausschalten lassen.

Getrennt von den Instrumenten steuern Consumer, welche der gemessenen Rohdaten überhaupt in eine Tabelle geschrieben werden. Ein aktiviertes Instrument ohne aktivierten Consumer erzeugt zwar ein Ereignis, dieses wird aber nirgendwo gespeichert und verursacht kaum Kosten. Diese Zweiteilung erlaubt es, Messung und Speicherung unabhängig voneinander fein zu dosieren, statt pauschal alles mitzuschreiben oder alles abzuschalten.

2. Instrumente, Konsumenten und Akteure gezielt konfigurieren

Die Tabellen setup_instruments und setup_consumers steuern beide Ebenen über einfache Updates. In MySQL 8.0 sind Statement-Instrumente und deren Digest-Zusammenfassung standardmäßig aktiv, während viele Wait- und Stage-Instrumente bewusst deaktiviert bleiben, um die Grundlast niedrig zu halten. Wer tiefer diagnostizieren will, aktiviert gezielt die passenden Gruppen für die Dauer der Untersuchung und schaltet sie danach wieder ab.

Zusätzlich lässt sich über setup_actors festlegen, für welche Nutzer und Hosts überhaupt instrumentiert wird. In einem Magento-Setup mit mehreren Datenbankbenutzern, etwa einem Anwendungsuser und einem separaten Reporting-User, reduziert eine gezielte Filterung die Menge der erzeugten Ereignisse spürbar, ohne dass die eigentlich interessanten Verbindungen aus dem Blick geraten.

-- Statement-Digest bleibt aktiv (Standard), Wait-Instrumente
-- nur für die Dauer einer Diagnose-Session einschalten
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'wait/synch/mutex/innodb/%';

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE 'events_waits_%';

-- Nur den Anwendungsuser der Storefront instrumentieren
UPDATE performance_schema.setup_actors
SET ENABLED = 'YES', HISTORY = 'YES'
WHERE HOST = '%' AND USER = 'magento_app';

3. Das Digest-Modell hinter events_statements_summary_by_digest

Der Digest-Mechanismus normalisiert jedes ausgeführte Statement, indem er Literale durch Platzhalter ersetzt und Whitespace vereinheitlicht. Zwei Abfragen, die sich nur in der konkreten Produkt-ID oder dem Store-Code unterscheiden, landen dadurch unter demselben DIGEST-Hash und derselben DIGEST_TEXT. Genau das braucht man bei einer ORM-lastigen Anwendung wie Magento, die dieselbe logische Abfrage in unzähligen Parametervarianten ausführt, ohne dass jede einzelne davon als separater Eintrag erscheint.

Die Tabelle events_statements_summary_by_digest aggregiert pro Digest unter anderem COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT, SUM_ROWS_EXAMINED, SUM_ROWS_SENT, SUM_NO_INDEX_USED und SUM_SELECT_FULL_JOIN. Aus diesen Spalten lässt sich ableiten, ob ein Query-Muster teuer ist, weil es oft läuft, weil eine einzelne Ausführung lange dauert, oder weil es unnötig viele Zeilen durchsucht, um am Ende nur wenige zurückzugeben.

4. Teure Query-Muster mit einer gezielten Abfrage identifizieren

Für die tägliche Diagnose reicht eine einzige Abfrage, die nach der aufsummierten Ausführungszeit sortiert. Die TIMER-Spalten liegen in Picosekunden vor und müssen für lesbare Werte umgerechnet werden. Wichtig ist, nicht nur nach der reinen Summe zu sortieren, sondern auch COUNT_STAR im Blick zu behalten: ein Muster mit moderater Einzeldauer, das aber hunderttausendfach pro Stunde läuft, verursacht in Summe oft mehr Serverlast als eine einzelne, seltene Report-Abfrage.

Ein zweiter, oft aufschlussreicherer Blick gilt dem Verhältnis von SUM_ROWS_EXAMINED zu SUM_ROWS_SENT. Weicht dieses Verhältnis stark voneinander ab und ist zusätzlich SUM_NO_INDEX_USED größer als null, deutet das fast immer auf einen fehlenden oder falsch entworfenen Index hin, den man gezielt mit EXPLAIN gegen genau diesen Digest-Text nachvollziehen kann.

SELECT
  DIGEST_TEXT,
  COUNT_STAR AS exec_count,
  ROUND(SUM_TIMER_WAIT / 1000000000000, 2) AS total_seconds,
  ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms,
  SUM_ROWS_EXAMINED,
  SUM_ROWS_SENT,
  SUM_NO_INDEX_USED
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

5. Wait-Events: Contention auf Mutex- und I/O-Ebene sichtbar machen

Während Statement-Digests zeigen, welche Abfrage teuer ist, zeigen Wait-Events, warum sie wartet. Instrumente wie wait/synch/mutex/innodb/buf_pool_mutex oder wait/io/file/innodb/innodb_data_file messen interne Wartezeiten auf Locks, Mutexe und Dateizugriffe, aggregiert in events_waits_summary_global_by_event_name. Erst diese Ebene erklärt, ob eine langsame Abfrage tatsächlich an einer fehlenden Optimierung liegt oder an Contention auf einer gemeinsam genutzten internen Struktur, an der viele Threads gleichzeitig hängen.

Wait-Instrumente sind deutlich zahlreicher und feingranularer als Statement-Instrumente, weshalb sie im Regelfall deaktiviert bleiben. Sie gezielt für ein begrenztes Diagnosefenster einzuschalten und danach wieder abzuschalten, liefert die nötige Tiefe, ohne dass die Instrumentierung selbst zu einem dauerhaften Kostenfaktor auf einem stark ausgelasteten Produktivsystem wird.

6. Stage-Events: den Lebenszyklus eines Statements nachvollziehen

Stage-Events übernehmen die Rolle des inzwischen entfernten SHOW PROFILE und zeigen, in welcher Phase ein Statement seine Zeit verbringt, etwa starting, checking permissions, Sending data oder closing tables. Die Tabelle events_stages_history_long hält diese Phasen pro Thread vor und macht sichtbar, ob die gemessene Laufzeit tatsächlich in der Serverausführung liegt oder überwiegend im Versand großer Ergebnismengen an den Client.

Für Magento ist das besonders bei Export- und Report-Abfragen relevant, die große Datenmengen zurückgeben. Zeigt die Stage-Analyse, dass der Großteil der Zeit in Sending data verbracht wird, hilft eine schnellere Netzwerkverbindung oder ein schlankerer Result-Set mehr als eine weitere Index-Optimierung, die an der eigentlichen Ursache vorbeigeht.

7. Overhead-Abwägung: was volle Instrumentierung im Produktivbetrieb kostet

Die reine Statement-Digest-Instrumentierung ist in MySQL 8.0 standardmäßig aktiv und dank eines weitgehend sperrenfreien Designs mit einem einstelligen Prozentbereich an zusätzlicher CPU-Last verbunden, was für praktisch jeden Magento-Shop vertretbar ist. Anders sieht es aus, sobald zusätzlich events_statements_history_long sowie feingranulare Wait- und Stage-Instrumente vollständig aktiviert werden: Bei sehr hoher Query-Rate summiert sich der Overhead pro Ereignis spürbar auf und kann bei ohnehin CPU-gebundenen Systemen messbar ins Gewicht fallen.

Die pragmatische Empfehlung lautet deshalb, Digest-Aggregation dauerhaft laufen zu lassen, aber Wait- und Stage-Instrumentierung nur für begrenzte Diagnosefenster einzuschalten. Wer regelmäßig tief instrumentieren muss, sollte die zusätzliche Last vorher auf einem Staging-System mit realistischer Last messen, statt sich auf pauschale Prozentangaben aus der Dokumentation zu verlassen, da der tatsächliche Effekt stark von Hardware und Query-Mix abhängt.

8. Sizing, Persistenz und der richtige Umgang mit einem Digest-Reset

Die Digest-Tabelle ist über performance_schema_digest_size in ihrer Größe begrenzt. Ist sie voll, werden neue, bisher unbekannte Digests unter einem generischen Overflow-Eintrag zusammengefasst, wodurch genau die Granularität verloren geht, die man für eine saubere Analyse braucht. Auf einem Shop mit vielen unterschiedlichen Query-Formen, etwa durch dynamische Layered-Navigation-Filter, lohnt sich ein bewusst höher gesetzter Wert statt der automatischen Server-Standardgröße.

Alle Performance-Schema-Daten sind rein speicherresident und überstehen keinen Neustart. Für einen sauberen Vorher-Nachher-Vergleich, etwa vor und nach einem Deployment oder einem Lasttest, empfiehlt sich ein expliziter TRUNCATE TABLE auf die relevanten Summary-Tabellen unmittelbar vor dem Testlauf, damit alte Messwerte die neue Auswertung nicht verwässern.

-- Digest-Statistiken vor einem gezielten Lasttest zuruecksetzen
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;

-- Größe der Digest-Tabelle dauerhaft über die Konfigurationsdatei
-- anheben (wirkt erst nach einem Neustart):
-- performance_schema_digest_size = 20000

9. Praxisbeispiel: teuerste Kategorie- und Produktabfragen in Magento finden

In der Praxis filtert man den Digest-Report zusätzlich auf Tabellennamen, die typische Magento-Hotspots betreffen, etwa catalog_product_entity, catalog_category_product oder sales_order_grid. So lassen sich während eines Lastspitzen-Fensters, etwa an einem Aktionstag, gezielt jene Query-Muster herausfiltern, die für den größten Anteil der aufsummierten Datenbankzeit verantwortlich sind, statt sich durch tausende irrelevante Einzeleinträge zu arbeiten.

Kombiniert man diese Filterung mit dem Verhältnis von untersuchten zu zurückgegebenen Zeilen, entsteht eine klare Priorisierung: Muster mit hohem SUM_NO_INDEX_USED-Wert und schlechtem Zeilenverhältnis sind fast immer echte Indexlücken und lassen sich schnell beheben, während Muster mit gutem Verhältnis, aber hoher Ausführungshäufigkeit eher Kandidaten für Caching oder eine Reduktion der Aufrufhäufigkeit im Anwendungscode sind.

SELECT DIGEST_TEXT, COUNT_STAR, SUM_NO_INDEX_USED,
       ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%catalog_product_entity%'
   OR DIGEST_TEXT LIKE '%catalog_category_product%'
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 15;

10. Zusammenfassung

11. FAQ: Performance Schema für Query-Analyse: Das Wichtigste auf einen Blick