Requêtes MySQL lentes PrestaShop : activer slow query log

Table des matières :

Requêtes MySQL lentes PrestaShop : ce que le slow query log révèle vraiment

Sur une boutique PrestaShop (8.1.x à 9.1.x) qui tourne en PHP 8.2/8.3, les « lenteurs MySQL » ne se manifestent pas forcément par des erreurs explicites. Les symptômes typiques côté front sont un TTFB qui explose (p. ex. > 800 ms) sur des pages catalogue, des timeouts sporadiques sur le checkout, et des pics CPU MySQL corrélés aux crawlers ou à la navigation à facettes. Côté back-office, c’est souvent l’indexation, les grilles produits, ou les exports qui font apparaître des requêtes longues et/ou très gourmandes en lignes examinées.

Le problème, c’est que le cœur PrestaShop mélange encore beaucoup de SQL « legacy » (via Db::getInstance()), des requêtes très jointées (lang, shops, groups, stock, prix spécifiques), et des patterns propices aux scans (filtres dynamiques, tris, recherche). Tant que vous n’avez pas une trace exploitable, vous finissez par « optimiser au pif » (ajout d’index au hasard, augmentation de ressources serveur), ce qui fait perdre du temps et masque les régressions introduites par des modules.

Le slow query log sert précisément à sortir de l’opinion : il enregistre les requêtes au-delà d’un certain seuil (temps d’exécution et/ou lignes examinées), avec des métriques permettant de trier par impact réel.

Concrètement, une entrée de slow log ressemble souvent à ceci (champs variables selon versions/config) :

Query_time : durée totale côté serveur (pas « le temps ressenti navigateur »).
Lock_time : temps passé en verrous (utile pour repérer contentions).
Rows_sent vs Rows_examined : le ratio est une alarme classique (beaucoup lu, peu renvoyé = filtre inefficace / index absent).
SET timestamp=...; : clé pour corréler avec un pic TTFB, un crawl, ou un job CRON.

L’objectif n’est pas de « chasser la requête la plus lente » une fois, mais de repérer ce qui coûte le plus au cumulé (temps total), ce qui scanne le plus (I/O), et ce qui est le plus fréquent (effet multiplicatif).

Pré-requis, risques et stratégie (prod vs staging)

Côté versions, les réglages ci-dessous visent MySQL 8.0+ et MariaDB 10.6+ (fréquent en hébergement). Le principe est identique, mais certaines variables diffèrent, et MySQL propose SET PERSIST (persistant sur disque) alors que MariaDB reste plus « traditionnel » (config + SET GLOBAL). Pré-requis minimal : accès root (ou au moins à la conf mysqld), possibilité de redémarrer MySQL, et accès aux logs système.

Avant même d’activer quoi que ce soit, fixez 3 points « opérationnels » (c’est souvent là que les audits partent en vrille) :

Fenêtre de collecte : vous voulez un créneau représentatif (trafic réel, facettes, bots, imports, cron). Une collecte de 10 minutes « à vide » ne sert à rien ; une collecte de 7 jours sans rotation devient un risque.
Horodatage et corrélation : vérifiez le fuseau (VM, conteneur, DB) pour recoller au monitoring web. Si vous exploitez des dashboards, standardiser en UTC simplifie beaucoup les corrélations.
Versionnement : notez la version PrestaShop + thème + modules modifiés au moment de la collecte. Sans ça, impossible d’attribuer une régression après déploiement.

En production, activer le slow query log a un coût, mais il est généralement acceptable si vous restez raisonnable sur les options. Le risque réel vient surtout de deux points : (1) le volume disque (si seuil trop bas) et (2) l’exposition de données sensibles dans le SQL (emails, IDs, parfois adresses selon les requêtes). Sur une boutique soumise au RGPD, traitez le slow log comme un artefact sensible : permissions strictes, rétention courte, accès limité, et chiffrement au repos si vous exportez vers une stack d’observabilité (en pratique : pas de partage « large » sur un S3/Blob sans politiques et audit).

Stratégie recommandée (pragmatique) :

Phase 1 (24–48 h) : activer en prod avec long_query_time assez haut (0.5–1.0 s) + min_examined_row_limit pour éviter le bruit.
Phase 2 (fenêtre courte) : descendre à 0.2–0.3 s pendant 30–60 minutes sur un trafic représentatif (soldes, campagne, crawl), puis remonter.
Phase 3 : analyser et corriger (index, réécriture, cache). Pour l’optimisation d’index, appuyez-vous sur une méthode reproductible (voir Index MySQL : optimiser WHERE, JOIN et ORDER BY avec EXPLAIN).

