Verzögerung der Replikation

Auf dieser Seite wird beschrieben, wie Sie die Replikationsverzögerung bei Cloud SQL-Lesereplikaten beheben können.

Übersicht

Cloud SQL-Lesereplikate nutzen die zeilenbasierte MySQL-Replikation mithilfe von globalen Transaktions-IDs (GTIDs). Änderungen werden in das Binärlog der primären Instanz geschrieben und an das Replikat gesendet. Dort werden sie empfangen und auf die Datenbank angewendet.

Die Replikationsverzögerung kann in verschiedenen Szenarien auftreten:

  • Die primäre Instanz kann die Änderungen nicht schnell genug an das Replikat senden.
  • Das Replikat kann die Änderungen nicht schnell genug empfangen.
  • Das Replikat kann die Änderungen nicht schnell genug anwenden.
Verwenden Sie den Messwert network_lag, um die ersten beiden Szenarien zu überwachen, wenn die primäre Instanz Änderungen nicht schnell genug senden kann oder das Replikat Änderungen nicht schnell genug empfangen kann.

Die Gesamtverzögerung wird durch den Messwert replica_lag angegeben. Der Unterschied zwischen replica_lag und network_lag kann der dritte Grund dafür sein, dass das Replikat Replikationsänderungen nicht schnell genug anwendet. Diese Messwerte werden unten im Abschnitt Replikationsverzögerung überwachen beschrieben.

Schnellere Replikatkonfiguration

Es gibt zwei Möglichkeiten, damit ein MySQL-Replikat Änderungen schneller anwendet. Nutzer können ihre Replikate mit den folgenden Optionen konfigurieren:

  • Parallele Replikation
  • Leistungsstarke Leerung

Parallele Replikation

Bei einer Replikationsverzögerung kann auch eine parallele Replikation helfen. Dazu wird das Replikat so konfiguriert, dass mehrere Threads parallel verwendet werden, um Änderungen auf das Replikat anzuwenden. Informationen zur Verwendung der parallelen Replikation finden Sie unter Parallele Replikation konfigurieren.

Leistungsstarke Leerung

Standardmäßig leert Cloud SQL for MySQL die Redo-Logs auf das Laufwerk. Ein leistungsstarkes Leeren von Elementen reduziert die Häufigkeit, mit der die Redo-Logs auf einmal pro Sekunde auf das Laufwerk geleert werden. Dies verbessert die Schreibleistung.

Legen Sie für das Flag innodb_flush_log_at_trx_commit des Lesereplikats den Wert 2 fest. Sie müssen auch das Flag sync_binlog auf einen höheren Wert setzen, damit das Flag innodb_flush_log_at_trx_commit wirksam ist.

Unter Tipps zum Arbeiten mit Flags finden Sie weitere Informationen zu diesem Flag.

Wenn das Flag innodb_flush_log_at_trx_commit für das Lesereplikat festgelegt ist und Cloud SQL erkennt, dass ein Absturz aufgetreten ist, erstellt Cloud SQL das Replikat automatisch neu.

Abfragen und Schema optimieren

In diesem Abschnitt werden einige gängige Abfrage- und Schemaoptimierungen vorgeschlagen, mit denen Sie die Replikationsleistung verbessern können.

Abfrageisolationsebene im Lesereplikat

Die Transaktionsisolationsebenen REPEATABLE READ und SERIALIZABLE erhalten Sperren, die die Replikationsänderungen blockieren können. Prüfen Sie, die Isolationsebene für Ihre Abfragen im Replikat zu reduzieren. Die Transaktionsisolationsebene READ COMMITTED bietet eventuell eine bessere Leistung.

Lang andauernde Transaktionen in der primären Datenbank

Wenn eine große Anzahl an Zeilen in einer einzelnen Transaktion aktualisiert wird, kann dies zu einem plötzlichen Anstieg der Anzahl an Änderungen führen, die auf die primäre Instanz angewendet und dann an das Replikat gesendet werden müssen. Dies gilt für einzelne Anweisungen für Aktualisierungs- oder Löschvorgänge, die mehrere Zeilen gleichzeitig betreffen. Änderungen werden nach dem Commit an das Replikat gesendet. Ein plötzlicher Anstieg von Änderungen auf dem Replikat kann die Wahrscheinlichkeit von Sperrenkonflikten im Replikat erhöhen, wenn die Abfragelast auf dem Replikat ebenfalls hoch ist. Dies führt zu Replikationsverzögerungen.

