Gründe, warum MySQL Kill Threads nicht beenden kann

Hintergrund

Im täglichen Gebrauch kann es vorkommen, dass sich in MySQL von Zeit zu Zeit einzelne oder viele Verbindungen häufen. In diesem Fall ziehen Sie im Allgemeinen in Betracht, diese lang gestapelten Verbindungen mit dem Kill-Befehl zwangsweise zu beenden, um die Anzahl der Verbindungen und die CPU-Ressourcen des Datenbankservers so schnell wie möglich freizugeben.

Problembeschreibung

Bei der tatsächlichen Verwendung des Kill-Befehls stellen Sie möglicherweise fest, dass die Verbindung nicht sofort beendet wird und noch immer in der Prozessliste angezeigt wird. Anstelle der üblichen Abfrage oder Ausführung wird jedoch „Befehl beendet“ angezeigt. Zum Beispiel:

mysql> Prozessliste anzeigen;
+----+------+--------------------+--------+---------+------+--------------+--------------------------------+
| ID | Benutzer | Host | db | Befehl | Zeit | Status | Info |
+----+------+--------------------+--------+---------+------+--------------+--------------------------------+
| 31 | root | 192.168.1.10:50410 | sbtest | Abfrage | 0 | wird gestartet | Prozessliste anzeigen |
| 32 | root | 192.168.1.10:50412 | sbtest | Abfrage | 62 | Benutzer sleep | select sleep(3600) from sbtest1 |
| 35 | root | 192.168.1.10:51252 | sbtest | Beendet | 47 | Daten werden gesendet | select sleep(100) from sbtest1 |
| 36 | root | 192.168.1.10:51304 | sbtest | Abfrage | 20 | Daten werden gesendet | select sleep(3600) from sbtest1 |
+----+------+--------------------+--------+---------+------+--------------+--------------------------------+

Ursachenanalyse

Im Zweifelsfall sollten Sie zunächst die offizielle Dokumentation lesen. Hier sind einige Auszüge aus der offiziellen Dokumentation:

Wenn Sie KILL verwenden, wird ein threadspezifisches Kill-Flag für den Thread gesetzt. In den meisten Fällen kann es einige Zeit dauern, bis der Thread beendet wird, da das Kill-Flag nur in bestimmten Abständen überprüft wird: Bei SELECT-Operationen wird das Flag für ORDER BY- und GROUP BY-Schleifen nach dem Lesen eines Zeilenblocks überprüft. Wenn das Kill-Flag gesetzt ist, wird die Anweisung abgebrochen.
ALTER TABLE-Operationen, die eine Tabellenkopie erstellen, überprüfen das Kill-Flag regelmäßig für jeweils einige kopierte Zeilen, die aus der Originaltabelle gelesen wurden. Wenn das Kill-Flag gesetzt wurde, wird die Anweisung abgebrochen und die temporäre Tabelle gelöscht.
Die KILL-Anweisung wird zurückgegeben, ohne auf eine Bestätigung zu warten, aber die Kill-Flag-Prüfung bricht den Vorgang innerhalb einer relativ kurzen Zeitspanne ab. Das Abbrechen des Vorgangs, um eine erforderliche Bereinigung durchzuführen, dauert ebenfalls einige Zeit.
Bei UPDATE- oder DELETE-Operationen wird das Kill-Flag nach jedem gelesenen Block und nach jeder aktualisierten oder gelöschten Zeile geprüft. Wenn das Kill-Flag gesetzt ist, wird die Anweisung abgebrochen. Wenn Sie keine Transaktionen verwenden, werden die Änderungen nicht zurückgesetzt.
GET_LOCK() bricht ab und gibt NULL zurück.
Befindet sich der Thread im Tabellensperrhandler (Zustand: Gesperrt), wird die Tabellensperre schnell abgebrochen.
Wenn der Thread bei einem Schreibaufruf auf freien Speicherplatz wartet, wird der Schreibvorgang mit der Fehlermeldung „Festplatte voll“ abgebrochen.

Der erste Absatz des offiziellen Dokuments beschreibt klar und deutlich den Kill-Mechanismus: Für den verbundenen Thread wird eine Kill-Markierung auf Thread-Ebene gesetzt, die erst bei der nächsten „Markierungserkennung“ wirksam wird. Dies bedeutet auch, dass, wenn die nächste „Markierungserkennung“ nicht rechtzeitig erfolgt, das im Problem beschriebene Phänomen auftreten kann.

In der offiziellen Dokumentation sind eine Reihe von Szenarien aufgeführt. Hier sind einige häufige Problemszenarien basierend auf der offiziellen Beschreibung:

• Wenn beim Ausführen von „Order By“ und „Group By“ in einer Select-Anweisung die CPU-Ressourcen des Servers knapp sind, dauert das Lesen/Abrufen eines Datenstapels länger, was sich wiederum auf den Zeitpunkt der nächsten „Markierungserkennung“ auswirkt.
• Beim Ausführen von DML-Operationen auf großen Datenmengen lösen SQL-Anweisungen wie kill ein Rollback der Transaktion aus (InnoDB-Engine). Obwohl die Anweisung beendet wird, dauert der Rollback-Vorgang sehr lange.
• Wenn während des Kill-Alter-Vorgangs die Serverlast hoch ist, dauert die Verarbeitung eines Datenstapels länger, was sich auf den Zeitpunkt der nächsten „Markierungserkennung“ auswirkt.
• Wenn wir uns auf den Kill-Mechanismus beziehen und eine induktive Beschreibung vornehmen, gilt: Jedes Verhalten, das die normale Ausführung von SQL-Anweisungen blockiert/verlangsamt, führt dazu, dass die nächste „Markierungserkennung“ verschoben wird oder nicht erfolgen kann, und führt letztendlich zum Fehlschlagen der Kill-Operation.
  

