memory_limit et charge serveur

[thori]

Bonjour,
J'ai un gros problème en ce moment avec mon hébergeur, je viens de transférer mon site d'un mutualiser vers un semi dédié et hier (3 jours après) j'ai reçu cet email de la part de mon nouveau hébergeur.

Hello,
Our techs noticed an increase in mysql CPU usage during the last 2 days. Can you please notify us of any changes you made to your site that might cause this.

This might include addition of plugins or any upgrades.

Please note that there is a single site on current server - that is yours. The CPU Mysql Usage is currently 200% with just 1 site in this brand new Xeon Node.
Please check your site and give us a brief feedback.
Thanks.

mon site n'a que 9000 visiteurs par jours, et sur le mutualisé ou j'étais il n'y avais aucun problème de ce genre, je n'ai rien changer a mon site le même que lorsqu'il était sur le mutualisé, la seule explication que j'ai trouvé c'est la memory_limit, sur l'ancien elle était à 128 mo et sur l'actuel elle est de 32 mo, alors j'aimerai savoir si cela peux être la cause de la charge serveur, sachant que ma base de donnée pèse 280 mo et que mes visiteurs font beaucoup de recherches, et de ce fait les requêtes mettent plus de temps pour être exécuté.
Merci davance.

[NxtGen]

Tu devrais peut-être voir à mettre en place un système de cache. Activer celui de Mysql si ce n'est pas déjà fait, et peut-être carrément mettre en cache les recherches de tes visiteurs, dans le cas où celles-ci sont récurrentes bien sur.

Vérifie également que les index sont bien présents là où il faut dans la structure de tes tables dans la base de données.

En bref, essaye d'optimiser au maximum ton code, évite les requêtes imbriquées, etc

Sans exemple de code précis, difficile de t'aider plus

[forummp3]

