Encore un problème curieux...

Bonjour à tous,

Ceux d'entre vous qui étaient déjà abonnés à la liste début juin se
souviennent peut-être d'un curieux problème de performance que je leur
avais soumis au sujet de l'intégration massive de données puisées dans
des fichiers CSV. En voici aujourd'hui un de la même trempe :

J'intègre en base plus de 5 millions enregistrements puisés dans un
fichier CSV. Ces enregistrements sont bruts et requièrent donc
d'importants traitements individuels (mise en forme, filtrage et
validation) avant d'être insérés en base. Ce travail préparatoire
m'interdit de passer par un brutal COPY FROM et je confie donc
l'ensemble du processus d'intégration à un script Perl.

Pour chacun de ces enregistrements, le script Perl effectue entre un
et trois SELECT, puis un INSERT.

Au départ, la table alimentée est *vide*.

Le mode opératoire de mon script est le suivant :

1. Ouverture d'une transaction

2. Traitement et insertion de 10 000 lignes

3. Clôture de la transaction

4. Exécution d'un 'ANALYZE' et, 1 fois sur 10 (i.e., tous les 100 000
lignes), d'un 'CLUSTER'

5. Retour au point 1.

A chaque boucle, mon script affiche le temps d'intégration des 10 000
dernières lignes et voici ce que cela donne :

10000 lignes traitées en 51 secondes ( 10000 lignes au total)
10000 lignes traitées en 143 secondes ( 20000 lignes au total)
10000 lignes traitées en 222 secondes ( 30000 lignes au total)
10000 lignes traitées en 313 secondes ( 40000 lignes au total)
[...]
10000 lignes traitées en 1884 secondes ( 230000 lignes au total)
10000 lignes traitées en 1912 secondes ( 240000 lignes au total)
[...]
10000 lignes traitées en 8191 secondes ( 910000 lignes au total)
10000 lignes traitées en 8347 secondes ( 920000 lignes au total)

Là, mon script a tourné pendant tout le week-end et je décide donc d'y
mettre fin brutalement par un petit Ctrl-C.

Je le relance dans la foulée et voici ce qui se passe (après qu'il ait
ignoré les lignes déjà traitées) :

10000 lignes traitées en 14 secondes ( 930000 lignes au total)
10000 lignes traitées en 16 secondes ( 940000 lignes au total)
10000 lignes traitées en 15 secondes ( 950000 lignes au total)
10000 lignes traitées en 16 secondes ( 960000 lignes au total)
10000 lignes traitées en 15 secondes ( 970000 lignes au total)
10000 lignes traitées en 16 secondes ( 980000 lignes au total)
[...]
10000 lignes traitées en 16 secondes (5250000 lignes au total)
10000 lignes traitées en 16 secondes (5260000 lignes au total)
10000 lignes traitées en 17 secondes (5270000 lignes au total)
10000 lignes traitées en 19 secondes (5280000 lignes au total)
10000 lignes traitées en 16 secondes (5290000 lignes au total)

Ce résultat est parfaitement reproductible et je l'ai constaté sur
deux machines différentes :
- Debian testing (paquet postgresql 7.4.7-6sarge1)
- Debian unstable (paquet postgresql 7.4.8-17)

Je dois bien vous dire qu'il me laisse fort perplexe... Si mon schéma
était mauvais, mes requêtes maladroites ou mon script codé avec les
pieds, je ne constaterais pas d'amélioration après l'avoir brutalement
arrêté et relancé. Or là, c'est le jour et la nuit, aussi bien en
terme de temps unitaire qu'en terme d'évolution lorsque le nombre de
tuples augmente.

Une fois encore, il me semble prendre l'optimiseur de PostgreSQL en
défaut et je suis complètement désemparé...

La table alimentée a la structure suivante :

----------------------------------------------------------------------

CREATE SEQUENCE seq_rel_id MINVALUE 1 START 1;

CREATE TABLE rel
(
rel_id INT DEFAULT nextval('seq_rel_id')
NOT NULL UNIQUE,
abo_id INT NOT NULL,
srv_id INT NOT NULL,
hoda TIMESTAMP(0) NOT NULL,
consolide BOOLEAN DEFAULT FALSE,

CONSTRAINT idx_rel_cle_primaire
PRIMARY KEY (rel_id),

CONSTRAINT rel_abo_connu
FOREIGN KEY (abo_id)
REFERENCES abo (abo_id)
ON UPDATE CASCADE
ON DELETE CASCADE,

CONSTRAINT rel_srv_connu
FOREIGN KEY (srv_id)
REFERENCES srv (srv_id)
ON UPDATE CASCADE
ON DELETE CASCADE
);

CREATE INDEX idx_rel_abo_id ON rel(abo_id);

CREATE INDEX idx_rel_srv_id ON rel(srv_id);

CREATE INDEX idx_rel_hoda ON rel(hoda);

CLUSTER idx_rel_abo_id ON rel;

----------------------------------------------------------------------

Les champs abo_id et srv_id des tables abo et srv auxquelles il est
fait référence sont les clés primaires de ces tables. Les index
adéquats ont donc été créés.

Pour info, les tables abo et srv contiennent respectivement 400 000 et
3 000 tuples mais cela est sans grande importance.

Savez-vous ce que je pourrais faire pour améliorer la situation ?

Je vous remercie par avance,

Sébastien

--
Sébastien Dinot, sebastien(dot)dinot(at)free(dot)fr
http://sebastien.dinot.free.fr/
Ne goûtez pas au logiciel libre, vous ne pourriez plus vous en passer !