Le blog de Jean David TECHER, un Réunionnais à Saint-Priest/Lyon

Aller au contenu | Aller au menu | Aller à la recherche


< 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 >

dimanche 12 octobre 2008

Coup pour partionnement de tables avec opérations classiques (SELECT, INSERT,UPDATE) - Part VI

Suite à mes précédents billets, qui étaient assez empirique il est temps de se poser la question: pour une stratégie de partionnement de table, qu'est-ce qui s'avérer le plus coûteux?

Nous allons ici exploiter la possibilité qu'offre le formattage des fichiers de log de PostgreSQL vers le format CSV pour proposer ici un exemple d'illustration.

Serveur PostgreSQL et configuration

On prendra un serveur PostgreSQL 8.3

david@bremko:/var/lib/david$ pg_config --version
PostgreSQL 8.3.3

On a la configuration suivante

david@bremko:/var/lib/david$ grep -v '^#' $(PGDATABASE=test psql -At -p 5432 -h localhost -c "show config_file")|sed -e "s:#.*$::g"|grep '^[a-z]'
max_connections = 100			
shared_buffers = 32MB			
max_fsm_pages = 204800			
log_destination = 'csvlog'		
logging_collector = on		
log_directory = 'pg_log'		
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'	
log_min_duration_statement = 0	
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = on
log_hostname = on
log_line_prefix = '%u'			
log_statement = 'all'			
datestyle = 'iso, dmy'
lc_messages = 'fr_FR.UTF-8'			
lc_monetary = 'fr_FR.UTF-8'			
lc_numeric = 'fr_FR.UTF-8'			
lc_time = 'fr_FR.UTF-8'				
default_text_search_config = 'pg_catalog.french'

Remarque:Il faut penser à créer ou vider le répertoire $PGDATA/pg_log si nécessaire

export PATH=/opt/postgresql-8.3.3/bin:$PATH
export PGDATA=/var/lib/david
rm -rf $PGDATA/pg_log;mkdir $PGDATA/pg_log

La structure de la base sera la suivante contenu du fichier mesure_schema.sql

BEGIN;
CREATE TABLE mesure(value float,repetition integer default 0);

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 ( NEW.value>=0 and NEW.value<100000) DO INSTEAD INSERT INTO mesure_100000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_200000 AS ON INSERT TO mesure WHERE ( NEW.value>=100000 and NEW.value<200000) DO INSTEAD INSERT INTO mesure_200000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_300000 AS ON INSERT TO mesure WHERE ( NEW.value>=200000 and NEW.value<300000) DO INSTEAD INSERT INTO mesure_300000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_400000 AS ON INSERT TO mesure WHERE ( NEW.value>=300000 and NEW.value<400000) DO INSTEAD INSERT INTO mesure_400000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_500000 AS ON INSERT TO mesure WHERE ( NEW.value>=400000 and NEW.value<500000) DO INSTEAD INSERT INTO mesure_500000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_600000 AS ON INSERT TO mesure WHERE ( NEW.value>=500000 and NEW.value<600000) DO INSTEAD INSERT INTO mesure_600000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_700000 AS ON INSERT TO mesure WHERE ( NEW.value>=600000 and NEW.value<700000) DO INSTEAD INSERT INTO mesure_700000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_800000 AS ON INSERT TO mesure WHERE ( NEW.value>=700000 and NEW.value<800000) DO INSTEAD INSERT INTO mesure_800000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_900000 AS ON INSERT TO mesure WHERE ( NEW.value>=800000 and NEW.value<900000) DO INSTEAD INSERT INTO mesure_900000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_1000000 AS ON INSERT TO mesure WHERE ( NEW.value>=900000 and NEW.value<1000000) DO INSTEAD INSERT INTO mesure_1000000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_out AS ON INSERT TO mesure WHERE ( NEW.value < 0 or  NEW.value>1000000) DO INSTEAD INSERT INTO mesure_out VALUES (NEW.value);

