¿Por qué mi consulta SELECT se ejecuta lentamente en mi clúster de base de datos de Amazon Aurora MySQL?

Descripción breve

Hay varios motivos por los que las consultas SELECT pueden ejecutarse con lentitud en su clúster de base de datos compatible con Aurora MySQL:

• Los recursos del sistema de Amazon Relational Database Service (Amazon RDS) están sobreutilizados. Esto puede ocurrir debido a una alta utilización de la CPU, poca memoria o una carga de trabajo que supera lo que puede gestionar su tipo de instancia de base de datos.
• La base de datos se bloquea y los eventos de espera resultantes provocan que las consultas SELECT no funcionen correctamente.
• La consulta SELECT escanea tablas completas en tablas grandes o la consulta carece de los índices necesarios.
• La longitud de la lista de historial (HLL) de InnoDB ha aumentado considerablemente debido a las transacciones de larga duración.

Resolución

Supervisión de los recursos del sistema de Amazon RDS usando métricas

Se recomienda supervisar siempre el uso de la CPU y liberar memoria en el clúster de Amazon Aurora. Si bien es normal que se produzcan picos de uso de CPU ocasionales, una utilización alta y constante de la CPU durante periodos de tiempo prolongados puede hacer que las consultas SELECT se ejecuten lentamente. Utilice las siguientes herramientas para determinar cómo y dónde se utiliza su CPU:

1.    Las métricas de Amazon CloudWatch son la forma más sencilla de supervisar el uso de la CPU. Para obtener más información sobre las métricas disponibles para Aurora, consulte Métricas de CloudWatch para Aurora.

2.    Supervisión mejorada ofrece una visión detallada de las métricas a nivel del sistema operativo con un grado de detalle más bajo. El desglose detallado muestra de qué forma los procesos utilizan la CPU.

3.    Información de rendimiento determina con precisión la carga de la base de datos. Active Información de rendimiento para su instancia de base de datos y, a continuación, compruebe si la carga supera la vCPU máxima. También puede supervisar las consultas con carga y los SQL por tiempos de espera, e identificar los usuarios que provocan los mayores tiempos de espera.

Las consultas SELECT también pueden ejecutarse lentamente debido a las búsquedas en el disco. Para minimizar la E/S del disco, el motor de la base de datos intenta almacenar en caché el bloque leído del disco. Esto significa que la próxima vez que la base de datos necesite el mismo bloque de datos, ese bloque se obtendrá de la memoria y no del disco.

Utilice estas métricas para comprobar si está atendiendo una consulta concreta desde el disco o la memoria:

• VolumeReadsIOPS: Esta métrica es el número de operaciones de lectura de [disco] a nivel de volumen facturado. Se recomienda que este valor sea lo más bajo posible.
• BufferCacheHitRatio: Esta métrica es el porcentaje de solicitudes que atiende la memoria caché del búfer. Se recomienda que este valor sea lo más alto posible. Si BufferCacheHitRatio cae y su instrucción SELECT es lenta, está procesando la consulta desde volúmenes subyacentes.

Otro recurso importante para identificar las instrucciones SELECT lentas es el registro de consultas lentas. Active el registro de consultas lentas en su clúster de base de datos para registrar estas consultas y tomar medidas más adelante. Para la versión compatible con MySQL 5.6, utilice el esquema de rendimiento de MySQL para supervisar el rendimiento de las consultas de forma continua.

Identificación de bloqueos y eventos de espera

Amazon RDS bloquea los datos de la base de datos para que solo una sesión de usuario pueda escribir o actualizar una fila en un momento dado. Cualquier otra transacción que requiera esta fila se mantendrá en espera. En un bloqueo compartido, las transacciones de escritura/actualización se mantienen en espera mientras que las transacciones de lectura leen los datos. Si una consulta está esperando acceder a una fila que está bloqueada por otra consulta, esto puede provocar un bloqueo.

Para identificar los interbloqueos en la base de datos, habilite el parámetro innodb_print_all_deadlocks en sus grupos de parámetros. A continuación, supervise el parámetro mysql-error.log desde la consola, la CLI o la API de RDS.

También puede iniciar sesión en MySQL con una cuenta de administrador y, a continuación, ejecutar este comando para identificar los interbloqueos en el resultado del comando en la sección Último interbloqueo detectado:

mysql> SHOW ENGINE INNODB STATUS\G;

Comprobar si la consulta utiliza un índice

Si una consulta no tiene un índice o realiza escaneos de la tabla completa, la consulta se ejecuta más lentamente. Los índices ayudan a acelerar las consultas SELECT.

Para comprobar si la consulta utiliza un índice, utilice la consulta EXPLAIN. Esta es una herramienta útil para solucionar problemas relacionados con consultas lentas. En el resultado de EXPLAIN, compruebe los nombres de las tablas, la clave utilizada y el número de filas escaneadas durante la consulta. Si el resultado no muestra ninguna clave en uso, cree un índice en las columnas utilizadas en la cláusula WHERE.

