[RESOLU] Gros problèmes de performences SQL en passant de 1.0.0.3 à 1.2.2.0

[StoreCommander 8]

Bonsoir,

certaines requêtes sont énormes et surcharge le serveur sur une boutique de 3.000 produits, 6.000 déclinaisons, 10.000 clients.

Serveur dédié Dual Core, 2 Go ram, MySQL 5 etc.

Par exemple pour la requête :

SELECT p.*, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, p.`ean13`,
           i.`id_image`, il.`legend`, t.`rate`
       FROM `ps_product` p
       LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 2)
       LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
       LEFT JOIN `ps_image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = 2)
       LEFT JOIN `ps_tax` t ON t.`id_tax` = p.`id_tax`
       LEFT JOIN `ps_category_product` cp ON (cp.`id_product` = p.`id_product`)
       INNER JOIN `ps_category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
       INNER JOIN `ps_customer_group` cg ON (cg.`id_group` = ctg.`id_group`)
       WHERE (`reduction_price` > 0 OR `reduction_percent` > 0)
       AND (`reduction_from` = `reduction_to` OR (`reduction_from` <= '2009-08-28' AND `reduction_to` >= '2009-08-28'))
       AND p.`active` = 1
       AND (cg.`id_customer` = 1 OR ctg.`id_group` = 1)
       ORDER BY RAND() LIMIT 1;

Le log affiche :

# Query_time: 1656.424628  Lock_time: 0.000200  Rows_sent: 1  Rows_examined: 2767049  Rows_affected: 0  Rows_read: 1

Traduction : pour trouver 1 seul produit, MySQL parcours 2.8 millions d'enregistrements !

Ce genre de requête se retrouve dans les méthodes getProducts de Manufacturer.php, Supplier.php, Product.php donc sur les pages les plus importantes.

Le site n'est pas exploitable en l'état, chaque page mettant plusieurs secondes à s'afficher quand elles ne bloquent pas MySQL. (testé sur un deuxième serveur pour vérif)

Avez-vous les mêmes problèmes avec votre boutique ?

[PikPik83 0]

Bonjour,

J'ai aussi de gros ralentissement sur ma boutique (beaucoup plus modeste) avec un plus petit serveur dédié. Après chercher une ligne sur plusieurs millions de lignes, c'est pas anormal pour un serveur SQL.

Par contre c'est le grand nombre de requêtes qui plombent MySQL; et prestashop est assez gourmand de ce coté là.

Je pense aussi qu'on peut faire un gros travail d'optimisation du coté du serveur MySQL, avec des index, etc...

L'archivage des donnés aussi peut être une piste.

Voila après c'est des idées pour chercher d'où vient le problème, et suis partant pour chercher plus en avant !

[StoreCommander 8]

Bonjour,

pour les millions je suis d'accord dans un cadre général mais vu les stats du site et le résultat attendu par cette requête, c'est anormal.

Typiquement pour ce genre de requête pourquoi aller chercher les informations secondaires à l'intérieur de la requête ?

Je ne suis pas expert en optimisation MySQL 5 (trop récent pour un barbu comme moi) mais je pense qu'il faudrait récupérer uniquement l'id_produit et ensuite chercher les informations, à mon sens mieux vaut 4 petites requêtes qu'une grosse très lourde.

Dans cet exemple on pourrait conserver 3 tables au lieu de 8 : product, category_group, customer_group (correspondant aux conditions WHERE) puis partir à la recherche des infos dans les autres tables avec des conditions uniquement sur les index de ces tables.

Mais je laisse la parole aux spécialistes...

[PikPik83 0]

Non, il vaut mieux toujours prendre toute les données en une fois, et non pas en deux requêtes. Ce qui est long pour MySQL c'est la recherche, clairement, la difficultés c'est le "WHERE" et les "JOIN [...] ON", bien que ces dernières soit bien plus optimisées.

Utiliser une jointure par exemple est bien plus efficace que d'utiliser 4 requêtes avec des conditions WHERE.

Sinon, on peux aussi regarder du coter des "Index" qui peuvent vraiment accélérer une requête.

Par exemple mettre un index sur "ps_product.active" s'il n'existe pas.