Unterteilen Sie dann große Transaktionen in mehrere kleinere Transaktionen.

Fehlende Primärschlüssel

Cloud SQL-Lesereplikate verwenden eine zeilenbasierte Replikation. Diese wird langsam ausgeführt, wenn die replizierten MySQL-Tabellen keine Primärschlüssel haben. Wir empfehlen in diesem Fall, dass alle replizierten Tabellen Primärschlüssel erhalten.

Für MySQL 8 oder höher empfehlen wir, das Flag sql_require_primary_key auf ON zu setzen, damit Tabellen in Ihrer Datenbank Primärschlüssel haben müssen.

Exklusive Sperren aufgrund von DDL

DDL-Befehle (Data Definition Language) wie ALTER TABLE und CREATE INDEX können aufgrund von exklusiven Sperren zu Replikationsverzögerungen im Replikat führen. Um Sperrenkonflikte zu vermeiden, sollten Sie die DDL-Ausführung zu Zeiten planen, in denen die Abfragelast auf den Replikaten geringer ist.

Überlastetes Replikat

Wenn ein Lesereplikat zu viele Abfragen erhält, kann die Replikation blockiert werden. Ziehen Sie in Betracht, die Lesevorgänge auf mehrere Replikate aufzuteilen, um die Last auf den einzelnen Replikaten zu reduzieren.

Sie können Abfragespitzen vermeiden, indem Sie Replikatleseabfragen in Ihrer Anwendungslogik oder in einer Proxy-Ebene, falls Sie eine verwenden, drosseln.

Wenn es auf der primären Instanz zu Spitzen bei der Aktivität kommt, können Sie Updates verteilen.

Monolithische primäre Datenbank

Ziehen Sie eine vertikale Fragmentierung der primären Datenbank in Betracht, um zu verhindern, dass eine oder mehrere verzögerte Tabellen alle anderen Tabellen zurückhalten.

Replikationsverzögerung überwachen

Mit den Messwerten replica_lag und network_lag können Sie die Replikationsverzögerung überwachen und ermitteln, ob die Ursache der Verzögerung in der primären Datenbank, im Netzwerk oder im Replikat liegt.

MesswertBeschreibung
Replikationsverzögerung
(cloudsql.googleapis.com/database/replication/replica_lag)

Die Anzahl der Sekunden, die der Zustand des Replikats hinter dem Zustand der primären Instanz zurückliegt. Dies ist die Differenz zwischen der aktuellen Zeit und dem ursprünglichen Zeitstempel, bei dem die primäre Datenbank die Transaktion übergeben hat, die derzeit auf das Replikat angewendet wird. Insbesondere können Schreibvorgänge als verzögert gewertet werden, selbst wenn sie vom Replikat empfangen wurden, wenn das Replikat den Schreibvorgang noch nicht auf die Datenbank angewendet hat.

Dieser Messwert gibt den Wert von Seconds_Behind_Master an, wenn SHOW SLAVE STATUS auf dem Replikat ausgeführt wird. Weitere Informationen finden Sie im MySQL-Referenzhandbuch unter Replikationsstatus prüfen.

Fehlernummer des letzten E/A-Threads
(cloudsql.googleapis.com/database/mysql/replication/last_io_errno)

Gibt den letzten Fehler an, der zum fehlgeschlagenen E/A-Thread geführt hat. Wenn diese nicht null ist, wird die Replikation unterbrochen. Dies kommt selten vor, ist aber möglich. In der MySQL-Dokumentation finden Sie die Bedeutung des Fehlercodes. Beispielsweise wurden eventuell binlog-Dateien in der primären Instanz gelöscht, bevor das Replikat sie erhalten hat. Cloud SQL erstellt das Replikat in der Regel automatisch neu, wenn die Replikation unterbrochen wird. Der Messwert last_io_errno weist eventuell auf den Grund hin.

Fehlernummer des letzten SQL-Threads
(cloudsql.googleapis.com/database/mysql/replication/last_sql_errno)

Gibt den letzten Fehler an, der zum Ausfall des SQL-Threads geführt hat. Wenn diese nicht null ist, wird die Replikation unterbrochen. Dies kommt selten vor, ist aber möglich. In der MySQL-Dokumentation finden Sie die Bedeutung des Fehlercodes. Cloud SQL erstellt das Replikat in der Regel automatisch neu, wenn die Replikation unterbrochen wird. Der Messwert last_sql_errno weist eventuell auf den Grund hin.

