Optimisation de requêtesRésolu

Question

Bonjour tout le monde!
Dans le cadre d'un projet je dois r&#233;aliser des statistiques sous forme de graphiques et radars.
Pour ceci j'utilise JpGraph (j'ai aussi essay&#233; ChartDirector)
Mais j'ai un gros soucis, car la requ&#234;te que 'jeffectue &#224; l'air de faire flancher le serveur (Xeon 700) sur lequel je travaille.

Je dois faire des moyennes sur des mesures inscrites en base tous les 1/4 heure :

<?
 $dateToday =   date("Y-m-d");
 $annee =   date("Y");
 $mois =   date("m");
 $jour =   date("d");
 $heure =   date("H");
 $minute =   date("i");
 $seconde =   date("d");
 $s =   0;
 $heureTotale =  date("H:i:s");

 //Etablit la connexion avec le serveur de base de donn&#233;es, ainsi qu'&#224; la base
 server_connect("localhost","","");
 //Etablit la connexion &#224; la base de donn&#233;es
 bdd_connect("theBDD");

for ($h=12;$h <= $heure;$h++)
{
 for ($m=0;$m <= 45;$m=$m+15)
 {
  $h1 = $h + 1;
  $m0 = ":0";
  $s0 = ":0";
  $m1 = $m + 15;
  $heureprecedente = $h .":". $m . $s0;
  $heuresuivante = $h .":". $m1 . $s0;
  if ($m < 45)
  { 
   $query = 'select AVG(ms.MESURE_RAM), AVG(ms.MESURE_CPU), AVG(mme.TR), AVG(l.TR)'
     . ' FROM mesure_syst ms, mesure m, mesure_meta mme, mesure_lan l'
     . ' WHERE m.ID_MESURE = ms.ID_MESURE'
     . ' AND m.ID_MESURE = mme.ID_MESURE'
     . ' AND m.ID_MESURE = l.ID_MESURE '
     . ' AND m.DATE_MESURE = "' . $dateToday .  '" '
     . ' AND m.HEURE_MESURE between "' . $heureprecedente . '" and "' . $heuresuivante . '" ';
  }
  else
  { 
   $heureprecedente = $h .":". $m . $s0;
   $m1 = 59;
   $heuresuivante = $h .":". $m1 . $s0;
   $query = 'select AVG(ms.MESURE_RAM), AVG(ms.MESURE_CPU), AVG(mme.TR), AVG(l.TR)'
     . ' FROM mesure_syst ms, mesure m, mesure_meta mme, mesure_lan l'
     . ' WHERE m.ID_MESURE = ms.ID_MESURE'
     . ' AND m.ID_MESURE = mme.ID_MESURE'
     . ' AND m.ID_MESURE = l.ID_MESURE '
     . ' AND m.DATE_MESURE = "' . $dateToday .  '" '
     . ' AND m.HEURE_MESURE between "' . $heureprecedente . '" and "' . $heuresuivante . '" ';
  }
   $resultQuery = mysql_query($query);
   $rowQuery = mysql_fetch_row($resultQuery);
   $row1[$h] = $rowQuery[0]/1000;
   $row2[$h] = $rowQuery[1];
   $row3[$h] = $rowQuery[2];
   $row4[$h] = $rowQuery[3];

  $tx_RAM[$h] = $row1[0];
  $tx_CPU[$h] = $row2[0];
  $tx_META[$h] = $row3[$h];
  $tx_LAN[$h] = $row4[$h];

  echo("Charge RAM en performance entre $heureprecedente et $heuresuivante :  $tx_RAM[$h]  ");
  echo("Charge CPU moyenne en performance entre $heureprecedente et $heuresuivante : $tx_CPU[$h]");
  echo("Temps de r&#233;ponse moyen en performance M entre $heureprecedente et $heuresuivante : $row3[$h]");
  echo("Temps de r&#233;ponse moyen LAN en performance entre $heureprecedente et $heuresuivante : $row4[$h]");
 
 }
}

// ensuite vient l'affichage....... (je ne vais pas d&#233;crire la suite....)
?>

