Ursachen und Lösungen für Verzögerungen bei der MySQL Master-Slave-Replikation

Quelle: Öffentliches Konto „Oracle’s Shadow Gallery“

In einer asynchronen oder halbsynchronen Replikationsstruktur ist es normal, dass es bei Slaves zu Verzögerungen kommt.
Obwohl Verzögerungen normal sind, wird im Allgemeinen vom Unternehmen beurteilt, ob sie Aufmerksamkeit erfordern.
Wenn beispielsweise ein Lesevorgang in der Slave-Datenbank ausgeführt wird, der eine hohe Konsistenz erfordert, und die Verzögerung unter einem bestimmten Wert liegen muss, sollte darauf geachtet werden.

Ein kurzer Überblick über die Replikationslogik:

1. Die Masterdatenbank zeichnet Änderungen an der Datenbankinstanz im Binärprotokoll auf.
2. Master has sent all binlog to slave; waiting for more updates einen binlog dump -Thread, um die Änderungen im Binlog in Echtzeit zu überwachen und diese neuen Ereignisse an die Slave-Datenbank zu übertragen (Master hat das gesamte Binlog an Slave gesendet; wartet auf weitere Updates)
3. Empfangen Sie diese Ereignisse vom IO Thread der Bibliothek und zeichnen Sie sie im Relaylog auf.
4. Der Slave- SQL Thread liest die Ereignisse des Relay-Protokolls und wendet diese Ereignisse auf die Slave-Instanz an (oder spielt sie erneut ab).

Oben ist die Standardlogik für die asynchrone Replikation. Die halbsynchrone Replikation unterscheidet sich geringfügig und wird hier nicht beschrieben.

Darüber hinaus lässt sich sehr einfach feststellen, ob in der Slave-Bibliothek eine Verzögerung vorliegt:
Verwenden Sie in der Slave-Datenbank SHOW SLAVE STATUS
Überprüfen Sie einfach Seconds_Behind_Master .

Ursachen für Verzögerungen und Lösungen

〇Häufige DML-Anfragen an die Hauptdatenbank (große tps)

Das heißt, die Hauptdatenbank weist viele Schreibanforderungen mit einer großen Anzahl gleichzeitiger Einfüge-, Lösch- und Aktualisierungsvorgänge auf, wodurch in kurzer Zeit eine große Menge an Binärprotokollen generiert wird.

【Ursachenanalyse】

Die Masterdatenbank schreibt Daten gleichzeitig, während der SQL Thread der Slavedatenbank Protokolle in einem einzelnen Thread anwendet, was leicht zu einer Ansammlung von Relaylogs und zu Verzögerungen führen kann.

【Lösung】

Führen Sie Sharding durch und verteilen Sie Schreibanforderungen durch Skalierung. Oder erwägen Sie ein Upgrade auf MySQL 5.7+ und aktivieren Sie die parallele Replikation basierend auf logischen Uhren.

〇Die Hauptdatenbank führt große Transaktionen aus

Beispielsweise das Importieren einer großen Menge an Daten: INSERT INTO $tb1 SELECT * FROM $tb2、LOAD DATA INFILE usw. Beispielsweise UPDATE , DELETE der gesamten Tabelle usw.
Exec_Master_Log_Pos hat sich nicht geändert, Slave_SQL_Running_State Reading event from the relay log
Analysieren Sie das Binärprotokoll der Hauptdatenbank und sehen Sie sich die Transaktionen an, die aktuell von der Hauptdatenbank ausgeführt werden.

【Ursachenanalyse】

Wenn 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】

Teilen Sie große Transaktionen auf und übermitteln Sie sie rechtzeitig.

〇Die Hauptdatenbank führt DDL-Anweisungen für große Tabellen aus

Das Phänomen ähnelt dem Ausführen großer Transaktionen in der Hauptdatenbank.
Überprüfen Sie, dass Exec_Master_Log_Pos nicht verschoben wurde. Dies kann an der Ausführung von DDL liegen.
Analysieren Sie das Binärprotokoll der Hauptdatenbank und sehen Sie sich die Transaktionen an, die aktuell von der Hauptdatenbank ausgeführt werden.

【Ursachenanalyse】

1. DDL wurde nicht gestartet und ist blockiert. SHOW SLAVE STATUS zeigt an, dass Slave_SQL_Running_State waiting for table metadata lock und Exec_Master_Log_Pos unverändert bleibt.
2. DDL wird ausgeführt, und SQL Thread Single-Thread-Anwendung verursacht eine erhöhte Latenz. Slave_SQL_Running_State ist altering table , Exec_Master_Log_Pos bleibt unverändert

【Lösung】

Verwenden Sie processlist oder information_schema.innodb_trx , um die Abfrage zu finden, die die DDL-Anweisung blockiert, beenden Sie die Abfrage und ermöglichen Sie die normale Ausführung des DDL in der Slave-Datenbank.
Die durch DDL selbst verursachte Verzögerung lässt sich nur schwer vermeiden. Es wird empfohlen, Folgendes zu berücksichtigen:
① Außerhalb der Spitzenzeiten ausführen. ② Nach set sql_log_bin=0 DDL manuell auf der Master- bzw. Slave-Datenbank ausführen (dieser Vorgang kann bei einigen DDL-Vorgängen zu Dateninkonsistenzen führen, testen Sie ihn daher gründlich).

