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.
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étrique | Description |
|---|---|
| Délai avant réplication ( cloudsql.googleapis.com) |
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 |
| Numéro d'erreur du dernier thread d'E/S ( cloudsql.googleapis.com) |
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 |
| Numéro d'erreur du dernier thread SQL ( cloudsql.googleapis.com) |
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 |
| Latence du réseau ( cloudsql.googleapis.com) |
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 |
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 :
| Champ | Description |
|---|---|
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 ci-dessus, 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.