Das sys-Schema: praktische Views für die tägliche MySQL-Diagnose

1. Was das sys-Schema ist und wofür es gebaut wurde

Das sys-Schema ist seit MySQL 5.7 fest als eigene Datenbank installiert und besteht ausschließlich aus Views, Stored Procedures und Funktionen, die auf der performance_schema und teilweise auf dem information_schema aufsetzen. Es speichert selbst keine eigenen Daten, sondern übersetzt vorhandene Rohdaten in eine Form, die sich ohne tiefes Wissen über interne Tabellenlayouts direkt lesen und interpretieren lässt.

Der eigentliche Wert liegt darin, dass wiederkehrende Diagnosefragen, etwa nach den teuersten Query-Mustern oder ungenutzten Indizes, nicht mehr jedes Mal neu als komplexe JOIN-Abfrage gegen mehrere Performance-Schema-Tabellen formuliert werden müssen. Diese Logik ist im sys-Schema bereits als fertige View hinterlegt und lässt sich mit einem einfachen SELECT abrufen.

2. Formatierte Views vs. x$-Rohdaten-Pendants

Fast jede sys-View existiert in zwei Varianten: die formatierte Standardversion, etwa sys.statement_analysis, und ein Pendant mit vorangestelltem x$, etwa sys.x$statement_analysis. Die formatierte Version nutzt Hilfsfunktionen wie format_time() und format_bytes(), um Picosekunden und Byte-Zahlen in gut lesbare Einheiten wie Millisekunden oder Megabyte umzurechnen.

Für die manuelle Durchsicht am Bildschirm ist die formatierte Variante fast immer die richtige Wahl. Sobald die Werte aber weiterverarbeitet werden sollen, etwa für ein Monitoring-Dashboard oder eine automatisierte Schwellenwert-Prüfung, greift man besser zur x$-Variante, weil dort die numerischen Rohwerte ohne Formatierungsverluste vorliegen und sich zuverlässig sortieren und vergleichen lassen.

-- Lesbar formatiert für die manuelle Durchsicht
SELECT * FROM sys.statement_analysis LIMIT 10;

-- Rohe Zahlenwerte für Monitoring/Automatisierung
SELECT * FROM sys.x$statement_analysis
ORDER BY total_latency DESC LIMIT 10;

3. sys.statement_analysis: der schnelle Einstieg in teure Queries

Die View sys.statement_analysis baut direkt auf events_statements_summary_by_digest auf, ergänzt aber lesbare Spalten wie avg_latency, full_scan als einfaches Ja/Nein-Flag und eine gekürzte, gut lesbare Query-Vorschau. Damit entfällt die manuelle Umrechnung von Picosekunden und das Nachschlagen, ob SUM_NO_INDEX_USED für eine bestimmte Zeile größer als null war.

In der Praxis ersetzt eine einzige Abfrage gegen diese View oft den ersten Blick, den man sonst über mehrere Performance-Schema-Rohabfragen zusammensetzen müsste. Für eine tiefere Analyse, etwa den exakten Digest-Text ohne Kürzung, führt trotzdem weiterhin ein Weg zurück zur Rohtabelle, aber als Einstiegspunkt für die tägliche Routine reicht die View meistens aus.

SELECT query, exec_count, avg_latency, full_scan, rows_sent_avg
FROM sys.statement_analysis
WHERE db = 'magento'
ORDER BY avg_latency DESC
LIMIT 15;

4. sys.statements_with_full_table_scans: fehlende Indizes aufspüren

Diese View filtert gezielt jene Digests heraus, bei denen ein signifikanter Anteil der Ausführungen ohne Index ausgekommen ist, also einen vollständigen Table-Scan verursacht hat. Statt selbst das Verhältnis aus SUM_NO_INDEX_USED und COUNT_STAR zu berechnen, liefert die View bereits eine vorsortierte Liste mit den Spalten no_index_used_count, no_good_index_used_count und dem geschätzten Anteil betroffener Ausführungen.

Für eine Magento-Datenbank ist das der direkteste Weg, um Layered-Navigation-Filter oder individuelle Report-Abfragen zu finden, die versehentlich ohne passenden Index laufen. Ein Blick in diese View vor jedem größeren Release, der neue Abfragen einführt, deckt solche Regressionen oft auf, bevor sie unter echter Last überhaupt auffallen.

Wichtig ist dabei, nicht jeden gefundenen Table-Scan reflexartig als Fehler zu behandeln. Bei sehr kleinen Referenztabellen mit wenigen Dutzend Zeilen ist ein vollständiger Scan oft genauso schnell wie eine Indexsuche und ein zusätzlicher Index würde nur unnötige Schreiblast erzeugen. Die View liefert deshalb den Ausgangspunkt für eine gezielte Prüfung, nicht automatisch die fertige Handlungsanweisung, und sollte immer zusammen mit der betroffenen Tabellengröße und der tatsächlichen Ausführungshäufigkeit bewertet werden.

SELECT query, exec_count, no_index_used_count,
       no_index_used_pct, last_seen
FROM sys.statements_with_full_table_scans
ORDER BY no_index_used_pct DESC, exec_count DESC
LIMIT 15;

5. sys.schema_unused_indexes und sys.schema_redundant_indexes: Indexpflege

sys.schema_unused_indexes listet Indizes auf, die seit dem letzten Zurücksetzen der zugrunde liegenden Tabellen-I/O-Statistiken kein einziges Mal für eine Leseoperation genutzt wurden. Wichtig ist dabei der Zeitbezug: Diese Statistik läuft seit dem letzten Neustart oder einem expliziten TRUNCATE der Basisdaten, weshalb ein junger Server oder ein gerade erst gestarteter Batch-Job fälschlich viele Indizes als ungenutzt ausweisen kann, obwohl sie später im Monatslauf gebraucht werden.

