¿Cómo puedo solucionar los problemas de una consulta que se ejecuta con lentitud y mejorar el rendimiento de mi consulta en Amazon RDS para MySQL?

Resolución

Para solucionar los problemas de una consulta que se ejecuta con lentitud y mejorar el rendimiento de las consultas, sigue estos pasos:

Comprobación de la utilización de los recursos

Para supervisar la utilización de los recursos e identificar cuándo disminuye el rendimiento de las consultas, haz lo siguiente:

• Para supervisar tus recursos durante un periodo de tiempo específico, utiliza las métricas de Amazon CloudWatch.
• Para supervisar el rendimiento de las bases de datos, utiliza la consola de Amazon RDS para ver las métricas de rendimiento.
• Para identificar los procesos activos o programados que podrían afectar al rendimiento de las bases de datos, comprueba el estado de la instancia. Asegúrate de comprobar los eventos de Amazon RDS que se produjeron cuando el rendimiento de la base de datos era deficiente.

Comprobación de la carga de trabajo que contribuye al consumo de recursos

Para comprobar la carga de trabajo que contribuye al consumo de recursos, utiliza Información de rendimiento de Amazon RDS. Si la carga de trabajo actual supera el límite de vCPU, el servidor está sobrecargado. Si el servidor está sobrecargado, comprueba las consultas que contribuyen a la carga de trabajo y optimiza las consultas. A continuación, modifica la clase de la instancia.

Para investigar el recurso que consume la mayor cantidad de eventos de espera, divide la carga de la base de datos según el número de eventos de espera. Las bandas de color más gruesas en el gráfico de carga indican los tipos de espera que más contribuyen a la carga de trabajo. Para obtener más información, consulta Monitoreo de la carga de base de datos con Performance Insights en Amazon RDS.

Para identificar las consultas lentas, activa slow_query_log en el grupo de parámetros. Para comprobar si la carga de trabajo de la instancia ha aumentado, utiliza las métricas de CloudWatch para comprobar lo siguiente:

• Conexiones a bases de datos: El número de sesiones de cliente que están conectadas a la instancia de base de datos.
• Rendimiento de recepción de red (MB/segundo): la velocidad del tráfico de red de entrada y salida de la instancia de base de datos.
• Rendimiento de escritura y lectura: la cantidad promedio de megabytes leídos o escritos en el disco por segundo.
• Latencia de escritura y lectura: el tiempo medio de una operación de lectura o escritura en milisegundos. La correlación de las métricas de latencia con el aumento de las conexiones a la base de datos o las métricas de rendimiento puede indicar que la carga de trabajo es la causa de la lentitud de las consultas. Para obtener más información, consulta ¿Por qué el almacenamiento de mi instancia de Amazon RDS para MySQL o MariaDB está lleno?
• IOPS (lectura y escritura): la cantidad promedio de operaciones de lectura o escritura en disco por segundo.
• Espacio de almacenamiento libre (MB): la cantidad de espacio en disco que la instancia de base de datos no utiliza actualmente.
• Equilibrio de ráfagas (%): el porcentaje de créditos de E/S de ráfaga de SSD de uso general (gp2) disponibles.

Para ver la lista de sistemas operativos (SO) de la carga de trabajo y las métricas del sistema, utiliza la supervisión mejorada. De forma predeterminada, el intervalo para la supervisión mejorada es de 60 segundos. Se recomienda establecer el intervalo entre 1 y 5 segundos para obtener puntos de datos más detallados.

Comprobación de si las consultas tienen un índice o si escanean toda la tabla

Si la consulta tiene un índice o escanea toda la tabla, la consulta se ejecuta con lentitud.

Para comprobar si la consulta utiliza un índice, ejecuta la consulta EXPLAIN. Para obtener más información, consulta EXPLAIN Statement (Instrucción EXPLAIN) en el sitio web de MySQL. En el resultado de EXPLAIN, comprueba los nombres de las tablas, las claves utilizadas y el número de filas que se escanean para la consulta. Si el resultado no muestra ninguna clave en uso, crea un índice en las columnas utilizadas en la cláusula WHERE. Si la tabla requiere indexación, comprueba que las estadísticas de la tabla estén actualizadas. Para obtener más información, consulta The INFORMATION_SCHEMA STATISTICS Table (La tabla INFORMATION_SCHEMA STATISTICS) en el sitio web de MySQL. Cuando las estadísticas están actualizadas, el optimizador de consultas utiliza los índices más selectivos con la cardinalidad correcta. Como resultado, el rendimiento de las consultas mejora.

Comprobación de la longitud de la lista de historial

InnoDB utiliza el control de concurrencia de versiones múltiples (MVCC). MVCC conserva varias copias del mismo registro para preservar la coherencia de la lectura. La longitud de la lista de historial 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. Además, la longitud de la lista de historial aumenta mientras que otras transacciones modifican las tablas que utiliza la transacción de larga duración.

