Délai avant réplication

Cette page explique comment résoudre les problèmes de délai avant réplication pour les instances dupliquées avec accès en lecture de Cloud SQL.

Présentation

Les instances dupliquées avec accès en lecture de Cloud SQL utilisent la réplication basée sur les lignes MySQL à l'aide des identifiants de transaction globaux (GTID). Les modifications sont écrites dans le journal binaire de l'instance principale et envoyées à l'instance dupliquée où elles sont reçues, puis appliquées à la base de données.

Le délai avant réplication peut se produire dans plusieurs scénarios, par exemple :

  • L'instance principale ne peut pas envoyer les modifications suffisamment rapidement à l'instance dupliquée.
  • L'instance dupliquée ne peut pas recevoir les modifications suffisamment rapidement.
  • L'instance dupliquée ne peut pas appliquer les modifications suffisamment rapidement.
Utilisez la commande suivante : network_lag pour surveiller les deux premiers scénarios, lorsque l'instance principale ne peut pas envoyer de modifications suffisamment rapidement ou que l'instance dupliquée ne peut pas recevoir les modifications suffisamment rapidement.

Le délai total est observé avec la métrique replica_lag. La différence entre replica_lag et network_lag peut indiquer la troisième raison pour laquelle l'instance dupliquée ne peut pas appliquer les modifications de réplication suffisamment rapidement. Ces métriques sont décrites dans la section Surveiller le délai avant réplication ci-dessous.

Configuration accélérée des instances dupliquées

Il existe deux manières d'accélérer l'application des modifications des instances dupliquées MySQL. Les utilisateurs peuvent configurer leurs instances dupliquées avec les options suivantes :

  • Réplication parallèle
  • Vidage haute performance

Réplication parallèle

La réplication parallèle peut aider à allonger le délai avant réplication en configurant l'instance dupliquée pour qu'elle utilise plusieurs threads agissant en parallèle pour appliquer les modifications à l'instance dupliquée. Pour savoir comment utiliser la réplication parallèle, consultez la page Configurer la réplication parallèle.

Vidage haute performance

Par défaut, Cloud SQL pour MySQL vide les journaux de rétablissement sur le disque après chaque transaction. Le vidage haute performance réduit la fréquence à laquelle les journaux de rétablissement sont vidés sur le disque une fois par seconde, ce qui améliore les performances d'écriture.

Définissez l'option innodb_flush_log_at_trx_commit sur l'instance dupliquée avec accès en lecture sur 2. Vous devez également définir l'option sync_binlog sur une valeur plus élevée pour que l'option innodb_flush_log_at_trx_commit soit efficace.

Pour plus d'informations sur cette option, consultez la section Conseils pour l'utilisation d'options.

Lorsque l'option innodb_purge_log_at_trx_commit est définie sur l'instance dupliquée avec accès en lecture et que Cloud SQL détecte un plantage, Cloud SQL recrée automatiquement cette instance.

Optimiser les requêtes et le schéma

Cette section fournit des suggestions d'optimisations courantes de requêtes et de schémas que vous pouvez appliquer pour améliorer les performances de réplication.

Niveau d'isolation de requête dans l'instance dupliquée avec accès en lecture

Les niveaux d'isolation de transaction REPEATABLE READ et SERIALIZABLE acquièrent des verrous susceptibles de bloquer les modifications de réplication. Envisagez de réduire le niveau d'isolation pour vos requêtes dans l'instance dupliquée. Le niveau d'isolation des transactions READ COMMITTED peut offrir de meilleures performances.

Transactions de longue durée dans la base de données principale

Si un grand nombre de lignes sont mises à jour au cours d'une même transaction, cela peut entraîner un pic soudain de modifications à appliquer à l'instance principale, puis à l'instance dupliquée. Cela s'applique aux mises à jour ou aux suppressions d'instructions uniques qui affectent de nombreuses lignes à la fois. Les modifications sont envoyées à l'instance dupliquée après leur commit. L'application d'un pic soudain de modifications au niveau de l'instance dupliquée peut augmenter les risques de conflits de verrous dans l'instance dupliquée si la charge des requêtes sur l'instance dupliquée est également élevée, ce qui entraîne un délai avant réplication donné.

Envisagez de diviser les transactions volumineuses en plusieurs transactions plus petites.

Clés primaires manquantes

Les instances dupliquées avec accès en lecture de Cloud SQL utilisent une réplication basée sur les lignes, qui fonctionne mal si les tables MySQL répliquées ne possèdent pas de clé primaire. Nous recommandons que toutes les tables répliquées possèdent des clés primaires.

