[php] Wie leistungsstart ist PHP_MySQL

[Lutz Zetzsche]

Hi René,

Am Sonntag, 29. Oktober 2006 03:15 schrieb René Thiel:
> > Wird jede Zeile als einzelnes Insert an MySQL geschickt?
> > Wenn ja, kann man da sicher optimieren, entweder prepared Statement
> > benutzen oder mehrere Datensätze gleichzeitig eintragen.
> > (Beispiel: INSERT INTO table (id, name) VALUES (1, 'wert1'), (2,
> > 'wert'), ...)
>
> Diese Idee hatte ich auch schon, bringt bei 41.000 Datensätzen einen
> Geschwindigkeitsvorteil von ca. 30%.

30% sind schon mal eine erhebliche Verbesserung. :-)

> Das Problem sind aber die Log-Dateien mit fast 1.000.000 Zeilen
> (wie schon geschrieben dauert es schon 336 sec, um nur die einzelnen
> Zeilen unbearbeitet in die DB zu schreiben.)
>
> > Was Optimierung angeht kann ich ohne deinen Code zu kennen nur
> > raten. Was ist den das eigentliche Problem mit der Geschwindigkeit,
> > woher kommen diese 600 Sekunden?
>
> <?php
> .....
>      $sql  = "INSERT INTO `".$Jahr."_".$Standort."` ( ";
>      $sql .= "  `DateTime` ";
>      $sql .= ", `IP` ";
>      $sql .= ", `Call` ";
>      $sql .= ", `Typ` ";
>      $sql .= ", `Verbindungen` ";
>      $sql .= ", `Kommentar` ";
>      $sql .= ") VALUES ";
>
>      foreach ($teile as $buffer)
>      {
>       if (preg_match("'( CALL )'",$buffer))
>       {
>        $teiler_1 = split(" ",$buffer);
>        $DateTime = $Jahr."-".$month[$teiler_1[0]]."-".$teiler_1[1]."
> ".$teiler_1[2]; $IP = $teiler_1[3];
>        $Call = $teiler_1[4]." ".$teiler_1[5];
>        $Typ = $teiler_1[6];
>        $entferner = "/".$teiler_1[0]." ".$teiler_1[1]."
> ".$teiler_1[2]." ".$teiler_1[3]." ".$teiler_1[4]." ".$teiler_1[5]."
> ".$teiler_1[6]."/";
>        $teiler_3 = ltrim(preg_replace($entferner,"",$buffer));
>        $teiler_4 = split(" ",$teiler_3);
>        $Verbindungen = $teiler_4[0];
>        $Verbindungen_x = "'".quotemeta($Verbindungen)."'";
>        $Verbindungen = mysql_real_escape_string($Verbindungen);
>        $Kommentar =
> ltrim(@preg_replace($Verbindungen_x,"",$teiler_3)); $Kommentar =
> mysql_real_escape_string($Kommentar);
>
>        if ($Zaehler > 0) {$sql .= ", ";}
>        $Zaehler++;
>        $sql .= "( ";
>        $sql .= "  '".$DateTime."' ";
>        $sql .= ", '".$IP."' ";
>        $sql .= ", '".$Call."' ";
>        $sql .= ", '".$Typ."' ";
>        $sql .= ", '".$Verbindungen."' ";
>        $sql .= ", '".$Kommentar."' ";
>        $sql .= ") ";
>       }
>      }
>      $ergebnis = @mysql_query($sql, $verbindung);
> .....
> ?>
>
> Aus:
> Jul 31 12:26:31 172.16.199.1 CALL 3 A:Disc  523->808704
> G729AB,60(56,3)/G729AB,0(0,0) TEL2:523:->GW1:808704: Cause: Normal
> call clearing
> mache ich damit:
> INSERT INTO `2006_816` (`DateTime`, `IP`, `Call`, `Typ`,
> `Verbindungen`, `Kommentar`) VALUES ('2006-07-31 12:26:31',
> '172.16.199.1', 'CALL 3', 'A:Disc', '523->808704',
> 'G729AB,60(56,3)/G729AB,0(0,0) TEL2:523:->GW1:808704: Cause: Normal
> call clearing'), ...

Es wäre sehr wichtig, Dein komplettes Skript zu kennen und genau zu 
messen, an welchen Stellen wieviel Zeit gebraucht wird. Nur so wirst Du 
die eigentlichen Zeitfresser entdecken.

Es fängt mit solchen Kleinigkeiten wie
	preg_match("'( CALL )'",$buffer)
an, wo Du halt anstatt eines regulären Ausdruckes in diesem Fall viel 
lieber strpos() oder strstr() verwenden solltest.

> > Wenn die Auswertung jahresweise geschieht, wird das Script ja nicht
> > sehr häufig aufgerufen
>
> Richtig, es geht hier nur um das Einlesen der Log-Files und deren
> Auswertung, die Anzeige der ausgewerteten Daten (inkl. Tortengrafik)
> ist unproblematisch.
>
> Allerdings sprechen wir über 19 Standorte und (bisher) 26 Monate -
> das dauert schon ein paar Tage.
>
> > wenn der Kunde im nächsten Jahr doppelt soviele Logfiles hat, muss
> > das auch irgendwie gehen.
>
> Nicht doppelt soviele Logfiles, aber wahrscheinlich noch größere -
> und genau deshalb meine Frage: Habe ich denen zu viel versprochen?

Also es gibt ganz grundsätzliche Lösungsansätze. Da Du keine Details 
genannt hast, führe ich sie einfach mal auf. Vielleicht hast Du sie ja 
schon umgesetzt, aber für alle Fälle... :-)

