Lösung für die lange Verzögerung der MySQL-Datenbank-Master-Slave-Replikation

Vorwort

Die Verzögerung der MySQL Master-Slave-Replikation ist in der Branche seit langem ein Problem. Das Auftreten von Verzögerungen verringert den Wert der Trennung von Lesen und Schreiben zwischen Master und Slave, was der Verwendung von MySQL in Unternehmen mit hohen Echtzeitanforderungen an Daten nicht förderlich ist.

UDB ist ein von UCloud gestarteter Cloud-Datenbankdienst. Er ist seit sechs Jahren online und betreibt Zehntausende von UDB MySQL-Instanzen. Das Team stellt nicht nur hohe Verfügbarkeit, hohe Leistung und benutzerfreundliche Produktfunktionen bereit, sondern hilft den Benutzern auch dabei, täglich durchschnittlich 2–3 Probleme mit Verzögerungen bei der Master-Slave-Replikation von MySQL-Instanzen zu lösen. Aus viel Praxis haben wir verschiedene Ursachen und Lösungen für Verzögerungen bei der Master-Slave-Replikation zusammengefasst und teilen sie nun hier.

Die Bedeutung von Latenzproblemen

Der Master-Slave-Replikationsmechanismus wird häufig in der internen Implementierung von UDB verwendet: Die von UDB erstellten Slave- und Master-Datenbanken übernehmen die Datenreplikation „Master-Slave-Replikation“. Darüber hinaus übernimmt das Flaggschiffprodukt von UDB, „UDB MySQL High Availability Instance“, auch den „Dual-Master-Modus“, bei dem zwei Datenbanken gegenseitig Master-Slave sind, um Daten zu replizieren, und der Kern des Dual-Master-Modus ist der Master-Slave-Replikationsmechanismus.

Wenn zwischen der Master-Slave-Replikation eine Verzögerung auftritt, wird die Konsistenz der Master-Slave-Daten beeinträchtigt.

Im Szenario der hochverfügbaren Replikationsfunktion haben wir im UDB-Design der hochverfügbaren Notfallwiederherstellung berücksichtigt, dass bei inkonsistenten Primär- und Standby-Daten die Umschaltung auf die hochverfügbare Notfallwiederherstellung standardmäßig nicht zulässig ist. Denn wenn die Primär- und Standbydaten inkonsistent sind, ein Disaster Recovery-Umschalten erfolgt und Daten in die neue Primärdatenbank geschrieben werden, kann dies aus geschäftlicher Sicht unerwartete schwerwiegende Folgen haben.

Das Problem der Replikationsverzögerung hat nicht nur negative Folgen für die hohe Verfügbarkeit von UDB, sondern kann auch im Szenario von schreibgeschützten Slave-Datenbanken gewisse Auswirkungen auf das Geschäft haben, wenn die Replikationsverzögerung der Slave-Datenbank auftritt. Dies äußert sich beispielsweise in inkonsistentem Lesen und Schreiben im Geschäft - die neu hinzugefügten/geänderten Daten können nicht gefunden werden usw.

Dies zeigt, dass das Verzögerungsproblem der Master-Slave-Replikation bei Datenbankoperationen besondere Aufmerksamkeit erfordert. Normalerweise führt der DBA 'SHOW SLAVE STATUS' auf der Bibliothek aus und beobachtet

Der Wert von „Seconds_Behind_Master“ kann Ihnen dabei helfen, die Datenreplikationsverzögerung zwischen einer aktuellen Datenbank und ihrer Masterdatenbank zu verstehen. Dieser Wert ist so wichtig, dass wir ihn separat auf der UDB-Überwachungsschnittstelle extrahiert und das Überwachungselement „Slave-Synchronisierungsverzögerung“ so gestaltet haben, dass das Betriebs- und Wartungspersonal ihn direkt auf der Konsole beobachten kann.

Analyse und Lösung des Verzögerungsproblems in der Produktionsumgebung

Wir haben die häufigsten Fälle von Verzögerungen bei der Master-Slave-Replikation in mehrere Kategorien zusammengefasst. Im Folgenden finden Sie eine Zusammenfassung der Phänomenbeschreibung, der Ursachenanalyse und der Lösungen für die entsprechenden Fälle.