Pour MySQL 8 ou une version ultérieure, nous vous recommandons de définir l'option sql_require_primary_key sur ON pour spécifier que les tables de votre base de données doivent disposer de clés primaires.

Verrous exclusifs en raison de LDD

Les commandes LDD (langage de définition de données), telles que ALTER TABLE et CREATE INDEX peuvent entraîner un délai avant réplication dans l'instance dupliquée en raison de verrous exclusifs. Pour éviter les conflits de verrouillage, envisagez de planifier l'exécution LDD pendant les périodes où la charge de la requête est inférieure sur les instances dupliquées.

Instance dupliquée surchargée

Si une instance dupliquée avec accès en lecture reçoit trop de requêtes, la réplication peut être bloquée. Envisagez de répartir les lectures entre plusieurs instances dupliquées pour réduire la charge sur chacune d'entre elles.

Pour éviter les pics de requêtes, envisagez de limiter les requêtes de lecture des instances dupliquées dans votre logique d'application ou dans une couche de proxy, si vous en utilisez une.

En cas de pics d'activité sur l'instance principale, envisagez de répartir les mises à jour.

Base de données principale monolithique

Envisagez de segmenter la base de données principale verticalement (ou horizontalement) pour éviter qu'une ou plusieurs tables en retard ne retiennent toutes les autres tables.

Surveiller le délai avant réplication

Vous pouvez utiliser les métriques replica_lag et network_lag pour surveiller le délai avant réplication et déterminer si la cause de ce délai se trouve dans la base de données principale, le réseau ou l'instance dupliquée.

MétriqueDescription
Délai avant réplication
(cloudsql.googleapis.com/database/replication/replica_lag)

Nombre de secondes de retard de l'état de l'instance dupliquée par rapport à l'état de l'instance principale. Il s'agit de la différence entre l'heure actuelle et l'horodatage d'origine auquel la base de données principale a validé la transaction actuellement appliquée sur l'instance dupliquée. En particulier, les écritures peuvent être considérées comme retardées même si elles ont été reçues par l'instance dupliquée, si celle-ci n'a pas encore appliqué l'écriture à la base de données.

Cette métrique indique la valeur de Seconds_Behind_Master lorsque SHOW SLAVE STATUS est exécuté sur l'instance dupliquée. Pour en savoir plus, consultez la page Vérifier l'état de la réplication dans le manuel de référence MySQL.
Numéro d'erreur du dernier thread d'E/S
(cloudsql.googleapis.com/database/mysql/replication/last_io_errno)

Indique la dernière erreur qui a entraîné l'échec du thread d'E/S. Dans le cas contraire, la réplication est interrompue. Ceci est rare, mais peut se produire. Consultez la documentation MySQL pour comprendre ce que le code d'erreur indique. Par exemple, les fichiers binlog de l'instance principale ont pu être supprimés avant que l'instance dupliquée ne les reçoive. Cloud SQL recrée automatiquement l'instance dupliquée si la réplication est interrompue. Cette métrique last_io_errno peut vous expliquer pourquoi.

Numéro d'erreur du dernier thread SQL
(cloudsql.googleapis.com/database/mysql/replication/last_sql_errno)

Indique la dernière erreur à l'origine de l'échec du thread SQL. Dans le cas contraire, la réplication est interrompue. Ceci est rare, mais peut se produire. Consultez la documentation MySQL pour comprendre ce que le code d'erreur indique. Généralement, Cloud SQL recrée automatiquement l'instance dupliquée si la réplication est interrompue. Cette métrique last_sql_errno peut vous expliquer pourquoi.

Latence du réseau
(cloudsql.googleapis.com/database/replication/network_lag)

Durée, en secondes, nécessaire pour écrire le binlog dans la base de données principale et atteindre le thread d'E/S dans l'instance dupliquée.

Si la valeur de network_lag est nulle ou négligeable, mais que la valeur replica_lag est élevée, cela indique que le thread SQL n'est pas en mesure d'appliquer les modifications de réplication assez rapidement.

Vérifier la réplication

Pour vérifier que la réplication fonctionne, exécutez l'instruction suivante sur l'instance dupliquée :

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)

Si la réplication se produit, la première colonne (Slave_IO_State) affiche Waiting for master to send event ou un message similaire. En outre, le champ Last_IO_Error est vide.

Si la réplication n'a pas lieu, la colonne Slave_IO_State affiche l'état Connecting to master et la colonne Last_IO_Error affiche l'état error connecting to master cloudsqlreplica@x.x.x.x:3306.

