Lange laufende Abfragen können die Leistung Ihrer MySQL-Datenbank ernsthaft beeinträchtigen und alles verursachen, von langsamen Antwortzeiten bis hin zu vollständigen Engpässen, die jeden Benutzer betreffen. Diese lästigen Abfragen in den Griff zu bekommen – zu wissen, was sie sind, warum sie auftreten und wie man sie verwaltet – ist der Schlüssel für den reibungslosen Betrieb Ihrer Datenbank.

Ganz gleich, ob es darum geht, sie frühzeitig zu erkennen, sie aufzuhalten oder eine Möglichkeit einzurichten, automatisch mit ihnen umzugehen, dieser Leitfaden deckt alles ab.

Was ist eine lang laufende Abfrage?

Eine lang laufende Abfrage in MySQL ist eine Abfrage, deren Ausführung ungewöhnlich lange dauert.
Die spezifische Dauer, die eine Abfrage als „lang andauernd“ klassifiziert, kann abhängig von den Leistungsstandards Ihrer Anwendung variieren. Wenn eine Abfrage länger als gewöhnlich ausgeführt wird und Ihre Datenbank langsamer wird, gilt sie im Allgemeinen als lang ausgeführt.

Hauptursachen lang andauernder Abfragen

Die Ursachen für lang laufende Abfragen können vielfältig sein:

• Fehlende ordnungsgemäße Indizierung – Ohne geeignete Indizierung muss MySQL die gesamte Tabelle scannen, um die erforderlichen Daten abzurufen. Dieser Prozess ist insbesondere bei großen Tabellen äußerst ineffizient, da er viel Zeit und Ressourcen verbraucht.

• Situationen mit hoher Auslastung – Wenn der Server eine große Menge an Abfragen verarbeitet oder einige komplexe Abfragen gleichzeitig verarbeitet, werden die verfügbaren Ressourcen (wie CPU und Speicher) knapp. Dieser Wettbewerb um Ressourcen kann die Ausführung von Abfragen verzögern und zu längeren Laufzeiten führen, insbesondere in Zeiten der Spitzenauslastung.

• Sperrkonflikt – Dies tritt auf, wenn mehrere Transaktionen gleichzeitig Zugriff auf dieselben Daten erfordern, aber blockiert werden, weil andere Vorgänge die erforderlichen Sperren halten. Wenn beispielsweise eine Transaktion eine Zeile aktualisiert, muss eine andere Transaktion, die dieselbe Zeile lesen oder aktualisieren möchte, warten, bis die erste Transaktion abgeschlossen ist und die Sperre aufhebt.

• Unsachgemäße Normalisierung – Während die Normalisierung dazu beiträgt, Datenredundanz zu vermeiden und die Datenintegrität zu verbessern, können übermäßig normalisierte Datenbanken zu komplexen Abfragen mit mehreren Verknüpfungen führen. Diese können die Leistung beeinträchtigen. Auf der anderen Seite kann eine Unternormalisierung zu übermäßiger Datenduplizierung führen, was zu größeren Tabellen und langsameren Abfragen führt.

• Große Verknüpfungen – Abfragen, die die Verknüpfung großer Tabellen beinhalten, insbesondere ohne geeignete Indizes, können langsam sein. Die Datenbank muss Zeilen in den Tabellen basierend auf Join-Bedingungen abgleichen, ein Prozess, der ohne effiziente Indizierung sehr ressourcenintensiv und langsam sein kann.

Identifizieren lang laufender Abfragen

Um lang laufende Abfragen effektiv verwalten zu können, müssen Sie diese zunächst identifizieren. Hier sind einige Methoden:

1. Verwenden von SHOW PROCESSLIST

Die SHOW PROCESSLIST; Mit dem Befehl können Sie schnell eine Momentaufnahme aller aktiven Abfragen erstellen, die auf Ihrem Server ausgeführt werden. Dieser Befehl zeigt jede Abfrage zusammen mit mehreren wichtigen Informationen an, einschließlich der Dauer der Ausführung der einzelnen Abfragen. Diejenigen mit einem hohen „Zeit“-Wert sind wahrscheinlich Ihre lang andauernden Abfragen. So können Sie diesen Befehl verwenden:

VOLLSTÄNDIGE PROZESSLISTE ANZEIGEN;

Dieser Befehl listet alle aktuellen Prozesse auf, zeigt an, wer sie gestartet hat, welche Art von Befehl sie ausführen und vor allem, wie lange sie schon dabei sind. Wenn Sie Abfragen entdecken, die ungewöhnlich lange ausgeführt wurden, handelt es sich dabei um Ihre Abfragen mit langer Laufzeit. Sie können dann entscheiden, ob Sie sich eingehender mit der Optimierung befassen oder sie einfach löschen möchten, wenn sie die Leistung Ihres Systems beeinträchtigen.

2. Das Protokoll langsamer Abfragen

