Deze week moest ik wat stresstests uitvoeren en had doorvoor grote hoeveelheden data nodig. Normaal had ik daarvoor in php een lusje geschreven die een berg insert-queries naar de database zou sturen. Ik werd er echter op gewezen dat je dit veel sneller kunt doen door even een functie in PostgreSQL te schrijven die dat zelf regelt. En jawel, dat ging verbazingwekkend snel! 100.000 records inserten in minder dan 5 seconden.
Op zich is het niet zo spannend, maar het geeft wel aan dat je door het gebruik van functies binnen de database, de data een stuk sneller kunt verwerken dan wanneer je iedere keer een nieuwe query naar de database moet sturen. Daarnaast is het een stuk veiliger, je kunt de gebruiker de toegang tot de tabel ontzeggen en uitsluitend toegang geven tot de functies.
Ga er eens mee spelen en doe er je voordeel mee.
http://www.postgresql.org/docs/8.1/interactive/server-programming.html
Gebruikte tabel:
CREATE TABLE stress
(
id bigserial NOT NULL,
content text,
CONSTRAINT pk_id PRIMARY KEY (id)
)
Gebruikte PostgreSQL-functie (kan beter, output wordt niet gebruikt):
CREATE OR REPLACE FUNCTION stresstest(int8, int8)
RETURNS int8 AS
$BODY$
DECLARE
ii int8;
BEGIN
FOR ii IN $1..$2
LOOP
INSERT INTO stress(content) VALUES(MD5(CURRENT_TIMESTAMP));
END LOOP;
RETURN $2;
END;
$BODY$
LANGUAGE 'plpgsql';
Gebruikte PHP-code:
<?php
error_reporting(E_ALL);
// Hier jouw eigen gegevens invullen:
$dbname = 'test';
$dbuser = '*****';
$dbpasswd = '*****';
$dbhost = 'localhost';
$dbport = '5432';
$avgQuery = 0;
$avgFunction= 0;
// aanpassen naar eigen wens:
$repeat = 5;
$records = 100000;
if(!pg_connect('host='.$dbhost.' dbname='.$dbname.' user='.$dbuser.' password='.$dbpasswd.' port='.$dbport)){
echo 'Geen databaseverbinding!';
}
else {
echo 'Er wordt '.$repeat.' keer een snelheidstest uitgevoerd waarbij '.number_format($records, 0, ',', '.'). ' records worden aangemaakt. Resultaten:';
echo '<table border=1>'.PHP_EOL;
## insert met een gewone insert-query rechtstreeks in de tabel
echo '<th colspan=2>INSERT query:</th>';
for ($x = 1; $x <= $repeat; $x++){
$start = microtime(true);
for ($i = 0; $i < $records; $i++ ){
// md5-functie van de database maakt de boel nog langzamer, php doet dit sneller
$query = "INSERT INTO stress(content) VALUES('".md5(time())."')";
if(!pg_exec($query)){
echo pg_last_error().PHP_EOL;
echo $query.PHP_EOL;
}
}
$end = microtime(true);
$time = $end - $start;
$avgQuery += $time;
echo '<tr><td>'.$x.'</td><td>'.$time.'</td></tr>'.PHP_EOL;
}
## En nu een insert met de functie uitvoeren:
echo '<th colspan=2>Function:</th>';
for ($x = 1; $x <= $repeat; $x++){
$start = microtime(true);
$query = "SELECT stresstest(1, ".$records.")";
if(!pg_exec($query)){
echo pg_last_error().PHP_EOL;
echo $query.PHP_EOL;
}
$end = microtime(true);
$time = $end - $start;
$avgFunction += $time;
echo '<tr><td>'.$x.'</td><td>'.$time.'</td></tr>'.PHP_EOL;
}
$gemQuery = $avgQuery/$repeat;
$gemFunction = $avgFunction/$repeat;
$gemVerschil = number_format($gemQuery/$gemFunction, 1, ',', '.');
echo '<th colspan=2>Gemiddelden:</th>';
echo '<tr><td>Query:</td><td>'.$gemQuery.'</td></tr>';
echo '<tr><td>Functie:</td><td>'.$gemFunction.'</td></tr>';
echo '</table>'.PHP_EOL;
echo 'Het gebruik van de functie was gemiddeld '.$gemVerschil.' keer zo snel.';
pg_close();
}
?>
Resultaten:
Er wordt 5 keer een snelheidstest uitgevoerd waarbij 100.000 records worden aangemaakt. Resultaten:
INSERT query:
1: 127.626353979
2: 127.663748026
3: 129.388574123
4: 128.646729946
5: 125.241792917
Function:
1: 4.24825119972
2: 5.77088189125
3: 4.0562710762
4: 4.04982805252
5: 5.46645998955
Gemiddelden:
Query: 127.713439798
Functie: 4.71833844185
Het gebruik van de functie was gemiddeld 27,1 keer zo snel.
Ps. Er valt vast nog wel het e.e.a. te optimaliseren, daar heb ik me niet of nauwelijks mee beziggehouden. De functie kan in elk geval nog wat eenvoudiger.
