¿Cómo soluciono los problemas de una instrucción SELECT que se ejecuta lentamente en mi clúster de base de datos de Aurora MySQL-Compatible?

Descripción corta

La instrucción SELECT puede ejecutarse con lentitud en el clúster de base de datos de Aurora MySQL-Compatible por los siguientes motivos:

• Estás usando de manera excesiva los recursos del sistema de bases de datos.
• La base de datos está bloqueada.
• La instrucción SELECT escanea tablas completas en tablas grandes. O bien, la consulta no tiene los índices necesarios.
• Las transacciones de larga duración aumentan la longitud de tu lista de historial (HLL) de InnoDB.

Resolución

Nota: Si se muestran errores al ejecutar comandos de la Interfaz de la línea de comandos de AWS (AWS CLI), consulta Solución de problemas de AWS CLI. Además, asegúrate de utilizar la versión más reciente de la AWS CLI.

Uso de CloudWatch Database Insights para comprobar por qué una instrucción SELECT se ejecuta con lentitud

Activa Performance Insights y utiliza Database Insights para detectar las consultas que provocan una carga elevada de la base de datos. Supervisa dimensiones como SQL, usuarios y esperas en el gráfico de carga de la base de datos. Asimismo, supervisa la sección Consultas de SQL lentas de la pestaña Telemetría de base de datos. Además, puedes analizar la ejecución de consultas a partir de las estadísticas SQL de Performance Insights. Por ejemplo, si el número de filas que has examinado por llamada es superior a lo normal, significa que el plan de ejecución es ineficaz.

Uso de las métricas para supervisar los recursos de los sistemas de instancias de base de datos

Un uso elevado de la CPU, que quede poca memoria o las cargas de trabajo grandes que superan las capacidades de la clase de la instancia de base de datos pueden provocar que la instrucción SELECT se ejecute con lentitud. Para supervisar los recursos de la instancia de base de datos, utiliza las siguientes herramientas:

• Usa las métricas de Amazon CloudWatch para Amazon Aurora para supervisar el uso de la CPU.
• Usa la opción Supervisión mejorada para ver los detalles de las métricas del sistema operativo (SO).
• Usa los datos de los procesos del sistema operativo en la pestaña Telemetría de la base de datos para comprobar si la carga de la base de datos supera el máximo de vCPU.

Las búsquedas de disco pueden provocar que la instrucción SELECT se ejecute con lentitud. Para minimizar la E/S del disco, el motor de la base de datos almacena en caché el bloque leído del disco. Cuando la base de datos necesita usar los mismos datos, los obtiene de la memoria en lugar del disco. Para determinar si se atiende a una consulta específica desde el disco o la memoria, utiliza las siguientes métricas:

• Comprueba la métrica ReadIOPS para ver el número de operaciones de E/S del disco. Se recomienda mantener este valor lo más bajo posible.
• Comprueba la métrica BufferCacheHitRatio para ver el porcentaje de solicitudes que atiende la caché del búfer. Se recomienda mantener este valor lo más alto posible.
• Comprueba la métrica FreeableMemory para ver la memoria disponible de la instancia de base de datos. Se recomienda mantener este valor estable. La falta de memoria disponible puede provocar un bajo nivel de BufferCacheHitRatio y un nivel alto de RReadIOPS.
  Nota: Si la métrica BufferCacheHitRatio cae y la instrucción SELECT se ejecuta con lentitud, el motor procesa las consultas de los volúmenes subyacentes.

La búsqueda de almacenamiento local en disco también puede provocar que la instrucción SELECT se ejecute con lentitud. La versión Aurora MySQL-Compatible usa el almacenamiento local para tablas temporales manuales e internas. Para más información, consulta Nuevo comportamiento de tabla temporal en Aurora MySQL versión 3. Para supervisar y resolver las búsquedas de disco del almacenamiento local, comprueba las métricas del sistema operativo Rstemp de Database Insights. Se recomienda mantener estos valores lo más bajos posible.

La saturación de la red puede provocar que las instrucciones SELECT se ejecuten con lentitud. Aurora ejecuta las operaciones de E/S en el volumen del clúster a través de la red y envía el resultado de la consulta al cliente a través de la red. Para supervisar y resolver la saturación de la red, comprueba las métricas NetworkThroughput y StorageNetworkThroughput. El rendimiento total de la red debe ser inferior al ancho de banda de red de la instancia de base de datos.

Si el uso de algún recurso supera las capacidades de tu tipo de instancia de base de datos debido a la carga de trabajo, actualiza la clase de instancia de base de datos.

Identificación de bloqueos e interbloqueos

Un interbloqueo se produce cuando dos o más transacciones no pueden continuar porque se bloquean entre sí. Para identificar los interbloqueos en la base de datos, activa el parámetro innodb_print_all_deadlocks en tus grupos de parámetros. Para más información, consulta innodb_print_all_deadlocks en el sitio web de MySQL. A continuación, supervisa el archivo mysql-error.log desde la consola de Amazon RDS, la interfaz de línea de comandos de AWS o la API.

(Opcional) Para identificar los interbloqueos, inicia sesión en una cuenta de administrador de MySQL y, a continuación, ejecuta el siguiente comando:

SHOW ENGINE INNODB STATUS\G;