Selon la documentation MySQL, voici d'autres champs d'intérêt intéressants liés au délai avant réplication :

ChampDescription
Master_Log_File
 Nom du fichier journal binaire source à partir duquel le thread d'E/S est en cours de lecture.
Read_Master_Log_Pos
 Position dans le fichier journal binaire source actuel que le thread d'E/S a lu.
Relay_Log_File
 Nom du fichier journal du relais que le thread SQL lit et exécute actuellement.
Relay_Log_Pos
 Position dans le fichier journal du relais actuel jusqu'à ce que le thread SQL l'ait lu et exécuté.
Relay_Master_Log_File
 Nom du fichier journal binaire source contenant l'événement le plus récent exécuté par le thread SQL.

Dans l'exemple précédent, Relay_Master_Log_File a la valeur mysql-bin.199898. Master_Log_File a pour valeur mysql-bin.199927. Le suffixe numérique 199898 est inférieur à 199927. Cela signifie que même si l'instance dupliquée a reçu un fichier journal mysql-bin.199927 plus récent, elle applique toujours l'ancien mysql-bin.199898.

Dans ce cas, le thread SQL est en retard dans l'instance dupliquée.

Vous pouvez également vous connecter à la base de données principale et exécuter la commande suivante :

  SHOW MASTER STATUS;

Cette commande indique le fichier binlog en cours d'écriture dans la base de données principale.

Si le fichier journal binaire de la base de données principale est plus récent que Master_Log_File dans l'instance dupliquée, cela signifie que le thread d'E/S est en retard. L'instance dupliquée lit toujours un ancien fichier journal binaire à partir de la base de données principale.

Lorsque le thread d'E/S est en retard, la métrique network_lag est également élevée. Lorsque le thread SQL est en retard, mais que le thread d'E/S ne l'est pas, la métrique network_lag n'est pas aussi élevée, mais la valeur replica_lag est élevée.

Les commandes précédentes vous permettent d'observer les détails des délais avant qu'ils ne se produisent, mais les métriques network_lag et replica_lag vous permettent d'examiner les occurrences passées du retard.

Recréer une instance répliquée en retard

Recréez une instance répliquée en retard lorsque la réplication est en retard d'une durée acceptable.

Avec Cloud SQL, vous pouvez configurer votre instance répliquée avec accès en lecture pour qu'elle se recrée si la réplication est en retard au-delà d'une durée acceptable, et si ce retard persiste pendant au moins cinq minutes.

Si vous définissez un délai de réplication acceptable comme étant inférieur à 360 secondes (six minutes) et qu'un délai de réplication d'au moins 361 secondes persiste pendant plus de cinq minutes, l'instance principale crée un nouvel instantané d'elle-même au bout de cinq minutes, et le réplica en lecture est recréé à l'aide de cet instantané.

La recréation d'une instance répliquée avec accès en lecture en retard présente les avantages suivants :

  • Vous contrôlez ce qui est considéré comme une plage acceptable pour le délai de réplication.
  • Vous pouvez réduire le temps passé à résoudre les problèmes de délai de réplication de plusieurs heures, voire de plusieurs jours.

D'autres informations sur les fonctionnalités s'appliquent :

  • Compatible avec les versions suivantes :
    • MySQL 5.7
    • MySQL 8.0
    • MySQL 8.4
  • Une plage acceptable pour le délai de réplication doit être définie en secondes.
  • La valeur minimale acceptable est de 300 secondes ou cinq minutes.
  • La valeur maximale acceptable est de 31 536 000 secondes, soit un an.
    • Si vous activez l'option "Recréer le réplica en retard" pour une instance, mais que vous ne définissez pas le délai de réplication maximal acceptable, Cloud SQL utilise la valeur par défaut d'un an.
  • Types d'instances compatibles :
    • Instance dupliquée avec accès en lecture
    • Instance dupliquée interrégionale avec accès en lecture
    • Instance répliquée en cascade
  • La valeur définie pour le champ replicationLagMaxSeconds est spécifique à chaque instance de réplica. Si une instance principale comporte plusieurs instances répliquées, vous pouvez attribuer une valeur différente à chacune d'elles.
  • Lorsqu'une réplique est recréée, les utilisateurs peuvent s'attendre à une certaine indisponibilité pendant que les opérations suivantes sont effectuées :
    • La réplication est arrêtée.
    • La réplique est supprimée.
    • Un instantané de l'instance principale est créé.
    • La réplique est recréée à partir de ce dernier instantané. La nouvelle instance répliquée utilise le même nom et la même adresse IP que l'instance répliquée précédente. Par conséquent, MySQL doit s'arrêter et redémarrer.
    • La nouvelle réplique commence à répliquer les données.
  • replicationLagMaxSeconds est un champ au niveau de l'instance. Chaque instance a sa propre valeur.

  • Si vous disposez de plusieurs instances répliquées avec accès en lecture pour la même instance principale, vous pouvez définir une valeur unique pour le champ replicationLagMaxSeconds pour chaque réplica.

    Définir des seuils de temps différents pour différentes répliques peut vous aider à éviter un scénario dans lequel toutes les répliques sont mises hors service en même temps.

