Journal des requêtes lentes MySQL : identifier les goulots d'étranglement de base de données dans PrestaShop

Pourquoi les performances de la base de données sont importantes dans PrestaShop

PrestaShop est une application gourmande en base de données. Chaque page produit, liste de catégorie, résultat de recherche, mise à jour du panier et étape de commande implique de multiples requêtes de base de données. Une page produit typique peut générer de 50 à 200 requêtes SQL ou plus, selon le nombre de modules installés, la complexité du produit (combinaisons, caractéristiques, pièces jointes) et le thème. Lorsque l'une de ces requêtes s'exécute lentement, la page entière ralentit, et l'effet se multiplie sous charge.

Le défi est d'identifier quelles requêtes sont réellement lentes. Avec des centaines de requêtes par chargement de page, vous ne pouvez pas simplement deviner. Vous avez besoin de données. Le journal des requêtes lentes MySQL est l'outil le plus direct et le plus fiable pour collecter ces données. Il enregistre chaque requête dépassant un seuil de temps que vous définissez, vous donnant une image claire de l'endroit où votre base de données passe le plus de temps.

Ce guide couvre comment activer et configurer le journal des requêtes lentes, comment analyser les résultats, comment interpréter les plans d'exécution de requêtes et comment appliquer les optimisations les plus courantes pour les bases de données PrestaShop.

Activer le journal des requêtes lentes

Le journal des requêtes lentes est une fonctionnalité MySQL qui écrit dans un fichier journal les requêtes dépassant un temps d'exécution spécifié. Il est désactivé par défaut sur la plupart des installations car il ajoute une petite surcharge d'E/S, mais le coût en performances est négligeable comparé à la valeur diagnostique qu'il fournit.

Configuration via my.cnf

Pour activer le journal des requêtes lentes de façon permanente, ajoutez les lignes suivantes à votre fichier de configuration MySQL. Sur la plupart des systèmes Linux, ce fichier se trouve à /etc/mysql/my.cnf, /etc/my.cnf ou dans un répertoire comme /etc/mysql/conf.d/ :

slow_query_log = 1 active la fonctionnalité.

slow_query_log_file = /var/log/mysql/slow-query.log spécifie où le journal est écrit. Assurez-vous que le processus MySQL a les permissions d'écriture sur ce répertoire.

long_query_time = 1 définit le seuil en secondes. Toute requête prenant plus de temps que cette valeur est journalisée. Commencez à 1 seconde pour attraper les pires contrevenants, puis descendez à 0,5 ou même 0,1 seconde à mesure que vous optimisez les pires requêtes et voulez trouver des goulots d'étranglement plus subtils.

log_queries_not_using_indexes = 1 journalise les requêtes n'utilisant aucun index, quelle que soit leur durée. C'est extrêmement utile pour PrestaShop car de nombreux problèmes de performance sont causés par des scans complets de tables sur de grandes tables. Cependant, cela peut générer beaucoup d'entrées de journal sur une boutique active, donc vous voudrez peut-être l'activer temporairement pendant l'analyse et le désactiver après.

Après avoir édité le fichier de configuration, redémarrez MySQL pour que les changements prennent effet.

Activation en cours d'exécution

Vous pouvez également activer le journal des requêtes lentes sans redémarrer MySQL en exécutant des commandes SQL. Connectez-vous à MySQL en tant que root et exécutez :

SET GLOBAL slow_query_log = 'ON';

SET GLOBAL long_query_time = 1;

SET GLOBAL log_queries_not_using_indexes = 1;

Les changements en cours d'exécution prennent effet immédiatement mais ne persistent pas après un redémarrage de MySQL. Cette approche est utile pour des sessions d'analyse temporaires où vous voulez collecter des données pendant une période spécifique puis désactiver la journalisation.

Choisir le bon seuil

La valeur de long_query_time détermine ce qui est journalisé. Un seuil trop élevé signifie que vous manquez des requêtes modérément lentes qui impactent collectivement les performances. Un seuil trop bas inonde le journal d'entrées qui ne sont pas individuellement problématiques.

Pour une analyse initiale, commencez à 1 seconde. Cela capture les requêtes clairement trop lentes. Après les avoir optimisées, abaissez le seuil à 0,5 seconde, puis 0,2 seconde. Sur une base de données PrestaShop bien optimisée, l'objectif est qu'aucune requête ne prenne plus de 0,1 seconde, mais atteindre ce niveau nécessite un travail d'optimisation significatif.

