und SQL: Routine performanter machen

[Mike©]

Moin,

ich habe eine kleine Routine, welche ein Verzeichnis mit ca. 120.000 Dateien durchsucht und prüft ob die Dateinamen noch in der DB gespeichert sind.

Das Script läuft über 1 Stunde, was bei der Datenmenge nicht verwunderlich ist. Allerdings glaube ich, das man vielleicht die Routine noch optimieren könnte.

Habt Ihr vielleicht Verbesserungsvorschläge, welche sich auf die Laufzeit auswirken?

while ( $file = readdir ($hDir) )
      {
        if ( ($file != ".") && ($file != "..") )
           {
             $strSQL="select count(*) as Found from tbl_images WHERE file_name='".$file."'";
             $hErgebnis = @mssql_query($strSQL, $hVerbindung);

$result0 = mssql_fetch_array($hErgebnis);
             $Found = $result0['Found'] ;

if ( $Found == 0 )
                {
                  $File_Path=sprintf("./../data/cia_images/%s",$file);
                  @unlink($File_Path);
                }
          }
      }

Danke & regds
Mike©

[Cheatah]

Hi,

Habt Ihr vielleicht Verbesserungsvorschläge, welche sich auf die Laufzeit auswirken?

vermeide zunächst einmal, in einer Schleife DB-Selects abzufeuern. Bei 120.000 Daten könnte es zwar Speicherprobleme geben, aber ein Weg wäre es, zunächst einmal ein Array mit den Werten aufzubauen, dann ein einziges Select über _alle_ file_name-Werte auszuführen, die gefundenen Werte aus dem Array zu entfernen und den Rest zu dumpen. Wenn der Speicher das nicht mitmacht, kannst Du etwas Ähnliches in Chunks von z.B. 1000 Stück ausführen - das reduziert die Selects auf zumindest 120 (immer noch viel, aber weniger als bisher ;-)

Cheatah

[Mike©]

Moin Cheatah,

vermeide zunächst einmal, in einer Schleife DB-Selects abzufeuern. Bei 120.000 Daten könnte es zwar Speicherprobleme geben, aber ein Weg wäre es, zunächst einmal ein Array mit den Werten aufzubauen, dann ein einziges Select über _alle_ file_name-Werte auszuführen, die gefundenen Werte aus dem Array zu entfernen und den

im Prinzip habe ich das verstanden, aber ich kann mir momentan gar nicht vorstellen wie die Abfrage aussehen soll.

Ich habe dann also ein Array mit 120.000 Feldern.
Ein Select mit einem Array? Wie könnte dieses Query aussehen?

regds
Mike©

[Cheatah]

Hi,

im Prinzip habe ich das verstanden, aber ich kann mir momentan gar nicht vorstellen wie die Abfrage aussehen soll.

"selektiere alle Dateinamen aus der Tabelle". Ganz einfach :-)

Ich habe dann also ein Array mit 120.000 Feldern.
Ein Select mit einem Array? Wie könnte dieses Query aussehen?

Nein, erst der Vergleich soll das Array beachten, nicht schon die Selektion. Es sei denn, Du meinst die Chunk-Variante, dort dachte ich an den IN-Operator.

Cheatah

[Mike©]

Moin,

"selektiere alle Dateinamen aus der Tabelle". Ganz einfach :-)

das bringt mich auf eine Idee. Ich lese die Dateinamen aus dem Verzeichnis in ein Array und alle Dateinamen aus der DB in ein Ergebnis Array ( 1 Query ). Nun muss ich nur die beiden Array vergleichend abarbeiten.

Das sollte doch dann viel schneller sein. Liege ich da richtig?

regds
Mike©

[Cheatah]

Hi,

das bringt mich auf eine Idee. Ich lese die Dateinamen aus dem Verzeichnis in ein Array und alle Dateinamen aus der DB in ein Ergebnis Array ( 1 Query ). Nun muss ich nur die beiden Array vergleichend abarbeiten.
Das sollte doch dann viel schneller sein. Liege ich da richtig?

