Optimierung der binären Protokollreplikation für Aurora My SQL

Im Folgenden erfahren Sie, wie Sie die Leistung der Binärprotokollreplikation optimieren und damit verbundene Probleme in Aurora My beheben könnenSQL.

Tipp

Bei dieser Diskussion wird davon ausgegangen, dass Sie mit dem Mechanismus zur Replikation von SQL Binärprotokollen in My und seiner Funktionsweise vertraut sind. Hintergrundinformationen finden Sie unter Implementierung der Replikation in der SQL Dokumentation My.

Replikation von Binärprotokollen mit mehreren Threads

Bei der binären Protokollreplikation mit mehreren SQL Threads liest ein Thread Ereignisse aus dem Relay-Log und stellt sie in eine Warteschlange, damit SQL Worker-Threads sie anwenden können. Die SQL Worker-Threads werden von einem Koordinator-Thread verwaltet. Die binären Protokollereignisse werden nach Möglichkeit parallel angewendet.

Die Multithread-Binärprotokollreplikation wird in Aurora My SQL Version 3 und in Aurora My SQL Version 2.12.1 und höher unterstützt.

Wenn eine Aurora My SQL DB-Instance für die Verwendung der binären Protokollreplikation konfiguriert ist, verwendet die Replica-Instance standardmäßig die Single-Thread-Replikation für Aurora SQL My-Versionen unter 3.04. Um die Multithread-Replikation zu aktivieren, aktualisieren Sie den replica_parallel_workers-Parameter in Ihrer benutzerdefinierten Parametergruppe auf einen Wert größer als Null.

Für Aurora My SQL Version 3.04 und höher ist die Replikation standardmäßig Multithreading-fähig, wobei die Einstellung auf replica_parallel_workers gesetzt ist. 4 Sie können diesen Parameter in Ihrer benutzerdefinierten Parametergruppe ändern.

Die folgenden Konfigurationsoptionen helfen Ihnen bei der Optimierung der Multithread-Replikation. Informationen zur Verwendung finden Sie unter Optionen und Variablen für die Replikation und die binäre Protokollierung im SQL Referenzhandbuch.

Die optimale Konfiguration hängt von mehreren Faktoren ab. Beispielsweise wird die Leistung für die Binärprotokollreplikation von Ihren Datenbank-Workload-Merkmalen und der DB-Instanzklasse beeinflusst, auf der das Replikat ausgeführt wird. Wir empfehlen daher, alle Änderungen an diesen Konfigurationsparametern gründlich zu testen, bevor Sie neue Parametereinstellungen auf eine Produktionsinstanz anwenden:

• binlog_group_commit_sync_delay

• binlog_group_commit_sync_no_delay_count

• binlog_transaction_dependency_history_size

• binlog_transaction_dependency_tracking

• replica_preserve_commit_order

• replica_parallel_type

• replica_parallel_workers

In Aurora My SQL Version 3.06 und höher können Sie die Leistung für binäre Protokollreplikate verbessern, wenn Sie Transaktionen für große Tabellen mit mehr als einem sekundären Index replizieren. Diese Funktion führt einen Threadpool ein, um sekundäre Indexänderungen parallel auf ein Binlog-Replikat anzuwenden. Die Funktion wird durch den aurora_binlog_replication_sec_index_parallel_workers DB-Cluster-Parameter gesteuert, der die Gesamtzahl der parallel Threads steuert, die für die Anwendung der sekundären Indexänderungen verfügbar sind. Der Parameter ist standardmäßig auf 0 (deaktiviert) gesetzt. Für die Aktivierung dieser Funktion ist kein Instanzneustart erforderlich. Um diese Funktion zu aktivieren, beenden Sie die laufende Replikation, legen Sie die gewünschte Anzahl parallel Worker-Threads fest und starten Sie die Replikation dann erneut.

Sie können diesen Parameter auch als globale Variable verwenden, wobei n ist die Anzahl der parallel Worker-Threads:

SET global aurora_binlog_replication_sec_index_parallel_workers=n;

Optimierung der Binlog-Replikation (Aurora My SQL 2.10 und höher)

In Aurora My SQL 2.10 und höher wendet Aurora automatisch eine Optimierung an, die als Binlog-I/O-Cache bezeichnet wird, auf die binäre Protokollreplikation. Durch Zwischenspeichern der zuletzt festgeschriebenen Binlog-Ereignisse soll diese Optimierung die Leistung des Binlog-Dump-Threads verbessern und gleichzeitig die Auswirkungen auf Vordergrundtransaktionen auf der Binlog-Quell-Instance begrenzen.

Anmerkung

Der für diese Funktion verwendete Speicher ist unabhängig von der Einstellung My SQLbinlog_cache.

