Postgresql et datawarehouse

[brunolf]

Bonjour à tous,

J'étudie actuellement la migration de mon datawarehouse d'oracle vers postgresql (8.2 pour le moment, je vais prochainement essayer la 8.3).

Quelques infos sur la base datawarehouse :

- environ 100 Go
- Plus grosses tables : environ 30 millions de lignes
- Modèle en étoile

Les premiers tests de compatibilité avec nos outils BI (cognos8) sont plutot concluants, je n'ai pas détecté d'incompatibilité bloquante pour le moment.

J'ai cependant un gros problème en ce qui concerne l'exécution des requêtes de mise à jour de cubes d'analyse.

Ce type de requête à la particularité d'être exécutée de nuit, et de remonter un grand nombre d'enregistrements ( 30 millions dans mon cas )

L'exécution de la requête démarre bien, mais la consommation de mémoire sur le serveur est gigantesque ! le process postmaster consomme toute la ram (4Go sur ce serveur) puis rempli petit à petit le swap jusqu'au blocage de la machine.

J'ai déjà modifié certains paramètres dans le fichier postgresql.conf :
- shared_buffers = 1048576 (1024 Mo)
- work_mem = 65536 (64 Mo)
- temp_buffers = 51200 (50 Mo)
- maintenance_work_mem = 262144 (256 Mo)

Depuis ces changements, la performance des requêtes s'est considérablement améliorée, mais j'ai toujours mon problème de consommation mémoire et de swap.

C'est comme si les données extraites étaient "retenues" en mémoire par postgres .... ce qui ne m'intéresse pas du tout, vu que ce type de requête n'est exécutée qu'une seule fois par jour.

Pour ceux qui ont eu le courage de me lire, voici mes questions :

- Existe-t-il un paramètre du type "Durée de rétention", nous permettant de dire à Postgres "d'oublier" rapidement les données extraites afin de limiter la consommation mémoire et le swap ?

- Avez vous un retour d'expérience sur l'utilisation de postgres en mode datawarehouse et sur la façon de le configurer pour ce type d'utilisation ?

D'avance merci !

[bustaf]

