Cómo detectar consultas lentas en producción con el Slow Query Log de MySQL

El panel de pedidos tarda varios segundos, pero desde PHP solo ves que la solicitud completa fue lenta. El Slow Query Log de MySQL permite capturar las sentencias que superan un umbral, agrupar las que se repiten y elegir cuál investigar primero. Esta guía usa MySQL 8 y una captura temporal en producción.

1. Revisa la configuración antes de cambiarla

SELECT VERSION();

SHOW GLOBAL VARIABLES
WHERE Variable_name IN (
  'slow_query_log', 'slow_query_log_file', 'log_output',
  'long_query_time', 'min_examined_row_limit',
  'log_queries_not_using_indexes', 'datadir'
);

Guarda los valores originales y comprueba el espacio disponible en el volumen donde se escribe el registro. log_output debe incluir FILE para usar mysqldumpslow. Con TABLE, la salida va a mysql.slow_log, y con NONE no se escribe. El destino también afecta al registro general, así que coordina cualquier cambio si el servidor ya lo utiliza. Si MySQL está en Docker, la ruta que devuelve el servidor corresponde al sistema de archivos del contenedor.

2. Activa una captura temporal

Con un usuario autorizado, y si log_output ya incluye FILE, habilita el registro. Usa un segundo como punto de partida y ajusta el umbral según el tráfico y la latencia que quieras investigar.

SET GLOBAL long_query_time = 1.0;
SET GLOBAL slow_query_log = ON;

long_query_time se expresa en segundos. El cambio global del umbral se aplica a conexiones nuevas. Las conexiones abiertas conservan su valor de sesión. Si PHP usa conexiones persistentes o hay un pool, verifica el umbral desde una conexión de la aplicación y coordina su renovación si hace falta.

SELECT @@GLOBAL.long_query_time AS umbral_global,
       @@SESSION.long_query_time AS umbral_sesion,
       @@SESSION.min_examined_row_limit AS minimo_filas;

El mínimo de filas examinadas también influye en qué sentencias aparecen. Durante una primera captura por duración, revisa si log_queries_not_using_indexes ya está activado, porque puede incluir consultas por su patrón de acceso aunque sean rápidas. Observa el tamaño del archivo durante la ventana y limita el acceso al registro, ya que el SQL puede contener datos de clientes.

3. Prioriza las consultas por su impacto acumulado

Trabaja sobre un archivo que cubra la ventana del incidente o sobre una copia delimitada a ese intervalo. Sustituye la ruta del ejemplo por la ruta real de la captura:

# Diez patrones con mayor tiempo acumulado
mysqldumpslow -l -s t -t 10 /ruta/captura-slow.log

# Diez patrones más frecuentes
mysqldumpslow -l -s c -t 10 /ruta/captura-slow.log

# Diez patrones con mayor tiempo medio
mysqldumpslow -l -s at -t 10 /ruta/captura-slow.log

La herramienta agrupa sentencias con la misma estructura y sustituye muchos valores literales por marcadores. La opción -l evita que reste el tiempo de bloqueo al calcular el tiempo mostrado. En un ejemplo ilustrativo, una consulta registrada 500 veces con una media de 1,2 segundos suma unos 600 segundos de ejecución acumulada. Una consulta aislada de 12 segundos puede ser más visible para un usuario, pero la primera merece atención si aparece constantemente en la pantalla de pedidos. El tiempo acumulado suma ejecuciones y puede incluir consultas simultáneas.

4. Lee una entrada y analiza el plan

# Query_time: 1.234567  Lock_time: 0.000321
# Rows_sent: 20  Rows_examined: 85000
SELECT id, fecha_creacion, total
FROM pedidos
WHERE cliente_id = 42
ORDER BY fecha_creacion DESC, id DESC
LIMIT 20;

En esta entrada ficticia, la consulta examina 85 000 filas para devolver 20. Es una señal para revisar el filtro, el orden y los índices. Lock_time aporta contexto sobre los bloqueos registrados. Si el retraso aparece en PHP y el registro no lo refleja, compara la medición del servidor con la de la aplicación. La espera para adquirir los bloqueos iniciales no cuenta como tiempo de ejecución a efectos del registro.

En una copia con datos representativos, ejecuta EXPLAIN con parámetros similares a los del incidente. Usa EXPLAIN ANALYZE para medir el plan de ese SELECT cuando sea apropiado, porque ejecuta la sentencia. Después del cambio, compara ventanas de tráfico similares con el mismo umbral. Revisa también la latencia en la aplicación, ya que las consultas que mejoren por debajo del umbral dejarán de aparecer en el registro.

5. Cierra la captura y restaura los valores originales

Al terminar, restaura los parámetros que cambiaste. Por ejemplo, si el registro estaba apagado y el umbral original era de diez segundos:

SET GLOBAL slow_query_log = OFF;
SET GLOBAL long_query_time = 10;

Si el registro ya estaba encendido, déjalo activo con su configuración anterior. Recuerda que las conexiones abiertas durante la captura pueden conservar el umbral de sesión hasta que se renueven. Si mantienes el registro habilitado, configura rotación y retención. Trata los archivos como datos de producción y comparte únicamente extractos redactados.

Documentación y lectura relacionada

• Manual de MySQL sobre el Slow Query Log
• Cómo resumir el registro con mysqldumpslow
• Cómo acelerar consultas lentas en MySQL con índices y EXPLAIN