Analyser le journal des requêtes lentes MySQL dans l'hébergement : Conseils d'optimisation

Journal des requêtes lentes MySQL me montre dans l'hébergement quelles requêtes consomment du temps, à quelle fréquence elles apparaissent et pourquoi elles ralentissent. Je te montre des étapes concrètes pour activer le log, l'évaluer et modifier les requêtes de manière à ce que les pages se chargent plus rapidement et que les ressources du serveur travaillent plus efficacement.

Points centraux

• Activation et fixer des seuils de manière judicieuse
• Évaluation avec pt-query-digest et mysqldumpslow
• Métriques interpréter les données : Query_time, Lock_time, Rows_examined
• Tuning par des index, EXPLAIN et Rewrites
• Automatisation et monitoring dans l'hébergement

Quel est le rôle du Slow Query Log dans l'hébergement ?

Hébergement signifie ressources partagées, donc chaque milliseconde compte par requête. J'utilise le journal pour trouver les requêtes qui s'exécutent plus longtemps qu'une valeur limite définie et je vois pour chaque requête des chiffres clés comme Query_time, Lock_time, Rows_sent et Rows_examined. Ces chiffres m'indiquent s'il y a un index manquant, une jointure défavorable ou un balayage complet de la table. Sur les serveurs avec plusieurs sites notamment, une seule mauvaise requête peut solliciter fortement le CPU et les E/S. Je donne alors la priorité aux requêtes dont le temps total est le plus élevé, car c'est là que se trouve le plus grand effet de levier sur le temps de chargement et la charge du serveur.

Activation et seuils raisonnables

LancementJe peux utiliser runtime ou en permanence via my.cnf, en fonction de l'accès à l'hébergement. Pour les tests rapides, j'active temporairement le log et je définis long_query_time sur une valeur qui correspond au trafic et au matériel. Pour les sites très utilisés, je vais souvent jusqu'à 0,1 seconde, mais je surveille la taille du journal pour que les E/S ne grossissent pas inutilement. Si les accès directs aux fichiers sont limités, j'utilise les options de schéma de performance du shell MySQL pour générer des rapports. Après le réglage fin, j'écris les paramètres finaux dans le fichier de configuration et je redémarre le service.

SET GLOBAL slow_query_log = 'ON' ;
SET GLOBAL long_query_time = 1 ;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log' ;
SET GLOBAL log_queries_not_using_indexes = 'ON

SHOW VARIABLES LIKE 'slow_query%' ;
SHOW VARIABLES LIKE 'long_query_time' ;

Durable je définis des options comme log_throttle_queries_not_using_indexes et log_slow_admin_statements pour que le log reste utile et n'explose pas. Je documente chaque valeur, par exemple pourquoi long_query_time est de 0,5 ou 0,1 seconde. Cela me permet d'affiner ultérieurement les choses. Dans les environnements partagés, je discute souvent de l'activation avec le fournisseur ou j'utilise son panel. Je relie chaque activation à une date de début afin de pouvoir comparer proprement les effets dans le monitoring et les métriques.

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 10
log_slow_admin_statements = 1

Evaluer efficacement le log lent

Données brutes sont bruyantes, c'est pourquoi je les regroupe avec pt-query-digest et les trie en fonction du temps total sur une période raisonnable. Je détecte ainsi des modèles, des requêtes très variables et des familles de requêtes qui ne varient que par des paramètres. Je vérifie la répartition, pas seulement la moyenne, car les valeurs aberrantes génèrent de véritables problèmes pour les utilisateurs. Pour un aperçu rapide, j'utilise mysqldumpslow pour voir les dix groupes les plus lents. J'obtiens plus de profondeur en utilisant des fenêtres de temps, des filtres de base de données et une exportation vers une analyse de texte.

pt-query-digest /var/log/mysql/slow-query.log
pt-query-digest --since '24h' /var/log/mysql/slow-query.log
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

