Lösen Sie das Problem der blockierenden Positionierungs-DDL in MySQL 5.7

Lösen Sie das Problem der blockierenden Positionierungs-DDL in MySQL 5.7

Im vorherigen Artikel „Änderungen der MySQL-Tabellenstruktur, Sie müssen die Metadatensperre kennen“ haben wir den Hintergrund und die grundlegenden Konzepte der Einführung von MDL vorgestellt und auf der „Tao“-Ebene gelernt, was MDL ist. Sehen wir uns als Nächstes an, wie MDL-bezogene Probleme aus einer „technischen“ Perspektive identifiziert werden können.

In MySQL 5.7 wird für MDL eine neue Tabelle performance_schema.metadata_locks eingeführt. Diese Tabelle kann relevante Informationen zu MDL anzeigen, einschließlich Objekt, Typ und Wartestatus.

MDL-Instrumentierung aktivieren

Das entsprechende Instrument ist allerdings nicht aktiviert (MySQL 8.0 ist standardmäßig aktiviert). Es kann auf zwei Arten aktiviert werden:

Vorübergehender Effekt

performance_schema.setup_instrume nts表wird geändert, nach dem Neustart der Instanz wird sie jedoch auf den Standardwert zurückgesetzt.

UPDATE performance_schema.setup_instruments SET ENABLED = "JA", TIMED = "JA"
WO NAME = 'warten/sperren/Metadaten/SQL/MDL';

Dauerhaft

In der Konfigurationsdatei festlegen

[mysqld]
Leistungsschemainstrument = "Warten/Sperren/Metadaten/SQL/MDL = EIN"

Testszenario

Nachfolgend finden Sie eine einfache Demo, die zeigt, wie das Blockierungsproblem von DDL-Operationen in MySQL 5.7 lokalisiert werden kann.

Sitzung1> beginnen;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)
Sitzung1> löschen aus slowtech.t1, wo ID=2;
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)
Sitzung1> wähle * aus slowtech.t1;
+------+------+
| Ich würde | Name |
+------+------+
| 1 | ein |
+------+------+
1 Zeile im Satz (0,00 Sek.)
Sitzung1> Update slowtech.t1, setze Name='c', wobei ID=1;
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)
Übereinstimmende Zeilen: 1 Geändert: 1 Warnungen: 0
Sitzung2> Tabelle slowtech.t1 ändern, c1 int hinzufügen; ##Blockiert Sitzung3> Prozessliste anzeigen;
+----+------+-----------+---------+---------+------+---------------------------------+------------------------------------+
| ID | Benutzer | Host | db | Befehl | Zeit | Status | Info |
+----+------+-----------+---------+---------+------+---------------------------------+------------------------------------+
| 2 | root | localhost | NULL | Ruhezustand | 51 | | NULL |
| 3 | root | localhost | NULL | Abfrage | 0 | wird gestartet | Prozessliste anzeigen |
| 4 | root | localhost | NULL | Abfrage | 9 | Warte auf Sperre der Tabellenmetadaten | alter table slowtech.t1 add c1 int |
+----+------+-----------+---------+---------+------+---------------------------------+------------------------------------+
3 Zeilen im Satz (0,00 Sek.)
Sitzung3> wähle Objekttyp, Objektschema, Objektname, Sperrtyp, Sperrdauer, Sperrstatus, Besitzer-Thread-ID aus Leistungsschema.Metadata_Locks;
+-------------+--------------------+----------------+---------------------+---------------+-------------+----------------+
| Objekttyp | Objektschema | Objektname | Sperrtyp | Sperrdauer | Sperrstatus | Besitzer-Thread-ID |
+-------------+--------------------+----------------+---------------------+---------------+-------------+----------------+
| TABELLE | slowtech | t1 | SHARED_WRITE | TRANSAKTION | GEWÄHRT | 27 |
| GLOBAL | NULL | NULL | INTENTION_EXCLUSIVE | STATEMENT | GEWÄHRT | 29 |
| SCHEMA | slowtech | NULL | INTENTION_EXCLUSIVE | TRANSACTION | GEWÄHRT | 29 |
| TABELLE | slowtech | t1 | SHARED_UPGRADABLE | TRANSAKTION | GEWÄHRT | 29 |
| TABELLE | slowtech | t1 | EXKLUSIV | TRANSAKTION | AUSSTEHEND | 29 |
| TABELLE | Leistungsschema | Metadatensperren | SHARED_READ | TRANSAKTION | GEWÄHRT | 28 |
+-------------+--------------------+----------------+---------------------+---------------+-------------+----------------+
6 Zeilen im Satz (0,00 Sek.)