sys.schema_redundant_indexes arbeitet anders und rein strukturell: Sie erkennt Indizes, deren Spaltenreihenfolge ein reines Präfix eines anderen, umfassenderen Index ist, unabhängig von tatsächlicher Nutzung. Ein Index auf (store_id) neben einem bestehenden Index auf (store_id, product_id) ist ein typisches Beispiel, das diese View zuverlässig aufdeckt und das jede Schreiboperation unnötig verlangsamt.

SELECT object_schema, object_name, index_name
FROM sys.schema_unused_indexes
WHERE object_schema = 'magento';

SELECT table_name, redundant_index_name,
       dominant_index_name, redundant_index_columns
FROM sys.schema_redundant_indexes
WHERE table_schema = 'magento';

6. I/O- und Tabellenstatistiken mit sys.schema_table_statistics

Diese View aggregiert I/O-Wartezeiten und Zugriffszähler pro Tabelle und liefert damit eine Rangliste, welche Tabellen tatsächlich die meiste Zeit für Lese- und Schreibzugriffe binden. Spalten wie io_read_latency und io_write_latency machen sichtbar, ob eine Tabelle wie sales_order_grid vor allem durch Lesezugriffe aus dem Grid-Frontend oder durch Schreibzugriffe während der Indexierung belastet wird.

Gerade bei EAV-lastigen Magento-Tabellen wie catalog_product_entity_varchar oder catalog_product_entity_int hilft diese View, den tatsächlichen I/O-Anteil am Gesamtsystem einzuordnen, bevor man mit Partitionierung oder Denormalisierung eingreift, deren Aufwand sich nur lohnt, wenn die betroffene Tabelle wirklich einen relevanten Anteil der Gesamtlast trägt.

7. sys.processlist und sys.session: laufende Abfragen live diagnostizieren

sys.processlist und die kompaktere sys.session liefern gegenüber dem klassischen SHOW PROCESSLIST deutlich mehr Kontext pro laufender Verbindung: den aktuellen Statement-Text, die bisherige Laufzeit in lesbarer Form, den letzten bekannten Wait-Event und, sofern eine Transaktion aktiv ist, deren bisherige Dauer. Das ersetzt in der Praxis oft mehrere manuelle Abfragen gegen information_schema.PROCESSLIST und die zugehörigen Wait-Tabellen.

Besonders wertvoll ist das bei akuten Lastspitzen: Ein einziger Blick in sys.session zeigt sofort, welche Verbindung am längsten offen ist, worauf sie aktuell wartet, und ob eine lang laufende Transaktion möglicherweise andere Sessions blockiert, statt separat SHOW ENGINE INNODB STATUS nach Lock-Informationen durchsuchen zu müssen.

8. Helferprozeduren: ps_setup_enable_thread und Co. steuern die Performance Schema

Das sys-Schema bringt auch Stored Procedures mit, die die Konfigurationstabellen der Performance Schema kapseln, etwa sys.ps_setup_enable_thread(), um Instrumentierung gezielt für einen einzelnen laufenden Thread einzuschalten, oder sys.ps_setup_reset_to_default(), um alle Instrument- und Consumer-Einstellungen wieder auf den Auslieferungszustand zurückzusetzen.

Diese Prozeduren sind besonders praktisch, wenn man während einer akuten Diagnose eine einzelne verdächtige Verbindung tiefer instrumentieren möchte, ohne die globalen Einstellungen für den gesamten Server zu verändern und danach mühsam wieder rückgängig machen zu müssen.

-- Instrumentierung für eine bestimmte Thread-ID gezielt einschalten
CALL sys.ps_setup_enable_thread(42);

-- Alle Performance-Schema-Einstellungen wieder auf Standard setzen
CALL sys.ps_setup_reset_to_default(TRUE);

9. Praxis-Workflow: ein Magento-Datenbank-Check in fünf Views

Ein sinnvoller Routine-Check kombiniert mehrere Views nacheinander: zuerst sys.statement_analysis für einen Überblick über die teuersten Muster, dann sys.statements_with_full_table_scans, um akute Indexlücken zu finden, gefolgt von sys.schema_unused_indexes und sys.schema_redundant_indexes, um die Indexlandschaft regelmäßig zu bereinigen, und abschließend sys.schema_table_statistics, um zu prüfen, ob die identifizierten Hotspots auch tatsächlich zu den I/O-lastigsten Tabellen passen.

Wichtig ist, diesen Check nicht einmalig, sondern regelmäßig, etwa wöchentlich, durchzuführen und dabei einen bewussten Startzeitpunkt für die zugrunde liegenden Statistiken zu setzen. Nur so lassen sich Aussagen über ungenutzte Indizes treffen, die auch monatlich laufende Batch-Jobs und saisonale Abfragemuster korrekt berücksichtigen, statt auf Basis weniger Betriebsstunden voreilige Löschentscheidungen zu treffen.

In der Praxis lohnt es sich, die Ergebnisse dieses Workflows in einem einfachen Änderungsprotokoll festzuhalten, etwa welcher Index wann als ungenutzt markiert und tatsächlich entfernt wurde. So entsteht über mehrere Durchläufe hinweg eine nachvollziehbare Historie, die auch bei einem späteren Performance-Problem hilft, mögliche Zusammenhänge zwischen einer Indexänderung und einem veränderten Query-Verhalten schneller einzugrenzen, statt bei jeder neuen Auffälligkeit wieder bei null anzufangen.

10. Zusammenfassung

11. FAQ: sys-Schema für die Diagnose: Das Wichtigste auf einen Blick