Forums PostgreSQL.fr

krashtest31

Membre

Paramètrage mémoire pour un Data Warehouse

Bonjour à tous,

J'utilise une base PostgreSQL 9.4 comme Data Warehouse (grosses tables dénormalisées, plusieurs millions de lignes, peu de jointures)
Le serveur a 2 CPU Xeon à 2.5 Ghz et 16 Go de RAM. Il tourne sous Ubuntu 14
Le stockage est sur baie de SSD. La taille actuelle de la base est de +/- 100 Go

Après diverses lectures a droite à gauche sur le net, voici le paramétrage actuel de mon postgresql.conf

shared_buffers 3GB
work_mem 512MB
maintenance_work_mem 512MB
temp_buffers 256MB
Effective_cache_size 12GB
Wal_buffers 16MB
Check_point_segment 64
Checkpoint_timeout 30min
Checkpoint_completion_target 0.9
Checkpoint_warning 0
tcp_keepalives_idle 120
tcp_keepalives_interval 60
tcp_keepalives_count 10
synchronous_commit off
Default_statistics_target 400
Enable_seqscan off

Notes :
- les tables sont toutes créées en UNLOGGED
- il y a des indexs partout où cela est nécessaire
- La base est chargée toutes les nuits (7h de traitement), un gros VACUUM FULL + REINDEX + ANALYZE est lancé après l'alimentation.
- Il n'y a que du SELECT en journée

les requêtes tournent parfaitement bien en journée mais j'ai des difficultés lors de l'alimentation de mes tables de faits, notamment sur les requêtes d'alimentation utilisant des regroupements  (GROUP BY) sur les grosses tables.

Exemple tout bête sur la requête ci-dessous :

SELECT 
	r.id_immeuble,
	SUM ( CASE
		WHEN sens = 'D' THEN r.montant
		WHEN sens = 'C' and r.date_comptable < to_date ( '01/' || to_char ( current_date,'MM/YYYY') ,  'DD/MM/YYYY' ) THEN -r.montant
		ELSE 0
	END ) as SOLDE_RAPPRO_BQ,
	MAX ( r.date_lettrage ) as DATE_LETTRAGE_MAX,
	MAX ( r.date_comptable ) as DATE_RAPPRO_MAX

FROM	dwh_ics.itf_rappro_bq r

GROUP BY	r.id_immeuble

la table "dwh_ics.itf_rappro_bq" compte 13 millions de lignes, la requête dure +45min !!
il y a des index sur les champs "date_comptable", "sens" et "id_immeuble", les statistiques sont à jour. Je ne vois pas quoi faire d'autre ?

- Voyez vous quelque chose à modifier dans mon fichier de conf ?
- Comment savoir si la mémoire de mon serveur est saturée (linux utilise toute la mémoire par défaut) ?

Merci de votre retour !

rjuju

Administrateur

Re : Paramètrage mémoire pour un Data Warehouse

Vous n'avez pas précisé le nombre de connexions autorisées.  Il faudrait également positionner le paramètre random_page_cost à 1 si vous avez des SSD, et augmenter le paramètre effective_io_concurrency (entre 10 et 20 sur un bon ssd).

Pour le reste, pouvez-vous vous fournir un plan d'exécution (options buffers, analyze et verbose activées) de la requête quand elle est lente ? (auto_explain peut vous aider).

Pour la mémoire, regardez vos métriques de supervision pour savoir si la mémoire le cache descend à certains moments, et s'il l'utilisation des disques est importante ou non.

---

Julien.
https://rjuju.github.io/

krashtest31

Membre

Re : Paramètrage mémoire pour un Data Warehouse

Merci de votre réponse rapide

1) mon paramètre "max_connections" est à "50" mais je dois rarement dépasser les 10 sessions simultanées

2) je viens de basculer le paramètre "random_page_cost" à 1.0 et "effective_io_concurrency" à 10 dans le conf, test cette nuit

3)  Voici le plan d'execution de la requete ci-dessus retourné au bout de 28 minutes :

