Optimisation BDD

[Canou83]

Bonjour, dans un soucis d'amélioration de la vitesse, je suis à la recherche de plusieurs solutions.

Dans un premier temps, j'ai rajouté des index dans la BDD mais il me reste tout de même encore des lenteurs.




- Existe-t-il une liste officielle et exhaustive des index manquant/ rajouter dans les tables ? (ALTER TABLE `category` ADD INDEX `parent_id` (`parent_id`);)....

- Pourquoi ces index, qui si j'ai bien compris sont indispensables à une bonne exécution, ne sont-ils pas en natif dans opencart ?

Dans un deuxième temps, j'ai aussi lancé un log de la BDD afin d'identifier les requêtes lentes qui pénalisent la bonne exécution du site.

Par exemple :

Code: Tout sélectionner

# User@Host: web160aquamag[web160aquamag] @  [10.0.89.125]
# Query_time: 0.006793  Lock_time: 0.000167 Rows_sent: 1  Rows_examined: 1442
SET timestamp=1389899921;
SELECT COUNT(DISTINCT p.product_id) AS total FROM oc_category_path cp LEFT JOIN oc_product_to_category p2c ON (cp.category_id = p2c.category_id) LEFT JOIN oc_product p ON (p2c.product_id = p.product_id) LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '2' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND cp.path_id = '276';

ou encore :

Code: Tout sélectionner

