[Projet Master2] Import de données en masse pour application statistique [Résolu]

[bilou972]

Bonjour,
pour mon projet de fin d'étude de Master Informatique, je dois réaliser une application qui permet de charger des fichiers historiques concernant l'année 2007, sur trois départements. Il faut compter 20 000 lignes par fichier, pour 12 mois.
12 x 3 x 20 000 je vous laisse imaginer la quantité de données à charger.
La création de clé primaire en amont étant anarchique (et buggée ? Je précise que je ne peut rien faire à ce niveau), il arrive de retrouver plusieurs fois la meme clé sur le meme département ou d'un département à l'autre, ma clé primaire est donc composée du numéro d intervention (entier), de l'id du departement(varchar 2 caractères mais je n'ai pas défini cette limite) ainsi que d'un autre champ unique trouvé (varchar non limité).
Au début de l'importation je ma clé primaire n'était composée que de deux champs, et je trouvai que l'importation par COPY FROM etait assez rapide malgré la quantité de données. J'ai du ajouter un troisième champ, et depuis c'est de plus en plus lent et je me trouve confronté a des timeout (max 30 sec of execution sur php).

J'aimerai votre avis, puis-je supprimer la clé primaire ? Cela va il impacter la rapidité de mon script (en bien)? Ou dois-je créer une nouvelle clé primaire pour remplacer cette clé composite ?

De manière plus générale, j'aimerais que vous me conseillez sur les bonne pratiques à suivre pour la mise en place et la bonne utilisation d'une telle base.

Merci à ceux qui seront arrivés jusqu'au bout de ce message, ce fut long mais j'ai tenté de décrire au mieux le contexte de la mise en place.

[scheu]

Pour des perfs optimales, c'est mieux de ne pas avoir de clé primaire sur la table, mais attention à ne pas insérer de doublons ;-)

[bilou972]

Ah, oui je n'y avai pas pensé, il y a le risque d'insérer des doublons.
Donc que me conseilles tu ?

L'absence de clé primaire permet elle aussi d'améliorer les performances au niveau des COUNT ?

[scheu]

Non l'absence de clé primaire améliore les perfs juste pour les insert et update, car à chaque fois il contrôle justement avant de modifier s'il n'y a pas de doublons, ce qui peut être coûteux pour de gros volumes
Une clé primaire crée aussi implicitement un index qui est mis à jour à chaque modif (insert/update/delete) qui peut être coûteux aussi

Sur les select, les PK peuvent même améliorer les perfs car postgresql utilisera peut être l'index si le select ne ramène pas trop de lignes
Regarde la doc sur l'optimiseur Postgresql avec notamment le calcul des stats et les plans d'exécution

[bilou972]

Après avoir fait quelques tests, je peux parler plus en détail des performances au niveau des requêtes.

Code :

1
2
3
4
5
6
 
SELECT count("No INTERVENTION"),"CODE_INTERVENTION",e."SECTEUR","DATE IT" 
FROM histo h 
JOIN "ETL" e ON h."ILOT"=e."UI" 
AND h."BASE_GPC"=e."BASE_GPC" 
AND "DATE IT" BETWEEN '01/05/2007' AND '31/05/2007' AND h."BASE_GPC"='1G' GROUP BY "DATE IT","CODE_INTERVENTION",e."SECTEUR" ORDER BY "SECTEUR","CODE_INTERVENTION";

Une requête comme celle-ci prend en moyenne 90 secondes pour s'exécuter (je précise qu'il y a 37 000 lignes estimées), est-ce normal ou y'a t'il des choses que je puisse faire pour en améliorer les performances?

Pour scheu, j'ai lu le document et d'apres le explain la première opération qu'il fait est un 'group agrégate', et il fait les filter (clause where) lors des dernière opérations. J'ai vaguement souvenir d'un cours de licence ou l'on parlait d'optimisation des requêtes en commencant par faire les clauses where mais dans ce cas précis j'aurai besoin d'un coup de pouce.

[scheu]

Quelles sont les volumétries (nb de lignes) de tes tables, et le nb de lignes retournées par ta requête ?
Les stats sur ces 2 tables sont-elles à jour ?
As-tu des indexes sur ces 2 tables ? Sur quelles colonnes ?
Mets-nous le résultat de "explain ta_requete;"

[bilou972]

• Table histo : 37 000 lignes estimées ( non comptées)
• etl : 240 lignes comptées

Pour les stats : j'ai fai un vacuum analyse tel que postgres m'a conseillé, mais je n'ai pa vu la différence et postgres ne m'a pa suggéré de creer un index. Peut être que je n'ai pas vu ?

• La table histo à un index sur No Intervention, Base_gpc et liste_a (colonne non utilisée dans cette requete)
• la table etl n'a pas d'index.

Code :

