Die 5 effektivsten MySQL-Befehle zur Problembehebung

Für diejenigen, die Datenbanken managen, kann das Diagnostizieren und Lösen von Problemen innerhalb von MySQL ein zeitaufwendiger und herausfordernder Prozess sein. An diesem Punkt ist das Wissen über die richtigen Werkzeuge und Befehle entscheidend, um Probleme schnell zu identifizieren und die Leistung der Datenbank zu verbessern.

In diesem Artikel werden wir die effektivsten Befehle untersuchen, die zur Identifizierung und Behebung von Problemen in MySQL eingesetzt werden können. Wir werden erörtern, welche Befehle verwendet werden sollten, in welchen Szenarien bestimmte Befehle angebracht sind und welche Art von Informationen jeder Befehl liefert. Zusätzlich bieten wir Schritt-für-Schritt-Erklärungen zur Nutzung jedes einzelnen Befehls.

Vertikale Ausgabe von "Show Processlist" sortiert

Um einen effizienten Überblick über die aktiven Prozesse in Ihrer MySQL-Datenbank zu erhalten, ermöglicht der Befehl: 

mysql -e 'SHOW PROCESSLIST\G' | grep State: | sort | uniq -c | sort -rn

eine präzise und sortierte Analyse der Zustände der Prozesse. Hier ein beispielhaftes Ergebnis dieser Abfrage:

58        State:  
1         State: Sending data  
1         State: Master has sent all binlog to slave; 
waiting for      binlog to be updated  
1         State: init

Dieser Befehl demonstriert die Möglichkeit, Datenbankoperationen direkt aus dem Bash-Verzeichnis heraus durchzuführen, ohne die MySQL-Konsole betreten zu müssen. Wenn mysql gestartet wird, nutzt das System standardmäßig den Benutzer “root“. Sofern keine Passworteinstellung vorliegt oder eine Konfigurationsdatei my.cnf vorhanden ist, wird der Befehl ausgeführt, der in Anführungszeichen steht und mit dem -e Flag spezifiziert wurde. Für die explizite Angabe von Benutzername und Passwort kann der Befehl nach diesem Muster modifiziert werden: 

mysql -u root -p"${MARIADB_ROOT_PASSWORD}" 
-e 'SHOW PROCESSLIST\G' | grep State: | sort | uniq -c | sort -rn

Dieses Vorgehen optimiert die Effizienz und minimiert die Zugriffszeit auf wichtige Datenbankinformationen.
Hier ist die Analyse dieser Zeile im Detail:

mysql -e 'SHOW PROCESSLIST\G':
Dieser Teil initialisiert den mysql-client und führt die Abfrage SHOW PROCESSLIST aus. Diese Abfrage listet die aktuellen Prozesse auf, die auf dem MySQL-Server laufen. Das SHOW PROCESSLIST \G Kommando wird verwendet, um die Ergebnisse in einem übersichtlicheren Format anzuzeigen.

" | “:
Dieses Symbol leitet die Ausgabe eines Befehls an einen anderen weiter. Es ermöglicht uns, die Ausgabe eines Befehls als Eingabe für einen anderen Befehl zu verwenden.

grep State: Dieser Befehl sucht nach dem Wort "State:" in der Ausgabe. Die Ausgabe von SHOW PROCESSLIST in MySQL enthält eine Reihe von Informationen für jeden Prozess, und "State:" ist eine dieser Informationen.

sort:
Dies sortiert die Ausgabe. Standardmäßig erfolgt die Sortierung alphabetisch.

uniq -c:
Dieser Befehl reduziert aufeinanderfolgende Zeilen auf eine einzelne Zeile und zählt, wie oft jede Zeile vorkommt ( -c Option).

sort -rn:  Dieser Befehl sortiert die Zahlen von höchster zu niedrigster ( -r Option) und sortiert sie numerisch ( -nOption).

