So beheben Sie die durch MySQL DDL verursachte Synchronisierungsverzögerung

Vorwort

Verfassen einer Fallanalyse, hauptsächlich einer Einführung und Empfehlung von Tools. Der Synchronisierungsmechanismus von MySQL ist relativ einfach. Die auf der Master-Datenbank ausgeführten DML- und DDL-Befehle werden erneut auf der Slave-Datenbank ausgeführt. Daher dauert die Ausführung von DDL, die auf der Master-Datenbank 10 Minuten dauert, auf der Slave-Datenbank theoretisch mindestens 10 Minuten. Dies bedeutet, dass die Synchronisierung der Slave-Datenbank um mehr als 10 Minuten verzögert wird und die Synchronisierung erst fortgesetzt wird, nachdem der DDL ausgeführt wurde.

Lösung

Aus Sicht der MySQL-Synchronisierungsprinzipien besteht der Hauptgrund darin, dass der DDL-Vorgang allein zu lange dauert, wodurch die Slave-Datenbank im Master-Zustand hängen bleibt. Dann ist es einfach, sich eine Lösung für dieses Problem auszudenken: „Zerlegen“ Sie die DDL-Operation, teilen Sie eine große Operation (dasselbe gilt für große Transaktionen) in mehrere kleine Operationen auf und reduzieren Sie die Zeit einer einzelnen Operation.

Zum „Disassemblieren“ von DDL-Operationen werden im Allgemeinen MySQL Online-DDL-Tools wie pt-osc, facebook-osc, oak-online-alter-table, gh-ost usw. verwendet. Die Ideen dieser Tools sind ähnlich. Sie erstellen eine Spiegeltabelle der Quelltabelle, führen zuerst die Änderung der Tabellenstruktur aus und synchronisieren dann die vollständigen Daten und inkrementellen Daten der Quelltabelle. Dadurch kann die Synchronisierungsverzögerung vermieden werden, die durch einen einzelnen DDL-Vorgang verursacht wird.

Tool-Einführung

In diesem Artikel wird gh-ost vorgestellt, ein von Github verwaltetes MySQL-Online-DDL-Tool. Es verwendet ebenfalls das Spiegeltabellenformat, verzichtet jedoch auf die Verwendung ineffizienter Trigger und extrahiert stattdessen die erforderlichen inkrementellen Daten aus dem Binärprotokoll, um die Datenkonsistenz zwischen der Spiegeltabelle und der Quelltabelle aufrechtzuerhalten. Der gesamte Online-DDL-Vorgang blockiert das Lesen und Schreiben nur für einige Sekunden, wenn die Quelltabelle und die Spiegeltabelle schließlich umbenannt werden.

So funktioniert es

Der Betriebsablauf von go-ost ​​ist wie folgt:

• Erstellen Sie im Master eine Spiegeltabelle (_tablename_gho) und eine Heartbeat-Tabelle (_tablename_ghc).
• Schreiben Sie den Fortschritt und die Zeit von Online-DDL in die Heartbeat-Tabelle.
• Führen Sie eine ALTER-Operation an der Spiegeltabelle durch.
• Als Slave verkleiden und mit einer Slave-Instanz des Masters verbinden, um Binlog-Informationen zu erhalten (standardmäßig mit dem Slave verbinden, kann aber auch mit dem Master verbinden).
• Schließen Sie den Datenabgleich der Spiegeltabelle im Master ab:
  • Daten aus der Quelltabelle in die Spiegeltabelle kopieren;
  • Führen Sie inkrementelle Datenänderungen basierend auf Binlog-Informationen durch;
• Sperren Sie die Quelltabelle.
• Bestätigen Sie die Zeit in der Heartbeat-Tabelle, um sicherzustellen, dass die Daten vollständig synchronisiert sind.
• Ersetzen Sie die Quelltabelle durch die Spiegeltabelle.
• Online-DDL abgeschlossen.
• Funktionen oder Features, die in Zukunft unterstützt werden:
  • Unterstützt Fremdschlüssel.
  • Wenn ein Ghost-Prozess unerwartet beendet wird, können Sie einen neuen Prozess starten, um den Online-DDL fortzusetzen.

Der Inhalt von _tablename_ghc ist wie folgt:

Nutzungsbeschränkung