◆ Fall 1: Häufige DML-Anfragen an die Hauptdatenbank

Während Geschäftsspitzenzeiten kann es bei manchen Benutzern zu Verzögerungen bei der Master-Slave-Replikation kommen, insbesondere wenn auf dem Datenbankmaster eine große Anzahl von Schreibanforderungsvorgängen stattfindet, d. h. eine große Anzahl gleichzeitiger Vorgänge wie Einfügen, Löschen und Aktualisieren.

Beschreibung des Phänomens

Durch Beobachten des QPS-Werts der Schreibvorgänge der Masterdatenbank können wir sehen, dass der QPS-Wert der Schreibvorgänge der Masterdatenbank plötzlich ansteigt, begleitet von einer Erhöhung der Master-Slave-Replikationsverzögerung. Dies kann als Folge häufiger DML-Anfragen an die Masterdatenbank beurteilt werden.

Wie in der obigen Abbildung gezeigt, stiegen die QPS gegen 17:58 plötzlich an und die schreibbezogenen QPS auf der Konsole stiegen ebenfalls entsprechend an. Wenn die QPS plötzlich ansteigt, erhöht sich auch die entsprechende Latenz allmählich, wie in der folgenden Abbildung dargestellt.

Ursachenanalyse

Nach der Analyse glauben wir, dass dies auf eine große Anzahl von Schreibanforderungsvorgängen in der Hauptdatenbank zurückzuführen ist, die in kurzer Zeit eine große Menge an Binlog generiert haben. Diese Vorgänge müssen mit der Slave-Datenbank synchronisiert und ausgeführt werden, was zu einer Verzögerung bei der Datenreplikation zwischen Master und Slave führt.

Eine genauere Analyse der Ursache zeigt, dass während der Hauptgeschäftszeit die Master-Datenbank Daten gleichzeitig schreibt, während der SQL-Thread der Slave-Datenbank das Binlog-Protokoll in einem einzelnen Thread wiedergibt, was leicht zu einer Ansammlung von Relaylogs und zu Verzögerungen führen kann.

Lösung

Wenn es sich bei der Version um MySQL 5.7 oder eine frühere Version handelt, können Sie Sharding verwenden, um die Schreibanforderungen durch horizontale Skalierung aufzuteilen und so die Parallelität der Schreibanforderungen an das Binärprotokoll zu erhöhen.

Wenn es sich um MySQL 5.7 oder höher handelt, wird in MySQL 5.7 die parallele Replikation basierend auf der logischen Uhr (Group Commit) verwendet. In MySQL 8.0 wird eine parallele Replikation basierend auf Write Set verwendet. Beide Lösungen können die Leistung der Binlog-Wiedergabe verbessern und die Latenz reduzieren.

◆ Fall 2: Die Hauptdatenbank führt eine große Transaktion aus

Bei einer großen Transaktion handelt es sich um die Ausführung einer Transaktion, die sehr lange dauert. Häufige Anweisungen, die große Transaktionen generieren, sind:

Es werden zahlreiche langsame Datenimportanweisungen verwendet, wie etwa INSERT INTO $tb, SELECT * FROM $tb, LOAD DATA INFILE usw.
Verwenden Sie UPDATE- und DELETE-Anweisungen, um UPDATE und DELETE für eine große Tabelle auszuführen.
Wenn diese Transaktion auf der Slave-Datenbank wiederholt wird, kann es zu einer Verzögerung der Master-Slave-Replikation kommen.

Beschreibung des Phänomens

Wir analysieren die Ergebnisse von SHOW SLAVE STATUS und stellen fest, dass sich das Feld Exec_Master_Log_Pos nicht geändert hat und second_behinds_master weiter ansteigt, während der Wert des Felds Slave_SQL_Running_State „Ereignis aus dem Relay-Protokoll lesen“ lautet. Gleichzeitig können wir durch Analysieren des Binärprotokolls der Masterdatenbank und Betrachten der aktuell von der Masterdatenbank ausgeführten Transaktionen einige große Transaktionen finden. Dies stellt im Wesentlichen fest, dass die Verzögerung der Master-Slave-Replikation durch die Ausführung großer Transaktionen verursacht wird.

Ursachenanalyse

