Jump to content

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

StoreCommander
 Share

Recommended Posts

StoreCommander Newbie

StoreCommander

Posted
Posted

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 ?

Link to comment
Share on other sites
PikPik83 Newbie

PikPik83

Posted
Posted

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 !

Link to comment
Share on other sites
StoreCommander Newbie

StoreCommander

Posted
  • Author
Posted

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...

Link to comment
Share on other sites
PikPik83 Newbie

PikPik83

Posted
Posted

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?

Link to comment
Share on other sites
Damien Metzger Newbie

Damien Metzger

Posted
Posted
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 :)
Link to comment
Share on other sites
StoreCommander Newbie

StoreCommander

Posted
  • Author
Posted

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.

11194_Lg3B5ee67u9kEQ60vAdG_t

Link to comment
Share on other sites
Ann Newbie

Ann

Posted
Posted

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.

Link to comment
Share on other sites
Damien Metzger Newbie

Damien Metzger

Posted
Posted

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.

Link to comment
Share on other sites
StoreCommander Newbie

StoreCommander

Posted
  • Author
Posted

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.

Link to comment
Share on other sites
StoreCommander Newbie

StoreCommander

Posted
  • Author
Posted

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.

Link to comment
Share on other sites
Thomas Dedericks Newbie

Thomas Dedericks

Posted
Posted

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 :/

11344_O4zDJOaRUhGZbuF9KGWQ_t

Link to comment
Share on other sites
codegrunt Newbie

codegrunt

Posted
Posted

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

Link to comment
Share on other sites
tlandru Newbie

tlandru

Posted
Posted

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)

Link to comment
Share on other sites
StoreCommander Newbie

StoreCommander

Posted
  • Author
Posted

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.

Link to comment
Share on other sites
DrÿSs' Newbie

DrÿSs'

Posted
Posted

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 ?

Link to comment
Share on other sites
  • 3 weeks later...
Agence Xmedia Newbie

Agence Xmedia

Posted
Posted

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!!!

Link to comment
Share on other sites
Agence Xmedia Newbie

Agence Xmedia

Posted
Posted
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
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing
×
×
  • Create New...