Tunning avec PostgreSQL
Utilisation des resources de la machine
Présentation
La solution est semble assez simple : utiliser de façon optimale les différents caches présents sur la machine (cache L2/L3 du processeur puis cache disque et enfin cache applicatif) pour éviter le maximum les lectures physiques sur le disque qui sont bien sûr très couteuses.
Bien sûr, c’est un paramètre qui dépend fortement de l’utilisation de la base et de la configuration physique de la machine. Si il y a trop de cache, la machine risque de manquer de mémoire et de se mettre à swapper et s’il n’y a pas assez de cache il y aura également beaucoup d’I/O disque car trop peu de données pourront être conservées dans le cache. D’un autre coté, si votre application fait une utilisation intensive du SQL (via des requêtes complexes), il y aura un besoin certain de définir un sort buffer important pour permettre de faire le maximum de calculs en mémoire sans écrire de fichier temporaire sur le disque.
Paramètres PostgreSQL
Vous le voyez il ne s’agit pas d’un problème simple et il faudra bien surveiller le comportement de votre base (et de façon régulière) afin de changer les paramètres de votre base de données.
Voici quelques paramètres très important pour les performances de votre serveur PostgreSQL. Les valeurs fournies sont prévues pour un serveur de production dédiée PostgreSQL :
shared_mem : 25% de la RAM
sort_mem : 10% du shared_mem
wal_buffers : 10% du shared_mem chiffres tirés d’une présentation de Thierry Missimilly (groupe Bull)
Optimisation des requetes
Connaitre le plan d’exécution d’une requête
Sous PostgreSQL la syntaxe pour connaître le façon dont ce dernier traite vos requêtes il faut utiliser la syntaxe suivante : EXPLAIN select * from nodes; QUERY PLAN 1. ——————————————————– Seq Scan on nodes (cost=0.00..43.15 rows=15 width=336) (1 row)
L’option ANALYSE permet d’exécuté réellement la requête, en plus de l’affichage du plan d’execution. EXPLAIN ANALYSE select * from nodes; QUERY PLAN 1. ————————————————————————————————— Seq Scan on nodes (cost=0.00..43.15 rows=15 width=336) (actual time=0.036..0.408 rows=15 loops=1) Total runtime: 0.528 ms (2 rows)
Forcer certains paramètres de l’optimiseur de requête
Les paramètres suivants permettent de désactiver certaines fonctions utilisées par l’optimiseur de requêtes de PostgreSQL :
SET enable_sort TO off; : Désactive l’utilisation des étapes de tri explicite par le planificateur
SET enable_hashjoin TO off; : Désactive l’utilisation des jointures hachées par le planificateur
SET enable_nestloop TO off; : Désactive l’utilisation des jointures de boucles imbriquées par le planificateur Attention, il est intéressant de modifier ces paramètres lors de vos tests pour mieux comprendre comment l’optimiseur traite vos requêtes mais il n’est pas conseillé d’utiliser cela en production.