nein, das ist exakt das, was ich vorgeschlagen habe, nur mit einer zusätzlichen Schleife (die DB-Daten in ein Array speichern) und viiieeel mehr Speicherbedarf :-) Du kannst bereits während des Select-Durchlaufs den Vergleich durchführen (subtrahierend).

Cheatah

[Mike©]

Moin Cheatah,

Das sollte doch dann viel schneller sein. Liege ich da richtig?

nein, das ist exakt das, was ich vorgeschlagen habe, nur mit einer zusätzlichen Schleife (die DB-Daten in ein Array speichern) und viiieeel mehr Speicherbedarf :-) Du kannst bereits während des Select-Durchlaufs den Vergleich durchführen (subtrahierend).

Danke, ich werde das morgen mal durch testen.

regds
Mike©

[Ilja]

yo,

um mal wieder den vorschlag von Cheatah aufzugreifen (habe scheinbar keine eigenen lösungen), würde ich nach dem alphabet vorgehen, da 120.000 Filenamen doch recht viel sind. sprich erst mal alle files, die mit A beginnen, dann mit B, etc.

das sollte sich recht gut in einer schleife (24 durchläufe) abbilden lassen und einen String für den NOT IN Operator aufbauen, wo eben alle Namen mit dem entsprechenden anfangsbuchstaben drinne stehen. dann die abfrage mit der zusätzlichen WHERE bedingung LIKE "anganfsbuchstabe%" abfeuern und das ergebnis ausgeben lassen.

SELECT filename
FROM tabelle
WHERE filename LIKE "A%"
AND filenname NOT IN ($string)

Ilja

[Blaubart]

Mahlzeit.

Um deine bisherige Herangehensweise zu optimieren, hab ich auch keine großartig anderen Vorschläge als die bisher gemachten. Allerdings hab ich eine Frage:

Soll dieses Skript in größeren zeitlichen Abständen "zum Aufräumen" laufen oder wie hast du das geplant? Offenbar werden ja im laufenden Betrieb Dateinamen aus der DB gelöscht. Bietet es sich da nicht an, auch gleich die Datei(en) zu löschen, anstatt eine Woche später 120.000 Einträge durchzuackern?

[Mike©]

Moin Blaubart,

Soll dieses Skript in größeren zeitlichen Abständen "zum Aufräumen" laufen oder wie hast du das geplant? Offenbar werden ja im laufenden Betrieb Dateinamen aus der DB gelöscht. Bietet es sich da nicht an, auch gleich die Datei(en) zu löschen, anstatt eine Woche später 120.000 Einträge durchzuackern?

also, die Routine läuft jede Nacht gegen 1:00 Uhr. Bei den Dateien handelt es sich um Images welche asynchron zur DB den ganzen Tag aus der ganzen Welt downgeloaded werden. Insofern kann da natürlich die ein oder andere Dateileiche übrig bleiben.
Natürlich werden die Images auch gelöscht, wenn der Eintrag in der DB gelöscht wird, aber ich mag die Systeme gerne "sauber" ;-)

regds
Mike©

[Mike©]

Moin,

