MySQL-Leistung überwachen: Verwendung von SHOW STATUS und SHOW PROCESSLIST

Die Diagnose von Leistungsengpässen und das Verständnis des Zustands Ihrer MySQL-Datenbank sind grundlegende Fähigkeiten für jeden Administrator oder Entwickler. Langsame Abfragen, Verbindungsfluten oder unerwartete Ressourcennutzung können die Anwendungsleistung erheblich beeinträchtigen. Glücklicherweise stellt MySQL integrierte, leicht zugängliche Befehle bereit, die sofortige Einblicke in Echtzeit bieten. Dieser Artikel befasst sich eingehend mit zwei der wichtigsten Befehle für die Leistungsdiagnose: SHOW STATUS und SHOW PROCESSLIST.

Durch die Beherrschung dieser Tools erhalten Sie die Möglichkeit, aktive Verbindungen zu analysieren, serverweite Zähler zu überprüfen und genau festzustellen, wo Ihre Systemressourcen verbraucht werden.

Verständnis der Systemgesundheit in Echtzeit 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 bis hin zur Cache-Effizienz und Sperrwartezeiten.

Globaler vs. Sitzungsstatus

Bei der Ausführung dieses Befehls ist es wichtig, den Geltungsbereich zu verstehen:

SHOW GLOBAL STATUS: Zeigt die seit dem Start der MySQL-Serverinstanz angesammelten Zähler an. Dies bietet eine Gesamtübersicht über den allgemeinen Serverzustand und langfristige Trends.
SHOW SESSION STATUS: Zeigt Zähler an, die nur für die aktuell verwendete Verbindung (Sitzung) gelten. 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 mehrere für die anfängliche Leistungsbeurteilung von entscheidender Bedeutung. Normalerweise möchten Sie die Ausgabe an grep weiterleiten oder eine WHERE-Klausel verwenden, um nach Relevanz zu filtern.

1. Überwachung von Verbindungen und Threads

Diese Variablen helfen Ihnen, die Verbindungslast zu verstehen:

Variablenname	Beschreibung
`Threads_connected`	Die Anzahl der aktuell geöffneten 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 Start des Servers. 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 veralteten Abfrage-Cache verwenden (in älteren MySQL-Versionen verfügbar, in neueren entfernt/veraltet), sind diese Metriken unerlässlich:

Qcache_hits: Anzahl der Male, in denen eine Abfrage aus dem Cache bedient wurde.
Qcache_lowmem_prunes: Anzahl der Abfragen, die dazu führten, dass der Cache ältere Einträge aufgrund von geringem Speicherplatz verdrängt hat.

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

Für moderne Bereitstellungen, die die InnoDB-Speicher-Engine verwenden, überwachen Sie die Pufferpool-Aktivität:

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 Anfragen deutet auf die Notwendigkeit eines größeren Pufferpools hin).

Praktischer Tipp: Um die Effizienz des Pufferpools schnell zu beurteilen, 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 und ermöglicht es Ihnen, lang laufende oder blockierte Abfragen zu identifizieren.

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 (zum Beenden des Prozesses verwendet).
User	Das verbundene Benutzerkonto.
Host	Der Host, von dem die Verbindung stammt.
db	Die Datenbank, die der Thread gerade verwendet.
Command	Die Art 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ächlich ausgeführte SQL-Anweisung (oder abgeschnitten, wenn sie lang ist).

Filtern und Interpretieren der Ausgabe

Bei großen Produktionssystemen kann die vollständige Prozessliste überwältigend sein. Es ist gängige Praxis, 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 von blockierenden oder langsamen Abfragen

Überwachen Sie die Spalten Time und Command:

Hoher Time-Wert: Jede Abfrage, die über einen längeren Zeitraum läuft (z. B. mehr als 10 Sekunden, abhängig von Ihrem SLA), muss sofort untersucht werden. Überprüfen Sie die entsprechende Info-Spalte, um die SQL-Anweisung anzuzeigen.
Command = 'Sleep': Diese Verbindungen sind untätig, verbrauchen aber weiterhin Ressourcen. Wenn sie sich übermäßig ansammeln, sollten Sie die Variable wait_timeout anpassen.
Command = 'Query': Dies sind aktiv laufende Anweisungen. Achten Sie genau auf ihren State.

3. Identifizieren von Sperrproblemen

Wenn Abfragen darauf warten, Ressourcen zu erlangen, zeigt der State-Spalte dies oft an:

Waiting for table metadata lock (Warten auf Tabellenmetadaten-Sperre)
Waiting for table lock (Warten auf Tabellensperre)
Waiting for lock (Warten auf Sperre)

Wenn Sie zahlreiche Threads in einem Wartezustand sehen, signalisiert dies eine Kontention, die normalerweise durch eine lang laufende Transaktion verursacht wird, 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 in der Prozessliste

Warnung: Verwenden Sie KILL mit Vorsicht. Das Beenden einer aktiven Transaktion kann die Datenbank in einem inkonsistenten Zustand hinterlassen, wenn die Transaktion mitten in einem komplexen Schreibvorgang war. Versuchen Sie, wenn möglich, immer zuerst, die Abfrage zu identifizieren und zu optimieren.

Kombination von Status- und Prozessinformationen zur Fehlerbehebung

Effektives MySQL-Monitoring beinhaltet oft die Korrelation dieser beiden Befehle:

Erste Prü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'. Stehen Sie vor einer Flut oder nur einer fehlerhaften Abfrage?
Tiefgehende Analyse: Wenn eine bestimmte Abfrage langsam ist, analysieren Sie deren Auswirkungen auf die Ressourcenzähler, indem Sie Innodb_buffer_pool_reads oder die Raten der Erstellung temporärer Tabellen überprüfen, während die Abfrage läuft (erfordert einen Basislinienvergleich).

Durch die regelmäßige Überprüfung dieser dynamischen Ausgaben vermeiden Sie Rätselraten und wenden gezielte Lösungen an, um die Stabilität und Geschwindigkeit von MySQL zu verbessern.

Fazit

Die Befehle SHOW STATUS und SHOW PROCESSLIST sind die wichtigsten Werkzeuge für die MySQL-Diagnose in Echtzeit. SHOW STATUS liefert den historischen Kontext und die Zähler-Metriken, die zur Abstimmung der Serverkonfiguration erforderlich sind, während SHOW PROCESSLIST den sofortigen Schnappschuss liefert, der zur Bewältigung aktueller Notfälle oder lang laufender Ausführungen erforderlich ist. Die regelmäßige Nutzung dieser Befehle ist unerlässlich, um eine leistungsstarke Datenbankumgebung aufrechtzuerhalten.