Astuce de terrain : pour mesurer l’effet « avant/après », relevez aussi le compteur serveur :

SHOW GLOBAL STATUS LIKE 'Slow_queries';

Vous pourrez comparer le rythme d’apparition des requêtes lentes entre deux déploiements ou deux périodes (à seuil identique).

Activer le slow query log sur MySQL 8 (fichier, variables dynamiques, Docker)

Sur MySQL 8, le plus simple et le plus robuste reste log en fichier (plutôt que TABLE) pour éviter de faire grossir la table mysql.slow_log et de dégrader les perfs par écriture dans InnoDB. Dans un fichier de conf (selon distro : /etc/mysql/mysql.conf.d/mysqld.cnf, /etc/my.cnf, etc.), ajoutez sous [mysqld] :

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 0.5
min_examined_row_limit = 1000
log_slow_admin_statements = 0
log_queries_not_using_indexes = 0
log_output = FILE

Points d’attention (souvent oubliés) :

Droits et répertoire : assurez-vous que /var/log/mysql/ existe et que l’utilisateur MySQL peut écrire.
SELinux/AppArmor (selon distributions) : un MySQL « confiné » peut refuser l’écriture dans un chemin non autorisé. Si le log ne se crée pas, cherchez d’abord là.
log_queries_not_using_indexes : tentant, mais souvent trop bruyant sur PrestaShop (requêtes petites mais sans index, sous-requêtes, tables temporaires). Gardez-le désactivé au départ.

Après redémarrage (systemctl restart mysql), vérifiez :

SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'min_examined_row_limit';
SHOW VARIABLES LIKE 'log_output';

Sur MySQL 8, vous pouvez aussi activer sans redémarrage avec SET GLOBAL (temporaire) ou SET PERSIST (persistant) :

SET PERSIST slow_query_log = ON;
SET PERSIST long_query_time = 0.5;
SET PERSIST min_examined_row_limit = 1000;

Différence importante : SET GLOBAL retombe au redémarrage ; SET PERSIST écrit dans mysqld-auto.cnf (pratique, mais à tracer dans vos runbooks et IaC).

En environnement conteneurisé (Docker / Kubernetes), gardez en tête que le fichier de log doit vivre sur un volume persistant. Si votre MySQL est packagé dans une image, montez /var/log/mysql sur un volume, ou redirigez le log vers une destination collectée.

Exemple minimal en docker-compose.yml (illustratif) :

services:
  mysql:
    image: mysql:8.0
    volumes:
      - mysql-data:/var/lib/mysql
      - ./mysql-logs:/var/log/mysql
      - ./my.cnf:/etc/mysql/conf.d/my.cnf:ro
volumes:
  mysql-data:

Cela évite le piège classique : « j’ai activé le slow log, puis j’ai recréé le conteneur et j’ai perdu la preuve ».

MariaDB 10.6+ et hébergements gérés : mêmes objectifs, options différentes

Sur MariaDB, la configuration est similaire, mais vous trouverez des options de verbosité/filtrage spécifiques selon versions. Exemple minimal dans 50-server.cnf ou my.cnf :

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 0.5
min_examined_row_limit = 1000
log_output = FILE

L’activation à chaud se fait généralement en SET GLOBAL :

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL min_examined_row_limit = 1000;

Selon votre contexte, deux réglages MariaDB peuvent être utiles (sans en abuser) :

Réduire le bruit : si le log devient énorme, remontez long_query_time et/ou augmentez min_examined_row_limit.
Limiter l’impact opérationnel : certains environnements très chargés préfèrent une collecte courte mais « dense » (phase 2) plutôt qu’un slow log permanent.

Sur hébergement mutualisé ou managé (cPanel, Plesk, DBaaS), vous n’avez parfois pas accès au fichier de conf serveur ni aux fichiers logs. Dans ce cas, cherchez :

Une option « slow query log » dans le panel (rare mais possible).
Un accès aux logs via l’interface du provider (export / viewer).
À défaut : bascule temporaire en environnement maîtrisé (staging proche prod) et reproduisez la charge (tests de charge + crawl simulé).

Si vous avez des erreurs ou limitations côté provider (droits, quotas, I/O), c’est rarement un « bug PrestaShop » ; c’est un indicateur de mismatch d’hébergement pour votre charge (voir Hébergement e-commerce : mutualisé, VPS, cloud ou SaaS comparés et Erreurs base de données OVHcloud : diagnostic et solutions d’hébergement Web).

