Wie behebe ich hohe Binlog-Replikatverzögerungen mit Amazon RDS für MySQL und Aurora MySQL?

Kurzbeschreibung

Amazon RDS für MySQL verwendet asynchrone Replikation, wobei ein Replikat manchmal hinter der primären DB-Instance zurückbleibt. Um die Replikationsverzögerung zu überwachen, verwende Amazon RDS für MySQL-Lesereplikat mit positionsbasierter Replikation in binären Protokolldateien.

Verwende Amazon CloudWatch, um die Metrik ReplicaLag für Amazon RDS zu überprüfen. Die Metrik ReplicaLag gibt den Wert des Felds Seconds_Behind_Master des Befehls SHOW SLAVE STATUS an.

Für Aurora misst die AuroraBinlogReplicaLag-Metrik die Replikatverzögerung zwischen Aurora-DB-Clustern, die Binärprotokolle verwenden.

Hinweis: In MySQL-Versionen 8.0.22 und höher hat der Befehl SHOW REPLICA STATUS den Befehl SHOW SLAVE STATUS ersetzt. Weitere Informationen findest du unter SHOW SLAVE | REPLICA STATUS-Anweisung auf der MySQL-Website.

Das Feld Seconds_Behind_Master zeigt die aktuelle Verzögerung in Sekunden an, mit der sich die Replikat-DB-Instance hinter der Quell-Instance befindet. Außerdem wird der ursprüngliche Wert angezeigt, der auf der primären DB-Instance für das Ereignis protokolliert wurde, das auf der Replikat-DB-Instance verarbeitet wird.

Die MySQL-Replikation verwendet den binären Protokoll-Dump, den Replikations-E/A-Empfänger und Replikations-SQL-Anwender-Threads. Weitere Informationen zur Arbeitsweise der Threads findest du unter Replikations-Threads auf der MySQL-Website. Wenn es bei der Replikation zu einer Verzögerung kommt, überprüfe, ob das IO_THREAD-Replikat oder das SQL_THREAD-Replikat die Verzögerung verursacht. Dann kannst du die Ursache für die Verzögerung ermitteln.

Lösung

Identifikation des Replikations-Threads, der verzögert ist

Führe die folgenden Schritte aus:

1. Um zu ermitteln, wohin die Quell- oder primäre DB-Instance Binärprotokolle schreibt, führe den Befehl SHOW MASTER STATUS auf der primären DB-Instance aus:

   mysql> SHOW MASTER STATUS;

   Beispielausgabe:

   +----------------------------+----------+--------------+------------------+-------------------+
   | File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
   +----------------------------+----------+--------------+------------------+-------------------+
   | mysql-bin.066552           |      521 |              |                  |                   |
   +----------------------------+----------+--------------+------------------+-------------------+
   1 row in set (0.00 sec)

   Hinweis: In der vorherigen Beispielausgabe schreibt die Quell- oder primäre DB-Instance die Binärprotokolle in die Datei mysql-bin.066552.
2. Um den Ausgabestatus zu ermitteln, führe den Befehl SHOW SLAVE STATUS oder SHOW REPLICA STATUS auf der Replikat-DB-Instance aus:

   mysql> SHOW SLAVE STATUS\G;

Beispielausgabe 1:

*************************** 1. row ***************************
Master_Log_File: mysql-bin.066548
Read_Master_Log_Pos: 10050480
Relay_Master_Log_File: mysql-bin.066548
Exec_Master_Log_Pos: 10050300
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

In der vorherigen Beispielausgabe zeigt Master_Log_File: mysql-bin.066548, dass das IO_THREAD-Replikat aus der binären Protokolldatei mysql-bin.066548 liest. Die primäre DB-Instance schreibt die binären Protokolle in die Datei mysql-bin.066552. Das IO_THREAD-Replikat liegt vier Binlogs dahinter. Da Relay_Master_Log_File jedoch mysql-bin.066548 ist, liest das SQL_THREAD-Replikat aus derselben Datei wie IO_THREAD. Das SQL_THREAD-Replikat behält die Geschwindigkeit bei, aber das Replikat IO_THREAD verzögert sich.

Beispielausgabe 2:

*************************** 1. row ***************************
Master_Log_File: mysql-bin.066552
Read_Master_Log_Pos: 430
Relay_Master_Log_File: mysql-bin.066530
Exec_Master_Log_Pos: 50360
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Die vorhergehende Beispielausgabe zeigt, dass die Protokolldatei der primären Instance mysql-bin.066552 ist. Der IO_THREAD behält die Geschwindigkeit bei der primären DB-Instance bei. In der Replikatausgabe führt der SQL-Thread Relay_Master_Log_File: mysql-bin.066530 aus. Infolgedessen hinkt SQL_THREAD 22 Binärprotokollen hinterher.

