Optimierung von grosser MySQL Abfrage

[Chris]

Guten Morgen zusammen,

ich sitze nun die ganze Nacht an einer Abfrage die mich zum verzweifeln bringt ;(

Ich habe eine sehr grosse DB (MySQL) in der Hoster stecken, diese Hoster werden regelmässig angepingt, die Ergebnisse werden wiederum gespeichert.
Nun möchte ich alle Hoster Rausfiltern die Laut dem Ping Ergebniss über 99% liegen.
Funktionnieren tut das Script nur leider mir sehr sehr grosser Serverlast (CPU 90%) loadaverage beinahe 10.
Das Ping Ergebniss begrenzt sich auf 0=erfolgreich 1=erfolglos.

Ich suche nach einer Möglichkeit nach z.B. jeder 20 Abfrage in der Whileschleife eine Ruhephase im Script einzubauen um dem Server Zeit zur Erholung zu geben.

Ich habe euch die Schleife mitgesendet, ich bin euch für jede Art der Verbesserung dankbar.

Die Schleife:

$data=mysql_query("SELECT hoster_id FROM watch GROUP BY hoster_id ORDER by hoster_id LIMIT ".$_POST["von"].",".$_POST["max"]." ");

while($row=mysql_fetch_array($data))
 {

$offline  = mysql_query("SELECT id FROM watch WHERE hoster_id='$row[hoster_id]' AND ping='0' ORDER by hoster_id LIMIT ".$_POST["von"].",".$_POST["max"]." ");
   $total    = mysql_query("SELECT id FROM watch WHERE hoster_id='$row[hoster_id]' ORDER by hoster_id LIMIT ".$_POST["von"].",".$_POST["max"]." ");

$gesamt   = mysql_num_rows($offline);
   $erreicht = mysql_num_rows($total);
   $prozent  = $gesamt*100/$erreicht;

if($erreicht!='' && $gesamt!='' && $prozent > "99"){
     $bcc   .= "$row[hoster_id]\n";
     $i++;
   }

free($offline,$total);
  clear($gesamt,$erreicht,$prozent);

}

Die Hauptlast liegt im SELECTEN der einzellnen Online und Offline Ergebnisse der einzellnen Hoster ...

Ich danke euch für eure Hilfe ...

Viele Grüße

Chris

[Daniela Koller]

Hi Chris

$data=mysql_query("SELECT hoster_id FROM watch GROUP BY hoster_id ORDER by hoster_id LIMIT ".$_POST["von"].",".$_POST["max"]." ");

Die erste Query ist nicht wirklich richtig, was macht da ein Group By? In diesem Zusammenhang tut der gar nichts ausser vielleicht das was ein Distinct hier tun sollte (wenn der nötig ist, solltest du dein DB-Design überdenken).

Die Hauptlast liegt im SELECTEN der einzellnen Online und Offline Ergebnisse der einzellnen Hoster ...

Ich würde es anders lösen (deinen Limit-Teil hab ich mal
weggelassen, den einfach wieder anfügen):

Du brauchst nur 2 Queries:

Zuerst holst du die Anzahl der Ausfälle pro Hoster indem du die beiden Tabellen hoster und watch zusammen joinst:

SELECT hoster.hoster_id, count(id) as ausfaelle
  FROM hoster
    INNER JOIN watch ON(watch.hoster_id = hoster.hoster_id)
  WHERE watch.ping = '0'
  ORDER by hoster.hoster_id

Achja, warum ist ping ein String? Wenn es keiner ist, gehören da auch keine ' um die 0 rum.

Als zweites holst du analog die Anzahl der Ping-Anfragen. Dazu kannst du einfach die Where-Bedingung der ersten Anfrage weglassen.

Erst jetzt fängst du an mit deinem Loop. Du fügst jeweils die beiden passenden Einträge zusammen. Für $gesamt und $erreicht musst du jetzt die beiden count Werte aus dem Ergebnis auslesen. Deswegen unbedingt beiden mit as einen Namen geben. Vorsicht, die beiden Listen haben nicht dieselbe Länge falls ein Hoster keinen einzigen Ausfall hatte. Das musst du umgehen indem du jeweils die hoster_id prüfst und allenfalls ein 0 bei den Ausfällen einfügst. Für die Loop-Bedingung nimmst du die längere Liste, also die in der alle Pings stehen.

Weiter wichtig sind Indizes. Ich nehme mal an hoster.hoster_id ist Primärschlüssel, deswegen hat das automatisch einen Index. Was hier wichtig ist, ist ein Index über watch.hoster_id.

Noch etwas zu deinem Limit: Da direkt die HTTP-Parameter zu verwenden ist gefährlich. Die solltest du in jedem Fall Escapen, dazu gibt es eine PHP-Funktion deren Name ich gerade nicht weis.

Das ganze wäre mit CASE in einer einzigen Query zu lösen, afaik beherrscht das MySQL jedoch nicht.

Gruss Daniela

[Chris]

Hi Daniela,

danke Dir für Deine Hilfe.
Ich habe nun die Struktur der DB ein wenig angepasst (index auf hoster_id) und siehe da die Abfrage dauert knapp 2-3 sec ...
Das ist mal ein Fortschritt, die verkürzte Abfrage habe ich gleich mit eingebaut und es sieht wesentlich schöner im Code aus und funktioniert besser als vorher ;)

Ich danke Dir und wünsche Dir noch einen schönen Tag

Chris