Tuning PostgreSQL : configuration mémoire pour Linux
3 juin 2017
Concernant les réglages de la mémoire à opérer pour PostgreSQL, la documentation officielle est claire comme du jus de chique. Et si, comme Boileau, je pense que « Ce que l’on conçoit bien s’énonce clairement« , il est évident que les rédacteurs de la documentation officielle de PostgreSQL me semblent passablement embrouillés ! De là à dire qu’ils ne comprennent pas grand chose à ce qu’ils écrivent… ;+)
Mémoire disponible
C’est à l’aide de la commande free sous Linux que vous pouvez déterminer la mémoire disponible de votre système. Ici, la mémoire disponible est égale à free+buff/cache, soit 961556 octets !
total used free shared buff/cache available Mem: 1016452 54896 504176 7240 457380 805208 Swap: 1048572 0 1048572
Shared Memory
Sous Linux, PostgreSQL n’utilise plus le mécanisme de la Shared Memory depuis la version 9.4 de la base de données Open Source. Il n’y a donc plus lieu de modifier le fichier /etc/sysctl.conf, comme nous le faisions auparavant !
effective_cache_size
effective_cache_size est une directive du fichier postgresql.conf, qui détermine la quantité de mémoire utilisée par PostgreSQL. Vous pouvez aller jusqu’à la fixer à la totalité de la mémoire disponible obtenue précédemment et calculée avec free. L’éditeur, quant à lui, préconise 75% de la RAM totale de la machine dans le cas d’un serveur dédié. Dans mon exemple, je le fixe à 958454 octets, soit la valeur inférieure la plus proche de la division de 961556 par 8 Ko.
shared_buffers
Ce paramètre shared_buffers doit être mis en fait à 1/3 du effective_cache_size. L’éditeur recommande de le régler à 25% de la RAM totale de la machine. Ce paramètre règle la quantité de mémoire pour la mise en cache des blocs du système de fichiers contenant tables et index. Dans mon exemple, ce paramètre est égal à 319488 octets.
work_mem
Concernant le work_mem, c’est la détermination de la taille mémoire pour les opérations de tris, de regroupement au niveau des requêtes effectués par les utilisateurs. Elle est égale au 1/3 du effective_cache_size, divisée par le nombre maxi de connections géré par la directive max_connections. Dans mon exemple, pour 20 connexions maximum, je l’ai arrondi à 16384 octets, soit le multiple de 8 ko le plus proche de la division de 319488/20.
maintenance_work_mem
Concernant ce paramètre, les valeurs produites par pgtune et Alexey Vasiliev proposent une quantité de 64 Mo par Go de RAM pour une base OLTP (base métier). D’autres proposent 50 Mo par Go. Cette mémoire est utilisée lors des opérations liées à la réindexation, au vacuum et au pg_dump. Dans mon exemple, j’ai mis ce paramètre à 64 Mo pour 1 Go.
Le site d’Alexey Vasiliev
Alexey Vasiliev, alias leopard, a développé un service en ligne vous permettant de calculer ces différents paramètres à ajouter à votre fichier postgresql.conf.
pgtune
C’est un outil disponible sur les distributions Linux. Pour l’installer sous CentOS, il faut ajouter les dépôts EPEL.
yum install epel-release yum install pgtune
Le souci est que pgtune n’est plus maintenu depuis octobre 2009 ! Concernant le calcul du paramètre work_mem, les données obtenues semblent extrêmement « agressives ». La commande s’emploie de la manière suivante :
pgtune -M 1073741824 -T OLTP -c 20 -i /var/lib/pgsql/9.6/data/postgresql.conf -o /var/lib/pgsql/9.6/data/postgresql.conf.new
En dehors du work_mem, les résultats de pgtune sont proches de ceux d’Alexey Vasiliev
maintenance_work_mem = 64MB effective_cache_size = 768MB work_mem = 48MB shared_buffers = 256MB max_connections = 20