¿Cómo puedo solucionar un retraso importante de la réplica con Amazon RDS para MySQL?

Descripción corta

Dado que Amazon RDS para MySQL utiliza la replicación asincrónica, a veces la réplica no puede progresar con la instancia de base de datos principal y provoca un retraso en la replicación.

Para supervisar el retraso de la replicación, utilice una réplica de lectura de RDS para MySQL con replicación basada en la posición de los archivos de registro binarios.

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

El campo Seconds_Behind_Master muestra la marca de tiempo actual en la instancia de base de datos de réplica. También muestra la marca de tiempo original registrada 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, consulte Replication threads (Subprocesos de replicación) en el sitio web de MySQL. Si se produce un retraso en la replicación, averigüe si se debe a la réplica IO_THREAD o SQL_THREAD. A continuación, podrá identificar la causa raíz del retraso.

Resolución

Identificación del subproceso de replicación que está retrasado

Ejecute 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.

Ejecute el comando SHOW SLAVE STATUS en la instancia de base de datos de 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

Nota: 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.

Normalmente, IO_THREAD no provoca grandes retrasos de la replicación porque IO_THREAD solo lee los registros binarios de la instancia principal o de origen. 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, utilice 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 aproximadamente 2 horas.

Para minimizar el retraso, puede supervisar el registro lento de las consultas en la instancia principal. También puede reducir las instrucciones de larga duración a instrucciones o transacciones más pequeñas.

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úrese 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 la replicación funcione 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. Para obtener más información, consulte Clases de instancia de base de datos.

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

La replicación de MySQL es de un solo subproceso de forma predeterminada. Por lo tanto, cuando ejecuta 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. Esto puede provocar un retraso entre la instancia de base de datos de origen y la réplica de lectura.

La replicación de varios subprocesos (en paralelo) está disponible para MySQL 5.6 y versiones posteriores. Para obtener más información sobre la replicación de varios subprocesos, consulte 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, la replicación de varios subprocesos no se recomienda cuando se omiten los errores de replicación, porque es difícil identificar qué transacciones omite. 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 cambia el valor a 0, también desactivará la capacidad del servidor MySQL para sincronizar el registro binario con el disco. En su lugar, el sistema operativo 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, desactive 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, consulte sync_binlog en el sitio web de MySQL.

Binlog_format se ha definido como ROW

El subproceso de SQL realiza un análisis completo de la tabla cuando se replica en los siguientes escenarios:

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

Esto se debe a que el valor predeterminado del parámetro slave_rows_search_algorithms es TABLE_SCAN,INDEX_SCAN.

Para solucionar este problema temporalmente, cambie 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 añadir una clave principal explícita a cada tabla.

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

Retraso en la creación de la réplica

Amazon RDS toma una instantánea de la base de datos para crear una réplica de lectura de una instancia principal de MySQL. 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.

Tras establecer 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, cree una copia de seguridad manual antes de solicitar la creación de la réplica. La instantánea de la base de datos será 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 su nueva réplica de lectura, puede realizar operaciones que utilicen análisis de tablas completas. Por ejemplo, ejecute mysqldump en su réplica de lectura para tablas o bases de datos específicas, de modo que Amazon RDS dé 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 puede utilizar la característica de calentamiento de la caché de InnoDB bajo demanda. 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. El rendimiento mejora porque 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. Una vez creada una réplica de lectura, puede volver a cargar el grupo de búferes.

Información relacionada

Uso de la replicación de MySQL en Amazon RDS

Uso de réplicas de lectura de MySQL