Cómo habilitar el registro de consultas lentas en MySQL y MariaDB

El registro de consultas lentas es una función de diagnóstico integrada en MySQL y MariaDB que registra cada sentencia SQL cuyo tiempo de ejecución supera un umbral configurable. Captura la duración de la consulta, el tiempo de bloqueo, las filas examinadas, las filas enviadas y el texto SQL completo, proporcionando a los administradores de bases de datos y desarrolladores un registro de auditoría preciso, basado en archivos, de cada consulta que está degradando el rendimiento de la aplicación.

Habilitarlo es una de las acciones de mayor impacto que puede tomar durante el ajuste del rendimiento de la base de datos. A diferencia de las herramientas de monitoreo genéricas, el registro de consultas lentas identifica con precisión las sentencias exactas responsables de la latencia, lo que lo hace indispensable para la optimización de índices, la reestructuración de consultas y la planificación de capacidad en cualquier servidor, desde un entorno de VPS Hosting de un solo inquilino hasta un clúster de base de datos dedicado de múltiples nodos.

Por qué el registro de consultas lentas importa más allá del monitoreo básico

La mayoría de los equipos recurren a EXPLAIN o SHOW PROCESSLIST de forma reactiva, después de que los usuarios reportan lentitud. El registro de consultas lentas funciona de forma proactiva: acumula evidencia durante horas o días de tráfico real, capturando infractores intermitentes que nunca aparecen durante una ventana de inspección manual.

Los principales beneficios operativos incluyen:

• Aislamiento de cuellos de botella — distingue los escaneos completos de tablas vinculados a CPU de los problemas de contención de bloqueos usando las relaciones Query_time vs. Lock_time
• Análisis de brechas de índices — el indicador log_queries_not_using_indexes muestra cada consulta que realiza un escaneo completo, independientemente de su tiempo de ejecución bruto
• Detección de regresiones — comparar instantáneas del registro antes y después de un despliegue revela si el nuevo código introdujo patrones de consulta más lentos
• Evidencia para la planificación de capacidad — los valores de Rows_examined que son órdenes de magnitud superiores a Rows_sent indican índices faltantes o mal utilizados, que se agravan bajo carga

MySQL vs. MariaDB: Comparación de características del registro de consultas lentas

Ambos motores comparten la misma infraestructura central de registro de consultas lentas heredada de MySQL 5.1, pero MariaDB la ha extendido de varias maneras significativas.

Las opciones log_slow_verbosity y log_slow_rate_limit en MariaDB son particularmente valiosas en entornos de producción de alto rendimiento donde registrar cada consulta lenta podría convertirse en sí mismo en una carga para el rendimiento.

Paso 1: Localizar el archivo de configuración

MySQL y MariaDB leen su configuración desde diferentes rutas predeterminadas según la distribución y el método de instalación.

MySQL:

/etc/my.cnf (basado en RPM: RHEL, CentOS, AlmaLinux, Rocky Linux)
/etc/mysql/my.cnf (Debian/Ubuntu)
/etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu con paquete mysql-server)

MariaDB:

/etc/my.cnf.d/server.cnf (basado en RPM)
/etc/mysql/mariadb.conf.d/50-server.cnf (Debian/Ubuntu)
/etc/mysql/mariadb.cnf (distribuciones Debian más antiguas)

Si no está seguro de qué archivo está activo, consulte el proceso en ejecución:
mysqld --verbose --help 2>/dev/null | grep -A1 "Default options"
Esto imprime la lista ordenada exacta de archivos que el daemon lee al inicio, incluyendo cualquier directorio !includedir.
Abra el archivo de configuración principal con su editor preferido:
sudo nano /etc/my.cnf
Paso 2: Agregar directivas del registro de consultas lentas a [mysqld]
Todos los parámetros del registro de consultas lentas pertenecen a la sección [mysqld]. Si la sección no existe, créela al inicio del archivo.
[mysqld]
# Core slow query log settings
slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/slow-query.log
long_query_time         = 1

# Log queries that skip index usage entirely
log_queries_not_using_indexes = 1

# Avoid flooding the log with index warnings on low-traffic tables
min_examined_row_limit  = 100