Nota: En el resultado esperado de MySQL Workbench, consulta la sección Último interbloqueo detectado.

Incluso si no hay ningún interbloqueo, una transacción larga que mantenga los bloqueos podría estar creando esos bloqueos. Para saber qué bloqueos están activos, consulta ¿Por qué se ha bloqueado una consulta a mi instancia de base de datos de Amazon RDS para MySQL cuando no hay ninguna otra sesión activa?

Comprueba si la consulta utiliza un índice

Si una consulta no tiene un índice o realiza escaneos completos de la tabla, la consulta se ejecutará con lentitud. Los índices permiten que las instrucciones SELECT se ejecuten más rápido. Para comprobar si la consulta utiliza un índice, usa la instrucción EXPLAIN. Para más información, consulta instrucción EXPLAIN en el sitio web de MySQL.

En el resultado EXPLAIN, comprueba los nombres de las tablas, la clave y el número de filas que el motor escanea durante la consulta. Si el resultado no muestra claves en uso, crea un índice en las columnas en la cláusula WHERE. Si la tabla tiene la indexación necesaria, comprueba si la tabla de estadísticas está actualizada. Utiliza la cláusula ANALYZE para actualizar las estadísticas. Para más información, consulta The INFORMATION_SCHEMA STATISTICS Table (La tabla INFORMATION_SCHEMA STATISTICS) en el sitio web de MySQL.

Para identificar instrucciones SELECT que se ejecutan con lentitud, usa el comando slow_query_log. Para registrar las consultas lentas, activa el registro de consultas lentas en el clúster de base de datos.

Comprobación de HLL

InnoDB utiliza el control de concurrencia de versiones múltiples (MVCC). MVCC conserva varias copias del mismo registro para mantener la coherencia de la lectura. La longitud de la lista de historial (HLL) es el número total de registros de acciones deshechas que contienen modificaciones en la lista de historial. Cuando hay una transacción de larga duración que escribe o lee datos, la longitud de la lista de historial aumenta hasta que la transacción se completa o se revierte. Otras transacciones modifican las tablas que utiliza la transacción de larga duración. Si tu carga de trabajo requiere varias transacciones abiertas o de ejecución prolongada, es posible que tu base de datos tenga un valor de HLL alto. Además, si los subprocesos de purga no pueden mantenerse al día con los cambios en la base de datos, es posible que tengas un valor de HLL alto. Un valor de HLL alto provoca un mayor uso de recursos, un rendimiento lento e inconsistente de la instrucción SELECT y un aumento del almacenamiento. En casos extremos, un valor de HLL alto puede provocar una interrupción de la base de datos.

Para supervisar tu HLL, usa la métrica RollbackSegmentHistoryListLength de la instancia de escritura. O bien, ejecuta el siguiente comando:

SHOW ENGINE INNODB STATUS;

Resultado de ejemplo:

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

Si usas Aurora MySQL-Compatible, HLL se encuentra en el nivel de clúster. Para comprobar HLL en el nivel de clúster, conéctate a tu instancia de escritura y ejecuta la siguiente instrucción:

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;

Nota: Puedes usar la instrucción anterior para identificar el retraso de la réplica entre los nodos de lector y escritor. Comprueba el número de secuencia de registro (LSN) más antiguo que la base de datos utiliza para leer desde el almacenamiento y el ID de transacción de vista de lectura más antiguo de la instancia de base de datos (Trx ID). Asegúrate de que una de las instancias contenga una vista de lectura antigua.

Para conectarte a una instancia que contiene una vista de lectura antigua, ejecuta la siguiente instrucción:

SELECT a.trx_id, a.trx_state, a.trx_started,  TIMESTAMPDIFF(SECOND,a.trx_started, now()) as "Seconds Transaction Has Been Open",    
a.trx_rows_modified, b.USER, b.host, b.db, b.command, b.time, b.state    
from information_schema.innodb_trx a, information_schema.processlist b    
where a.trx_mysql_thread_id=b.id    
order by trx_started;

Nota: Utiliza la instrucción anterior para identificar la sesión o transacción que tiene el valor trx_id más antiguo. Para activar la operación de purga, determina si puedes finalizar la sesión.

Para resolver un nivel alto de HLL, toma las siguientes medidas:

• Si las escrituras en DML provocan un aumento en HLL, anula la transacción para cancelar la consulta. Ten en cuenta que este proceso es largo debido a la cantidad de actualizaciones que debes deshacer.
• Si una instrucción READ provoca un aumento en HLL, usa mysql.rds_kill_query para cancelar la consulta.
  Nota: Ponte en contacto con el administrador de la base de datos para comprobar si puedes cancelar una consulta.

Para evitar un nivel alto de HLL, se recomienda guardar los datos en lotes más pequeños. Además, no reinicies el clúster ni la instancia de base de datos. Purga HLL cuando puedas acceder a los datos de memoria del conjunto de búferes. Si reinicias la base de datos, es posible que la caché de páginas que puede sobrevivir se pierda. Si se pierde la caché de páginas que puede sobrevivir, es necesario leer las páginas de datos del volumen del clúster para purgar HLL. Este proceso es más lento que una purga de la memoria y genera 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

Optimización de Aurora MySQL