• Das Binlog-Format muss Zeilen verwenden und binlog_row_image muss VOLL sein.
• Die erforderlichen Berechtigungen sind SUPER, REPLICATION CLIENT, REPLICATION SLAVE auf *.* und ALL auf dbname.*
  • Wenn Sie bestätigen, dass das Binärprotokoll im Zeilenformat vorliegt, können Sie -assume-rbr hinzufügen und die Superberechtigung ist nicht mehr erforderlich.
  • TiDB kann nicht verwendet werden, da es keine REPLICATION-bezogenen Berechtigungen unterstützt.
• Fremdschlüssel werden nicht unterstützt.
  • Unabhängig davon, ob es sich bei der Quelltabelle um eine Primärtabelle oder eine untergeordnete Tabelle handelt, kann sie nicht verwendet werden.
• Trigger werden nicht unterstützt.
• Primärschlüssel, die JSON-Spalten enthalten, werden nicht unterstützt.
• Die Migrationstabelle muss einen explizit definierten Primärschlüssel oder einen nicht leeren eindeutigen Index haben.
• Das Migrationstool berücksichtigt keine Groß- und Kleinschreibung. Wenn eine Tabelle mit demselben Namen, aber anderer Groß- und Kleinschreibung vorhanden ist, kann sie nicht migriert werden.
• Wenn der Primärschlüssel oder der nicht leere eindeutige Index der Migrationstabelle einen Aufzählungstyp enthält, wird die Migrationseffizienz erheblich reduziert.

Hinweise zur Verwendung

• Wenn die Quelltabelle viele Daten enthält, versuchen Sie, diese stapelweise zu löschen.
  • aus Tabelle tablename_old löschen, Limit 5000;
  • Alternativ können Sie während der Leerlaufzeit die Tabellendaten mit „truncate table tablename_old“ löschen und dann die Tabelle löschen.
• Wenn Sie mehrere gh-ost-Server auf einer einzelnen MySQL-Instanz starten, um Online-DDL-Operationen an mehreren Tabellen durchzuführen, müssen Sie den Parameter -replica-server-id angeben.
• Behalten Sie stets den verfügbaren Speicherplatz im Auge, insbesondere wenn Sie mit großen Tabellen arbeiten.
  • Die Spiegeltabelle von gh-ost enthält alle Daten der Quelltabelle und benötigt doppelt so viel Speicherplatz.
  • gh-ost generiert während des Betriebs eine große Menge an Binärprotokollen, und binlog_row_image muss VOLL sein, was viel Speicherplatz beansprucht.
• Der Vorgang zum Umbenennen der Spalte kann Probleme verursachen. Erwägen Sie, die Lösch- und Hinzufügungsvorgänge zu kombinieren.

Anwendungsbeispiele

Das Installationspaket steht auf der offiziellen GitHub-Website zum Download bereit, siehe Versionshinweis.

Der eigentliche Befehl kann wie folgt aufgerufen werden (Zeilenmodus ist aktiviert):

gh-ost --max-load=Threads_running=50 \
            --critical-load=Ausführende Threads=100 \
            --chunk-size=3000 --user="temp" --password="test" --host=10.10.1.10 \
            --allow-on-master --database="sbtest" --table="sbtest1" \
            --alter="engine=innodb" --cut-over=default \
            --exact-rowcount --concurrent-rowcount --default-retries=120 \
            --timestamp-old-table -assume-rbr --panic-flag-file=/tmp/ghost.panic.flag \
            --ausführen

Einige Parameterbeschreibungen

Der obige Bestellinhalt ist maßgebend:

max-load=Threads_running=50 Wenn mehr als 50 Clients SQL-Abfragen ausführen, werden Online-DDL-Vorgänge angehalten. critical-load=Threads_running=100 Wenn mehr als 100 Clients SQL-Abfragen ausführen, werden Online-DDL-Vorgänge unterbrochen. chunk-size=3000 Jeder Synchronisierungsvorgang verarbeitet 3000 Datenzeilen. allow-on-master Ermöglicht die Ausführung aller mit Online-DDL verbundenen Vorgänge in der Masterdatenbank. alter Online-DDL-Vorgänge erfordern nur einen Teil der alter-Anweisung (den Teil in eckigen Klammern).
                                     Beispiel: alter table sbtest.sbtest1 [add column t int not NULL]