Dieses Skript nimmt die auf dem MySQL-Server laufenden Prozesse, findet ihre Zustände, zählt die eindeutigen Zustände und sortiert sie nach den am häufigsten wiederholten Zuständen. Dies ist nützlich, um die Arbeitslast auf dem MySQL-Server zu verstehen, insbesondere wenn es viele Prozesse gibt und wir sehen wollen, welche Zustände am häufigsten auftreten. 

Die MySQL-Dokumentation dazu finden Sie hier.

Überprüfung des InnoDB-Motors auf aktive Transaktionen

mysql -e 'SHOW ENGINE INNODB STATUS\G' | grep -E  -A 5 'ACTIVE [0-9][0-9][0-9]'

Dies ist ein Befehlszeilenskript, das den Befehl SHOW ENGINE INNODB STATUS\G' in MySQL ausführt. SHOW ENGINE INNODB STATUS gibt Informationen zum Status des InnoDB storage engine aus. '\G' macht die Ergebnisse lesbarer, da es die Ergebnisse in Zeilen anstelle von Spalten anzeigt.

1-) mysql -e:Dies ist eine Option für die Verwendung der MySQL-Befehlszeilenschnittstelle zum Ausführen von Befehlen.-e wird verwendet, um den folgenden Befehl auszuführen.

2-) 'SHOW ENGINE INNODB STATUS\G': Dies ist der an MySQL gesendete Befehl. SHOW ENGINE INNODB STATUS zeigt den Status des InnoDB-Speichermotors an. '\G' formatiert die Ausgabe für die Lesbarkeit, indem jede Information auf einer separaten Zeile angezeigt wird.

3-) '|':Dies ist der "Pipe"-Operator, eine Möglichkeit, die Ausgabe eines Unix-/Linux-Befehls als Eingabe an einen anderen zu übergeben. In diesem Fall leitet er die Ausgabe des MySQL-Befehls an den nächsten Befehl weiter.

4-) grep -E: Der grep-Befehl wird verwendet, um Zeilen in einer Textdatei zu suchen, die zu einem bestimmten Muster passen. Die -E Option ermöglicht die erweiterte Suche und erlaubt die Verwendung von regulären Ausdrücken (Regex).

5-) '-A 5': Dies gibt die Verwendung des grep-Befehls an. Hier wird die -A-Option verwendet, um eine bestimmte Anzahl von Zeilen nach den übereinstimmenden Zeilen anzuzeigen. -A 5 zeigt also 5 Zeilen nach der übereinstimmenden Zeile.

6-) 'ACTIVE [0-9][0-9][0-9]': Dies ist ein regulärer Ausdruck, der dem grep-Befehl übergeben wird. Hier sucht er nach Zeilen, die das Wort ACTIVE gefolgt von drei Ziffern enthalten.

Wir versuchen, aktive Transaktionen mit diesem Befehl zu erfassen.

Zunächst erstelle ich eine Transaktion und starte einen Prozess.

MariaDB [sbtest]> BEGIN;
Query OK, 0 rows affected (0.009 sec)

