Vous n'êtes pas identifié(e).

Pages : 1

#1 Re : Optimisation » Optimisation d'une requete » 28/05/2018 11:35:24

krashtest31

Bonjour et merci de vos réponses


> Connexion locale ou distante ?
Il s'agit d'une connexion distante mais j'ai exécuté la requête sur le serveur par acquis de conscience, les temps d’exécution sont identiques


> Combien de lignes contiennent les tables concernées ?
ods_transac.compromis 34 130
ods_transac.prospect 250 145
ods_transac.employe 2 264
ods_transac.lot 371 383
ods_transac.mandat_transaction 372 158
ods_transac.notaire 36 083


> Quelle est la configuration matérielle ?
3 CPU Xeon 2.5 GHz, 16 Go RAM, baie de disque SSD en RAID


> Quelle version de PostgreSQL ?
9.4


> Il y a une énorme sous-estimation du nombre de lignes renvoyés par le parcours d'index sur la table lot (1 ligne estimée en retour, 370540 lignes renvoyées).
> Il serait intéressant de savoir pourquoi et de corriger cela. Cela permettrait d'avoir un plan plus intéressant.


J'utilise le paramètre "default_statistics_target" à 400 dans le "postgresql.conf"
il y a un ANALYZE tous les soirs (et un VACUUM FULL ANALYZE tous les week-end)
Que puis-je faire d'autre ?



J'ajoute que c'est une des seules requêtes qui me pose problème, les autres du même type s’exécutent parfaitement
dommage qu'on ne puisse pas ajouter des HINT comme Oracle...
Il y a par exemple un index sur le champ "affaire" de la table "compromis" qui n'est pas utilisé, je me demande pourquoi ? ( pour la jointure c.affaire = o.lot_id )

#2 Optimisation » Optimisation d'une requete » 24/05/2018 10:11:03

krashtest31
Réponses : 3

Bonjour,


Je me casse les dents sur une requête inexplicablement lente.
Elle prend 26 minutes pour seulement 300 000 lignes ramenées


Tous les index sont en place, les stats sont calculées, les tables ont des VACUUM quotidien, bref ....

Voici la requête :


select
	c.compromis_id as ID_COMPROMIS,
	e.societe_id as ID_SOCIETE,
	c.affaire as ID_LOT,
	o.proprietaire_id as ID_PROPRIETAIRE,
	n_v.perso as ID_NOTAIRE_VENDEUR,
	p.perso as ID_ACHETEUR,
	n_a.perso as ID_NOTAIRE_ACHETEUR,
	e.user_id ::integer as ID_EMP_NEGO,
	c.negoext1 as ID_NEGO_EXT1,
	c.negoext2 as ID_NEGO_EXT2,
	c.negoext3 as ID_NEGO_EXT3,
	c.date as DATE_SIGN_COMPROMIS,
	c.datelimite as DATE_CONDITIONS_SUSP,
	c.dateprevuevente as DATE_PREVUE_VENTE,
	c.dateeffectivevente as DATE_EFFECTIVE_VENTE,
	c.dateencaissement as DATE_ENCAISSEMENT,
	c.datesupprvente as DATE_ANNUL_VENTE,
	c.conditionsuspensive as LIB_CONDITIONS_SUSP,
	m.prixinitiale::double precision as PRIX_INITIAL,
	m.prixvente::double precision as PRIX_VENTE,
	m.tauxcomm::double precision as TAUX_COMM,
	( m.prixvente*(m.tauxcomm/100) )::double precision as COMMISSION_AGENCE_TTC,
	c.tva::double precision as TAUX_TVA,
	( ( m.prixvente*(m.tauxcomm/100) ) / (1+(c.tva/100)) )::double precision as COMMISSION_AGENCE_HT,
	(m.prixvente - ( m.prixvente*(m.tauxcomm/100) ))::double precision as PRIX_NET_VENDEUR,
	c.partaffaire::double precision as POURC_PART_SORTANTE,
	c.partacheteur::double precision as POURC_PART_ENTRANTE,
	coalesce(c.partExt1,0) + coalesce(c.partExt2,0) + coalesce(c.partExt3, 0) ::double precision as POURC_PART_EXT,
	CASE
		WHEN 	c.datesupprvente is null then ( ( m.prixvente*(m.tauxcomm/100) ) / (1+(c.tva/100)) ) * ( coalesce(c.partacheteur,0) + coalesce(c.partaffaire,0) )
		ELSE 0
	END ::double precision as CA_HT,
	CASE
		WHEN c.dateencaissement IS NOT NULL THEN 'Vente encaissée'
		WHEN c.datesupprvente IS NOT NULL THEN 'Vente annulée'
		WHEN c.dateeffectivevente IS NOT NULL THEN 'Vente effective'
		WHEN c.date IS NOT NULL THEN 'Vente prévue'
		ELSE null
	END as STATUT_VENTE