cut-over=default Automatisches Wechseln zwischen der Spiegeltabelle und der Quelltabelle, nachdem die Datensynchronisierung abgeschlossen ist exact-rowcount Genaue Berechnung der Zeilenanzahl, um einen genaueren Fortschritt zu gewährleisten timestamp-old-table Zeitstempel zum Benennen der alten Tabelle verwenden assume-rbr Neustart des Slave-Threads und Überprüfung des Zeilenformats überspringen, nach dem Setzen von panic-flag-file ist keine Super-Berechtigung erforderlich Nach dem Erstellen dieser Datei wird eine Unterbrechung des Online-DDL-Vorgangs erzwungen

Zusätzlich zu diesen Parametern bietet gh-ost auch viele Möglichkeiten, Online-DDL-Operationen extern anzuhalten oder abzubrechen. Detaillierte Informationen können mit dem Befehl gh-ost --help angezeigt werden.

Ausgabebeispiel

# Migriere `sbtest`.`sbtest1`; Ghost-Tabelle ist `sbtest`.`_sbtest1_gho`
# Migration von 10.10.1.10:3306; Überprüfung von 10.10.1.10:3306; Ausführung auf localhost-debian
# Migration gestartet am Do Jul 30 11:30:17 +0800 2020
# Blockgröße: 3000; max. Verzögerung in Millis: 1500 ms; DML-Batchgröße: 10; max. Auslastung: laufende Threads = 50; kritische Auslastung: laufende Threads = 100; Nice-Verhältnis: 0,000000
# Drossel-Zusatzflag-Datei: /tmp/gh-ost.throttle
# Panik-Flag-Datei: /tmp/ghost.panic.flag
# Bereitstellung auf Unix-Socket: /tmp/gh-ost.sbtest.sbtest1.sock
Kopie: 0/9863066 0,0 %; Angewendet: 0; Rückstand: 0/1000; Zeit: 0 s (gesamt), 0 s (Kopie); Streamer: mysql-bin.000050:31635038; Verzögerung: 0,03 s, Status: Migration; ETA: N/A
Kopie: 0/9863066 0,0 %; Angewendet: 0; Rückstand: 0/1000; Zeit: 1 s (gesamt), 1 s (Kopie); Streamer: mysql-bin.000050:31639503; Verzögerung: 0,03 s, Status: Migration; ETA: N/A
Kopieren: 69000/9999998 0,7 %; Angewendet: 0; Rückstand: 0/1000; Zeit: 2 s (gesamt), 2 s (kopieren); Streamer: mysql-bin.000050:44815698; Verzögerung: 0,03 s, Status: Migration; ETA: 4 m49 s
Kopieren: 135000/9999998 1,4 %; Angewendet: 0; Rückstand: 0/1000; Zeit: 3 s (gesamt), 3 s (kopieren); Streamer: mysql-bin.000050:57419220; Verzögerung: 0,03 s, Status: Migration; ETA: 3 m 39 s
Kopieren: 195000/9999998 2,0 %; Angewendet: 0; Rückstand: 0/1000; Zeit: 4 s (gesamt), 4 s (kopieren); Streamer: mysql-bin.000050:68877374; Verzögerung: 0,03 s, Status: Migration; ETA: 3 m 21 s
......(ausgelassen)
Kopie: 9729000/9999998 97,3 %; Angewendet: 0; Rückstand: 0/1000; Zeit: 3 min 16 s (gesamt), 3 min 16 s (Kopie); Streamer: mysql-bin.000057:8595335; Verzögerung: 0,04 s, Status: Migration; ETA: 5 s
[2020/07/30 11:33:32] [info] binlogsyncer.go:723 rotieren nach (mysql-bin.000057, 4)
Kopie: 9774000/9999998 97,7 %; Angewendet: 0; Rückstand: 0/1000; Zeit: 3 min 17 s (gesamt), 3 min 17 s (Kopie); Streamer: mysql-bin.000057:17190073; Verzögerung: 0,03 s, Status: Migration; ETA: 4 s
[2020/07/30 11:33:32] [info] binlogsyncer.go:723 rotieren nach (mysql-bin.000057, 4)
Kopie: 9822000/9999998 98,2 %; Angewendet: 0; Rückstand: 0/1000; Zeit: 3 min 18 s (gesamt), 3 min 18 s (Kopie); Streamer: mysql-bin.000057:26357495; Verzögerung: 0,04 s, Status: Migration; ETA: 3 s
Kopie: 9861000/9999998 98,6 %; Angewendet: 0; Rückstand: 0/1000; Zeit: 3 min 19 s (gesamt), 3 min 19 s (Kopie); Streamer: mysql-bin.000057:33806865; Verzögerung: 0,03 s, Status: Migration; ETA: 2 s
Kopie: 9903000/9999998 99,0 %; Angewendet: 0; Rückstand: 0/1000; Zeit: 3 min 20 s (gesamt), 3 min 20 s (Kopie); Streamer: mysql-bin.000057:41828922; Verzögerung: 0,03 s, Status: Migration; ETA: 1 s
Kopie: 9951000/9999998 99,5 %; Angewendet: 0; Rückstand: 0/1000; Zeit: 3 min 21 s (gesamt), 3 min 21 s (Kopie); Streamer: mysql-bin.000057:50996347; Verzögerung: 0,03 s, Status: Migration; ETA: 0 s
Kopie: 9999998/9999998 100,0 %; Angewendet: 0; Rückstand: 0/1000; Zeit: 3 min 22 s (gesamt), 3 min 21 s (Kopie); Streamer: mysql-bin.000057:60354465; Verzögerung: 0,03 s, Status: Migriere; ETA: fällig
# Migriere `sbtest`.`sbtest1`; Ghost-Tabelle ist `sbtest`.`_sbtest1_gho`
# Migration von 10.10.1.10:3306; Überprüfung von 10.10.1.10:3306; Ausführung von onlocalhost-debian
# Migration gestartet am Do Jul 30 11:30:17 +0800 2020
# Blockgröße: 3000; max. Verzögerung in Millis: 1500 ms; DML-Batchgröße: 10; max. Auslastung: laufende Threads = 50; kritische Auslastung: laufende Threads = 100; Nice-Verhältnis: 0,000000
# Drossel-Zusatzflag-Datei: /tmp/gh-ost.throttle
# Panik-Flag-Datei: /tmp/ghost.panic.flag
# Bereitstellung auf Unix-Socket: /tmp/gh-ost.sbtest.sbtest1.sock
Kopie: 9999998/9999998 100,0 %; Angewendet: 0; Rückstand: 0/1000; Zeit: 3 min 23 s (gesamt), 3 min 21 s (Kopie); Streamer: mysql-bin.000057:60359997; Verzögerung: 0,03 s, Status: Migriere; ETA: fällig
[2020/07/30 11:33:41] [info] binlogsyncer.go:164 Syncer wird geschlossen ...
[2020/07/30 11:33:41] [Fehler] binlogstreamer.go:77 Synchronisierung schließen mit Fehler: Synchronisierung wird geschlossen ...
[2020/07/30 11:33:41] [info] binlogsyncer.go:179 syncer ist geschlossen

