Detaillierte Erläuterung von MySQL-Deadlocks sowie Datenbank- und Tabellen-Sharding-Problemen

Notieren Sie die Problempunkte der MySQL-Produktion.

Geschäftsszenario und Problembeschreibung

Bei der Anforderung einer externen Schnittstelle liegt die Anzahl der Anfragen pro Tag bei etwa 9 Millionen.

Es gliedert sich in zwei Positionen: Anforderungsposition und Empfangsposition. Mit der Anforderung wird die externe Schnittstelle aufgerufen, und mit dem Beleg wird die Schnittstelle zum Empfangen und Senden verwendet.

Die Daten werden vor dem Absenden der Anfrage in die Datenbank eingefügt.

Wenn die Schnittstelle nach der Anforderung einen Anruffehler zurückgibt, wird der Datenbankstatus auf „Fehler“ aktualisiert.

Wenn das Senden erfolgreich ist, wird auf eine Empfangsbestätigung vom Upstream gewartet und anschließend der Datenbankstatus aktualisiert.

Während des Produktionsbetriebs trat das durch MySQL verursachte Problem der MQ-Verbraucherakkumulation innerhalb eines halben Jahres zweimal auf.

Problemanalyse

Erfassen Sie zwei Produktionsprobleme unterschiedlicher Ursache und führen Sie eine Ursachenanalyse durch.

MySQL-Deadlock-Problem

Sehen Sie sich den TPS der mq Aggregationsplattform an
In der Produktion stellt man fest, dass sich die Anzahl der MQ-Daten angesammelt und erhöht hat. Der TPS liegt jedoch nur bei etwa 30 und ist nicht gestiegen.

Dies verlangsamt den MQ-Verbrauch und führt zu einer kontinuierlichen Ansammlung. Der konkrete Grund für die ständige Anhäufung von MQ muss weiter untersucht werden.

Anzeigen von Produktionsserverprotokollen

Beim Überprüfen des Produktionsserverprotokolls habe ich einen Deadlock-Fehler festgestellt.

Fehlerantwort von MySQLConnection [node=24, id=277499, threadId=2735941, state=borrowed, closed=false, autocommit=true, host=10.1.10.74, port=3306, database=sep_4, localPort=27744, isClose:false, toBeClose:false, MySQLVersion:5.7.25], err: Beim Versuch, eine Sperre zu erhalten, wurde ein Deadlock gefunden. Versuchen Sie, die Transaktion neu zu starten. Code: 1213

Das spezifische SQL lautet wie folgt:

Status der Aktualisierungsphase festlegen = „Erfolgreich“, Antwortzeit = „07.03.2021 10:40:11“, wobei Code = „000123“ und Erstellungszeit > „03.03.2021 00:00:00“;

Das heißt, beim Ausführen des Dienstes ist ein Deadlock aufgetreten.

Da es nicht intuitiv ist, die genaue Anzahl der Einträge und die dafür benötigte Zeit auf dem Produktionsserver anzuzeigen, haben wir den DBA gebeten, die langsamen SQL-Anweisungen und die dafür benötigte Zeit herauszufinden.

Nach der Überprüfung stellte sich heraus, dass das längste langsame SQL bis zu 7780 ms dauerte.

Wenn Sie genau hinsehen, werden Sie feststellen, dass SQL feststellt, dass dieselbe ID ausgeführt wird und die andere sich im Lock-Wait-Zustand befindet.

Und es gibt viele Lock-Wait-Zustände in diesem langsamen SQL.

Was verursacht einen Deadlock?

Die von MySQL verwendete Datenbank-Engine ist InnoDB. Verstehen Sie zunächst, was ein Deadlock ist:

Deadlock: Bedeutet, dass zwei oder mehr Prozesse gerade ausgeführt werden.
Ein Phänomen des Aufeinanderwartens, das durch den Wettbewerb um Ressourcen entsteht. Ohne äußere Kraft werden sie nicht in der Lage sein, voranzukommen.
Zu diesem Zeitpunkt befindet sich das System in einem Deadlock-Zustand oder weist einen Deadlock auf. Diese Prozesse, die immer aufeinander warten, werden als Deadlock-Prozesse bezeichnet.

Aus der obigen Untersuchung können wir erkennen, dass das Deadlock-Problem folgendes ist:

Wenn Sie SQL ausführen, um eine Datenzeile zu aktualisieren, wird die Datenzeile gesperrt. Nach Abschluss der Ausführung wird die Zeilensperre aufgehoben und das nicht ausgeführte SQL befindet sich im Wartezustand für die Sperre.

Der Grund hierfür liegt im Programm darin, dass vor und nach dem Senden sowie beim Empfangen einer Quittung häufig mit der Datenbank gearbeitet wird und dabei möglicherweise dieselben Daten gleichzeitig bearbeitet werden.

Daher kommt es während der Ausführung zu einer Wartezeit auf die Sperre.

Der Sharding-Schlüssel ist nicht in der Sharding-Tabelle enthalten

Die erste Warnung ist, dass die CPU-Auslastung der Stage_Prod-Bibliothek auf 85 % gestiegen ist.

Ist die Anzahl der Datenbank-Threads erschöpft?

Nachdem wir den Datenbankverbindungsstatus überprüft haben, können wir sehen, dass die Anzahl der Datenbankverbindungen nicht voll ist.

Erkennen Sie langsames SQL und Zeitverbrauch

Erkanntes SQL-Problem:

Status der Aktualisierungsphase festlegen = „Erfolgreich“, Antwortzeit = „07.03.2021 10:40:11“, wobei Erstellungszeit > „03.03.2021 00:00:00“;