Lire, agréger et prioriser : mysqldumpslow, pt-query-digest, EXPLAIN ANALYZE

Une fois le slow query log activé, l’erreur classique est de lire le fichier « à la main » et de partir sur la première requête qui a l’air moche. Ce qu’il vous faut, c’est une agrégation par signature (digest) : sinon vous corrigez une instance, pas le problème récurrent.

Pour un tri rapide :

mysqldumpslow -t 20 -s t /var/log/mysql/mysql-slow.log
mysqldumpslow -t 20 -s r /var/log/mysql/mysql-slow.log

-s t : top par temps total.
-s r : top par nombre de lignes examinées.

Pour une analyse sérieuse, Percona Toolkit (pt-query-digest) reste la référence de terrain, parce qu’il sort des percentiles, du temps cumulé, et des exemples paramétrés. Exemple :

pt-query-digest /var/log/mysql/mysql-slow.log > /root/slow-report.txt

Checklist « lecture utile » (rapide, mais efficace) :

Priorisez les requêtes par temps cumulé, pas seulement par Query_time max.
Regardez le ratio Rows_examined / Rows_sent : quand il explose, l’optimisation passe souvent par un index ou une réécriture (ou un tri évitable).
Identifiez la source : core PrestaShop, module, ou requête « administrative » (export, indexation, stats).
Repérez les pics (horaires) : si tout se produit à H+00, suspectez cron, indexation, ou flux.

Ensuite, vous passez en mode « preuve » : EXPLAIN pour le plan, et sur MySQL 8, EXPLAIN ANALYZE pour l’exécution réelle (temps par opérateur). Beaucoup de lenteurs PrestaShop ne viennent pas d’un manque de CPU, mais de mauvais accès (full scan) provoqué par :

index composite manquant (filtre + tri),
colonnes non sélectives en tête d’index,
conditions OR ou LIKE '%...%' qui cassent les index,
jointures sur des colonnes non indexées dans des tables de modules.

Sur l’optimisation d’index et l’interprétation des plans, ne réinventez pas la roue : voir Index MySQL : optimiser WHERE, JOIN et ORDER BY avec EXPLAIN et, pour la vision globale (PHP-FPM + OPcache + MySQL), Performance PrestaShop : benchmarks et optimisation PHP-FPM, OPCache, MySQL.

Cas concrets PrestaShop : requêtes lentes typiques et remédiations réalistes

Navigation à facettes / filtres catalogue : c’est l’une des sources les plus fréquentes de requêtes lentes, surtout si un module ajoute des jointures ou si les index ne sont plus alignés avec le schéma. Vous verrez souvent des requêtes qui explosent en rows_examined à cause de filtres cumulés + tri (prix, disponibilité, nouveauté) et de groupements. Ici, le slow log vous dit immédiatement si le problème est un « gros JOIN » (catalogue) ou un effet de bord (module qui recalcule des compteurs, requête répétée).

Mini-scénario fréquent (e-commerce B2C en France, gros catalogue) : une campagne d’acquisition génère un trafic mobile important sur des pages catégories filtrées (« taille + couleur + prix »). Le slow log remonte une signature dominante dont le Rows_examined est très élevé, alors que Rows_sent reste bas (quelques dizaines de produits). Souvent, les corrections efficaces sont :

index composite qui suit réellement la clause WHERE et l’ORDER BY principal (plutôt qu’un index mono-colonne),
limitation des tris coûteux (ex. tri par prix combiné à des conditions complexes),
réduction des jointures optionnelles (données non indispensables au listing).

Remédiation : (1) vérifier les index existants sur les tables impliquées, (2) réduire les ORDER BY coûteux, (3) désactiver/limiter les filtres qui forcent des scans, et (4) mettre en place du cache là où c’est cohérent (pages catégories, fragments). Si vous avez déjà une stratégie cache serveur, consolidez-la avec Cache PrestaShop : Varnish, Redis, Memcached et OPcache côté serveur et, si vous utilisez Redis, Redis PHP : activer l’extension, choisir les bases et surveiller l’usage.

Recherche interne / autocomplete : sur gros catalogues, la recherche peut générer des requêtes lentes via des jointures sur les tables de mots, ou via un moteur SQL mal adapté à l’intent (typiquement du LIKE ou des scores recalculés). Le slow log vous aide à décider si vous devez optimiser le schéma SQL… ou sortir le use case vers un moteur dédié.