Sie können sehen, dass der Protokollinhalt einen detaillierten Fortschrittsprozentsatz und die verbleibende Zeit der Migration ausgibt, was sehr praktisch ist, um die Endzeit der Wartung abzuschätzen und den Fortschritt der DDL-Ausführung zu überprüfen.

Hinweise zur Verwendung von MySQL für die Tencent Cloud-Datenbank

• Das Standard-binlog_row_image von Tencent Cloud Database MySQL ist MINIMAL. Sie müssen es vor der Verwendung in der Steuerung aktiv auf FULL einstellen (Online-Änderung, sofort wirksam).
• Bei Tencent Cloud Database, Alibaba Cloud Database, MySQL im Container usw. können Portprobleme auftreten. Fügen Sie einfach den Parameter --aliyun-rds hinzu.
  • Die Fehlermeldung ähnelt „FATAL: Unerwarteter Datenbankport gemeldet“.
  • Verwandte Diskussionen finden Sie unter Probleme.
    

Um zusammenzufassen

gh-ost gibt bessere Informationen aus, migriert Daten effizienter und unterstützt mehr Funktionen als pt-osc und andere Tools. Die Probleme mit gh-ost (z. B. Speicherplatz) treten jedoch auch bei anderen Tools auf. Wenn Sie Latenzprobleme während DDL-Vorgängen vermeiden möchten, wird daher empfohlen, gh-ost Vorrang zu geben.

Oben finden Sie Einzelheiten zur Lösung der durch MySQL DDL verursachten Synchronisationsverzögerung. Weitere Informationen zur durch MySQL DDL verursachten Synchronisationsverzögerung finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!