Konzentrieren Sie sich hier auf den Sperrstatus. „PENDING“ bedeutet, dass der Thread auf MDL wartet, und „GRANTED“ bedeutet, dass der Thread MDL hält.

So finden Sie die Sitzung, die die Blockierung verursacht

In Kombination mit owner_thread_id können wir sehen, dass Thread 29 auf das MDL von Thread 27 wartet. Zu diesem Zeitpunkt kann Thread 52 beendet werden.

Es ist jedoch zu beachten, dass owner_thread_id nur die Thread-ID angibt, nicht die ID in der angezeigten Prozessliste. Wenn Sie die einem Thread entsprechende Prozesslisten-ID finden möchten, müssen Sie die Tabelle performance_schema.threads abfragen.

Sitzung3> wähle * aus performance_schema.threads, wobei thread_id in (27,29)\G
*************************** 1. Reihe ***************************
   THREAD_ID: 27
    NAME: Thread/SQL/eine_Verbindung
    TYP: VORDERGRUND
  PROCESSLIST_ID: 2
 PROCESSLIST_USER: root
 PROCESSLIST_HOST: lokaler Host
  PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Schlafen
 PROCESSLIST_TIME: 214
 PROCESSLIST_STATE: NULL
 PROCESSLIST_INFO: NULL
 ÜBERGEORDNETE THREAD-ID: 1
    ROLLE: NULL
  INSTRUMENTIERT: JA
   GESCHICHTE: JA
 VERBINDUNGSTYP: Socket
  THREAD_OS_ID: 9800
*************************** 2. Reihe ***************************
   THREAD_ID: 29
    NAME: Thread/SQL/eine_Verbindung
    TYP: VORDERGRUND
  PROCESSLIST_ID: 4
 PROCESSLIST_USER: root
 PROCESSLIST_HOST: lokaler Host
  PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Abfrage
 PROCESSLIST_TIME: 172
 PROCESSLIST_STATE: Warten auf Sperre der Tabellenmetadaten
 PROCESSLIST_INFO: Tabelle „slowtech.t1“ ändern, c1 int hinzufügen
 ÜBERGEORDNETE THREAD-ID: 1
    ROLLE: NULL
  INSTRUMENTIERT: JA
   GESCHICHTE: JA
 VERBINDUNGSTYP: Socket
  THREAD_OS_ID: 9907
2 Zeilen im Satz (0,00 Sek.)

Indem wir diese beiden Tabellen kombinieren und auf die Ausgabe von sys.innodb_lock_waits verweisen, können wir die Wartebeziehung von MDL tatsächlich intuitiv darstellen.

WÄHLEN
 a.OBJECT_SCHEMA AS gesperrtes_schema,
 a.OBJECT_NAME AS gesperrte_Tabelle,
 "Metadatensperre" AS locked_type,
 c.PROCESSLIST_ID AS wartende_Prozesslisten-ID,
 c.PROCESSLIST_TIME AS Wartezeit,
 c.PROCESSLIST_INFO AS warte_abfrage,
 c.PROCESSLIST_STATE AS Wartezustand,
 d.PROCESSLIST_ID AS blockierende_Prozesslisten-ID,
 d.PROCESSLIST_TIME AS Sperralter,
 d.PROCESSLIST_INFO AS blockierende_Abfrage,
 concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
AUS
 performance_schema.metadata_locks ein
JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
UND a.OBJEKTNAME = b.OBJEKTNAME
UND a.lock_status = 'AUSSTEHEND'
UND b.lock_status = 'GEWÄHRT'
UND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
UND a.lock_type = 'EXKLUSIV'
JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID\G