〇Die Konfiguration der Master-Bibliothek und der Slave-Bibliothek ist inkonsistent:

【Ursachenanalyse】

Hardware: Der Master-Instanz-Server verwendet SSD, während der Slave-Instanz-Server eine normale SAS-Festplatte verwendet und die CPU-Hauptfrequenz inkonsistent ist. Konfiguration: beispielsweise inkonsistente Schreibstrategie für RAID-Karten, inkonsistente Einstellungen der OS-Kernel-Parameter, inkonsistente Strategie zur Platzierung von MySQL-Festplatten usw.

【Lösung】

Versuchen Sie, die Konfiguration von DB-Maschinen zu vereinheitlichen (einschließlich Hardware- und Optionsparameter).
Sogar bei einigen OLAP-Unternehmen ist die Hardwarekonfiguration der Slave-Instanz höher als die der Master-Instanz.

〇Der Tabelle fehlt ein Primärschlüssel oder ein eindeutiger Index

Wenn binlog_format=row ist und der Tabelle ein Primärschlüssel oder ein eindeutiger Index fehlt, kann die Latenz der Slave-Datenbank während UPDATE und DELETE stark ansteigen.
Zu diesem Zeitpunkt liest Slave_SQL_Running_State Reading event from the relay log .
Und die Tabelle SHOW OPEN TABLES WHERE in_use=1 existiert immer.
Exec_Master_Log_Pos bleibt unverändert.
Die CPU-Auslastung des mysqld-Prozesses beträgt fast 100 % (wenn kein Leseverkehr stattfindet) und der IO-Druck ist nicht groß.

【Ursachenanalyse】

Nehmen wir einen Extremfall an, in dem die Masterdatenbank 200.000 Datenzeilen in einer 5-Millionen-Tabelle aktualisiert. Die Update-Anweisung erfordert einen vollständigen Tabellenscan. Im Zeilenformat werden 200.000 Update-Vorgänge im Binärprotokoll aufgezeichnet. In diesem Fall ist die Wiedergabe des SQL-Threads sehr langsam und jedes Update erfordert möglicherweise einen vollständigen Tabellenscan.

【Lösung】

Überprüfen Sie die Tabellenstruktur, um sicherzustellen, dass jede Tabelle einen expliziten Auto-Increment-Primärschlüssel hat, und erstellen Sie entsprechende Indizes.

〇Der Druck der Bibliothek selbst ist zu groß

【Ursachenanalyse】

Die Slave-Datenbank führt eine große Anzahl von Auswahlanforderungen aus, oder die meisten Auswahlanforderungen des Unternehmens werden an die Slave-Datenbankinstanz weitergeleitet, sogar eine große Anzahl von OLAP-Unternehmen, oder die Slave-Datenbank wird gesichert usw.
Zu diesem Zeitpunkt ist die CPU-Auslastung möglicherweise zu hoch, die IO-Auslastung kann zu hoch sein und die SQL-Thread-Anwendung kann zu langsam sein.

【Lösung】

Erstellen Sie weitere Slaves, um Lese-Anfragen zu verteilen und den Druck auf vorhandene Slave-Instanzen zu verringern.

０MyISAM-Speicher-Engine

Zu diesem Zeitpunkt wartet die Slave-Bibliothek Slave_SQL_Running_State Waiting for table level lock

【Ursachenanalyse】

MyISAM unterstützt nur Sperren auf Tabellenebene und Lesen und Schreiben können nicht gleichzeitig ausgeführt werden.
Wenn der entsprechende Wert von @@concurrent_insert festgelegt ist, kann die Master-Datenbank während der Auswahl gleichzeitig Einfügungen ausführen, während der Wiedergabe des SQL Thread der Slave-Datenbank jedoch nicht gleichzeitig. Wenn Sie interessiert sind, können Sie sich die Implementierung von MyISAM ansehen.

【Lösung】

Natürlich entscheide ich mich, es zu verzeihen. Da ich mich für MyISAM entschieden habe, sollte ich mental darauf vorbereitet sein. (Es gibt andere Szenarien, in denen die Verwendung von MyISAM in einer Replikationsstruktur nicht empfohlen wird.)
Wechseln Sie zu InnoDB.

Zusammenfassen:

Verwenden Sie SHOW SLAVE STATUS und SHOW PROCESSLIST um den aktuellen Status der Slave-Bibliothek anzuzeigen. (Dies kann auch durch eine Sicherung aus der Datenbank vermieden 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. E/A, CPU usw., und überlegen Sie, ob der Schreibvorgang des Masters und der eigene Druck des Slaves zu hoch sind.

Wenn keiner der oben genannten Gründe zutrifft, bitten Sie die DBA-Experten um Hilfe.

Natürlich ist Seconds_Behind_Master nicht unbedingt genau. In einigen Szenarien sind die Master-Slave-Daten inkonsistent, obwohl Seconds_Behind_Master 0 ist.
Das wird ein anderer Blog-Beitrag für ein anderes Mal.

Der vollständige Text ist vorbei.

Oben finden Sie ausführliche Informationen zu den Ursachen und Lösungen für die Verzögerung der MySQL-Master-Slave-Replikation. Weitere Informationen zur Verzögerung der MySQL-Master-Slave-Replikation finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!