MySQL-Deadlock- und Protokollanalyse

In diesem Artikel geht es hauptsächlich um MySQL-Deadlocks und -Protokolle. Wie kann man MySQL-Probleme im Internet schnell lokalisieren und Ausnahmen im tatsächlichen Geschäft beheben? Dieser Artikel teilt relevante Erfahrungen und Methoden basierend auf zwei tatsächlichen Fällen. Ich hoffe, dass er allen helfen kann.

In letzter Zeit sind in Online-MySQL mehrere Datenanomalien aufgetreten, die alle am frühen Morgen auftraten. Da es sich bei dem Geschäftsszenario um eine typische Data-Warehouse-Anwendung handelt, ist der Druck tagsüber gering und kann nicht reproduziert werden. Einige der Anomalien sind sogar seltsam und die endgültige Ursachenanalyse ist ziemlich schwierig. Wie können wir also Online-MySQL-Probleme schnell lokalisieren und Ausnahmen im tatsächlichen Geschäft beheben? Im Folgenden werde ich relevante Erfahrungen und Methoden basierend auf zwei tatsächlichen Fällen teilen.

Fall 1: Ein Teil der Datenaktualisierung ist fehlgeschlagen

Eines Tages berichteten Kanalklassenkameraden, dass nur sehr wenige Kanaldaten vorhanden seien Ein bestimmter Bericht war 0. Die meisten Kanaldaten sind normal. Diese Daten werden regelmäßig jeden frühen Morgen von einem Statistikprogramm aktualisiert. Es liegt auf der Hand, dass entweder alles normal ist oder alles fehlschlägt. Was könnte also der Grund für die Abweichung einiger einzelner Daten sein?

Das erste, woran wir denken können, ist, einen Blick auf das Protokoll der Statistikaufgabe zu werfen. Nachdem wir uns jedoch das vom Statistikprogramm gedruckte Protokoll angesehen hatten, konnten wir keine ungewöhnliche Beschreibung wie einen SQL-Aktualisierungsfehler finden . Was genau geschah damals in der Datenbank? Bevor ich das MySQL-Serverprotokoll überprüfte, schaute ich mir regelmäßig den Datenbankstatus an:

Ich habe am frühen Morgen zufällig einen Deadlock in diesem Update festgestellt:

Aus Platzgründen habe ich hier viel Kontext weggelassen. Wie Sie diesem Protokoll entnehmen können, halten TRANSACTION 1 und TRANSACTION 2 jeweils eine bestimmte Anzahl von Zeilensperren und warten dann Die Sperre der anderen Partei hat schließlich einen Deadlock erkannt und sich dann für ein Rollback entschieden. TRANSAKTION 1: Innodbs aktuelle Methode zur Behandlung von Deadlocks besteht darin, die Transaktion mit der geringsten exklusiven Sperre auf Zeilenebene zurückzusetzen.

Dann gibt es hier 3 Fragen:

1. Sperrt die Innodb-Zeilensperre nicht nur eine Zeile?

Da diese Tabelle von der Innodb-Engine stammt, unterstützt InnoDB Zeilensperren und Tabellensperren. InnoDB-Zeilensperren werden durch Sperren von Indexeinträgen im Index implementiert. Dies unterscheidet sich von MySQL und Oracle, bei denen die entsprechenden Datenzeilen im Datenblock gesperrt werden. Die Zeilensperren-Implementierungsfunktion von InnoDB bedeutet, dass InnoDB Sperren auf Zeilenebene nur verwendet, wenn Daten über Indexbedingungen abgerufen werden. Andernfalls verwendet InnoDB Tabellensperren und sperrt alle gescannten Zeilen! In praktischen Anwendungen sollte dieser Funktion der InnoDB-Zeilensperre besondere Aufmerksamkeit gewidmet werden, da sie sonst zu einer großen Anzahl von Sperrkonflikten führen und somit die Parallelitätsleistung beeinträchtigen kann. Da die Zeilensperre von MySQL eine Sperre für den Index und nicht für den Datensatz ist, kommt es bei Verwendung desselben Indexschlüssels zu einem Sperrkonflikt, obwohl auf Datensätze verschiedener Zeilen zugegriffen wird. Wenn wir Bereichsbedingungen anstelle von Gleichheitsbedingungen verwenden, um Daten abzurufen und gemeinsame oder exklusive Sperren anzufordern, sperrt InnoDB die Indexeinträge vorhandener Datensätze, die die Bedingungen erfüllen. Außerdem werden durch Lückensperren nicht nur mehrere Zeilen gesperrt Wenn Sie beim Sperren gleiche Bedingungen verwenden, um die Sperrung eines nicht vorhandenen Datensatzes anzufordern, verwendet InnoDB auch Lückensperren!

