Configuration du système et de l’instance

Module J1

Dalibo SCOP

24.04

17 avril 2024

Sur ce document

Formation Module J1
Titre Configuration du système et de l’instance
Révision 24.04
PDF https://dali.bo/j1_pdf
EPUB https://dali.bo/j1_epub
HTML https://dali.bo/j1_html
Slides https://dali.bo/j1_slides
TP https://dali.bo/j1_tp
TP (solutions) https://dali.bo/j1_solutions

Licence Creative Commons CC-BY-NC-SA

Cette formation est sous licence CC-BY-NC-SA. Vous êtes libre de la redistribuer et/ou modifier aux conditions suivantes :

  • Paternité
  • Pas d’utilisation commerciale
  • Partage des conditions initiales à l’identique

Marques déposées

PostgreSQL® Postgres® et le logo Slonik sont des marques déposées par PostgreSQL Community Association of Canada.

Versions de PostgreSQL couvertes

Ce document ne couvre que les versions supportées de PostgreSQL au moment de sa rédaction, soit les versions 12 à 16.

Configuration du système et de l’instance

PostgreSQL

Introduction

  • L’optimisation doit porter sur les différents composants
    • le serveur qui héberge le SGBDR : le matériel, la distribution, le noyau, les systèmes de fichiers
    • le moteur de la base de données : postgresql.conf
    • la base de données : l’organisation des fichiers de PostgreSQL
    • l’application en elle-même : le schéma et les requêtes
  • Quelques considérations générales sur l’optimisation
  • Choix et configuration du matériel
  • Choix et configuration du système d’exploitation
  • Configuration du serveur de bases de données
  • Outils

Considérations générales - 1

  • Deux points déterminants :
    • vision globale du système d’information
    • compréhension de l’utilisation de la base

Considérations générales - 2

  • L’optimisation n’est pas un processus unique
    • il s’agit au contraire d’un processus itératif
  • La base doit être surveillée régulièrement !
    • nécessité d’installer des outils de supervision

Matériel

  • Performances très liées aux possibilités du matériel et de l’OS
  • 4 composants essentiels
    • les processeurs
    • la mémoire
    • les disques
    • le système disque (RAID, SAN)

CPU

  • Trois critères importants
    • nombre de cœurs
    • fréquence
    • cache
  • Privilégier
    • le nombre de cœurs si le nombre de sessions parallèles est important
    • ou la fréquence si les requêtes sont complexes
  • 64 bits

RAM

  • Essentielle pour un serveur de bases de données
  • Plus il y en a, mieux c’est
    • moins d’accès disque
  • Pour le système comme pour PostgreSQL

Disques

Technologie Temps d’accès Débit en lecture
RAM ~ 1 ns ~ 5 Go/s
NVMe ~ 100 µs ~ 3 Go/s
SSD (SATA) ~ 100 µs ~ 300 Mo/s
HDD SAS 15ktpm ~ 1 ms ~ 100 Mo/s
HDD SATA ~ 5 ms ~ 100 Mo/s

RAID

  • Pour un SGBD :
    • RAID 1 : système, journaux de transactions
    • RAID 10 : fichiers de données
  • RAID 5 déconseillé pour les bases de données (écritures)
  • RAID soft déconseillé !
  • Qualité des cartes !
  • Cache :
    • en lecture : toujours
    • en écriture : si batterie présente & supervisée

SAN

  • Pouvoir sélectionner les disques dans un groupe RAID
  • Attention au cache
    • toujours activer le cache en lecture
    • activer le cache en écriture que si batterie présente
  • Attention à la latence réseau !
  • Attention au système de fichiers
    • NFS : risques de corruptions et problèmes de performance

Virtualisation

  • Masque les ressources physiques au système
    • plus difficile d’optimiser les performances
  • Propose généralement des fonctionnalités d’overcommit
    • grandes difficultés à trouver la cause du problème du point de vue de la VM
    • dédier un minimum de ressources aux VM PostgreSQL
  • En pause tant que l’hyperviseur ne dispose pas de l’ensemble des vCPU alloués à la machine virtuelle (steal time)
  • Mutualise les disques = problèmes de performances
    • préférer attachement direct au SAN
    • disques de PostgreSQL en Thick Provisionning

Virtualisation : les bonnes pratiques

  • Éviter le time drift : même source NTP sur les VM et l’ESXi
  • Utiliser les adaptateurs réseau paravirtualisés de type VMXNET3
  • Utiliser l’adaptateur paravirtualisé PVSCSI pour les disques dédiés aux partitions PostgreSQL
  • Si architecture matérielle NUMA :
    • dimensionner la mémoire de chaque VM pour qu’elle ne dépasse pas le volume de mémoire physique au sein d’un groupe NUMA

