[Résolu] Performances LAMP

[Greywolf]

Bonjour,

 

Dans le cadre du boulot, j'ai créé une base de données sous MySQL v5 type MyISAM associé à Apache2 (mpm-prefork) avec php5 en module, le tout sous Debian Testing sur un AMD 64 3500+ 1Go DDR-RAM.

 

La BdD grossissant de jour en jour je suis confronté à des problèmes de performances lorsque l'on réalise des requêtes qui retournent beaucoup de résultats (> 100000) avec un processus Apache2 qui tourne sans arrêt.

 

Ma BdD consiste en 15 tables correspondant chacune à un analyseur; chaque table comporte entre 10 et 20 champs selon les analyseurs; + 3 tables d'identifiant divers (date, lieu,...) exportées en clés étrangères dans les 15 premières.

 

L'application PHP développée consiste en

• authentification de l'utilisateur avec stockage des infos de login/pass dans des variables de session
  formulaire de sélection pour une construction personnalisée des requêtes mysql à réaliser (Cf mon précédent

topic)
construction de la requête en fonction des choix récupérées par la méthode POST, interrogation de la base MySQL
Calculs sur les résultats retournés avec des fonctions statistiques fournies par Stats.php du projet PEAR et écriture dans un fichier texte sur le serveur des data extraites
transformation des résultats fournis en ligne par Mysql pour les récupérer en colonne *
création d'un formulaire pour tracer des graphiques à partir des résultats récupérés à l'aide de la librairie Jpgraph*

tout ceci marche pas trop mal du moment que la requête n'englobe pas trop d'analyseurs (boucle foreach sur chaque analyseur sur les étapes 2 et 3) ou si la requête est limitée à un analyseur et quelques dates/lieux/....

 

Au niveau optimisation, je me suis inspiré de ce site http://www.onlamp.com/pub/a/onlamp/2004/02...amp_tuning.html

• j'ai mis les grosses requêtes en cache SQL; lorsque je réalise une grosse requête directement via le client mysql, celle ci met moins d'une seconde à sortir (et moins de 0.1 seconde quand il y a un hit dans le cache)
  j'ai créé des index sur les champs utilisés dans les clauses WHERE des requêtes mysql
  j'ai compilé un accélérateur php basé sur Zend (eaccelerator)
  j'ai modifié les paramètres mpm_perfork comme suit
  

<IfModule mpm_prefork_module>
StartServers		  5
MinSpareServers	   5
MaxSpareServers	  10
MaxClients		  10
MaxRequestsPerChild   100
</IfModule>

Je pense avoir deux goulots d'étranglements dans mon appli PHP au niveau des étapes 3 et 4 (celles avec les *)

-mysql_fetch_array retourne les éléments du tableau de résultat ligne par ligne et moi je les veux colonne par colonne; j'ai donc une fonction fetch_AllDataqui lit ligne à ligne le tableau de résultat et fait une rotation à 90° de tout ce monde là dans un tableau associatif avec le noms de mes champs en en-tête.

- le tableau associatif est ensuite éclaté en autant de variables de session qu'il y a de champs sélectionnés (multipliés par le nombre d'analyseurs choisis) pour pouvoir les passer au script jpgraph

 

J'ai dans un premier temps commenté cette dernière partie (l'étape 4 n'est donc pas réalisée) et j'ai inséré des fonctions microtime en début et fin de mon script php pour évaluer son temps d'éxécution global:

 

Sur une requête globale sur 1 table (avec des inner join sur les 3 tables date/lieu/...), le script php m'indique un temps d'exécution de 3.3 secondes dont 1.5 seconde pour la fonction fetch_Alldata.

 

sauf que avant de voir ce score s'afficher sur mon navigateur, j'ai du patienter 10-15 minutes :-/

Pendant ce temps, un processus apache2 occupe 99,9% de la charge CPU avec une charge mémoire comprise entre 10 et 20% (infos données par top)

 