Gardez à l'esprit que le temps d'exécution des requêtes varie avec la charge du serveur. Une requête qui prend 0,3 seconde sous charge normale peut prendre 2 secondes pendant un pic de trafic en raison de la contention CPU, des goulots d'étranglement d'E/S disque ou de la contention de verrouillage. Le journal des requêtes lentes capture les temps d'exécution réels, donc l'analyse des journaux des périodes de trafic de pointe vous donne l'image la plus réaliste.

Analyser le journal des requêtes lentes

Le journal brut des requêtes lentes est un fichier texte avec des entrées qui ressemblent à ceci :

# Time: 2024-03-15T14:22:33.456789Z
# User@Host: prestashop[prestashop] @ localhost []
# Query_time: 3.456123 Lock_time: 0.000234 Rows_sent: 1 Rows_examined: 847293
SET timestamp=1710511353;
SELECT * FROM ps_product WHERE active = 1 AND id_product NOT IN (SELECT id_product FROM ps_category_product WHERE id_category = 2);

Les champs clés sont Query_time (combien de temps la requête a pris), Lock_time (combien de temps elle a attendu un verrou), Rows_sent (combien de lignes ont été retournées) et Rows_examined (combien de lignes MySQL a dû examiner pour trouver le résultat). Une requête qui examine 847 293 lignes pour retourner 1 ligne est un signe clair d'index manquant ou de structure de requête inefficace.

Utiliser mysqldumpslow

Lire le fichier journal brut est impraticable pour les boutiques actives qui génèrent des milliers d'entrées de requêtes lentes. L'outil mysqldumpslow, inclus avec MySQL, agrège et résume les entrées du journal des requêtes lentes. Il regroupe les requêtes identiques (en abstrayant les valeurs spécifiques) et les trie selon divers critères.

Pour trouver les 10 requêtes les plus lentes par temps moyen : mysqldumpslow -s at -t 10 /var/log/mysql/slow-query.log

Pour trouver les requêtes avec le plus de temps d'exécution total : mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

Pour trouver les requêtes ayant examiné le plus de lignes : mysqldumpslow -s r -t 10 /var/log/mysql/slow-query.log

Le flag -s spécifie l'ordre de tri : at pour temps moyen, t pour temps total, c pour nombre (combien de fois la requête est apparue), r pour lignes examinées. Le flag -t limite la sortie aux N premières requêtes.

Le tri le plus utile pour l'analyse initiale est par temps total (-s t), qui montre quelles requêtes consomment le plus de temps de base de données globalement. Une requête qui prend 0,5 seconde mais s'exécute 1000 fois par heure consomme plus de temps total qu'une requête qui prend 5 secondes mais ne s'exécute qu'une fois par heure.

Utiliser pt-query-digest

Pour une analyse plus détaillée, pt-query-digest de Percona Toolkit est l'outil standard de l'industrie. Il fournit des statistiques beaucoup plus détaillées que mysqldumpslow, incluant les distributions en percentiles des temps de requête, l'analyse de variance et les statistiques au niveau des tables.

Utilisation basique : pt-query-digest /var/log/mysql/slow-query.log

La sortie commence par une section de profil classant les requêtes par temps total, similaire à mysqldumpslow mais avec plus de détails. Chaque requête reçoit ensuite une section détaillée montrant les temps d'exécution minimum, maximum, moyen, médian et au 95e percentile, plus la distribution des lignes examinées et envoyées.

Le 95e percentile est particulièrement important pour les performances PrestaShop. Il vous indique le temps d'exécution sous lequel tombent 95 % des exécutions. Si la moyenne est de 0,3 seconde mais le 95e percentile est de 2,5 secondes, vous avez un problème de cohérence : la plupart du temps la requête est acceptable, mais 5 % des utilisateurs connaissent une réponse beaucoup plus lente.

Vous pouvez installer Percona Toolkit sur Debian et Ubuntu avec apt install percona-toolkit ou le télécharger depuis le site web de Percona. L'installation en vaut la peine sur tout serveur où vous exécutez PrestaShop.

Requêtes lentes courantes dans PrestaShop

Certains motifs de requêtes apparaissent de façon répétée dans les journaux de requêtes lentes PrestaShop. Connaître ces motifs vous aide à diagnostiquer les problèmes plus rapidement.

Scans complets de table sur ps_product

