MariaDB / Cómo configurar y leer el registro de consultas lentas

El registro de consultas lentas de MariaDB guarda SQL que supera un umbral de tiempo o cumple otras condiciones de registro activadas. Para leer un archivo, activa slow_query_log, incluye FILE en log_output y revisa el umbral y los filtros de la conexión que ejecuta la consulta. Los ajustes actuales pueden cambiarse por SQL sin reiniciar; para conservarlos después hay que guardarlos en un archivo de opciones.

Qué se registra y qué necesitas

El registro permite estudiar la duración del SQL en el servidor y las filas examinadas. Activarlo no acelera automáticamente las consultas: sirve para investigar planes e índices. La respuesta de una página incluye además red y aplicación, por lo que no equivale al tiempo de ejecución SQL.

El umbral habitual es long_query_time, en segundos: 1 significa un segundo. Una condición de ausencia de índices puede incluir consultas rápidas, mientras que límites de filas, filtros y muestreo pueden omitir consultas que superan el umbral. Consulta la descripción del registro de consultas lentas de MariaDB.

Esta guía se dirige a un servidor Linux autogestionado. Necesitas una cuenta SQL administrativa y permisos del sistema para editar la configuración. Las variables globales requieren el privilegio correspondiente en tu versión. Si se deniega el acceso, utiliza una cuenta administrativa en vez de conceder permisos amplios a la aplicación. Los servicios gestionados pueden exigir sus grupos de parámetros y visores de registros.

MariaDB 10.11 introdujo nuevos nombres para algunas variables; los antiguos siguen disponibles como alias. Los ejemplos usan esos nombres conocidos y debes comprobar su compatibilidad en el servidor real. La referencia de variables de servidor MariaDB detalla versiones y alcance.

Activar sin reiniciar y comprobar una consulta de prueba

Anotar los valores y guardar los ajustes originales

Con la autenticación local por socket habitual de Ubuntu puedes usar sudo mariadb. En otro entorno, emplea su método de autenticación, como mariadb -u cuenta_administrativa -p. Anota primero versión, directorio de datos y valores globales. Un alias puede mostrarse con su nombre nuevo.

SELECT VERSION(), @@GLOBAL.datadir;
SHOW GLOBAL VARIABLES WHERE Variable_name IN (
  'slow_query_log', 'log_slow_query',
  'slow_query_log_file', 'log_slow_query_file',
  'long_query_time', 'log_slow_query_time',
  'log_output', 'general_log', 'log_slow_rate_limit',
  'min_examined_row_limit', 'log_queries_not_using_indexes',
  'log_slow_filter', 'log_slow_disabled_statements'
);

Para deshacer una prueba temporal, guarda estos valores globales en la misma conexión administrativa. Las variables de usuario @saved_... solo existen en esa conexión y desaparecen al desconectarse. Guarda también los resultados en tus notas y ejecuta posteriormente la restauración en la misma conexión.

SET @saved_slow = @@GLOBAL.slow_query_log,
    @saved_file = @@GLOBAL.slow_query_log_file,
    @saved_output = @@GLOBAL.log_output,
    @saved_time = @@GLOBAL.long_query_time;

Activar el archivo y distinguir los valores de sesión

El ejemplo utiliza salida a archivo. Un nombre relativo como mariadb-slow.log se resuelve dentro de @@GLOBAL.datadir. Si ya mantienes un registro lento, puedes conservar su ruta existente.

SET GLOBAL slow_query_log_file = 'mariadb-slow.log';
SET GLOBAL log_output = 'FILE';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log = ON;

log_output también controla la salida del registro general de consultas. Si se usa TABLE, revisa ese requisito antes de aplicar el ejemplo. Elige FILE,TABLE si necesitas ambos destinos. NONE impide la salida aunque el registro esté activado.

Los valores globales son los valores iniciales de nuevas conexiones; no reescriben automáticamente el umbral de cada sesión existente. Compara ambos alcances con la consulta siguiente. Un pool de conexiones puede reutilizar sesiones antiguas, por lo que debes planificar una reconexión o un ajuste de sesión. Véase GLOBAL y SESSION en SHOW VARIABLES.

SELECT @@GLOBAL.long_query_time AS global_seconds,
       @@SESSION.long_query_time AS session_seconds,
       @@GLOBAL.slow_query_log AS global_log,
       @@SESSION.slow_query_log AS session_log;

Ejecutar una prueba controlada y restaurar la sesión

La prueba guarda los ajustes de sesión y establece temporalmente un segundo, cero filas mínimas y un muestreo de uno. Vacía filtros solo en esa sesión. SLEEP no examina filas de tablas y un mínimo positivo podría excluirlo. Comprueba también que los tipos de instrucciones desactivados no excluyan SELECT.