1
2
3
4
5
6
7
8
9
10
"GroupAggregate  (cost=59869.42..60033.69 rows=6571 width=40)"
"  ->  Sort  (cost=59869.42..59885.85 rows=6571 width=40)"
"        Sort Key: e."SECTEUR", h."CODE_INTERVENTION", h."DATE IT""
"        ->  Hash Join  (cost=9.44..59452.76 rows=6571 width=40)"
"              Hash Cond: ((h."ILOT")::text = (e."UI")::text)"
"              ->  Seq Scan on histo h  (cost=0.00..59300.69 rows=12307 width=41)"
"                    Filter: (("DATE IT" >= '2007-05-01'::date) AND ("DATE IT" <= '2007-05-31'::date) AND (("BASE_GPC")::text = '1G'::text))"
"              ->  Hash  (cost=7.99..7.99 rows=116 width=25)"
"                    ->  Seq Scan on "ETL" e  (cost=0.00..7.99 rows=116 width=25)"
"                          Filter: ('1G'::text = ("BASE_GPC")::text)"

Voila! je te remercie pour ton aide.

[scheu]

Combien de lignes de la table histo vérifient la condition "DATE IT" BETWEEN '01/05/2007' AND '31/05/2007' AND "BASE_GPC"='1G' ?

Combien de lignes de la table etl vérifient la condition "BASE_GPC"='1G' ?

Combien de temps prend juste la requête suivante :

Code :

SELECT * FROM histo WHERE "DATE IT" BETWEEN '01/05/2007' AND '31/05/2007' AND "BASE_GPC"='1G'

[bilou972]

Je réponds rapidement en citant :

Envoyé par scheu

Combien de lignes de la table histo vérifient la condition "DATE IT" BETWEEN '01/05/2007' AND '31/05/2007' AND "BASE_GPC"='1G' ?

10 500 lignes

Combien de lignes de la table etl vérifient la condition "BASE_GPC"='1G' ?

116 lignes

Combien de temps prend juste la requête suivante :

Code :

SELECT * FROM histo WHERE "DATE IT" BETWEEN '01/05/2007' AND '31/05/2007' AND "BASE_GPC"='1G'

99 secondes

[scheu]

Le problème vient donc juste du filtre WHERE "DATE IT" BETWEEN '01/05/2007' AND '31/05/2007' AND "BASE_GPC"='1G' sur la table histo, vu que ça prend la totalité du temps de la requête
Quels sont les types de données des colonnes date_it et base_gpc ?
Combien de temps met la requête SELECT * from histo ?
Indique le résultat de "explain SELECT * from histo"

As-tu configuré le fichier postgresql.conf ou bien as-tu tout laissé par défaut à l'installation ? Dans le fichier postgresql.conf, quelles sont les valeurs des paramètres shared_buffers, work_mem, temp_buffers, maintenance_work_mem ?

Combien de RAM as-tu sur ton serveur ?

[bilou972]

Envoyé par scheu

Le problème vient donc juste du filtre WHERE "DATE IT" BETWEEN '01/05/2007' AND '31/05/2007' AND "BASE_GPC"='1G' sur la table histo, vu que ça prend la totalité du temps de la requête
Quels sont les types de données des colonnes date_it et base_gpc ?

date it : date
base gpc :character varying

Combien de temps met la requête SELECT * from histo ?

258 secondes soit 4,3 minutes

Indique le résultat de "explain SELECT * from histo"

"Seq Scan on histo (cost=0.00..56493.68 rows=374268 width=2180)"

As-tu configuré le fichier postgresql.conf ou bien as-tu tout laissé par défaut à l'installation ?

Tout est par défaut.

Dans le fichier postgresql.conf, quelles sont les valeurs des paramètres shared_buffers, work_mem, temp_buffers, maintenance_work_mem ?

shared_buffers = 32MB
#work_mem = 1MB (J'ai laissé le # car c'est commenté je suppose)
#temp_buffers = 8MB
#maintenance_work_mem = 16MB

Combien de RAM as-tu sur ton serveur ?

Ce n'est pas un serveur, pour le moment tout est sur ma machine de développement qui possède 1Go de ram. Par la même occasion si tu as une préconisation pour le choix du serveur, je t'en prie.
(Désolé j'ai pas répondu hier).

[scheu]

4 minutes pour parcourir une table de 300 000 lignes ça paraît beaucoup quand-même.
Le problème doit venir soit de ton paramétrage postgresql, soit du hardware

Si y a pas grand chose d'autre qui tourne sur ton pc, essaie d'augmenter le shared_buffers à 256MB, et le work_mem à 32MB, de reloader la conf (pg_ctl reload -D <rep>), et de retester

Sinon c'est sans doute ton disque qui est lent, en tous les cas ce n'est pas un problème lié à ta requête

[bilou972]

Ok merci.
Je ferai la configuration postgres sur le serveur quand je déploierai.