MariaDB / Configurer et lire le journal des requêtes lentes

Le journal des requêtes lentes de MariaDB enregistre les SQL dépassant un seuil de durée ou répondant à d’autres conditions activées. Pour lire un fichier, activez slow_query_log, incluez FILE dans log_output et vérifiez le seuil et les filtres de la connexion exécutante. Les réglages en cours peuvent être modifiés par SQL sans redémarrage ; leur persistance nécessite un fichier d’options.

Que contient le journal et que faut-il préparer ?

Ce journal aide à étudier la durée des SQL côté serveur et les lignes examinées. L’activer n’accélère pas automatiquement les requêtes : utilisez-le pour examiner plans d’exécution et index. La réponse d’une page comprend aussi le réseau et le traitement applicatif ; elle ne correspond donc pas à la seule durée SQL.

Le seuil habituel, long_query_time, est exprimé en secondes : 1 signifie une seconde. Une condition d’absence d’index peut inclure des requêtes rapides ; limites de lignes, filtres et échantillonnage peuvent au contraire omettre des requêtes dépassant le seuil. Consultez la présentation du journal des requêtes lentes MariaDB.

Ce guide concerne un serveur Linux autogéré. Il faut un compte SQL administratif et les droits système pour modifier la configuration. Les variables globales exigent le privilège correspondant dans votre version. En cas de refus, utilisez un compte administratif plutôt que d’étendre largement les droits de l’application. Un service de base de données géré peut imposer ses groupes de paramètres et son interface de journaux.

MariaDB 10.11 a introduit de nouveaux noms pour certaines variables ; les anciens noms ci-dessous restent des alias. Les exemples utilisent ces noms familiers, à vérifier sur le serveur réel. La référence des variables serveur MariaDB précise portée et versions.

Activer sans redémarrage et vérifier une requête de test

Relever les valeurs et conserver les paramètres initiaux

Avec l’authentification locale par socket habituelle sous Ubuntu, utilisez sudo mariadb. Sinon, employez la méthode configurée, par exemple mariadb -u compte_administratif -p. Relevez version, répertoire de données et valeurs globales. Un alias peut apparaître sous son nouveau nom dans le résultat.

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'
);

Pour revenir après un essai temporaire, sauvegardez ces valeurs dans la même connexion administrative. Les variables utilisateur @saved_... n’existent que dans cette connexion et disparaissent à sa fermeture. Conservez aussi les résultats dans vos notes et exécutez la restauration ultérieure dans cette même connexion.

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;

Activer le fichier et distinguer les valeurs de session

L’exemple suivant utilise un fichier. Le nom relatif mariadb-slow.log se résout dans @@GLOBAL.datadir. Si un journal est déjà exploité, vous pouvez conserver son emplacement actuel.

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 pilote également la sortie du journal général. Si le serveur utilise déjà TABLE, examinez ce besoin avant d’appliquer l’exemple. Choisissez FILE,TABLE si les deux destinations sont nécessaires. NONE empêche toute sortie même quand la collecte est activée.

Les valeurs globales sont des valeurs initiales pour les nouvelles connexions ; elles ne réécrivent pas automatiquement les seuils des sessions existantes. Comparez les deux portées ci-dessous. Un pool applicatif peut réutiliser d’anciennes connexions : prévoyez une reconnexion ou un réglage ciblé des sessions. Voir GLOBAL et SESSION dans 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;

Tester dans la connexion et restaurer la session

Le test sauvegarde les valeurs de session puis fixe temporairement une seconde, zéro ligne minimale et un échantillonnage de un. Il vide les filtres uniquement pour cette session. Comme SLEEP n’examine aucune ligne de table, un minimum positif pourrait l’exclure. Vérifiez aussi que les types d’instructions exclus n’interdisent pas 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;

Une exécution terminée normalement retourne 0. Ensuite, le journal doit contenir slow_log_probe_13708, puisque sa durée dépasse le seuil. La durée réelle varie avec la charge. Ce test vérifie le chemin de collecte, pas les performances ou les index.

Après lecture du fichier, restaurez les valeurs de session dans la même connexion SQL. Puisqu’il s’agit des anciennes valeurs, vérifiez séparément la collecte réelle, par exemple avec une nouvelle connexion héritant des valeurs globales souhaitées.

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;

Rendre la configuration persistante sous Ubuntu ou Rocky Linux

SET GLOBAL ne remplace pas un fichier d’options permanent. Identifiez les fichiers et arguments réellement utilisés par le service. Examinez les sorties avant partage pour éviter d’exposer des réglages sensibles.

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

1. Identifiez et sauvegardez la configuration. Les paquets Ubuntu habituels utilisent /etc/mysql/mariadb.conf.d/ et ceux de Rocky /etc/my.cnf.d/. Le guide MariaDB de Rocky Linux documente également ce dernier emplacement. Après vérification du chemin inclus, vous pouvez créer 60-slow-log.cnf sous Ubuntu ou slow-query.cnf sous Rocky. Examinez tout fichier du même nom avant modification.
2. Ajoutez les options serveur ci-dessous. Contrôlez les fichiers lus ensuite et les arguments qui pourraient remplacer les valeurs, puis résolvez les doublons contradictoires. Une seconde est un point de départ, pas une cible universelle.