Utile est en outre de jeter un coup d'œil dans d'autres protocoles lorsque des fonctions d'application ou PHP entrent en jeu. Pour cela, j'ai recours à des flux de travail de logs existants et je regroupe les résultats. Ce guide me permet souvent de commencer : Analyser les logs. Je synchronise les horodatages afin de pouvoir comparer les pics de trafic avec les pics de requêtes. Cela me permet de voir si les échecs de cache, les tâches cron ou les tâches d'importation sollicitent la base de données au même moment.

Interpréter correctement les métriques

Query_time m'indique le temps d'exécution pur ; je donne la priorité aux requêtes de plus d'une seconde en premier. Lock_time indique les temps d'attente dus aux verrous, qui proviennent souvent de transactions inutilement longues ou de gros lots. Le rapport Rows_examined/ Rows_sent me dit si les requêtes examinent trop de lignes et si des index manquent. Si le journal contient beaucoup d'entrées „No index use“, j'active le throttling et j'examine de plus près les tables concernées. Il est important de toujours s'attaquer à la cause plutôt qu'au symptôme : Un index sur la bonne colonne bat toute mise à jour matérielle.

Métriques	Ce que je vois	Mesure
Query_time élevé	Longue durée de fonctionnement par version	Vérifier EXPLAIN, réécrire la requête, compléter l'index
Lock_time élevé	Temps d'attente pour les blocages	Raccourcir les transactions, réduire la taille des lots, isolation appropriée
Rows_examined ≫ Rows_sent	Trop de scans, peu de retours	Indexer les colonnes de filtres, établir la sargabilité
Aucun indice utilisé	Balayage complet de la table	Créer un index, éviter l'expression dans WHERE

Valeurs limites j'ajuste après la première semaine pour ne pas m'enfoncer dans le bruit. Je diminue long_query_time par étapes jusqu'à ce que j'aie suffisamment de résultats pour des améliorations systématiques. Je documente chaque adaptation avec la date et la raison. L'évaluation reste ainsi concentrée. Des résultats de valeur m'évitent de devoir faire deux fois le même travail plus tard.

Pratique : le réglage des requêtes étape par étape

EXPLAIN est mon point de départ avant de modifier le code. Je recherche „type : ALL“, „rows“ avec de grands nombres et „Using filesort“ ou „Using temporary“. Les fonctions sur les colonnes dans WHERE ou JOIN empêchent souvent l'utilisation d'index. Au lieu de cela, je formule des conditions sargables et je vérifie ensuite le nouveau plan. Chaque étape doit effectuer la réduction de ligne tôt et de manière ciblée.

EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2026 ;

-- C'est mieux :
CREATE INDEX idx_orders_created ON orders(created_at) ;
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01' ;

JOINs j'optimise en contrôlant l'ordre de jointure et les index correspondants sur les clés de jointure. Je vérifie si un index composite couvre WHERE + ORDER BY pour éviter le filesort. Je place LIMIT là où seul un aperçu est nécessaire. J'économise la mise en cache des résultats au niveau de l'application pour les requêtes identiques répétées avec un faible taux de modification. Tu trouveras ici une introduction plus approfondie aux index et aux verrous : Indices et verrouillage.

Stratégies d'indexation pour CMS et boutiques

WordPress, WooCommerce ou les systèmes de boutique génèrent des modèles typiques : beaucoup de lecture, une écriture ponctuelle, souvent avec des méta-tables ou des tables de produits. J'analyse les itinéraires les plus fréquents - page d'accueil, catégorie, recherche, checkout - et place des index ciblés sur les colonnes de filtre, de tri et de jointure. Les index de couverture (par ex. (status, created_at, id)) permettent d'économiser de nombreux recours au tableau. Pour les recherches sur les préfixes, j'utilise des formes d'index appropriées ou du texte intégral, au lieu de LIKE ‚%wort%‘. Je mesure chaque changement d'index avant et après le live avec les mêmes profils de charge.

Croissance dans les ensembles de données, je vérifie la cardinalité et les histogrammes afin de ne pas indexer sur des valeurs rares. Je maintiens le nombre d'index à un niveau bas afin de maîtriser la charge d'écriture et l'utilisation de la mémoire. Des index composites consolidés remplacent plusieurs index individuels. Je régule les tâches de type auto-vacuum dans MySQL par des analyses régulières et des reconstructions uniquement si nécessaire. Ainsi, l'optimiseur reste fiable.