GroupAggregate  (cost=10009498003.17..10011689441.20 rows=5009002 width=45) (actual time=1404978.045..1728692.948 rows=20677 loops=1)"
  Output: id_immeuble, id_agence, id_base, sum(CASE WHEN ((sens)::text = 'D'::text) THEN montant WHEN (((sens)::text = 'C'::text) AND (date_comptable < to_date(('01/'::text || to_char((('now'::cstring)::date)::timestamp with time zone, 'MM/YYYY'::text)), ' (...)"
  Group Key: r.id_immeuble, r.id_agence, r.id_base"
  ->  Sort  (cost=10009498003.17..10009623228.20 rows=50090012 width=45) (actual time=1404973.381..1665300.805 rows=50088348 loops=1)"
        Output: id_immeuble, id_agence, id_base, sens, montant, date_comptable, date_lettrage"
        Sort Key: r.id_immeuble, r.id_agence, r.id_base"
        Sort Method: external merge  Disk: 2884536kB"
        ->  Seq Scan on dwh_ics.itf_rappro_bq r  (cost=10000000000.00..10001551129.12 rows=50090012 width=45) (actual time=1.761..63870.558 rows=50088348 loops=1)"
              Output: id_immeuble, id_agence, id_base, sens, montant, date_comptable, date_lettrage"
Planning time: 0.676 ms"
Execution time: 1728934.307 ms"

4) J'utilise zabbix comme outil de supervision, je vaisme reseigner auprés de l'admin sys pour ajouter des composants de suivi

rjuju

Administrateur

Re : Paramètrage mémoire pour un Data Warehouse

Je doute que cela soit la même requête, vu que celle-ci fait un group by sur 3 colonnes et non une seule.  Et la table contient 50 millions de lignes, pas 13.

La majorité du temps de cette requête est passé à trier ces 50 millions de lignes.  Vous pouvez essayer avec un work_mem bien plus haut (plusieurs Go), mais il serait sans doute plus efficace de créer un index sur (id_immeuble, id_agence, id_base).

---

Julien.
https://rjuju.github.io/

ruizsebastien

Membre

Re : Paramètrage mémoire pour un Data Warehouse

bonjour,

au passage, je vous conseille de supprimer tous vos index avant le chargement de chaque nuit puis le vacuum full puis recréer les index.
Ce devrait être plus rapide comme ça.

---

Cordialement,

Sébastien.

rjuju

Administrateur

Re : Paramètrage mémoire pour un Data Warehouse

+1

Et au passage j'ai oublié de préciser un VACUUM FULL suivi d'un REINDEX ne sert plus à rien depuis la version 9.0 : le REINDEX est fait par le VACUUM FULL.

---

Julien.
https://rjuju.github.io/

gleu

Administrateur

Re : Paramètrage mémoire pour un Data Warehouse

J'ajoute mon petit grain de sel. En ne lisant que le plan d'exécution, on voit que tout le temps est passé sur le tri. PostgreSQL doit créer un fichier sur disque de pratiquement 3 Go pour effectuer ce tri. Comme il n'est guère raisonnable de vouloir augmenter work_mem à ce type de valeur, un index sur les trois colonnes triées pourrait être une excellente solution, surtout s'il est stocké sur un SSD.

---

Guillaume.

krashtest31

Membre

Re : Paramètrage mémoire pour un Data Warehouse

Merci à tous pour vos réponses, j'ai l'impression d'avoir + appris sur Postgres en 1 journée qu'au cours des 6 derniers mois.

Alors, concernant ma requête, effectivement, je n'ai besoin que d'1 seule colonne pour faire mon update (id_immeuble) et elle est indexée. Dans le plan d'execution, je passe d'un "groupaggegate" à un "hashaggregate" sans le "sort". Le temps d’exécution est réduit à 10 minutes, c'est formidable.

Grace à vos conseils, je suis en train de réécrire et optimiser une bonne partie de mes scripts d'alimentation

Dernière modification par krashtest31 (13/10/2017 15:15:24)