SET @session_log = @@SESSION.slow_query_log,
    @session_time = @@SESSION.long_query_time,
    @session_rows = @@SESSION.min_examined_row_limit,
    @session_rate = @@SESSION.log_slow_rate_limit,
    @session_filter = @@SESSION.log_slow_filter;
SET SESSION slow_query_log = ON;
SET SESSION long_query_time = 1;
SET SESSION min_examined_row_limit = 0;
SET SESSION log_slow_rate_limit = 1;
SET SESSION log_slow_filter = '';
SELECT SLEEP(1.2) AS slow_log_probe_13708;

Una ejecución completada normalmente devuelve 0. Al terminar, el registro debe contener slow_log_probe_13708, puesto que supera el umbral. La duración real varía según la carga. Es una prueba del recorrido de registro, no de rendimiento ni de índices.

Tras comprobar el archivo, restaura la sesión en la misma conexión SQL. Como vuelve a los valores anteriores, verifica por separado la captura real posterior, por ejemplo con una conexión nueva que herede los valores globales deseados.

SET SESSION slow_query_log = @session_log;
SET SESSION long_query_time = @session_time;
SET SESSION min_examined_row_limit = @session_rows;
SET SESSION log_slow_rate_limit = @session_rate;
SET SESSION log_slow_filter = @session_filter;

Guardar una configuración persistente en Ubuntu o Rocky Linux

SET GLOBAL no sustituye un archivo de opciones persistente. Identifica los archivos y argumentos usados realmente por el servicio. Revisa las salidas por si contienen ajustes sensibles antes de compartirlas.

sudo systemctl cat mariadb
sudo mariadbd --help --verbose
sudo mariadbd --print-defaults

1. Identifica y respalda la configuración. Los paquetes habituales de Ubuntu usan /etc/mysql/mariadb.conf.d/; los de Rocky, /etc/my.cnf.d/. La guía de MariaDB de Rocky Linux documenta esta última ubicación. Tras confirmar la ruta incluida puedes crear 60-slow-log.cnf en Ubuntu o slow-query.cnf en Rocky. Revisa antes cualquier archivo existente con el mismo nombre.
2. Añade estas opciones de servidor. Busca archivos posteriores o argumentos que sobrescriban los valores y resuelve duplicados contradictorios. Un segundo es un punto de partida, no un objetivo universal.

[mariadb]
slow_query_log=ON
slow_query_log_file=mariadb-slow.log
log_output=FILE
long_query_time=1
log_slow_rate_limit=1
min_examined_row_limit=0

3. Reinicia en una ventana de mantenimiento y comprueba los valores. Confirma con mariadbd --print-defaults que se lee el archivo y reinicia como sigue. Adapta la última orden a la autenticación local y al nombre real del servicio.

sudo systemctl restart mariadb
sudo systemctl status mariadb --no-pager
sudo mariadb -e "SHOW GLOBAL VARIABLES LIKE 'slow_query_log%'; SHOW GLOBAL VARIABLES LIKE 'long_query_time'; SHOW GLOBAL VARIABLES LIKE 'log_output';"

Abre otra conexión, verifica ruta, umbral, destino y valores de sesión y repite la prueba. Otra ruta absoluta requiere escritura para la cuenta del servidor y revisar AppArmor en Ubuntu o SELinux en Rocky. Empezar con un nombre relativo en el directorio de datos existente evita introducir reglas para otra carpeta. La configuración de archivos de opciones de MariaDB explica el orden de búsqueda.

Leer el archivo o la tabla

Examinar el archivo y resumir duración o frecuencia

Utiliza la ruta confirmada mediante SQL. Cambia /var/lib/mysql/ si tu directorio es otro. tail -F sigue el nombre del archivo y muestra registros nuevos; pulsa Ctrl+C para salir de la visualización.

sudo tail -n 80 /var/lib/mysql/mariadb-slow.log
sudo tail -F /var/lib/mysql/mariadb-slow.log

Localiza la prueba u otra instrucción en el original y examina conjuntamente Query_time, Lock_time, Rows_sent y Rows_examined. mariadb-dumpslow agrupa SQL parecido en archivos grandes. Si falta, revisa los paquetes de tu distribución; también puede estar instalado con su nombre anterior, mysqldumpslow.

sudo mariadb-dumpslow -s t -t 10 /var/lib/mysql/mariadb-slow.log
sudo mariadb-dumpslow -s at -t 10 /var/lib/mysql/mariadb-slow.log
sudo mariadb-dumpslow -s c -t 10 /var/lib/mysql/mariadb-slow.log