Nachdem eine große Transaktion im Binärprotokoll aufgezeichnet und mit der Slave-Datenbank synchronisiert wurde, dauert es sehr lange, bis die Slave-Datenbank die Transaktion ausführt. Während dieser Zeit kommt es zu einer Verzögerung der Master-Slave-Replikation.

Wenn beispielsweise die Master-Datenbank 200 Sekunden zum Aktualisieren einer großen Tabelle benötigt und die Master- und Slave-Datenbanken ähnliche Konfigurationen aufweisen, benötigt die Slave-Datenbank zum Aktualisieren der großen Tabelle fast die gleiche Zeit. Zu diesem Zeitpunkt beginnen sich die Verzögerungen der Slave-Datenbank anzuhäufen, und nachfolgende Ereignisse können nicht aktualisiert werden.

Lösung

Unsere Methode zur Verbesserung der durch diese Situation verursachten Verzögerung der Master-Slave-Replikation besteht darin, die große Transaktionsanweisung in mehrere kleine Transaktionen aufzuteilen, sodass sie rechtzeitig festgeschrieben werden können und die Verzögerung der Master-Slave-Replikation verringert wird.

◆ Fall 3: Die Hauptdatenbank führt DDL-Anweisungen auf einer großen Tabelle aus

DDL steht für Data Definition Language und bezieht sich auf einige Anweisungen, die die Tabellenstruktur ändern, beispielsweise das Hinzufügen eines Felds oder eines Index zur Tabelle. Wenn DDL-Anweisungen für eine große Tabelle in der Masterdatenbank ausgeführt werden, kann es zu einer Verzögerung der Master-Slave-Replikation kommen.

Beschreibung des Phänomens

Wenn die Ausgabe von SHOW SLAVE STATUS, das von der Slave-Bibliothek ausgeführt wird, zeigt, dass sich Exec_Master_Log_Pos nicht geändert hat und die Master-Bibliothek keine großen Transaktionen ausführt, ist es möglich, dass der DDL einer großen Tabelle ausgeführt wird. Dies kann durch Analysieren des Binärprotokolls der Hauptdatenbank und Betrachten der aktuell von der Hauptdatenbank ausgeführten Transaktionen bestätigt werden.

Die Ausführung von DDL-Anweisungen kann weiter wie folgt unterteilt werden:

1. DDL wurde nicht gestartet und ist blockiert. In diesem Fall zeigt das Ergebnis von SHOW SLAVE STATUS, dass Slave_SQL_Running_State auf die Sperre der Tabellenmetadaten wartet und Exec_Master_Log_Pos unverändert bleibt.

2. Der DDL wird ausgeführt und die SQL-Thread-Single-Thread-Anwendung verursacht eine erhöhte Latenz. In diesem Fall können Sie durch Beobachten der Ergebnisse von SHOW SLAVE STATU feststellen, dass Slave_SQL_Running_State die Tabelle ändert, während Exec_Master_Log_Pos unverändert bleibt.

Wenn das oben beschriebene Phänomen auftritt, ist es sehr wahrscheinlich, dass die Masterdatenbank DDL-Anweisungen für die große Tabelle ausführt, sie mit der Slavedatenbank synchronisiert und sie in der Slavedatenbank erneut wiedergibt, was zu einer Verzögerung der Master-Slave-Replikation führt.

Ursachenanalyse

Der Grund für die durch DDL verursachte Verzögerung der Master-Slave-Replikation ähnelt dem großer Transaktionen. Es liegt auch daran, dass die Slave-Bibliothek das Binlog von DDL langsam ausführt, was zu einer Verzögerung der Master-Slave-Replikation führt.

Lösung

In diesem Fall verwenden wir hauptsächlich SHOW PROCESSLIST oder die Abfrage information_schema.innodb_trx, um die blockierende DDL-Anweisung zu finden und die zugehörigen Abfragen zu KILLEN, damit DDL in der Slave-Datenbank normal ausgeführt werden kann.

Die durch DDL selbst verursachte Verzögerung lässt sich nur schwer vermeiden. Es wird empfohlen, Folgendes zu berücksichtigen:

Vermeiden Sie Geschäftsspitzen und versuchen Sie, die Ausführung außerhalb der Geschäftszeiten zu planen.

