Cómo habilitar el registro de consultas lentas de MySQL

Las consultas inesperadamente lentas son uno de los problemas de rendimiento de MySQL más comunes. Una consulta que tiene un rendimiento aceptable en el desarrollo puede fallar cuando se ve presionada por una carga de trabajo de producción.

Las aplicaciones grandes pueden ejecutar cientos de consultas de base de datos únicas cada vez que se alcanza un punto final. Esto hace que sea difícil identificar las consultas que están provocando retrasos en la respuesta del servidor. El registro de consultas lentas de MySQL es una opción de depuración que puede ayudarlo a identificar declaraciones SQL sospechosas, proporcionando un punto de partida para sus investigaciones.

Índice de contenidos
  1. Habilitación del registro de consultas lentas
  2. Usando el archivo de configuración de MySQL
  3. Personalización del contenido del registro
  4. Interpretación del registro de consultas lentas
  5. Registro lento de consultas y copias de seguridad
  6. Resumen

Habilitación del registro de consultas lentas

El registro es un mecanismo integrado para registrar consultas SQL de ejecución prolongada. Las consultas que no se completen dentro de un tiempo configurado se escribirán en el registro. La lectura del contenido del registro le muestra el SQL que se ejecutó y el tiempo que tomó.

El registro lento de consultas está desactivado de forma predeterminada. Puede activarlo en su servidor ejecutando el siguiente comando desde un shell administrativo de MySQL:

SET GLOBAL slow_query_log_file="/var/log/mysql/mysql-slow.log";
SET GLOBAL slow_query_log=1;

El cambio se aplica de inmediato. Las consultas lentas ahora se registrarán en /var/log/mysql/mysql-slow.log. Puede revisar este archivo periódicamente para identificar las consultas con un rendimiento deficiente.

MySQL cuenta una consulta como "lenta" si tarda más de 10 segundos en completarse. Este límite suele ser demasiado relajado para las aplicaciones web orientadas al usuario donde se esperan respuestas casi instantáneas. Puede cambiar el límite configurando el long_query_time variable:

SET GLOBAL long_query_time=1;

El valor establece la duración mínima para consultas lentas. Es importante encontrar un equilibrio que se adapte a su propia aplicación. Un umbral demasiado alto excluirá las consultas que realmente están afectando el rendimiento. Por el contrario, valores muy bajos pueden hacer que se capturen demasiadas consultas, creando un registro excesivamente ruidoso.

Usando el archivo de configuración de MySQL

Debe habilitar el registro de consultas lentas en su archivo de configuración de MySQL si planea usarlo a largo plazo. Esto asegurará que el registro se reanude automáticamente después de que se reinicie el servidor MySQL.

La ubicación del archivo de configuración puede variar según la distribución de la plataforma. por lo general es en /etc/mysql/my.cnf o /etc/mysql/mysql.conf.d/mysqld.cnf. Agregue las siguientes líneas para replicar la configuración que se habilitó dinámicamente anteriormente:

slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=1

Reinicie MySQL para aplicar sus cambios:

$ sudo service mysql restart

El registro de consultas lentas ahora estará activo cada vez que se inicie el servidor MySQL.

Personalización del contenido del registro

El registro normalmente solo incluye consultas SQL que excluyen el umbral "lento" y que han sido enviadas por aplicaciones cliente. Esto excluye cualquier operación administrativa lenta que pueda ocurrir, como la creación de índices y la optimización de tablas, así como las consultas que tienen la potencial ser lento en el futuro.

Puede ampliar el registro para incluir esta información realizando los siguientes cambios en su archivo de configuración:

  • log_slow_admin_statements = 1 - Incluye instrucciones SQL administrativas como ALTER TABLE, CREATE INDEX, DROP INDEXy OPTIMIZE TABLE. Esto rara vez es deseable ya que estas operaciones generalmente se ejecutan durante los scripts de mantenimiento y migración. No obstante, esta configuración puede ser útil si su aplicación también realiza dinámicamente estas tareas.
  • log_slow_replica_statements = 1 - Esta configuración habilita el registro lento de consultas para consultas replicadas en servidores de réplica. Esto está deshabilitado por defecto. Usar log_slow_slave_statements en cambio, para las versiones de MySQL 8.0.26 y anteriores.
  • log_queries_not_using_indexes = 1 - Cuando esta configuración está habilitada, se registrarán las consultas que se espera que recuperen todos los registros de la tabla o vista de destino, incluso si no excluyeron el umbral de consulta lenta. Esto puede ayudar a identificar cuándo a una consulta le falta un índice o no puede usarlo. Las consultas que tienen un índice disponible aún se registrarán si carecen de restricciones que limiten la cantidad de filas recuperadas.