Nachdem wir das gesagt haben, werfen wir einen Blick auf den Index unserer Geschäftstabelle:

Sie können sehen, dass der Index dieser Tabelle äußerst unvernünftig ist: Es gibt 3 Indizes, aber das Update nutzt den Index nicht vollständig. Daher verwendet das Update den Index nicht genau und muss mehrzeilige Bereichsdaten sperren, was zu einem Deadlock führt.

Nachdem wir das Prinzip kennen, können wir sorgfältig einen kombinierten Index mit vier Feldern erstellen, damit die Aktualisierung den Innodb-Index genau verwenden kann. Nachdem wir den Index aktualisiert haben, ist dieses Deadlock-Problem tatsächlich gelöst.

Hinweis: innodb druckt nicht nur die Transaktionen und Sperren aus, die von den Transaktionen gehalten und abgewartet werden, sondern auch die Datensätze selbst, die leider Es kann sein, dass die von innodb für das Ausgabeergebnis reservierte Länge überschritten wird (nur 1 Mio. Inhalt kann gedruckt werden und nur die neuesten Deadlock-Informationen können beibehalten werden. Wenn Sie die vollständige Ausgabe nicht sehen können, können Sie unter einer beliebigen Bibliothek eine innodb_monitor- oder innodb_lock_monitor-Tabelle erstellen). Diesmal werden die Innodb-Statusinformationen alle 15 Sekunden vollständig und im Fehlerprotokoll aufgezeichnet. Beispiel: Erstellen Sie die Tabelle innodb_monitor(a int)engine=innodb;, löschen Sie die Tabelle einfach, wenn keine Notwendigkeit besteht, sie im Fehlerprotokoll aufzuzeichnen.

2. Warum schlagen beim Rollback nur einige Update-Anweisungen fehl?

Warum schlagen beim Rollback nur einige Update-Anweisungen fehl?

Das liegt daran, dass unser Innodb standardmäßig automatisch übermittelt wird:

Im Falle mehrerer Update- oder Insert-Anweisungen führt innodb nach der Ausführung jeder SQL sofort einen Commit durch, um die Änderungen beizubehalten und gleichzeitig die Sperre aufzuheben. Aus diesem Grund gibt es in diesem Beispiel nur wenige Anweisungen nach der Deadlock-Rollback-Transaktion.

Es ist zu beachten, dass es normalerweise eine andere Situation gibt, die ebenfalls dazu führen kann, dass einige Anweisungen zurückgesetzt werden, was besondere Aufmerksamkeit erfordert. In innodb gibt es einen Parameter namens: innodb_rollback_on_timeout

Das offizielle Handbuch beschreibt dies:

In MySQL 5.1 führt InnoDB nur ein Rollback durch die letzte Anweisung zu einem Transaktionszeitlimit. Wenn –innodb_rollback_on_timeout angegeben ist, führt ein Transaktionszeitlimit dazu, dass InnoDB die gesamte Transaktion abbricht und zurücksetzt (das gleiche Verhalten wie in MySQL 4.1).

Erklärung: Wenn dieser Parameter deaktiviert ist oder nicht vorhanden ist, wird nur die letzte Abfrage der Transaktion zurückgesetzt, wenn eine Zeitüberschreitung auftritt. Wenn er aktiviert ist, wird die gesamte Transaktion zurückgesetzt stößt auf eine Zeitüberschreitung.