Das Einrichten des Protokolls für langsame Abfragen ist eine weitere großartige Strategie, um diese problematischen Abfragen abzufangen. Diese praktische MySQL-Funktion protokolliert jede Abfrage, deren Ausführung länger dauert als ein bestimmter Schwellenwert. Es geht nicht nur darum, lang laufende Abfragen abzufangen – es kann Ihnen auch dabei helfen, Abfragen zu identifizieren, die Indizes nicht effizient nutzen.

Um das langsame Abfrageprotokoll zum Laufen zu bringen, müssen Sie einige Einstellungen in Ihrer MySQL-Konfigurationsdatei (entweder my.cnf oder my.ini) anpassen:

• Slow_query_log – Setzen Sie dies auf 1, um das Protokoll zu aktivieren.
• Slow_query_log_file – Geben Sie den Dateipfad an, in dem Sie das Protokoll speichern möchten.
• Long_query_time – Legen Sie die minimale Ausführungszeit (in Sekunden) fest, die eine Abfrage für die Protokollierung qualifiziert. Wenn Sie ihn beispielsweise auf „2“ setzen, werden alle Abfragen protokolliert, deren Ausführung länger als zwei Sekunden dauert.

3. Leistungsschema

Das Leistungsschema von MySQL ist für eine detailliertere Untersuchung von unschätzbarem Wert. Dieses Tool wurde entwickelt, um Serverereignisse zu überwachen und Leistungsmetriken zu verfolgen, sodass Sie einen klareren Überblick über die Abfrageausführung und die Gesamtsystemleistung erhalten.

Stellen Sie sicher, dass es in Ihrer MySQL-Konfiguration aktiviert ist, indem Sie die folgende Zeile hinzufügen:

[mysqld]
performance_schema = ON

Sobald es aktiviert ist, können Sie verschiedene Leistungsschematabellen durchsuchen, um die Leistung Ihrer Abfragen zu analysieren. Wenn Sie beispielsweise Abfragen mit langer Laufzeit ermitteln möchten, sollten Sie einen Blick in die Tabelle events_statements_history_long werfen. So können Sie es abfragen:

SELECT EVENT_ID, SQL_TEXT, TIMER_WAIT/1000000000 AS 'Dauer (Sekunden)'
VON performance_schema.events_statements_history_long
WHERE TIMER_WAIT > 10000000000;

Mit dieser Abfrage können Sie alle Abfragen finden, die länger als 10 Sekunden ausgeführt wurden. Es gibt Ihnen Details wie den SQL-Text und wie lange jede Abfrage ausgeführt wurde.

Manuelles Beenden lang laufender Abfragen

Wenn Sie eine Abfrage festgestellt haben, die zu lange dauert und die Ressourcen Ihres Systems belastet, haben Sie die Möglichkeit, sie manuell zu beenden. Dies geschieht mit dem KILL-Befehl gefolgt von der spezifischen Prozess-ID der Abfrage.

Sie können die Prozess-ID finden, indem Sie den Befehl SHOW PROCESSLIST ausführen, der alle aktuell laufenden Prozesse und ihre jeweiligen IDs anzeigt. Durchsuchen Sie die Liste nach Abfragen, die einen hohen „Zeit“-Wert aufweisen, der angibt, wie lange sie bereits ausgeführt wurden.

Sobald Sie eine problematische Abfrage identifiziert und ihre Prozess-ID notiert haben, können Sie sie mit dem KILL-Befehl beenden:

KILL [Prozess-ID];

Ersetzen Sie [Prozess-ID] durch die tatsächliche Nummer aus der Ausgabe von SHOW PROCESSLIST.

Seien Sie bei diesem Ansatz vorsichtig. Das plötzliche Anhalten einer Abfrage kann manchmal zu Problemen führen, z. B. dazu, dass Ihre Daten in einem inkonsistenten Zustand bleiben, wenn die Abfrage gerade Informationen schreibt oder aktualisiert.

So automatisieren Sie das Beenden von Abfragen mit langer Laufzeit

Das Einrichten einer Automatisierung für die Verarbeitung lang laufender Abfragen kann ein echter Lebensretter sein und verhindern, dass diese trägen oder nicht optimierten Abfragen Ihre Datenbankressourcen belasten und das gesamte System verlangsamen oder sogar blockieren. Gehen Sie jedoch vorsichtig vor – die Verwendung dieses Tools ohne die richtigen Prüfungen kann tatsächlich tiefer liegende Leistungsprobleme verbergen, die Ihre Aufmerksamkeit erfordern.

Stellen Sie immer sicher, dass Sie über eine umfassende Protokollierung und Überwachung verfügen, um die Auswirkungen beendeter Abfragen auf Ihre Anwendung zu analysieren, und erwägen Sie, diese Abfragen zu verbessern, anstatt sie einfach automatisch zu beenden. Betrachten Sie die automatische Beendigung als Teil einer umfassenderen Strategie zur Leistungsoptimierung und nicht als Komplettlösung.