Les requêtes contre la table ps_product sans utilisation correcte d'index sont parmi les requêtes lentes les plus courantes. Lorsque votre catalogue dépasse quelques milliers de produits, toute requête qui scanne la table produit entière devient problématique. Cherchez les requêtes avec des clauses WHERE sur des colonnes non indexées, ou des requêtes qui joignent ps_product avec ps_product_lang et ps_product_shop sans utiliser efficacement les clés primaires.

Listes de produits par catégorie avec de nombreux filtres

Lorsque les clients utilisent la navigation à facettes (recherche facettée) pour filtrer les produits par attributs, caractéristiques ou fourchettes de prix, PrestaShop génère des requêtes complexes joignant plusieurs tables. Les tables ps_layered_* utilisées par le module de recherche facettée peuvent devenir des goulots d'étranglement si des index sont manquants ou si le processus d'indexation n'a pas été exécuté récemment.

Requêtes de recherche

La recherche intégrée de PrestaShop utilise les tables ps_search_word et ps_search_index. Sur les boutiques avec de gros catalogues et de nombreux termes de recherche, ces tables deviennent volumineuses et les requêtes contre elles ralentissent. La requête de recherche implique typiquement une opération de texte intégral ou de multiples conditions LIKE, qui sont toutes deux intrinsèquement plus lentes que les recherches par index.

Requêtes de panier et de commandes

Les requêtes qui agrègent les données de panier ou de commandes peuvent être lentes sur les boutiques avec un long historique. Si votre table ps_cart a des millions de lignes (ce qui est courant car PrestaShop crée un nouveau panier pour presque chaque visiteur), les requêtes qui scannent cette table deviennent lentes. Il en va de même pour ps_orders et ps_order_detail sur les boutiques à fort volume.

Requêtes de statistiques et de rapports

Les modules de statistiques du back-office exécutent souvent des requêtes d'agrégation (SUM, COUNT, GROUP BY) sur de grandes tables comme ps_orders, ps_connections et ps_page_viewed. Ces requêtes peuvent être extrêmement lentes car elles scannent de grands ensembles de données. Sur les boutiques fonctionnant depuis des années, ces tables peuvent contenir des millions de lignes, et les requêtes de statistiques qui fonctionnaient parfaitement sur un petit jeu de données prennent maintenant des minutes.

Requêtes générées par les modules

Les modules tiers génèrent fréquemment des requêtes inefficaces car les développeurs de modules testent souvent contre de petits jeux de données. Un module qui fonctionne parfaitement avec 100 produits peut générer des requêtes catastrophiquement lentes avec 10 000 produits. Le journal des requêtes lentes vous aide à identifier quels modules sont responsables car le texte de la requête contient souvent des noms de tables ou des motifs qui pointent vers des modules spécifiques.

Utiliser EXPLAIN pour analyser les requêtes

Une fois que vous avez identifié des requêtes lentes dans le journal, l'étape suivante est de comprendre pourquoi elles sont lentes. L'instruction EXPLAIN vous montre comment MySQL prévoit d'exécuter une requête, incluant quels index il utilise, combien de lignes il prévoit d'examiner et quelles stratégies de jointure il emploie.

Lire la sortie d'EXPLAIN

Exécutez EXPLAIN suivi de la requête lente. La sortie montre une ligne par table dans la requête, avec ces colonnes importantes :

type : Comment MySQL accède à la table. Valeurs du meilleur au pire : system/const (une seule ligne, essentiellement gratuit), eq_ref (une ligne par jointure, utilisant un index unique), ref (plusieurs lignes, utilisant un index non unique), range (scan de plage d'index), index (scan complet d'index), ALL (scan complet de table). Si vous voyez ALL sur une table avec plus de quelques milliers de lignes, c'est presque certainement votre goulot d'étranglement.

key : Quel index MySQL a effectivement choisi pour cette table. Si c'est NULL, aucun index n'est utilisé et MySQL scanne la table entière.

rows : Le nombre estimé de lignes que MySQL doit examiner. C'est une estimation, pas exact, mais cela vous donne un sens de l'échelle. Si la valeur estimée des lignes est proche du nombre total de lignes dans la table, vous avez un scan complet de table.

Extra : Informations supplémentaires sur le plan d'exécution. Surveillez Using filesort (MySQL doit trier les résultats sans index, ce qui est lent pour les grands jeux de données), Using temporary (MySQL crée une table temporaire, souvent pour les opérations GROUP BY ou DISTINCT), et Using where (MySQL filtre les lignes après les avoir lues, ce qui signifie que l'index ne couvre pas entièrement la clause WHERE).

Exemple EXPLAIN