# Log slow administrative statements (ALTER TABLE, OPTIMIZE TABLE, etc.)
log_slow_admin_statements = 1
Descripción de los parámetros:

slow_query_log = 1 — activa la función; establezca en 0 para deshabilitar sin eliminar el bloque
slow_query_log_file — ruta absoluta al archivo de registro; el usuario del proceso MySQL/MariaDB (mysql) debe tener acceso de escritura al directorio padre
long_query_time = 1 — umbral en segundos, acepta valores decimales (p. ej., 0.5 para 500 ms); el valor predeterminado de 10 segundos casi siempre es demasiado permisivo para aplicaciones web
log_queries_not_using_indexes — registra consultas de escaneo completo independientemente de long_query_time; combinar con min_examined_row_limit para suprimir el ruido de tablas pequeñas
min_examined_row_limit — una consulta debe examinar al menos este número de filas antes de calificar para el registro bajo log_queries_not_using_indexes; evita que búsquedas triviales de una sola fila contaminen el registro
log_slow_admin_statements — captura operaciones a nivel de esquema que bloquean tablas y que frecuentemente se pasan por alto como fuentes de latencia

Adiciones específicas de MariaDB que vale la pena habilitar en producción:
# MariaDB only — extended per-query statistics in the log
log_slow_verbosity      = query_plan,explain

# MariaDB only — log only 1 in every N qualifying queries (rate limiting)
log_slow_rate_limit     = 10
log_slow_verbosity = query_plan,explain agrega el plan de ejecución del optimizador directamente en cada entrada del registro, eliminando la necesidad de volver a ejecutar EXPLAIN manualmente después del hecho — un ahorro de tiempo significativo al diagnosticar consultas que solo aparecen bajo patrones de carga de producción.
Paso 3: Crear el archivo de registro y establecer permisos
Si el directorio de destino no existe, créelo y asigne la propiedad antes de reiniciar el servicio. Omitir este paso es una de las razones más comunes por las que el registro de consultas lentas falla silenciosamente al activarse.
sudo mkdir -p /var/log/mysql
sudo touch /var/log/mysql/slow-query.log
sudo chown mysql:mysql /var/log/mysql/slow-query.log
sudo chmod 640 /var/log/mysql/slow-query.log
En sistemas con SELinux habilitado (RHEL, CentOS, AlmaLinux), el contexto del archivo también debe configurarse correctamente:
sudo semanage fcontext -a -t mysqld_log_t "/var/log/mysql(/.*)?"
sudo restorecon -Rv /var/log/mysql
No establecer el contexto SELinux correcto hace que el daemon se inicie correctamente pero omita silenciosamente la escritura en el archivo de registro — un caso límite frustrante que no produce ningún error obvio en /var/log/messages.
Paso 4: Reiniciar el servicio de base de datos
Aplique los cambios de configuración reiniciando el servicio. En distribuciones basadas en systemd (el estándar en cualquier servidor Linux moderno):
# MySQL
sudo systemctl restart mysqld

# MariaDB
sudo systemctl restart mariadb
En sistemas más antiguos basados en init.d:
# MySQL
sudo service mysqld restart

# MariaDB
sudo service mariadb restart
Después del reinicio, verifique que el servicio se haya iniciado correctamente:
sudo systemctl status mysqld    # or mariadb
sudo journalctl -u mysqld -n 50 --no-pager
Cualquier error de configuración en my.cnf impedirá el inicio y aparecerá en la salida del journal.
Paso 5: Habilitar el registro de consultas lentas en tiempo de ejecución (sin reinicio)
Para servidores de producción donde un reinicio es disruptivo, MySQL y MariaDB admiten habilitar el registro de consultas lentas de forma dinámica mediante SET GLOBAL. Los cambios realizados de esta manera surten efecto inmediatamente, pero no persisten tras un reinicio del servicio a menos que también se escriban en my.cnf.
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 1;
SET GLOBAL min_examined_row_limit = 100;
Este es el enfoque correcto para diagnósticos de emergencia en un sistema en vivo — habilitarlo, capturar una muestra de 15 a 30 minutos durante el tráfico pico, luego deshabilitarlo nuevamente sin tocar el archivo de configuración ni reiniciar el daemon.
Paso 6: Verificar la configuración
Conéctese al cliente MySQL o MariaDB:
mysql -u root -p
Luego ejecute una búsqueda de patrón en la tabla de variables del sistema:
SHOW VARIABLES LIKE '%slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
Salida esperada para una instancia correctamente configurada:
+-------------------------------+-------------------------------+
| Variable_name                 | Value                         |
+-------------------------------+-------------------------------+
| slow_query_log                | ON                            |
| slow_query_log_file           | /var/log/mysql/slow-query.log |
+-------------------------------+-------------------------------+

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| long_query_time | 1.000 |
+-----------------+-------+