Si tu carga de trabajo exige varias transacciones abiertas o de larga duración, puedes esperar ver una longitud de la lista de historial alta en la base de datos. Si no supervisas el tamaño de la longitud de la lista de historial, el rendimiento puede disminuir con el tiempo. Una longitud de la lista de historial alta también puede provocar un alto consumo de recursos, un rendimiento lento e incoherente de las instrucciones SELECT y un aumento del almacenamiento.

Nota: Las transacciones de larga duración no son la única causa de los picos de longitud de la lista de historial. Si los hilos de purga no pueden mantenerse al día con los cambios de la base de datos, la longitud de la lista de historial sigue siendo alta. En casos extremos, también puede producirse una interrupción de la base de datos.

Para comprobar la longitud de la lista de historial, 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 has activado Información de rendimiento en tus instancias de Amazon RDS para MySQL, sigue los pasos que se indican a continuación para comprobar el valor de RollbackSegmentHistoryListLength:

1. Navega hasta Información de rendimiento del escritor.
2. Selecciona Administrar métricas y, a continuación, selecciona Métricas de base de datos.
3. Selecciona la métrica trx_rseg_history_len y, a continuación, selecciona Actualizar gráfico.

Para resolver los problemas que hacen que la longitud de la lista de historial aumente, haz lo siguiente:

• Si las escrituras de DML hacen que aumente la longitud de la lista de historial, cancela o detén las instrucciones de escritura. Esto implica una reversión de la transacción interrumpida y lleva mucho tiempo deshacer las actualizaciones.
• Si una instrucción READ hace que la longitud de la lista de historial aumente, utiliza mysql.rds_kill_query para detener la consulta.
• Si la consulta se ejecuta durante mucho tiempo, trabaja con el administrador de bases de datos para comprobar si puedes detener la consulta.

Nota: Se recomienda que evites las transacciones abiertas o de ejecución prolongada en la base de datos y que guardes los datos en lotes pequeños.

Mejora del rendimiento de las consultas

Para mejorar el rendimiento de las consultas, utiliza las siguientes prácticas recomendadas:

• Para encontrar los estados en los que se dedica más tiempo, perfila las consultas más lentas. Para obtener más información, consulta SHOW PROFILE statement (Instrucción SHOW PROFILE) en el sitio web de MySQL.

• Ejecuta el comando SHOW FULL PROCESSLIST junto con la supervisión mejorada. Cuando se usan juntos, puedes revisar la lista de operaciones que se realizan actualmente en el servidor de base de datos.

• Utiliza el comando SHOW ENGINE INNODB STATUS para obtener información sobre el procesamiento de transacciones, los eventos de espera y los interbloqueos.

• Busca y resuelve las consultas de bloqueo. Para obtener más información, 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?

• Publica los registros de MySQL en Registros de CloudWatch. Los registros cambian cada hora para mantener el límite del 2 % del espacio de almacenamiento asignado. Los registros que tienen más de dos semanas de antigüedad o que tienen un tamaño combinado que supera el límite del 2 % se eliminan.

• Para supervisar el uso de los recursos y recibir alertas cuando se superen los límites, configura una alarma de CloudWatch.

• Busca el plan de operación de la consulta y comprueba si la consulta utiliza los índices adecuados. Usa EXPLAIN para optimizar la consulta y ver detalles sobre cómo MySQL ejecuta la consulta. Para obtener más información, consulta Optimizing Queries with EXPLAIN (Optimización de consultas con EXPLAIN) en el sitio web de MySQL.

• Mantén actualizadas las estadísticas de las consultas con ANALYZE TABLE. Para obtener más información, consulta ANALYZE TABLE statement (Instrucción ANALYZE TABLE) en el sitio web de MySQL.

• MySQL 8.0 usa EXPLAIN ANALYZE. La instrucción EXPLAIN ANALYZE muestra dónde MySQL asigna los tiempos en la consulta y por qué se asigna ese tiempo. Cuando se completa la consulta, EXPLAIN ANALYZE imprime el plan y sus medidas. Para obtener más información, consulta Obtaining Information with EXPLAIN ANALYZE (Obtención de información con EXPLAIN ANALYZE) en el sitio web de MySQL.

• En MySQL 8.0, las esperas de bloqueo se enumeran en el esquema de rendimiento de la tabla data_lock_waits. Para obtener más información, consulta Using InnoDB transaction and locking information (Uso de la información de transacciones y bloqueos de InnoDB) en el sitio web de MySQL.

  Ejemplo:

  SELECT  r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
  FROM       performance_schema.data_lock_waits w
  INNER JOIN information_schema.innodb_trx b
    ON b.trx_id = w.blocking_engine_transaction_id
  INNER JOIN information_schema.innodb_trx r
    ON r.trx_id = w.requesting_engine_transaction_id;