3. Wie kann die Wahrscheinlichkeit eines Innodb-Deadlocks verringert werden?

Deadlocks lassen sich in Zeilensperr- und Transaktionsszenarien nur schwer vollständig beseitigen, aber Sperrkonflikte und Deadlocks können durch Tabellendesign und SQL-Anpassung reduziert werden, einschließlich:

So viel wie möglich verwenden Niedrigere Isolationsstufe: Wenn beispielsweise eine Lückensperre auftritt, können Sie die Transaktionsisolationsstufe der Sitzung oder Transaktion auf RC-Ebene (Read Committed) ändern, um dies zu vermeiden. Zu diesem Zeitpunkt müssen Sie binlog_format jedoch auf „row“ oder „mixed“ festlegen Format

Entwerfen Sie Indizes sorgfältig und versuchen Sie, Indizes für den Zugriff auf Daten zu verwenden, um das Sperren präziser zu gestalten und dadurch die Wahrscheinlichkeit von Sperrkonflikten zu verringern.

Wählen Sie eine angemessene Transaktionsgröße und die Wahrscheinlichkeit von Sperrkonflikten Bei kleinen Transaktionen wird es kleiner sein;

Wenn Sie den Datensatz sperren, ist es am besten, gleichzeitig eine ausreichende Sperrstufe anzufordern. Wenn Sie beispielsweise Daten ändern möchten, beantragen Sie am besten direkt eine exklusive Sperre, anstatt zuerst eine gemeinsame Sperre zu beantragen, und fordern Sie dann beim Ändern eine exklusive Sperre an.

Wann Verschiedene Programme greifen auf eine Gruppe von Tabellen zu. Es wird vereinbart, dass auf jede Tabelle in der gleichen Reihenfolge zugegriffen wird. Bei einer Tabelle sollte der Zugriff auf die Zeilen in der Tabelle möglichst in einer festen Reihenfolge erfolgen. Dies kann die Wahrscheinlichkeit eines Deadlocks erheblich verringern.

Versuchen Sie, gleiche Bedingungen für den Zugriff auf Daten zu verwenden, um die Auswirkungen von Lückensperren auf das gleichzeitige Einfügen zu vermeiden.

Beantragen Sie keine Sperrstufe das übersteigt den tatsächlichen Bedarf; Sperren werden bei Abfragen nicht angezeigt;

Für einige bestimmte Transaktionen können Tabellensperren verwendet werden, um die Verarbeitungsgeschwindigkeit zu erhöhen oder die Möglichkeit eines Deadlocks zu verringern.

Fall 2: Wartezeit bei seltsamem Schloss

Es gibt 6 Uhr morgens und 8 Uhr morgens für mehrere aufeinanderfolgende Tage. Eine Aufgabe Beim Laden der lokalen Infile-Daten wurde eine Java-SQL-Ausnahme von „Sperrwartezeitüberschreitung überschritten“ gemeldet. Nach der Kommunikation mit Klassenkameraden auf der Plattform stellten wir fest, dass dies ein Problem mit einer zu kurzen Sperrzeit oder einem Sperrkonflikt in unserem System war eigene Unternehmensdatenbank. Aber wenn Sie zurückdenken, sollten Sie das nicht auch tun? Ist das nicht immer gut? Darüber hinaus handelt es sich grundsätzlich um Einzelaufgaben, und es gibt keinen Konflikt zwischen mehreren Personen.

Egal, um wessen Problem es sich handelt, prüfen wir zunächst, ob ein Problem mit unserer Datenbank vorliegt:

Das standardmäßige Sperrzeitlimit beträgt 50 Sekunden Wirklich nicht kurz. Es ist wahrscheinlich sinnlos, es wie ein totes Pferd zu versuchen. . .