Da IO_THREAD die Binärprotokolle nur von der Primär- oder Quell-Instance liest, verursacht IO_THREAD normalerweise keine großen Replikationsverzögerungen. Netzwerkkonnektivität und Netzwerklatenz können sich jedoch auf die Geschwindigkeit der Lesevorgänge zwischen den Servern auswirken. Eine hohe Bandbreitennutzung kann dazu führen, dass das IO_THREAD-Replikat langsamer arbeitet.

Wenn das SQL_THREAD-Replikat die Replikationsverzögerungen verursacht, verwende die folgenden Schritte zur Problembehandlung.

Schreibabfragen mit langer Ausführungszeit auf der primären Instance

Schreibabfragen mit langer Ausführungszeit auf der primären DB-Instance, deren Ausführung auf der Replikat-DB-Instance die gleiche Zeit in Anspruch nimmt, können die Anzahl der seconds_behind_master erhöhen. Wenn beispielsweise die Ausführung einer Änderung an der primären Instance 1 Stunde dauert, beträgt die Verzögerung 1 Stunde. Wenn die Änderung am Replikat ebenfalls 1 Stunde in Anspruch nimmt, beträgt die Gesamtverzögerung 2 Stunden.

Um Verzögerungen zu minimieren, überwache das langsame Abfrageprotokoll auf der primären Instance und identifiziere Abfragen, die optimiert werden müssen. Du kannst auch Anweisungen mit langer Ausführungszeit auf kleinere Anweisungen oder Transaktionen reduzieren.

Um Leistungsprobleme im Zusammenhang mit Binlogs zu beheben, analysiere die Warteereignisse in Performance Insights, um weitere Informationen zu erhalten. Du kannst auch die Isolationsstufen anpassen.

Unzureichende Größe oder unzureichender Speicher der DB-Instance-Klasse

Wenn die Klasse oder Speicherkonfiguration der Replikat-DB-Instance niedriger als bei der primären Instance ist, kann es sein, dass das Replikat aufgrund unzureichender Ressourcen gedrosselt wird. Das Replikat kann die Anzahl der Änderungen auf der primären Instance nicht beibehalten.

Um dieses Problem zu beheben, stelle sicher, dass der DB-Instance-Typ des Replikats mit dem der primären DB-Instance identisch oder höher ist. Damit die Replikation effektiv funktioniert, benötigt jedes Lesereplikat dieselbe Anzahl an Rechen- und Speicherressourcen wie die Quell-DB-Instance.

Parallele Abfragen werden auf der primären DB-Instance ausgeführt

Standardmäßig ist die MySQL-Replikation Single-Thread-fähig. Wenn du Abfragen parallel auf der primären Instance ausführst, werden die Abfragen in serieller Reihenfolge auf das Replikat übertragen. Wenn eine große Anzahl von Schreibvorgängen in die Quell-Instance parallel erfolgt, verwenden Schreibvorgänge im Lesereplikat einen einzigen SQL_THREAD zur Serialisierung. Dann kann es zu einer Verzögerung zwischen der Quell-DB-Instance und dem Lesereplikat kommen.

In den MySQL-Versionen 8.0.27 und höher ist der Standardwert für replica_parallel_workers 4. Dieser Wert bedeutet, dass Replikate standardmäßig über mehrere Threads verfügen. In ähnlicher Weise erfolgt die Replikation für Aurora MySQL Version 3.04 und höher standardmäßig über mehrere Threads, wobei der Wert replica_parallel_workers auf 4 gesetzt ist. Du kannst diesen Parameter in deiner benutzerdefinierten Parametergruppe ändern.

Weitere Informationen zur Replikation mit mehreren Threads findest du unter Binärprotokollierungsoptionen und Variablen auf der MySQL-Website.

Die Replikation mit mehreren Threads kann zu Replikationslücken führen. Beispielsweise ist es schwierig, Transaktionen zu identifizieren, die du überspringst. Wenn du die Multithread-Replikation verwendest, empfiehlt es sich nicht, die Replikationsfehler zu überspringen. Dies kann zu Lücken in der Datenkonsistenz zwischen der primären und der replizierten DB-Instance führen.

Binäre Protokolle, die mit der Festplatte auf der Replikat-DB-Instance synchronisiert werden

Wenn du automatische Backups auf dem Replikat aktivierst, entsteht ein Mehraufwand für die Synchronisierung der binären Protokolle mit der Festplatte auf dem Replikat. Der Standardwert des Parameters sync_binlog ist auf 1 gesetzt. Wenn du den Wert zu 0 änderst, kann der MySQL-Server das Binärprotokoll nicht mit der Festplatte synchronisieren. Stattdessen leert das Betriebssystem (OS) gelegentlich die Binärprotokolle auf die Festplatte.

