MySQL-Leistungsüberwachung: Verwendung von SHOW STATUS und SHOW PROCESSLIST

Wenn eine MySQL-gestützte Anwendung langsamer wird, sind SHOW STATUS und SHOW PROCESSLIST die schnellsten integrierten Überprüfungen, die Sie durchführen können, bevor Sie ein Dashboard öffnen. Sie erklären nicht jedes Problem von selbst, beantworten aber zwei praktische Fragen: Was hat der Server getan und was läuft gerade?

Verständnis der Echtzeit-Systemgesundheit mit SHOW STATUS

Der Befehl SHOW STATUS, der oft synonym mit SHOW GLOBAL STATUS oder SHOW SESSION STATUS verwendet wird, liefert eine Fülle von Informationen über die Serveraktivität seit dem letzten Neustart oder seit Beginn der aktuellen Sitzung. Diese Statusvariablen fungieren als Zähler und verfolgen alles von Verbindungsversuchen über Cache-Effizienz bis hin zu Sperrwartezeiten.

Globaler vs. Sitzungsstatus

Bei der Ausführung dieses Befehls ist es entscheidend, den Gültigkeitsbereich zu verstehen:

SHOW GLOBAL STATUS: Zeigt Zähler an, die seit dem Start der MySQL-Serverinstanz akkumuliert wurden. Dies bietet einen Überblick über die allgemeine Servergesundheit und langfristige Trends.
SHOW SESSION STATUS: Zeigt Zähler an, die nur für die aktuell verwendete Verbindung (Sitzung) spezifisch sind. Dies ist nützlich, um die Leistungsauswirkungen bestimmter Transaktionen zu isolieren.

Wichtige Leistungsindikatoren (KPIs) aus SHOW GLOBAL STATUS

Obwohl SHOW GLOBAL STATUS Hunderte von Variablen zurückgibt, sind einige für die anfängliche Leistungsbeurteilung kritisch. Normalerweise möchten Sie die Ausgabe mit grep filtern oder eine WHERE-Klausel verwenden, um nach Relevanz zu filtern.

1. Verbindungs- und Thread-Überwachung

Diese Variablen helfen Ihnen, die Verbindungslast zu verstehen:

Variablenname	Beschreibung
`Threads_connected`	Die Anzahl der derzeit offenen Verbindungen (Clients).
`Threads_running`	Die Anzahl der aktiven Threads, die derzeit Abfragen ausführen (sollte im Allgemeinen niedrig sein).
`Max_used_connections`	Die höchste Anzahl gleichzeitiger Verbindungen seit dem Serverstart. Nützlich für die Dimensionierung von `max_connections`.

Beispiel: Überprüfen aktiver Verbindungen:

SHOW GLOBAL STATUS LIKE 'Threads_%';

2. Abfrage-Caching und Effizienz

Wenn Sie den Legacy-Abfrage-Cache (verfügbar in älteren MySQL-Versionen, in neueren veraltet/entfernt) verwenden, sind diese Metriken wesentlich:

Qcache_hits: Anzahl der Abfragen, die aus dem Cache bedient wurden.
Qcache_lowmem_prunes: Anzahl der Abfragen, die dazu führten, dass der Cache aufgrund von niedrigem Speicher ältere Einträge entfernte.

3. InnoDB-Engine-Metriken (Am wichtigsten für modernes MySQL)

Überwachen Sie bei modernen Bereitstellungen mit der InnoDB-Speicher-Engine die Aktivität des Puffer-Pools:

Innodb_buffer_pool_read_requests: Gesamtzahl der Leseanforderungen.
Innodb_buffer_pool_reads: Anzahl der physischen Lesevorgänge von der Festplatte (ein hohes Verhältnis von physischen Lesevorgängen zu Anforderungen weist auf die Notwendigkeit eines größeren Puffer-Pools hin).