Paramètres du serveur, mise en cache et mémoire

InnoDB Je détermine la taille du buffer pool en fonction des enregistrements actifs et de la taille des index, et non en fonction de valeurs forfaitaires. Je l'augmente jusqu'à ce que la Working Set Size soit largement en mémoire et que le Page Miss Rate baisse. Je règle tmp_table_size et max_heap_table_size de manière à ce qu'il y ait moins de tables temporaires sur le disque. Pour la sécurité en écriture et la latence, j'équilibre innodb_flush_log_at_trx_commit de manière judicieuse par rapport à l'application. Au niveau de l'application, je mets en cache les résultats fréquents et j'utilise la mise en cache HTTP pour que la base de données voie moins de requêtes.

Matériel informatique et les effets de réseau sont pris en compte dans le diagnostic : Des E/S de stockage lentes ou une CPU surchargée détectent immédiatement les requêtes. C'est pourquoi je mesure l'IO-wait parallèlement aux métriques de la base de données. Celui qui a besoin de plus de réserves planifie une mise à l'échelle verticale ou horizontale avec un objectif mesurable. Ce guide te donne un aperçu compact des goulets d'étranglement, du réglage et des ressources : Matériel et mémoire cache. Je m'assure ainsi de ne pas tourner le mauvais bouton à l'aveuglette.

Concurrence et verrouillage dans l'hébergement

Lock_time se développe lorsque de longues transactions touchent de nombreuses lignes ou lorsque des tâches de nettoyage sont exécutées à l'heure de pointe. Je raccourcis les écritures, je divise les grandes mises à jour en petits lots et je réduis ainsi le temps de maintien des verrous. Des niveaux d'isolation appropriés réduisent les conflits sans compromettre la cohérence des données. J'allège les hotspots avec des index secondaires et des conditions WHERE appropriées afin que moins de lignes soient concernées. Je planifie les jobs d'arrière-plan dans des créneaux horaires à faible trafic afin de donner la priorité aux actions des utilisateurs.

impasses j'examine des modèles récurrents : mêmes tableaux, ordre changeant, lignes identiques. J'uniformise l'ordre d'accès dans le code et les procédures stockées. La logique de reprise avec gigue résout les collisions temporaires. Lorsque c'est possible, j'isole les opérations les plus coûteuses dans des files d'attente. Ainsi, la variance diminue sensiblement et la performance perçue augmente.

Alertes et flux de travail automatisés

Routine suggère l'actionnisme : j'évalue le log quotidiennement ou hebdomadairement, en fonction du trafic et de la fréquence des releases. Un petit script compte les nouvelles occurrences au cours des dernières minutes et m'envoie un e-mail lorsque la valeur seuil augmente. En outre, je génère régulièrement des rapports pt-query-digest et garde toujours un œil sur le top 10. J'accompagne les jours de release d'un monitoring plus étroit. Je détecte ainsi les régressions avant que les utilisateurs ne les remarquent.

#!/bin/bash
LOG_FILE="/var/log/mysql/slow-query.log"
THRESHOLD=100
RECENT_COUNT=$(awk -v cutoff="$(date -d '5 minutes ago' '+%Y-%m-%dT%H:%M')" '/^# Time:/ { if ($3 >= cutoff) count++ } END { print count+0 }' "$LOG_FILE")
if [ "$RECENT_COUNT" -gt "$HRESHOLD" ] ; then
    echo "ALERTE : $RECENT_COUNT slow queries" | mail -s "Alerte MySQL" [email protected]
fi

Transparence je crée des responsabilités claires : Qui réagit aux pics, qui adapte les indices, qui teste les versions. Je résume les résultats dans de brefs journaux des changements. Ainsi, chaque membre de l'équipe comprend pourquoi un changement est intervenu et quel a été son impact. Un processus structuré permet de gagner du temps et d'éviter les fausses alertes.