MariaDB [sbtest]> update sbtest1 set c='xx' where id=3;
Query OK, 0 rows affected (0.001 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Nun können wir unseren Code ausführen und seine Ausgabe untersuchen.

Wenn wir nur bis zum ABSCHNITT AKTIV schreiben, können wir alle aktiven Transaktionen sehen.

root@mys:/home/my/# mysql -e 'SHOW ENGINE INNODB STATUS\G' | grep -E  -A 5 'ACTIVE '
---TRANSACTION 25, ACTIVE 11 sec
2 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 1
MariaDB thread id 31, OS thread handle 139904669455936, query id 90 localhost root 
--------
FILE I/O
--------

Jeder der Zahlenbereiche nach ACTIVE repräsentiert eine Ziffer, und wir können sie verwenden, um zu begrenzen, wenn wir möchten. Zum Beispiel, wenn ich alle drei Zahlen addiere, werden jetzt Transaktionen aufgelistet, die seit mehr als 100 Sekunden laufen.
Wenn ich nur zwei hinzufüge, werden jetzt Transaktionen aufgelistet, die seit mehr als 10 Sekunden laufen.
Und wenn ich nur 1 davon hinzufüge, entspricht dies dem Eintippen bis zum ACTIVE-Abschnitt. 

root@mys:/home/my/# mysql -e 'SHOW ENGINE INNODB STATUS\G' | grep -E  -A 5 'ACTIVE [0-9][0-9][0-9]'
root@mys:/home/my/
root@mys:/home/my/# mysql -e 'SHOW ENGINE INNODB STATUS\G' | grep -E  -A 5 'ACTIVE [0-9][0-9]'
---TRANSACTION 27, ACTIVE 20 sec
2 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 1
MariaDB thread id 31, OS thread handle 139904669455936, query id 95 localhost root 
--------
FILE I/O
--------

Wie oben zu sehen ist, hat es uns beim ersten Mal keine Ergebnisse geliefert. Denn mit diesem Befehl suchen wir nach Transaktionen, bei denen alle 3 Ziffern der Aktivitätsdauer größer als 0 sind.

Nach einer Weile haben wir nur nach 2 Schritten gefragt und dieses Mal haben wir Ergebnisse erhalten. Denn unsere Transaktion war zu diesem Zeitpunkt seit 20 Sekunden aktiv.

Wir versuchen es noch einmal, nachdem wir etwas länger gewartet haben.

root@mys:/home/my/# mysql -e 'SHOW ENGINE INNODB STATUS\G' | grep -E  -A 5 'ACTIVE [0-9][0-9][0-9]'
---TRANSACTION 27, ACTIVE 119 sec
2 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 1
MariaDB thread id 31, OS thread handle 139904669455936, query id 95 localhost root 
--------
FILE I/O
--------

Schließlich sind alle 3 Ziffern größer als 0 und wir konnten die Ausgabe dieses Befehls erhalten.

Verwenden von mysqladmin zum Abrufen einiger Metriken

mysqladmin ext -i1 | awk '/Queries/{q=$4-qp;qp=$4}/Threads_connected/{tc=$4}/Threads_running/{printf "%5d %5d %5d\n", q, tc, $4}'
97344538794    79    10
 1336          68     5
 1344          73     9
 1513          74     7
 1849          72     8
 1331          70     7
  604          70     8
  604          70     8

mysqladmin, ist ein Befehlszeilenwerkzeug, das zur Verwaltung des MySQL-Datenbankservers verwendet wird. Diese vielseitige Anwendung ermöglicht es uns, verschiedene Aufgaben zur Verwaltung und Überwachung eines MySQL-Servers durchzuführen.

Der Befehl mysqladmin ext -i1 wird verwendet, um die Erweiterungsstatistiken des MySQL-Servers in bestimmten Intervallen anzuzeigen. Die Option -i1 stellt sicher, dass die Statistiken alle Sekunde aktualisiert werden.
'|': Dies ist der "Pipe"-Operator, eine Möglichkeit, die Ausgabe eines Unix/Linux-Befehls als Eingabe an einen anderen zu übergeben. In diesem Fall leitet er die Ausgabe des MySQL-Befehls an den nächsten Befehl weiter.
Und dann wird die Ausgabe mithilfe von awk verarbeitet, um die gewünschten Informationen zu extrahieren.

Die Spalten in der Ausgabe enthalten folgende Informationen:

Abfragen: Berechnet als Differenz zwischen der Gesamtanzahl der Abfragen zwischen zwei aufeinanderfolgenden Messungen. Dies gibt die Anzahl der neuen Abfragen an, die zwischen zwei Messungen ausgeführt wurden.

Verbundene Threads: Stellt die Gesamtzahl der mit dem Server verbundenen Threads dar.

Laufende Threads: Stellt die Anzahl der gerade laufenden Threads dar.

Zum Beispiel betrachten wir die erste Zeile: 

• Es sind 97344538794 neue Abfragen aufgetreten.
• 79 Threads sind verbunden.
• 10 Threads laufen gerade.

Für ein weiteres Beispiel nehmen wir die dritte Zeile: 

• Zwischen den letzten beiden Messungen sind 8 neue Abfragen aufgetreten.
• 73 Threads sind verbunden.
• 9 Threads laufen gerade.

Diese Ausgabe kann verwendet werden, um die Leistung Ihres MySQL-Servers zu überwachen und zu verstehen. Sie bietet wertvolle Einblicke in Aspekte wie Änderungen der Abfragenlast, Zunahmen oder Abnahmen der Verbindungsanzahl und aktuelle Thread-Auslastung. 

Verwenden von mysqladmin zum Abrufen einiger Metriken

"Transaktion" ist ein grundlegendes Konzept im Datenbankmanagement, das eine Reihe von Operationen (oder eine einzelne Operation) als eine einzige Einheit behandelt. Es stellt sicher, dass entweder alle Änderungen, die von der Transaktion in der Datenbank vorgenommen wurden, übernommen werden, oder keine von ihnen.

In MySQL umfasst eine Transaktion in der Regel die folgenden Schritte:

 Beginn (BEGIN): Die Transaktion beginnt, und die Ressourcen, die mit der Transaktion verbunden sind, werden vom Datenbankmanager zugewiesen.

Ausführung von Operationen: Die Transaktion umfasst eine Reihe von SQL-Befehlen, die in der Datenbank definiert sind. Diese Befehle können Operationen wie das Lesen, Einfügen, Aktualisieren oder Löschen von Daten in der Datenbank durchführen.

COMMIT: Wenn die Transaktion erfolgreich abgeschlossen ist, werden die vorgenommenen Änderungen dauerhaft gemacht, und die Datenbank wird aktualisiert. Dieser Schritt bestätigt, dass alle Operationen auf die Datenbank angewendet wurden.
ROLLBACK: Wenn die Transaktion auf einen Fehler stößt oder fehlschlägt, werden alle vorgenommenen Änderungen rückgängig gemacht, und die Datenbank kehrt zu ihrem Zustand zurück, bevor die Transaktion begonnen hat. Diese Aktion kehrt die Auswirkungen der Transaktion auf die Datenbank um.

Transaktionen werden in vielen Szenarien verwendet, insbesondere um eine Reihe von miteinander verbundenen Operationen auszuführen und die Datenintegrität sicherzustellen. Daher sollten Situationen wie 'Idle Transaction' berücksichtigt werden und gegebenenfalls Transaktionsprozesse optimiert werden.

Nun lassen Sie uns eine Beispielssituation erstellen und sie im Detail untersuchen.

Angenommen, wir haben eine Datenbank und eine Tabelle in MySQL mit dem folgenden Inhalt:

MariaDB [sbtest]>
MariaDB [sbtest]> select * from sbtest1;
+----+---+-------+------+
| id | k | c     | pad  |
+----+---+-------+------+
|  1 | 1 | ax    | test |
|  2 | 1 | bx    | test |
+----+---+-------+------+
2 rows in set (0.002 sec)

Natürlich können wir unsere Aufgaben hier erledigen, indem wir unsere Codes Zeile für Zeile schreiben. Zum Beispiel können wir die "c"-Werte bei id=1 und id=2 wie folgt ändern.

MariaDB [sbtest]> update sbtest1 set c='cx' where id=1;
Query OK, 1 row affected (0.002 sec)
MariaDB [sbtest]> update sbtest1 set c='dx' where id=2;
Query OK, 1 row affected (0.002 sec)

Und wenn wir unsere Tabelle überprüfen, sehen wir, dass sich die Werte geändert haben.

MariaDB [sbtest]> select * from sbtest1;
+----+---+-------+------+
| id | k | c     | pad  |
+----+---+-------+------+
|  1 | 1 | cx    | test |
|  2 | 1 | dx    | test |
+----+---+-------+------+
2 rows in set (0.002 sec)

Nun möchten wir einen ähnlichen Prozess mit einer Transaktion durchführen. Zuerst erstellen wir sie mit dem BEGIN-Befehl und versuchen, den "c"-Wert bei id=2 zu ändern.

MariaDB [(none)]> BEGIN;
Query OK, 0 rows affected (0.001 sec)

MariaDB [sbtest]> update sbtest1 set c='ex' where id=2;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Um diese Operation abzuschließen, wie oben erwähnt, müssen wir sie entweder mit COMMIT bestätigen oder mit ROLLBACK abbrechen.
Zuerst betrachten wir den Endzustand unserer Tabelle, wenn wir keines dieser beiden Dinge tun.

MariaDB [sbtest]> select * from sbtest1;
+----+---+-------+------+
| id | k | c     | pad  |
+----+---+-------+------+
|  1 | 1 | cx    | test |
|  2 | 1 | dx    | test |
+----+---+-------+------+

2 rows in set (0.002 sec)

Wir sehen, dass die gewünschten Änderungen nicht in der Tabelle widergespiegelt werden. Um die Transaktion erfolgreich abzuschließen und wirksam zu machen, müssen wir den COMMIT -Befehl verwenden, aber wir haben ihn noch nicht verwendet.

Oder wenn wir diese Transaktion abbrechen möchten, müssen wir den ROLLBACK-Befehl verwenden. 

MariaDB [(sbtest)]> BEGIN;
Query OK, 0 rows affected (0.001 sec)

MariaDB [sbtest]> update sbtest1 set c='ex' where id=2;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0
MariaDB [sbtest]> COMMIT;

Wenn wir unsere Tabelle nach dem Hinzufügen des COMMIT-Befehls betrachten, können wir sehen, dass die gewünschte Änderung stattfindet.

MariaDB [sbtest]> select * from sbtest1;
+----+---+-------+------+
| id | k | c     | pad  |
+----+---+-------+------+
|  1 | 1 | cx    | test |
|  2 | 1 | ex    | test |
+----+---+-------+------+
2 rows in set (0.002 sec)

Wir können sehen, dass die gewünschte Änderung stattfindet.
Was passiert also, wenn wir die Transaktion ohne Verwendung von COMMIT und ROLLBACK Befehlen verlassen?

MariaDB [(sbtest)]> BEGIN;
Query OK, 0 rows affected (0.001 sec)

MariaDB [sbtest]> update sbtest1 set c='fx' where id=1;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Wenn wir den Code in diesem Zustand belassen, sehen wir, dass die Änderung, die wir an der Tabelle vornehmen wollten, nicht reflektiert wird. Aber wenn wir kurz über die Arbeitslogik von Datenbanken sprechen:

Wenn die Datenbank eine Zeile in der Tabelle bearbeiten möchte, muss sie diese Zeile zunächst sperren.

Wenn wir die Transaktion in diesem Zustand verlassen, sieht es so aus, als hätte sie die Tabelle nicht geändert, aber im Hintergrund hat sie mit der Tabelle interagiert und sie gesperrt, um diese Zeile bearbeiten zu können.

In diesem Fall, wenn wir uns von einer anderen Sitzung aus mit derselben Datenbank verbinden und versuchen, diese Zeile zu ändern, sehen wir Folgendes:

MariaDB [sbtest]> update sbtest1 set c='gx' where id=1;


ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Die Datenbank hat einen Standard-Wartezeitwert. Sie versucht zu warten, bis diese Zeit abgelaufen ist, und gibt dann einen Fehler aus. Aufgrund der von uns erstellten Transaktion hat sie versucht, diese Zeile zuerst zu ändern und sie daher gesperrt.

Wie können wir das lösen?

Wenn wir eine Transaktion starten, ausführen und sie dann alleine lassen [in einer Situation, in der die Verbindung nicht unterbrochen wird (d. h. durch Schließen der Befehlszeile oder Trennen)], bleibt diese Transaktion auf der Rückseite aktiv. Dies kann auch für Anwendungen der Fall sein. Wenn eine Anwendung stecken bleibt oder abstürzt, kann dies zu Idle-Transaktionen führen. Solange diese Transaktion aktiv ist, kann die Zeile der Tabelle, die sie sperrt, von keiner anderen Transaktion verwendet werden, die eine exklusive Sperre benötigt.

Wenn wir auf dieses Problem stoßen,
führen wir den Befehl "show processlist" aus, um unsere Prozesse anzuzeigen.

MariDB [sbtest]> show processlist;
+----+------+--------------------+--------+-------------+------+-----------------------------------------
| Id | User | Host               | db     | Command     | Time | State    | Info             | Progress |
+----+------+--------------------+--------+-------------+------+-----------------------------------------
|  7 | root | localhost          | sbtest | Sleep       |   55 |          | NULL             |    0.000 |
| 14 | repl | 10.244.18.77:40226 | NULL   | Binlog Dump |  829 | Master   | NULL             |    0.000 |
| 97 | root | localhost          | sbtest | Query       |    0 | starting | show processlist |    0.000 |

+----+------+--------------------+--------+-------------+------+----------------------------------------+
3 rows in set (0.001 sec)

Es scheint, dass es sich im Schlafmodus befindet und seit 55 Sekunden im Hintergrund läuft. 

Um dieses Problem zu lösen, müssen wir diesen Prozess mit dem 'kill'-Befehl beenden.

MariaDB [sbtest]> kill 7;
Query OK, 0 rows affected (0.001 sec)

Bevor wir den 'kill'-Befehl ausführen, starten wir den Befehl, um den Wert von "c" in der Zeile mit id=1 erneut zu ändern.

MariaDB [sbtest]> update sbtest1 set c='gx' where id=1;
Query OK, 1 row affected (12.546 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Wie Sie sehen können, konnte dieser Befehl zuerst nicht auf die Zeile in der Tabelle zugreifen, da unsere alte Transaktion im Hintergrund lief, und unsere Transaktion hier war aufgehalten. 

Nach einer Weile, als wir diese im Hintergrund laufende Transaktion beendet hatten, wurde diese Transaktion realisiert.

Wenn Sie MySQL oder MariaDB verwenden, können Sie dieses Problem auf folgende Weise lösen.

Wenn Sie den Percona Server verwenden, können Sie dieses Problem mit der Methode "Kill Idle Transactions" von Percona lösen.

In Percona ist standardmäßig ein Zeitwert für "innodb_kill_idle_transaction" festgelegt.

Wenn eine Transaktion diese Zeit überschreitet, wird sie automatisch zerstört. 

Sie können diesen Standardwert in der Konfigurationsdatei der Datenbank ändern und bearbeiten (typischerweise "my.cnf" oder "my.ini" genannt).

Außerdem ist "innodb_kill_idle_transaction" dynamisch. Sie können also auch diese Zeiteinstellung von der Befehlszeile aus ändern.

MariaDB [sbtest]> set global innodb_kill_idle_transaction=10;
ERROR 1193 (HY000): Unknown system variable 'innodb_kill_idle_transaction

Wie bereits erwähnt, handelt es sich hier um eine spezifische Situation für Percona.

Wenn wir versuchen, dies in MariaDB und MySQL zu testen, erhalten wir einen Fehler wie diesen. In Percona MySQL sehen wir jedoch, dass der Befehl set global innodb_kill_idle_transaction=10; funktioniert.
Sie können auf den Percona-Blogpost zugreifen, der detaillierte Informationen zu "innodb_kill_idle_transaction" enthält, hier.

Vielen Dank fürs Lesen! Haben Sie Probleme mit Ihrer Datenbank? Zögern Sie nicht, uns zu kontaktieren und besuchen Sie unsere Kontaktseite.