Praktischer Tipp: Um die Effizienz des Puffer-Pools schnell zu bewerten, berechnen Sie die Trefferquote: (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests.

4. Temporäre Tabellen und Sortierungen

Diese zeigen an, wie viel interne Verarbeitung MySQL durchführt:

Created_tmp_tables: Anzahl der erstellten temporären Tabellen im Speicher.
Created_tmp_disk_tables: Anzahl der temporären Tabellen, die auf die Festplatte geschrieben werden mussten (langsam).

Wenn Created_tmp_disk_tables hoch ist, müssen Sie möglicherweise tmp_table_size oder max_heap_table_size erhöhen.

Diagnose der aktiven Arbeitslast mit SHOW PROCESSLIST

Während SHOW STATUS Ihnen sagt, was passiert ist, sagt Ihnen SHOW PROCESSLIST, was gerade passiert. Es zeigt Informationen über die derzeit im Server ausgeführten Threads an, sodass Sie langlaufende oder blockierte Abfragen identifizieren können.

Die Struktur der Prozessliste

Der Befehl gibt mehrere Spalten aus, die jeweils Kontext zu einer aktiven Verbindung liefern:

Spalte	Beschreibung
Id	Die eindeutige Verbindungs-ID (wird zum Beenden des Prozesses verwendet).
User	Das verbundene Benutzerkonto.
Host	Der Host, von dem die Verbindung stammt.
db	Die Datenbank, die derzeit vom Thread verwendet wird.
Command	Der Typ des ausgeführten Befehls (z. B. Query, Sleep, Connect).
Time	Die Anzahl der Sekunden, die der Thread in seinem aktuellen Zustand verbracht hat.
State	Die spezifische Aktion, die der Thread ausführt (z. B. Sending data, Copying to tmp table).
Info	Die tatsächliche ausgeführte SQL-Anweisung (oder abgeschnitten, wenn lang).

Filtern und Interpretieren der Ausgabe

Bei großen Produktionssystemen kann die vollständige Prozessliste überwältigend sein. Es ist üblich, das Schlüsselwort FULL zu verwenden, um sicherzustellen, dass Sie den gesamten Abfragetext sehen, und dann nach den Spalten Time oder State zu filtern.

1. Anzeigen des vollständigen Befehlstextes

Verwenden Sie immer FULL, wenn Sie langsame Abfragen vermuten, da die Standardausgabe das Feld Info oft abschneidet:

SHOW FULL PROCESSLIST;

2. Identifizieren blockierender oder langsamer Abfragen

Überwachen Sie die Spalten Time und Command:

Hoher Time-Wert: Jede Abfrage, die über einen längeren Zeitraum läuft (z. B. über 10 Sekunden, abhängig von Ihrer SLA), muss sofort untersucht werden. Überprüfen Sie die entsprechende Spalte Info, um das SQL zu sehen.
Command = 'Sleep': Diese Verbindungen sind im Leerlauf, verbrauchen aber dennoch Ressourcen. Wenn sie sich übermäßig ansammeln, sollten Sie die Variable wait_timeout anpassen.
Command = 'Query': Dies sind aktiv ausgeführte Anweisungen. Achten Sie besonders auf deren State.

3. Identifizieren von Sperrproblemen

Wenn Abfragen auf Ressourcen warten, zeigt die Spalte State dies oft an:

Waiting for table metadata lock
Waiting for table lock
Waiting for lock

Wenn Sie zahlreiche Threads in einem Wartezustand sehen, deutet dies auf Konflikte hin, die normalerweise durch eine langlaufende Transaktion verursacht werden, die Sperren hält, die andere benötigen.

Aktion: Beenden eines Prozesses

Wenn Sie eine außer Kontrolle geratene Abfrage identifizieren, die die Leistung stark beeinträchtigt, können Sie sie mit dem Befehl KILL gefolgt von der Prozess-ID beenden:

KILL 12345; -- Ersetzen Sie 12345 durch die tatsächliche Id aus der Prozessliste

Warnung: Verwenden Sie KILL mit Vorsicht. Das Beenden einer aktiven Transaktion kann die Datenbank in einen inkonsistenten Zustand versetzen, wenn die Transaktion mitten in einer komplexen Schreiboperation war. Versuchen Sie nach Möglichkeit immer zuerst, die Abfrage zu identifizieren und zu optimieren.

Kombinieren von Status- und Prozessinformationen zur Fehlerbehebung

Eine effektive MySQL-Überwachung beinhaltet oft die Korrelation zwischen diesen beiden Befehlen:

Erste Überprüfung: Führen Sie SHOW FULL PROCESSLIST aus. Notieren Sie alle Abfragen mit hoher Laufzeit oder übermäßige Verbindungen.
Kontextprüfung: Überprüfen Sie die Verbindungsanzahl mit SHOW GLOBAL STATUS LIKE 'Threads_connected'. Haben Sie eine Flut von Verbindungen oder nur eine schlechte Abfrage?
Tiefergehende Analyse: Wenn eine bestimmte Abfrage langsam ist, analysieren Sie deren Auswirkungen auf Ressourcenzähler, indem Sie Innodb_buffer_pool_reads oder die Erstellungsrate temporärer Tabellen überprüfen, während die Abfrage läuft (erfordert einen Basislinienvergleich).

Durch regelmäßiges Überprüfen dieser dynamischen Ausgaben gehen Sie über Vermutungen hinaus und wenden gezielte Lösungen an, um die MySQL-Stabilität und -Geschwindigkeit zu verbessern.

Eine realistische Triage-Routine

Ein guter erster Durchlauf dauert weniger als eine Minute. Beginnen Sie mit der Prozessliste:

SHOW FULL PROCESSLIST;

Suchen Sie nach einer Reihe aktiver Abfragen, langen Time-Werten, Sperrwartezeiten und vielen inaktiven Sleep-Verbindungen. Eine einzelne langsame Berichtsabfrage wird anders behandelt als Hunderte von Webverbindungen, die auf dieselbe Tabellensperre warten.

Überprüfen Sie dann die Thread-Zähler:

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Threads_connected',
  'Threads_running',
  'Max_used_connections'
);