ich habe die Routine mal "umgebaut". Leider hat sich an der Ausführungszeit nichts geändert :-(

$x=0;

$strSQL="select file_name from tbl_images WHERE image_typ = 'INV' OR image_typ = 'AWB'";
$hErgebnis = @mssql_query($strSQL, $hVerbindung);

while ( $result0=mssql_fetch_array($hErgebnis))
      {
            $name_sql=$result0['file_name'];
            $Files[$x]=$name_sql;
            $x++;
      }

$Image_Dir='./../data/cia_images';

$hDir=opendir($Image_Dir);

while ( $file = readdir ($hDir) )
      {
        if ( ($file != ".") && ($file != "..") )
           {
             if ( !in_array($file, $Files) )
                # Datei löschen
           }
       }

closedir($hDir);

regds
Mike©

[dedlfix]

echo $begrueszung;

Nun ja, das wird zwar nicht der Bringer werden, aber das:

while ( $result0=mssql_fetch_array($hErgebnis))
      {
            $name_sql=$result0['file_name'];
            $Files[$x]=$name_sql;
            $x++;
      }

bekommt man ohne Übersichtsverlust auch kürzer:

while ( $result0=mssql_fetch_row($hErgebnis))
  $Files[] = $result0[0];

Beachte die Verwendung von mssql_fetch_row statt mssql_fetch_array!

echo "$verabschiedung $name";

[Mike©]

Moin dedlfix,

Nun ja, das wird zwar nicht der Bringer werden, aber das:

while ( $result0=mssql_fetch_row($hErgebnis))
  $Files[] = $result0[0];

*ACK* Dieser Part benötigt auch nur höchstens 2 Minuten, der nachfolgende Teil ist der "Zeitfresser"

regds
Mike©

[lulu]

Huhu Mike

*ACK* Dieser Part benötigt auch nur höchstens 2 Minuten, der nachfolgende Teil ist der "Zeitfresser"

Probier mal die Dateinamen als Array-Indizes zu verwenden.
Also

[...]
            $Files[$name_sql] = true;
[...]

if ( !isset($Files[$file]) )
                # Datei löschen

Das sollte schneller sein.

Viele Grüße

lulu

[Mike©]

Moin Lulu,

Probier mal die Dateinamen als Array-Indizes zu verwenden.
Also

[...]
            $Files[$name_sql] = true;
[...]

if ( !isset($Files[$file]) )
                # Datei löschen

danke Lulu und allen welche mit Tips beigetragen haben.

Die Ausführungszeit von anfänglich > 1 Stunde beträgt nun < 5 Minuten
*RESPEKT*

danke & regds
Mike©

[Mike©]

Moin,

Probier mal die Dateinamen als Array-Indizes zu verwenden.

momentan sieht das ja super aus, bei 120.000 Feldern im Array.
Wie kann ich feststellen ab wann der Speicher nicht mehr für das Array ausreicht? Da das Programm im Hintergrund läuft, würde ich einen Crash zunächst mal nicht mitbekommen.

Ich nehme mal an das der verwendete Speicher der Arbeitspeicher ist. Könnte ich dann theoretisch die Maximale Größe des Array berechnen?
Theoretisch deswegen, weil der Arbeitsspeicher ja nicht nur von mir genutzt wird.
Die Dateinamen sind 55 Zeichen lang. Wieviel Speicher würde dann ein indiziertes Array bei 120.000 Einträgen benötigen?

Fragen über Fragen.

Danke & regds
Mike©

[lulu]

Huhu Mike

Die Dateinamen sind 55 Zeichen lang. Wieviel Speicher würde dann ein indiziertes Array bei 120.000 Einträgen benötigen?

Mmmh, da müsste man wissen wie PHP das intern verwaltet.

Man kann es aber auch ungefähr abschätzen z.B. mit Hilfe von http://php.net/manual/en/function.memory-get-usage.php

Ein einfacher "Milchmädchentest" hat bei mir ca. 13,5 MB ergeben.

Bedenken hätte ich da auch.
Evtl. kannst Du mit zeilenweisem abarbeiten von zwei Flatfiles etwas
resourcenschonenderes basteln.

Also z.B. Datei A (sämtliche Dateien aus dem Filesystem)

a1
a2
a3
gippsnich
gippsauchnich
a4
a6
[...]
Datei B (die "guten" Dateien aus der DB)

a1
a2
a3
a4
a6
[...]

// PseudoScript

while ( $lineB = getLineFromB()):

while ( ($lineA = getLineFromA()) != $lineB){
   unlink ($lineA);
}

endwhile;

Dazu müssen dann aber beide Listen in gleicher Weise geordnet vorliegen.
Und die Menge B muss vollständig in Menge A enthalten sein.
Es darf also nicht den Fall geben, dass eine Datei in der DB aber nicht im Filesystem steht.
Ist also auch eine etwas wackelige Konstruktion.

Viele Grüße

lulu

[Andreas Korthaus]

Hallo!

Die Dateinamen sind 55 Zeichen lang. Wieviel Speicher würde dann ein indiziertes Array bei 120.000 Einträgen benötigen?

Mmmh, da müsste man wissen wie PHP das intern verwaltet.

Einen Ansatz gibt es hier: http://marc.theaimsgroup.com/?l=php-dev&m=110259346006082&w=2

Formel: 42 * number of array elements + size of keys + data

also wenn ich das richtig verstehe müsste das etwas so aussiehen:

120000 * (42 + 55 + 1) =  ca. 12 MB

Kommt ja auch ungefähr hin mit dem was Du überschlagen hast.

Bedenken hätte ich da auch.

Es kommt drauf an. Wenn es kein Problem für den Server ist hätte ich kein Problem damit wenn das Script >100 MB verbraucht. Denn nur wenn sich die Daten als PHP-Arrays im RAM befinden geht das ganze gleichzeitig verhältnismäßig effizient und unproblematische (siehe Deine "Problematik" unten).

Evtl. kannst Du mit zeilenweisem abarbeiten von zwei Flatfiles etwas
resourcenschonenderes basteln.

Man könnte z.B. auch direkt relativ RAM-schonend mit den DB-Daten arbeiten, wenn man mysql_unbuffered_query verwendet (natürlich darf man die dann nicht in ein Array schreiben...).
Vielleicht kann man auch die lokalen Dateinamen in die DB schreiben, das heißt in eine temporäre Tabelle, evtl. HEAP. Du könntest in einer Schleife ohne viel Ram zu verbrauchen eine Datei erzeugen, die entweder direkt als "Daten" in die DB eingelesen werden kann, oder direkt ein SQL-Statement erzeugen. Dies dann möglichst effizient (vermutlich am besten über den lokalen mysql-client, bei remote-server evtl. auf komprimierte Übertragung achten!) in die DB einlesen. Dann könntest Du mit einem entsprechend effizienten SQL-Statement alle Dateinamen filtern, die zu löschen sind.

Oder Du kannst evtl. mit Datei-IDs arbeiten. Das heißt noch irgendwo eine Datei-ID führen, so dass Du die langen Namen nicht brauchst. Damit ließe sich sicherlich die Hälfte des benötigten Arbeitsspeichers sparen.

120.000 Dateien in einem Verzeichnis ist nicht unbedingt die beste Idee, vor allem wenn man kein Dateisystem verwendet, welches mit sowas effizient umgehen kann. Wenn Du einmalig die Dateinamen in eindeutige IDs (Zahlen) umbenennst, köntest Du z.B. auch direkt Verzeichnisse anlegen. Wenn Du kein Dateisystem verwendest welches effizient mit vielen Dateien im Verzeichnis umgehen kann (AFAIR z.B. ReiserFS) könntest Du z.B. alle Dateien dessen ID mit 1 anfängt (oder besser endet) in Verzeichnis 1 legen.
Die ersten Ziffern zu verwenden hat den Vorteil dass man entsprechende Einträge aus der DB indizieren und so effizient abfragen kann, wenn man die letzten Ziffern verwendet ist die Verteilung der Dateien auf die Verzeichnisse allerdings gleichmäßiger. Kommt also drauf an was man genau machen will.
So wäre zum einen der Zugriff schneller, außerdem könntest Du die Dateien beim lesen in 10 gleiche Teile teilen (z.B. per glob('*1')).
Oder wenn Du noch "tiefer" gehst die ersten oder letzten _zwei_ Ziffern verwenden, wären dann 100 gleiche Teile usw. Aber dann müsstest Du vermutlich einiges umbauen.

Ich würde allerdings nicht zu viel Zeit in eine Optimierung bzgl. RAM-Verbrauch stecken, falls es hier in absehbarer Zeit eh keine Probleme geben wird.
Das Schwierigkeit liegt darin, dass Du - damit das ganze schnell ist - mindestens eines der beiden Listings als PHP-Array im RAM haben solltest. Wenn Du das vermeiden willst/musst wird es halt mit Sicherheit _deutlich_ langsamer und komplizierter.

Mit Hilfe des Profilers von xdebug kannst Du sehr gut untersuchen wo genau im Script Zeit/RAM verbraucht wird.

Damit würde ich einfach ein paar Ideen ausprobieren.

Grüße
Anreas

[Mike©]

Moin Andreas,

Mmmh, da müsste man wissen wie PHP das intern verwaltet.

vielen Dank für deine ausführlichen Anmerkungen. Ich werde das ein oder andere mal näher betrachten.

regds
Mike©