Apparemment le serveur flanche, car quand l'heure (c'est &#224; dire la variable $heure) est inf&#233;rieure &#224; 12, &#231;a fonctionne, mais apr&#232;s :
Fatal error: Maximum execution time of 30 seconds exceeded in d:\www\reporting\ben\ebl\courbes_4s.php on line 79

Avec la ligne 79 : $resultQuery  = mysql_query($query);

Pouvez vous m'aider s'il vous pla&#238;t?
Ya t'il un moyens d'optimiser la requ&#234;te? Ou m&#234;me (solution pir) de fixer le temps d'excution de la requ&#234;te &#224; un temps sup&#233;rieur que 30 secondes ??

cs_Anthomicro · Accepted Answer

Rajoute un index dans tes trois tables sur le champ "ID_MESURE" Ensuite rajoute un index DANS la table mesure sur le champ DATE_MESURE et sur le champ HEURE_MESURE ta requête devrait s'en trouver accélérée énormément vu le nombre d'enregistrements que t'as dans ta table. Une fois les index rajoutés, exécute ta requête en rajoutant un EXPLAIN devant (dans phpmyadmin) et copie le tableau que tu obtiens ici, pour qu'on voit si l'optimisation a porté ses fruits. a +

Entraide, dépannage et vulgarisation informatique: Mon site de vulgarisation informatique

cs_Anthomicro · Answer

Salut, tu peux nous coller les structures des tables suivantes : mesure_syst ms, mesure m, mesure_meta mme, mesure_lan l stp ? merci

Entraide, dépannage et vulgarisation informatique: Mon site de vulgarisation informatique

zeldoi5 · Answer

Merci de ton intérêt (désolé du retard pour répondre, mais j'ai perdu ma synchro hier...) Alors je reprends Tu veux les structures de mes tables, les voici : Pour la table mesure_syst : Pour la table mesure : Pour la table mesure_lan : int(11) ID_MESUREID_MESURE_LAN ID_MESURE int(11) ID_MESURE int(11) ID_MESURE int(11) MESURE_CPU double HEURE_MESURE time TR float MESURE_RAM double DATE_MESURE date RAM_SIZE double Pour la table mesure_meta : ID_MESURE_META int(10) ID_MESURE int(11) TR float Je fais mes jointures sur les champs en italique, et je relève les champs en gras. Voilà. Sinon j'ai augmenté le temps d'excution du script php (par max_execution_time(60) dans php.ini) ou j'aurai pu mettre dans le script set_limit_time(30) ...) ca permet d'executer les requêtes.... Mais ça n'est pas optimiser....

cs_Anthomicro · Answer

Non je voudrais la structure du genre ça : CREATE TABLE `connectes` ( `ip` int(9) unsigned NOT NULL default '0', `pseudo` varchar(20) NOT NULL default '', `temps` int(10) unsigned NOT NULL default '0', `page_titre` varchar(100) NOT NULL default '', `page_url` varchar(200) NOT NULL default '', UNIQUE KEY `ip` (`ip`) ) TYPE=HEAP;

Entraide, dépannage et vulgarisation informatique: Mon site de vulgarisation informatique

zeldoi5 · Answer

Ben je ne peux pas trop te dire, car les tables sont déja crées en dur dans la base... Néanmoins, je te fais un copié collé : table Mesure : Champ | Type | Attributs | Null | Défaut | Extra | Action | ---- , ID_MESURE, int(11), , Non, 0 , DATE_MESURE, date, , Non, 0000-00-00 , HEURE_MESURE, time, , Non, 00:00:00 Table Mesure_lan : ID_MESURE_LAN, int(10) , UNSIGNED, Non, , auto_increment ID_MESURE, int(10) , UNSIGNED, Non, 0 TR, float, , Non, 0 Table Mesure_syst : ID_MESURE_SYST, int(11), , Non, , auto_increment ID_MESURE, int(11), , Non, 0 MESURE_CPU, double, , Non, 0 MESURE_RAM, double, , Non, 0 Table Mesure_meta : ID_MESURE_META, int(10) , UNSIGNED, Non, , auto_increment ID_MESURE, int(10) , UNSIGNED, Non, 0 TR, float, , Non, 0 C'est tout ce que je peux avoir....

