¿Cómo soluciono los problemas de retraso elevado en las réplicas de binlog con Amazon RDS para MySQL y Aurora MySQL?

Descripción corta

Dado que Amazon RDS para MySQL utiliza la replicación asincrónica, a veces se produce un retraso en la réplica en relación con la instancia de base de datos principal. Para supervisar el retraso de una replicación, utiliza una réplica de lectura de Amazon RDS para MySQL con replicación basada en la posición de los archivos de registro binarios.

Para comprobar la métrica ReplicaLag para Amazon RDS, usa Amazon CloudWatch. La métrica ReplicaLag informa del valor del campo Seconds_Behind_Master del comando SHOW SLAVE STATUS.

Para Aurora, la métrica AuroraBinlogReplicaLag mide el retraso de la réplica entre los clústeres de base de datos de Aurora que utilizan registros binarios.

Nota: Para las versiones 8.0.22 y posteriores de MySQL, el comando SHOW REPLICA STATUS se sustituye por el comando SHOW SLAVE STATUS. Para obtener más información, consulta SHOW SLAVE | REPLICA STATUS statement (Instrucción SHOW SLAVE | REPLICA STATUS) en el sitio web de MySQL.

El campo Seconds_Behind_Master muestra el retraso actual en segundos en el que la instancia de base de datos de réplica está detrás de la instancia de origen. También muestra el valor original registrado en la instancia de base de datos principal para los procesos de eventos en la instancia de base de datos de réplica.

La replicación de MySQL utiliza el volcado de registros binarios, el receptor de E/S de replicación y los subprocesos del aplicador de SQL de replicación. Para obtener más información sobre el funcionamiento de los subprocesos, consulta Replication threads (Subprocesos de replicación) en el sitio web de MySQL. Si se produce un retraso en la replicación, comprueba si se debe a la réplica IO_THREAD o SQL_THREAD. A continuación, podrás identificar la causa raíz del retraso.

Resolución

Identificación del subproceso de replicación que se retrasa

Sigue estos pasos:

1. Para identificar dónde la instancia de base de datos principal o de origen escribe los registros binarios, ejecuta el comando SHOW MASTER STATUS en la instancia de base de datos principal:

   mysql> SHOW MASTER STATUS;

   Resultado de ejemplo:

   +----------------------------+----------+--------------+------------------+-------------------+
   | File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
   +----------------------------+----------+--------------+------------------+-------------------+
   | mysql-bin.066552           |      521 |              |                  |                   |
   +----------------------------+----------+--------------+------------------+-------------------+
   1 row in set (0.00 sec)

   Nota: En el resultado del ejemplo anterior, la instancia de base de datos principal o de origen escribe los registros binarios en el archivo mysql-bin.066552.
2. Para conocer el estado de salida, ejecuta el comando SHOW SLAVE STATUS o SHOW REPLICA STATUS en la instancia de base de datos de la réplica:

   mysql> SHOW SLAVE STATUS\G;

Resultado de ejemplo 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

En el resultado de ejemplo anterior, el archivo Master_Log_File: mysql-bin.066548 muestra que la réplica IO_THREAD lee el archivo de registro binario mysql-bin.066548. La instancia de base de datos principal escribe los registros binarios en el archivo mysql-bin.066552. La réplica IO_THREAD tiene cuatro registros binarios de retraso. Sin embargo, dado que Relay_Master_Log_File es mysql-bin.066548, la réplica SQL_THREAD lee el mismo archivo que IO_THREAD. La réplica SQL_THREAD mantiene la velocidad, pero la réplica IO_THREAD se retrasa.

Resultado de ejemplo 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

El resultado del ejemplo anterior muestra que el archivo de registro de la instancia principal es mysql-bin.066552. IO_THREAD mantiene la velocidad con la instancia de base de datos principal. En el resultado de la réplica, el subproceso de SQL ejecuta Relay_Master_Log_File: mysql-bin.066530. Como resultado, SQL_THREAD tiene un retraso de 22 registros binarios.

