Slow Query Logs

Las consultas lentas son uno de los problemas de rendimiento más comunes en bases de datos MySQL. El slow query log registra automáticamente las consultas que tardan más de un umbral configurable, proporcionándote un punto de partida claro para la optimización. En lugar de adivinar qué consultas causan problemas, dejas que MySQL te diga exactamente cuáles necesitan atención.

Activar el slow query log

Verifica primero si el slow query log está habilitado:

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW VARIABLES LIKE 'long_query_time';

Por defecto, el slow query log está desactivado y el umbral es de 10 segundos, lo que es demasiado alto para la mayoría de las aplicaciones. Puedes activarlo y ajustar el umbral sin reiniciar el servidor:

-- Activar el slow query log
SET GLOBAL slow_query_log = 'ON';
 
-- Establecer el umbral a 1 segundo
SET GLOBAL long_query_time = 1;

Para que la configuración persista después de reiniciar MySQL, añádela al archivo de configuración:

# /etc/mysql/my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1

Configurar el umbral

La variable long_query_time acepta valores decimales, lo que permite umbrales menores a un segundo:

-- Registrar consultas que tardan más de 500 milisegundos
SET GLOBAL long_query_time = 0.5;
 
-- Registrar consultas que tardan más de 100 milisegundos
SET GLOBAL long_query_time = 0.1;

Un umbral de 1 segundo es un buen punto de partida. Si después de optimizar las consultas más lentas quieres ir más allá, reduce el umbral gradualmente. Un umbral muy bajo (como 0.01) puede generar un archivo de log enorme en servidores con mucho tráfico.

Registrar consultas sin índice

La opción log_queries_not_using_indexes registra consultas que no usan ningún índice, incluso si son rápidas. Esto es muy útil para identificar problemas antes de que crezcan los datos:

SET GLOBAL log_queries_not_using_indexes = 'ON';

Una consulta que hace un full scan en una tabla con 100 filas es rápida. Pero cuando esa tabla crezca a 10 millones de filas, la misma consulta será un cuello de botella. Activar esta opción te permite detectar esos problemas de forma temprana.

Para evitar que esta opción llene el log con demasiadas entradas, limita la tasa de registro:

-- Limitar a 60 consultas sin índice por minuto en el log
SET GLOBAL log_throttle_queries_not_using_indexes = 60;

Formato del slow query log

Cada entrada del slow query log contiene información valiosa sobre la consulta:

# Time: 2026-02-14T15:23:45.123456Z
# User@Host: app_user[app_user] @ webserver [192.168.1.50]  Id: 12345
# Query_time: 3.456789  Lock_time: 0.000123  Rows_sent: 1  Rows_examined: 850000
SET timestamp=1739542625;
SELECT p.nombre, p.precio, c.nombre AS categoria
FROM productos p
JOIN categorias c ON p.categoria_id = c.id
WHERE p.descripcion LIKE '%smartphone%'
ORDER BY p.precio DESC;

La línea Query_time muestra el tiempo total de ejecución. Lock_time indica cuánto tiempo estuvo la consulta esperando por un bloqueo. Rows_sent es el número de filas devueltas al cliente. Rows_examined es el número de filas que MySQL tuvo que examinar internamente. Una gran diferencia entre Rows_examined y Rows_sent indica una consulta ineficiente que lee mucho más de lo que necesita.

Analizar con mysqldumpslow

La herramienta mysqldumpslow viene incluida con MySQL y resume el contenido del slow query log agrupando consultas similares:

# Las 10 consultas más lentas por tiempo total
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

Count: 245  Time=3.45s (845s)  Lock=0.00s (0s)  Rows=1.0 (245), app_user[app_user]@webserver
  SELECT p.nombre, p.precio, c.nombre AS categoria
  FROM productos p JOIN categorias c ON p.categoria_id = c.id
  WHERE p.descripcion LIKE 'S'
  ORDER BY p.precio DESC