Ensuite je reste persuadé que tu peux améliorer les temps de recherche avec des optimisations MySQL, des tables d'une part, et du serveur de l'autre.

Si tu peux regarder du coté des index et voir si les temps d'exécution changent?

[PikPik83 0]

Par contre, le p.* est pas terrible niveau sécurité et rapidité de la requête, il est préférable d'énumérer les champs, même si ce n'est pas pratique.

[StoreCommander 8]

Le serveur de test est planté et je n'ai pas tous les accès pour le redémarrer, à lundi pour d'autres tests !

Bon week end !

[Damien Metzger 95]

Par contre, le p.* est pas terrible niveau sécurité et rapidité de la requête, il est préférable d'énumérer les champs, même si ce n'est pas pratique.

C'est d'ailleurs tellement agréable de devoir reprendre 200 requêtes pour le moindre champ rajouté dans une table...
Quand on veut tout, une * c'est très bien pour la maintenance.

PSE, affiche nous le résultat d'un explain on on pourra te dire ce qui coince

[PikPik83 0]

Dans mon souvenir le * était pas terrible, même si je suis 100% d'accord avec toi au niveau de la maintenance.
Après cela reste un souvenir et les souvenir souvent faux.

[StoreCommander 8]

Sur une base en local cela donne ceci avec la requête du premier post.

[Damien Metzger 95]

Ben voila, on voit clairement que la jointure sur les groupes de clients est faite n'importe comment.
Je n'en suis pas l'auteur, donc je ne peux pas vraiment suggérer de patch la comme ça, faut que j'en parle en interne.

[StoreCommander 8]

Après lecture plus approfondie du mysql-slow.log, le point commun des requêtes lentes est la restriction par les groupes d'utilisateurs :

AND (cg.`id_customer` = 1 OR ctg.`id_group` = 1)

Donc plus on a de clients, plus le serveur sera lent.

Et un deuxième groupe de requêtes qui prennent 7 secondes :

SELECT
       (
         (
           /* quantity of products witch don't have attributes */
           IFNULL((
                 SELECT SUM(quantity)
                 FROM `ps_product`
                 WHERE id_product NOT IN
                 (
                   /* products with attributes */
                   SELECT DISTINCT(id_product)
                   FROM `ps_product_attribute`
                 )
                 AND id_product IN
                 (
                     /* products direclty in the categories listed bellow */
                     SELECT DISTINCT(id_product)
                     FROM `ps_category_product`
                     WHERE id_category IN (11,24,240,241,242,243,2430,244,245,246,247,2471,2470,248,25,250,2501,2500,251,252,254,2541,2540,256,26,260,2600,261,262,2621,263,2631,2632,2630,264,265,266,27,273,2731,270,2701,275,272,274,278,276,277,2771,2770,271,28,281,284,2841,285,286,288,2880,283,2831,287,289,282,29,290,2900,291,2910,292,293,2931,294,2940,295,296,297,2970,298,2981,2980,299,2991,2992,2990,2993)
                 )
               ),0)
         )
         +
         (
           /* quantity of products witch have attributes */
               IFNULL((
                 SELECT SUM(quantity)
                 FROM `ps_product_attribute` pa
                 WHERE pa.id_product IN
                 (
                     /* products direclty in the categories listed bellow */
                     SELECT DISTINCT(id_product)
                     FROM `ps_category_product`
                     WHERE id_category IN (11,24,240,241,242,243,2430,244,245,246,247,2471,2470,248,25,250,2501,2500,251,252,254,2541,2540,256,26,260,2600,261,262,2621,263,2631,2632,2630,264,265,266,27,273,2731,270,2701,275,272,274,278,276,277,2771,2770,271,28,281,284,2841,285,286,288,2880,283,2831,287,289,282,29,290,2900,291,2910,292,293,2931,294,2940,295,296,297,2970,298,2981,2980,299,2991,2992,2990,2993)
                 )
               ),0)
         )
       ) as nb LIMIT 1;

Dont je joins le EXPLAIN.

[Ann 3]

Bonjour,

Loin d'être une spécialiste en info, j'ai constaté que les requêtes vers toutes les langues ralentissaient considérablement le site. J'ai donc viré dans ma base toutes les tables concernant les langues autres que celles utilisées dans ma boutique.

[Damien Metzger 95]