Dado que IO_THREAD solo lee los registros binarios de la instancia principal o de origen, normalmente IO_THREAD no provoca grandes retrasos de la replicación. Sin embargo, la conectividad y la latencia de la red pueden afectar a la velocidad de las lecturas entre los servidores. El uso elevado del ancho de banda puede hacer que la réplica IO_THREAD funcione más lentamente.

Si la réplica SQL_THREAD es la causa de los retrasos en la replicación, utiliza los siguientes pasos para resolver el problema.

Consultas de escritura de ejecución prolongada en la instancia principal

Las consultas de escritura de ejecución prolongada en la instancia de base de datos principal que tardan el mismo tiempo en ejecutarse en la instancia de base de datos de réplica pueden aumentar el valor seconds_behind_master. Por ejemplo, si un cambio en la instancia principal tarda 1 hora en ejecutarse, el retraso es de 1 hora. Si el cambio también tarda 1 hora en completarse en la réplica, el retraso total es de 2 horas.

Para minimizar el retraso, supervisa el registro de consultas lento en la instancia principal e identifica las consultas que necesitan optimización. También puedes reducir las instrucciones de larga duración a instrucciones o transacciones más pequeñas.

Para solucionar problemas de rendimiento relacionados con binlog, analiza los eventos de espera que se encuentran en Información de rendimiento para obtener más información. También puedes ajustar los niveles de aislamiento.

Tamaño o almacenamiento insuficiente de la clase de la instancia de base de datos

Si la configuración del almacenamiento o la clase de la instancia de base de datos de réplica son inferiores a las de la instancia principal, es posible que la réplica se vea limitada debido a la falta de recursos. La réplica no puede mantener la cantidad de cambios en la instancia principal.

Para resolver este problema, asegúrate de que el tipo de instancia de base de datos de réplica sea igual o superior al de la instancia de base de datos principal. Para que las replicaciones funcionen de manera eficaz, cada réplica de lectura requiere el mismo número de recursos de computación y almacenamiento que la instancia de base de datos de origen.

Ejecución de consultas paralelas en la instancia de base de datos principal

De forma predeterminada, la replicación de MySQL es de un solo subproceso. Cuando ejecutas consultas en paralelo en la instancia principal, las consultas se confirman en la réplica por orden. Cuando se produce un gran volumen de escrituras en la instancia de origen en paralelo, las escrituras en la réplica de lectura usan un único SQL_THREAD para serializarse. A continuación, puede producirse un retraso entre la instancia de base de datos de origen y la réplica de lectura.

Con las versiones 8.0.27 y posteriores de MySQL, el valor predeterminado para replica_parallel_workers es 4. Este valor significa que las réplicas tienen subprocesos múltiples de forma predeterminada. Del mismo modo, para la versión 3.04 y posteriores de Aurora MySQL, la replicación es multiproceso de forma predeterminada, con el valor replica_parallel_workers establecido en 4. Puedes modificar este parámetro en tu grupo de parámetros personalizado.

Para obtener más información sobre la replicación de varios subprocesos, consulta Binary logging options and variables (Variables y opciones de registros binarios) en el sitio web de MySQL.

La replicación de varios subprocesos puede provocar brechas en la replicación. Por ejemplo, es difícil identificar las transacciones que se omiten. Cuando se utiliza la replicación con subprocesos múltiples, no se recomienda omitir los errores de replicación. Esto puede provocar brechas en la coherencia de datos entre la instancia de base de datos principal y la réplica.

Registros binarios sincronizados con el disco de la instancia de base de datos de réplica

Cuando se activan las copias de seguridad automáticas en la réplica, se produce una sobrecarga para sincronizar los registros binarios con el disco de la réplica. El valor predeterminado del parámetro sync_binlog es 1. Si cambias el valor a 0, el servidor MySQL no podrá sincronizar el registro binario con el disco. En su lugar, el sistema operativo (SO) de vez en cuando vacía los registros binarios en el disco.

Para reducir la sobrecarga de rendimiento necesaria para sincronizar los registros binarios con el disco en cada confirmación, desactiva la sincronización de registros binarios. Sin embargo, si se produce un fallo eléctrico o el sistema operativo se bloquea, es posible que algunas de las confirmaciones no se sincronicen con los registros binarios. La falta de sincronización puede afectar a las capacidades de recuperación a un momento dado (PITR). Para obtener más información, consulta sync_binlog en el sitio web de MySQL.