Bonjour
hexdump -C /proc/kcore (pour lister la mémoire active) (ctrl c pour arreter)
echo 3 > /proc/sys/vm/drop_caches (complètement inutile c'est déjà géré par le système)
echo 1 > /proc/sys/vm/drop_cache (complètement inutile c'est déjà géré par le système)
commande sync (pour un flush buffers du FS)
Pour la mémoire
La partition swap (pagination) est gérée par le système comme une entité globale.
Le swap s'active si vous surpassez la mémoire disponible. (tous processus confondus)
Pour la mémoire vous pouvez gérer avec certains utilitaires une notion de priorité processus.
Il n'y pas vraiment de configuration miracle pour des tables unitaires de 30 millions
d'enregistrements ...
Il m'arrive d'utiliser Postgresql pour analyser de l'échantillonnage automate qui genère
des quantités de données largement plus importantes que celles que vous énoncez.
Je repartis correctement la charge pour m'aligner a un concept de fonctionnement plausible.
Bon courage

[scheu]

Les paramètres de mémoire que tu as mis sont peut-être trop importants et dépassent la taille physique de ta RAM (néanmoins 4 Go de RAM pour un datawarehouse de 100 Go c'es peu malgré tout)

L'augmentation de ces paramètres n'est pas la seule piste d'optimisation
De toute façon avec 100 Go de données en datawarehouse, tout ne tiendra pas en cache et il y aura forcément des accès disque

Regarde les requêtes les plus longues, les plans d'exécution
Fais régulièrement des vacuum et des calculs de stats, ajoute peut-être des indexes, ...

[brunolf]

Bonjour,

Vous m'inquiétez ... actuellement ma base tourne en production sous Oracle avec 2,5 Go de mémoire, et ça se passe plutôt pas mal.

Les requêtes les plus longues sont identifiées, en gros, elles n'attaquent qu'une table, et la descendent entièrement (presque 30 Millions de lignes)

Je ne voie pas trop comment changer cela puisque c'est exactement ce que je veux faire.

Ce genre de requête est très longue (2-3h sur oracle).
En comparant les perfs d'oracle et de postgres sur des volumes de données limités, j'aurais tendance à dire que cela va même plus vite sur postgres.

Le seul pb est le chargement du swap qui fini par saturer la machine...

Je fais quelques tests cette semaine en version 8.3, je vous tiens au courant d'une éventuelle évolution de mon problème.

[scheu]

Diminue tes paramètres pour éviter de swapper ça ira peut-être mieux ... Vouloir consommer plus de mémoire que le serveur en a est souvent pire que mieux ...

[bustaf]

Bonsoir
Je pense que Scheu a écrit l'inverse de ce qu'il voulait dire..
Plus vous diminuez la mémoire relative au processus Postgresql
plus le système va swapper...
Le problème avec le swap système..
Un pid qui swap est restreint par les agents sémaphores du système
il ne peut pas s'approprier une quantité de mémoire gigantesque a lui seul..
C'est pour cette raison que la taille de la partition de swap est relative à la quantité de mémoire disponible sur la machine.
Meme si vous avez un Peta en swap avec des roquettes pardon des requetes sur 30 millions de lignes cela va être difficile.

Votre remarque:
L'exécution de la requête démarre bien, mais la consommation de mémoire sur le serveur est gigantesque ! le process postmaster consomme toute la ram (4Go sur ce serveur) puis rempli petit à petit le swap jusqu'au blocage de la machine.
Il y a peut être une solution (je ne l 'ai pas testé) pour éviter le crach
le tmpfs

http://www.ibm.com/developerworks/library/l-fs3.html

Postgresql est un moteur de base puissant de même facture que Oracle ou Db2 etc ...
SI vous voulez des solutions précises
informez votre O/S actif , votre hardware
Le nombre de champs dans la table
le type de requete affectée.
Je pense que sous DVC on est capable de vous donner une solution

NB
Si LINUX
Regardez les paramètres kernel actifs; au shell tapez
cat /proc/sys/kernel/shmmax
cat /proc/sys/kernel/shmall

Regardez la doc PG
16.4.1. Mémoire partagée et sémaphore
Vous devez monter shmmax pour vous aligner avec la configuration (fille) que represente
PG,c'est le sytème qui se trouve au plus au niveau....
Aligner egalement votre valeur parametre instruite par (ulimit -s) avec PG c'est dans la doc

Bon courage...

[bustaf]

Bonjour
(mes propos précédents)
Il y a peut être une solution (je ne l 'ai pas testé) pour éviter le crach
le tmpfs

J'ai effectué des recherches sur le tmpfs pour voir si il est possible de mieux
contrôler la charge système et améliorer les perfs de PG. (wal etc...)
Pour la gestion mémoire:
find / -name pgsql_tmp -print me donne les répertoires des fichiers de temporisation
Si je monte ou je crée un lien sur ce point de montage spécifique paramétré avec des valeurs
restreintes le système de swap va se déclencher en fonction d'un éventuel débordement des ces valeurs
allouées.(Il faut également aligner sort_mem qui gère l'indice de déclenchement)
Donc théoriquement je dois contrôler le déclenchement prématuré du swap système sur l'instance précise
de Postgresql avant u(n éventuel débordement calqué sur l'entièreté de la mémoire système. (Faudrait t'il qu il soit encore capable d'arrêter un débordement du swap...)
La gestion mémoire est très complexe je pense qu'il faut faire des essais
réels avant de déduire le bien fondé ou la véracité d'une telle procédure.

Quelques liens sur le sujet:
http://artisan.karma-lab.net/node/1600
http://free-electrons.com/kerneldoc/...tems/tmpfs.txt
http://docs.postgresql.fr/annotated_...l_conf_74.html
http://books.google.fr/books?id=XJStF4p9RvkC&pg=PA32&dq=tmpfs#PPA32,M1
http://www.xenotime.net/linux/doc/swap-mini-howto.txt

Il faut également voir le hugetlbfs plus actuel
http://free-electrons.com/kerneldoc/...ugetlbpage.txt
http://www.kernel.org/doc/ols/2006/o...ages-83-90.pdf

[bustaf]

Je termine ce que j'ai commencé..
Précision work_mem est un alias de sort_mem)
J'ai effectué les tests pour la procédure entre le tmpfs et le swap
le tmpfs est bien dynamique dans son allocation mémoire ,
un débordement sur sa taille instruite au montage n'active
pas le swap système Postgresql affiche une erreur HINT au moment ou il ne trouve
plus assez d'espace pour temporiser.Cette erreur n'a aucune incidence sur l'intégrité de la base.
il suffit de monter un tmpfs avec une taille appropriée a la charge fonctionnement.
Si on démonte cette pseudo unité le répertoire se replace sur le système de fichiers classique.
Le bon coté que je vois va dans le sens de la sécurité pour un serveur sortant
on peut anticiper un débordement de l'espace de temporisation via un montage bien
quantifié.
(On arrête les roquettes SQL avant un éventuel crash ...)
(une sorte de quota simple et efficace... sur une plage mémoire dynamique)

Pour le système hugetlbfs il n'est pas présent sur toutes les distributions
Il faut installer le package libhugetlbfs ou compiler son source.
J'ai fait quelques essais la mémoire semble mieux gérée (pour le système).
Pour de la performance a moindre coût Il faudrait essayer de la flash SSD à 235 Mo/s
pour monter le pgsql_tmp en Ramdisk classique.
J'ai compilé en 32 bit Apache 2.2.10 et Postgresql 8.2.10 avec le compilateur
de chez Intel
(Intel ® C++ Compiler 11.0 Professional Edition for Linux)
il a des options de parallélisme pour les architectures multiprocesseurs et
d'autres liées a la spécificité des modèles.
Je vais voir si cela apporte un gain significatif.
@Pg++

[brunolf]

Bonjour,

Merci beaucoup pour toutes ces infos et ces tests... n'étant pas du tout expert sur ces sujets, je vais voir avec notre administrateur système si on peut faire des tests dans ce sens.

Encore merci.

[bustaf]

Bonjour
Réponse
Je vais voir si cela apporte un gain significatif.
(Intel ® C++ Compiler 11.0 Professional Edition for Linux)
Oui!!! c'est plus que significatif...
Correctement instruit dans ses options (optimisation et (typage processeur (s)) c'est impressionnant...
Son seul bug ,il est payant (Exploitation professionnelle)
Bon courage

[el muchacho]

Je pense que pour des traitements sur des millions de données, il faut de toute façon utiliser les curseurs et faire un peu de PL/pgSQL, sinon tout est chargé en RAM.

Sinon:
http://www.powerpostgresql.com/PerfList/
http://www.powerpostgresql.com/Downl...d_conf_80.html
Il semblerait que SharedBuffers est dimensionné un peu trop grand, 512 Mo serait plus raisonnable, non ?

Enfin, la 8.3 est censée être pas mal optimisée par rapport à la 8.2.