Système d’exploitation

  • Quel système choisir ?
  • Quelle configuration réaliser ?

Choix du système d’exploitation

  • PostgreSQL fonctionne sur différents systèmes
    • principalement développé et testé sous Linux
    • *BSD, macOS, Windows, Solaris, etc.
  • Windows intéressant pour les postes des développeurs
    • mais moins performant que Linux
    • moins d’outillage

Choix du noyau

  • Choisir la version la plus récente du noyau car
    • plus stable
    • plus compatible avec le matériel
    • plus de fonctionnalités
    • plus de performances
  • Utiliser la version de la distribution Linux
    • ne pas le compiler soi-même

Configuration du noyau

  • À configurer :
    • cache disque système
    • swap
    • overcommit
    • huge pages
    • affinité entre cœurs et mémoire
    • scheduler

Contrôle du cache disque système

  • Lissage de l’écriture des dirty pages
  • Paramètres
    • vm.dirty_ratio et vm.dirty_background_ratio
    • ou : vm.dirty_bytes et vm.dirty_background_bytes
  • Encore utiles malgré les paramètres PostgreSQL *_flush_after

Configuration du swap

La mémoire sert de cache au disque, pas l’inverse !

  • Swap : pas plus de 2 Go
  • et à décourager :
vm.swappiness = 10

Configuration de la sur-réservation mémoire

Le noyau peut autoriser trop de réservation mémoire.

  • Si saturation :
    • kill -9 du processus par l’OOM killer
    • et donc redémarrage
    • purge du cache
  • Désactiver sur serveur dédié :
vm.overcommit_memory = 2
vm.overcommit_ratio  = ?  # à calculer, souvent 70-80

Huge pages

Pages mémoire de 2 Mo au lieu de 4 ko :

  • Les processus consomment moins de mémoire
  • shared buffers non swappés
  • PostgreSQL :
huge_pages = try  # ou: on / off
  • Noyau :
vm.nr_overcommit_hugepages = ?  # selon shared_buffers +10%
vm.overcommit_ratio        = ?  # à baisser
  • Transparent Huge Pages : à désactiver

Configuration de l’affinité processeur / mémoire

  • Pour architecture NUMA (multi-sockets)
  • Chaque socket travaille plus efficacement avec une zone mémoire allouée
  • Peut pénaliser le cache disque système
    • vm.zone_reclaim_mode : passer à 0

Configuration de l’ordonnanceur

  • Réduire la propension du kernel à migrer les processus
    • kernel.sched_migration_cost_ns = 5000000 (×10) (si kernel < 5.13)
  • Désactiver le regroupement par session TTY
    • kernel.sched_autogroup_enabled = 0