Images d'erreurs et corrections rapides

Pour en savoir plus : Les scans de tableaux déclenchent une charge disproportionnée. Je vérifie d'abord s'il manque un index approprié sur la colonne de filtrage ou si une expression bloque l'index. J'élimine les lock_time élevés en raccourcissant les transactions et en égalisant les opérations concurrentes. Je désamorce les logs débordants avec log_throttle_queries_not_using_indexes et un long_query_time réaliste. Je mesure immédiatement chaque correction par rapport aux chiffres initiaux afin que les succès restent visibles.

Stockage-Je reconnais les goulots d'étranglement à l'augmentation de l'attente IO et à la latence élevée du disque pendant les pics de requêtes. Je réduis alors les écritures inutiles, par exemple en actualisant moins souvent les champs non modifiables. Lorsque les tables augmentent, je prévois un archivage ou des stratégies de partitionnement pour que les données chaudes restent en mémoire. Pour les déclarations d'administration en période de pointe, j'active log_slow_admin_statements afin d'identifier les facteurs de coûts cachés. Les petites corrections ciblées sont ici plus rapidement rentables que les grandes transformations.

Particularités des environnements gérés et en nuage

hébergement géré ou les services cloud limitent souvent les accès aux fichiers. Dans de tels cas, je règle log_output sur TABLE et j'évalue le journal lent directement à partir de la base de données. Dans MySQL 8.0, j'utilise en plus SET PERSIST pour définir durablement des paramètres sans accès direct à my.cnf. Dans les groupes de paramètres cloud (par exemple pour les services gérés), je saisis les mêmes variables et je prévois une fenêtre de maintenance pour le redémarrage.

-- Si autorisé : paramètres persistants sans redémarrage
SET PERSIST slow_query_log = ON ;
SET PERSIST long_query_time = 0.5 ;
SET PERSIST log_output = 'TABLE' ; -- Alternative à FILE pour un accès limité aux fichiers

-- évaluation si log_output=TABLE
SELECT start_time, user_host, query_time, lock_time, rows_sent, rows_examined, sql_text
FROM mysql.slow_log
ORDER BY query_time DESC
LIMIT 50 ;

RemarqueEn cas de fort trafic, log_output=FILE peut être plus performant, car la journalisation des tables génère des frais généraux supplémentaires. Cependant, dans les environnements restrictifs, TABLE est souvent la seule solution. Je fixe alors des limites plus strictes (par exemple min_examined_row_limit) pour que le volume reste contrôlable.

Rotation, conservation et protection des données

Rotation empêche les logs de remplir le disque. Je fais une rotation quotidienne ou par taille, je compresse les anciens fichiers et je respecte une stratégie de conservation claire (par exemple, 14 jours). Après la rotation, je déclenche un log-flush pour que MySQL écrive proprement dans le nouveau fichier. Ainsi, l'analyse et le fonctionnement restent stables.

# /etc/logrotate.d/mysql-slow
/var/log/mysql/slow-query.log {
  daily
  rotate 14
  size 100M
  compress
  missingok
  notifempty
  créer 640 mysql adm
  postrotate
    test -x /usr/bin/mysqladmin || exit 0
    /usr/bin/mysqladmin flush-logs
  endscript
}

Protection des données est obligatoire : les logs lents peuvent contenir des valeurs de paramètres. Je limite strictement l'accès (droits sur les fichiers, groupes) et je vérifie si des données sensibles sont enregistrées. Si nécessaire, je travaille avec la liaison de paramètres dans l'application afin qu'aucun texte en clair se rapportant à des personnes n'apparaisse dans le journal. Pour les partages en équipe, je préfère partager des rapports agrégés plutôt que des logs bruts.

Utiliser le schéma de performance et le schéma sys

Schéma de performance fournit des métriques même sans activer le journal lent. J'active les consommateurs pertinents pour les déclarations et j'évalue ensuite les vues sys. Avantage : je vois les top digests et la répartition de la latence presque en temps réel, regroupés sur des requêtes similaires.