Diese Funktion gilt nicht für Aurora DB-Instances, die die db.t2- und db.t3-Instanceklassen verwenden.

Sie müssen keine Konfigurationsparameter anpassen, um diese Optimierung zu aktivieren. Insbesondere wenn Sie den Konfigurationsparameter in früheren SQL Versionen von Aurora My aurora_binlog_replication_max_yield_seconds auf einen Wert ungleich Null einstellen, setzen Sie ihn für Aurora My SQL 2.10 und höher wieder auf Null zurück.

Die Statusvariablen aurora_binlog_io_cache_reads und aurora_binlog_io_cache_read_requests sind in Aurora My SQL 2.10 und höher verfügbar. Diese Statusvariablen helfen Ihnen zu überwachen, wie oft die Daten aus dem Binlog-I/O-Cache gelesen werden.

• aurora_binlog_io_cache_read_requests zeigt die Anzahl der Binlog-I/O-Leseanfragen aus dem Cache an.

• aurora_binlog_io_cache_reads zeigt die Anzahl der Binlog-I/O-Lesevorgänge an, die Informationen aus dem Cache abrufen.

Die folgende SQL Abfrage berechnet den Prozentsatz der Binlog-Leseanfragen, die die zwischengespeicherten Informationen nutzen. In diesem Fall ist es umso besser, je näher das Verhältnis an 100 liegt.

mysql> SELECT
  (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS
    WHERE VARIABLE_NAME='aurora_binlog_io_cache_reads')
  / (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS
    WHERE VARIABLE_NAME='aurora_binlog_io_cache_read_requests')
  * 100
  as binlog_io_cache_hit_ratio;
+---------------------------+
| binlog_io_cache_hit_ratio |
+---------------------------+
|         99.99847949080622 |
+---------------------------+

Die Binlog-I/O-Cache-Funktion enthält auch neue Metriken im Zusammenhang mit den Binlog-Dump-Threads. Dump-Threads sind die Threads, die erstellt werden, wenn neue Binlog-Replikate mit der Binlog-Quell-Instance verbunden sind.

Die Metriken des Dump-Threads werden alle 60 Sekunden mit dem Präfix in das Datenbankprotokoll gedruck [Dump thread metrics]. Die Metriken umfassen Informationen für jedes Binlog-Replikat wie Secondary_id, Secondary_uuid, den Namen der Binlog-Datei und die Position, die jedes Replikat liest. Zu den Metriken gehört auch Bytes_behind_primary, das den Abstand in Byte zwischen Replikationsquelle und Replikat darstellt. Diese Metrik misst die Verzögerung des Replikat-I/O-Threads. Diese Zahl unterscheidet sich von der Verzögerung des SQL Replikat-Applier-Threads, die durch die seconds_behind_master Metrik im Binlog-Replikat dargestellt wird. Sie können feststellen, ob Binlog-Replikate die Quelle aufholen oder zurückfallen, indem Sie überprüfen, ob die Entfernung abnimmt oder zunimmt.

Optimierung der Binlog-Replikation (Aurora My SQL Version 2 bis 2.09)

Um die binäre Protokollreplikation für Aurora My zu optimierenSQL, passen Sie die folgenden Optimierungsparameter auf Clusterebene an. Diese Parameter helfen Ihnen, das richtige Gleichgewicht zwischen Latenz für die Binärprotokoll-Quell-Instance und Replikationsverzögerung anzugeben.

• aurora_binlog_use_large_read_buffer

• aurora_binlog_read_buffer_size

• aurora_binlog_replication_max_yield_seconds

Anmerkung

Für My SQL 5.7-kompatible Cluster können Sie diese Parameter in Aurora My SQL Version 2 bis 2.09.* verwenden. In Aurora My SQL 2.10.0 und höher werden diese Parameter durch die Binlog-I/O-Cache-Optimierung ersetzt und Sie müssen sie nicht verwenden.

Überblick über den großen Lese-Puffer und die Max-Yield-Optimierung

Es kann zu einer verringerten Leistung der binären Protokollreplikation kommen, wenn der binäre Protokoll-Dump-Thread auf das Aurora Cluster-Volume zugreift, während der Cluster eine hohe Anzahl von Transaktionen verarbeitet Sie können die Parameter aurora_binlog_use_large_read_buffer, aurora_binlog_replication_max_yield_seconds und aurora_binlog_read_buffer_size verwenden, um diese Art von Konflikt zu minimieren.