Netzwerkverzögerung
(cloudsql.googleapis.com/database/replication/network_lag)

Die Zeit in Sekunden, die vom Schreiben des binlogs in der primären Datenbank bis zum Erreichen des E/A-Threads in der Replik vergeht.

Wenn network_lag null oder vernachlässigbar, replica_lag aber hoch ist, bedeutet dies, dass der SQL-Thread die Replikationsänderungen nicht schnell genug anwenden kann.

Replikation prüfen

Führen Sie die folgende Anweisung für das Replikat aus, um zu prüfen, ob die Replikation funktioniert:

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Queueing master event to the relay log
                  Master_Host: xx.xxx.xxx.xxx
                  Master_User: cloudsqlreplica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.199927
          Read_Master_Log_Pos: 83711956
               Relay_Log_File: relay-log.000025
                Relay_Log_Pos: 24214376
        Relay_Master_Log_File: mysql-bin.199898
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 24214163
              Relay_Log_Space: 3128686571
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: master_server_ca.pem
           Master_SSL_CA_Path: /mysql/datadir
              Master_SSL_Cert: replica_cert.pem
            Master_SSL_Cipher:
               Master_SSL_Key: replica_pkey.pem
        Seconds_Behind_Master: 2627
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 321071839
                  Master_UUID: 437d04e9-8456-11e8-b13d-42010a80027b
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: System lock
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 437d04e9-8456-11e8-b13d-42010a80027b:52111095710-52120776390
            Executed_Gtid_Set: 437d04e9-8456-11e8-b13d-42010a80027b:1-52113039508
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

Wenn die Replikation erfolgt, wird in der ersten Spalte Slave_IO_State der Wert Waiting for master to send event oder eine ähnliche Nachricht angezeigt. Außerdem ist das Feld Last_IO_Error leer.

Wenn die Replikation nicht erfolgt, wird in der Spalte Slave_IO_State der Status Connecting to master und in der Spalte Last_IO_Error der Status error connecting to master cloudsqlreplica@x.x.x.x:3306 angezeigt.

Gemäß der MySQL-Dokumentation gibt es weitere im Zusammenhang mit Replikationsverzögerungen relevante Felder:

FeldBeschreibung
Master_Log_File
Der Name der binären Quelllogdatei, aus der der E/A-Thread aktuell liest.
Read_Master_Log_Pos
Die Position in der aktuellen binären Quelllogdatei, bis zu der der E/A-Thread gelesen hat.
Relay_Log_File
Der Name der Relay-Logdatei, die der SQL-Thread derzeit liest und ausführt.
Relay_Log_Pos
Die Position in der aktuellen Relay-Logdatei, bis zu der der SQL-Thread gelesen und ausgeführt wurde.
Relay_Master_Log_File
Der Name der binären Quelllogdatei, die das letzte vom SQL-Thread ausgeführte Ereignis enthält.

Im vorherigen Beispiel hat Relay_Master_Log_File den Wert mysql-bin.199898. Master_Log_File beispielsweise den Wert mysql-bin.199927. Das numerische Suffix 199898 ist kleiner als 199927. Das bedeutet, dass das Replikat weiterhin die ältere Datei mysql-bin.199898 verwendet, obwohl es die neuere mysql-bin.199927-Logdatei erhalten hat.

In diesem Fall verzögert sich der SQL-Thread im Replikat.

Sie können auch eine Verbindung zur primären Datenbank herstellen und Folgendes ausführen:

  SHOW MASTER STATUS;

Dieser Befehl zeigt an, welche binlog-Datei in die primäre Datenbank geschrieben wird.

Wenn die binäre Logdatei der primären Datenbank aktueller ist als die Master_Log_File-Datei im Replikat, bedeutet dies, dass der E/A-Thread sich verzögert. Das Replikat liest dann noch eine ältere binäre Logdatei aus der primären Datenbank.

Wenn sich der E/A-Thread verzögert, ist auch der Messwert network_lag hoch. Wenn der SQL-Thread sich verzögert, der E/A-Thread aber nicht, ist der Messwert network_lag nicht so hoch, der Wert replica_lag aber hoch.

Mit den vorherigen Befehlen können Sie Verzögerungsdetails während der Verzögerung ermitteln. Die Messwerte network_lag und replica_lag bieten Ihnen einen Einblick in frühere Verzögerungen.