-- Activer les consommateurs pour l'historique des états (si possible au moment de l'exécution)
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'.
WHERE NAME IN ('events_statements_history', 'events_statements_history_long') ;

-- Aperçu rapide des groupes de requêtes coûteux
SELECT nom_du_schéma, texte_du_digest, count_star,
       ROUND(sum_timer_wait/1e12, 3) AS total_s,
       ROUND(avg_timer_wait/1e9, 3) AS avg_ms,
       ROUND(max_timer_wait/1e9, 3) AS pmax_ms
FROM sys.statement_analysis
ORDER BY sum_timer_wait DESC
LIMIT 10 ;

Combinaison à partir d'un journal lent (valeurs aberrantes lentes) et d'un schéma de performance (largeur, fréquence) me montre aussi bien des cas isolés que des inducteurs de coûts systématiques. Je compare les deux vues avec les schémas de trafic afin de créer des tâches prioritaires.

EXPLAIN ANALYZE et Optimizer Trace

EXPLAIN ANALYZE (à partir de MySQL 8.0.18) ajoute des temps mesurés aux estimations. Je compare les estimations de lignes avec les valeurs réelles et je détecte les erreurs d'estimation de l'Optimizer. En cas de plans contradictoires, j'analyse la trace de l'Optimizer pour voir pourquoi un index n'a pas été choisi.

-- Plan avec valeurs de mesure
EXPLAIN ANALYSER
SELECT o.id, o.created_at
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'DE' AND o.status = 'paid
ORDER BY o.created_at DESC
LIMIT 50 ;

-- Tracer les décisions de l'optimiseur
SET optimizer_trace='enabled=on" ;
SELECT ... ; -- requête à examiner
SELECT TRACE FROM information_schema.OPTIMIZER_TRACE\G
SET optimizer_trace="enabled=off" ;

RésultatSi les estimations sont très erronées, j'actualise les statistiques (ANALYZE TABLE), je complète les histogrammes ou je reforme les indices/requêtes de manière à ce que la sélectivité intervienne rapidement.

Des modèles de réécriture qui fonctionnent presque toujours

OR sur UNION ALLPlusieurs conditions OR sur différentes colonnes empêchent souvent l'utilisation d'index. Je les sépare en deux requêtes sélectives et j'unifie les résultats si les doublons peuvent être exclus.

-- Avant :
SELECT * FROM t WHERE a = ? OR b = ?

-- Mieux :
(SELECT * FROM t WHERE a = ?)
UNION ALL
(SELECT * FROM t WHERE b = ? AND a  ?) ;

PaginationOFFSET/LIMIT devient cher à mesure que l'OFFSET augmente. Je passe à la pagination Keyset et j'utilise une clé de tri appropriée (idéalement indexée et monotone).

-- Coûteux :
SELECT id, title FROM posts ORDER BY created_at DESC LIMIT 50 OFFSET 5000 ;

-- Mieux (Keyset) :
SELECT id, title
FROM posts
WHERE created_at < :cursor
ORDER BY created_at DESC
LIMIT 50 ;

Indices composites: l'ordre compte. Je trie les colonnes de l'index en fonction de la sélectivité et du modèle de requête (filtre WHERE en premier, puis colonnes de tri). L'objectif est d'obtenir un index de couverture qui évite les filesort et les table-lookups.

Index fonctionnels et index générés dans MySQL 8

Expressions dans WHERE/JOIN bloquent souvent les index. Dans MySQL 8.0, j'indexe des expressions ciblées ou je travaille avec des colonnes générées pour établir la sargabilité. Cela vaut particulièrement la peine avec les CAST pour les métavaleurs numériques ou les champs JSON.