Angenommen, Sie haben eine Situation, in der aurora_binlog_replication_max_yield_seconds auf größer als 0 gesetzt ist und die aktuelle Binlog-Datei des Dump-Threads aktiv ist. In diesem Fall wartet der binäre Protokoll-Dump-Thread bis zu einer bestimmten Anzahl von Sekunden, bis die aktuelle Binärprotokolldatei durch Transaktionen gefüllt wird. Diese Wartezeit vermeidet Konflikte, die sich aus der Replikation jedes Binärprotokoll-Ereignisses ergeben können. Dies erhöht jedoch die Replica-Verzögerung für binäre Protokollreplikationen. Diese Replicas können um die gleiche Anzahl von Sekunden wie die aurora_binlog_replication_max_yield_seconds-Einstellung hinter die Quelle zurückfallen.

Die aktuelle Binärprotokolldatei ist die Binärprotokolldatei, die der Dump-Thread gerade liest, um die Replikation durchzuführen. Wir berücksichtigen, dass eine Binärprotokolldatei aktiv ist, wenn die Binärprotokolldatei aktualisiert oder geöffnet wird, um durch eingehende Transaktionen aktualisiert zu werden. Nachdem Aurora My die aktive Binlog-Datei SQL gefüllt hat, SQL erstellt My eine neue Binlog-Datei und wechselt zu dieser. Die alte Binärprotokolldatei wird inaktiv. Es wird nicht mehr von eingehenden Transaktionen aktualisiert.

Anmerkung

Bevor Sie diese Parameter anpassen, messen Sie Ihre Transaktionslatenz und Ihren Durchsatz über die Zeit. Möglicherweise stellen Sie fest, dass die Leistung der binären Protokollreplikation stabil ist und eine geringe Latenz aufweist, selbst wenn gelegentliche Konflikte auftreten.

aurora_binlog_use_large_read_buffer

Wenn dieser Parameter auf 1 gesetzt ist, SQL optimiert Aurora My die binäre Protokollreplikation auf der Grundlage der Einstellungen der Parameter aurora_binlog_read_buffer_size undaurora_binlog_replication_max_yield_seconds. Wenn 0 aurora_binlog_use_large_read_buffer ist, SQL ignoriert Aurora My die Werte der aurora_binlog_replication_max_yield_seconds Parameter aurora_binlog_read_buffer_size und.

aurora_binlog_read_buffer_size

Binäre Protokoll-Dump-Threads mit größerem Lesepuffer minimieren die Anzahl der Lese-I/O-Vorgänge, indem sie weitere Ereignisse für jede I/O lesen. Der Parameter aurora_binlog_read_buffer_size legt die Größe des Lesepuffers fest. Der große Lesepuffer kann den Konflikt zwischen Binärprotokollen für Workloads reduzieren, die eine große Menge an Binärprotokoll-Daten erzeugen.

Anmerkung

Dieser Parameter hat nur Auswirkungen, wenn der Cluster auch die Einstellung ha aurora_binlog_use_large_read_buffer=1.

Eine Erhöhung der Größe des Lesepuffers hat keinen Einfluss auf die Leistung der binären Protokollreplikation. Binäre Protokoll-Dump-Threads warten nicht darauf, dass die Aktualisierung von Transaktionen den Lesepuffer füllt.

aurora_binlog_replication_max_yield_seconds

Wenn Ihr Workload eine geringe Transaktionslatenz erfordert und Sie eine gewisse Replikationsverzögerung tolerieren können, können Sie den Parameter aurora_binlog_replication_max_yield_seconds erhöhen. Dieser Parameter steuert die Eigenschaft „Maximum Yield“ der binären Protokollreplikation in Ihrem Cluster.

Anmerkung

Dieser Parameter hat nur Auswirkungen, wenn der Cluster auch die Einstellung ha aurora_binlog_use_large_read_buffer=1.

Aurora My SQL erkennt jede Änderung des aurora_binlog_replication_max_yield_seconds Parameterwerts sofort. Sie müssen die DB-Instance nicht neu starten. Wenn Sie diese Einstellung aktivieren, beginnt der Dump-Thread jedoch erst dann zu arbeiten, wenn die aktuelle Binärprotokolldatei ihre maximale Größe von 128 MB erreicht und in eine neue Datei gedreht wird.

Zugehörige Parameter

Verwenden Sie die folgenden DB-Cluster-Parameter, um die Binärprotokoll-Optimierung zu aktivieren.

Parameter	Standard	Zulässige Werte	Beschreibung
aurora_binlog_use_large_read_buffer	1	0, 1	Schalter zum Einschalten der Replikationsverbesserung. Wenn der Wert 1 ist, verwendet der binäre Protokoll-Dump-Thread aurora_binlog_read_buffer_size für die binäre Protokollreplikation, andernfalls wird die Standardpuffergröße (8 K) verwendet. Wird in Aurora My SQL Version 3 nicht verwendet.
aurora_binlog_read_buffer_size	5242880	8192-536870912	Liest die Puffergröße, die vom binären Protokoll-Dump-Thread verwendet wird, wenn der Parameter aurora_binlog_use_large_read_buffer auf 1 gesetzt ist. Wird in Aurora My SQL Version 3 nicht verwendet.
aurora_binlog_replication_max_yield_seconds	0	0 -36000	Für Aurora My SQL Version 2.07.* ist der zulässige Höchstwert 45. Unter 2.09 und späteren Versionen können Sie einen höheren Wert einstellen. Für Version 2 funktioniert dieser Parameter nur, wenn der Parameter aurora_binlog_use_large_read_buffer auf 1 gesetzt ist.