Threads_connected sagt Ihnen, wie viele Clients verbunden sind. Threads_running ist während einer Verlangsamung normalerweise wichtiger, da es zeigt, wie viele Threads aktiv arbeiten. Viele verbundene, schlafende Clients können verschwenderisch sein, aber viele laufende Threads können bedeuten, dass der Server echtem Druck ausgesetzt ist.

Überprüfen Sie als Nächstes, ob die Arbeitslast temporäre Tabellen auf der Festplatte erstellt:

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Created_tmp_tables',
  'Created_tmp_disk_tables',
  'Sort_merge_passes'
);

Diese Zähler sind seit dem Start kumulativ, daher kann eine einzelne Momentaufnahme irreführend sein. Machen Sie während des Vorfalls zwei Momentaufnahmen im Abstand von einigen Minuten. Wenn die temporären Festplattentabellen schnell zunehmen, überprüfen Sie Abfragen mit GROUP BY, ORDER BY, großen Joins, Textspalten oder fehlenden Indizes. Die Erhöhung von tmp_table_size kann in einigen Fällen helfen, aber eine bessere Abfrage oder ein besserer Index ist oft die sauberere Lösung.

Betrachtung des InnoDB-Drucks

Die meisten modernen MySQL-Bereitstellungen verwenden InnoDB, daher verdienen InnoDB-Zähler Aufmerksamkeit:

SHOW GLOBAL STATUS WHERE Variable_name LIKE 'Innodb_buffer_pool_read%';

Innodb_buffer_pool_read_requests zählt logische Leseanforderungen. Innodb_buffer_pool_reads zählt Lesevorgänge, die auf die Festplatte gehen mussten. Wenn physische Lesevorgänge bei normalem Datenverkehr schnell ansteigen, ist der Puffer-Pool möglicherweise zu klein für den Arbeitsdatensatz, Abfragen scannen möglicherweise zu viele Daten oder ein Batch-Job schiebt nützliche Seiten aus dem Cache.

Sperrwartezeiten sind eine weitere häufige Schmerzquelle:

SHOW GLOBAL STATUS WHERE Variable_name LIKE 'Innodb_row_lock%';

Steigende Zeilensperrwartezeiten bedeuten nicht automatisch, dass InnoDB defekt ist. Sie bedeuten normalerweise, dass Transaktionen Sperren länger halten als von der Anwendung erwartet. Suchen Sie nach offenen Transaktionen, langsamen Aktualisierungen oder Codepfaden, die eine Transaktion starten, externe Dienste aufrufen und viel später committen.

Für tiefere Sperr- und Transaktionsdetails kann SHOW ENGINE INNODB STATUS\G helfen, aber seine Ausgabe ist dicht. Verwenden Sie es, wenn die Prozessliste Sperrwartezeiten anzeigt und Sie das Transaktionsmuster dahinter identifizieren müssen.

Sicherere Verwendung von `KILL`

KILL ist nützlich, aber kein Aufräumknopf. Wenn Sie eine Verbindung beenden, die eine große Transaktion ausführt, muss MySQL möglicherweise die Arbeit rückgängig machen, und ein Rollback kann Zeit in Anspruch nehmen. In einigen Vorfällen ist das trotzdem die richtige Maßnahme, aber treffen Sie die Entscheidung bewusst.

Bevorzugen Sie das Beenden der Abfrage zuerst, wenn Ihre MySQL-Version und Berechtigungen dies unterstützen:

KILL QUERY 12345;

