Las consultas lentas son las asesinas silenciosas del rendimiento de bases de datos. Una sola consulta sin optimizar ejecutándose miles de veces al día puede colapsar un servidor MySQL que de otro modo funcionaría bien. El slow query log de MySQL es tu primera línea de defensa — captura cada consulta que excede un umbral de tiempo, proporcionándote los datos necesarios para identificar y eliminar cuellos de botella. Combinado con herramientas de análisis como mysqldumpslow y pt-query-digest, puedes encontrar, priorizar y corregir sistemáticamente las consultas más problemáticas de tu carga de trabajo.
Requisitos Previos
- MySQL 5.7+ o MySQL 8.0 (los comandos funcionan en ambos; se indican diferencias menores)
- Acceso root o administrativo al servidor MySQL
- Acceso por shell al servidor que ejecuta MySQL
- Percona Toolkit instalado (para
pt-query-digest) - Conocimiento básico de SQL y archivos de configuración de MySQL
Activar el Slow Query Log
El slow query log está desactivado por defecto en la mayoría de instalaciones de MySQL. Puedes activarlo permanentemente mediante configuración o temporalmente en tiempo de ejecución.
Configuración Permanente (my.cnf)
Agrega estas líneas a tu archivo de configuración de MySQL, normalmente en /etc/mysql/my.cnf o /etc/my.cnf:
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_output = FILEReinicia MySQL para aplicar:
sudo systemctl restart mysqlConfiguración en Tiempo de Ejecución (Sin Reinicio)
Activa el slow query log sin reiniciar el servidor:
SET GLOBAL slow_query_log = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_output = 'FILE';Verifica la configuración:
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';Salida esperada:
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/log/mysql/slow.log |
+---------------------+-------------------------------+Opciones de log_output
MySQL soporta tres destinos de salida:
- FILE — escribe en el archivo especificado por
slow_query_log_file(predeterminado, recomendado) - TABLE — escribe en la tabla
mysql.slow_log(consultable pero agrega sobrecarga) - NONE — desactiva el registro a pesar de que
slow_query_log=1
Usa FILE para producción. La opción TABLE agrega sobrecarga de escritura medible y hace que el esquema mysql crezca de forma impredecible.
Configurar Parámetros del Log
long_query_time
Este umbral determina qué consultas se registran. El valor predeterminado de 10 segundos es demasiado alto para la mayoría de las cargas de trabajo.
-- Registrar consultas que tarden más de 0.5 segundos
SET GLOBAL long_query_time = 0.5;
-- Registrar TODAS las consultas (útil para auditorías cortas, no para producción)
SET GLOBAL long_query_time = 0;Valores iniciales recomendados:
| Entorno | long_query_time | Justificación |
|---|---|---|
| Desarrollo | 0 | Captura todo para análisis |
| Staging | 0.1 | Detecta consultas >100ms |
| Producción | 1.0 | Enfoque en consultas claramente lentas |
| Agresivo | 0.5 | Equilibrio entre ruido y cobertura |
log_queries_not_using_indexes
Este parámetro registra consultas que realizan escaneos completos de tabla, sin importar el tiempo de ejecución:
SET GLOBAL log_queries_not_using_indexes = 1;Es extremadamente valioso para detectar consultas que son rápidas ahora pero que se degradarán conforme las tablas crezcan. Sin embargo, puede generar muchas entradas de log en bases de datos con muchas tablas pequeñas.
min_examined_row_limit
Filtra consultas triviales requiriendo un número mínimo de filas examinadas:
SET GLOBAL min_examined_row_limit = 1000;Esto evita que el log se llene con búsquedas rápidas de una sola fila que resultan no usar un índice.
Análisis con mysqldumpslow
mysqldumpslow viene incluido con MySQL y no requiere instalación adicional. Parsea el slow query log, abstrae valores literales y agrupa consultas similares.
Uso Básico
# Top 10 consultas por tiempo total
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# Top 10 consultas por frecuencia
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
# Top 10 consultas por tiempo promedio
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log
# Filtrar consultas que coincidan con un patrón
mysqldumpslow -s t -t 10 -g "orders" /var/log/mysql/slow.logOpciones de Ordenamiento
| Flag | Ordena por |
|---|---|
-s t | Tiempo total |
-s c | Frecuencia (conteo) |
-s at | Tiempo promedio |
-s l | Tiempo de bloqueo |
-s al | Tiempo promedio de bloqueo |
-s r | Filas examinadas |
-s ar | Promedio de filas examinadas |
Ejemplo de Salida
Count: 1523 Time=2.45s (3731s) Lock=0.00s (1s) Rows=245.3 (373534), root[root]@localhost
SELECT * FROM orders WHERE customer_id = N AND status = 'S' ORDER BY created_at DESC LIMIT N;Esto indica que la consulta se ejecutó 1,523 veces, con un promedio de 2.45 segundos cada una, examinando 245 filas por ejecución y consumiendo 3,731 segundos en total.
Limitaciones
mysqldumpslow es útil para revisiones rápidas pero tiene limitaciones significativas:
- Sin análisis de percentiles (P95, P99)
- Sin distribución de tiempos de respuesta
- No puede leer logs en formato binario o TABLE
- Opciones limitadas de filtrado y reportes
Análisis Profundo con pt-query-digest
pt-query-digest del Percona Toolkit es el estándar de la industria para análisis de slow query logs. Proporciona información mucho más detallada que mysqldumpslow.
Instalación
# Debian/Ubuntu
sudo apt-get install percona-toolkit
# RHEL/CentOS
sudo yum install percona-toolkit
# Desde código fuente
wget https://www.percona.com/downloads/percona-toolkit/LATEST/tarball/percona-toolkit-LATEST.tar.gz
tar xzf percona-toolkit-LATEST.tar.gz
cd percona-toolkit-* && perl Makefile.PL && make && sudo make installUso Básico
# Reporte completo
pt-query-digest /var/log/mysql/slow.log
# Reporte de las últimas 24 horas
pt-query-digest --since '24h' /var/log/mysql/slow.log
# Filtrar por base de datos
pt-query-digest --filter '$event->{db} eq "myapp"' /var/log/mysql/slow.log
# Salida a archivo
pt-query-digest /var/log/mysql/slow.log > /tmp/slow-report.txtEstructura del Reporte
La salida tiene tres secciones:
1. Resumen General
# 2.1M QPS, 1.2x concurrency, 45% of time in query
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 3731s 100ms 45s 2.45s 4.12s 1.33s 1.85s
# Lock time 1s 0 15ms 10us 22us 38us 8us
# Rows sent 373534 0 1000 245 480.0 180.3 198.0
# Rows examine 2.1M 0 50000 1382 3200 2100 8502. Perfil (Ranking de Consultas)
# Rank Query ID Response time Calls R/Call Item
# ==== ================================ ============== ====== ======= ====
# 1 0xE77769C62EF669AA1A6... 1800.0 48.2% 1523 1.1819 SELECT orders
# 2 0xA4B9D8C1F3E5A7B2C9... 950.3 25.5% 892 1.0654 SELECT products
# 3 0xF1C2D3E4A5B6C7D8E9... 480.1 12.9% 3201 0.1500 SELECT users3. Detalle por Consulta — para cada consulta clasificada, obtienes la huella completa, distribución de tiempos, recomendaciones EXPLAIN y una consulta de ejemplo con valores literales.
mysqldumpslow vs pt-query-digest vs MySQL Enterprise Monitor vs PMM
| Característica | mysqldumpslow | pt-query-digest | MySQL Enterprise Monitor | Percona PMM |
|---|---|---|---|---|
| Costo | Gratis (incluido) | Gratis (código abierto) | Licencia comercial | Gratis (código abierto) |
| Instalación | Ninguna | Percona Toolkit | Servidor dedicado | Docker/VM |
| Monitoreo en tiempo real | No | No | Sí | Sí |
| Análisis histórico | Básico | Excelente | Excelente | Excelente |
| Fingerprinting | Básico | Avanzado | Avanzado | Avanzado |
| Análisis de percentiles | No | Sí (P95, P99) | Sí | Sí |
| Dashboard visual | No | No | Sí | Sí (Grafana) |
| Integración EXPLAIN | No | Parcial | Sí | Sí |
| Alertas | No | No | Sí | Sí |
| Ideal para | Revisiones rápidas | Análisis profundo | Equipos enterprise | Equipos que quieren UI gratis |
Optimización de Consultas Lentas
Una vez que has identificado las consultas más lentas, sigue este enfoque sistemático.
Paso 1: EXPLAIN de la Consulta
EXPLAIN SELECT * FROM orders
WHERE customer_id = 42 AND status = 'pending'
ORDER BY created_at DESC LIMIT 10;Columnas clave a examinar:
- type: Debe ser
ref,rangeoconst. EvitaALL(escaneo completo de tabla) - key: Qué índice eligió MySQL.
NULLsignifica que no se usó índice - rows: Filas estimadas examinadas. Números altos indican índices faltantes
- Extra: Vigila
Using filesortyUsing temporary
En MySQL 8.0, usa EXPLAIN ANALYZE para estadísticas reales de ejecución:
EXPLAIN ANALYZE SELECT * FROM orders
WHERE customer_id = 42 AND status = 'pending'
ORDER BY created_at DESC LIMIT 10;Paso 2: Agregar Índices Faltantes
Basándote en la salida de EXPLAIN, crea índices compuestos que cubran las cláusulas WHERE y ORDER BY:
-- Cubre WHERE customer_id AND status, ORDER BY created_at
CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);Reglas de diseño de índices:
- Columnas de igualdad primero (
customer_id,status) - Columnas de rango/ordenamiento al final (
created_at) - Incluye columnas del SELECT si creas un índice de cobertura
- Evita sobre-indexar — cada índice ralentiza las escrituras
Paso 3: Reescribir Patrones Problemáticos
Reescrituras comunes que mejoran el rendimiento:
-- MAL: Subconsulta ejecutada por cada fila
SELECT * FROM orders WHERE customer_id IN (
SELECT id FROM customers WHERE region = 'US'
);
-- BIEN: JOIN se ejecuta una vez
SELECT o.* FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE c.region = 'US';
-- MAL: SELECT * trae columnas no utilizadas
SELECT * FROM orders WHERE id = 42;
-- BIEN: Selecciona solo columnas necesarias
SELECT id, status, total, created_at FROM orders WHERE id = 42;
-- MAL: Función en columna indexada impide uso del índice
SELECT * FROM orders WHERE YEAR(created_at) = 2025;
-- BIEN: Escaneo por rango usa el índice
SELECT * FROM orders
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';Escenario del Mundo Real
Administras la base de datos de una plataforma de comercio electrónico. Los usuarios reportan que las páginas de productos cargan lentamente durante horas pico (2-6 PM), pero el resto del sitio funciona bien. Las métricas del servidor muestran picos de CPU que coinciden con las quejas, pero la memoria y el I/O de disco son normales.
Paso 1: Activa el slow query log con long_query_time=0.5 durante las horas pico.
Paso 2: Después de 2 horas de registro, ejecuta pt-query-digest:
pt-query-digest --since '2h' /var/log/mysql/slow.logPaso 3: El reporte revela que la consulta principal consume el 62% del tiempo total:
SELECT p.*, c.name as category_name,
(SELECT AVG(rating) FROM reviews WHERE product_id = p.id) AS avg_rating,
(SELECT COUNT(*) FROM reviews WHERE product_id = p.id) AS review_count
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.category_id = 15 AND p.active = 1
ORDER BY created_at DESC LIMIT 20;Paso 4: EXPLAIN muestra que las subconsultas correlacionadas se ejecutan una vez por fila de producto (2,400 veces por carga de página). Corrige reescribiendo con un JOIN:
SELECT p.*, c.name as category_name,
COALESCE(r.avg_rating, 0) AS avg_rating,
COALESCE(r.review_count, 0) AS review_count
FROM products p
JOIN categories c ON p.category_id = c.id
LEFT JOIN (
SELECT product_id, AVG(rating) as avg_rating, COUNT(*) as review_count
FROM reviews GROUP BY product_id
) r ON r.product_id = p.id
WHERE p.category_id = 15 AND p.active = 1
ORDER BY p.created_at DESC LIMIT 20;Paso 5: Agrega un índice compuesto:
CREATE INDEX idx_products_category_active_created
ON products (category_id, active, created_at DESC);Resultado: El tiempo de consulta baja de 2.4 segundos a 12 milisegundos. La carga de páginas vuelve a la normalidad.
Precauciones y Casos Especiales
- La rotación de logs es esencial. Sin rotación, el slow log puede consumir todo el espacio disponible en disco. Usa
logrotateo el comandoFLUSH SLOW LOGSde MySQL con un cron job. - Monitoreo de espacio en disco. Configurar
long_query_time=0en un servidor ocupado puede generar gigabytes de datos de log por hora. Siempre monitorea el uso de disco al reducir el umbral. - Retraso de replicación por logging. En réplicas con
log_slow_replica_statements=1(MySQL 8.0.26+), registrar consultas replicadas puede aumentar el retraso. MonitoreaSeconds_Behind_Source. - Inundación por log_queries_not_using_indexes. Tablas pequeñas de búsqueda (países, estados) activarán este flag aunque los escaneos completos en tablas de 50 filas sean óptimos. Usa
min_examined_row_limitpara filtrarlas. - Sentencias preparadas. Por defecto, las sentencias preparadas no se registran. Configura
log_slow_extra=1(MySQL 8.0.14+) para capturar estadísticas adicionales. - Los cambios en tiempo de ejecución no persisten. Los cambios con SET GLOBAL se pierden al reiniciar. Siempre actualiza
my.cnfademás de los comandos en tiempo de ejecución.
Resolución de Problemas
El archivo del slow query log no se crea: Verifica que MySQL tenga permisos de escritura en el directorio de logs:
sudo mkdir -p /var/log/mysql
sudo chown mysql:mysql /var/log/mysql
sudo chmod 750 /var/log/mysqlEl log muestra cero consultas a pesar de tener slow_query_log=ON:
Verifica que el long_query_time no esté configurado demasiado alto:
SELECT @@global.long_query_time;Si muestra 10.000000, las consultas deben tardar más de 10 segundos para registrarse. Redúcelo a 1.
pt-query-digest reporta “No events processed”:
El archivo de log puede estar vacío o el formato no es reconocido. Asegúrate de que log_output=FILE (no TABLE). Verifica los permisos:
ls -la /var/log/mysql/slow.log
file /var/log/mysql/slow.logmysqldumpslow muestra “Permission denied”:
La herramienta necesita acceso de lectura al archivo de log. Ejecuta con sudo o agrega tu usuario al grupo mysql:
sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow.logLas consultas aparecen en el slow log pero EXPLAIN muestra ejecución rápida:
Esto generalmente significa contención de bloqueos. Revisa el campo Lock_time en la entrada del log. Tiempos de bloqueo altos indican problemas de bloqueo a nivel de tabla o fila, no problemas de optimización de consultas.
Resumen
- Activa el slow query log con
slow_query_log=1y configuralong_query_timea 1 segundo o menos - Usa
log_queries_not_using_indexespara detectar consultas que se degradarán conforme los datos crezcan - Comienza con
mysqldumpslowpara análisis rápido; cambia apt-query-digestpara análisis de nivel producción - Enfócate en el tiempo total (frecuencia × tiempo promedio) en lugar de solo la ejecución más lenta
- Usa
EXPLAINyEXPLAIN ANALYZE(MySQL 8.0) para entender los planes de ejecución antes de crear índices - Diseña índices compuestos con columnas de igualdad primero y columnas de rango/ordenamiento al final
- Reescribe subconsultas correlacionadas como JOINs para mejoras de un orden de magnitud
- Siempre implementa rotación de logs y monitorea el espacio en disco al usar el slow query log en producción