Detaillierte Erläuterung der MySQL-Lösung zur USE DB-Überlastung

Wenn wir auf einen Fehler stoßen, denken wir oft darüber nach, wie wir den Fehler beheben können, anstatt über die Grundursache des Fehlers nachzudenken. Dies führt dazu, dass wir zwar den Fisch gewinnen, aber die Fangmethode verlieren. Heute möchten wir einen Denkprozess beschreiben, der durch einen USE DB-Überlastungsfehler verursacht wurde.

Fehlerbeschreibung

Heute ist bei einem Freund ein schwerwiegender Datenbankfehler aufgetreten. Die Fehlerumgebung ist wie folgt:

MySQL 5.6.16

RR-Isolationsebene

GITD Schließen

Die Aufführung ist wie folgt:

Use db kann nicht auf die Datenbank zugreifen

Tabellenstatus anzeigen. Tabelleninformationen können nicht abgefragt werden.

Laut schema.processlist gibt es viele Warten auf Tabellenmetadatensperren

In seiner Verzweiflung hat er viele Threads gelöscht und festgestellt, dass er sie immer noch nicht wiederherstellen konnte. Schließlich hat er eine Transaktion gelöscht, die nicht rechtzeitig übermittelt wurde, um den Normalzustand wiederherzustellen. Es ist nur noch ein Screenshot wie unten gezeigt übrig:

Extraktion von Fehlerinformationen

Zurück zum obigen Bild können wir die Anweisungstypen wie folgt zusammenfassen:

1. ERSTELLEN SIE TABELLE A ALS SELECT B

Sein STATE sendet Daten

2. TABELLE A LÖSCHEN

Sein Status ist „Warten auf Sperre der Tabellenmetadaten“

3. Wählen Sie * aus A

Sein Status ist „Warten auf Sperre der Tabellenmetadaten“

4. TABELLENSTATUS ANZEIGEN [wie 'A']

Sein Status ist „Warten auf Sperre der Tabellenmetadaten“

Informationsanalyse

Dieser Fall ist nicht einfach zu analysieren, da es sich um eine Kombination aus MDL LOCK auf MySQL-Ebene und InnoDB-Zeilensperre im RR-Modus handelt und wir auf den ZUSTAND von schema.processlist achten müssen.

Es wird empfohlen, meine folgenden Artikel zu lesen, um MDL LOCK zu lernen:

https://www.jb51.net/article/131383.htm

In diesem Abschnitt werden die folgenden beiden Methoden zum Überprüfen von MDL LOCK verwendet:

Methode 1 : Ich habe der MDL LOCK-Quellcode-Sperrfunktion eine Protokollausgabe hinzugefügt. Wenn Sie die zu verschiedenen Anweisungen hinzugefügten MDL LOCK-Typen analysieren möchten, können Sie nur diese Methode verwenden, da MDL LOCK häufig aufblitzt und performance_schema.metadata_locks dies nicht beobachten kann.

Methode 2 : Verwenden Sie performance_schema.metadata_locks 5.7, um einen blockierten Zustand zu beobachten.

So öffnen Sie die MDL-Überwachung in P_S:

1. Analyse der gesendeten Daten der Tabelle B in CREATE TABLE A AS SELECT B

Der Status des Sendens von Daten kann tatsächlich viele Bedeutungen haben. Nach meinem derzeitigen Verständnis ist dies ein allgemeiner Begriff für Anweisungen vom Typ SELECT in der oberen MySQL-Schicht, wenn die INNODB-Schicht und die MySQL-Schicht miteinander interagieren. Daher kann er Folgendes aufweisen:

Die Datenmenge, auf die zugegriffen werden muss, ist sehr groß und muss möglicherweise optimiert werden.

Weil das Erhalten einer Zeilensperre auf der INNODB-Ebene eine Wartezeit erfordert, wie beispielsweise unser allgemeines SELECT FOR UPDATE.

Gleichzeitig müssen wir auch beachten, dass die Sperrmethode von SELECT B im RR-Modus mit der von INSERT ... SELECT übereinstimmt, daher werde ich sie hier nicht wiederholen:

Seiner Reaktion nach befand er sich in Situation 2, weil er am Ende eine lange bestehende, nicht festgeschriebene Transaktion abbrach. Und die gesamte Anweisung CREATE TABLE A AS SELECT B kann nicht abgerufen werden, da einige Datenbanken in Tabelle B gesperrt sind, wodurch sich die gesamte Anweisung im Status „Daten senden“ befindet.

2. Analyse von SHOW TABLE STATUS [wie 'A'] Warten auf Sperre der Tabellenmetadaten

Dies ist der wichtigste Teil dieses Falls. SHOW TABLE STATUS[like 'A'] ist blockiert. Sein STATUS ist „Warten auf Sperre der Tabellenmetadaten“. Beachten Sie, dass es sich hier um eine Tabelle handelt, da es viele Arten von MDL-Sperren gibt. In dem Artikel, in dem MDL vorgestellt wird, habe ich erwähnt, dass beim Beschreiben einer Tabelle MDL_SHARED_HIGH_PRIO(SH) angezeigt wird. Tatsächlich wird bei der Ausführung von SHOW TABLE STATUS für diese Tabelle auch MDL_SHARED_HIGH_PRIO(SH) angezeigt.

Methode 1

Methode 2

Beide Methoden können die Existenz von MDL_SHARED_HIGH_PRIO(SH) beobachten und ich habe eine Blockierungssituation simuliert.

MDL_SHARED_HIGH_PRIO (SH) ist jedoch ein MDL LOCK-Typ mit einer sehr hohen Priorität, wie unten gezeigt:

Kompatibilität:

Priorität der Sperrwarteschlange:

Außer der Blockierung durch MDL_EXCLUSIVE(X) gibt es keine andere mögliche Blockierungsbedingung. Dies ist also ein sehr wichtiger Durchbruch.

III. Analyse des Hinzufügens von MDL LOCK zu Tabelle A in CREATE TABLE A AS SELECT B

Das war mir vorher nicht bekannt und es ist auch der Teil, der in diesem Fall am meisten Zeit in Anspruch nimmt. Wie im vorherigen Artikel analysiert, gibt es nur eine Möglichkeit für eine Anweisung wie SHOW TABLE STATUS [like 'A'], die nur MDL_SHARED_HIGH_PRIO (SH) MDL LOCK setzt, das bei MDL LOCK blockiert werden soll, und zwar, dass Tabelle A MDL_EXCLUSIVE (X) gesetzt hat.

Dann begann ich zu vermuten, dass diese DDL-Anweisung MDL_EXCLUSIVE(X) auf Tabelle A vor dem Ende der Anweisung ausführen würde. Dann führte ich einen tatsächlichen Test durch und stellte fest, dass dies tatsächlich der Fall war, und zwar wie folgt:

Methode 1

Methode 2

Schade, dass MDL_EXCLUSIVE(X) in performance_schema.metadata_locks nicht angezeigt wird, aber MDL_SHARED(S) wird angezeigt. Wir können im von mir ausgegebenen Protokoll sehen, dass hier ein Upgrade-Vorgang durchgeführt wurde, um MDL_SHARED(S) auf MDL_EXCLUSIVE(X) zu aktualisieren. Und aus der vorherigen Kompatibilitätsliste blockiert nur MDL_EXCLUSIVE(X) MDL_SHARED_HIGH_PRIO(SH). Wir sollten also bestätigen können, dass der Upgrade-Vorgang hier tatsächlich durchgeführt wird, andernfalls wird SHOW TABLE STATUS[like 'A'] nicht blockiert.

4. Analyse von SELECT * FROM A Warten auf die Sperre der Tabellenmetadaten

Sie denken vielleicht, dass SELECT nicht gesperrt wird, aber das ist auf der InnoDB-Ebene der Fall. Auf der MySQL-Ebene wird MDL_SHARED_READ(SR) wie folgt festgelegt:

Methode 1

Methode 2

Es ist ersichtlich, dass MDL_SHARED_READ(SR) existiert und derzeit blockiert ist.

Die Kompatibilität ist wie folgt:

Offensichtlich sind MDL_SHARED_READ(SR) und MDL_SHARED_HIGH_PRIO(SH) inkompatibel und müssen warten.

5. Analyse von DROP TABLE A Warten auf die Sperre der Tabellenmetadaten

Dies lässt sich leicht analysieren, da Tabelle A über eine X-Sperre verfügt und DROP TABLE A über eine MDL_EXCLUSIVE(X)-Sperre verfügen muss, die natürlich mit MDL_EXCLUSIVE(X) inkompatibel ist. wie folgt:

Methode 1

Methode 2

Unter ihnen ist EXCLUSIVE das, was wir MDL_EXCLUSIVE(X) nennen. Es existiert und ist derzeit blockiert.

6. Warum wird auch die Verwendung von DB blockiert?

Wenn Sie den MySQL-Client ohne die Option -A (oder no-auto-rehash) verwenden, müssen Sie bei der Verwendung von USE DB mindestens Folgendes tun:

1. Die MDL (SH)-Sperre für jede Tabelle in der Datenbank erfolgt wie folgt (rufen Sie MDL_context::acquire_lock auf, um die Information zu erhalten, wenn die Sperre auftritt)

Methode 1

Methode 2

Es ist ersichtlich, dass USE DB aufgrund von MDL_SHARED_HIGH_PRIO(SH) tatsächlich blockiert ist.

2. Fügen Sie jede Tabelle zum Tabellencache hinzu und öffnen Sie die Tabelle (rufen Sie open_table_from_share() auf).

Dann ist diese Situation genau dieselbe wie die Situation, in der SHOW TABLE STATUS [wie 'A'] blockiert ist, was ebenfalls durch eine Inkompatibilität der MDL-Sperre verursacht wird.

Analyse und Sortierung

Mit der vorherigen Analyse können wir die Ursachen für diesen Fehler wie folgt herausfinden:

Es gibt einen seit langem nicht festgeschriebenen DML-Datensatz auf Tabelle B.
Die Anweisung fügt bestimmten Daten in Tabelle B auf der InnoDB-Ebene eine InnoDB-Zeilensperre hinzu.

Schritt 1 führt zur Blockierung von CREATE TABLE A AS SELECT B <br /> Weil SELECT B im RR-Modus die Daten in Tabelle B sperren muss und da Schritt 1 die Daten gesperrt hat, wird die Wartezeit ausgelöst und STATE sendet Daten.

Schritt 2 führt dazu, dass andere Anweisungen blockiert werden <br /> Da CRATE TABLE A AS SELECT B MDL_EXCLUSIVE(X) hält, bevor Tabelle A erstellt wird, blockiert diese Sperre alle anderen Anweisungen in Bezug auf Tabelle A, einschließlich DESC/SHOW TABLE STATUS/USE DB (nicht-A)-Anweisungen, die nur MDL_SHARED_HIGH_PRIO(SH)MDL LOCK halten. Der ZUSTAND wird vereinheitlicht als „Warten auf Sperre der Tabellenmetadaten“.

Simulationstest

Testumgebung:

5.7.14

GITD Schließen

RR-Isolationsebene

Verwenden Sie das Skript:

Hier sind die Schritte:

Sitzung1