Forums PostgreSQL.fr

sbouchard

Membre

Durée d'exécution d'une requête

Bonjour,

Contexte : 
Etude de faisabilité pour une migration SQLServer 2005 vers PostgreSQL 8.4.3 d'un projet de Datawarehouse (modeste)

Environnement :
PostgreSQL 8.4.3 sur WindowsServer2008
Client pgAdmin sur un poste de travail WindowXP
Client psql sur un poste de travail WindowXP

Problème : Durée d'exécution d'une requête SQL dans les traitements ETL sous PostgreSQL

La requête :

DELETE FROM HISTO_PersoHoraires 
WHERE Numero NOT IN 
    (SELECT Numero_TEC FROM KEEP_PersoHoraires)
  AND DateArchivage IS NULL

Volume des données pour tests :
Table HISTO_PersoHoraires : 1'383'508 lignes
Table KEEP_PersoHoraires  : 1'383'508 lignes
Nombre de lignes satisfaisant le critère DateArchivage IS NULL : 1'379'998

Durée d'exécution : 
Dans SQLServer  - Environ 10 minutes
Dans PostgreSQL - Après 6h30 la requête est toujours en cours d'exécution

Structure des tables : 

spagobi_stagingarea=> \d histo_persoHoraires
                  Table "spagobi.histo_persohoraires"
          Column           |              Type              | Modifiers
---------------------------+--------------------------------+-----------
 numero                    | numeric(18,0)                  | not null
 num_formations            | numeric(18,0)                  |
 num_lieuxdeformations     | numeric(18,0)                  |
 num_persomodules          | numeric(18,0)                  |
 num_justifications        | numeric(18,0)                  |
 num_periodesenseignements | numeric(18,0)                  |
 ladate                    | timestamp(6) without time zone |
 estabsent                 | numeric(18,0)                  | not null
 estenvacances             | numeric(18,0)                  |
 datearchivage             | timestamp(6) without time zone |
 luetarchive               | numeric(18,0)                  |
 ajuser                    | character varying(120)         |
 ajdate                    | timestamp(6) without time zone |
 mouser                    | character varying(120)         |
 modate                    | timestamp(6) without time zone |
 mocount                   | numeric(18,0)                  |
Indexes:
    "histo_persohoraires_pkey" PRIMARY KEY, btree (numero)
    "xu_ladate" btree (ladate)
    "xu_num_lieuxdeformations" btree (num_lieuxdeformations)
    "xu_num_persomodules" btree (num_persomodules)


spagobi_stagingarea=> \d keep_persoHoraires
                            Table "spagobi.keep_persohoraires"
   Column   |     Type      |                          Modifiers
------------+---------------+--------------------------------------------------------------
 numero     | numeric(18,0) | not null default nextval('seq_keep_persohoraires'::regclass)
 numero_tec | numeric(18,0) | not null
Indexes:
    "pk_keep_persohoraires" PRIMARY KEY, btree (numero)

Plan d'exécution de la requête : 

spagobi_stagingarea=> EXPLAIN DELETE FROM HISTO_PersoHoraires
spagobi_stagingarea-> WHERE Numero NOT IN
spagobi_stagingarea->     (SELECT Numero_TEC FROM KEEP_PersoHoraires)
spagobi_stagingarea->   AND DateArchivage IS NULL;
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Seq Scan on histo_persohoraires  (cost=29136.00..14526900364.85 rows=689978 width=6)
   Filter: ((datearchivage IS NULL) AND (NOT (SubPlan 1)))
   SubPlan 1
     ->  Materialize  (cost=29136.00..46936.00 rows=1280000 width=8)
           ->  Seq Scan on keep_persohoraires  (cost=0.00..22856.00 rows=1280000 width=8)

Mes questions :
Ce problème provient-il d'un mauvais paramétrage de mon serveur base de données?
Devrais-je indexer différemment certaines colonnes?
Avez-vous une piste de recherche à m'indiquer?

Cela m'ennuie un peu de retravailler sur la requête car pour une première étude nous
voulions faire une migration 1-1, c'est-à-dire réécrire les traitements tels quels afin d'avoir un
comparatif des 2 environnements.

D'avance merci pour vos avis

Sylvie

flo

Membre

Re : Durée d'exécution d'une requête

Déjà il ne peut utiliser aucun index... est-ce que c'est normal?
Il n'y a en particulier pas d'index sur keep_persohoraires.numero_tec. Peut-être qu'avec cet index il pourrait utiliser un plan différent?

Dernière modification par flo (31/05/2011 17:56:37)

Marc Cousin

Membre

Re : Durée d'exécution d'une requête

Vous avez probablement un work_mem trop bas pour une requête de ce type. Avec la syntaxe 'IN', postgresql ne va pas savoir faire d'antijoin, et va donc partir sur ce genre de plan.

Non, aucun intérêt de passer par un index pour aller rechercher tous les enregistrements de keep_persohoraires. On ramène 100% des enregistrements.