Si je stoppe le processus apache en insérant une clause max_execution_time dans le script php, j'obtiens la même page beaucoup plus rapidement mais avec une erreur de temps d'exécution dépassé (sauf que tout y est...)

 

si certains ont des idées sur la question, parce que là.....

 

merci

[KewlCat]

C'est très très louche que ta page soit complète (finie de générer et reçue par le navigateur) alors qu'apparemment la connexion n'est toujours pas close. Comment est configuré ton "accélérateur de PHP" ? Peut-on forcer un flush des buffers ? Y aurait-il des erreurs de Content-Length qui forceraient le navigateur à attendre des données alors que PHP a fini ?

C'est pour le moins étrange.

 

D'ailleurs, 10 à 15 minutes pour générer une page avec seulement 10000 lignes manipulées, même en PHP, même avec MySQL, c'est anormalement élevé.

 

Au passage, il me semblait que InnoDB donnait de meileurs résultats que MyISAM (tout en étant plus proche des normes SQL)... Du moins c'est ce dont je crois me rappeler ; ça date de l'époque où le moteur InnoDB avait été rendu disponible aux versions "non-entreprise" de MySQL...

Bref. Ca m'étonnerait que ça soit ça, également.

 

Te sens-tu prêt à ajouter des traces partout (messages sur la console, messages dans la page, ou autre solution de ton choix), vraiment partout et pas uniquement globalement, pour déterminer quelle méthode démarre / finit à quelle heure, et ainsi avoir des infos beaucoup plus précises que ce que top te fournit ?

 

Peux-tu essayer d'utiliser ethereal ou tcpdump (ou un autre outil de ton choix) pour savoir exactement ce qui transite (entêtes HTTP comprises) sur le réseau pendant tout le temps de la requête ? "Ce qui transite", mais aussi et surtout "quand" ...

[Greywolf]

Hello Kewlcat,

 

merci de ta réponse et des pistes indiquées.

En fait la page n'est reçue (en fait affichée par le navigateur) qu'au bout de 15 minutes :-/. Le navigateur reste sur le formulaire initial et l'indicateur de chargement tourne, tourne, tourne...

 

Comment est configuré ton "accélérateur de PHP" ? Peut-on forcer un flush des buffers ? Y aurait-il des erreurs de Content-Length qui forceraient le navigateur à attendre des données alors que PHP a fini ?

 

la configuration est celle par défaut, je vais essayer de le désactiver pour voir si ça change quelquechose.

Comment puis-je voir de potentielles erreurs de Content-Length? (en sniffant les headers HTTP ?)

 

Te sens-tu prêt à ajouter des traces partout (messages sur la console, messages dans la page, ou autre solution de ton choix), vraiment partout et pas uniquement globalement, pour déterminer quelle méthode démarre / finit à quelle heure, et ainsi avoir des infos beaucoup plus précises que ce que top te fournit ?

 

je vais essayer de logger un maximum de choses dans syslog, la page ne s'affichant pas au fur et à mesure, je vais peut être commenter encore des bouts de code pour espérer trouver où cela coince (si ça vient du code) :-/

 

edit: ce qui semble prendre du temps, c'est la manipulation de mon tableau associatif, je mets mon code dans un autre post.

 

Peux-tu essayer d'utiliser ethereal ou tcpdump (ou un autre outil de ton choix) pour savoir exactement ce qui transite (entêtes HTTP comprises) sur le réseau pendant tout le temps de la requête ? "Ce qui transite", mais aussi et surtout "quand" ...

Je vais utiliser wireshark sur le serveur pour voir ce qui est envoyé et quand c'est envoyé

edit: bon rien de transcendant apparemment, 8 paquets envoyés pour le formulaire, la réponse vient plus de 15 minutes après

 

Pour MyISAM, on me disait que c'était plus rapide pour une base destinée essentiellement à des requêtes SELECT.

[Greywolf]

<?php
session_start();

ini_set('error_reporting', E_ALL);
ini_set('display_errors', TRUE);
ini_set('max_execution_time',0);