Verzögertes Replikat neu erstellen

Erstellen Sie ein Replikat neu, wenn die Replikation zu lange in Verzug ist.

Mit Cloud SQL können Sie Ihr Lesereplikat so konfigurieren, dass es neu erstellt wird, wenn die Replikation länger als ein akzeptables Zeitlimit verzögert ist und diese Verzögerung mindestens fünf Minuten anhält.

Wenn Sie eine zulässige Replikationsverzögerung von weniger als 360 Sekunden (6 Minuten) festlegen und eine Replikationsverzögerung von mindestens 361 Sekunden länger als fünf Minuten andauert, erstellt die primäre Instanz nach fünf Minuten einen neuen Snapshot von sich selbst und das Lesereplikat wird mit diesem Snapshot neu erstellt.

Das Erstellen eines zurückgebliebenen Lesereplikats bietet folgende Vorteile:

  • Sie legen fest, was als akzeptabler Bereich für die Replikationsverzögerung gilt.
  • So können Sie die Zeit für die Fehlerbehebung bei Replikationsverzögerungen um Stunden oder sogar Tage verkürzen.

Es gelten zusätzliche Funktionsdetails:

  • Kompatibel mit den folgenden Versionen:
    • MySQL 5.7
    • MySQL 8.0
    • MySQL 8.4
  • Ein zulässiger Bereich für die Replikationsverzögerung muss in Sekunden definiert werden.
  • Der kleinste akzeptable Wert liegt bei 300 Sekunden oder fünf Minuten.
  • Der maximal zulässige Wert ist 31.536.000 Sekunden oder ein Jahr.
    • Wenn Sie die Option „Ausstehendes Replikat neu erstellen“ für eine Instanz aktivieren, aber die maximal zulässige Replikationsverzögerung nicht festlegen, verwendet Cloud SQL den Standardwert von einem Jahr.
  • Unterstützte Instanztypen:
    • Lesereplikat
    • Regionenübergreifendes Lesereplikat
    • Kaskadierendes Replikat
  • Der für das Feld replicationLagMaxSeconds festgelegte Wert ist für jede Replikinstanz spezifisch. Wenn eine primäre Instanz mehrere Replikate hat, können Sie jedem Replikate einen anderen Wert zuweisen.
  • Wenn ein Replikat neu erstellt wird, kann es zu einer kurzen Ausfallzeit kommen, während die folgenden Vorgänge abgeschlossen werden:
    • Die Replikation wird beendet.
    • Das Replikat wird gelöscht.
    • Es wird ein Snapshot der primären Instanz erstellt.
    • Das Replikat wird aus diesem neuesten Snapshot neu erstellt. Das neue Replikat verwendet denselben Namen und dieselbe IP-Adresse wie das vorherige Replikat. Daher muss MySQL angehalten und neu gestartet werden.
    • Die Datenreplikation für die neue Replik beginnt.
  • replicationLagMaxSeconds ist ein Feld auf Instanzebene. Jede Instanz hat einen eigenen Wert.
  • Wenn Sie mehrere Lesereplikate für dieselbe primäre Instanz haben, können Sie für jedes Replikat einen eindeutigen Wert für das Feld replicationLagMaxSeconds festlegen.

    Wenn Sie unterschiedliche Zeitgrenzwerte für unterschiedliche Replikate definieren, können Sie verhindern, dass alle Replikate gleichzeitig ausfallen.

„Ausstehendes Replikat neu erstellen“ aktivieren

Die Funktion zum Erstellen eines verzögerten Replikats ist standardmäßig deaktiviert. Sie können die Funktion beim Erstellen einer Instanz mit einer der folgenden Methoden aktivieren:

Verwenden Sie den Befehl gcloud sql instances create mit dem Flag
--replication-lag-max-seconds-for-recreate, um eine neue Instanz für Leserepliken zu erstellen:

gcloud beta sql instances create REPLICA_INSTANCE_NAME \
  --master-instance-name=PRIMARY_INSTANCE_NAME \
  --database-version=DATABASE_VERSION \
  --tier=TIER \
  --edition=EDITION \
  --region=REGION \
  --root-password=PASSWORD \
  --replication-lag-max-seconds-for-recreate=REPLICATION_LAG_MAX_SECONDS

