Requêtes très longues causant des 504 Gateway Timeout

[PrestaPaul]

Bonjour,

Je suis développeur web et nouveau sur Prestashop et j'avais l'habitude de travailler avec Magento 2 sans rencontrer ce genre de problèmes malgré de grosses bases de données (2M clients, gros volume de commande, etc) mais depuis que je suis passé sur un nouveau projet Prestashop bien plus petit (300k clients, volume de commande correct) je ne parviens pas à développer correctement.

J'ai essayé de comprendre pourquoi certaines actions prenaient 3 ans (voire finissaient par donner une erreur 504) et chaque fois que je regarde la requête, ça implique les orders ou invoices.

J'ai l'impression que Prestashop tient à faire des requêtes vers ces tables pour un oui ou pour un non, ce que je trouve être une très mauvaise pratique. Avec les cross-sells par exemple, mes produits mettaient une plombe à charger car il fait une requête sur les orders pour afficher un produit (non sérieusement, il n'y a que moi qui trouve que c'est une très mauvaise pratique ?)

J'ai plus de 350k orders/invoices, je comprends que c'est beaucoup mais je ne comprends pas pourquoi les requêtes sont aussi longues à s'exécuter malgré tout ? Quand j'essaie d'afficher les ventes sur l'année en cours sur le dashboard du backoffice, j'ai une erreur 504 par exemple.