Considérez une requête lente : SELECT p.id_product, pl.name FROM ps_product p LEFT JOIN ps_product_lang pl ON p.id_product = pl.id_product WHERE pl.id_lang = 1 AND p.active = 1 AND p.price > 100 ORDER BY p.date_add DESC LIMIT 20

Exécuter EXPLAIN sur cette requête pourrait montrer que la table ps_product est accédée avec le type ALL (scan complet de table), aucune clé n'est utilisée, et la colonne Extra montre Using where; Using filesort. Cela vous dit que MySQL lit chaque ligne de la table produit, filtre par statut actif et prix, puis trie les résultats par date. Sur une table de 50 000 produits, cela implique la lecture et le tri de milliers de lignes pour n'en retourner que 20.

La correction serait de créer un index composite sur (active, price, date_add) ou de restructurer la requête pour mieux tirer parti des index existants.

Optimisation des index pour PrestaShop

Ajouter les bons index est le moyen le plus efficace d'accélérer les requêtes lentes. Un index permet à MySQL de trouver des lignes sans scanner la table entière, similaire à la façon dont l'index d'un livre vous permet de trouver un sujet sans lire chaque page.

Quand ajouter un index

Ajoutez un index quand EXPLAIN montre un scan complet de table (type ALL) sur une table avec beaucoup de lignes, quand une requête s'exécute fréquemment et apparaît régulièrement dans le journal des requêtes lentes, et quand la clause WHERE, la condition JOIN ou la clause ORDER BY de la requête référence des colonnes qui ne sont actuellement pas indexées.

N'ajoutez pas d'index aveuglément. Chaque index accélère les lectures mais ralentit les écritures (INSERT, UPDATE, DELETE) car MySQL doit mettre à jour l'index à chaque modification de données. PrestaShop effectue de nombreuses écritures (mises à jour du panier, création de commandes, suivi des statistiques), donc une indexation excessive crée ses propres problèmes de performance.

Index composites

Pour les requêtes PrestaShop, les index composites (multi-colonnes) sont souvent plus efficaces que les index à colonne unique. Un index composite sur (id_shop, id_lang, active) permet à MySQL de gérer efficacement les requêtes filtrant sur les trois colonnes. L'ordre des colonnes dans l'index est important : MySQL utilise l'index de gauche à droite, donc la colonne la plus sélective (celle qui filtre le plus de lignes) devrait généralement venir en premier.

L'architecture multiboutique et multilingue de PrestaShop signifie que de nombreuses requêtes incluent des conditions id_shop et id_lang. Ces colonnes apparaissent dans pratiquement chaque requête contre les tables de produits, catégories et CMS. Si vous ajoutez des index personnalisés, inclure ces colonnes est souvent nécessaire pour que l'index soit utile.

Index couvrants

Un index couvrant contient toutes les colonnes dont une requête a besoin, de sorte que MySQL peut satisfaire la requête entière depuis l'index sans lire les données réelles de la table. Cela est indiqué dans EXPLAIN par Using index dans la colonne Extra. Les index couvrants offrent les meilleures performances possibles car lire depuis un index est plus rapide que lire depuis la table elle-même (les index sont plus petits et plus susceptibles de tenir en mémoire).

Ajouts d'index courants pour PrestaShop

Plusieurs index non présents dans une installation PrestaShop par défaut peuvent améliorer significativement les performances sur les grandes boutiques. Ceux-ci incluent des index sur la colonne date_add dans ps_cart et ps_orders pour les requêtes qui filtrent ou trient par date, des index composites sur ps_product_attribute pour les requêtes avec beaucoup de combinaisons, et des index sur les colonnes personnalisées ajoutées par les modules qui exécutent des requêtes fréquentes contre elles.

Avant d'ajouter un index, vérifiez l'amélioration en exécutant la requête lente avec et sans l'index. Utilisez EXPLAIN pour confirmer que MySQL utilise effectivement le nouvel index. Un index inutilisé gaspille de l'espace disque et ralentit les écritures sans fournir aucun bénéfice.

Gestion des connexions et optimisation des requêtes

Pooling de connexions

PrestaShop utilise par défaut une seule connexion de base de données par requête. Chaque processus PHP ouvre une connexion à MySQL, exécute ses requêtes et ferme la connexion à la fin de la requête. Sur les boutiques actives avec de nombreux visiteurs simultanés, cela crée un taux élevé de création et destruction de connexions, ce qui a un coût.