*************************** 1. Reihe ***************************
    gesperrtes_schema: slowtech
    gesperrte_Tabelle: t1
     locked_type: Metadatensperre
  warte_prozesslisten-id: 4
     Wartealter: 259
    waiting_query: Tabelle slowtech.t1 ändern, c1 int hinzufügen
    waiting_state: Wartet auf die Sperre der Tabellenmetadaten
  Blockierungsprozesslisten-ID: 2
    Sperralter: 301
    Blockierungsabfrage: NULL
sql_kill_blocking_connection: KILL 2
1 Zeile im Satz (0,00 Sek.)

Die Ausgabe ist auf den ersten Blick klar. Wenn Sie MDL für DDL-Operationen erhalten möchten, führen Sie kill 2 aus.

Offizielles sys.schematablelock_waits

Tatsächlich integriert MySQL 5.7 ähnliche Funktionen in die sys-Bibliothek. Im gleichen Szenario lautet die Ausgabe wie folgt:

mysql> wähle * aus sys.schema_table_lock_waits\G
*************************** 1. Reihe ***************************
    Objektschema: slowtech
     Objektname: t1
   wartende_thread_id: 29
     warte_pid: 4
    Wartekonto: root@localhost
   waiting_lock_type: EXKLUSIV
  Wartezeit_Sperrdauer: TRANSAKTION
    waiting_query: Tabelle slowtech.t1 ändern, c1 int hinzufügen
   Wartezeit_Abfrage_Sekunden: 446
 waiting_query_rows_affected: 0
 Wartende Abfragezeilen geprüft: 0
   Blockierungsthread-ID: 27
    Sperr-PID: 2
   Sperrkonto: root@localhost
   Sperrtyp: SHARED_READ
  blocking_lock_duration: TRANSAKTION
  sql_kill_blocking_query: KILL-ABFRAGE 2
sql_kill_blocking_connection: KILL 2
*************************** 2. Reihe ***************************
    Objektschema: slowtech
     Objektname: t1
   wartende_Thread_ID: 29
     warte_pid: 4
    Wartekonto: root@localhost
   waiting_lock_type: EXKLUSIV
  Wartezeit_Sperrdauer: TRANSAKTION
    waiting_query: Tabelle slowtech.t1 ändern, c1 int hinzufügen
   Wartezeit_Abfrage_Sekunden: 446
 waiting_query_rows_affected: 0
 Wartende Abfragezeilen geprüft: 0
   Blockierungsthread-ID: 29
    Blockierungs-PID: 4
   Sperrkonto: root@localhost
   Sperrtyp: SHARED_UPGRADABLE
  blocking_lock_duration: TRANSAKTION
  sql_kill_blocking_query: KILL-ABFRAGE 4
sql_kill_blocking_connection: KILL 4
2 Zeilen im Satz (0,00 Sek.)

Analysieren Sie insbesondere die offiziellen Ergebnisse,

Es gibt nur eine Änderungstabellenoperation, aber es werden zwei Datensätze generiert, und die Kill-Objekte der beiden Datensätze sind unterschiedlich. Wenn Sie mit der Tabellenstruktur nicht vertraut sind und den Datensatzinhalt nicht sorgfältig lesen, werden Sie zwangsläufig das falsche Objekt killen.

Wenn außerdem N Abfragen durch DDL-Operationen blockiert werden, werden N*2 Datensätze generiert. Bei vielen Blockierungsvorgängen bestehen diese N*2-Datensätze ausschließlich aus Rauschen.

Im vorherigen SQL gab eine „Alter Table“-Operation immer nur einen Datensatz aus, unabhängig davon, wie viele Operationen blockiert waren.

So zeigen Sie die Vorgänge an, die von der blockierenden Sitzung ausgeführt wurden

Aber das obige SQL hat auch einen Nachteil: seine blockierende Abfrage ist NULL und in Sitzung 1 hat es eindeutig drei SQLs ausgeführt.

Dies hängt mit performance_schema.threads zusammen (ähnlich wie show processlist), das nur das aktuell ausgeführte SQL ausgibt. Es ist tatsächlich unmöglich, das bereits ausgeführte SQL anzuzeigen.

