Wie behebe ich hohe Replikatverzögerungen bei Amazon RDS für MySQL?

Lesedauer: 7 Minute

Ich möchte wissen, warum es zu Replikatverzögerungen kommt, wenn ich Amazon Relational Database Service (Amazon RDS) für MySQL verwende.

Kurzbeschreibung

Da Amazon RDS für MySQL die asynchrone Replikation verwendet, kann das Replikat manchmal nicht mit der primären DB-Instance fortfahren und verursacht eine Replikationsverzögerung.

Um eine Replikationsverzögerung zu überwachen, verwende ein RDS für MySQL-Lesereplikat mit positionsbasierter Replikation der binären Protokolldateien.

Überprüfe in Amazon CloudWatch die Metrik ReplicaLag für Amazon RDS. Die Metrik ReplicaLag gibt den Wert des Felds Seconds_Behind_Master des Befehls SHOW SLAVE STATUS an.

Das Feld Seconds_Behind_Master zeigt den aktuellen Zeitstempel auf der Replikat-DB-Instance. Außerdem wird der ursprüngliche Zeitstempel 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 Funktionsweise der Threads findest du unter Replication threads (Replikations-Threads) auf der MySQL-Website. Wenn es bei der Replikation zu einer Verzögerung kommt, ermittle, ob das IO_THREAD-Replikat oder das SQL_THREAD-Replikat die Verzögerung verursacht. Du kannst dann die Ursache der Verzögerung identifizieren.

Lösung

Identifikation des Replikations-Threads, der verzögert ist

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.

Führe den Befehl SHOW SLAVE 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

Hinweis: 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ären Protokollen hinterher.

Normalerweise verursacht IO_THREAD keine großen Replikationsverzögerungen, da IO_THREAD nur die binären Protokolle von der primären oder Quell-Instance liest. 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 ungefähr 2 Stunden.

Um Verzögerungen zu minimieren, kannst du das langsame Abfrageprotokoll auf der primären Instance überwachen. Du kannst auch Anweisungen mit langer Ausführungszeit auf kleinere Anweisungen oder Transaktionen reduzieren.

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. Weitere Informationen findest du unter DB-Instance-Klassen.

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

Die MySQL-Replikation ist standardmäßig Single-Thread-fähig. Wenn du also 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.

Replikation mit mehreren Threads (parallele Replikation) ist bei MySQL 5.6 und späteren Versionen verfügbar. Weitere Informationen zur Replikation mit mehreren Threads findest du unter Binary logging options and variables (Binärprotokollierungsoptionen und Variablen) auf der MySQL-Website.

Die Replikation mit mehreren Threads kann zu Replikationslücken führen. Beispielsweise ist die Replikation mit mehreren Threads keine bewährte Methode, wenn du die Replikationsfehler überspringst, da es schwierig ist, die übersprungenen Transaktionen zu identifizieren. 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 auf 0 änderst, deaktiviere auch die Fähigkeit des MySQL-Servers, das binäre Protokoll mit der Festplatte zu synchronisieren. Stattdessen leert das Betriebssystem gelegentlich die binären Protokolle auf die Festplatte.

Um den Leistungsmehraufwand zu reduzieren, der erforderlich ist, um die binären Protokolle bei jedem Commit mit der Festplatte zu synchronisieren, deaktiviere die Synchronisierung binärer Protokolle. 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

Der SQL-Thread führt in den folgenden Szenarien einen vollständigen Tabellenscan durch, wenn er repliziert:

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

Das passiert, 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

Amazon RDS erstellt einen DB-Snapshot, um das Lesereplikat einer primären MySQL-Instance zu erstellen. 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 du die Replikation eingerichtet hast, 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 ist dann ein inkrementelles 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 Lesreplikat zu reduzieren, kannst du Operationen ausführen, die Scans ganzer Tabellen verwenden. Führe beispielsweise mysqldump auf dem Lesereplikat für bestimmte Tabellen oder Datenbanken aus, sodass Amazon RDS alle gesicherten Tabellendaten von Amazon Simple Storage Service (Amazon S3) priorisiert.

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