El registro de consultas que no usan índices puede aumentar significativamente la verbosidad. Puede haber situaciones en las que se espere o sea necesario un análisis de índice completo. Estas consultas seguirán apareciendo en el registro aunque no se puedan resolver.

Puede calificar consultas de límite sin índices configurando el log_throttle_queries_not_using_indexes variable. Esto define el número máximo de registros que se escribirán en un período de 60 segundos. un valor de 10 significa que se registrarán hasta 10 consultas por minuto. Después del décimo evento, no se registrarán más consultas hasta que se abra la siguiente ventana de 60 segundos.

Interpretación del registro de consultas lentas

Cada consulta que llegue al registro de consultas lentas mostrará un conjunto de líneas similares a las siguientes:

# Time: 2022-07-12T19:00:00.000000Z
# [email protected]: demo[demo] @ mysql [] Id: 51
# Query_time: 3.514223  Lock_time: 0.000010  Rows_sent: 5143  Rows_examined: 322216
SELECT * FROM slow_table LEFT JOIN another_table ...

Las líneas comentadas sobre la consulta contienen la hora a la que se ejecutó, el usuario de MySQL con el que se conectó el cliente y las estadísticas que proporcionan la duración y el número de filas enviadas. El ejemplo anterior tardó 3,5 segundos en completarse y analizó más de 320 000 filas, antes de enviar solo 5143 al cliente. Esto podría ser una indicación de que los índices faltantes están causando que MySQL inspeccione demasiados registros.

Opcionalmente, puede incluir más información en el registro configurando el log_slow_extra = 1 variable del sistema en su archivo de configuración. Esto agregará la identificación del hilo, la cantidad de bytes recibidos y enviados, y la cantidad de filas consideradas para ordenar, así como también los recuentos de solicitudes específicas de la declaración que brindan visibilidad sobre cómo MySQL manejó la consulta.

El archivo de registro debe tratarse con cuidado ya que su contenido será confidencial. Las consultas se muestran completas, sin enmascarar los valores de los parámetros. Esto significa que los datos del usuario estarán presentes si está utilizando el registro de consultas lentas en un servidor de producción. El acceso debe estar restringido a los desarrolladores y administradores de bases de datos que están ajustando las sentencias SQL.

Registro lento de consultas y copias de seguridad

Una frustración común con el registro de consultas lento surge cuando también usa MySQLDump para crear copias de seguridad de la base de datos. de larga duración SELECT * FROM ... se ejecutarán consultas para obtener los datos de sus tablas y alimentarlos en su copia de seguridad. Se incluirán en el registro de consultas lentas como cualquier otra instrucción SQL. Esto puede contaminar el registro si realiza copias de seguridad con regularidad.

Puede solucionar esto deshabilitando temporalmente el registro de consultas lentas antes de ejecutar mysqldump. Puede reactivar el registro después de que se complete la copia de seguridad. Ajuste su secuencia de comandos de respaldo para que se vea similar a lo siguiente:

#!/bin/bash

# Temporarily disable slow query logging
mysql -uUser -pPassword -e "SET GLOBAL slow_query_log=0";

# Run mysqldump
mysqldump -uUser -pPassword --single-transaction databaseName | gzip > backup.bak

# Enable the slow query log again
mysql -uUser -pPassword -e "SET GLOBAL slow_query_log=1"

Esto mantendrá la actividad de MySQLDump fuera del registro de consultas lentas, lo que facilitará el enfoque en el SQL que ejecuta su aplicación.

Resumen

El registro de consultas lentas de MySQL es una de las formas más efectivas de identificar la causa de los problemas de rendimiento. Comience por estimar el retraso que está experimentando y use este valor como suyo long_query_time. Reduzca el valor si no aparece nada en el registro después de haber reproducido el problema.

El registro de consultas lentas no le dirá exactamente cómo solucionar la ralentización. Sin embargo, la capacidad de ver el SQL exacto recibido por el servidor le permite repetir declaraciones de bajo rendimiento y luego medir el efecto de las optimizaciones. Agregar un índice o una restricción faltante puede ser la diferencia entre una consulta que toca miles de filas y una que funciona con un puñado.

Descubre más contenido

Subir Change privacy settings