Wobei:

  • REPLICA_INSTANCE_NAME ist der Name der Replikinstanz.
  • PRIMARY_INSTANCE_NAME ist der Name der primären Instanz.
  • DATABASE_VERSION ist die Datenbankversion der Instanz. Beispiel: MYSQL_8_0_31.
  • TIER ist der Maschinentyp, den Sie für die Replikinstanz verwenden möchten. Beispiel: db-perf-optimized-N-4. Weitere Informationen finden Sie unter Benutzerdefinierte Instanzkonfigurationen.
  • EDITION ist die Version, die Sie für die Replikinstanz verwenden möchten. Beispiel: ENTERPRISE_PLUS. Weitere Informationen finden Sie unter Instanz erstellen.
  • REGION ist die Region, die Sie für die Replikinstanz verwenden möchten. Beispiel: us-central1.
  • PASSWORD ist das Root-Passwort für die Instanz.
  • REPLICATION_LAG_MAX_SECONDS ist die maximal zulässige Replikationsverzögerung in Sekunden. Beispiel: 600. Der kleinste akzeptable Wert liegt bei 300 Sekunden oder fünf Minuten. Der maximal zulässige Wert ist 31.536.000 Sekunden oder ein Jahr.

Das Feld replicationLagMaxSeconds befindet sich in der Ressource DatabaseInstance. Fügen Sie dem Anfragetext dieses Feld hinzu:

{
  "settings": {
  "replicationLagMaxSeconds" :REPLICATION_LAG_MAX_SECONDS,
  }
  ...
}

Wobei:

  • REPLICATION_LAG_MAX_SECONDS ist die maximal zulässige Replikationsverzögerung in Sekunden. Beispiel: 600.

Zeitrahmen für die Neuerstellung bei Replikationsverzögerung aktualisieren

Sie können die Einstellungen einer Instanz mit einer der Methoden aufrufen, die unter Zusammenfassungsinformationen zu einer Instanz aufrufen beschrieben werden.

Anhand dieser Informationen können Sie entscheiden, ob Sie den Zeitrahmen für die Replikationsverzögerung, den Sie als akzeptabel angegeben haben, aktualisieren möchten, bevor das Replikat neu erstellt wird.

Aktualisieren Sie den Zeitraum für die Neuerstellung der Instanz basierend auf dem Replikationsrückstand mit dem Befehl gcloud sql instances patch:

gcloud beta sql instances patch INSTANCE_NAME \
  --replication-lag-max-seconds-for-recreate=REPLICATION_LAG_MAX_SECONDS

Wobei:

  • INSTANCE_NAME ist der Name der Instanz.
  • REPLICATION_LAG_MAX_SECONDS ist die maximal zulässige Replikationsverzögerung in Sekunden. Beispiel: 700. Wenn Sie zum Standardwert von einem Jahr zurückkehren möchten, geben Sie 31536000 ein. Der kleinste akzeptable Wert liegt bei 300 Sekunden oder fünf Minuten. Der maximal zulässige Wert ist 31.536.000 Sekunden oder ein Jahr.

Die Richtlinie kann mit instances.patch und instance.insert aktualisiert werden.

Ein Beispiel für die Aktualisierung der Einstellung mit der REST API finden Sie unter Instanz bearbeiten.

Beschränkungen

Für das Erstellen zurückgebliebener Repliken gelten die folgenden Einschränkungen:

  • Werte für replicationLagMaxSeconds können nur in Sekunden festgelegt werden.
  • Indexe, die vor einem Neuerstellungsvorgang auf dem Lesereplikat erstellt wurden, werden nicht beibehalten. Wenn ein Index vorhanden ist, erstellen Sie einen sekundären Index, nachdem die Replik neu erstellt wurde.
  • Um häufige Ausfallzeiten bei Lesereplikaten zu vermeiden, sind Neuerstellungen auf eine pro Tag und Instanz beschränkt.
  • Hochverfügbare Lesereplikate und Replikate externer Server werden von dieser Funktion nicht unterstützt.
  • Wenn Sie die Neuerstellung von verzögerten Replikaten auf einem kaskadierenden Replikat aktivieren, erstellt Cloud SQL zuerst die untergeordneten Replikate, um die Replikationskonsistenz aufrechtzuerhalten.
  • Für das Erstellen eines regionenübergreifenden Replikats fallen zusätzliche Kosten an.
  • Sie können die Neuerstellung von verzögerten Replikas nicht in der Google Cloud Console aktivieren.

Wie geht es weiter?