Um den Leistungsmehraufwand zu reduzieren, der erforderlich ist, um die Binärprotokolle bei jedem Commit mit der Festplatte zu synchronisieren, deaktiviere die Synchronisierung der Binärprotokolle. Aber wenn es einen Stromausfall gibt oder das Betriebssystem abstürzt, werden einige der Übernahmen möglicherweise nicht mit den binären Protokollen synchronisiert. Eine Asynchronisierung kann sich auf die Features zur Point-in-Time-Wiederherstellung (PITR) auswirken. Weitere Informationen findest du unter sync_binlog auf der MySQL-Website.

Binlog_format ist auf ROW gesetzt

Wenn die Replikation in den folgenden Szenarien stattfindet, führt der SQL-Thread einen vollständigen Tabellenscan durch:

• Das binlog_format auf der primären DB-Instance ist auf ROW gesetzt.
• Die Quelltabelle hat keinen Primärschlüssel.

Dieser Scan erfolgt, weil der Standardwert des Parameters slave_rows_search_algorithms TABLE_SCAN,INDEX_SCAN ist.

Um dieses Problem vorübergehend zu beheben, ändere den Suchalgorithmus in INDEX_SCAN,HASH_SCAN, um den Mehraufwand eines vollständigen Tabellenscans zu reduzieren. Für eine dauerhaftere Lösung empfiehlt es sich, jeder Tabelle einen expliziten Primärschlüssel hinzuzufügen.

Weitere Informationen zum Parameter slave-rows-search-algorithms findest du unter slave_rows_search_algorithms auf der MySQL-Website.

Verzögerung bei der Replikaterstellung (gilt für RDS MySQL)

Um das Lesereplikat einer primären MySQL-Instance zu erstellen, erstellt Amazon RDS einen DB-Snapshot. Anschließend stellt Amazon RDS den Snapshot wieder her, um eine neue DB-Instance zu erstellen, und stellt eine Replikation zwischen den beiden her.

Nachdem Amazon RDS die Replikation eingerichtet hat, tritt eine Verzögerung auf, wenn Amazon RDS ein Backup der primären DB-Instance erstellt. Erstelle ein manuelles Backup, bevor du die Replikaterstellung aufrufst, um die Verzögerung zu minimieren. Der DB-Snapshot wird dann zu einem inkrementellen Backup.

Wenn du ein Lesereplikat aus einem Snapshot wiederherstellst, wartet das Replikat nicht darauf, dass alle Daten von der Quelle übertragen wurden. Die Replikat-DB-Instance ist für die Ausführung der DB-Operationen verfügbar. Die vorhandenen Amazon Elastic Block Store (Amazon EBS)-Snapshots erstellen im Hintergrund ein neues Volume.

Hinweis: Bei Amazon RDS für MySQL-Replikaten (Amazon EBS-gestützte Volumes) kann die Replikatverzögerung zunächst zunehmen, da verzögertes Laden die Replikationsleistung beeinträchtigen kann.

Um die Auswirkungen des verzögerten Ladens auf Tabellen für das neue Lesereplikat zu reduzieren, führe die Operationen aus, die Scans ganzer Tabellen verwenden. Führe beispielsweise mysqldump auf dem Lesereplikat für bestimmte Tabellen oder Datenbanken aus, um alle gesicherten Tabellendaten von Amazon Simple Storage Service (Amazon S3) zu priorisieren.

Du kannst auch das On-Demand-Feature InnoDB-Cache-Warming für RDS MySQL verwenden. Das Feature InnoDB-Cache-Warming speichert den Status des Pufferpools auf der Festplatte in einer Datei namens ib_buffer_pool im InnoDB-Datenverzeichnis. Da Amazon RDS den aktuellen Status des primären DB-Instance-Puffer-Pools ausgibt, bevor du das Lesereplikat erstellst, verbessert sich die Leistung mit der geringeren Last. Lade dann den Pufferpool neu, nachdem du ein Replikat erstellt hast.

Auswirkungen von Replikatverzögerungen

Eine große Replikatverzögerung auf der Replikat-Instance oder im Cluster kann zu einer Anhäufung von Binärprotokollen in der Quelle führen. Um möglichen Problemen vorzubeugen, solltest du die Größe der Binlog-Dateien und den Speicherplatzverbrauch überwachen und verwalten. Für RDS kannst du die BinLogDiskUsage-Metrik auf der Quell-DB-Instance überwachen.

Konfiguriere den optimalen Aufbewahrungszeitraum für Binlogs, um die Point-in-Time-Wiederherstellungsfunktionen mit der Speichernutzung in einer RDS MySQL-Instance und in Aurora in Einklang zu bringen. Um Binlogs für Replikation und Wiederherstellung zu nutzen, solltest du dich außerdem mit den Benennungskonventionen und dem Rotationsverhalten von Binlog-Dateien vertraut machen. Führe den Befehl SHOW BINARY LOGS aus, um eine Liste der verfügbaren Protokolle abzurufen.

Ähnliche Informationen

Arbeiten mit der MySQL-Replikation in Amazon RDS

Arbeiten mit MySQL Read Replicas

Replikation von binären Protokollen für Aurora MySQL optimieren