Signal faible mais révélateur : vous voyez dans le slow log des requêtes de recherche modestes (0.3–0.7 s) mais extrêmement fréquentes, souvent corrélées à l’autocomplete. Dans ce cas, même une petite amélioration (cache court, limitation de fréquence, index) peut réduire fortement la charge globale ; mais quand le catalogue devient très volumineux, la réponse réaliste est souvent d’intégrer un moteur spécialisé (voir Recherche interne PrestaShop : intégrer Meilisearch pour booster la conversion ou PrestaShop ElasticSearch : accélérer la recherche produit sur grands catalogues).

Checkout / panier / prix spécifiques : les lenteurs sont souvent moins visibles, mais plus critiques (conversion). On retrouve des requêtes sur règles panier, prix spécifiques, transporteurs, taxes, stock, parfois répétées en boucle (pattern N+1 côté code).

Le slow log permet d’objectiver : est-ce une requête unique à 2 secondes, ou 200 requêtes à 80 ms ? La remédiation n’est pas la même :

requête unique lente : index, réduction du périmètre, réécriture SQL ;
rafale de requêtes « moyennes » : caching, réduction d’appels redondants, correction de boucles (souvent introduites par surcharge/module).

Pour sécuriser les modifications dans le tunnel, gardez une checklist de contrôle (voir Contrôle PrestaShop post-modification : checklist tunnel de commande et règles panier).

Industrialiser : rotation, corrélation avec la perf web, et surveillance

Un slow query log sans rotation, c’est un incident disque programmé. Configurez logrotate (Linux) avec compression et rétention courte. Exemple /etc/logrotate.d/mysql-slow :

/var/log/mysql/mysql-slow.log {
  daily
  rotate 7
  compress
  delaycompress
  missingok
  notifempty
  create 640 mysql adm
  postrotate
    /usr/bin/mysqladmin flush-logs
  endscript
}

Deux ajustements utiles selon contexte :

Si vous avez des pics (soldes, TV, influence), passez temporairement en size (ex. rotation à 200–500 Mo) pour éviter de remplir le disque avant la rotation quotidienne.
En conteneurs, la rotation peut être gérée côté nœud (log driver) ou via un agent ; assurez-vous que la responsabilité est claire, sinon personne ne la fait.

L’autre point clé : corréler les requêtes lentes avec ce que voit l’utilisateur. Si votre TTFB dérive, vous devez pouvoir dire : « c’est MySQL » vs « c’est PHP-FPM » vs « c’est le cache qui bypass ». Pour ça, le slow log est un signal, pas une solution unique. Alignez-le avec vos métriques de stack (CPU, I/O, pool PHP-FPM, hit ratio cache) et votre monitoring applicatif (voir Surveillance PrestaShop : tableau de bord, seuils et réduction des fausses alertes et PrestaShop performance : monitoring, tests de charge et runbooks soldes).

Enfin, si vous voulez exploiter les slow logs à l’échelle (multi-boutiques, multi-serveurs), shippez-les vers une stack centralisée (ELK/OpenSearch, Loki, etc.) et indexez par digest + tags (env, shop, version, module). Le slow log est du texte : il se prête très bien à un pipeline Logstash/ingest (voir Logstash : plugins Input, Filter, Output pour Elasticsearch et Kafka). L’objectif est simple : transformer « on a des lenteurs » en une liste priorisée de requêtes, avec propriétaires (core/module), plan d’action, et validation post-fix (temps p95, rows_examined, et impact sur TTFB — cf. TTFB PrestaShop : réduire le Time To First Byte sous 200 ms).

Requêtes MySQL lentes PrestaShop : activer slow query log

Requêtes MySQL lentes PrestaShop : ce que le slow query log révèle vraiment

Pré-requis, risques et stratégie (prod vs staging)

Activer le slow query log sur MySQL 8 (fichier, variables dynamiques, Docker)

MariaDB 10.6+ et hébergements gérés : mêmes objectifs, options différentes

Lire, agréger et prioriser : mysqldumpslow, pt-query-digest, EXPLAIN ANALYZE

Cas concrets PrestaShop : requêtes lentes typiques et remédiations réalistes

Industrialiser : rotation, corrélation avec la perf web, et surveillance

À lire aussi

Optimisation PrestaShop : réduire les requêtes SQL et les hooks

PrestaShop 9 : module reCAPTCHA gratuit et alternatives anti-spam

PrestaShop configuration : éviter l’état panier dans la table ps_configuration