require_once 'Math/Stats.php';
include 'scr/fetchdata.php';
$temps_debut = microtime_float();

//Rappel des choix postés par l'utilisateur
...
//connexion à la BDD
  $hote="localhost";
  $utilisateur=$_SESSION['login'];
  $password=$_SESSION['password'];
  $connexion = mysql_connect($hote,$utilisateur,$password);

//si la connexion est active et si au moins un analyseur a été choisi
if ($connexion>0 && isset($_POST['analyseur'])) {
mysql_select_db("mabase") or die("Could not select database");
//écriture d'une div contenant un tableau

//Construction d'une requete par analyseur  
 foreach ($analyseur as &$value) {
   //Sélection des champs a extraire en fonction de l'analyseur
   switch ($value) {
       case a:
            $champs="......";
       case b:
      ...
   }
   //requete commune
   $query = "SELECT $champs FROM `$value` 
             INNER JOIN `urban_section` ON `$value`.`urban_section_idurban_section` = urban_section.idurban_section
             INNER JOIN `travel_date` ON `$value`.`travel_date_idtravel_date` = travel_date.idtravel_date
             INNER JOIN `travel` ON `$value`.`travel_idtravel` = travel.idtravel
             WHERE `valid` = '1'";

 //Construction de la requ?te en fonction des choix de l'utilisateur
 //rajout de clauses WHERE

 //nom de fichier
   $filename ="requete_".$value.".txt";

//Récupération des résultats
 $mysql_result = mysql_query($query);

//création d'un tableau associatif $assoc_result qui comporte $nrows lignes
 $assoc_result=array();
 $nrows=fetchAllData($mysql_result,$assoc_result);

 mysql_free_result($mysql_result);

 

jusqe là ça va à peu près, la fonction fetchAllData est gourmande en mémoire mais ça passe plutôt pas trop mal

//Création d'un fichier de résultat
 $repertoire = "uploadtemp/";
 if (!($fp = fopen("$repertoire$filename", 'w'))) {
   return;
   }

//Si la requete retourne des résultats, on extrait des arrays du tableau associatif pour chaque analyseur
 if (isset($nrows) && $nrows != 0) {
 switch($value) {
       case a:
           $res_a_date = $assoc_result['TRAVEl_DATE'];
         ....

         fprintf($fp,"date\tMois\tType trajet\ttron?on\theure\tNOx\tNO\tNO2\tperiodes\n");
         for ($i = 0; $i < $nrows; $i++) {                                      fprintf($fp,"%s\t%s\t%s\t%s\t%s\t%.3f\t%.3f\t%.3f\t%s\n",$res_a_date[$i],$res_a_month[$i],$res_a_type[$i],$res_a_section[$i],$res_a_hour[$i],$res_a_NOx[$i],$res_a_NO[$i],$res_a_NO2[$i],$res_a_period[$i]);
          }

         fclose($fp);        
         $s = new Math_Stats();
         $s->setData($res_a_NOx);
         $mean = $s->mean();
         $median = $s->median();
         $quartiles = $s->quartiles();
         $min = $s->min();
         $max = $s->max();
        unset($s);
       printf ('<tr><td>%s</td><td>%.3f</td><td>%.3f</td><td>%.3f</td><td>%.3f</td><td>%.3f</td><td>%.3f</td><td>%.3f</td><td>%.3f</td><td><a href="uploadtemp/%s">%s</a></td></tr>', $value, $mean, $median, $quartiles[25], $quartiles[75], $min, $max, $quartiles[10], $quartiles[90],$filename,$value);
        break;
      case b:
         ....
 }
unset($assoc_result);
}
 else {
   echo '<tr><td>'. $value .'</td><td>Aucune valeur pour l\'analyseur ' . $value . '</td></tr>';
   unset ($assoc_result);
   }

//fin de la boucle foreach($analyseur as &$value)
}

//fermeture de la div
echo '</table></div>';

//fin de la condition if ($connexion >0)
}
else {
   echo "Impossible de se connecter à la base de données !";
   }

 