Activer l'option "Recréer le réplica en retard"

La fonctionnalité de recréation de l'instance répliquée en retard est désactivée par défaut. Pour l'activer lorsque vous créez une instance, utilisez l'une des méthodes suivantes :

gcloud

Utilisez la commande gcloud sql instances create pour créer une instance répliquée avec accès en lecture avec l'option
--replication-lag-max-seconds-for-recreate :

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

Où :

  • REPLICA_INSTANCE_NAME est le nom de l'instance de réplica.
  • PRIMARY_INSTANCE_NAME est le nom de l'instance principale.
  • DATABASE_VERSION correspond à la version de la base de données de l'instance. Exemple :MYSQL_8_0_31
  • TIER correspond au type de machine que vous souhaitez utiliser pour l'instance répliquée. Exemple :db-perf-optimized-N-4 Pour en savoir plus, consultez Configurations d'instances personnalisées.
  • EDITION correspond à l'édition que vous souhaitez utiliser pour l'instance répliquée. Exemple :ENTERPRISE_PLUS Pour en savoir plus, consultez Créer une instance.
  • REGION est la région que vous souhaitez utiliser pour l'instance répliquée. Exemple :us-central1
  • PASSWORD correspond au mot de passe racine de l'instance.
  • REPLICATION_LAG_MAX_SECONDS correspond à la latence ou au délai de réplication maximal acceptable en secondes. Exemple :600 La valeur minimale acceptable est de 300 secondes ou cinq minutes. La valeur maximale acceptable est de 31 536 000 secondes, soit un an.

API REST

Le champ replicationLagMaxSeconds se trouve dans la ressource DatabaseInstance. Ajoutez ce champ au corps de la requête :

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

Où :

  • REPLICATION_LAG_MAX_SECONDS correspond à la latence ou au délai de réplication maximal acceptable en secondes. Exemple :600

Mettre à jour le délai de recréation pour le décalage de réplication

Pour afficher les paramètres d'une instance, utilisez l'une des méthodes décrites dans Afficher les informations récapitulatives d'une instance.

Grâce à ces informations, vous pouvez choisir de mettre à jour ou non le délai de latence de réplication que vous avez spécifié comme acceptable avant la recréation du réplica.

gcloud

Utilisez la commande gcloud sql instances patch pour mettre à jour le délai de recréation de l'instance en fonction du temps de latence de la réplication :

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

Où :

  • INSTANCE_NAME est le nom de l'instance.
  • REPLICATION_LAG_MAX_SECONDS correspond à la latence ou au délai de réplication maximal acceptable en secondes. Exemple :700 Si vous souhaitez revenir à la valeur par défaut d'un an, saisissez 31536000. La valeur minimale acceptable est de 300 secondes ou cinq minutes. La valeur maximale acceptable est de 31 536 000 secondes, soit un an.

API REST

Vous pouvez mettre à jour la règle à l'aide de instances.patch et instance.insert.

Pour voir un exemple de mise à jour du paramètre à l'aide de l'API REST, consultez Modifier une instance.

Limites

Les limites suivantes s'appliquent à la recréation des répliques en retard :

  • Les valeurs de replicationLagMaxSeconds ne peuvent être définies qu'en secondes.
  • Les index créés sur l'instance répliquée avec accès en lecture avant une opération de recréation ne seront pas conservés. Si un index existe, créez un index secondaire après la recréation de la réplique.
  • Pour éviter les temps d'arrêt fréquents sur les instances répliquées en lecture, les recréations sont limitées à une par jour et par instance.
  • Les répliques de serveurs externes ne sont pas compatibles avec cette fonctionnalité.
  • Si vous activez la recréation des instances répliquées en retard sur une instance répliquée en cascade, Cloud SQL recrée d'abord les instances répliquées feuilles afin de maintenir la cohérence de la réplication.
  • La recréation d'une instance dupliquée interrégionale entraîne des coûts supplémentaires.
  • Vous ne pouvez pas activer la recréation des instances répliquées en retard dans la console Google Cloud .

Étapes suivantes