cs_Anthomicro · Answer

Dans phpmyadmin tu peux avoir accès à la structure de la table, tu cliques sur "exporter" tu coches "structure" et tu décoche "transmettre".

Entraide, dépannage et vulgarisation informatique: Mon site de vulgarisation informatique

zeldoi5 · Answer

Ok!
D&#233;sol&#233; je ne savais pas :(

CREATE TABLE `mesure` (
  `ID_MESURE` int(11) NOT NULL default '0',
  `ID_SONDE` int(11) NOT NULL default '0',
  `DATE_MESURE` date NOT NULL default '0000-00-00',
  `HEURE_MESURE` time NOT NULL default '00:00:00',
  PRIMARY KEY  (`ID_MESURE`),
  KEY `DATE_MESURE` (`DATE_MESURE`)
) TYPE=MyISAM;
    
CREATE TABLE `mesure_lan` (
  `ID_MESURE_LAN` int(10) unsigned NOT NULL auto_increment,
  `ID_MESURE` int(10) unsigned NOT NULL default '0',
  `TR` float NOT NULL default '0',
  PRIMARY KEY  (`ID_MESURE_LAN`)
) TYPE=MyISAM AUTO_INCREMENT=39858 ;
    
CREATE TABLE `mesure_meta` (
  `ID_MESURE_META` int(10) unsigned NOT NULL auto_increment,
  `ID_MESURE` int(10) unsigned NOT NULL default '0',
  `TR` float NOT NULL default '0',
  PRIMARY KEY  (`ID_MESURE_META`)
) TYPE=MyISAM AUTO_INCREMENT=39860 ;
    
CREATE TABLE `mesure_syst` (
  `ID_MESURE_SYST` int(11) NOT NULL auto_increment,
  `ID_MESURE` int(11) NOT NULL default '0',
  `MESURE_CPU` double NOT NULL default '0',
  `MESURE_RAM` double NOT NULL default '0',
  `RAM_SIZE` double NOT NULL default '0',
  PRIMARY KEY  (`ID_MESURE_SYST`)
) TYPE=MyISAM AUTO_INCREMENT=53720 ;

malalam · Answer

Hello,



deja tu devrais reduire tes INT(11) et INT(10).

Enfin...sauf si tu comptes utiliser des nombres de 11 chiffres...ce qui m'etonnerait vu que php n'aimerait pas ca.

zeldoi5 · Answer

Ah ben je te le dis, l'execution est SUPER TOP am&#233;lior&#233;e!
Par contre j'ai besoin de votre aide, j'ai une requ&#234;te qui merdouille :

La req&#234;te qui me test entre x:45 et x:59 (avec x une heure) ne fonctionne pas, la voici :
 

$query = 'select AVG(ms.MESURE_RAM), AVG(ms.MESURE_CPU), AVG(mme.TR), AVG(l.TR)' 
             . ' FROM mesure_syst ms, mesure m, mesure_meta mme, mesure_lan l' 
             . ' INNER JOIN m.ID_MESURE = ms.ID_MESURE' 
             . ' INNER JOIN  m.ID_MESURE = mme.ID_MESURE' 
             . ' INNER JOIN  m.ID_MESURE = l.ID_MESURE ' 
             . ' WHERE m.DATE_MESURE = "' . $dateToday .  '" ' 
             . ' AND m.HEURE_MESURE between "' . $heureprecedente . '" and "' . $heuresuivante . '" ';

Surtout que je l'ai essay&#233; sur PhpMyAdmin (en changeant les inner join, sinon &#231;a marche pas  ) et &#231;a fonctionne... Alors je ne sais plus quoi penser.... 
La voici en "format" phpMyAdmin.....
 

SELECT AVG( MESURE_RAM ) , AVG( MESURE_CPU ) , AVG( mesure_lan.TR ) , AVG(mesure_meta.TR ) 
FROM  `mesure_syst` ,  `mesure` ,  `mesure_meta` ,  `mesure_lan` 
WHERE mesure.ID_MESURE = mesure_syst.ID_MESURE 
AND mesure.ID_MESURE = mesure_meta.ID_MESURE 
AND mesure.ID_MESURE = mesure_lan.ID_MESURE 
AND mesure.DATE_MESURE =  "2005-04-29" 
AND mesure.HEURE_MESURE 
BETWEEN  "06:45:00" AND  "06:59:00"