Wenn Sie das SQL überprüfen, werden Sie feststellen, dass dieses SQL das Shard-Schlüsselcodefeld nicht enthält. Diese SQL-Anweisung wird ausgeführt, wenn die Quittung eingeht.

Überprüfen Sie die Produktionsserverprotokolle

Es gibt eine Beurteilung im Code. Wenn der Codewert nicht leer ist, überträgt SQL den Codewert. Sollten Sie dieses nicht mitgeführt haben, müssen Sie die Gründe hierfür prüfen.

Wenn Sie sich den Code ansehen, werden Sie feststellen, dass der Code von Redis abgerufen wird und beim Senden auf Redis gesetzt wird. Aber es ist seltsam, dass es nicht eingestellt ist.

Wir vermuteten zunächst, dass es sich um ein Redis-Problem handelte, kommunizierten dann mit der Redis-Wartungsplattform und stellten fest, dass es sich tatsächlich um ein Problem handelte, das durch einen Redis-Fehler verursacht wurde.

Warum schießt die CPU ohne Shard-Schlüssel in die Höhe?

Zunächst verwendet das Unternehmen HotDB, um Datenbank und Tabelle zu trennen, da das tägliche Eingabevolumen etwa 9 Millionen beträgt und eine Tabelle Hunderte Millionen Daten enthält.

Wenn Sie nur den Index verwenden, wird dieser den Anforderungen nicht gerecht.

Die HotDB-Datenbank und -Tabelle wurden in verschiedene Teile aufgeteilt und das Hash-Sharding wurde basierend auf dem Codewert mit insgesamt 64 Shards durchgeführt. D.h. 64 Datenbanken werden in 16 Instanzen auf 8 Servern verteilt.

Dadurch kann eine ungleichmäßige Datenverteilung zwischen den Shards und theoretisch eine Überkonzentration auf einen bestimmten Shard vermieden werden.

Wenn das SQL den Shard-Schlüsselcode nicht enthält, werden alle DML-Operationen zur Ausführung an alle zugrunde liegenden Bibliotheken gesendet, was einem einmaligen Durchlaufen der Bibliothek entspricht.

Dies kann zu einer CPU-Auslastung von bis zu 99 % oder sogar zu einem Serverabsturz führen, was sehr beängstigend ist.

Lösung

Notfallmaßnahme: Vorher mehrere Dienste stoppen, um den Datenbankbetrieb zu reduzieren

Die kontinuierliche Ansammlung von Daten beeinträchtigt die Geschwindigkeit der Datenverarbeitung. Dann müssen wir zunächst die Geschwindigkeit des Vorgangs reduzieren. Der schnellste Weg besteht darin, den Dienst zu stoppen und die Häufigkeit der Datenbankvorgänge zu reduzieren.

Reduzieren Sie Datenbankvorgänge, um Datenbank-Deadlocks zu vermeiden

Deadlocks treten normalerweise auf, weil die Übermittlung von DML-Operationen vom Programm nicht richtig kontrolliert und nicht rechtzeitig erfolgt.

Reduzieren Sie wiederholte Vorgänge an denselben Daten. Reduzieren Sie bei Batchvorgängen die Anzahl der DMLs in jedem Batch, um eine schnelle Übermittlung sicherzustellen, lange Transaktionen zu vermeiden und eine wiederholte Übermittlung von DMLs zu vermeiden.

Wie kann man also den Betriebsaufwand reduzieren?

SQL zusammenführen

Führen Sie das Einfügen vor dem Senden und das Aktualisieren beim Senden in einer SQL-Anweisung zusammen. Vermeiden Sie so mehrere Vorgänge mit denselben Daten.

Reduzieren Sie lange Transaktionen und die Anzahl der Einträge während der Batch-Ausführung

Während der Ausführung stellte sich heraus, dass die Ausführung von jeweils 20 SQL-Anweisungen in Stapeln effizienter war als die gleichzeitige Ausführung von 200 SQL-Anweisungen.

Versuchen Sie daher, dieses Problem so weit wie möglich zu vermeiden.

Jede SQL-Anweisung muss einen Sharding-Schlüssel für jede Datenbank oder Tabelle enthalten.

Der Grundsatz besteht darin, dass die Betriebsgeschwindigkeit der gesamten Datenbank nicht durch einzelne Daten beeinträchtigt werden darf.

Der Shard-Schlüssel muss enthalten sein. Wenn er nicht enthalten ist, wird ein Fehler ausgegeben.

Erhöhen Sie das Zeitintervall zum Öffnen und Schließen

Verwenden Sie Code als Shard-Schlüssel und createTime als Partition. Anschließend können Sie, während Sie die Existenz des Codes sicherstellen, die Öffnungs- und Schließintervalle schreiben, um die Ausführungseffizienz zu verbessern.

Bessere Lösung: SQL sequentielle Ausführung

Mit dieser Lösung kann sichergestellt werden, dass die auszuführenden SQL-Anweisungen nacheinander ausgeführt werden und so Deadlocks vermieden werden.

Allerdings muss hier je nach Geschäftsszenario differenziert werden.

Rezension

Um das MySQL-Deadlock-Problem zu lösen, versuchen Sie, häufige Vorgänge mit denselben Daten und lange Transaktionen zu vermeiden.
Achten Sie beim Sharding-Problem darauf, den Sharding-Schlüssel mit einzuschließen.
Überwachungsmechanismen sind unabdingbar;

Zusammenfassen

Dies ist das Ende dieses Artikels über MySQL-Deadlocks und -Sharding. Weitere Informationen zu MySQL-Deadlocks und -Sharding finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!