PSE : la ça devient carrément vilain, mais je n'ai pas la moindre idée d'où provient cette requête.

Ann : Ca c'est le fameux problème de faire la part entre fonctionnalités et performances. Bien sûr que les langues ralentissent les requêtes, mais pas plus qu'elles ne le devraient si on veut du multilangue.
Normalement le problème n'est pas différent pour les groupes de clients, mais la réalisation n'a pas été à la hauteur donc ça ralenti vraiment.

[StoreCommander 8]

Trouvée :

/Classes/Category.php ligne 337

    public static function countNbProductAndSub($id_category, $id_lang)
   {
       $tab = array(intval($id_category));
       Category::getAllSubCats($tab, intval($id_category), intval($id_lang));

       $listCategories = implode(',', $tab);
       $sql = 'SELECT
       (
         (
           /* quantity of products witch don\'t have attributes */
           IFNULL((
                 SELECT SUM(quantity)
                 FROM `'._DB_PREFIX_.'product`
                 WHERE id_product NOT IN
                 (
                   /* products with attributes */
                   SELECT DISTINCT(id_product)
                   FROM `'._DB_PREFIX_.'product_attribute`
                 )
                 AND id_product IN
                 (
                     /* products direclty in the categories listed bellow */
                     SELECT DISTINCT(id_product)
                     FROM `'._DB_PREFIX_.'category_product`
                     WHERE id_category IN ('.$listCategories.')
                 )
               ),0)
         )
         +
         (
           /* quantity of products witch have attributes */
               IFNULL((
                 SELECT SUM(quantity)
                 FROM `'._DB_PREFIX_.'product_attribute` pa
                 WHERE pa.id_product IN
                 (
                     /* products direclty in the categories listed bellow */
                     SELECT DISTINCT(id_product)
                     FROM `'._DB_PREFIX_.'category_product`
                     WHERE id_category IN ('.$listCategories.')
                 )
               ),0)
         )
       ) as nb';
       $result = Db::getInstance()->getRow($sql);
       return $result['nb'];
   }

Je précise que c'est celle du SVN, elle n'a pas été modifiée.
Elle n'a d'ailleurs pas de commentaire en entête. Elle est utilisée uniquement dans /Classes/AdminTab.php ligne 1197 correspondant à un appel de tabs/AdminCategories.php donc à chaque fois que l'on affiche le catalogue dans le backoffice.

[StoreCommander 8]

Solution pour éviter cette dernière, il suffit de mettre en commentaire la ligne 41 de admin/tabs/AdminCategories.php :

//        'physical_products_quantity' => array('title' => $this->l('In stock Products'), 'align' => 'center', 'width' => 50),

Cela accélère l'affichage du catalogue dans le BackOffice.

[Thomas Dedericks 0]

Salut,

J'ai le même genre de problème, mais sur le frontend. Apparemment, c'est la requête du bloc "nouveaux produits" qui plante.

SELECT p . * , pl.`description` , pl.`description_short` , pl.`link_rewrite` , pl.`meta_description` , pl.`meta_keywords` , pl.`meta_title` , pl.`name` , p.`ean13` , i.`id_image` , il.`legend` , t.`rate` , m.`name` AS manufacturer_name
FROM `ps_product` p
LEFT JOIN `ps_product_lang` pl ON ( p.`id_product` = pl.`id_product`
AND pl.`id_lang` =2 )
LEFT JOIN `ps_image` i ON ( i.`id_product` = p.`id_product`
AND i.`cover` =1 )
LEFT JOIN `ps_image_lang` il ON ( i.`id_image` = il.`id_image`
AND il.`id_lang` =2 )
LEFT JOIN `ps_tax` t ON ( t.`id_tax` = p.`id_tax` )
LEFT JOIN `ps_manufacturer` m ON ( m.`id_manufacturer` = p.`id_manufacturer` )
LEFT JOIN `ps_category_product` cp ON ( cp.`id_product` = p.`id_product` )
INNER JOIN `ps_category_group` ctg ON ( ctg.`id_category` = cp.`id_category` )
INNER JOIN `ps_customer_group` cg ON ( cg.`id_group` = ctg.`id_group` )
WHERE p.`active` =1
AND DATEDIFF( p.`date_add` , DATE_SUB( NOW( ) , INTERVAL 20
DAY ) ) >0
AND (
cg.`id_customer` =10668
OR ctg.`id_group` =1
)
GROUP BY p.`id_product`
ORDER BY p.`date_add` DESC
LIMIT 0 , 4