Binlog_format se ha definido como ROW

Cuando se produce la replicación en los siguientes escenarios, el subproceso de SQL realiza un análisis completo de la tabla:

• En la instancia de base de datos principal binlog_format se ha definido como ROW.
• La tabla de origen no tiene una clave principal.

Este análisis se produce porque el valor predeterminado del parámetro slave_rows_search_algorithms es TABLE_SCAN,INDEX_SCAN.

Para solucionar este problema temporalmente, cambia el algoritmo de búsqueda a INDEX_SCAN,HASH_SCAN con el fin de reducir la sobrecarga de un análisis completo de la tabla. Para obtener una solución más permanente, se recomienda agregar una clave principal explícita a cada tabla.

Para obtener más información sobre el parámetro slave-rows-search-algorithms, consulta slave_rows_search_algorithms en el sitio web de MySQL.

Retraso en la creación de réplicas (aplicable a RDS para MySQL)

Para crear una réplica de lectura de una instancia principal de MySQL, Amazon RDS toma una instantánea de la base de datos. A continuación, Amazon RDS restaura la instantánea para crear una nueva instancia de base de datos y establece la replicación entre ambas.

Una vez que Amazon RDS establece la replicación, se produce un retraso cuando Amazon RDS crea una copia de seguridad de la instancia de base de datos principal. Para minimizar este retraso, crea una copia de seguridad manual antes de solicitar la creación de la réplica. La instantánea de base de datos se convierte entonces en una copia de seguridad incremental.

Cuando se restaura una réplica de lectura a partir de una instantánea, la réplica no espera a que se transfieran todos los datos desde el origen. La instancia de base de datos de réplica está disponible para llevar a cabo operaciones de base de datos. Las instantáneas existentes de Amazon Elastic Block Store (Amazon EBS) crean un nuevo volumen en segundo plano.

Nota: En el caso de las réplicas de Amazon RDS para MySQL (volúmenes respaldados por Amazon EBS), el retraso de la réplica podría aumentar inicialmente porque la carga diferida puede afectar al rendimiento de la replicación.

Para reducir los efectos de la carga diferida en las tablas de tu nueva réplica de lectura, realiza operaciones que utilicen análisis de tablas completas. Por ejemplo, ejecuta mysqldump en tu réplica de lectura para tablas o bases de datos específicas para dar prioridad a todos los datos de tablas de los que se hayan hecho copias de seguridad desde Amazon Simple Storage Service (Amazon S3).

También puedes utilizar la característica de calentamiento de la caché de InnoDB bajo demanda para RDS para MySQL. La característica de calentamiento de la caché de InnoDB guarda el estado del grupo de búferes en el disco, en un archivo denominado ib_buffer_pool en el directorio de datos de InnoDB. Dado que Amazon RDS descarga el estado actual del grupo de búferes de la instancia de base de datos principal antes de crear la réplica de lectura, el rendimiento mejora con la carga menor. A continuación, puedes volver a cargar el grupo de búferes una vez creada la réplica de lectura.

Efectos del retraso de la réplica

Un retraso de réplica grande en la instancia o el clúster de réplica puede provocar la acumulación de registros binarios en el origen. Para evitar posibles problemas, supervisa y administra el tamaño de los archivos binlog y el uso del espacio en disco. En el caso de RDS, puedes supervisar la métrica BinLogDiskUsage en la instancia de base de datos de origen.

Configura un periodo de retención de binlog óptimo para equilibrar las capacidades de recuperación a un momento dado con el uso del almacenamiento en una instancia MySQL de RDS y en Aurora. Además, para aprovechar los binlogs para la replicación y la recuperación, comprende las convenciones de nomenclatura de los archivos binlog y el comportamiento de rotación. Para obtener una lista de los registros disponibles, ejecuta el comando SHOW BINARY LOGS.

Información relacionada

Uso de la replicación de MySQL en Amazon RDS

Uso de réplicas de lectura de MySQL

Optimización de la replicación de registros binarios para Aurora MySQL