Si je laisse la partie dans laquelle j'explose $assoc_result en plusieurs arrays $res_[nom_de_l'analyseur]_[nom_du_champ] le temps de traitement explose

 

Peut-être y-a-t-il des grosses erreurs dans ce code? l'utilisation de boucle foreach et de switch n'arrange probablement pas le système.

Y-a-t-il un moyen plus simple de récupérer les résultats d'une requête mysql non pas en ligne mais en colonne? faire une requête par champ plutôt qu'une seule requête sur plusieurs champs?

 

edit:

Si je n'explose plus $assoc_result en petits tableaux, ça va tout de suite beaucoup mieux.

J'ai besoin tout de même d'accéder aux résultats extraits pour chaque analyseur pour le script de création de graphique jpgraph. J'ai donc associé dans ma boucle foreach($analyseur as &$value) le tableau $assoc_result à une variable de session $_SESSION['nom_de_l'analyseur']

il faudrait que j'accède facilement à $_SESSION['nom_de_l'analyseur']['type_de_polluant'][$i]

 

Les variables de session sont-elles faites pour trimbaler de grosses quantités de data?

[KewlCat]

Pour MyISAM, on me disait que c'était plus rapide pour une base destinée essentiellement à des requêtes SELECT.

Admettons. N'en connaissant pas plus long sur le sujet, je ne saurai te conseiller

rien de transcendant apparemment, 8 paquets envoyés pour le formulaire, la réponse vient plus de 15 minutes après

Ah ouais... quand même ! Quinze minutes avant la réception du HTTP 200 !!

Faudrait vraiment trouver un moyen de forcer le flush des buffers, parce que là c'est pas pratique du tout (et pas uniquement pour débugger, parce que je pense que les utilisateurs de ton appli seraient content de voir un petit quelque chose apparaitre pour leur demander de patienter...)

Y-a-t-il un moyen plus simple de récupérer les résultats d'une requête mysql non pas en ligne mais en colonne? faire une requête par champ plutôt qu'une seule requête sur plusieurs champs?

Effectivement, tu devrais peut-être récupérer un resultset (?) par colonne que tu veux traiter plutôt que de tout récupérer et de t'amuser ensuite à doubler la place mémoire occupée en recopiant chaque colonne de chaque ligne dans son tableau...