-- Exemple : tri numérique sur une zone de texte
ALTER TABLE product ADD COLUMN prix_num DECIMAL(10,2)
  GENERATED ALWAYS AS (CAST(prix AS DECIMAL(10,2)) STORED ;
CREATE INDEX idx_product_price_num ON product(price_num) ;

-- Query sans CAST et avec index
SELECT * FROM product
WHERE price_num BETWEEN 10 AND 50
ORDER BY price_num ;

Cabinet médicalJe teste si le nouvel index tire vraiment (EXPLAIN) et je mesure l'effet dans le Slow Log. Les colonnes générées aident aussi à filtrer efficacement les préfixes ou les variantes normalisées (LOWER(email)).

Aborder les patterns CMS/Shop de manière encore plus ciblée

Méta-tables (par ex. wp_postmeta) bénéficient d'index combinés sur (post_id, meta_key) ou (meta_key, meta_value). Pour les filtres fréquents sur meta_value_numeric, j'utilise des colonnes générées comme ci-dessus au lieu d'effectuer un CAST dans chaque requête. J'accélère les pages de recherche en supprimant les redondances (dénormalisation light) et en rendant l'accès en lecture convivial pour les index.

-- Typique de WordPress : accès rapide aux métadonnées d'un post
CREATE INDEX idx_postmeta_postid_metakey ON wp_postmeta(post_id, meta_key) ;
CREATE INDEX idx_postmeta_metakey_metavalue ON wp_postmeta(meta_key, meta_value(100)) ;

Checkout-J'optimise les chemins d'accès pour des temps de blocage minimaux : des transactions courtes, uniquement les lignes nécessaires, et des index correspondant exactement aux conditions WHERE utilisées. Pour les rapports, je prévois une agrégation asynchrone (tables intermédiaires) afin que les flux d'utilisateurs ne soient pas ralentis.

Limites du slow log et métriques complémentaires

Beaucoup de petites requêtes rapides ne se remarquent pas dans le journal lent, mais s'additionnent à la charge. C'est pourquoi j'effectue également un suivi du débit (requêtes/sec), des percentiles 95 et 99 et de la proportion de requêtes sans index. Dans les outils Performance Schema ou APM, j'identifie des modèles N+1 que je résous ensuite de manière ciblée par des jointures, des chargements par lots ou une mise en cache.

Échantillonnage est utile lorsque les logs deviennent trop volumineux. J'augmente légèrement long_query_time ou je fixe min_examined_row_limit pour ne saisir que les requêtes pertinentes. Important : toujours noter les modifications afin que les séries temporelles restent comparables.

La méthode de travail : Du constat à l'amélioration durable

Ligne de base d'abord : je m'assure d'un rapport avant (fenêtre temporelle, trafic, configuration). Ensuite, j'optimise une famille de requêtes après l'autre et je compare des fenêtres de temps identiques. Chaque correction est documentée dans le référentiel (quoi ? pourquoi ? valeur mesurée avant/après ?). De cette manière, les succès restent compréhensibles et sûrs en termes de régression.

# Déroulement approximatif (exemple)
1) pt-query-digest --since '7d' slow-query.log > baseline.txt
2) Sélectionner les 3 meilleurs query-digests (selon le temps total)
3) EXPLAIN/EXPLAIN ANALYZE, élaborer des propositions d'index et de réécriture
4) Générer les données de test, simuler le profil de charge
5) Déploiement avec monitoring (valeurs limites plus strictes pour 48h)
6) Rapport de comparaison : pt-query-digest --since '48h' > after.txt
7) Documenter le résultat, planifier la tranche suivante

Stabilité du plan j'observe dans le temps : si les plans changent (nouvelles versions, statistiques modifiées), je vérifie les histogrammes, ANALYZE TABLE et le paysage des index. Je ne place des hints que ponctuellement et de manière documentée, afin de ne pas entraver durablement l'Optimizer.

Résumé en étapes claires

Démarrer Cela signifie : activer le log, définir des valeurs limites raisonnables, collecter les données de la première semaine. Ensuite, je résume avec pt-query-digest, je donne la priorité au temps total et à la variance et je choisis les meilleurs pilotes. J'optimise les requêtes avec EXPLAIN, des conditions sargables et des index appropriés et je contrôle le verrouillage par des transactions plus courtes. Côté serveur, je règle les tampons, les tables temporaires et les stratégies de flush de manière appropriée. Enfin, j'automatise les alertes et répète régulièrement le cycle - la base de données reste ainsi rapide, même si le trafic et le volume de données augmentent.