Cette semaine (comme d’habitude en urgence par nécessité et peu de planification du client) un collègue a migré un Magento 1.9 vers l’un de nos serveurs. En principe, bien que la technologie pour faire fonctionner un Magento 1 soit généralement simple, il y a toujours quelques vérifications, optimisations (beaucoup de développeurs ne savent toujours pas qu’on peut utiliser Redis pour le cache et les sessions) et tests à réaliser.
Après la migration initiale, quelques tests que les pages se chargent correctement et la validation du client que tout est OK, les DNS sont changés et tout le trafic commence à arriver et avec lui la surprise. Le site, en accédant à certaines catégories et de façon presque aléatoire commence à laisser des processus du pool PHP-FPM bloqués et sans réponse au navigateur client.
Mon collègue me contacte pour essayer de trouver une solution. Le client veut seulement que ça fonctionne et son argument et celui de ses développeurs est clair et incontestable, sur leur ancien serveur ça fonctionnait bien…
Et maintenant ?
La configuration du serveur est étudiée pour les performances maximales et avec la dernière version de Debian et avec des centaines de clients profitant d’un environnement stable, sécurisé et rapide. Mais il y a souvent des particularités.
Un rapide coup d’oeil aux processus MySQL montre une requête en état « Sending data » qui par déduction correspond à chacune des pages et processus PHP bloqués et ils restaient dans cet état à l’infini. Évidemment quelque chose se passait avec cette requête qui dans la nouvelle version de MariaDB ne fonctionnait pas comme elle le devrait (oui, bien souvent MariaDB et MySQL font des choses étranges dans les versions récentes qui fonctionnaient bien dans les précédentes).
La version de MariaDB de l’ancien serveur était une 5.5 et l’actuelle une 10.5. Pour effectuer quelques tests, je désactive toutes les optimisations possibles et laisse le serveur MariaDB avec la configuration par défaut (comme l’ancien) mais rien. Mettre la même version de MariaDB sur une version récente de Debian pourrait être possible, soit avec Docker, systemd-nspawn, un chroot mais le VPS lui-même est un conteneur et ne s’entend pas bien avec d’autres systèmes de conteneurs à l’intérieur. Percona et MySQL d’Oracle n’offrent pas de versions anciennes installables pour cette version de Debian et compiler… il faut que ce soit facile à maintenir à l’avenir et qu’il y ait aussi un support de sécurité (la version de MariaDB 5.5 est obsolète) ; cette migration commence à ne plus être du tout rentable.
Avant de continuer sur le chemin d’installer un MySQL plus ancien (qui d’ailleurs ne garantissait pas non plus que ça résoudrait le problème) j’essaie d’analyser la requête pour voir si j’en tire quelque chose de clair :
SELECT count(DISTINCT e.entity_id) FROM `sales_flat_order_item` AS `order_items`
INNER JOIN `sales_flat_order` AS `order` ON `order`.entity_id = order_items.order_id AND `order`.state <> 'canceled'
LEFT JOIN `catalog_product_entity` AS `e` ON (e.type_id NOT IN ('grouped', 'configurable', 'bundle')) AND e.entity_id = order_items.product_id AND e.entity_type_id = 4
INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=6 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '343' WHERE (parent_item_id IS NULL);
J’ai eu dans le passé des batailles avec des requêtes complexes où généralement le manque d’un index clé (incompréhensible qu’il n’ait pas été ajouté par le développeur) générait une dégradation considérable des performances et le simple fait de l’ajouter faisait varier le temps d’exécution de la requête de plus d’1 minute à moins d’une seconde, ça multiplié avec de multiples visites simultanées sur un e-commerce sur certaines pages, c’est énorme.
Revenons à la requête, je commence à analyser les tables impliquées, les champs qui interviennent dans les JOIN et les index correspondants et je vois qu’un possible candidat manque : order_items.product_id. J’ajoute donc un index pour cette colonne.
ALTER TABLE `sales_flat_order_item` ADD KEY `product_id` (`product_id`);
Et problème résolu, plus de blocage avec la requête qui devient instantanée.
C’est ainsi qu’un simple index sur une table peut vous sauver la journée.
La raison du problème et pourquoi avec MariaDB 5.5 ça fonctionnait bien et pas avec la 10.5 ? Aucune idée, il peut y avoir un autre facteur que je n’ai pas envisagé ou une erreur dans l’importation de la base de données. Il y a sûrement une explication mais cela nécessiterait beaucoup de temps d’analyse et de tests et l’index a résolu le problème.