+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+
También puede confirmar que el registro está siendo escrito verificando el contador de consultas lentas:
SHOW GLOBAL STATUS LIKE 'Slow_queries';
Este contador se incrementa cada vez que una consulta supera long_query_time, independientemente de si el registro en archivo está activo — útil para confirmar que realmente están ocurriendo consultas lentas antes de dedicar tiempo a analizar un archivo de registro vacío.
Paso 7: Leer e interpretar el registro sin procesar
Use tail para monitorear el registro en tiempo real durante una prueba de carga o una ventana de tráfico pico:
sudo tail -f /var/log/mysql/slow-query.log
Una entrada típica del registro tiene este aspecto:
# Time: 2024-10-11T12:45:23.489187Z
# User@Host: app_user[app_user] @ 10.0.1.45 []  Id: 1042
# Query_time: 4.561529  Lock_time: 0.000115  Rows_sent: 1  Rows_examined: 847293
# Bytes_sent: 512
SET timestamp=1697030723;
SELECT * FROM orders WHERE customer_email = 'user@example.com' ORDER BY created_at DESC;
Lo que cada campo le indica:

Query_time — tiempo total de ejecución en segundos medido en tiempo real
Lock_time — tiempo empleado esperando bloqueos de tabla o de fila; una alta relación de Lock_time respecto a Query_time apunta a contención, no a un índice faltante
Rows_sent — filas devueltas al cliente
Rows_examined — filas que el motor de almacenamiento escaneó para producir el resultado; una relación de Rows_examined / Rows_sent superior a 100:1 es una señal clara de un índice faltante o poco selectivo
Bytes_sent — presente en la verbosidad extendida de MariaDB; útil para identificar consultas que devuelven conjuntos de resultados innecesariamente grandes

En el ejemplo anterior, la consulta examinó 847.293 filas para devolver 1 fila. Agregar un índice en customer_email reduciría Rows_examined a aproximadamente 1, reduciendo el tiempo de ejecución de 4,5 segundos a submilisegundos.
Paso 8: Analizar el registro con mysqldumpslow y pt-query-digest
Leer el archivo de registro sin procesar es poco práctico a escala. Dos herramientas agregan y clasifican las consultas lentas por impacto total.
Usando mysqldumpslow (incluido con MySQL/MariaDB)
# Top 10 queries by total execution time
sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# Top 10 queries by average execution time
sudo mysqldumpslow -s at -t 10 /var/log/mysql/slow-query.log

# Top 10 queries by rows examined
sudo mysqldumpslow -s r -t 10 /var/log/mysql/slow-query.log
mysqldumpslow normaliza los parámetros de las consultas (reemplazando valores literales con N o S) para que las consultas estructuralmente idénticas con diferentes valores de parámetros se agrupen juntas — esencial para identificar patrones de alta frecuencia.
Usando pt-query-digest (Percona Toolkit — Recomendado para producción)
# Install Percona Toolkit (Debian/Ubuntu)
sudo apt-get install percona-toolkit

# Install Percona Toolkit (RHEL/CentOS/AlmaLinux)
sudo yum install percona-toolkit

# Generate a full digest report
sudo pt-query-digest /var/log/mysql/slow-query.log

# Show only the top 5 queries by total time
sudo pt-query-digest --limit 5 /var/log/mysql/slow-query.log

