[php/psql] Optimaliseren van een postgresql update

[ACM]

Ik ben momenteel bezig met een heel klein php-applicatietje...

Ik heb een lijst met allerlei netwerk-ranges (dus 123.123.0.0/16 etc)
Die lijst bevat zo'n 29000 elementen en is volgens de tabel:

code:

create table networks
(
    networkid    SERIAL  not null primary key,
    network    INET    not null,
    country    VARCHAR(3) not null
);

Gemaakt, via het postgresql commando 'copy networks from stdin'; werd dit erg snel gevuld vanuit een file waar alleen de adressen (network in deze tabel dus).

Dat was dus geen punt.

Nu heb ik echter een andere file waar de country codes in staan.

Dit is wat ongelukkig aangezien deze in principe aan ip's gekoppeld zijn die binnen de netwerken liggen die hierboven zijn.

Aangezien het niet al te ernstig is, als er een paar kleine foutjes in zitten doe ik dat met de volgende php-code:

PHP:

<?
   $fp = fopen("inetnum.country.db", "r");
   $conn = pg_connect("host=magnus user=acm dbname=acm");

   $i = 0;
   $j = 0;
   $to = 96000;
   $x1 = time();
   while(!feof($fp))
   {
        if($i < $to)
        {
            $string = fgets($fp, 1024);
            $string = fgets($fp, 1024);
        }
        else
        {
            $string = fgets($fp, 1024);
            $address = substr($string, 0, strpos($string, ' '));
            $country = trim(fgets($fp, 1024));
            $query = "UPDATE networks
                 SET country = '$country'
                 WHERE network >> inet'$address' AND country = '';
             ";
            pg_exec($conn, $query) or die("Linenumber of file: $i\n");
       }
       $i += 2;
       if($i % 2000 == 0)
       {
            $x2 = time();
            $y = $x2 - $x1;
            if($y == 0)
                 $y = 1;
            $x1 = $x2;
            printf("%f done, in %f seconds that was: %f q/s\n", ($i / 2), $y, 1000/$y);
       }
   }
?>

De "overhead van de laatste if" is overigens nihil.

Maar dit schiet absoluut niet op, er worden nu zo'n 8.5 update queries per seconde uitgevoerd...

Per ipadres/country koppel wordt er een check tegen alle netwerk-adressen in de DB uitgevoerd.

Nu vroeg ik me af wat de makkelijkste manier is om dit op te lossen.

Momenteel dacht ik aan een array met alle landcodes (dus $array[$country] = $network) en dat dan daarna in een update om te zetten
(update networks set country = $country where country = '' AND (network >> $network[1] OR network >> $network[2] etc) )
Maar voor ik hier aan begin zou ik toch wel weten OF het dan ook sneller gaat...

Tsja, er zijn minder queries nodig (veel minder) maar de afgeschoten queries zijn wel weer een stuk langer (een enorme OR-reeks).

Aangezien er zo'n 430.000 elementen in die 2e file zitten er overlappen er dus erg veel maar dat checken is nog veel moeilijker.

Iemand nog tips??
Want zoals het nu lijkt, gaat het nog wel een paar uur duren

[edit]
Momenteel reageert www.postgresql.org niet zo lekker.
De manual is ook te vinden op:
http://postgresql.lerner.co.il/users-lounge/docs/

[ACM]

Stel je es een moeilijke vraag, is ie weer te moeilijk blijkbaar

[jochemd]

Als je nog geinteresseerd bent in het antwoord.
In plaats van een heleboel queries naar de database te sturen met afzonderlijke connecties etc. is het veel beter om ze allemaal in een connectie te sturen. En eerst even de automatische transactions uitschakelen en het gaat een heel stuk sneller.

[ACM]

Op zaterdag 06 oktober 2001 16:49 schreef jochemd het volgende:
Als je nog geinteresseerd bent in het antwoord.
In plaats van een heleboel queries naar de database te sturen met afzonderlijke connecties etc. is het veel beter om ze allemaal in een connectie te sturen. En eerst even de automatische transactions uitschakelen en het gaat een heel stuk sneller.

Mag 'COPY FROM STDIN' ook?

Nee, dat maakte geen f*ck uit voor het 'echte probleem'.
Of het inladen nou 10 minuten of 1 minuut duurt (nahja, was iets groter verschil).
Die (ene) query waar het echte probleem mee is, duurde gewoon ruim 6 uur...
(om die twee tabellen bij elkaar te rapen)


[edit]
Moet ik er natuurlijk wel bij zeggen dat ik het iets anders aangepakt had en eerst beide data-files ingelezen in de DB had.

Daarna pas een vergelijkbare (1 enkele) uitgevoerd.