Vous avez pas une id&#233;e?

cs_Anthomicro · Answer

Si elle fonctionne sous phpmyadmin c'est qu'il faut vérifier en php le contenu des variables que tu mets dans la requête, si tu as bien mis des quotes partout pour entourer les dates, etc... Poste le EXPLAIN requête stp, qu'on voit si y'a moyen d'optimiser encore. a +

Entraide, dépannage et vulgarisation informatique: Mon site de vulgarisation informatique

zeldoi5 · Answer

D'abord, merci de te pencher sur mes probl&#232;mes.

Ensuite voici l'execution du explain request :

EXPLAIN SELECT AVG( MESURE_RAM ) , AVG( MESURE_CPU ) , AVG( mesure_lan.TR ) , AVG( mesure_meta.TR ) 
FROM `mesure_syst` , `mesure` , `mesure_meta` , `mesure_lan` WHERE mesure.ID_MESURE mesure_syst.ID_MESURE  AND  mesure.ID_MESURE  mesure_meta.ID_MESURE AND mesure.ID_MESURE =  mesure_lan.ID_MESURE  AND  mesure.DATE_MESURE  = "2005-04-29" AND mesure.HEURE_MESURE
BETWEEN "06:45:00" AND "06:59:00"
 

R&#233;sultat :


table |
type |
possible_keys |
key |
key_len |
ref |
rows |
Extra |
----

mesure,
ref,
PRIMARY,DATE_MESURE,ID_MESURE,
DATE_MESURE,
3,
const,
68,
Using where,
----

mesure_syst,
ref,
ID_MESURE,ID_MESURE_2,
ID_MESURE,
4,
mesure.ID_MESURE,
1,
,
----

mesure_meta,
ref,
ID_MESURE,ID_MESURE_2,
ID_MESURE,
4,
mesure.ID_MESURE,
1,
Using where,
----

mesure_lan,
ref,
ID_MESURE,ID_MESURE_2,
ID_MESURE,
4,
mesure.ID_MESURE,
1,
Using where

cs_Anthomicro · Answer

Ok c'est parfait ;-) rien à changer

Entraide, dépannage et vulgarisation informatique: Mon site de vulgarisation informatique

zeldoi5 · Answer

J'ai fait un :
$resultQuery = mysql_query($query) or die("il y a une erreur ici !");

Pour voir si il n'y avait pas d'erreur...
Et il y en a une!!!
Voici l'affichage :

Requ&#234;te effectuee : select AVG(MESURE_RAM), AVG(MESURE_CPU), AVG(TR), AVG(TR) FROM mesure_syst ms INNER JOIN mesure m ON m.ID_MESURE ms.ID_MESURE INNER JOIN mesure_meta mme ON m.ID_MESURE mme.ID_MESURE INNER JOIN mesure_lan l ON m.ID_MESURE = l.ID_MESURE WHERE m.DATE_MESURE = "2005-04-29" AND m.HEURE_MESURE between "00:45:00" and "00:59:00" il y a une erreur ici !

J'y comprends rien!
C'est la m&#234;me que pr&#233;c&#233;demment avec les champs de l'heure qui changent....

cs_Anthomicro · Answer

Quel est le message d'erreur précis : $resultQuery = mysql_query($query) or die(mysql_error());

Entraide, dépannage et vulgarisation informatique: Mon site de vulgarisation informatique

zeldoi5 · Answer

avec la requ&#234;te suivante : 


Code:
,
----

      $query = 'select AVG(MESURE_RAM), AVG(MESURE_CPU), AVG(me.TR), AVG(l.TR)'
. ' FROM mesure_syst ms, mesure m, mesure_meta mme, mesure_lan l'
. ' INNER JOIN mesure m ' .
' ON m.ID_MESURE = ms.ID_MESURE'
. ' INNER JOIN mesure_meta mme' .
' ON m.ID_MESURE = mme.ID_MESURE'
. ' INNER JOIN mesure_lan l' .
' ON m.ID_MESURE = l.ID_MESURE '
. ' WHERE m.DATE_MESURE = "' . $dateToday .  '" '
             . ' AND m.HEURE_MESURE between "' . $heureprecedente . '" and "' . $heuresuivante . '" '; 