1. Aktivieren Sie den Ereignisplaner

Zuerst müssen Sie den MySQL Event Scheduler aktivieren, der standardmäßig deaktiviert ist. Mit dem Event Scheduler können Sie Aufgaben erstellen und planen, die der Server automatisch zu vordefinierten Zeiten ausführen soll. Führen Sie den folgenden Befehl aus:

SET GLOBAL event_scheduler = ON;

2. Erstellen Sie ein Ereignis, um lang laufende Abfragen zu beenden

Wenn der Scheduler aktiviert ist, besteht der nächste Schritt darin, das eigentliche Ereignis zu definieren, das die lang laufenden Abfragen überwacht und beendet. Das Ereignis wird jede Minute ausgeführt, um zu prüfen, ob Abfragen länger als ein bestimmter Schwellenwert (z. B. 60 Sekunden) ausgeführt werden. Sobald es identifiziert ist, werden diese Abfragen automatisch beendet. Hier ist eine Aufschlüsselung des SQL-Codes zum Einrichten dieses Ereignisses:

`EREIGNIS ERSTELLEN kill_long_running_queries
ALLE 1 MINUTE ZEITPLAN – Gibt an, wie oft das Ereignis ausgeführt wird
TUN
BEGINNEN
DECLARE done INT DEFAULT FALSE;
DECLARE proc_id INT; – Variable zum Speichern der Prozess-ID jeder Abfrage
DECLARE cur1 CURSOR FÜR SELECT ID FROM information_schema.processlist
WHERE Command = 'Query' AND Time > 60; – Ändern Sie „60“ zu Ihrem Schwellenwert in Sekunden
DECLARE CONTINUE HANDLER FÜR NOT FOUND SET done = TRUE;

ÖFFNEN cur1;

read_loop: LOOP
FETCH cur1 INTO proc_id;
WENN erledigt, DANN
LEAVE read_loop;
ENDE WENN;
KILL proc_id; – Tötet den durch proc_id
identifizierten Prozess ENDSCHLEIFE;

CLOSE cur1;
ENDE;`

3. Richten Sie die maximale Ausführungszeit für Abfragen in MySQL ein

Die Steuerung der maximalen Ausführungszeit für eine Abfrage trägt dazu bei, zu verhindern, dass die Datenbank durch übermäßig lang laufende Abfragen belegt wird. Dies erfolgt mithilfe der Systemvariablen max_execution_time in MySQL 5.7.8 und späteren Versionen, indem ein systemweites Ausführungszeitlimit für alle schreibgeschützten SELECT-Abfragen festgelegt wird:

SET GLOBAL max_execution_time = 2000;

Dies setzt das Limit auf 2000 Millisekunden (2 Sekunden)

Denken Sie daran, dass diese Einstellung nicht für gespeicherte Prozeduren, Funktionen oder Trigger gilt und beim Neustart des Servers auf die Standardeinstellung zurückgesetzt wird, sofern sie nicht zu Ihrer MySQL-Konfigurationsdatei hinzugefügt wird:

[mysqld]
max_execution_time = 2000

4. Richten Sie die maximale Anweisungszeit in MariaDB ein

MariaDB ist zwar eine Abspaltung von MySQL, bietet aber einen ähnlichen, aber unterschiedlichen Ansatz zur Verwaltung der Abfrageausführungszeiten. Ab MariaDB 10.1.1 können Sie zu diesem Zweck die Systemvariable max_statement_time verwenden:

SET GLOBAL max_statement_time = 2;

Dies begrenzt die Ausführungszeit für alle Abfragen auf 2 Sekunden.

Für eine dauerhafte Konfiguration durch Serverneustarts fügen Sie diese Zeile zu Ihrer MariaDB-Konfigurationsdatei hinzu:

[mysqld]
max_statement_time = 2

Identifizieren und beheben Sie langwierige Abfragen mit Query Analytics von Releem

Das Abfrageanalysetool von Releem revolutioniert die Art und Weise, wie Sie die Leistung Ihrer Datenbank überwachen und optimieren. Es sammelt automatisch detaillierte Informationen zu den 100 häufigsten Abfragen und liefert wichtige Kennzahlen wie die durchschnittliche Ausführungszeit und die Gesamtauswirkungen jeder Abfrage auf die Betriebseffizienz Ihrer Datenbank.

Mit Releem ist es nicht erforderlich, die PROCESSLIST-Ausgabe manuell zu durchsuchen oder in das langsame Abfrageprotokoll zu schauen, um leistungsschwache Abfragen zu identifizieren. Das Tool verfügt über ein intuitives Dashboard, mit dem Sie Abfragen, die verzögert sind oder übermäßig viel Zeit in Anspruch nehmen, mühelos sortieren und erkennen können. Dieser sofortige Einblick hilft Ihnen, Engpässe im Handumdrehen zu erkennen und zu beheben.