Nachdem Sie sql_log_bin = 0 festgelegt haben, führen Sie DDL manuell auf der Master- und Slave-Datenbank aus (dieser Vorgang kann bei einigen DDL-Vorgängen zu Dateninkonsistenzen führen, testen Sie ihn daher unbedingt gründlich). Wenn der Benutzer die Cloud-Datenbank UDB verwendet, können Sie sich an das Betriebs- und Wartungsteam von UCloud UDB wenden, um Unterstützung zu erhalten.

◆ Fall 4: Konfigurationsinkonsistenz zwischen Master- und Slave-Datenbanken

Wenn die Master- und Slave-Bibliotheken unterschiedliche Rechen- und Speicherressourcen oder unterschiedliche Kernel-Tuning-Parameter verwenden, können Master und Slave inkonsistent sein.

Beschreibung des Phänomens

Wir werden die Leistungsüberwachungsdaten der Master- und Slave-Datenbanken im Detail vergleichen. Wenn wir feststellen, dass die Überwachungsdaten sehr unterschiedlich sind, können wir durch Überprüfung der unterschiedlichen Konfigurationen der Master- und Slave-Datenbanken ein klares Urteil fällen.

Ursachenanalyse

Unterschiede in der Konfiguration verschiedener Hardware oder Ressourcen können zu Leistungsunterschieden zwischen Master und Slave führen, was zu Verzögerungen bei der Master-Slave-Replikation führt:

Hardware: Wenn beispielsweise der Server der Master-Datenbankinstanz SSD-Festplatten verwendet, der Server der Slave-Datenbankinstanz jedoch normale SAS-Festplatten, können die von der Master-Datenbank generierten Schreibvorgänge nicht sofort auf der Slave-Datenbank verarbeitet werden, was zu einer Verzögerung der Master-Slave-Replikation führt.
Konfiguration: Mögliche Gründe sind beispielsweise inkonsistente Schreibstrategien für RAID-Karten, inkonsistente Kernel-Parametereinstellungen des Betriebssystems, inkonsistente Strategien zur MySQL-Festplattenplatzierung usw.

Lösung

Erwägen Sie, die Konfiguration von DB-Maschinen (einschließlich Hardware- und Optionsparametern) so weit wie möglich zu vereinheitlichen. Sogar für einige OLAP-Unternehmen muss die Hardwarekonfiguration der Slave-Datenbankinstanz etwas höher sein als die der Master-Datenbank.

◆ Fall 5: Der Tabelle fehlt ein Primärschlüssel oder ein geeigneter Index

Wenn der Datenbanktabelle ein Primärschlüssel oder ein geeigneter Index fehlt, kann es zu einer Verzögerung der Master-Slave-Replikation kommen, wenn das Binlog-Format der Master-Slave-Replikation auf „Zeile“ eingestellt ist.

Beschreibung des Phänomens

Wenn wir die Datenbank überprüfen, stellen wir fest:

Beobachten Sie die Ausgabe von SHOW SLAVE STATUS und stellen Sie fest, dass Slave_SQL_Running_State das Ereignis aus dem Relay-Protokoll liest.

OFFENE TABELLEN ANZEIGEN, WO Die Tabelle mit in_use=1 immer vorhanden ist;

Beachten Sie, dass das Feld Exec_Master_Log_Pos von SHOW SLAVE STATUS unverändert bleibt.

Die CPU-Auslastung des mysqld-Prozesses liegt nahe bei 100 % (wenn kein Lesedienst vorhanden ist) und der IO-Druck ist nicht groß.

Wenn diese Phänomene auftreten, kann davon ausgegangen werden, dass der Tabelle mit hoher Wahrscheinlichkeit ein Primärschlüssel oder ein eindeutiger Index fehlt.

Ursachenanalyse

Wenn das Binlog-Format der Master-Slave-Replikation beispielsweise auf „Row“ eingestellt ist, gibt es ein Szenario, in dem die Masterdatenbank 200.000 Datenzeilen in einer Tabelle mit 5 Millionen aktualisiert. Im Zeilenformat zeichnet Binlog 200.000 Aktualisierungsvorgänge auf, was bedeutet, dass jeder Vorgang einen Datensatz aktualisiert. Wenn diese Anweisung einen fehlerhaften Ausführungsplan aufweist, z. B. einen vollständigen Tabellenscan, erfordert jede Aktualisierungsanweisung einen vollständigen Tabellenscan. Zu diesem Zeitpunkt ist die Wiedergabe von SQL-Threads äußerst langsam, was zu erheblichen Verzögerungen bei der Master-Slave-Replikation führt.