CREATE INDEX m_idx ON mesure(value);
CREATE INDEX m_idx_100000 ON mesure_100000(value);
CREATE INDEX m_idx_200000 ON mesure_200000(value);
CREATE INDEX m_idx_300000 ON mesure_300000(value);
CREATE INDEX m_idx_400000 ON mesure_400000(value);
CREATE INDEX m_idx_500000 ON mesure_500000(value);
CREATE INDEX m_idx_600000 ON mesure_600000(value);
CREATE INDEX m_idx_700000 ON mesure_700000(value);
CREATE INDEX m_idx_800000 ON mesure_800000(value);
CREATE INDEX m_idx_900000 ON mesure_900000(value);
CREATE INDEX m_idx_1000000 ON mesure_1000000(value);
CREATE INDEX m_idx_out ON mesure_out(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;

CREATE TABLE t_time(t_id serial,t_cumul timestamp);

CREATE VIEW t_time_test AS SELECT b.t_id-1,date_part('second',b.t_cumul-a.t_cumul) FROM  t_time a JOIN t_time b ON (b.t_id-a.t_id=1);

CREATE TABLE postgres_log
(
  log_time timestamp(3) with time zone,
  user_name text,
  database_name text,
  process_id integer,
  connection_from text,
  session_id text,
  session_line_num bigint,
  command_tag text,
  session_start_time timestamp with time zone,
  virtual_transaction_id text,
  transaction_id bigint,
  error_severity text,
  sql_state_code text,
  message text,
  detail text,
  hint text,
  internal_query text,
  internal_query_pos integer,
  context text,
  query text,
  query_pos integer,
  location text,
  PRIMARY KEY (session_id, session_line_num)
);

END;

Préparation des données à tester - échantillon de 450 000 valeurs

Ici on a maintenant 200 000 lignes dans notre fichier

david@bremko:/var/lib/david$ wc -l values_all.data
200000 values_all.data

On en ajoute (au moins) quelques 150 500 de plus

echo "Début de la génération := $(date)";
export NbData=150000;
for((i=0;i<=${NbData};i++));
	do psql -At -F" " -c "SELECT (random()*1500000)::integer" -d test >> values_all.data;
done;

On a le rendu suivant

On va émettre un son quand le fichier sera remplit en faisant

while [ $(wc -l values_all.data|awk {'print $1'}) -lt 262000 ];
do 
      echo > /dev/null;
done;
while true; 
do cat /usr/share/doc/libsdl-erlang/examples/Beep.wav > /dev/audio;
done

Ce qui m'a permis d'aller faire une sieste en attendant ...A mon retour ceci tournait encore, j'ai donc arrêté la génératione faisant [CRTL]+[C]. Il me manquait une centaines de milliers de lignes

Astuce!: sinon pour aller bien plus vite

psql -d test -At -F" " -c "select (random()*1500000)::integer from generate_series(1,127045)" >> values_all.data

J'ai donc obtenu

david@bremko:/var/lib/david$ wc -l values_all.data 
450000 values_all.data

Nickel !

Exécution du script

....

dropdb test
createdb test
 psql -d test -f mesure_schema.sql
date;PGHOST=localhost ./pg_exec.pl values_all.data ;date
pg_ctl stop
mv data/pg_log data/pg_log.old

Génération des graphes

Population de postgres_log

<

Onn redémarre le serveur et on on charge les données dans la table postgres_log

pg_ctl start
mkdir $PGDATA/pg_log
cd $PGDATA/pg_log.old
 for file in $(ls *.csv);do psql -d test -c "COPY postgres_log FROM '$PWD/$file' WITH csv;";done

Il faut ensuite par créer et populer les tables nécessaires pour les trois opérations (SELECT, UPDATE,INSERT) en chargeant le contenu du fichier mesure_schema_2.sql

begin;

create table s_selects(id serial,s_duration real);

create table s_inserts(id serial,s_duration real);

create table s_updates(id serial,s_duration real) ;

insert into s_selects(s_duration) select case when command_tag='SELECT' then replace(replace(message,'duration: ',''),' ms','')::real else 0::real end as s_duration from postgres_log where command_tag in ('UPDATE','INSERT','SELECT') and message ~ 'duration';

insert into s_inserts(s_duration) select case when command_tag='INSERT' then replace(replace(message,'duration: ',''),' ms','')::real else 0::real end as s_duration from postgres_log where command_tag in ('UPDATE','INSERT','SELECT') and message ~ 'duration';

insert into s_updates(s_duration) select case when command_tag='UPDATE' then replace(replace(message,'duration: ',''),' ms','')::real else 0::real end as s_duration from postgres_log where command_tag in ('UPDATE','INSERT','SELECT') and message ~ 'duration';

end;

Ce qui se fait en faisant

psql -d test -f mesure_schema_2.sql

Nous sommes maintenant prêt pour générer nos graphes. Pour se faire, nous pouvons utiliser GnuPlot.

Il faut générer les fichiers qui contiendront les données à grapher

for query in selects inserts updates;do psql -At -F" " -d test -c "SELECT * FROM s_$query" > values.$query.data;done

Avec GnuPlot, on fait

gnuplot>set yrange [0:40]
gnuplot> set xlabel "Numero operation"                 
gnuplot> set ylabel "Temps en millisecondes" 
gnuplot> plot 'values.selects.data' w lines lc 2 title 'SELECT count(*) FROM mesure WHERE value=?'     
gnuplot> plot 'values.inserts.data' w lines lc 3 title 'INSERT INTO mesure(value) VALUES (?)' 
gnuplot> plot 'values.updates.data' w lines lc 1 title 'UPDATE mesure SET repetition=repetition+1 WHERE value=?'

On a alors les graphes suivants


Fig.1 - Opérations sur INSERT

Fig.2 - Opérations sur SELECT

Fig.3 - Opérations sur UPDATE

Mais attention, il ne faut pas sauter au plafon de suite. Par exemple en zoomant de l'opération 174200 à 174300, on a le graphe suivant


Fig.4 - Opérations de l'opération 174200 à 174300.

En zoomant de l'opération 150000 à 20000, on a le fichier suivant

Mais attention, il ne faut pas sauter au plafon de suite. Par exemple en zoomant de l'opération 174200 à 174300, on a le graphe suivant


Fig.5 - Opérations de l'opération 150000 à 200000.

vendredi 10 octobre 2008

Prepare statements et insertion sur partitionnement de tables - performance sur un jeu de 1 millions de valeurs à insérer - Part IV

Ici, je m'intéresse à un jeu de 200 000 valeurs que j'ai généré aléatoirement entre 1 à 1 500 000. Je les ai ensuite copié dans un fichier texte: values.ma.txt.

Générer 1 million de valeurs prenant trop de temps, j'ai donc copier le même fichier en 5 fois dans un autre fichier de manière à avoir 1 million de données.

Je rappelle ici que le jeu est le suivant. J'ai un script perl qui lit les diverses valeurs sur chaque ligne d'un fichier. J'ai un partitionnement de tables regroupant les valeurs de 0 à 1000000 (voire plus) par pas de 100 000.

  • si la valeur courante est nouvelle alors, je l'insère en base. En fonction de sa valeur, elle sera insérer dans la table attendue. Par exemple si value=234500, elle ira dans la table mesure_300000 (car 200000<234500 et 234500<300000);
  • si la valeur courante est déjà présente en base, j'augmente son nombre d'occurence.

Remarque: Je rappelle que le script perl est dans mon précédent biller.

Testé avec PostgreSQL 8.1.11, sur une AMD 64 (classique de bureau), j'ai laissé tourner le script. J'ai obtenu le relevé suivant que j'ai ensuite converti en ce fichier

on a alors le graphe suivant. J'ai fait ici le relevé toutes les 5000 itérations pour les valeurs à tester.


Fig. 1 - Relevé du temps obtenu toutes les 5000 itérations. Dégradation des performances au bout de 45 000 itérations!!!

Fig 2 - Cumul du temps obtenu toutes les 5000 itérations

Constatation

On remarque donc que

  • AU bout de 40000~45000 itérations, c'est vraiment la dégringolade !
  • Le chargement pour le million de donner à tester a prit au moins 1h30 !

Prepare statements et insertion sur partitionnement de tables - Part III

Toujours sur l'exemple du poste précédent, on complique maintenant le jeu. On a ajouté maintenant la colonne répétition aux tables mesures_X qui décompte le nombre de fois où la valeur à insérer apparaît dans le fichier.

Pour chaque valeur lue dans le fichier values.txt

  • si la valeur à insérer est déjà présente en base alors on met à jour le champs répétition (UPDATE);
  • si la valeur n'est pas déjà présente alors on l'insère (INSERT).

Ce qui donne

#!/usr/bin/perl
use DBI;

$DB_name    = 'test';
$DB_user    = 'postgres';
$DB_pwd     = 'keizer';

$dbh = DBI->connect("dbi:Pg:dbname=$DB_name","$DB_user","$DB_pwd");

$sth0 = $dbh->prepare("set constraint_exclusion to on;select count(*) from mesure where value=?");
$sth1  = $dbh->prepare("set constraint_exclusion to on;insert into mesure(value) values (?)");
$sth2  = $dbh->prepare("set constraint_exclusion to on;update mesure set repetition=repetition+1 where value=?");


open FICHIER,"< values.txt" or  die "Le fichier n'existe pas !";

$iter=0;

while ($ligne = <FICHIER>){
        if (($iter%5000)==0){ print "$iter parsées\t";system "date '+%Y-%m-%d %H:%M:%S'";}

        $sth0->bind_param(1,$ligne);
        $sth0->execute();
        while ( ($count) = $sth0->fetchrow_array() ){
        if ($count==0){
                        $sth1->bind_param(1,$ligne);
                        $sth1->execute();
        }
        else{
                        $sth2->bind_param(1,$ligne);
                        $sth2->execute();
        }
       }
        $iter++;
}

close FICHIER;
$sth0->finish();
$sth1->finish();
$sth2->finish();
$dbh->disconnect();

A l'exécution, on aura

0 parsées       2008-10-10 17:57:53
5000 parsées    2008-10-10 17:58:07
10000 parsées   2008-10-10 17:58:20
15000 parsées   2008-10-10 17:58:33
20000 parsées   2008-10-10 17:58:47
25000 parsées   2008-10-10 17:59:00
30000 parsées   2008-10-10 17:59:14
35000 parsées   2008-10-10 17:59:28
40000 parsées   2008-10-10 17:59:42
45000 parsées   2008-10-10 17:59:58

Soit une moyenne de 14 secondes pour 50000 valeurs à parser soit 357 valeurs traitées par seconde sur une AMD 64 (sous réserver de la configuration du serveur).

On aura le schema de tables suivant

BEGIN;
CREATE TABLE mesure(value float,repetition integer default 0);

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 ( NEW.value>=0 and NEW.value<100000) DO INSTEAD INSERT INTO mesure_100000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_200000 AS ON INSERT TO mesure WHERE ( NEW.value>=100000 and NEW.value<200000) DO INSTEAD INSERT INTO mesure_200000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_300000 AS ON INSERT TO mesure WHERE ( NEW.value>=200000 and NEW.value<300000) DO INSTEAD INSERT INTO mesure_300000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_400000 AS ON INSERT TO mesure WHERE ( NEW.value>=300000 and NEW.value<400000) DO INSTEAD INSERT INTO mesure_400000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_500000 AS ON INSERT TO mesure WHERE ( NEW.value>=400000 and NEW.value<500000) DO INSTEAD INSERT INTO mesure_500000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_600000 AS ON INSERT TO mesure WHERE ( NEW.value>=500000 and NEW.value<600000) DO INSTEAD INSERT INTO mesure_600000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_700000 AS ON INSERT TO mesure WHERE ( NEW.value>=600000 and NEW.value<700000) DO INSTEAD INSERT INTO mesure_700000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_800000 AS ON INSERT TO mesure WHERE ( NEW.value>=700000 and NEW.value<800000) DO INSTEAD INSERT INTO mesure_800000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_900000 AS ON INSERT TO mesure WHERE ( NEW.value>=800000 and NEW.value<900000) DO INSTEAD INSERT INTO mesure_900000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_1000000 AS ON INSERT TO mesure WHERE ( NEW.value>=900000 and NEW.value<1000000) DO INSTEAD INSERT INTO mesure_1000000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_out AS ON INSERT TO mesure WHERE ( NEW.value < 0 or  NEW.value>1000000) DO INSTEAD INSERT INTO mesure_out VALUES (NEW.value);

CREATE INDEX m_idx_100000 ON mesure_100000(value);
CREATE INDEX m_idx_200000 ON mesure_200000(value);
CREATE INDEX m_idx_300000 ON mesure_300000(value);
CREATE INDEX m_idx_400000 ON mesure_400000(value);
CREATE INDEX m_idx_500000 ON mesure_500000(value);
CREATE INDEX m_idx_600000 ON mesure_600000(value);
CREATE INDEX m_idx_700000 ON mesure_700000(value);
CREATE INDEX m_idx_800000 ON mesure_800000(value);
CREATE INDEX m_idx_900000 ON mesure_900000(value);
CREATE INDEX m_idx_1000000 ON mesure_1000000(value);
CREATE INDEX m_idx_out ON mesure_out(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;

On a rappelons le 50 000 lignes dans le fichiers values.txt confirmée par la commande suivante:

test=# select count(a.value),a.repetition from mesure a group by 2;;
 count | repetition 
-------+------------
     1 |          3
    67 |          2
  1935 |          1
 45925 |          0
(4 lignes)
Soit 45925 valeurs qui ne se répètent pas, 1935 qui se répètent une fois etc...
test=# select 45925+1935*2+67*3+4;
 ?column? 
----------
    50000
(1 ligne)
ou de manière basique
test=# select sum(foo.decompte) from (select (repetition+1)*count(*) as decompte from mesure group by repetition ) as foo;
  sum  
-------
 50000
(1 ligne)

jeudi 9 octobre 2008

Prepare statements et insertion sur partitionnement de tables - Part II

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.

On commence par tout télécharger sur le site et on décompresse le fichier
[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.sql
    begin;
    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.sql
    begin;
    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 2008
Le 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