# Output to a file for later review
sudo pt-query-digest /var/log/mysql/slow-query.log > /tmp/slow_query_report.txt
pt-query-digest produce un informe clasificado que muestra la huella de cada consulta, el tiempo total de ejecución, el tiempo promedio, el recuento de llamadas y la distribución por percentiles. Es significativamente más potente que mysqldumpslow y es la herramienta estándar utilizada por los DBA profesionales para el análisis de registros lentos.
Paso 9: Configurar la rotación de registros con logrotate
Sin rotación, el registro de consultas lentas crece indefinidamente. En un servidor ocupado con long_query_time establecido en 1 segundo, el archivo puede alcanzar varios gigabytes en días.
Cree una configuración dedicada de logrotate:
sudo nano /etc/logrotate.d/mysql-slow
/var/log/mysql/slow-query.log {
    daily
    rotate 14
    missingok
    notifempty
    compress
    delaycompress
    sharedscripts
    postrotate
        /usr/bin/mysqladmin flush-logs 2>/dev/null || true
    endscript
}
Directivas clave explicadas:

rotate 14 — conserva 14 días de archivos comprimidos; ajuste según su presupuesto de disco y requisitos de auditoría
compress / delaycompress — comprime con gzip los archivos rotados, pero retrasa la compresión un ciclo para evitar comprimir un archivo que el daemon aún puede tener abierto
postrotate — ejecuta mysqladmin flush-logs después de la rotación, lo que indica al daemon que cierre el manejador del archivo de registro actual y abra uno nuevo; sin esto, MySQL/MariaDB continúa escribiendo en el archivo renombrado hasta el próximo reinicio

Fuerce una rotación manual para probar la configuración:
sudo logrotate -f /etc/logrotate.d/mysql-slow
Paso 10: Deshabilitar el registro de consultas lentas cuando ya no sea necesario
El registro continuo de consultas lentas con un umbral bajo (p. ej., 0,5 segundos) en un servidor de alto tráfico agrega una sobrecarga de E/S medible. Deshabilítelo una vez que haya recopilado datos suficientes:
Mediante el archivo de configuración (persistente):
[mysqld]
slow_query_log = 0
Luego reinicie el servicio:
sudo systemctl restart mysqld   # or mariadb
Mediante variable en tiempo de ejecución (inmediato, no persistente):
SET GLOBAL slow_query_log = 'OFF';
El método en tiempo de ejecución es preferible durante las horas de producción — surte efecto en milisegundos sin tiempo de inactividad.
Avanzado: Usar performance_schema como complemento
El registro de consultas lentas captura las consultas que superan un umbral de tiempo. La tabla performance_schema events_statements_summary_by_digest captura estadísticas agregadas para cada patrón de consulta distinto, independientemente del tiempo de ejecución. Usar ambos juntos proporciona una imagen completa.
SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    ROUND(SUM_TIMER_WAIT / 1e12, 3)     AS total_time_sec,
    ROUND(AVG_TIMER_WAIT / 1e12, 6)     AS avg_time_sec,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
Esta consulta muestra los 10 patrones de consulta que más tiempo consumen en todo el historial de sentencias — incluyendo consultas rápidas que se ejecutan millones de veces y dominan colectivamente el tiempo de CPU, lo que el registro de consultas lentas nunca capturaría.
Consideraciones sobre el entorno de alojamiento
El umbral óptimo de long_query_time depende en gran medida del rol del servidor y su perfil de recursos:

Entornos de alojamiento compartido — generalmente sin acceso directo a my.cnf; use SET GLOBAL si el proveedor de alojamiento otorga el privilegio SUPER o SYSTEM_VARIABLES_ADMIN, o solicite acceso al registro lento a través del panel de control
Entornos VPS — el acceso root completo significa control total sobre todos los parámetros de configuración; una instalación de VPS con cPanel expone la configuración del registro de consultas lentas a través del Editor de Configuración MySQL de WHM, que escribe directamente en my.cnf

Si su pila de aplicaciones incluye un frontend web administrado a través de un Panel de Control VPS, correlacionar las marcas de tiempo del registro de consultas lentas con las marcas de tiempo del registro de acceso HTTP de su aplicación es un método eficaz para rastrear la latencia de la base de datos hasta solicitudes específicas de cara al usuario.

