Tuning PostgreSQL : configuration mémoire pour Linux

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.

Configuration mémoire pour PostgreSQL sur Linux

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

Documentation

PostgreSQL  / Formateur Linux Formateur PostgreSQL Linux PostgreSQL 

Commentaires

Merci Denis pour ce résumé simple et limpide.

Laisser un commentaire

(requis)

(requis)