Si MySQL est intelligent, tu bénéficieras du fait de passer plusieurs requêtes de suite avec les mêmes clauses / mêmes jointures ? Sinon, il faut peut-être essayer de passer par des prepared_statements (si toutefois c'est possible en PHP, et avec tes requêtes...), ou construire des vues qui seront correctement rafraichies au remplissage des données et qui ne te feront pas faire de gymnastique excessive lorsque tu voudras consulter tes données (ok, ça déplace peut-être le problème, mais au moins à la consultation ça prendra moins de temps... reste à savoir si tu peux supporter de ralentir le remplissage...)

Les variables de session sont-elles faites pour trimbaler de grosses quantités de data?

Pas vraiment, non. De plus, ces données sont censées persister donc entre deux requêtes (voire plus fréquemment en fonction de l'humeur d'Apache - ou de sa configuration ) elles sont écrites dans un fichier ! Niveau perfs on peut faire mieux.

Tu n'as que la session pour communiquer tes tableaux de données à la bibliothèque jpgraph ?

 

-- edit --

Après relecture du code, je me demande si le fait d'avoir à chaque donnée lue un test sur l'existence de la clé dans tes tableaux associatifs (le "if( !array_key_exists($NAME,$a_AllData) ){ $a_AllData[$NAME]=array(); }") ne te pourrit pas un peu trop ton traitement. Tu n'aurais pas la possibilité de créer tous tes arrays une bonne fois avant cette boucle, et virer ce test ? (Tu as bien accès aux metadata de ton statement sans avoir à faire un fetch, rassure-moi ?)

 

-- edit --

Après re-re-relecture du post...

Si je n'explose plus $assoc_result en petits tableaux, ça va tout de suite beaucoup mieux.

Quel est l'ordre de grandeur ?

[Greywolf]

Quel est l'ordre de grandeur ?

 

pour la même requête, je suis passé de + de 15 minutes à 30 secondes

 

je vais regarder du côté du resultset (?).

 

Pour jpgraph, l'étape 4 décrite dans mon premier post est la création du formulaire pour l'utilisateur:

- choix du type de graphique

- choix des abscisses à représenter

- choix des analyseurs et, in extenso, choix du ou des polluants associés à tracer

- validation et envoi au script jpgraph par POST

 

Au tout début, j'ai un peu honte de le dire, j'avais explosé mon tableau de résultats en une chaîne de caractère avec implode pour les coller dans le champ value du formulaire.... j'ai rapidement abandonné au vu des performances réseau nécessaires: balancer 150M de data au client pour qu'il les renvoie au serveur par la méthode POST ce n'était pas génial.

 

Selon la doc de jpgraph, soit les data sont statiques (on oublie), soit elles sont extraites d'une BDD par le script de création jpgraph soit elles sont envoyées par POST.

 

Comme je n'avais pas envie de refaire une requête mysql pour récupérer les data précédemment extraites, j'ai pensé aux variables de session...

Sinon comme alternative, je peux peut-être stocker les requêtes mysql créées par les choix de l'utilisateur dans une variable de session et refaire un mysql_query dans le script jpgraph afin de récupérer les data à tracer. L'avantage c'est que la requête mysql sera dans le cache et sera censément traitée rapidement.

Je vais tenter les deux approches.

[KewlCat]

(...) refaire un mysql_query dans le script jpgraph afin de récupérer les data à tracer. L'avantage c'est que la requête mysql sera dans le cache et sera censément traitée rapidement

Et construire une vue dans ton script pour ensuite la consulter pour la construction des graphes dans la bibliothèque jpgraph ?

Le nom de la vue peut être dynamique et voyager en session (juste une chaîne : c'est mieux que 150Mo de données et c'est même mieux que les requêtes complètes)...

Tu peux détruire la vue à la fin de l'appel à jpgraph...

La consultation de la base sera bien plus rapide (opur peu que le mécanisme des vues de MySQL soit bien conçu )...

 

Note : remplacer "la vue" par "les vues" si tu souhaites en faire plusieurs, et (par exemple) préfixer leur nom de manière à retrouver laquelle correspond à quelles données

[Greywolf]

:-/ plusieurs variables de session font swapper mon serveur...

 

m'en vais regarder du côté des vues avec une nouvelle requête mysql

[Greywolf]

bon, j'améliore doucement les choses.

Je stocke la requête construite pour chaque analyseur dans une variable de session dont je récupère la partie WHERE .... dans le script jpgraph et en fonction des choix de l'utilisateur, je fais les SELECT qui vont bien.

 

Du coup, j'ai mis de côté la fonction FetchAllData() pour réaliser un

while ($row = mysql_fetch_array($mysql_result, MYSQL_ASSOC)) {
 fprintf($fp,"%s\t%s\t%s\t%s\t%s\t%.3f\t%.3f\t%.3f\t%s\n", \
  $row["travel_date"],$row["travel_month"],$row["travel_type"],$row["section_type"],\
  $row["my_hour"],$row["count"],$row["period"]);
 array_push($data,$row["count"]);
}

 

$mysql_result est issue de mysql_query($query); qui est exécutée si il existe des enregistrements correspondant aux critères (SELECT COUNT)

Donc cette boucle while est censée m'écrire dans un fichier txt en local sur le serveur les différentes valeurs retournées par la requête et remplir un tableau de data pour une colonne en particulier.

 

Si je ne commente pas la fonction fprintf, je fais planter mon navigateur La charge en mémoire virtuelle ne cesse d'augmenter jusqu'à faire planter le navigateur.

Le php étant exécuté côté serveur pourquoi la charge du client augmente-t-elle?

[KewlCat]

Il y a des données qui sont envoyées au navigateur pendant qu'il explose les compteurs ??