[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. Redémarrez en maintenance et vérifiez les valeurs. Confirmez la lecture du fichier avec mariadbd --print-defaults, puis redémarrez comme suit. Adaptez la dernière commande à l’authentification locale et au vrai nom du service.

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';"

Ouvrez une nouvelle connexion, contrôlez chemin, durée, destination et valeurs de session, puis répétez le test. Un autre chemin absolu exige les droits d’écriture du compte serveur et l’examen d’AppArmor sous Ubuntu ou de SELinux sous Rocky. Le nom relatif dans le répertoire de données existant évite d’abord de nouvelles règles d’accès pour un autre dossier. La configuration des fichiers d’options MariaDB explique leur ordre de recherche.

Lire le fichier ou la table de journalisation

Examiner le fichier et classer par durée ou fréquence

Utilisez le chemin confirmé par SQL. Remplacez /var/lib/mysql/ si votre répertoire diffère. tail -F suit le nom du fichier et affiche les nouveaux enregistrements ; Ctrl+C arrête l’affichage.

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

Repérez le test ou une autre instruction dans le fichier brut et examinez ensemble Query_time, Lock_time, Rows_sent et Rows_examined. mariadb-dumpslow regroupe les SQL similaires dans les gros journaux. Si l’outil manque, vérifiez les paquets de votre distribution ; il peut aussi porter son ancien nom, 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 trie par durée cumulée, -s at par durée moyenne et -s c par nombre d’exécutions. -t 10 demande les dix premiers groupes. Nombres et chaînes sont abstraits par défaut pour former les groupes. Priorisez avec ce résumé, puis examinez le SQL d’origine et son plan.

Lorsque la sortie TABLE est configurée

Si log_output contient TABLE ou FILE,TABLE, un compte autorisé peut interroger mysql.slow_log. Avec l’exemple FILE seul, l’absence de nouveaux enregistrements dans la table n’indique pas un échec du fichier.

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

Les capacités et les coûts des deux destinations diffèrent : la sortie EXPLAIN étendue, par exemple, est disponible uniquement dans un fichier. Ne modifiez pas à la légère cette destination commune ; consultez l’écriture des journaux MariaDB dans des tables.

Choisir les requêtes à examiner en priorité

Considérez la fréquence et la durée cumulée plutôt que la seule exécution la plus lente. Une requête de cinq secondes une fois et une requête de 0,2 seconde mille fois ont des priorités différentes selon la moyenne et le total. Mais un seuil d’une seconde omettrait la seconde avec la condition de durée ordinaire. Un journal filtré ne représente pas toute la charge.

N’ajoutez pas un index sur la seule base du journal. Un parcours complet peut convenir à une petite table, et un index coûte en écritures et en stockage. Comparez les plans avant et après dans des conditions réalistes identiques. Des diagnostics comme ANALYZE peuvent exécuter réellement les instructions : évaluez charge et modifications possibles avant usage.

Diagnostiquer une absence ou un excès d’enregistrements

• Activé mais vide : vérifiez NONE, le choix fichier/table, l’instance connectée et l’activation et le seuil de session. Une requête en cours peut n’apparaître qu’une fois terminée.
• Le test est absent : vérifiez le minimum de lignes, log_slow_filter, les instructions exclues et log_slow_rate_limit. 1000 ne signifie pas que chaque instruction admissible est enregistrée ; utilisez 1 dans la session de test contrôlée.
• Des requêtes rapides apparaissent : examinez log_queries_not_using_indexes ou le filtre not_using_index. Augmenter le seuil ne supprime pas nécessairement cette condition.
• Accès au fichier refusé : contrôlez le compte serveur, les dossiers parents, les refus AppArmor/SELinux et l’espace libre. Les droits de l’utilisateur du client ne sont pas ceux du processus serveur.

Adaptez les chemins de ces vérifications Linux. En cas d’échec de démarrage, cherchez les chemins refusés et options inconnues plutôt que de désactiver toute la sécurité.

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

Arrêter la collecte, restaurer les paramètres et gérer les fichiers

Les instructions suivantes désactivent la collecte sans supprimer les enregistrements existants. Si un fichier d’options l’active toujours, le prochain redémarrage la réactivera : modifiez donc aussi les paramètres persistants selon votre objectif.

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

Si la même connexion administrative ayant sauvegardé les valeurs initiales est encore ouverte, utilisez ce SQL pour restaurer les paramètres globaux. Après déconnexion, n’utilisez pas des variables utilisateur disparues ; reprenez les vraies valeurs notées ailleurs. Vérifiez séparément les sessions applicatives existantes et les fichiers d’options.

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;

Pour une collecte prolongée, gérez rétention, taille des fichiers et espace disque. Un simple renommage peut laisser le serveur écrire via son ancien descripteur. Examinez la politique logrotate existante du paquet et l’étape de réouverture. FLUSH SLOW LOGS ferme et rouvre le journal ; cette commande ne définit pas une politique d’archivage, compression ou suppression. Elle exige le privilège RELOAD correspondant.

FLUSH SLOW LOGS;

Alignez l’exploitation sur le guide de rotation des journaux MariaDB sous Linux et la référence FLUSH. Vérifiez qu’un fichier placé dans le répertoire de données est bien couvert. Le SQL peut contenir des données personnelles ou secrètes : restreignez l’accès aux journaux et sauvegardes et masquez les valeurs sensibles avant partage.