Le résultat de quelques tentavives d'affichage de la page d'accueil dans les processus mysql est attaché. Et c'est pas beau à voir :/

[Jean-Bruno 4]

Bonjour à tous

simple utilisateur, j'ai comme tous constaté une lenteur absolument hallucinante en FO mais pas en BO. Si vous aves une solution car je suis en production.

Bonne soirée

[Thomas Dedericks 0]

Précision : en virant le JOIN sur 'ps_customer_group' et le AND (cg.id_customer ...), ça fonctionne de nouveau. Mais évidemment, on ne peut plus contrôler que le visiteur a les permissions nécessaires pour voir les produits affichés :'(

[Jean-Bruno 4]

Et moi j'ai absolument besoin de mes groupes ! Comment faire, Damien au secours !

[Jean-Bruno 4]

Salut Patric, une idée ?

[Damien Metzger 95]

Bon conclusion, les groupes de clients font complètement ramer le front.
On va voir ce qu'on peut en faire. Mais n'hésitez à poster dans le bug tracker pour bien le signaler.

[StoreCommander 8]

Bug ajouté:
http://www.prestashop.com/bug_tracker/view/2824/

[Jean-Bruno 4]

merci les ZORROs ! j'espère qu'on va trouvé la solution
bonne journée

[Thomas Dedericks 0]

J'ai pas l'impression que ça existe, mais à tout hasard, est-ce qu'il y a un moyen de contourner l'utilisation des groupes sans modifier toutes les requêtes SQL dans les classes du core ?

[StoreCommander 8]

Non, on aurait déjà donné la solution

[Thomas Dedericks 0]

Non, on aurait déjà donné la solution

On peut toujours rêver Par contre, ça ne serait une "solution" que pour ceux qui n'ont pas vraiment besoin des groupes

Donc je vais devoir me taper les 17 requêtes, et prier pour que le problème soit corrigé dans la prochaine release. Pas glop.

[codegrunt 19]

I apologize for responding in English but my French is non-existent - hopefully someone has not already said the same thing in French.

The query as shown looks like it could trivially have both image joins and the tax join moved into a separate query and the data merged manually in PHP afterwards which would help execution speed. That would get of 3 joins right off the bat. Presumably some of the category stuff could be skipped as well if the query was adjusted to get the categories first and then do a second query after that to grab products using a known set of IDs instead of a join.

Cheers

[Jean-Bruno 4]

JE RAME !!!!!!!!
S'il vous plait les dev, faites quelque chose, j'ai une augmentation de mon trafic X 4 et ça tombe vraiment mal cette histoire de Groupes.
MERCI BEAUCOUP !

[tlandru 0]

Nous avions commencé à parler de ce soucis ici :

http://www.prestashop.com/forums/viewthread/24495/discussion_generale/1000_produits___1000_clients__requete_sur_le_front_de_73sec_a_cause_de__quotps_customer_group_quot

Seule solution pour nous : virer les vérifications de groupes sur les catégories, produits, transporteur et module de paiement (si qqun en voit d'autres, je n'ai pas testé de fond en comble la boutique encore)

[StoreCommander 8]

Voici les endroits à modifier, pas encore testé. J'essaierai de faire un patch dès que j'aurai le temps en attendant la team.

liste.txt

[Jean-Bruno 4]

Merci
PShopExpert
j'espère que ça va faire avancer l'affaire rapidement. Si j'ai le temps je teste ce soir !