Matriz de decisión práctica: Elegir el umbral correcto

Lista de verificación técnica y conclusiones clave

Antes de cerrar una investigación de consultas lentas, verifique cada uno de los siguientes puntos:

• La sección [mysqld] en my.cnf contiene slow_query_log = 1, una ruta válida de slow_query_log_file y un long_query_time apropiado para su perfil de tráfico
• El archivo de registro y su directorio padre son propiedad del usuario del sistema mysql con permisos de escritura; en sistemas SELinux, el contexto del archivo está establecido en mysqld_log_t

SHOW VARIABLES LIKE '%slow_query%' confirma slow_query_log = ON y la ruta de archivo correcta después del reinicio del servicio
SHOW GLOBAL STATUS LIKE 'Slow_queries' muestra un contador distinto de cero e incrementándose, confirmando que realmente están ocurriendo consultas que califican
log_queries_not_using_indexes está habilitado y emparejado con min_examined_row_limit para evitar que búsquedas triviales de una sola fila inunden el registro
log_slow_admin_statements está habilitado para capturar ALTER TABLE, OPTIMIZE TABLE y operaciones DDL similares que son fuentes comunes de bloqueos de tabla inesperados
Una configuración de logrotate está en su lugar con un gancho postrotate que llama a mysqladmin flush-logs

Preguntas frecuentes

¿Habilitar el registro de consultas lentas afecta el rendimiento de la base de datos?

Con un umbral de 1 segundo o superior en una carga de trabajo de producción típica, la sobrecarga es insignificante — generalmente menos del 1% del tiempo total de ejecución de consultas. La sobrecarga se vuelve medible solo cuando long_query_time se establece por debajo de 0,1 segundos o cuando log_queries_not_using_indexes está habilitado en un esquema con muchas tablas pequeñas sin índices. Use log_slow_rate_limit (MariaDB) o aumente min_examined_row_limit para mitigar esto.

¿Puedo habilitar el registro de consultas lentas sin reiniciar MySQL o MariaDB?

Sí. Use SET GLOBAL slow_query_log = 'ON' y SET GLOBAL long_query_time = 1 desde cualquier sesión de cliente MySQL con el privilegio SUPER o SYSTEM_VARIABLES_ADMIN. El cambio surte efecto inmediatamente. Escriba los mismos valores en my.cnf para hacerlos persistentes entre reinicios.

¿Cuál es la diferencia entre Query_time y Lock_time en el registro de consultas lentas?

Query_time es el tiempo total transcurrido en tiempo real desde que el servidor recibió la consulta hasta que envió la última fila al cliente. Lock_time es la porción de ese total empleada esperando adquirir bloqueos de tabla o de fila. Una consulta con Lock_time cercano a Query_time es un problema de contención de bloqueos, no un problema de índice — la solución implica el diseño de transacciones o reducir el alcance del bloqueo, no agregar índices.

¿Por qué mi archivo de registro de consultas lentas está vacío aunque slow_query_log = ON?

Las causas más comunes son: (1) ninguna consulta ha superado realmente long_query_time todavía — verifique con SHOW GLOBAL STATUS LIKE 'Slow_queries'; (2) la ruta del archivo de registro no existe o el usuario mysql carece de permiso de escritura; (3) en sistemas SELinux, el contexto del archivo es incorrecto; (4) la variable slow_query_log_file apunta a una ruta diferente a la del archivo que está inspeccionando — confirme con SHOW VARIABLES LIKE 'slow_query_log_file'.

¿Cómo encuentro la consulta más dañina en el registro de consultas lentas?

Ejecute pt-query-digest y ordene por R/Call (filas examinadas por llamada) o Response time (tiempo acumulado total). La consulta en la parte superior del ranking de Response time está consumiendo el mayor tiempo agregado de base de datos y debe ser el primer objetivo para el análisis de EXPLAIN y la optimización de índices. Si pt-query-digest no está disponible, use mysqldumpslow -s t -t 1 para extraer la consulta con el mayor tiempo total.