Simulieren Sie es

Hier verwenden wir einen Parameter innodb_thread_concurrency, um das Szenario der Blockierung der normalen Ausführung von SQL-Anweisungen zu simulieren:

Definiert die maximale Anzahl von Threads, die in InnoDB zulässig sind. Ein Wert von 0 (Standard) wird als unendliche Parallelität (keine Begrenzung) interpretiert. Diese Variable ist für die Leistungsoptimierung auf Systemen mit hoher Parallelität vorgesehen.

Wenn dieser Parameter auf einen niedrigen Wert eingestellt ist, werden InnoDB-Abfragen, die den Grenzwert überschreiten, gemäß der offiziellen Dokumentation blockiert. Daher wurde dieser Parameter in dieser Simulation auf einen sehr niedrigen Wert eingestellt.

mysql> Variablen wie „%innodb_thread_concurrency%“ anzeigen;
+-----------------------------------------+----------+
| Variablenname | Wert |
+-----------------------------------------+----------+
| innodb_thread_concurrency | 1 |
+-----------------------------------------+----------+
1 Zeile im Satz (0,00 Sek.)

Öffnen Sie dann zwei Datenbankverbindungen (Sitzung 1 und Sitzung 2), führen Sie in jeder select sleep(3600) from sbtest.sbtest1 aus und beenden Sie dann die Abfrage von Sitzung 2 bei der dritten Verbindung:

Sitzung 1:
mysql> wähle sleep(3600) aus sbtest.sbtest1;

Sitzung 2:
mysql> wähle sleep(3600) aus sbtest.sbtest1;
FEHLER 2013 (HY000): Verbindung zum MySQL-Server während der Abfrage verloren
MySQL>

Sitzung 3:
mysql> Prozessliste anzeigen;
+----+------+--------------------+------+---------+---------+----------+--------------+----------------------------------------+
| ID | Benutzer | Host | db | Befehl | Zeit | Status | Info |
+----+------+--------------------+------+---------+---------+----------+--------------+----------------------------------------+
| 44 | root | 172.16.64.10:39290 | NULL | Abfrage | 17 | Benutzer sleep | select sleep(3600) from sbtest.sbtest1 |
| 45 | root | 172.16.64.10:39292 | NULL | Abfrage | 0 | wird gestartet | Prozessliste anzeigen |
| 46 | root | 172.16.64.10:39294 | NULL | Abfrage | 5 | Daten werden gesendet | select sleep(3600) from sbtest.sbtest1 |
+----+------+--------------------+------+---------+---------+----------+--------------+----------------------------------------+
3 Zeilen im Satz (0,00 Sek.)

mysql> töten 46;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> Prozessliste anzeigen;
+----+------+--------------------+------+---------+---------+----------+--------------+----------------------------------------+
| ID | Benutzer | Host | db | Befehl | Zeit | Status | Info |
+----+------+--------------------+------+---------+---------+----------+--------------+----------------------------------------+
| 44 | root | 172.16.64.10:39290 | NULL | Abfrage | 26 | Benutzer sleep | select sleep(3600) from sbtest.sbtest1 |
| 45 | root | 172.16.64.10:39292 | NULL | Abfrage | 0 | wird gestartet | Prozessliste anzeigen |
| 46 | root | 172.16.64.10:39294 | NULL | Beendet | 14 | Daten werden gesendet | select sleep(3600) from sbtest.sbtest1 |
+----+------+--------------------+------+---------+---------+----------+--------------+----------------------------------------+
3 Zeilen im Satz (0,00 Sek.)

MySQL>

Wie Sie sehen, wird die Verbindung von Sitzung 2 nach der Ausführung des Kill-Befehls sofort getrennt, die von Sitzung 2 initiierte Abfrage bleibt jedoch weiterhin in MySQL bestehen. Wenn ähnliche Probleme durch innodb_thread_concurrency verursacht werden, können Sie natürlich direkt den set global verwenden, um die Obergrenze zu erhöhen, oder ihn direkt auf 0 setzen, um das Problem zu lösen. Die Änderung dieses Parameters wird für alle Verbindungen in Echtzeit wirksam.

Um zusammenzufassen

Der Kill-Vorgang von MySQL beendet die Datenbankverbindung nicht direkt und zwangsweise, wie man sich das vorstellt. Er sendet nur ein Beendigungssignal. Wenn die Ausführungseffizienz von SQL selbst zu langsam ist oder durch andere Faktoren beeinträchtigt wird (hohe Serverlast, Auslösen eines Rollbacks großer Datenmengen), kann dieser Kill-Vorgang diese problematischen Abfragen möglicherweise nicht rechtzeitig beenden. Im Gegenteil, er kann eine erneute Verbindung auslösen, nachdem die programmseitige Verbindung getrennt wurde, was zu ineffizienteren Abfragen führt und die Datenbank weiter belastet.

Oben sind die Details, warum MySQL Kill Threads nicht beenden kann. Weitere Informationen zu MySQL Kill Threads finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!