Quand je suis sur la page des catégories dans le backoffice je finis par avoir une 504 également (exemple ci-joint avec une requête de 841 secondes que j'ai préféré KILL à partir de 1200 secondes...)

--

Au delà de l'aspect surprenant pour moi de faire des requêtes à rallonge partout, qui est peut-être dû au fait que je ne suis pas habitué à Prestashop, rencontrez-vous ce genre de "lenteurs" aussi ou est-ce dû à une mauvaise configuration de mon environnement ? Je précise d'ailleurs que ces longues requêtes empêchent mes autres pages de charger (est-ce dû à Apache ? je travaille avec Nginx d'habitude)

J'utilise une VM Ubuntu 22.04 jammy avec VirtualBox (8Go de RAM, 80Go de disque dur) avec

• PHP 7.2
• MariaDB v15.1 Distrib 10.6.7
• Apache 2.4.52
• Prestashop 1.7.8.6

Quelqu'un pourrait-il éclairer ma lanterne au sujet de ces requêtes à rallonge ? Rencontrez-vous des problèmes similaires sur des projets avec de nombreuses données ? (invoices, orders, top_category)

Important à souligner: Je viens de rentrer dans une entreprise qui utilisait Prestashop 1.7.2.4 et qui a utilisé toutes les bad practices de développement possible (rajout de colonnes dans les tables natives, réécriture dans le core, etc) et j'essaie de "corriger" les données et de les importer sur une version clean de prestashop 1.7.8.6 ; je n'ai donc aucun module (j'en ai même désinstallé 2 "Commandes et CA" qui n'a pas désactivé les stats du dashboard comme je le pensais et "Vente croisée" pour désactiver les cross-sells qui ralentissait mes produits) et aucun thème pour le moment, j'ai juste réimporté les données principales en retirant les colonnes en trop et en essayant de corriger les problèmes d'import (customers, products, categories, sales).

Merci d'avance ! 😅

[AfterGlow93]

On 6/15/2022 at 10:04 AM, PrestaPaul said:

Bonjour,

Je suis développeur web et nouveau sur Prestashop et j'avais l'habitude de travailler avec Magento 2 sans rencontrer ce genre de problèmes malgré de grosses bases de données (2M clients, gros volume de commande, etc) mais depuis que je suis passé sur un nouveau projet Prestashop bien plus petit (300k clients, volume de commande correct) je ne parviens pas à développer correctement.

J'ai essayé de comprendre pourquoi certaines actions prenaient 3 ans (voire finissaient par donner une erreur 504) et chaque fois que je regarde la requête, ça implique les orders ou invoices.

J'ai l'impression que Prestashop tient à faire des requêtes vers ces tables pour un oui ou pour un non, ce que je trouve être une très mauvaise pratique. Avec les cross-sells par exemple, mes produits mettaient une plombe à charger car il fait une requête sur les orders pour afficher un produit (non sérieusement, il n'y a que moi qui trouve que c'est une très mauvaise pratique ?)

J'ai plus de 350k orders/invoices, je comprends que c'est beaucoup mais je ne comprends pas pourquoi les requêtes sont aussi longues à s'exécuter malgré tout ? Quand j'essaie d'afficher les ventes sur l'année en cours sur le dashboard du backoffice, j'ai une erreur 504 par exemple.

Quand je suis sur la page des catégories dans le backoffice je finis par avoir une 504 également (exemple ci-joint avec une requête de 841 secondes que j'ai préféré KILL à partir de 1200 secondes...)

--

Au delà de l'aspect surprenant pour moi de faire des requêtes à rallonge partout, qui est peut-être dû au fait que je ne suis pas habitué à Prestashop, rencontrez-vous ce genre de "lenteurs" aussi ou est-ce dû à une mauvaise configuration de mon environnement ? Je précise d'ailleurs que ces longues requêtes empêchent mes autres pages de charger (est-ce dû à Apache ? je travaille avec Nginx d'habitude)

J'utilise une VM Ubuntu 22.04 jammy avec VirtualBox (8Go de RAM, 80Go de disque dur) avec

• PHP 7.2
• MariaDB v15.1 Distrib 10.6.7
• Apache 2.4.52
• Prestashop 1.7.8.6

Quelqu'un pourrait-il éclairer ma lanterne au sujet de ces requêtes à rallonge ? Rencontrez-vous des problèmes similaires sur des projets avec de nombreuses données ? (invoices, orders, top_category)

Important à souligner: Je viens de rentrer dans une entreprise qui utilisait Prestashop 1.7.2.4 et qui a utilisé toutes les bad practices de développement possible (rajout de colonnes dans les tables natives, réécriture dans le core, etc) et j'essaie de "corriger" les données et de les importer sur une version clean de prestashop 1.7.8.6 ; je n'ai donc aucun module (j'en ai même désinstallé 2 "Commandes et CA" qui n'a pas désactivé les stats du dashboard comme je le pensais et "Vente croisée" pour désactiver les cross-sells qui ralentissait mes produits) et aucun thème pour le moment, j'ai juste réimporté les données principales en retirant les colonnes en trop et en essayant de corriger les problèmes d'import (customers, products, categories, sales).

Merci d'avance ! 😅

Bonjour, 

Il serait judicieux d'effectuer un profilling des requêtes, puis exécuter la requête en direct en utilisant un EXPLAIN.

Sur mon projet pro actuel, une boutique de pièces détachées avec +de 2 millions d'articles, 2000 catégories, aucune requête ne dépasse les 2-3 secondes.

Il y'a forcément un problème quelque part et il faut prendre son mal en patience et chercher le coupable (parfois, il suffit d'un index manquant dans une colonne de table et c'est le ralentissement assuré).

Magento2 est derrière en termes de performances comparé à PS, ce qui est d'autant plus étonnant.

Ceci dit, s'il y a eu des modifs du Core ... Bon courage ! Il faut tout reprendre à zéro

 

 

[PrestaPaul]

Le 17/06/2022 à 8:24 PM, AfterGlow93 a dit :

Bonjour, 

Il serait judicieux d'effectuer un profilling des requêtes, puis exécuter la requête en direct en utilisant un EXPLAIN.

Sur mon projet pro actuel, une boutique de pièces détachées avec +de 2 millions d'articles, 2000 catégories, aucune requête ne dépasse les 2-3 secondes.

Il y'a forcément un problème quelque part et il faut prendre son mal en patience et chercher le coupable (parfois, il suffit d'un index manquant dans une colonne de table et c'est le ralentissement assuré).

Magento2 est derrière en termes de performances comparé à PS, ce qui est d'autant plus étonnant.

Ceci dit, s'il y a eu des modifs du Core ... Bon courage ! Il faut tout reprendre à zéro

 

 

Justement, suite à ces modifs du core, j'ai décidé de partir sur un projet neuf. Le problème c'est que comme ils ont rajouté des colonnes et modifié les contraintes des tables, je me retrouve avec des données difficiles à travailler (ean > 13 caractères pour les produits ; reference > 9 caractères pour les commandes, etc).

Du coup j'essaie effectivement de déterminer ce qui peut ralentir les requêtes mais au niveau des indexes de la BDD il ne me semble pas qu'il y ait de manque vu que je me suis contenté de faire des INSERT depuis l'autre table en gardant la structure des tables de la nouvelle MàJ de prestashop.

Je vais voir ce que peut me raconter EXPLAIN et continuer mon investigation et je reviendrai commenter si je trouve une solution ou si j'ai plus de pistes à creuser 😄

Merci !

[PrestaPaul]

@AfterGlow93 J'ai essayé de repartir sur une base propre en réimportant mes données table par table (peut-être est-ce long parce que j'oublie des tables ?) Voici un exemple quand j'essaie de charger la liste des produits dans le backoffice. Je suis allé chercher la requête en question:

SELECT SQL_CALC_FOUND_ROWS
    p.`id_product` AS `id_product`,
    p.`reference` AS `reference`,
    sa.`price` AS `price`,
    p.`id_shop_default` AS `id_shop_default`,
    p.`is_virtual` AS `is_virtual`,
    pl.`name` AS `name`,
    pl.`link_rewrite` AS `link_rewrite`,
    sa.`active` AS `active`,
    shop.`name` AS `shopname`,
    image_shop.`id_image` AS `id_image`,
    cl.`name` AS `name_category`,
    0 AS `price_final`,
    pd.`nb_downloadable` AS `nb_downloadable`,
    sav.`quantity` AS `sav_quantity`,
    IF(sav.`quantity` <= 0, 1, 0) AS `badge_danger`
FROM
    `product` p
        LEFT JOIN
    `product_lang` pl ON (pl.`id_product` = p.`id_product`
        AND pl.`id_lang` = 1
        AND pl.`id_shop` = 1)
        LEFT JOIN
    `stock_available` sav ON (sav.`id_product` = p.`id_product`
        AND sav.`id_product_attribute` = 0
        AND sav.id_shop = 1
        AND sav.id_shop_group = 0)
        JOIN
    `product_shop` sa ON (p.`id_product` = sa.`id_product`
        AND sa.id_shop = 1)
        LEFT JOIN
    `category_lang` cl ON (sa.`id_category_default` = cl.`id_category`
        AND cl.`id_lang` = 1
        AND cl.id_shop = 1)
        LEFT JOIN
    `category` c ON (c.`id_category` = cl.`id_category`)
        LEFT JOIN
    `shop` shop ON (shop.id_shop = 1)
        LEFT JOIN
    `image_shop` image_shop ON (image_shop.`id_product` = p.`id_product`
        AND image_shop.`cover` = 1
        AND image_shop.id_shop = 1)
        LEFT JOIN
    `image` i ON (i.`id_image` = image_shop.`id_image`)
        LEFT JOIN
    `product_download` pd ON (pd.`id_product` = p.`id_product`)
WHERE
    (1 AND state = 1)
ORDER BY `id_product` DESC
LIMIT 0 , 20

Voici le résultat du EXPLAIN:

La requête met 85 secondes à retourner les 20 résultats attendus 😅 (et quand j'essaie de charger une fiche produit avec le module cross sells, je me prends un 504 gateway, la requête fait fait des jointures entre order_detail, product, product_shop, product_attribute, product_attribute_shop, stock_available, product_lang category_lang, image et il y a un where sur 4000 order_id 🤣 ; quand j'enlève le module cross sells j'ai plus de problème)

Une idée ? 👀

Edit: Quand je retire les lignes qui font des jointures sur image et image_shop, la requête s'exécute en 0,015 secondes 🤔 J'ai vérifié, mais les indexes existent bien sur les tables concernées...

Edited June 21, 2022 by PrestaPaul (see edit history)

[PrestaPaul]

Encore un exemple ; dans l'onglet "Catégories" du backoffice, il y a aussi une requête qui va chercher la catégorie avec les meilleures ventes sur le dernier mois glissant

SELECT 
    ca.`id_category`
FROM
    `category` ca
        LEFT JOIN
    `category_product` capr ON ca.`id_category` = capr.`id_category`
        LEFT JOIN
    (SELECT 
        pr.`id_product`, t.`totalPriceSold`
    FROM
        `product` pr
    LEFT JOIN (SELECT 
        pr.`id_product`,
            IFNULL(SUM(cp.`product_quantity`), 0) AS totalQuantitySold,
            IFNULL(SUM(cp.`unit_price_tax_excl` * cp.`product_quantity`), 0) / o.conversion_rate AS totalPriceSold
    FROM
        `product` pr
    LEFT OUTER JOIN `order_detail` cp ON pr.`id_product` = cp.`product_id`
    LEFT JOIN `orders` o ON o.`id_order` = cp.`id_order`
    WHERE
        o.invoice_date BETWEEN '2022-05-21 00:00:00' AND '2022-06-21 23:59:59'
    GROUP BY pr.`id_product`) t ON t.`id_product` = pr.`id_product`) t ON t.`id_product` = capr.`id_product`
WHERE
    ca.`level_depth` > 1
GROUP BY ca.`id_category`
ORDER BY SUM(t.`totalPriceSold`) DESC
LIMIT 1

Je l'ai lancé il y a plus de 10 minutes et il n'arrive toujours pas à retourner quoi que ce soit 😅 Et voici le résultat de l'EXPLAIN:

[AfterGlow93]

3 hours ago, PrestaPaul said:

@AfterGlow93 J'ai essayé de repartir sur une base propre en réimportant mes données table par table (peut-être est-ce long parce que j'oublie des tables ?) Voici un exemple quand j'essaie de charger la liste des produits dans le backoffice. Je suis allé chercher la requête en question:

SELECT SQL_CALC_FOUND_ROWS
    p.`id_product` AS `id_product`,
    p.`reference` AS `reference`,
    sa.`price` AS `price`,
    p.`id_shop_default` AS `id_shop_default`,
    p.`is_virtual` AS `is_virtual`,
    pl.`name` AS `name`,
    pl.`link_rewrite` AS `link_rewrite`,
    sa.`active` AS `active`,
    shop.`name` AS `shopname`,
    image_shop.`id_image` AS `id_image`,
    cl.`name` AS `name_category`,
    0 AS `price_final`,
    pd.`nb_downloadable` AS `nb_downloadable`,
    sav.`quantity` AS `sav_quantity`,
    IF(sav.`quantity` <= 0, 1, 0) AS `badge_danger`
FROM
    `product` p
        LEFT JOIN
    `product_lang` pl ON (pl.`id_product` = p.`id_product`
        AND pl.`id_lang` = 1
        AND pl.`id_shop` = 1)
        LEFT JOIN
    `stock_available` sav ON (sav.`id_product` = p.`id_product`
        AND sav.`id_product_attribute` = 0
        AND sav.id_shop = 1
        AND sav.id_shop_group = 0)
        JOIN
    `product_shop` sa ON (p.`id_product` = sa.`id_product`
        AND sa.id_shop = 1)
        LEFT JOIN
    `category_lang` cl ON (sa.`id_category_default` = cl.`id_category`
        AND cl.`id_lang` = 1
        AND cl.id_shop = 1)
        LEFT JOIN
    `category` c ON (c.`id_category` = cl.`id_category`)
        LEFT JOIN
    `shop` shop ON (shop.id_shop = 1)
        LEFT JOIN
    `image_shop` image_shop ON (image_shop.`id_product` = p.`id_product`
        AND image_shop.`cover` = 1
        AND image_shop.id_shop = 1)
        LEFT JOIN
    `image` i ON (i.`id_image` = image_shop.`id_image`)
        LEFT JOIN
    `product_download` pd ON (pd.`id_product` = p.`id_product`)
WHERE
    (1 AND state = 1)
ORDER BY `id_product` DESC
LIMIT 0 , 20

Voici le résultat du EXPLAIN:

La requête met 85 secondes à retourner les 20 résultats attendus 😅 (et quand j'essaie de charger une fiche produit avec le module cross sells, je me prends un 504 gateway, la requête fait fait des jointures entre order_detail, product, product_shop, product_attribute, product_attribute_shop, stock_available, product_lang category_lang, image et il y a un where sur 4000 order_id 🤣 ; quand j'enlève le module cross sells j'ai plus de problème)

Une idée ? 👀

Edit: Quand je retire les lignes qui font des jointures sur image et image_shop, la requête s'exécute en 0,015 secondes 🤔 J'ai vérifié, mais les indexes existent bien sur les tables concernées...

Le EXPLAIN indique que la table P a des indexs non utilisés, ce qui n'est pas normal.

Le fait que la requête s'exécute rapidement sans les lignes d'image, peut être normal si la requête est restée en cache entre temps. 

Je persiste, il y'a des problèmes d'index, le fait que l'EXPLAIN indique NULL en key, c'est que l'index possible (à gauche), notamment sur l'id_product (PRIMARY) n'est pas utilisé. Il devrait pourtant l'être, puisque c'est une clé PRIMARY utilisée lors des JOIN.

Il faudrait revoir les tables en question, auquel cas, faire un backup des tables, repartir sur un presta frais, et réimporter les données au fur et a mesure.

Idem pour la table pd, elle doit forcément contenir un id_product censé être une clé PRIMARY (ici aucun index).

Il faut revoir les indexs à nouveau.

 

[PrestaPaul]

Je suis toujours en train d'essayer de récupérer les données proprement pour partir sur un projet vierge. Pour les indexes effectivement, je viens de comprendre que les "NULL" n'étaient pas le résultat attendu 🤣

J'ai donc essayé de comparer ma BDD avec la structure native pour les rajouter et... surpris e !

 

Bon, après m'être débarrassé de MariaDB et être passé à MySQL 8.0, j'ai pu finalement rajouter les indexes à la main...
Mais une chose m'échappe toujours: pourquoi je dois les rajouter ?.? Pourquoi elles sont sur le fichier d'installation mais ne s'installent pas correctement chez moi ?

Je vais creuser cette piste et je reviendrai aux nouvelles prochainement 🤔

[AfterGlow93]

Bonjour,

Quel intérêt de retirer MariaDB, c'est le système de BDD le plus utilisé sur web, MariaDB reste plus performant et flexible pour un site sous PS.

Pour les index, pourquoi ne pas récupérer simplement le code créant les tables à l'installation, et le modifier, en passant le CREATE à ALTER et en laissant sur la section ADD INDEX ?

Il suffit d'un index manquant pour rendre la BDD lente et tripler voir décupler le temps de chargement.

 

[PrestaPaul]

Il y a 13 heures, AfterGlow93 a dit :

Bonjour,

Quel intérêt de retirer MariaDB, c'est le système de BDD le plus utilisé sur web, MariaDB reste plus performant et flexible pour un site sous PS.

Pour les index, pourquoi ne pas récupérer simplement le code créant les tables à l'installation, et le modifier, en passant le CREATE à ALTER et en laissant sur la section ADD INDEX ?

Il suffit d'un index manquant pour rendre la BDD lente et tripler voir décupler le temps de chargement.

 

J'ai viré MariaDB car j'utilise MySQL Workbench et que ça n'est pas compatible (causant l'erreur ci-dessus). Pour le côté plus flexible et plus performant c'est sûr ? Des tests ont été fait pour ça ? Car sur la page de recommandations de PrestaShop ils ne parlent pas spécialement de MariaDB 🤔

Pour les indexes mon problème c'est que c'est déjà le cas ; je suis parti sur une installation fraîche de Prestashop donc les indexes devraient y être par défaut (c'est ça que je ne comprenais pas) et j'ai juste rajouté tous les INSERT de ma précédente BDD (sans DROP les tables qui existaient déjà, afin de respecter les nouvelles colonnes et les nouvelles keys).