Aktivieren des Max-Yield-Mechanismus für die binäre Protokollreplikation

Sie können die Max-Yield-Optimierung der binären Protokollreplikation wie folgt aktivieren. Dadurch wird die Latenz für Transaktionen auf der Binärprotokoll-Quell-Instance minimiert. Es kann jedoch zu einer höheren Verzögerung bei der Replikation kommen.

So aktivieren Sie die Binlog-Optimierung mit maximaler Rendite für einen Aurora My-Cluster SQL

1. Erstellen oder bearbeiten Sie eine DB-Clusterparametergruppe unter Verwendung der folgenden Parametereinstellungen:

   • aurora_binlog_use_large_read_buffer: schaltet sich mit einem Wert von ON oder 1 ein.

   • aurora_binlog_replication_max_yield_seconds: Geben Sie einen Wert größer als 0 ein.

2. Ordnen Sie die DB-Cluster-Parametergruppe dem Aurora SQL My-Cluster zu, der als Binlog-Quelle fungiert. Befolgen Sie hierzu die Verfahren unter Parametergruppen für Amazon Aurora.

3. Bestätigen Sie, dass die Parameteränderung wirksam wird. Führen Sie dazu die folgende Abfrage für die Binärprotokoll-Quell-Instance aus.

   SELECT @@aurora_binlog_use_large_read_buffer, @@aurora_binlog_replication_max_yield_seconds;

   Die Ausgabe sollte in etwa wie folgt aussehen.

   +---------------------------------------+-----------------------------------------------+
   | @@aurora_binlog_use_large_read_buffer | @@aurora_binlog_replication_max_yield_seconds |
   +---------------------------------------+-----------------------------------------------+
   |                                     1 |                                            45 |
   +---------------------------------------+-----------------------------------------------+
   

Deaktivieren der Max-Yield-Optimierung der binären Protokollreplikation

Sie können die Max-Yield-Optimierung der binären Protokollreplikation wie folgt deaktivieren. Dadurch wird die Verzögerung bei der Replikation minimiert. Es kann jedoch zu einer höheren Latenz für Transaktionen auf der Binärprotokoll-Quell-Instance kommen.

So schalten Sie die Maximal-Yield-Optimierung für einen Aurora My-Cluster aus SQL

1. Stellen Sie sicher, dass die DB-Cluster-Parametergruppe, die dem Aurora SQL My-Cluster zugeordnet ist, auf 0 aurora_binlog_replication_max_yield_seconds gesetzt ist. Weitere Informationen zum Einstellen von Konfigurationsparametern unter Verwendung von Parametergruppen finden Sie unter Parametergruppen für Amazon Aurora.

2. Bestätigen Sie, dass die Parameteränderung wirksam wird. Führen Sie dazu die folgende Abfrage für die Binärprotokoll-Quell-Instance aus.

   SELECT @@aurora_binlog_replication_max_yield_seconds;

   Die Ausgabe sollte in etwa wie folgt aussehen.

   +-----------------------------------------------+
   | @@aurora_binlog_replication_max_yield_seconds |
   +-----------------------------------------------+
   |                                             0 |
   +-----------------------------------------------+
   

Ausschalten des großen Lesepuffers

Sie können die gesamte Funktion für große Lesepuffer wie folgt deaktivieren.

Um den großen Binärlog-Lesepuffer für einen Aurora SQL My-Cluster auszuschalten

1. Setzen Sie aurora_binlog_use_large_read_buffer auf OFF oder 0 zurück:

   Stellen Sie sicher, dass die DB-Cluster-Parametergruppe, die dem Aurora SQL My-Cluster zugeordnet ist, auf 0 aurora_binlog_use_large_read_buffer gesetzt ist. Weitere Informationen zum Einstellen von Konfigurationsparametern unter Verwendung von Parametergruppen finden Sie unter Parametergruppen für Amazon Aurora.

2. On the binlog source instance, run the following query.

   SELECT @@ aurora_binlog_use_large_read_buffer;

   Die Ausgabe sollte in etwa wie folgt aussehen.

   +---------------------------------------+
   | @@aurora_binlog_use_large_read_buffer |
   +---------------------------------------+
   |                                     0 |
   +---------------------------------------+