[DrÿSs' 22]

Bonjour,

Les bugs ont été identifiés et vont être corrigés dans la nouvelle version de demain.

[StoreCommander 8]

Yes !!!! Merci la team !!!!!

[StoreCommander 8]

Dispo sur le svn ! La version officielle ne devrait plus tardée Merci la team !

[Atch 399]

Dispo sur le svn ! La version officielle ne devrait plus tardée Merci la team !

Bah demain

Merki la team...


EDIT : A non c'est la SVN qui sort ...

Atch

[StoreCommander 8]

Cette solution reste d'actualité en attendant la version 1.2.4 ou 1.3 :

Mettre en commentaire la ligne 41 de admin/tabs/AdminCategories.php :

//        'physical_products_quantity' => array('title' => $this->l('In stock Products'), 'align' => 'center', 'width' => 50),

Cela accélère l'affichage du catalogue dans le BackOffice.

[DrÿSs' 22]

Les lenteurs en FO et en BO ont été corrigées et sont disponibles sur le SVN, et bientôt dans la 1.2.3.

[StoreCommander 8]

Ah bien du coup même plus besoin de mettre en commentaire la ligne dont je parlais, merci !

[StoreCommander 8]

...

EDIT: Je retire ce que j'ai dit Ca va assez vite !

[DrÿSs' 22]

Est-ce que c'est moins lent qu'avant ?

Combien avez-vous de produits au total ? Et de sous-catégories (la profondeur) ?

Nous n'arrivons pas à reproduire cette lenteur, pouvez-vous nous donner un accès à votre FTP (host, user, password) et votre back-office (url, email, password) par email à philippe at prestashop point com ?

[StoreCommander 8]

C'est tout bon, c'était ma faute... Désolé pour la fausse alerte.

[DrÿSs' 22]

Ouf

[Jean-Bruno 4]

Bonjour Philippe
Perso ça change pas grand chose sur mon install 1.2.3 toute neuve ! Si vous voulez voir c'est là http://www.denaska.com/boutique3/ et je suis prêt à vous donner tout ce que vous voulez

MERCI

[Jean-Bruno 4]

eureka ! j'ai réinstallé et réimporté de manière plus conventionelle et Presta est redevenu la bombe d'il y a quelques temps. Même le cache smarty à l'air de fonctionner. CHAPEAU BASQUE messieurs.
MERCI, j'adore ce soft et la communauté qu'il l'utilise.

a+

[Agence Xmedia 0]

Bonjour, j'ai un gros souci, apres quelques modifs sur mon site, je me suis rendu compte que ma base mysql ne repondait plus, j'ai d'abord cru à un plantage de chez amen et apres appele non.
Apparement mysql est surchargé à 300% , d'apres le gars de chez amen y'aurais pas mal d'erreure de script, je ne sais pas quoi faire pour regler le souci en sachant que demain ça doit etre l'ouverture du site!!!

[StoreCommander 8]

Bonjour

le problème a été corrigé dans la dernière version 1.2.4 (sauf pour les sites affiliés et ses affiliations qui rament)

[Agence Xmedia 0]

Bonjour

le problème a été corrigé dans la dernière version 1.2.4 (sauf pour les sites affiliés et ses affiliations qui rament)

Y'a t'il un tuto pour la maj vers la derniere version?
sauf pour les sites affiliés et ses affiliations qui rament: pourrais tu m'en dire plus?

[StoreCommander 8]

Tuto : voir page de téléchargement PrestaShop
Sur grosse boutique c'est inutilisable, le serveur mysql est plié en 4...

[Agence Xmedia 0]

Sur grosse boutique c'est inutilisable, le serveur mysql est plié en 4...

Le systeme d'affiliation?

[StoreCommander 8]

oui dans stats > sites affluents

[Agence Xmedia 0]

oui dans stats > sites affluents

y'a moyen de virer le truc?

[StoreCommander 8]

1. ne pas aller sur la page ci dessus
2. désinstaller le module "Affiliation - Accès boutique" s'il est installé

[Agence Xmedia 0]

1. ne pas aller sur la page ci dessus
2. désinstaller le module "Affiliation - Accès boutique" s'il est installé

merci

[Agence Xmedia 0]

1. ne pas aller sur la page ci dessus
2. désinstaller le module "Affiliation - Accès boutique" s'il est installé

Sites affluents: imposible de trouver dans ka derniere version de presta le module!!!

[Agence Xmedia 0]

Bonjour

le problème a été corrigé dans la dernière version 1.2.4 (sauf pour les sites affiliés et ses affiliations qui rament)

Bonjour, je reviens vers vous car j'ai toujours le mm souci, ça rame à bloc, des que je veux faire une modif sur un produit!!
J'ai suivi vos conseils et installé la derniere version de presta mais pourtant rien n'y fait