1. Logrotate verwenden und damit die Logs häufig rotieren, z.B. einmal 
die Woche. Dann hast Du pro Woche eine Log-Datei. Damit werden die 
einzelnen Dateien handhabbarer.

2. Anstatt die Log-Dateien einmal im Jahr in die Datenbank einzulesen, 
die Dateien sofort nach der Rotation einlesen.

3. Nur Log-Dateien einlesen, die noch nicht eingelesen wurden.

D.h. der erste logische Ansatzpunkt ist, die zu verarbeitende Datenmenge 
so zu verringern, daß sie handhabbarer wird.

Die nächsten Ansatzpunkte wäre dann schon im Skript. Exakte 
Zeitmessungen, wo die Zeit liegen bleibt, wären daher sehr wichtig. Aus 
eigener Erfahrung kann ich Dir sagen, daß man in einem Skript 
tatsächlich oft exakt einzelne Funktionen als Zeitfresser 
identifizieren kann. An diesen Stellen kannst Du dann ansetzen und nach 
alternativen Programmierlösungen suchen, die performanter sind. Bei 
großen Datenmengen wirkt sich das dann merklich aus. Grundsätzliche 
Empfehlungen wären hier, was Du ja sicher ohnehin schon machst:

Lies die Log-Dateien immer nur Zeilenweise ein und behalte nur soviel 
Daten im Speicher, wie Du gerade aktuell verarbeiten mußt. Also ein 
Minimum an Resourcen einsetzen.

Ein Flaschenhals Deines Ansatzes scheint die Datenbankverbindung zu sein 
(TCP/IP-Verbindungen auf einen anderen Rechner sind hier übrigens 
langsamer, als Localhost-Socket-Verbindungen). Daher habe ich einen Tip 
für Dich. Ich habe letztes Jahr einmal vor einem ähnlichen Problem 
gestanden, obwohl es da noch um weit geringere Datenmenge ging. Damals 
habe ich eine Lösung gefunden, um Daten in die Datenbank zu bekommen, 
die gegenüber INSERTs wirklich extrem performant ist. Diese Lösung 
sollte Dein Skript deutlich beschleunigen und geht so:

Anstatt die Daten mit INSERTs in die Datenbank zu schreiben, liest Du 
die Daten zeilenweise aus der Log-Datei aus und schreibst sie im 
gleichen Augenblick auseinandergenommen zeilenweise in eine andere 
csv-ähnliche Textdatei weg, wobei Du eine bestimmte Syntax einhältst. 
So hast Du nebenbei immer nur genau eine Zeile im Speicher. Wenn Du die 
Textdatei fertig geschrieben hast, liest Du sie mit einem einzigen 
SQL-Befehl, LOAD DATA INFILE, in die Datenbank ein:

	http://dev.mysql.com/doc/refman/4.1/en/load-data.html

Ich kann Dir versprechen, daß die Daten mit einer Dir bisher 
unbekannten, unglaublichen Geschwindigkeit eingelesen werden... ;-) 
Beachte allerdings, daß hier spezielle Rechte benötigt werden:

"For security reasons, when reading text files located on the server, 
the files must either reside in the database directory or be readable 
by all. Also, to use LOAD DATA INFILE on server files, you must have 
the FILE privilege. See Section 5.7.3, 'Privileges Provided by MySQL'"

Wenn Du alle diese Tricks ausgeschöpft hast, kannst Du dann noch ein 
Bißchen am Skriptparameter max_execution_time schrauben, z.B. auch mit 
set_time_limit().

Zusätzlich könntest Du mal gucken, ob das PHP-Skript schneller läuft, 
wenn Du es nicht über den Browser, sondern über die Kommandozeile 
ausführst. Kommandozeile ist übrigens ein gutes Stichwort:

Wenn Du die von mir vorgeschlagenen Lösungen implementierst, dann 
brauchst Du übrigens kein PHP-Skript. Ich habe das damals mit einem 
Shellskript gelöst. Das Shellskript kann den LOAD DATA INFILE-Befehl 
nämlich über den mysql-Befehl absetzen und dabei die Benutzerkennung 
mitgeben. :-) In dem Zusammenhang kannst Du auch über Cronjobs 
nachdenken, die nach dem von mir erwähnten Logrotate dann automatisch 
die Daten in die Datenbank schreiben. (PHP über CLI hat übrigens 
standardmäßig eine unbegrenzte max_execution_time.)

Wie Du siehst, gibt es also glücklicherweise eine Menge Ansätze, der 
Datenmenge Herr zu werden. Ich denke also nicht, daß Du Deinem Kunden 
zuviel versprochen hast. :-)


Viele Grüße
Lutz