La configuration par défaut de PostgreSQL est paramétrée plutôt pour des serveurs de petite taille.
Pour une base à forte volumétrie de données et de requêtes, s’exécutant sur un serveur puissant, il est nécessaire d’effectuer quelques ajustements.

  1. Recommandation
  2. Explications

Recommandation d’optimisation prosgresql avec pgtune

Il est recommandé de fortement augmenter les paramètres shared_buffers et work_mem qui indiquent la quantité de mémoire maximale à utiliser pour un certain nombre d’opérations.
L’utilitaire pg_tune peut être utilisé pour obtenir des indications sur les valeurs à utiliser, suivant la configuration matérielle :
https://pgtune.leopard.in.ua/#/

Voici un exemple de configuration que conseille pgtune :

# DB Version: 9.6
 # OS Type: linux
 # DB Type: web
 # Total Memory (RAM): 2 GB
 # CPUs num: 4
 # Data Storage: ssd
 
 max_connections = 200
 shared_buffers = 512MB
 effective_cache_size = 1536MB
 maintenance_work_mem = 128MB
 checkpoint_completion_target = 0.7
 wal_buffers = 16MB
 default_statistics_target = 100
 random_page_cost = 1.1
 effective_io_concurrency = 200
 work_mem = 1310kB
 min_wal_size = 1GB
 max_wal_size = 2GB
 max_worker_processes = 4
 max_parallel_workers_per_gather = 2

Description numéro une.

Description numéro une.

Texte

Explications valeurs configuration PostgreSQL

Voici l’explication des valeurs:

  • work_mem Ce paramètre est utilisé pour chaque connexion pour les opérations de tris et de hachages
  • max_connexions Nombre maximum de connexion
  • maintenance_work_mem Ce paramètre est utilisé pour les opérations de maintenance (CREATE INDEX, VACUUM, etc)
  • effective_cache_size 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
  • shared_buffers Taille des buffers de mémoire partagée entre les threads PostgreSQL. On peut donner 1/4 de la RAM pour une machine dédiée à PostgreSql
  • wal_buffers Buffer alloué au process wal_writer.On peut donner 8mo x la RAM en go pour une machine dédiée à PostgreSql
  • checkpoint_segments Nombre de segments écrits dans le fichier de LOG avant checkpoint
  • checkpoint_completion_target 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 Passer à 1.1 pour les disques SSD, 4.0 (défaut) pour les disques lents. Il favorise l’utilisation des index.
  • default_statistics_target PostgreSQL prend ses décisions sur la façon dont les requêtes s’exécutent en fonction des statistiques collectées sur chaque table de votre base de données.
    Ces informations sont collectées en analysant les tables, soit avec l’instruction ANALYZE, soit via l’autovide.
    Dans les deux cas, la quantité d’informations collectées au cours de l’étape Analyze est définie par default_statistics_target.
    L’augmentation de cette valeur rend l’analyse plus longue, et depuis l’analyse d’autovide se produit régulièrement, cela se transforme en surcharge de fond accrue pour la maintenance des bases de données. Mais s’il n’y a pas assez de statistiques sur une table, vous pouvez obtenir de mauvais plans pour les requêtes contre elle.
  • max_worker_processes On peut donner le nombre de coeur CPU
  • max_parallel_workers_per_gather On peut donner 1/2 du nombre de coeur CPU

Afin d’appliquer les changements sur la base de données, il faudra changer directement les valeurs calculées précédemment sur le fichier de configuration :

vim etc/postgresql/votre-version/main/postgresql.conf

Une fois enregistré, il faudra simplement redémarrer le service postgresql :

systemctl restart postgresql.service