Il m'accepte pas mes alias :

Not unique table/alias: 'm'

cs_Anthomicro · Answer

SELECT AVG(ms.MESURE_RAM), AVG(ms.MESURE_CPU), AVG(me.TR), AVG(l.TR) FROM mesure_syst ms,mesure_meta mme, mesure_lan l INNER JOIN mesure m ON m.ID_MESURE ms.ID_MESURE INNER JOIN mesure_meta mme ON m.ID_MESURE mme.ID_MESURE INNER JOIN mesure_lan l ON m.ID_MESURE = l.ID_MESURE WHERE m.DATE_MESURE = "' . $dateToday . '" AND m.HEURE_MESURE between "' . $heureprecedente . '" and "' . $heuresuivante . '"'; là ça fonctionne ?

Entraide, dépannage et vulgarisation informatique: Mon site de vulgarisation informatique

cs_Anthomicro · Answer

$query='SELECT AVG(ms.MESURE_RAM), AVG(ms.MESURE_CPU), AVG(me.TR), AVG(l.TR) FROM mesure_syst ms,mesure_meta mme, mesure_lan l INNER JOIN mesure m ON m.ID_MESURE ms.ID_MESURE INNER JOIN mesure_meta mme ON m.ID_MESURE mme.ID_MESURE INNER JOIN mesure_lan l ON m.ID_MESURE = l.ID_MESURE WHERE m.DATE_MESURE = "' . $dateToday . '" AND m.HEURE_MESURE between "' . $heureprecedente . '" and "' . $heuresuivante . '"'; ^^

Entraide, dépannage et vulgarisation informatique: Mon site de vulgarisation informatique

zeldoi5 · Answer

Ben je vauis essayer, mais comme le serveur mettait 3plombes &#224; r&#233;pondre.... (apparemment 200 Server...) et bien j'ai d&#233;cid&#233; de le rebooter!
Donc une fois reboot&#233;, je test!
D&#233;sol&#233; pour l'attente!

zeldoi5 · Answer

Apr&#232;s plein de reboot &#224; cause d'une requ&#234;te SQL qui tournait en boucle...
J'ai d&#233;cid&#233; de virer les INNER JOIN
et cette requ&#234;te semble fonctionner :

$query = 'select AVG(S.MESURE_RAM) , AVG(S.MESURE_CPU),AVG(E.TR),AVG(L.TR) '
     . ' FROM mesure_syst S,mesure M,mesure_meta E,mesure_lan L'
     . ' WHERE M.ID_MESURE = S.ID_MESURE'
     . ' AND M.ID_MESURE = E.ID_MESURE'
     . ' AND M.ID_MESURE = L.ID_MESURE'
     . ' AND M.DATE_MESURE = "' . $dateToday .  '" '
     . ' AND M.HEURE_MESURE between "' . $heureprecedente . '" and "' . $heuresuivante . '" ';

Autre soucis qui s'est r&#233;vel&#233;, c'est un probl&#232;me d'alias, j'ai donc choisi de ne mettre qu'une lettre en alias....

cs_Anthomicro · Answer

Ok du moment que t'as laissé les indexes c'est bon ;-)

Entraide, dépannage et vulgarisation informatique: Mon site de vulgarisation informatique

zeldoi5 · Answer

Au fait : MERCI BEAUCOUP!!!!!!!!!!!!!!!!!!

cs_Anthomicro · Answer

Tu peux cliquer sur "réponse acceptée" stp ? (sur les posts t'ayant aidé à résoudre ton problème) a +

Entraide, dépannage et vulgarisation informatique: Mon site de vulgarisation informatique

zeldoi5 · Answer

Voici c'est fait!

Bon week end!

cs_Anthomicro · Answer

Entraide, dépannage et vulgarisation informatique: Mon site de vulgarisation informatique

Optimisation de requêtes

24 réponses

Votre réponse

Discussions similaires