9000 visiteurs c'est deja pas mal, on ne peut pas dire que c'est un petit site, suffit qu'il y ai une page ou une requete mal optimisé et tu explose la charge serveur. Si tu fais des recherche, comme tu le dis, et que tu utilise par exemple la requete avec un like, c'est de là que viens surement le probleme (parsing d'une bdd de 128 mo...)

[Julia41]

Je rejoins l'avis de forummp3, 9000 visiteurs par jour, en fonction de ce qu'ils font, ça peut vite être beaucoup pour un site pas trop optimisé.

[thori]

Bonjour,
Merci pour votre aide, depuis la derniere je pense avoir fait quelques progrés ,j'ai pris un serveur core 2 4x 2.66 avec 4 go de ram, la consommation ram est arrivé a 3.9 go et mysql a 300 % de cpu, et voila ce que j'ai découvert .

une recherche sur le site donne:

<!-- Script execute time 5.25717 seconds -->
<!-- Time spent on the compilation templates 0.00547 seconds -->
<!-- Time spent to execute MySQL queries: 5.21109 seconds -->

<!-- Total number of MySQL queries 3 -->
<!-- Output using compression gzip -->
<!-- Total file size: 77260 bytes After compression: 16428 bytes -->

et ça peut arriver jusqu'a 7 ou 8 seconde pour 3 requêtes et quand on va sur la page statistiques par exemple qui comporte plus de requêtes j'ai ça:

<!-- Script execute time 0.28673 seconds -->
<!-- Time spent on the compilation templates 0.02853 seconds -->
<!-- Time spent to execute MySQL queries: 0.22931 seconds -->

<!-- Total number of MySQL queries 18 -->
<!-- Output using compression gzip -->
<!-- Total file size: 42635 bytes After compression: 10462 bytes -->

j'ai essayé de mettre en cache les requêtes de recherche, en remplaçant SELECT SQL_NO_CACHE par SELECT SQL_CACHE, ça semble marcher mais si je refais la même recherche 10 min après ça reviens comme avant, je pense que le cache ne dure pas longtemps,est-il possible de le mettre sur 6 heures par exemple.

ensuite j'ai creusé un peu la piste de l'index de table, et la j'avoue j'ai même pas su trouvé comme vérifier si il existe déjà.

voici le code php pour la recherche y aurait-il un moyen de l'optimiser ?.

$where = $where_posts;
$posts_fields = "SELECT SQL_NO_CACHE id, autor, " . PREFIX . "_post.date AS newsdate, " . PREFIX . "_post.date AS date, short_story AS story, " . PREFIX . "_post.xfields AS xfields, title, descr, keywords, category, alt_name, comm_num AS comm_in_news, allow_comm, rating, news_read, flag, editdate, editor, reason, view_edit, tags, '' AS output_comms";
$posts_from = "FROM " . PREFIX . "_post";
$sql_fields = $posts_fields;
$sql_find = "$sql_fields $posts_from $where";
$posts_count = "SELECT SQL_NO_CACHE COUNT(*) AS count $posts_from $where";
$sql_count = $posts_count;

j'ai aussi lus qu'on pouvais enregistrer les résultats de recherche sur un fichier TXT, mais j'ai pas trouvé comment .

Merci d'avance.

[forummp3]

tu n'as qu'a te creer ton propre cache en php/mysql. Tu créé un table "resultat" et tu insere la liste des resultats pour le mot clé.

[Julia41]

Bon, déjà tu vires ce SQL_NO_CACHE, c'est fait que pour faire du dev et/où benchmarker des Queries sans prendre le cache.

J'ai dégagé certaines de tes variables car bon, quand c'est du code qui n'est pas à nous le PREFIX, on s'en fou un peu et je l'ai explosé, c'est plus lisible.

Code: Tout sélectionner

$posts_fields = "SELECT
   SQL_CALC_FOUND_ROWS,
   id,
   autor,
   p_post.date AS newsdate,
   p_post.date AS date,
   short_story AS story,
   p_post.xfields AS xfields,
   title,
   descr,
   keywords,
   category,
   alt_name,
   comm_num AS comm_in_news,
   allow_comm,
   rating,
   news_read,
   flag,
   editdate,
   editor,
   reason,
   view_edit,
   tags, '' AS output_comms
   FROM p_post;";
$postcount = 'SELECT FOUND_ROWS();';

Voilà ce que ça donnerait de mon côté... Le FOUND_ROWS t'évitera un COUNT() inutile

Après ça:

Code: Tout sélectionner

$where = $where_posts;

On l'a pas, donc aucun idée de ce que c'est.

[Julia41]

Je me double post, mais si jamais tu peux me sortir la requête (pas le résultat). *finale* executé (sans variable PHP), ainsi que le résultat de cette même requête précédée d'un EXPLAIN :
EXPLAIN SELECT...
Tu dois avoir des couilles dans tes INDEX, voir pas d'INDEX du tout.

[thori]

bonjour,
j'ai passé ces deux derniers jours a essayer de trouver une solution mais rien, ton code a généré un message d'erreur.
j'ai mis ça a la place du premier, pensant l'alléger un peux la recherche marche toujours mais ça na rien changer.

$posts_fields = "SELECT autor, " . PREFIX . "_post.date AS date, short_story AS story, " . PREFIX . "_post.xfields AS xfields, title, descr, ''";

pour la requête elle ressemble a ça

SELECT SQL_NO_CACHE COUNT( * ) AS count FROM dle_post WHERE dle_post.approve AND (short_story LIKE '%C...

et pour l'index je crois l'avoir trouver dans phpmyadmin
pour la table post j'ai ça

Données 202,7 Mio
Index 75 084,0 Kio
Total 276,0 Mio
je pense qu'au pire je devrais limité les résultats afficher a 6 ou 7 résultats, ça allègerai un peu la requête,qu'en dit tu ?

[seebz]

En général, on recommande de faire un Count() sur le champ primaire et pas sur tous les champs (*)..

J'sais pas si ça aura beaucoup d'impact mais tu pers rien à essayer :

Code: Tout sélectionner

SELECT COUNT(id) FROM ...

[forummp3]

lol seebz, faut revoir la doc, ça revient au meme, mysql utilise la clé primaire pour le count(*) vu qu'il compte les lignes .

[fobec]

Bonjour,

une piste complémentaire pour soulager le serveur MySQL: mettre les résultats de requêtes en cache (fichier texte).
J'ai donné un exemple sur mon site,il y a quelques jours:
- query=SELECT * from table,
- serialize la requête query,
- enregistrer dans un fichier texte,
...
- lire le fichier texte,
- unserialize la requete,
- remplir le template HTML avec les résultats de la requete.

Si l'architecture du site est en MVC, c'est très facile à mettre en œuvre. Coté performance, les temps de réponse ont été divisés par 2 chez mon hébergeur.

[thori]

Bonjour,
Hier en regardant de plus prêt la requête qu'a demandé julia j'ai trouvé le code php responsable de la charge, il était juste avant celui de mon second message.
voila ça c'est avant

Code: Tout sélectionner

if( ! empty( $story ) ) {
            $titleonly_where = array ('0' => "short_story LIKE '%{story}%' OR full_story LIKE '%{story}%' OR " . PREFIX . "_post.xfields LIKE '%{story}%' OR title LIKE '%{story}%'",
                                '1' => "text LIKE '%{story}%'",
                                '2' => "short_story LIKE '%{story}%' OR full_story LIKE '%{story}%' OR " . PREFIX . "_post.xfields LIKE '%{story}%' OR title LIKE '%{story}%'",
                                '3' => "title LIKE '%{story}%'",
                                '5' => "id LIKE '{story}%'",
                                '6' => PREFIX . "_static.template LIKE '%{story}%'" );

j'ai changé la ligne 0 et 2 par ce code

Code: Tout sélectionner

"short_story LIKE '%{story}%' ",

la recherche donne à priori le même résultat, et en comparant les performances avec la même requête le temps d'exécution a été diviser par deux.
Pour le cache des requêtes c'est en cours, je fais des tests en local, j'ai réussi a créer des fichiers html a partir du cache, mais ils restent vides,je vous tiens au courant.
Merci

[Aaarrrgggh]

ça irait encore plus vite avec un full text.

[Julia41]

Ca irait encore plus vite avec sphinx