Count: 1230  Time=1.23s (1512s)  Lock=0.00s (0s)  Rows=50.0 (61500), app_user[app_user]@webserver
  SELECT * FROM pedidos WHERE cliente_id = N ORDER BY fecha_pedido DESC LIMIT N, N

Las opciones principales de mysqldumpslow son:

# Ordenar por tiempo medio de ejecución
mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log
 
# Ordenar por número de ocurrencias
mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log
 
# Ordenar por filas examinadas
mysqldumpslow -s ar -t 10 /var/log/mysql/mysql-slow.log

Los parámetros de ordenación son: t (tiempo total), at (tiempo medio), c (conteo), l (tiempo de bloqueo), al (tiempo medio de bloqueo), r (filas enviadas), ar (filas medias enviadas).

Percona pt-query-digest

Para un análisis más profundo, pt-query-digest de Percona Toolkit ofrece estadísticas mucho más detalladas:

# Instalar Percona Toolkit
sudo apt install percona-toolkit
 
# Analizar el slow query log
pt-query-digest /var/log/mysql/mysql-slow.log

La salida incluye un perfil con las consultas ordenadas por impacto total, estadísticas de distribución (percentiles p50, p95, p99) y una huella digital de cada consulta que agrupa variaciones de la misma consulta.

# Profile
# Rank Query ID                       Response time  Calls  R/Call
# ==== ============================== ============== ====== ======
#    1 0xABC123DEF456...              1512.3000 45.2%  1230  1.2295
#    2 0x789GHI012JKL...               845.0000 25.3%   245  3.4490
#    3 0xMNO345PQR678...               423.5000 12.7%    89  4.7584

Estrategia de optimización

Una vez que identificas las consultas lentas, la estrategia de optimización sigue un patrón claro.

Primero, usa EXPLAIN para entender el plan de ejecución de las consultas más problemáticas:

EXPLAIN SELECT p.nombre, p.precio, c.nombre AS categoria
FROM productos p
JOIN categorias c ON p.categoria_id = c.id
WHERE p.descripcion LIKE '%smartphone%'
ORDER BY p.precio DESC;

El ALL en la tabla p indica un full scan. El Using filesort indica un ordenamiento en memoria. Ambos son señales de ineficiencia.

La solución podría incluir crear un índice fulltext para la búsqueda, o reestructurar la consulta para evitar LIKE '%...'.

Gestión del archivo de log

El archivo del slow query log puede crecer significativamente en servidores con mucho tráfico. Configura la rotación con logrotate:

# /etc/logrotate.d/mysql-slow
/var/log/mysql/mysql-slow.log {
    daily
    rotate 7
    compress
    delaycompress
    missingok
    notifempty
    create 640 mysql mysql
    postrotate
        mysqladmin flush-logs
    endscript
}

También puedes rotar el log manualmente:

-- Vaciar y reiniciar el slow query log
SET GLOBAL slow_query_log = 'OFF';
-- Renombrar o mover el archivo desde el sistema operativo
SET GLOBAL slow_query_log = 'ON';

Activar temporalmente para diagnóstico

Si no quieres tener el slow query log activo permanentemente, puedes activarlo solo durante periodos de diagnóstico:

-- Activar durante una hora de tráfico alto
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
 
-- ... esperar el periodo de diagnóstico ...
 
-- Desactivar cuando termines
SET GLOBAL slow_query_log = 'OFF';

Esta técnica es útil para investigar problemas reportados sin el overhead continuo del logging.

Variables complementarias útiles

-- Ver estadísticas de consultas lentas
SHOW GLOBAL STATUS LIKE 'Slow_queries';

Este contador muestra el total acumulado de consultas lentas desde el último reinicio del servidor. Puedes monitorearlo para detectar tendencias.

-- Consultas que esperaron por bloqueos de tabla
SHOW GLOBAL STATUS LIKE 'Table_locks_waited';

El slow query log es una de las herramientas de diagnóstico más valiosas en MySQL. Activarlo, analizarlo regularmente y actuar sobre las consultas más problemáticas es una de las formas más efectivas de mejorar el rendimiento general de tu base de datos.