Dies versucht, die aktuelle Anweisung zu stoppen, während die Verbindung am Leben bleibt. Wenn der Client sich falsch verhält oder die Verbindung beendet werden muss, verwenden Sie:

KILL CONNECTION 12345;

Bevor Sie etwas beenden, erfassen Sie die Zeile der Prozessliste, den Benutzer, Host, die Datenbank und den SQL-Text. Nach dem Vorfall hilft Ihnen dieses Detail, die Quelle zu beheben, anstatt darauf zu warten, dass dieselbe Abfrage zurückkommt.

Häufige Prozesslistenzustände und was sie bedeuten

Sending data bedeutet nicht immer, dass MySQL Zeilen über das Netzwerk sendet. Es bedeutet oft, dass der Server Zeilen liest, filtert, sortiert oder vorbereitet. Wenn eine Abfrage viel Zeit dort verbringt, führen Sie EXPLAIN für die Anweisung aus und suchen Sie nach Tabellenscans, schlechter Join-Reihenfolge oder fehlenden Indizes.

Copying to tmp table oder Creating sort index deutet oft auf teure Sortier- oder Gruppierungsvorgänge hin. Überprüfen Sie, ob ein Index das WHERE- und ORDER BY-Muster unterstützen kann. Manchmal macht die Abfrage genau das, was das Produkt verlangt, aber sie gehört in einen asynchronen Bericht anstatt in einen Anforderungspfad.

Waiting for table metadata lock tritt oft auf, wenn DDL und normale Abfragen kollidieren. Ein scheinbar einfaches ALTER TABLE kann hinter einer offenen Transaktion warten, während sich spätere Abfragen hinter dem ausstehenden DDL stapeln. In diesem Fall kann das Beenden des ältesten Blockers sicherer sein, als jede wartende Abfrage zu beenden.

Zähler in nützliche Beweise verwandeln

Da die Werte von SHOW STATUS meistens Zähler sind, sind Raten nützlicher als rohe Zahlen. Erfassen Sie dieselben Variablen zweimal:

SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Slow_queries';

Warten Sie sechzig Sekunden und führen Sie sie dann erneut aus. Die Differenz gibt Ihnen die Rate während dieser Minute. Dies ist dieselbe Idee, die Dashboards verwenden, aber die manuelle Durchführung ist hilfreich, wenn Sie nur Terminalzugriff haben.

Machen Sie sich während Vorfällen Notizen. "Threads_running sprang von 8 auf 90, die Prozessliste zeigte 70 Abfragen, die auf eine Metadatensperre für orders warteten, und Max_used_connections änderte sich nicht" ist eine nützliche Diagnose. "MySQL war langsam" ist es nicht.

Wann diese Befehle nicht ausreichen

SHOW STATUS und SHOW PROCESSLIST sind Erstreaktionstools. Sie ersetzen nicht das langsame Abfrageprotokoll, Performance Schema, Abfragepläne oder Metriken auf Host-Ebene. Wenn dasselbe Problem zurückkehrt, aktivieren oder überprüfen Sie das langsame Abfrageprotokoll und untersuchen Sie die schlechtesten Anweisungen mit EXPLAIN.

Bei wiederkehrenden Verbindungsspitzen überprüfen Sie die Anwendungspool-Einstellungen und das Bereitstellungsverhalten. Die Erhöhung von max_connections kann Zeit erkaufen, aber es kann auch dazu führen, dass der Server mehr Arbeit annimmt, als er tatsächlich ausführen kann. Bei wiederkehrenden Sperrwartezeiten überprüfen Sie die Transaktionsgrenzen in der Anwendung. Eine Transaktion, die offen bleibt, während Code eine externe API aufruft, kann nicht zusammenhängende Anforderungen blockieren und MySQL langsamer erscheinen lassen, als es ist.

Überprüfen Sie auch den Host. Wenn die Festplattenlatenz hoch ist, die CPU ausgelastet ist, der Speicher ausgelagert wird oder ein lauter Nachbar Ressourcen stiehlt, zeigen MySQL-Zähler Symptome, aber nicht die gesamte Ursache. Eine gute Diagnose kombiniert Datenbankbefehle mit Systemmetriken.

SHOW STATUS gibt Ihnen Zähler und Kontext. SHOW FULL PROCESSLIST gibt Ihnen die Live-Arbeitslast. Zusammen verwendet, helfen sie Ihnen, den Unterschied zwischen Verbindungsdruck, einer schlechten Abfrage, Sperrkonflikten, festplattenintensiver temporärer Arbeit und InnoDB-Cache-Druck zu erkennen.