from
	ods_transac.compromis c
		LEFT OUTER JOIN ods_transac.prospect p ON ( c.acheteur=p.prospect_id )
		LEFT OUTER JOIN ods_transac.employe e ON ( p.nego_id = e.user_id )
		LEFT OUTER JOIN ods_transac.lot o ON ( c.affaire = o.lot_id )
		LEFT OUTER JOIN ods_transac.mandat_transaction m ON ( o.mandat_id = m.mandat_id )
		LEFT OUTER JOIN ods_transac.notaire n_a ON ( c.notaireacheteur = n_a.notaire_id )
		LEFT OUTER JOIN ods_transac.notaire n_v ON ( c.notairevendeur = n_v.notaire_id )

where
	c.flag_integration = -1

et voici l'explain analyze : https://explain.depesz.com/s/k6OI

Si vous avez la moindre piste ...
Merci d'avance

#3 Re : Général » Coupure électrique -> base redémarrée vide :-/ » 05/03/2018 12:21:16

krashtest31

Merci pour l'explication, c'est clair et imparable

Je vais repasser en LOGGED les tables qui contiennent des archivages et qui ne sont pas rechargées par le J-1
Dommage pour les perfs

#4 Général » Coupure électrique -> base redémarrée vide :-/ » 05/03/2018 11:15:43

krashtest31
Réponses : 2

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


Je réalise des sauvegardes quotidiennes manuellement (via un batch de pg_dump) de certains schémas seulement.


Il y a quelques jours (pendant mes vacances tant qu'on y est), coupure électrique sur le serveur vers 10h le matin ! (pas d'insert/update en journée à ce moment là)

Je vois dans les logs que la base s'est relancée lorsque le serveur a redémarré :

2018-03-01 10:08:25 CET FATAL:  le système de bases de données se lance
2018-03-01 10:08:26 CET LOG:  le système de bases de données n'a pas été arrêté proprement ; restauration  automatique en cours
2018-03-01 10:08:28 CET LOG:  les protections contre la réutilisation d'un membre MultiXact sont maintenant activées
2018-03-01 10:08:28 CET LOG:  le système de bases de données est prêt pour accepter les connexions

Je constate que la base est vide ! Pourquoi ne garde-t-elle pas les données présentes au moment de la coupure ?
Que signifie la mention "restauration automatique en cours" ? (je ne lui ai rien demandé) ?

Cela a-t-il un lien avec la variable "restart_after_crash=on" du fichier conf ?


Merci de votre aide

#5 Re : Général » Aucun espace disponible sur le périphérique ?! » 09/01/2018 12:33:35

krashtest31

Merci de votre réponse,


je viens de comprendre grâce à zabbix, qu'un traitement déclenché vers 14h30 hier (???) a fait gonfler mon tablespace TBS_DWH_DATA jusqu’à presque 100 Go !


1515492971-2018-01-09-11-14-03.png


je n'ai rien dans les logs postgresql ?

une idée pour comprendre ce qui s'est passé ?

#6 Général » Aucun espace disponible sur le périphérique ?! » 09/01/2018 11:44:24

krashtest31
Réponses : 3

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


Lors de l'alimentation de cette nuit, j'ai eu une erreur bizarre (extrait des logs de l'ETL Talend):
org.postgresql.util.PSQLException:ERREUR: n'a pas pu étendre le fichier « pg_tblspc/16417/PG_9.4_201409291/16406/28233551 » :
Aucun espace disponible sur le périphérique Indice : Vérifiez l'espace disque disponible.


les logs de la base indiquent :
CET ERREUR:  n'a pas pu écrire le bloc 9757302 du fichier temporaire : Aucun espace disponible sur le périphérique


C'est juste incompréhensible, le commande df -h me donne 86 Go de libre sur la partition !?


Sys. de fichiers Taille Utilisé Dispo Uti% Monté sur
udev               7,9G     12K  7,9G   1% /dev
tmpfs              1,6G    552K  1,6G   1% /run
/dev/dm-0          189G     95G   86G  53% /
none               4,0K       0  4,0K   0% /sys/fs/cgroup
none               5,0M       0  5,0M   0% /run/lock
none               7,9G    8,0K  7,9G   1% /run/shm
none               100M       0  100M   0% /run/user
/dev/sda1          236M    187M   37M  84% /boot


des idées ?
Merci d'avance

#7 Re : Optimisation » Paramètrage mémoire pour un Data Warehouse » 13/10/2017 15:14:54

krashtest31

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

#8 Re : Optimisation » Paramètrage mémoire pour un Data Warehouse » 12/10/2017 16:33:29

krashtest31

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

#9 Optimisation » Paramètrage mémoire pour un Data Warehouse » 12/10/2017 15:15:57

krashtest31
Réponses : 7

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 !

Pages : 1

Pied de page des forums

Propulsé par FluxBB