Und dieses Mal zeigte SHOW ENGINE INNODB STATUSG keine Deadlock-Informationen an. Dann wandte ich meine Aufmerksamkeit dem MySQL-Server-Protokoll zu und hoffte, anhand des Protokolls zu sehen, welche Vorgänge die Daten vor und nach diesem Moment ausgeführt haben. Hier ist eine kurze Einführung in die Zusammensetzung des MySQL-Protokolldateisystems:

(a) Fehlerprotokoll: Zeichnet Probleme auf, die beim Starten, Ausführen oder Stoppen von mysqld auftreten, standardmäßig aktiviert.
(b) Allgemeines Protokoll: Allgemeines Abfrageprotokoll, das alle Anweisungen und Anweisungen aufzeichnet. Beim Öffnen der Datenbank kommt es zu einem Leistungsverlust von ca. 5 %.
(c) Binlog-Protokoll: Binärformat, zeichnet alle Anweisungen auf, die Daten ändern, wird hauptsächlich für die Slave-Replikation und Datenwiederherstellung verwendet.
(d) Langsames Protokoll: Zeichnet alle Abfragen auf, deren Ausführung länger als long_query_time Sekunden dauert, oder Abfragen, die keine Indizes verwenden. Es ist standardmäßig deaktiviert.
(e) Innodb-Protokoll: Innodb-Redo-Protokoll, Rückgängig-Protokoll, wird zum Wiederherstellen von Daten und zum Rückgängigmachen von Vorgängen verwendet.

Wie Sie der obigen Einführung entnehmen können, befinden sich die aktuellen Protokolle für dieses Problem möglicherweise in d und b. Wenn in d kein Protokoll vorhanden ist, können Sie nur b aktivieren, aber b wird einen gewissen Verlust haben auf die Leistung der Datenbank, da es sich um ein vollständiges Protokoll handelt, ist das Volumen sehr groß, daher müssen Sie beim Öffnen vorsichtig sein:

Ich habe nur das vollständige Protokoll geöffnet loggen Sie sich jeden Tag eine halbe Stunde vor und nach dem Auftreten des Problems ein und finden Sie keine MySQL-Client-Anfrage an unsere Geschäftsdatenbank! Das Protokollformat ist wie folgt und zeichnet alle Verbindungen und Befehle auf:

Das Problem wurde im Grunde bestätigt, bevor die Client-Anfrage unsere Seite erreichte. Nach wiederholter Kommunikation und Bestätigung mit der Plattform wurde schließlich überprüft, dass die SQL-Aufgabe vor der Ausführung des Einfügens gestartet werden musste . Die Tabelle hat SQL abgerufen und den Aufgabenstatus aktualisiert. Infolgedessen kam es in dieser Tabelle zu einer großen Anzahl gleichzeitiger Einfügungen und Aktualisierungen, was dazu führte, dass einige SQL-Anweisungen beim Warten auf die Sperre abliefen. . .

MySQL-Protokollanalyseskript

Da der frühe Morgen die Geschäftsspitze des Data Warehouse ist, treten zu diesem Zeitpunkt viele Probleme auf, und einige seltsame Probleme treten häufig auf Dorf Dieser Laden existiert nicht mehr und kann tagsüber nicht wiederhergestellt werden. Wie wir die Protokolle erfassen, die uns wichtig sind, um das Problem schnell zu lokalisieren, hat oberste Priorität. Hier habe ich ein kleines Skript geschrieben, Crontab-Bereitstellung, Sie können den zu öffnenden Zeitraum auswählen, das Protokoll jede Minute testen, was erklärt werden muss Allgemeines Protokoll Schalten Sie es nicht einfach ein, da es sonst zu einem großen Verlust der Datenbankleistung führt.

Verwandte Empfehlungen:

Über die Verwendung von kill in MySQL Detaillierte Erläuterung des Befehls zur Lösung des Deadlock-Problems

Das Konzept und die Bedingungen des Deadlocks

Teilen einer Methode zur Lösung des MySQL-Deadlock-Problems

Das obige ist der detaillierte Inhalt vonMySQL-Deadlock- und Protokollanalyse. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!