Essayez, spécifiquement pour cette requête, d'augmenter le work_mem. Il est, je présume, à sa valeur par défaut de 1Mo ?
Par exemple, dans une session: set work_mem to '128MB'.

Hormis parcours de la table, pour une requête de ce type (avec à peu près la même volumétrie) et avec un select à la place du delete, je suis à 3 secondes d'exécution avec mon simple ordinateur portable (pour la partie filtrage bien sûr, je n'ai pas recréé toutes vos données…)

Par contre, je ne peux pas vous conseiller comme ça de passer le work_mem à 128Mo globalement dans le postgresql.conf: c'est un paramètre qui affecte toutes les sessions, ce qui fait que les sessions peuvent tous consommer beaucoup de mémoire avec ce paramètre à une valeur si élevée. C'est à réserver à des requêtes manipulant beaucoup de données en première approche. Sauf si vous êtes sure d'avoir peu de sessions en parallèle (ce qui est assez probable avec un datawarehouse…).

Pas la peine de réexécuter la requête, contentez vous d'un explain.

Donc :
set work_mem to '128MB';
explain…

---

Marc.

sbouchard

Membre

Re : Durée d'exécution d'une requête

Bonjour,

Merci pour votre réponse rapide, claire et précise! Vous êtes toujours aussi efficace!

Effectivement, en augmentant la valeur de ce paramètre pour la session, l'exécution de la requête dure 5 secondes. 

Cela me montre que j'ai encore bien des compétences à acquérir dans l'administration d'un serveur PostgreSQL!

Bonne journée!

Sylvie

Voici à titre documentaire le résultat de mon test avec le même jeu de données que précédemment :

spagobi_stagingarea=> set work_mem to '128MB';
SET

spagobi_stagingarea=> EXPLAIN DELETE FROM HISTO_PersoHoraires
spagobi_stagingarea-> WHERE Numero NOT IN
spagobi_stagingarea->   (SELECT Numero_TEC FROM KEEP_PersoHoraires)
spagobi_stagingarea-> AND DateArchivage IS NULL;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Seq Scan on histo_persohoraires  (cost=26056.00..63284.85 rows=689978 width=6)
   Filter: ((datearchivage IS NULL) AND (NOT (hashed SubPlan 1)))
   SubPlan 1
     ->  Seq Scan on keep_persohoraires  (cost=0.00..22856.00 rows=1280000 width=8)
(4 rows)


spagobi_stagingarea=> EXPLAIN ANALYSE DELETE FROM HISTO_PersoHoraires
spagobi_stagingarea-> WHERE Numero NOT IN
spagobi_stagingarea->   (SELECT Numero_TEC FROM KEEP_PersoHoraires)
spagobi_stagingarea-> AND DateArchivage IS NULL ;
                                                              QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on histo_persohoraires  (cost=26056.00..63284.85 rows=689978 width=6) (actual time=5004.884..5004.884 rows=0 loops=1)
   Filter: ((datearchivage IS NULL) AND (NOT (hashed SubPlan 1)))
   SubPlan 1
     ->  Seq Scan on keep_persohoraires  (cost=0.00..22856.00 rows=1280000 width=8) (actual time=0.047..1700.607 rows=1383508 loops=1)
 Total runtime: 5010.056 ms
(5 rows)

Marc Cousin

Membre

Re : Durée d'exécution d'une requête

Ça me semble plus raisonnable. Il ne s'agit que de hacher et comparer 1 million d'enregistrements entiers, après tout

Mais on est dans un cas où le meilleur plan a besoin de beaucoup de mémoire. Ce qu'on ne pourrait pas autoriser globalement sur une base transactionnelle avec 500 sessions, par exemple…

Si c'est un infocentre, et que vous connaissez approximativement le nombre de sessions, vous pouvez compter, pour ne pas prendre de risque, 3 à 5 tris/hachages simultanés par session en première approximation. Chaque session peut prendre (au besoin, elle ne va pas allouer 128Mo ici si elle n'en a pas besoin) autant de fois work_mem qu'elle effectue de tris ou hachages. Donc assurez vous simplement que vous n'aurez pas 50 sessions faisant simultanément 5 tris à 128Mo de work_mem: ça voudrait dire 50 X 5 X 128 Mo, soit 25 Go de ram.

Bref, le réglage de ce paramètre dépend vraiment du nombre de sessions effectuant des requêtes en même temps, de la complexité de ces requêtes, et de la mémoire sur le serveur.

Dernier point: si je comprends bien, c'est un nouveau projet. Pourquoi ne pas partir sur une 9.0 ? Il y a encore pas mal d'améliorations de l'optimiseur dans cette version, et vraiment peu de choses qui pourraient poser pb en termes de compatibilité. Il y en a encore quelques unes qui arrivent en 9.1 d'ailleurs (surtout si vous aviez besoin de recourir au partitionnement).

---

Marc.

sbouchard

Membre

Re : Durée d'exécution d'une requête

La migration vers la version 9.0.4 de mon serveur PostgreSQL est planifiée pour la semaine prochaine!

Merci pour ces conseils dans l'ajustement de ce paramètre work_mem.

Sylvie