Doch online ist das Töten eine Operation, die Vorsicht erfordert. Schließlich lässt sich nur schwer erkennen, ob es sich bei der Tötung um eine geschäftskritische Operation handelt. Oder handelt es sich um einen Batch-Updatevorgang? Gibt es also eine Möglichkeit, die Vorgänge vor der Transaktion zu erfassen?

Die Antwort ist ja.

Dies ist die Tabelle im Leistungsschema, in der Anweisungsereignisse (Operationsereignisse) aufgezeichnet werden, einschließlich

events_statements_current, events_statements_history, events_statements_history_long, vorbereitete_statements_Instanzen.

Die ersten drei werden häufig verwendet.

Die Tabellenstrukturen der drei sind genau gleich. Unter ihnen enthält events_statements_history auch die Operationen von events_statements_current, daher verwenden wir hier events_statements_history.

Das endgültige SQL lautet wie folgt:

WÄHLEN
 gesperrtes_schema,
 gesperrte_Tabelle,
 gesperrter Typ,
 warte_prozesslisten_id,
 Wartealter,
 warte_abfrage,
 Wartezustand,
 Blockierungsprozesslisten-ID,
 Sperralter,
 Teilzeichenfolgenindex (SQL-Text, "Transaktionsbeginn"; , -1) als Sperrabfrage,
 sql_kill_blockierende_Verbindung
