Trop grande charge de Mysql engendrant une grande lenteur

[comparef]

Bonjour à tous,

Je suis actuellement un des responsable du site *ww.wifeo.com qui est un service de création de sites destiné aux débutants.

Nous avons de gros problèmes de lenteur, duent à une trop grande charge du serveur accueillant les bdd mySQL (même en heures calmes...).

Je vais commencer par vous exposer l'infrastructure dont nous disposons afin que vous ayez tous les éléments :

>2 serveurs Intel bi-Xéon - 2 x 2.8 GHz, 2048Mo RAM, 30Mbps

(un pour accueillir la partie http, ftp et mail et l'autre pour nos bases de données mySQL)

Chacun de ces deux serveur a été optimisé selon sa fonction par un technicien de notre hébèrgeur.

Le contenu des sites de nos membres est stocké dans une base de donnée, et le slow query indique apparemment que c'est la requête qui extrait ce contenu qui consomme beaucoup et surcharge le serveur :

SELECT contenu_entete,contenu_corp FROM pages WHERE site='nom_du_site' AND page='nom_de_la_page';

contenu_entete étant le contenu de la partie haute des pages, et contenu_corp le contenu de la partie principale des pages.

Nous avons mis en place un système de mise en cache de ce contenu (cache renouvelé tous les 30 jours) mais cela n'a pas totallement résolu le problème.

Avez-vous des idées, ou des remarques (on a épuisé toutes les notre...) que ce soit dans l'optimisation de la requête ci-dessus, dans l'amélioration de l'infrastructure ou tout autre suggestion pour résoudre ce problème ?

Ce n'est pas une question précise mais j'espère que vous pourrez tout de même m'aider !

Merci d'avance pour votre réponse,

Robin

[suede]

Est-ce que tu fermes liberes systématiquement ton résultat (mysql_free_result() ) dès que tu l'as exploité plus fermeture des connections à la bd dès que possible ?

[comparef]

Est-ce que tu fermes liberes systématiquement ton résultat (mysql_free_result() ) dès que tu l'as exploité plus fermeture des connections à la bd dès que possible ?

La connexion a la bdd est effectivement fermée des que possible.

Pour mysql_free_result(), ce n'était pas le cas, je viens de le mettre en place.

Est-il recommendé de faire cela apres chque requete, ou uniquement apres les requete tres lourdes ?

Merci pour ton aide.

Modifié 5 Février 2007 par comparef

[Nicolas]

Bonjour,

Peut etre que tu auras la reponse ici

http://www.webmaster-hub.com/index.php?showtopic=23636

[comparef]

Bonjour,

Peut etre que tu auras la reponse ici

http://www.webmaster-hub.com/index.php?showtopic=23636

Merci, je vais donc mettre cette fonction apres chaque requete, comme ca je suis tranquil de ce coté là...

[adn]

Salut,

As-tu bien un index sur site et page ?

Question bête mais on sait jamais...

[comparef]

Salut,

As-tu bien un index sur site et page ?

Question bête mais on sait jamais...

oui, la requete ne selectionne bien qu'une seule ligne. (C'est souvent les truc betes auxquels on pense pas, c'est connu )

[suede]

Bonjour,

Peut etre que tu auras la reponse ici

http://www.webmaster-hub.com/index.php?showtopic=23636

En particulier le message de Thierry8.

[comparef]

Etant données qu'il y a énormément de mouvement et de modifications des tables chaque jour, Pensez-vous qu'effectuer les opérations suivantes sur toutes les tables, toutes les nuits (via une tache cron) serait-il utile ? :

OPTIMIZE TABLE nom_table

ANALYZE TABLE nom_table

[TheRec]

Bonsoir,

Si les requêtes (avec clause WHERE) sont très fréquentes sur ces champs

WHERE site='nom_du_site' AND page='nom_de_la_page'

Tu peux créer des INDEX sur ces deux champs (pas d'index FULLTEXT, des simples INDEX) avec l'instruction CREATE INDEX (MySQL >= 3.22).

Et voici une autre page du manuel de MySQL (5.0) expliquant l'utilité et l'utilisation des index.

Bonne continuation.

P.S. : Il faut bien entendu que les DEUX champs soient indexés pour que l'optimisation soit effective étant donné que tu utilises l'opérateur booléen AND.

[comparef]

Salut,

As-tu bien un index sur site et page ?

Question bête mais on sait jamais...

Bonsoir,

Si les requêtes (avec clause WHERE) sont très fréquentes sur ces champs

WHERE site='nom_du_site' AND page='nom_de_la_page'

Tu peux créer des INDEX sur ces deux champs (pas d'index FULLTEXT, des simples INDEX) avec l'instruction CREATE INDEX (MySQL >= 3.22).

Et voici une autre page du manuel de MySQL (5.0) expliquant l'utilité et l'utilisation des index.

Bonne continuation.

P.S. : Il faut bien entendu que les DEUX champs soient indexés pour que l'optimisation soit effective étant donné que tu utilises l'opérateur booléen AND.

Merci beaucoup, je crois que vous m'avez donné la solution miracle ! Je m'avance peut etre un peu, mais là ou on attendait plus de 50 secondes pour l'affichage de la page, a présent c'est instantané ! (apres avoir mis un index sur les deux champs du WHERE).

Donc voila sachez que vous venez de faire presque 10 000 heureux !

[TheRec]

De rien. Tiens, j'avais loupé l'intervention de adn (toi aussi ? ) qui donnait déjà cette réponse

Ce n'est pas un "miracle", les INDEX sont la base de l'optimisation, malheureusement bon nombre de développeurs semblent penser qu'ils se limitent à une utilisation sur les entiers (plus particulièrement sur les clé étrangères et clé uniques), mais ce n'est qu'une des utilisations.

Toutes fois ils n'ont pas que des avantages, l'ajout, la suppression et la modifications des enregistrements ans cette table seront moins rapides (car les INDEX doivent être tenus à jour), mais ce sacrifice est nécessaire surtout dans le cadre d'une utilisation ou la consultation est plus courante que les tâches citées précédemment.

[comparef]

Merci encore, je vais moi aussi me mettre a prêcher la bonne parole auprès de ceux qui ne connaissent pas les index dans les BDD...

Et au sujet de OPTIMIZE TABLE nom_table et ANALYZE TABLE nom_table, est-il utile de le faire par exemple chaque nuit sur chacune des tables ?

[TheRec]

Pour citer le manuel à propos d'OPTIMIZE TABLE :

Dans la plupart des installations, vous n'avez pas à utiliser OPTIMIZE TABLE. Même si vous faites beaucoup de mises à jour sur des colonnes à taille dynamique, il n'est pas évident que vous ayez à passer cette commande plus d'une fois par semaine ou par mois, et uniquement sur quelques tables.

Et les tables nécessitant l'usage de cette instruction sont celle où il y a des champs de types dynamiques (dont la taille peut varier : BLOG, TEXT, VARCHAR, etc.) et où beaucoup de changement se produisent... cette instruction fonctionne schématiquement comme une défragmentation du disque en réorganisant les morceau de la table afin qu'ils soient contigus et donc plus facilement accessible... et comme une défragmentation du disque dur, avant une certaine quantité de fragmentation il n'y a aucun bénéfice perceptible.

Quant à ANALYSE TABLE c'est un moyen d'optimiser les jointures qui sont faites sur les INDEX d'une table, donc avant que tu aies créé des INDEX sur les tables elle n'aurait pas servi (à par peut-être pour l'INDEX UNIQUE de tes tables ). L'utilisation est la même, une table dans laquelle il y a beaucoup de changements (ajout/modifications/suppression d'enregistrement, jointures évoluant souvent, etc.) profitera de cette instruction.

Le manuel de MySQL est assez bien rédigé et il a l'avantage d'être traduit en français (généralement correctement), c'est vraiment une bonne ressource lorsque tu te pose de question sur le fonctionnement propre de se système de gestion de bases de données, les auteurs ne se limitent pas à la simple définition des instructions, il expliquent aussi les cas d'utilisation et donnent des conseils.

[comparef]

Ok, merci pour ces explications, pour optimize et analize, je vais donc les faire une fois par semaine sur les tables qui bougent beaucoup.

Optimize marche, pas de probleme, en revanche analize me renvoi une erreur que je n'arrive pas a résoudre (pourtant j'ai cherché dans la doc mySQL )...

$requete=mysql_db_query($nom_bdd,"ANALYZE TABLE nom_table",$link)
or die ("erreur analyze table");

(la requete identique marche avec OPTIMIZE)

Voyez-vous une raison particuliere pour que cette requete ne fonctionne pas ?

Modifié 7 Février 2007 par comparef

[TheRec]

L'idéal serait de nous donner l'erreur en question et pas uniquement le code qui la génère Et sinon au lieu de "or die ("erreur analyze table");" tu peux mettre :

$requete=mysql_db_query($nom_bdd,"ANALYZE TABLE nom_table",$link) or die ('Erreur '.mysql_errno().': '.mysql_error());

Cela te donnera l'erreur renvoyée par MySQL en plus de celle générée par PHP

[comparef]

Désolé pour l'oubli.

Je viens de trouver le probleme... J'avais tappé ANALYSE au lieu de ANALYZE... Donc forcement maintenant ca marche mieux...

Ca peut arriver même aux meilleurs non ?

Merci pour ton aide.

[baobab33]

salut,

j'arrive peut-être un peu tard pour donner un avis, mais si jamais tu as encore tes problèmes de lenteurs voici comment j'ai appris à régler les paramètres de mysql, ce qui m'a effectivement permis d'améliorer les performances (rapidité) et j'ai aussi stoppé l'antivirus et l'antispam qui bouffent de la mémoire pour rien, ce qui m'a permis de réallouer tout ça à mysql.

Il faut utiliser : MySysop : MySql System Optimizer (la démo est en panne, mais ça marche bien, c facile à configurer)

En général on a tous un phpmyadmin qui est installé, tu peux avantageusement aller voir "Afficher l'état du serveur" en page d'accueil où tu trouveras une liste de variables commentées qui sont des bonne indications à suivre (dans le sillage de mysysop)

Il faut aussi s'imprégner de la doc mysql sur tous les paramètres qui existent (certains ne sont pas mentionné dans my.cnf, comme "query_cache_size" par ex. : il suffit de les ajouter et de redémarrer mysql)

http://dev.mysql.com/doc/refman/4.1/en/ser...-variables.html