Si la tabla requiere indexación, compruebe si las estadísticas de la tabla están actualizadas. Al asegurarse de que las estadísticas son precisas, el optimizador de consultas utiliza los índices más selectivos con la cardinalidad correcta. Esto mejora el rendimiento de las consultas.

Comprobar la longitud de la lista de historial (HLL)

InnoDB utiliza un concepto denominado control de concurrencia de versiones múltiples (MVCC). MVCC conserva varias copias del mismo registro para preservar la coherencia de la lectura. Esto significa que, cuando se confirma una transacción, InnoDB purga las copias antiguas. Sin embargo, cuando una transacción no se confirma durante mucho tiempo debido al crecimiento de los segmentos de deshacer, la longitud de la lista del historial (HLL) aumenta. La longitud de la lista del historial de InnoDB representa el número de cambios sin vaciar.

Si su carga de trabajo exige varias transacciones abiertas o de larga duración, puede esperar ver un HLL alto en la base de datos.

Nota: Las transacciones de larga duración no son la única causa de los picos de HLL. Incluso si los subprocesos de purga no pueden seguir el ritmo de los cambios en la base de datos, puede ser que el HLL siga alto.

Si no supervisa el tamaño del HLL, el rendimiento retrocede con el tiempo. El aumento del tamaño del HLL también puede provocar un mayor consumo de recursos, un rendimiento más lento e incoherente de la sentencia SELECT y un aumento del almacenamiento. En casos extremos, esto puede provocar la interrupción de la base de datos.

Para comprobar la longitud de la lista del historial, ejecute el siguiente comando:

SHOW ENGINE INNODB STATUS;

Salida:

------------ TRANSACTIONS ------------
Trx id counter 26368570695
Purge done for trx's n:o < 26168770192 undo n:o < 0 state: running but idle History list length 1839

En el caso de Aurora MySQL, debido a la naturaleza de los volúmenes del almacenamiento compartido, la longitud de la lista de historial es a nivel de clúster y no a nivel de instancia individual. Conéctese a su escritor y ejecute la siguiente consulta:

SELECT server_id, IF(session_id = 'master_session_id', 'writer', 'reader') AS ROLE, replica_lag_in_msec,
       oldest_read_view_trx_id , oldest_read_view_lsn
       from mysql.ro_replica_status;

Esta consulta le ayuda a comprender el retraso de la réplica entre los nodos del lector y el nodo de escritura. También detalla el LSN más antiguo utilizado por la instancia de base de datos para leer desde el almacenamiento y el ID de TRX más antiguo de la vista de lectura de la instancia de base de datos. Utilice esta información para comprobar si uno de los lectores tiene una vista de lectura antigua (en comparación con el estado de InnoDB del motor en el escritor).

Nota: A partir de las versiones 1.19 y 2.06 de Aurora MySQL, puede supervisar el HLL con la métrica RollbackSegmentHistoryListLength de CloudWatch. En versiones anteriores, también puede utilizar trx_rseg_history_len para consultar el HLL mediante el siguiente comando:

select NAME AS RollbackSegmentHistoryListLength,
COUNT from INFORMATION_SCHEMA.INNODB_METRICS where NAME = 'trx_rseg_history_len';

Si la opción Información de rendimiento está activada para sus instancias de Aurora MySQL, puede consultar RollbackSegmentHistoryListLength. Navegue hasta Información de rendimiento del escritor y haga lo siguiente:

1.    Seleccione Administrar métricas y, a continuación, seleccione Métricas de base de datos.

2.    Seleccione la métrica trx_rseg_history_len y, a continuación, seleccione Actualizar gráfico.

Utilice los siguientes métodos para resolver los problemas relacionados con el crecimiento del HLL:

• Si el DML (escrituras) provoca el crecimiento del HLL: La cancelación o finalización de esta sentencia implica la anulación de la transacción interrumpida. Esto lleva mucho tiempo, ya que todas las actualizaciones realizadas hasta el momento se están anulando.
• Si READ provoca el crecimiento del HLL: Finalice la consulta utilizando mysql.rds_kill_query.
• En función del tiempo que esté ejecutándose la consulta, trabaje con su administrador de base de datos para comprobar si puede finalizar la consulta mediante el procedimiento almacenado.

Se recomienda evitar el crecimiento supervisando el HLL mediante estos métodos y evitar transacciones abiertas o de larga duración en la base de datos. Además, se recomienda guardar los datos en lotes más pequeños.

Importante: No reinicie el clúster ni la instancia de base de datos. Es más eficiente purgar el HLL cuando este puede acceder a los datos de la memoria en el conjunto de búferes. Si reinicia la base de datos, es posible que la caché de páginas que puede sobrevivir se pierda. Cuando eso ocurre, se deben leer las páginas de datos del volumen del clúster para purgar el HLL. Este proceso es más lento que hacerlo en la memoria y provoca costes de facturación de E/S adicionales.

---

Información relacionada

Supervisión de los registros de Amazon Aurora MySQL, Amazon RDS para MySQL y MariaDB con Amazon CloudWatch