-s t ordena por tiempo acumulado, -s at por tiempo medio y -s c por cantidad de ejecuciones. -t 10 solicita los diez primeros grupos. Números y cadenas se abstraen por defecto para agrupar. Usa el resumen para priorizar y después revisa el SQL original y su plan.

Cuando está configurada la salida TABLE

Si log_output incluye TABLE o FILE,TABLE, una cuenta autorizada puede consultar mysql.slow_log. Con el ejemplo de solo FILE, que no haya registros nuevos en la tabla no significa que falle el archivo.

SELECT start_time, query_time, lock_time,
       rows_sent, rows_examined, sql_text
FROM mysql.slow_log
ORDER BY start_time DESC
LIMIT 20;

La salida a tabla tiene capacidades y costes distintos: por ejemplo, la información EXPLAIN ampliada solo se registra en archivos. No cambies sin revisar el destino compartido; consulta cómo escribir registros de MariaDB en tablas.

Decidir qué consultas investigar primero

Considera frecuencia y duración acumulada, no solo la ejecución individual más lenta. Una consulta de cinco segundos una vez y otra de 0,2 segundos mil veces tienen prioridades distintas según la media y el total. Pero un umbral de un segundo omitiría la segunda bajo la condición temporal normal. Un registro filtrado no representa toda la carga de la base.

No añadas un índice solo por una entrada del registro. Un recorrido completo puede ser razonable en una tabla pequeña, y los índices aumentan escrituras y almacenamiento. Compara planes antes y después en condiciones realistas iguales. Diagnósticos como ANALYZE pueden ejecutar realmente las instrucciones: evalúa antes carga y posibles cambios.

Resolver registros ausentes o excesivos

• Activado pero vacío: revisa NONE, archivo frente a tabla, instancia conectada y activación y umbral de la sesión. Una consulta en curso puede no aparecer hasta terminar.
• Falta la prueba: revisa el mínimo de filas, log_slow_filter, instrucciones excluidas y log_slow_rate_limit. 1000 no significa registrar todas las instrucciones elegibles; usa 1 en la sesión de prueba controlada.
• Aparecen consultas rápidas: comprueba log_queries_not_using_indexes o not_using_index en el filtro. Aumentar el umbral temporal no siempre elimina esa condición.
• Error de acceso al archivo: verifica cuenta del servidor, directorios superiores, denegaciones AppArmor/SELinux y espacio libre. Los permisos del usuario cliente no son los del proceso servidor.

Adapta las rutas de estas comprobaciones Linux. Ante un fallo de arranque, examina rutas denegadas y opciones desconocidas en vez de desactivar toda la política de seguridad.

sudo journalctl -u mariadb -b -n 100 --no-pager
sudo journalctl -k -b --no-pager
sudo ls -ld /var/lib/mysql
sudo ls -l /var/lib/mysql/mariadb-slow.log
df -h /var/lib/mysql

Detener la captura, restaurar ajustes y gestionar archivos

Las instrucciones siguientes desactivan el registro, pero no borran los datos existentes. Si el archivo de opciones lo mantiene activado, el siguiente reinicio lo volverá a habilitar: ajusta también la configuración persistente.

SET GLOBAL slow_query_log = OFF;
SET SESSION slow_query_log = OFF;

Si sigue abierta la misma conexión administrativa que guardó los valores originales, restaura los ajustes globales con este SQL. Tras desconectarte no uses variables de usuario desaparecidas; emplea los valores originales anotados aparte. Comprueba por separado las sesiones de aplicaciones y los archivos de opciones.

SET GLOBAL slow_query_log = OFF;
SET GLOBAL slow_query_log_file = @saved_file;
SET GLOBAL log_output = @saved_output;
SET GLOBAL long_query_time = @saved_time;
SET GLOBAL slow_query_log = @saved_slow;

Para una captura prolongada, gestiona retención, tamaños y espacio. Renombrar un archivo puede dejar al servidor escribiendo en su identificador anterior. Revisa la política logrotate del paquete y el paso de reapertura. FLUSH SLOW LOGS cierra y abre el registro lento; no crea una política de archivado, compresión ni borrado. Requiere el privilegio RELOAD correspondiente.

FLUSH SLOW LOGS;

Ajusta la operación con la guía de rotación de registros MariaDB en Linux y la referencia FLUSH. Si el archivo está en el directorio de datos, confirma que la rotación cubre esa ruta. El SQL puede contener datos personales o secretos: restringe el acceso a registros y copias y oculta valores sensibles antes de compartirlos.