Vous n'êtes pas identifié(e).
Pages : 1
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 offNotes :
- 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_immeublela 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 !
Hors ligne
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/
Hors ligne
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
Hors ligne
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/
Hors ligne
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.
Hors ligne
+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/
Hors ligne
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.
Hors ligne
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)
Hors ligne
Pages : 1