AUS
 (
  WÄHLEN
   b.OWNER_THREAD_ID AS gewährte Thread-ID,
   a.OBJECT_SCHEMA AS gesperrtes_schema,
   a.OBJECT_NAME AS gesperrte_Tabelle,
   "Metadatensperre" AS locked_type,
   c.PROCESSLIST_ID AS wartende_Prozesslisten-ID,
   c.PROCESSLIST_TIME AS Wartezeit,
   c.PROCESSLIST_INFO AS warte_abfrage,
   c.PROCESSLIST_STATE AS Wartezustand,
   d.PROCESSLIST_ID AS blockierende_Prozesslisten-ID,
   d.PROCESSLIST_TIME AS Sperralter,
   d.PROCESSLIST_INFO AS blockierende_Abfrage,
   concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
  AUS
   performance_schema.metadata_locks ein
  JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
  UND a.OBJEKTNAME = b.OBJEKTNAME
  UND a.lock_status = 'AUSSTEHEND'
  UND b.lock_status = 'GEWÄHRT'
  UND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
  UND a.lock_type = 'EXKLUSIV'
  JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
  JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID
 ) t1,
 (
  WÄHLEN
   Thread-ID,
   group_concat( CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text
  AUS
   performance_schema.events_statements_history
  GRUPPE NACH Thread-ID
 ) t2
WO
 t1.zuerkannte_thread_id = t2.thread_id \G
*************************** 1. Reihe ***************************
    gesperrtes_schema: slowtech
    gesperrte_Tabelle: t1
     locked_type: Metadatensperre
  warte_prozesslisten-id: 4
     Wartealter: 294
    waiting_query: Tabelle slowtech.t1 ändern, c1 int hinzufügen
    waiting_state: Wartet auf Sperre der Tabellenmetadaten
  Blockierungsprozesslisten-ID: 2
    Sperralter: 336
    Blockierungsabfrage: Löschen aus slowtech.t1, wobei ID=2; Auswählen * aus slowtech.t1; Aktualisieren von slowtech.t1, Festlegen des Namens='c', wobei ID=1
sql_kill_blocking_connection: KILL 2
1 Zeile im Satz, 1 Warnung (0,00 Sek.)

Aus der obigen Ausgabe können wir erkennen, dass blocking_query alle Operationen der aktuellen Transaktion in Sitzung 1 enthält, die in der Reihenfolge ausgegeben werden, in der sie ausgeführt werden.

Es ist zu beachten, dass events_statements_history standardmäßig nur die 10 letzten Vorgänge jedes Threads speichert. Wenn eine Transaktion viele Vorgänge enthält, ist es tatsächlich unmöglich, sie alle zu erfassen.

Zusammenfassen

Oben habe ich Ihnen erklärt, wie Sie das Problem der blockierten DDL in MySQL 5.7 finden. Ich hoffe, es wird Ihnen helfen. Wenn Sie Fragen haben, hinterlassen Sie mir bitte eine Nachricht und ich werde Ihnen rechtzeitig antworten. Ich möchte auch allen für ihre Unterstützung der Website 123WORDPRESS.COM danken!

Das könnte Sie auch interessieren:
  • So fügen Sie in MySQL 8.0 schnell Spalten hinzu
  • Detaillierte Erklärung zur Verwendung von MySQL Online DDL
  • So beheben Sie die durch MySQL DDL verursachte Synchronisierungsverzögerung
  • Detaillierte Erklärung der atomaren DDL-Syntax von MySQL 8.0
  • MySQL Online-DDL-Tool Gh-Ost-Prinzipanalyse
  • Verwendung von MySQL DDL-Anweisungen
  • Zusammenfassung gängiger MySQL-DDL-Operationen
  • Analyse der neuen Funktionen von MySQL 8.0 - Transaktionales Datenwörterbuch und Atomic DDL
  • Grundlegende Anweisungen der MySQL-Datendefinitionssprache DDL
  • MySQL 8.0 DDL-Atomaritätsfunktion und Implementierungsprinzip
  • Zusammenfassung der Verwendung von MySQL Online DDL gh-ost
  • Neue Funktionen in MySQL 8.0: Unterstützung für atomare DDL-Anweisungen
  • MySQL weist eine Riddle-Sicherheitslücke auf, die zum Verlust von Benutzernamen und Passwörtern führen kann
  • Zusammenfassung der schnellen Spaltenaddition bei MySQL 8.0 Online DDL

<<:  Detaillierte Erklärung, wie Node.js mit ES6-Modulen umgeht

>>:  Allgemeine Textverarbeitungsbefehle unter Linux und Vim-Texteditor

Artikel empfehlen

So fragen Sie die neueste Transaktions-ID in MySQL ab

Vorne geschrieben: Manchmal müssen Sie möglicherw...

Win10 MySQL 5.6.35 Winx64 kostenlose Installationsversion Konfigurations-Tutorial

Tutorial zur kostenlosen Konfiguration der mysql ...

Detaillierte Erklärung des JavaScript-Statuscontainers Redux

Inhaltsverzeichnis 1. Warum Redux 2. Redux-Datenf...

Vergleichende Analyse von MySQL Binlog-Protokollverarbeitungstools

Inhaltsverzeichnis Kanal Maxwell Datenbus Datenüb...

Tipps zum Anzeigen von Verlaufsdatensätzen und Hinzufügen von Zeitstempeln in Linux

Tipps zum Anzeigen von Verlaufsdatensätzen und Hi...

15 wichtige Variablen, die Sie zur MySQL-Leistungsoptimierung kennen müssen (Zusammenfassung)

Vorwort: MYSQL dürfte die beliebteste WEB-Backend...

Detaillierte Erklärung von Softlinks und Hardlinks in Linux

Inhaltsverzeichnis 1. Grundlegende Speicherung vo...

Java-Beispielcode zum Generieren von zufälligen Zeichen

Beispielcode: importiere java.util.Random; import...

So fragen Sie Daten innerhalb eines bestimmten Zeitraums mit dem Vue-Frontend und dem Django-Backend ab

Vorwort Während des Entwicklungsprozesses werden ...

So verkleinern Sie das Stammverzeichnis des XFS-Partitionsformats in Linux

Inhaltsverzeichnis Vorwort Systemumgebung Aktuell...

Verwenden Sie Vue3 zur Datenbindung und Anzeigelistendaten

Inhaltsverzeichnis 1. Vergleich mit Vue2 1. Neue ...

Bringen Sie Ihnen bei, wie Sie eine Reaktion aus HTML implementieren

Was ist React React ist eine einfache JavaScript-...

So lösen Sie das Phantomleseproblem in MySQL

Inhaltsverzeichnis Vorwort 1. Was ist Phantomlese...

So installieren Sie OpenJDK in Docker und führen das JAR-Paket aus

Bild herunterladen Docker-Pull OpenJDK Erstellen ...

Implementierung eines einfachen Rechners mit Javascript

In diesem Artikelbeispiel wird der spezifische Ja...