Bonjour,
J’annonce que cela risque d’être long et trop descriptif, mais j’essaye aussi d’être le plus exhaustif possible – mettre tout ça par écrit va me permettre d’y voir plus clair moi-même.
Voici les détails techniques de la base de données concernée:
> Version MySQL 8
> RAM 2048Mo
> Une base de données, 1715Mo
> Configuration
> event_scheduler OFF
> max_connections 200
> local_infile ON
> tmpdir /dev /shm
> log_bin_trust_function_creators OFF
> autocommit ON
> max_user_connections 200
> wait_timeout 60
> interactive_timeout 60
> sql_mode Legacy SQL Mode: NO_ENGINE_SUBSTITUTION
> max_allowed_packet 16M
> innodb_buffer_pool_size 1024M
Maintenant, le problème :
Je rencontre depuis plusieurs mois des épisodes chroniques, mais sans apparente régularité de saturation du CPU de la base de données de mon site marchand (Prestashop). Certaines séquences de saturation sont si sévères que tout le site devient inaccessible pendant plusieurs minutes voire des heures, et même un redémarrage de la base de données dans le backoffice OVH ne suffit pas à stopper l’incident.
Concrètement, quand un incident se produit, je constate un pic de connexions par minute dans les métriques de la base de données en question dans le backoffice OVH. De moins d’une dizaine de connexions enregistrées par minute en temps normal, le pic monte entre moins et plus d’une centaine. Une saturation réelle se produit quand le pic continue. Si je requête un show processlist lors d’un de ces incidents, je constate symétriquement qu’une centaine de requêtes sont en train d’être exécutées – ou le sont-elles vraiment ?
En terme de contexte et de temporalité, j’ai dit que les incidents semblaient se produire sans aucune régularité apparente, mais aussi sans d’explications contextuelles détectées – je veux dire qu’à l’instant T, je n’ai aucune idée de ce qui cause ces incidents. Par rapport à la temporalité, depuis que je m’occupe de ce site, je n’avais jamais noté ce genre d’incidents avant fin avril 2024. Le site fonctionnait sans aucun incident depuis sa migration sur Prestashop 1.7 en janvier 2023.
Je ne suis pas sûr des dates précises, mais je note deux évènements liés à la base de données qui ont eu lieu autour d’avril 2024. Premier point, entre avril et juin 2024, j’ai opéré un nettoyage de la base de données – réduisant son volume de 50 % environ, en me concentrant sur les tables qui stockaient des données statistiques enregistrées par Prestashop, et redondantes avec Google Analytics. Deuxième point, le changement de version de MySQL qu’OVH a mis en place de la 5.7 à la 8. Cependant cela s’est fait entre fin mai et juillet 2024, or les incidents ont bien commencé en avril 2024.
Fin mai j’avais ouvert un autre topic qui évoquait déjà ce problème, cependant je m’étais concentré sur la question des logs remplis de warnings inutiles, car je pensais qu’ils masquaient les erreurs qui m’auraient permis de diagnostiquer le problème correctement. On m’avait judicieusement informé que ces warnings ne masqueraient pas des erreurs réelles. Une personne m’avait même conseillé plus en détail par message privé. Dans la continuité de ces conseils, j’ai eu l’opportunité de discuter du problème avec un expert de la gestion de site Prestashop – mais pas OVH. Ces deux personnes allaient dans le même sens d’une analyse plus approfondie de mes slow queries.
Après m’être familiarisé avec pt-query-digest, j’ai remarqué que l’une des requêtes les plus lentes était une requête liée à un module particulier de Prestashop, je l’ai donc désactivé. Dans le même mouvement, en utilisant show processlist pendant les périodes de saturation, je remarquais que deux autres requêtes liées à deux autres modules semblaient se répéter de très nombreuses fois pour aucune raison apparente. J’ai également désactivé ces deux modules.
En parallèle de tout cela, j’ai contacté deux fois le support OVH. La première fois, il m’avait été conseillé de réduire les wait_timeout et interactive_timeout de 3600 à 60 – sans m’expliquer les raisons derrière une telle proposition. La personne de la communauté avec qui j’avais échangé en message privé m’avait un peu plus expliqué la logique, mais avait aussi souligné que ça n’aurait probablement peu d’impact. En réponse au deuxième ticket, le support OVH m’a cette fois-ci conseillé de réduire l’innodb_buffer_pool_size de 1024 Mo - soit 50 % de la RAM de la bdd comme recommandé à peu près partout où je me suis documenté - contre 128Mo. Là encore aucune justification n’a été apportée pour un tel changement. Changement que je n’ai pas fait, parce qu’il semble aller contre tout ce que j’ai pu lire à ce sujet – mais peut-être que quelqu’un ici saura expliquer la pertinence d’un tel changement.
Après tous ces changement et un mois d’accalmie environ, les problèmes sont revenus. L’incident d’hier était particulièrement massif. Le redémarrage de la base de données ne l’arrêtaient pas. J’ai enjoint tous les employés de l’entreprise à arrêter d’utiliser le site, et j’ai manuellement mis le site en maintenant pour empêcher les utilisateurs d’y accéder. 5 à 10 minutes environ après avoir mis le site en maintenance, l’incident s’est résolu. Difficile de savoir si cela est totalement lié, car ce ne fut pas immédiat.
Ayant récupéré les slow queries d’hier, je vois que la première requête remontée par le query-digest est une simple requête SELECT COUNT(*) FROM ps_shop LIMIT 1 – retournant le nombre de boutiques que le site Prestashop a – qui s’est exécutée plus de 4500 fois sur la seule journée d’hier, soit plus de 50 % du temps de réponse de toutes les requêtes d’hier. À cet instant, j’ignore l’origine d’une telle requête – elle n’apparaissait pas dans les précédents digest des slow queries. Et j’ignore même pourquoi ou comment elle se serait exécutée autant de fois.
Je ne sais même pas si le fait qu’elle se soit exécutée autant de fois hier indique que c’est la requête problématique – peut-être qu’une autre raison à entraîner un blocage des requêtes et c’est celle-ci qui s’est retrouvée bloquée ? Ayant déjà désactivé trois modules pour enlever des requêtes que je jugeais problématiques, je me demande même si cette approche requête par requête est pertinente. Tout me porte à croire que le problème est ailleurs qu’une ou plusieurs requêtes qui seraient en cause – après tout le site fonctionnait normalement depuis 15 mois avec les mêmes modules et donc les mêmes requêtes. Il va de soi que c’est de toute façon une bonne pratique d’optimiser les requêtes lentes, mais là je ne sais pas. Peut-être que je n’utilise pas pt-query-digest de façon optimale ?
En conclusion, je suis dans une impasse, et je suis à court d’idées et de connaissances pour explorer ce problème encore plus en profondeur.