BI experience

Optimisation du serveur de base de données PostgreSQL

| 0 Commentaires

Pour de bonnes performances au niveau de la restitution, il faut à la fois optimiser les rapports, cube, univers.. créés grâce au logiciel de restitution et la base de données où est stocké le datawarehouse. Celle-ci doit passer par une phase d’optimisation, de configuration.

Je vous propose dans ce post de détailler la configuration de postgreSQL qu’un DBA est venu faire chez nous.

Tout d’abord notre environnement de production où la BDD a un serveur dédié était :

  • VM Debian
  • 2 Go RAM
  • 2 processeurs Intel(R) Xeon(R) 1,6 Ghz

kernel.shmmax : désigne le maximum de mémoire partagée entre les processus.

Pour connaître sa valeur actuelle voici la commande : cat /proc/sys/kernel/shmmax

Valeur par défaut : kernel.shmmax=33554432  (32 Mo de segment )

Pour changer cette valeur sysctl -w kernel.shmmax=134217728 (soit 128 Mo)  (cette valeur a été choisie par rapport à la configuration citée plus haut. Elle pourra donc être modulée)

Ensuite toute la configuration se fera dans le fichier : postgresql/x.x/main/postgresql.conf

work_mem=10MB : Ce paramètre est utilisé pour chaque connexion pour les opérations de tris et de hachages.

max_connexions=100 : Valeur par défaut, à conserver.

maintenance_work_mem=128MB : Ce paramètre est utilisé pour les opérations de maintenance (CREATE INDEX, VACUUM, etc). (On pourra jouer sur ce paramètre en fonction de la configuration du serveur)

effective_cache_size=1GB : Permet de donner à l’optimiseur une idée de la mémoire disponible pour le cache des tables et des index. En général, on peut le positionner jusqu’à 2/3 de la RAM pour une machine dédiée à PostgreSql, mais la moitié est suffisante. (On pourra jouer sur ce paramètre en fonction de la configuration du serveur)

shared_buffers = 64MB : Taille des buffers de mémoire partagée entre les threads PostgreSQL. (On pourra aussi jouer sur ce paramètre en fonction de la configuration du serveur)

wal_buffers = 4 MB Buffer alloué au process wal_writer. Largement suffisant, sachant qu’il y a très peu d’écriture en utilisation normale vu que nous sommes dans un contexte décisionnel avec un datawarehouse qui est utilisé pour faire de la lecture intensive.

checkpoint_segments = 10 : Nombre de segments écrits dans le fichier de LOG avant checkpoint, ie écriture dans les fichiers de data.

checkpoint_completion_target = 0.8 : Pourcentage de temps utilisé par le process writer pour effectuer le checkpoint par rapport au temps max d’écriture sur les 10 segments de LOG. Ce paramètre permet de diluer l’application des modifications sur les fichiers de data dans le temps.

random_page_cost = 2.0 : Passer à 2.0 pour les disques rapides récents, 4.0 (défaut) pour les disques lents. Il favorise l’utilisation des index.

Redémarrer postgresql pour appliquer ces changements :
/etc/init.d/postgresql-x.x restart

Le changement de ces quelques paramètres a vraiment amélioré significativement nos performances. J’espère que ce post pourra  en aider d’autres pour que leurs requêtes SQL soient encore plus réactives. Si vous connaissez d’autres astuces, n’hésitez pas postez!

Laisser un commentaire

Champs Requis *.

*