Lösung

In diesem Fall überprüfen wir die Tabellenstruktur, stellen sicher, dass jede Tabelle einen expliziten automatisch inkrementierenden Primärschlüssel hat, und unterstützen Benutzer beim Erstellen geeigneter Indizes.

◆ Fall 6: Der Eigendruck des Sklaven ist zu hoch

Wenn die Leistungsbelastung der Slave-Datenbank sehr hoch ist, kann sie manchmal nicht mit der Aktualisierungsgeschwindigkeit der Master-Datenbank mithalten, was zu einer Verzögerung der Master-Slave-Replikation führt.

Beschreibung des Phänomens

Beim Beobachten der Datenbankinstanz stellen Sie möglicherweise fest, dass die CPU-Auslastung zu hoch und die IO-Auslastung zu hoch ist, was dazu führt, dass die SQL-Thread-Anwendung zu langsam ist. Auf diese Weise kann festgestellt werden, dass die Verzögerung bei der Master-Slave-Replikation durch übermäßigen Druck auf die Slave-Bibliothek selbst verursacht wird.

Ursachenanalyse

Einige UCloud-Benutzer verwenden den Lese-/Schreibtrennungsmodus für die Master- und Slave-Datenbanken, und die meisten Leseanforderungen werden auf der Slave-Datenbank ausgeführt. In einem Szenario mit einer großen Anzahl von Leseanforderungen im Unternehmen erzeugt die Slave-Datenbank einen viel größeren Leistungsdruck als die Master-Datenbank. Einige Benutzer betreiben sogar OLAP-Geschäfte, die viele Rechenressourcen auf der Slave-Datenbank verbrauchen, was auch höhere Leistungsanforderungen an die Slave-Datenbank stellt, was wiederum zu Verzögerungen bei der Master-Slave-Replikation führt.

Lösung

In diesem Fall empfehlen wir Benutzern, weitere Slaves zu erstellen, um Leseanforderungen zu verteilen und den Druck auf vorhandene Slave-Instanzen zu verringern. Für das OLAP-Geschäft können Sie eine Slave-Datenbank speziell für das OLAP-Geschäft einrichten und eine entsprechende Master-Slave-Replikationsverzögerung für diese Slave-Datenbank zulassen.

Zusammenfassen

Wenn Sie den Master-Slave-Replikationsmodus von MySQL zur Datenreplikation verwenden, ist die Master-Slave-Replikationsverzögerung ein wichtiger Faktor, der berücksichtigt werden muss. Dies beeinträchtigt die Konsistenz der Daten und somit auch die Hochverfügbarkeit beim Disaster Recovery-Switching der Datenbank.

Wenn es zu einer Verzögerung bei der Master-Slave-Replikation zwischen Datenbanken kommt, hat unser Team auf Grundlage früherer Erfahrungen die folgenden Methoden und Prozesse zusammengefasst, um das Problem zu beheben:

Verwenden Sie SHOW SLAVE STATUS und SHOW PROCESSLIST, um den aktuellen Status der Slave-Bibliothek anzuzeigen. (Ähnliche Gründe können übrigens auch bei der Sicherung aus der Bibliothek ausgeschlossen werden.)

Wenn sich Exec_Master_Log_Pos nicht ändert, berücksichtigen Sie große Transaktionen, DDL und keinen Primärschlüssel und überprüfen Sie das Binärprotokoll und die Position, die der Masterdatenbank entsprechen.

Wenn sich Exec_Master_Log_Pos ändert und die Verzögerung allmählich zunimmt, berücksichtigen Sie die Belastung des Slave-Computers, z. B. IO, CPU usw., und überlegen Sie, ob der Druck auf den Master-Schreibvorgang und den Slave selbst zu groß ist.

Dieser Artikel stammt von: UCloud Technology und wird von den leitenden UCloud-Experten Ding Shun und Zhang Suning geteilt.

Das ist alles für diesen Artikel. Ich hoffe, dass der Inhalt dieses Artikels für Ihr Studium oder Ihre Arbeit von gewissem Referenzwert ist. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.