# User@Host: web160aquamag[web160aquamag] @  [10.0.89.125]
# Query_time: 0.001356  Lock_time: 0.000318 Rows_sent: 1  Rows_examined: 361
SET timestamp=1389899920;
SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM oc_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '3' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM oc_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '3' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM oc_product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '3') AS reward, (SELECT ss.name FROM oc_stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '2') AS stock_status, (SELECT wcd.unit FROM oc_weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '2') AS weight_class, (SELECT lcd.unit FROM oc_length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '2') AS length_class, (SELECT AVG(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM oc_review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '1496' AND pd.language_id = '2' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0';


Ou même :

Code: Tout sélectionner

# User@Host: web160aquamag[web160aquamag] @  [10.0.89.125]
# Query_time: 0.005733  Lock_time: 0.000133 Rows_sent: 1  Rows_examined: 1058
SET timestamp=1389899851;
SELECT COUNT(DISTINCT p.product_id) AS total FROM oc_product_to_category p2c LEFT JOIN oc_product p ON (p2c.product_id = p.product_id) LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '2' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND p2c.category_id = '99';

Comment interpréter ces résultats ?
Comment agir et régler ces requêtes lentes ?
Qu'est ce que des requêtes non fermées ?

Je pense avoir fait le tour des questions importantes.

Merci pour vos futurs explications

[LeorLindel]

Bonjour,

Tu obtiendras quantité d'informations et de réponses sur le site MySQL concernant toutes les questions que tu pourrais te poser sur l'optimisation de la BDD.

Bonne lecture.

[Canou83]

Merci LeorLindel pour ta réponse.

J'ai parcouru le site que tu indiques, Je ne suis pas certain de comprendre.

Les résultats du tableau de bord de ma BDD me pointent des lenteurs/erreurs sur des requêtes et des requêtes non fermées, et des index manquant.

Les éléments que je trouve dans Mysql me permettent d'optimiser mon fichier my.cnf. Mais à un moment c'est évidement lié, et j'ai beau optimiser my.cnf, tant que je ne règle pas le problème des requêtes/index/fermetures requêtes, je n'aurai pas d'amélioration.

C'est précisément là ma/mes questions.

[LeorLindel]

Il y a ce module qui est vraiment très populaire et performant (à la lecture du nombre de vente, d'avis et de votes)

[Canou83]

Ahhhhhh Voilà un bon conseil !

Je m'obstinai à optimiser cette partie en mettant les mains dans le moteur, mais si il existe un module...

Je vais suivre ton conseils t ton expertise.

Merci.

[LeorLindel]

Et voici un module complémentaire.

[Canou83]

C'est Noël. Merci.

[LeorLindel]

Ce ne sont que des étrennes, Noël étant terminé !

[Canou83]

Ou le nouvel an Chinois...

Bien le premier module est une pure... tuerie. Les scores de vitesse de chargement sont incroyables.
Je n'ai pas encore utilisé le second, mais cela ne sera tarder.

En ce qui concerne le premier. 2 ou 3 trucs ne sont pas réglés, nottament l'affichage du contenu au-dessus de la ligne de flottaison.

Il me reste ça :
Supprimez les ressources JavaScript qui bloquent l'affichage :

Code: Tout sélectionner

http://www.aquamagasin.com/catalog/view/javascript/jquery.cluetip.js

Code: Tout sélectionner

http://www.aquamagasin.com/min/mi6wgylumfwg6zzpozuwk5y/my6wuylwmfzwg4tjob2c62trovsxe6jpnjyxkzlspewtclrxfyys43ljnyxgu4zmorugk3lff5kw42lwmvzhg5lnf5vhgl3tmvwgky3unzqxmltnnfxc42ttfr2gqzlnmuxvk3tjozsxe43vnuxwu4zpmn2xg5dpnvpxgy3snfyhi4zonjzsy2tbozqxgy3snfyhil3kof

Optimisez l'affichage des styles CSS pour les URL suivantes :

Code: Tout sélectionner

http://www.aquamagasin.com/min/mi6wgylumfwg6zzpozuwk5y/my6xi2dfnvss6vlonf3gk4ttovws643upfwgk43imvsxil3tor4wyzltnbswk5c7mjwhkzjomnzxgldunbsw2zjpkvxgs5tfojzxk3jpon2hs3dfonugkzluf5zgk43qn5xhg2lwmuxgg43tfrvgc5tbonrxe2lqoqxwu4lvmvzhsl3vnexxi2dfnvsxgl3vnewwy2lhnb2g4zltomxwu4lvmvzhsllvnewtclryfyytmltdovzxi33nfzrxg4zmnjqxmyltmnzgs4duf5vhc5lfoj4s6y3pnrxxeytppaxwg33mn5zge33yfzrxg4zmorugk3lff5kw42lwmvzhg5lnf5zxi6lmmvzwqzlfoqxxg3djmrsxg2dpo4xgg43tfr2gqzlnmuxvk3tjozsxe43vnuxxg5dznrsxg2dfmv2c63djorswcy3dn5zgi2lpnyxgg43tfr2gqzlnmuxvk3tjozsxe43vnuxwu4zpmzwgk6bpmzwgk6dtnruwizlsfzrxg4zmorugk3lff5kw42lwmvzhg5lnf5vhgl3unfyhg6jpmnzxgltunfyhg6jomnzxgldunbsw2zjpkvxgs5tfojzxk3jpnjzs6y3mn52will2n5xw2ltdonzsy5dimvwwkl3emvtgc5lmoqxxg5dznrsxg2dfmv2c6y3pn5vwszldov2hi4romnzxg/combined.css

Code: Tout sélectionner

https://fonts.googleapis.com/css?family=Ubuntu+Condensed

Code: Tout sélectionner

http://www.aquamagasin.com/catalog/view/javascript/jquery.cluetip.css

je ne voudrai pas les supprimer, mais les rendres asynchrones comme j'ai pu le faire pour toutes les lignes dans catalog/view/theme/montheme/common/header.tpl

Mais je ne trouve pas ou pour ceux là.

Merci.

[slwsu]

Salut, as tu tester l'import dans http://www.mysql.fr/products/workbench/, je l'ai fait et effectivement il y a des choses possible...

[slwsu]

- Existe-t-il une liste officielle et exhaustive des index manquant/ rajouter dans les tables ? (ALTER TABLE `category` ADD INDEX `parent_id` (`parent_id`);)....

Avec les schéma fourni par mysqlWorkbench tu le verra

- Pourquoi ces index, qui si j'ai bien compris sont indispensables à une bonne exécution, ne sont-ils pas en natif dans opencart ?

Ils augmente la vitesse d’exécution car le moteur n'a a comparer que des id

Dans un deuxième temps, j'ai aussi lancé un log de la BDD afin d'identifier les requêtes lentes qui pénalisent la bonne exécution du site.

Il est possible de mettre en cache les requêtes dont tu parle avec la fonction php ob_start(); et supprimer les différents caches lors de modifications enregistrés coté admin

[Canou83]

Merci slwsu, je regarderai ça la semaine prochaine

Bon WE.

[Canou83]

Salut, as tu tester l'import dans http://www.mysql.fr/products/workbench/, je l'ai fait et effectivement il y a des choses possible...

Impossible de me connecter à distance sur mon serveur privé chez OVH.

je dois pas m'y prendre comme il faut.

[slwsu]

Salut,
de mémoire il me semble qu'ovh n'autorise pas les connexions externe...
Ce que tu peux faire c'est exporter ta bdd et la réinstaller dans mysqlworkbench

[Canou83]

J'essayai d'appliquer ça : http://guides.ovh.com/ConnexionDistanteMySQL mais rien n'y fait.

je vais essayer l'import oui.