Comment les configurer

  • Outil
    • sysctl
  • Fichier de configuration
    • /etc/sysctl.conf
    • /etc/sysctl.d/*conf

Choix du système de fichiers

  • Windows :
    • NTFS
  • Linux :
    • ext4, XFS
    • LVM pour la flexibilité
  • Solaris :
    • ZFS
  • Utiliser celui préconisé par votre système d’exploitation/distribution

Configuration du système de fichiers

  • Quelques options à connaître :
    • noatime, nodiratime
    • dir_index
    • data=writeback
    • nobarrier
  • Permet de gagner un peu en performance

Configuration de l’antivirus

Pas d’antivirus

Serveur de bases de données

  • Version
  • Configuration
  • Emplacement des fichiers

Version

  • Chaque nouvelle version majeure a des améliorations de performance
    • mettre à jour est un bon moyen pour gagner en performances
  • Ne pas compiler

Configuration - mémoire partagée

  • shared_buffers = ...
    • 25 % de la RAM en première intention
    • max 40 %
    • complémentaire du cache OS
  • wal_buffers

Configuration - mémoire des processus

  • work_mem

    • par processus, voire nœud
    • valeur très dépendante de la charge et des requêtes
    • fichiers temporaires vs saturation RAM
  • × hash_mem_multiplier

  • maintenance_work_mem

Configuration - planificateur

  • effective_cache_size
  • random_page_cost

Configuration - parallélisation : principe

  • Par défaut : 1 requête = 1 processus
  • Grosses tables : parallel workers en complément
  • Nombreux nœuds parallélisables

Configuration - parallélisation : paramètres

  • Nombre de parallel workers :
    • max_parallel_workers_per_gather (défaut : 2)
    • max_parallel_workers (8)
    • max_worker_processes (8)
  • Taille minimale des tables/index :
    • min_parallel_table_scan_size (8 Mo)
    • min_parallel_index_scan_size (512 ko)
  • Indexation et VACUUM :
    • max_parallel_maintenance_workers (2)

Configuration - WAL

  • fsync (on !)
  • min_wal_size (80 Mo) / max_wal_size (1 Go)
  • checkpoint_timeout (5 min, ou plus)
  • checkpoint_completion_target (passer à 0.9)

Configuration - statistiques

  • track_activities
  • track_counts
  • track_functions, track_io_timing et track_wal_io_timing

Configuration - autovacuum

  • autovacuum

Tablespaces : principe

  • Espace de stockage physique d’objets
    • et non logique !
  • Simple répertoire (hors de PGDATA)
    • lien symbolique depuis pg_tblspc
  • Pour :
    • répartir I/O et volumétrie
    • données froides/chaudes
    • tri sur disque séparé

Tablespaces : mise en place

CREATE TABLESPACE chaud LOCATION '/SSD/tbl/chaud';

CREATE DATABASE nom TABLESPACE 'chaud';

ALTER DATABASE nom SET default_tablespace TO 'chaud';

GRANT CREATE ON TABLESPACE chaud TO un_utilisateur ;

CREATE TABLE une_table (…) TABLESPACE chaud ;

ALTER TABLE une_table SET TABLESPACE chaud ;  -- verrou !

ALTER INDEX une_table_i_idx SET TABLESPACE chaud ; -- pas automatique

Tablespaces : configuration

  • default_tablespace
  • temp_tablespaces
  • Droits à ouvrir :
GRANT CREATE ON TABLESPACE  ssd_tri  TO  dupont ;
  • Performances :
    • seq_page_cost, random_page_cost
    • effective_io_concurrency, maintenance_io_concurrency
ALTER TABLESPACE chaud SET ( random_page_cost = 1 );
ALTER TABLESPACE chaud SET ( effective_io_concurrency   = 500,
                             maintenance_io_concurrency = 500 ) ;

Emplacement des journaux de transactions

  • Placer les journaux sur un autre disque
  • Option --wal-dir de l’outil initdb
  • Lien symbolique

Emplacement des fichiers statistiques

  • Avant la v15
    • placer les fichiers statistiques sur un autre disque
    • option stats_temp_directory
  • À partir de la v15
    • cela peut être intéressant pour pg_stat_statements

Outils

  • pgtune
  • pgbench
  • postgresqltuner.pl

Outil pgtune

  • Outil écrit en Python, par Greg Smith
    • repris en Ruby par Alexey Vasiliev
  • Propose quelques meilleures valeurs pour certains paramètres
  • Quelques options pour indiquer des informations système
  • Version web : https://pgtune.leopard.in.ua/
  • Il existe également pgconfig

Outil pgbench

  • Outil pour réaliser rapidement des tests de performance
  • Fourni dans les modules de contrib de PostgreSQL
  • Travaille sur une base de test créée par l’outil…
    • … ou sur une vraie base de données

Types de tests avec pgbench

  • On peut faire varier différents paramètres, tel que :
    • le nombre de clients
    • le nombre de transactions par client
    • faire un test de performance en SELECT only, UPDATE only ou TPC-B
    • faire un test de performance dans son contexte applicatif
    • exécuter le plus de requêtes possible sur une période de temps donné
    • etc.

Environnement de test avec pgbench

  • pgbench est capable de créer son propre environnement de test
  • Environnement adapté pour des tests de type TPC-B
  • Permet de rapidement tester une configuration PostgreSQL
    • en termes de performance
    • en termes de charge
  • Ou pour expérimenter/tester

Environnement réel avec pgbench

  • pgbench est capable de travailler avec une base existante
  • Lecture des requêtes depuis un ou plusieurs fichiers
  • Utilisation possible de variables et commandes

Outil postgresqltuner.pl

Conclusion

  • PostgreSQL propose de nombreuses voies d’optimisation.

  • Cela passe en priorité par un bon choix des composants matériels et par une configuration pointilleuse.

  • Mais ceci ne peut se faire qu’en connaissance de l’ensemble du système, et notamment des applications utilisant les bases de l’instance.

Questions

N’hésitez pas, c’est le moment !

Quiz

Installation de PostgreSQL depuis les paquets communautaires

Introduction à pgbench

Travaux pratiques

Utilisation de pgbench

Influence de fsync et synchronous_commit

Travaux pratiques (solutions)