Le paramètre max_connections de MySQL limite le nombre de connexions simultanées autorisées. Si votre boutique manque de connexions, les visiteurs voient des erreurs "Too many connections". La valeur par défaut est souvent 151, ce qui peut être insuffisant pour les boutiques à fort trafic avec de nombreux workers PHP-FPM.

Pour déterminer la bonne valeur, vérifiez la variable de statut Max_used_connections, qui vous indique le nombre maximal de connexions simultanées depuis le démarrage de MySQL. Définissez max_connections à au moins 20 % au-dessus de ce pic pour fournir une marge lors des pics de trafic.

Techniques d'optimisation de requêtes

Au-delà de l'indexation, plusieurs optimisations au niveau des requêtes peuvent améliorer les performances de la base de données PrestaShop :

Éviter SELECT * : Les requêtes sélectionnant toutes les colonnes transfèrent plus de données que nécessaire entre MySQL et PHP. Le cœur de PrestaShop utilise parfois SELECT * par commodité, mais les requêtes personnalisées devraient spécifier uniquement les colonnes nécessaires.

Limiter les sous-requêtes : L'optimiseur de MySQL gère les sous-requêtes moins efficacement que les JOINs dans de nombreux cas. Si vous voyez des requêtes lentes avec des motifs IN (SELECT ...), les réécrire en JOINs améliore souvent les performances. Cela s'applique particulièrement aux requêtes générées par les modules.

Utiliser LIMIT judicieusement : Pour les listes paginées, PrestaShop utilise typiquement LIMIT offset, count. Pour les grands offsets (comme la page 500 d'une liste de produits), cela devient lent car MySQL doit lire et ignorer toutes les lignes jusqu'à l'offset. Une approche plus efficace est la pagination par keyset, où vous filtrez par le dernier identifiant vu au lieu d'utiliser un offset.

Opérations par lots : Les modules qui traitent les produits en boucle exécutent souvent une requête par produit. Réécrire celles-ci en requêtes par lots (utilisant des clauses IN ou des instructions CASE pour les mises à jour) réduit dramatiquement le nombre d'allers-retours vers la base de données.

Surveillance et optimisation continue

L'optimisation de base de données n'est pas une tâche ponctuelle. À mesure que votre catalogue grandit, que les modèles de trafic changent et que vous installez de nouveaux modules, de nouvelles requêtes lentes apparaissent. Établissez une routine pour surveiller les performances de la base de données.

Activez le journal des requêtes lentes de façon permanente avec un seuil raisonnable (0,5 à 1 seconde). Examinez le journal hebdomadairement ou mensuellement en utilisant pt-query-digest. Faites attention aux nouvelles requêtes qui apparaissent dans le journal et aux requêtes existantes dont le temps d'exécution augmente au fil du temps.

Surveillez les métriques de performance clés de MySQL : le taux de succès du buffer pool (devrait être au-dessus de 99 %), le nombre de requêtes lentes par heure, le temps moyen de requête et l'utilisation des connexions. Ces métriques vous donnent un avertissement précoce de dégradation des performances avant qu'elles n'impactent les utilisateurs.

Lorsque vous ajoutez ou mettez à jour des modules, vérifiez s'ils introduisent de nouvelles requêtes lentes. Exécutez la fonctionnalité du module tout en surveillant le journal des requêtes lentes pour détecter les problèmes avant qu'ils n'affectent le trafic de production. Un module qui génère des requêtes efficaces sur une boutique de test avec 50 produits peut créer des goulots d'étranglement sévères sur une boutique de production avec 50 000 produits. Tester avec des données à l'échelle de la production est le seul moyen fiable de vérifier les performances d'un module.

Résumé

Le journal des requêtes lentes MySQL est votre outil le plus précieux pour trouver et corriger les goulots d'étranglement de base de données dans PrestaShop. Activez-le, définissez un seuil approprié et utilisez des outils d'analyse comme mysqldumpslow ou pt-query-digest pour identifier les pires contrevenants. Utilisez EXPLAIN pour comprendre pourquoi des requêtes spécifiques sont lentes, et appliquez des index ciblés pour éliminer les scans complets de table. Surveillez vos performances de base de données en continu, car l'optimisation est un processus continu à mesure que votre boutique grandit. La combinaison d'analyse du journal des requêtes lentes, d'optimisation guidée par EXPLAIN et d'indexation appropriée peut transformer une boutique PrestaShop lente en une boutique qui gère de gros catalogues et un fort trafic avec des chargements de page réactifs.