jeudi 9 octobre 2008
Prepare statements et insertion sur partitionnement de tables - Part II
Par david techer, jeudi 9 octobre 2008 à 19:57 :: PostGIS et PostgreSQL
Par rapport au poste précédent, on va cette fois ci répartir les données dans la table mesure par pas de 100 000.
[jdtecher@jdtecher momo]$ wget http://www.davidgis.fr/download/mesure.zip;unzip mesure.zip;cd mesure
--19:51:00-- http://www.davidgis.fr/download/mesure.zip
=> `mesure.zip'
Résolution de www.davidgis.fr... 82.236.232.184
Connexion vers www.davidgis.fr|82.236.232.184|:80...connecté.
requête HTTP transmise, en attente de la réponse...200 OK
Longueur: 424097 (414K) [application/zip]
100%[=============================================================================================================>] 424097 107.08K/s ETA 00:00
19:51:04 (105.94 KB/s) - « mesure.zip » sauvegardé [424097/424097]
Archive: mesure.zip
creating: mesure/
inflating: mesure/mesures_schema.sql
inflating: mesure/mesures_insert.sql
inflating: mesure/mesures_exec.sql
Voici la structure des diverses tables
BEGIN; CREATE TABLE mesure(value float); CREATE TABLE mesure_100000 ( check (value >= 0 and value<100000) ) INHERITS (mesure); CREATE TABLE mesure_200000 ( check (value >= 100000 and value<200000)) INHERITS (mesure); CREATE TABLE mesure_300000 ( check (value >= 200000 and value<300000)) INHERITS (mesure); CREATE TABLE mesure_400000 ( check (value >= 300000 and value<400000)) INHERITS (mesure); CREATE TABLE mesure_500000 (check (value >= 400000 and value<500000) ) INHERITS (mesure); CREATE TABLE mesure_600000 ( check (value >= 500000 and value<600000)) INHERITS (mesure); CREATE TABLE mesure_700000 ( check (value >= 600000 and value<700000)) INHERITS (mesure); CREATE TABLE mesure_800000 ( check (value >= 700000 and value<800000)) INHERITS (mesure); CREATE TABLE mesure_900000 ( check (value >= 800000 and value<900000)) INHERITS (mesure); CREATE TABLE mesure_1000000 (check (value >= 900000 and value<1000000) ) INHERITS (mesure); CREATE TABLE mesure_out ( check (value < 0 or value>1000000)) INHERITS (mesure); CREATE OR REPLACE RULE mesure_insert_100000 AS ON INSERT TO mesure WHERE ( value>=0 and value<100000) DO INSTEAD INSERT INTO mesure_100000 VALUES (NEW.value); CREATE OR REPLACE RULE mesure_insert_200000 AS ON INSERT TO mesure WHERE ( value>=100000 and value<200000) DO INSTEAD INSERT INTO mesure_200000 VALUES (NEW.value); CREATE OR REPLACE RULE mesure_insert_300000 AS ON INSERT TO mesure WHERE ( value>=200000 and value<300000) DO INSTEAD INSERT INTO mesure_300000 VALUES (NEW.value); CREATE OR REPLACE RULE mesure_insert_400000 AS ON INSERT TO mesure WHERE ( value>=300000 and value<400000) DO INSTEAD INSERT INTO mesure_400000 VALUES (NEW.value); CREATE OR REPLACE RULE mesure_insert_500000 AS ON INSERT TO mesure WHERE ( value>=400000 and value<500000) DO INSTEAD INSERT INTO mesure_500000 VALUES (NEW.value); CREATE OR REPLACE RULE mesure_insert_600000 AS ON INSERT TO mesure WHERE ( value>=500000 and value<600000) DO INSTEAD INSERT INTO mesure_600000 VALUES (NEW.value); CREATE OR REPLACE RULE mesure_insert_700000 AS ON INSERT TO mesure WHERE ( value>=600000 and value<700000) DO INSTEAD INSERT INTO mesure_700000 VALUES (NEW.value); CREATE OR REPLACE RULE mesure_insert_800000 AS ON INSERT TO mesure WHERE ( value>=700000 and value<800000) DO INSTEAD INSERT INTO mesure_800000 VALUES (NEW.value); CREATE OR REPLACE RULE mesure_insert_900000 AS ON INSERT TO mesure WHERE ( value>=800000 and value<900000) DO INSTEAD INSERT INTO mesure_900000 VALUES (NEW.value); CREATE OR REPLACE RULE mesure_insert_1000000 AS ON INSERT TO mesure WHERE ( value>=900000 and value<1000000) DO INSTEAD INSERT INTO mesure_1000000 VALUES (NEW.value); CREATE OR REPLACE RULE mesure_insert_out AS ON INSERT TO mesure WHERE ( value < 0 or value>1000000) DO INSTEAD INSERT INTO mesure_out VALUES (NEW.value); CREATE VIEW mesure_all as SELECT * FROM mesure_100000 UNION ALL SELECT * FROM mesure_200000 UNION ALL SELECT * FROM mesure_300000 UNION ALL SELECT * FROM mesure_400000 UNION ALL SELECT * FROM mesure_500000 UNION ALL SELECT * FROM mesure_600000 UNION ALL SELECT * FROM mesure_700000 UNION ALL SELECT * FROM mesure_800000 UNION ALL SELECT * FROM mesure_900000 UNION ALL SELECT * FROM mesure_1000000 UNION ALL SELECT * FROM mesure_out; END;Les deux fichiers ont le contenu suivant avec 50 000 valeurs aléataoires identiques dans les deux fichiers
- pour
mesures_insert.sqlbegin; insert into mesure values (1061095); insert into mesure values (584444); insert into mesure values (1117347); insert into mesure values (634985); insert into mesure values (249222); .... ..... ... .... ... .... end;
- pour
mesures_exec.sqlbegin; prepare insert_mesure(real) as insert into mesure values ($1); SET constraint_exclusion = on; execute insert_mesure(1061095); execute insert_mesure(584444); execute insert_mesure(1117347); execute insert_mesure(634985); execute insert_mesure(249222); .... ..... ... .... ... .... end;
Maintenant on va tester l'import pour 50 000 valeurs aléatoires pour le prepare/execute et l'insertion usuelle
[jdtecher@jdtecher mesure]$ for mode in insert exec;do dropdb test > /dev/null;createdb test > /dev/null ;\ psql -d test -f mesures_schema.sql > /dev/null;echo " mode = $mode";date;psql -d test -f mesures_$mode.sql > /dev/null ;\ date;done mode = insert jeu oct 9 19:51:38 CEST 2008 jeu oct 9 19:52:14 CEST 2008 mode = exec jeu oct 9 19:52:15 CEST 2008 jeu oct 9 19:52:25 CEST 2008Le rapport est quand même remarquable. Soit un facteur de 3.5 sur une machine AMD64 qui a servit de base de Test pour un serveur PostgreSQL 8.1
Exemple en Perl DBI
On a maintenant un fichier values.txt qui contient les diverses valeurs déjà présentes. Nous allons utilisé perl pour lire les valeurs et les insérer grâce à DBI.
On aura par exemple le script suivant
#!/usr/bin/perl
use DBI;
$DB_name = 'test';
$DB_user = 'postgres';
$DB_pwd = '****';
$dbh = DBI->connect("dbi:Pg:dbname=$DB_name","$DB_user","$DB_pwd");
print "\nConnection error: $DBI::errstr\n\n";
$sth = $dbh->prepare("insert into mesure values(?)");
open FICHIER,"< values.txt" or die "Le fichier n'existe pas !";
while ($ligne = ){
$sth->bind_param(1,$ligne);
$sth->execute();
}
close FICHIER;
$sth->finish();
$dbh->disconnect();
Pour le lancer
PGHOST=localhost ./pg_exec.pl
. Promis quand le temps me le permettra, je remettrais à jour ma doc 
!