PostgreSQL Performances

18 décembre 2020

Dalibo SCOP

Creative Commons BY-NC-SA

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

Pour qu’une optimisation soit réussie, il faut absolument tenir compte de tous les éléments ayant une responsabilité dans les performances. Cela commence avec le matériel. Il ne sert à rien d’améliorer la configuration du serveur PostgreSQL ou les requêtes si, physiquement, le serveur ne peut tenir la charge, que cela soit la cause des processeurs, de la mémoire, du disque ou du réseau. Le matériel est donc un point important à vérifier dans chaque tentative d’optimisation. De même, le système d’exploitation est pour beaucoup dans les performances de PostgreSQL : son choix et sa configuration ne doivent pas être laissés au hasard. La configuration du moteur a aussi son importance et cette partie permettra de faire la liste des paramètres importants dans le seul cadre des performances. Même l’organisation des fichiers dans les partitions des systèmes disques a un intérêt.

L’optimisation (aussi appelée tuning) doit donc être réalisée sur tous ces éléments à la fois pour être optimale !


  • 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

Il est très difficile d’optimiser un serveur de bases de données sans savoir comment ce dernier va être utilisé. Par exemple, le nombre de requêtes à exécuter simultanément et leur complexité est un excellent indicateur pour mieux apprécier le nombre de cœurs à placer sur un serveur. Il est donc important de connaître la façon dont les applications travaillent avec les bases. Cela permet de mieux comprendre si le matériel est adéquat, s’il faut changer telle ou telle configuration, etc. Cela permet aussi de mieux configurer son système de supervision.


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

Après avoir installé le serveur et l’avoir optimisé du mieux possible, la configuration optimale réalisée à ce moment ne sera bonne que pendant un certain temps. Si le service gagne en popularité, le nombre d’utilisateurs peut augmenter. La base va de toute façon grossir. Autrement dit, les conditions initiales vont changer. Un serveur optimisé pour 10 utilisateurs en concurrence ne le sera plus pour 50 utilisateurs en concurrence. La configuration d’une base de 10 Go n’est pas la même que celle d’une base de 1 To.

Cette évolution doit donc être surveillée à travers un système de supervision et métrologie approprié et compris. Lorsqu’un utilisateur se plaint d’une impression de lenteur sur le système, ces informations collectées rendent souvent la tâche d’inspection plus rapide. Ainsi, l’identification du ou des paramètres à modifier, ou plus généralement des actions à réaliser pour corriger le problème, est plus aisée et repose sur une vision fiable et réelle de l’activité de l’instance.

Le plus important est donc de bien comprendre qu’un SGBD ne s’optimise pas qu’une seule fois, mais que ce travail d’optimisation sera à faire plusieurs fois au fur et à mesure de la vie du serveur.

À une échelle beaucoup plus petite, un travail d’optimisation sur une requête peut forcer à changer la configuration d’un paramètre. Cette modification peut faire gagner énormément sur cette requête… et perdre encore plus sur les autres. Là aussi, tout travail d’optimisation doit être fait prudemment et ses effets surveillés sur une certaine période pour s’assurer que cette amélioration ne s’accompagne pas de quelques gros inconvénients.


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)

PostgreSQL est un système qui se base fortement sur le matériel et le système d’exploitation. Il est donc important que ces deux composants soient bien choisis et bien configurés pour que PostgreSQL fonctionne de façon optimale pour les performances.

Au niveau du matériel, les composants essentiels sont :

  • les processeurs (CPU) ;
  • la mémoire (RAM) ;
  • les disques ;
  • le système disque (carte RAID, baie SAN, etc.).

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

PostgreSQL est un système multi-processus. Chaque connexion d’un client est gérée par un processus, responsable de l’exécution des requêtes et du renvoi des données au client. Ce processus n’est pas multi-threadé. Par conséquent, chaque requête exécutée est traitée par un cœur de processeur. Plus vous voulez pouvoir exécuter de requêtes en parallèle, plus vous devez avoir de processeurs (ou plus exactement de cœurs). On considère habituellement qu’un cœur peut traiter de 4 à 20 requêtes simultanément. Cela dépend notamment beaucoup des requêtes, de leur complexité, de la quantité de données manipulée et retournée, etc. Il est donc essentiel de connaître le nombre de requêtes traitées simultanément pour le nombre d’utilisateurs connectés. S’il s’agit d’un SGBD pour une application web, il y a de fortes chances que le nombre de requêtes en parallèle soit assez élevé. Dans ce contexte, il faut prévoir un grand nombre de cœurs processeurs. En revanche, sur un entrepôt de données, nous trouvons habituellement peu d’utilisateurs avec des requêtes complexes et gourmandes en ressources. Dans ce cas, beaucoup de processeurs n’apporteront rien. Mieux vaut peu de cœur, mais que ces derniers soient plus puissants afin de répondre plus efficacement aux besoins importants de calculs complexe.

Ainsi, la fréquence (et donc la puissance) des processeurs est un point important à considérer. Il peut faire la différence si les requêtes à exécuter sont complexes : temps de planification réduit, calculs plus rapides donc plus de requêtes exécutées sur une période de temps donnée. Généralement, un système utilisé pour des calculs (financiers, scientifiques, géographiques) a intérêt à avoir des processeurs à fréquence élevée.

À partir de la version 9.6, un processus exécutant une requête peut demander l’aide d’autre processus (appelés workers) pour l’aider à traiter cette requête. Les différents processus utiliseront des CPU différents, permettant ainsi une exécution parallélisée d’une requête. Ceci n’est possible qu’à partir de la version 9.6 et uniquement pour des requêtes en lecture seule. De plus, seules certaines actions sont parallélisables : parcours séquentiel, jointure, calcul d’agrégats. Ceci a un impact important pour les requêtes consommatrices en temps CPU. De ce fait, le facteur principal de choix reste toujours le nombre de CPU disponibles.

Le cache processeur est une mémoire généralement petite, mais excessivement rapide et située au plus près du processeur. Il en existe plusieurs niveaux. Tous les processeurs ont un cache de niveau L2, certains ont même un cache de niveau L3. Plus cette mémoire est importante, plus le processeur peut conserver de données utiles et éviter des allers-retours en mémoire RAM coûteux en temps. Le gain en performance pouvant être important, le mieux est de privilégier les processeurs avec beaucoup de cache.

Le choix processeur se fait donc suivant le type d’utilisation du serveur :

  • une majorité de petites requêtes en très grande quantité : privilégier le nombre de cœurs ;
  • une majorité de grosses requêtes en très petite quantité : privilégier la puissance processeur.

Dans tous les cas, choisissez la version des processeurs avec le plus de mémoire cache embarquée.

La question 32 bits/64 bits ne se pose plus : il n’existe pratiquement plus que du 64 bits. De plus, les processeurs 64 bits sont naturellement plus performants pour traiter des données sur 8 octets (bigint, double precision, numeric, timestamps, etc.) qui tiennent dans un registre mémoire.

Il existe une autre question qui ne se pose plus tellement : vaut-il mieux Intel ou AMD ? cela a très peu d’importance. AMD a une grande maîtrise des systèmes multi-cœurs, et Intel est souvent puissant et optimisé sur les échanges avec la mémoire. Cela pourrait être des raisons de les sélectionner, mais la différence devient de plus en plus négligeable de nos jours.


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

Toute opération sur les données doit se faire en mémoire. Il est donc nécessaire qu’une bonne partie de la base tienne en mémoire, ou tout du moins la partie active. La partie passive est rarement présente en mémoire, car généralement composée de données historiques qui sont peu ou pas lues et jamais modifiées.

Un cache disque permet de limiter les accès en lecture et écriture vers les disques. L’optimisation des accès aux disques est ainsi intimement liée à la quantité de mémoire physique disponible. Par conséquent, plus il y a de mémoire, mieux c’est. Cela permet de donner un cache disque plus important à PostgreSQL, tout en laissant de la place en mémoire aux sessions pour traiter les données (faire des calculs de hachage par exemple).

Il est à noter que, même avec l’apparition des disques SSD, l’accès à une donnée en mémoire est bien plus rapide qu’une donnée sur disque. Nous aborderons ce point dans le chapitre consacré aux disques.


Disques

  • Trois grandes technologies : SATA, SAS et SSD
Technologie Temps d’accès Débit en lecture
RAM ~ 1 ns ~ 5 Go/s
Fusion IO ~ 0.015 ms ~ 2 Go/s
SSD ~ 0.1 ms ~ 200 Mo/s
SCSI 15ktpm ~ 1 ms ~ 100 Mo/s
SATA ~ 5 ms ~ 100 Mo/s

Il existe actuellement trois types de modèles de disques :

  • SATA, dont la principale qualité est d’être peu cher ;
  • SAS, rapide, fiable, mais cher ;
  • SSD, très rapide en temps d’accès, très cher.

Les temps d’accès sont très importants pour un SGBD. Effectivement, ces derniers conditionnent les performances des accès aléatoires, utilisés lors des parcours d’index. Le débit en lecture, lui, influe sur la rapidité de parcours des tables de façon séquentielle (bloc par bloc, de proche en proche).

Il est immédiatement visible que la mémoire est toujours imbattable, y compris face aux disques SSD avec un facteur 100 000 en performance de temps d’accès entre les deux ! À l’autre bout de l’échelle se trouvent les disques SATA. Leur faible performance en temps d’accès ne doit pas pour autant les disqualifier. Leur prix est là aussi imbattable et il est souvent préférable de prendre un grand nombre de disques pour avoir de bonnes performances. Cependant, la fiabilité des disques SATA impose de les considérer comme des consommables et de toujours avoir des disques de secours prêts à remplacer une défaillance.

Il est souvent préconisé de se tourner vers des disques SAS (SCSI). Leurs temps d’accès et leur fiabilité ont fait de cette technologie un choix de prédilection dans le domaine des SGBD. Mais si le budget ne le permet pas, des disques SATA en plus grand nombre permettent d’en gommer les défauts.

Dans tous les cas, le nombre de disques est un critère important, car il permet de créer des groupes RAID efficaces ou de placer les fichiers de PostgreSQL suivant leur utilisation. Par exemple les journaux de transactions sur un système disque, les tables sur un autre et les index sur un dernier.

Le gros intérêt du disque SSD est d’avoir un temps d’accès très rapide. Il se démarque des disques magnétiques (comme SAS ou SATA) par une durée d’accès à une page aléatoire aussi rapide que celle à une donnée contiguë (ou séquentielle). C’est parfait pour accéder à des index. Ils avaient une durée de vie plutôt limitée par rapport aux disques magnétiques. De nos jours, ce n’est plus tellement le cas grâce à des algorithmes d’écriture complexes permettant d’atteindre des durées de vie équivalentes, voire plus importantes, que celles des disques magnétiques. Néanmoins, ces mêmes algorithmes mettent en péril la durabilité des données en cas d’interruption brutale. Il est ainsi déconseillé d’utiliser cette technologie sans avoir au préalable effectué des tests de fiabilité intensifs. Les disques SSD les plus onéreux (souvent au détriment de leurs performances pures) réussiront ces tests. Les autres peuvent servir à stocker des données volatiles, comme les fichiers temporaires pour le tri et le hachage, ainsi que les tables et index temporaires. Il est possible de configurer le noyau pour optimiser l’utilisation des SSD :

# echo noop > /sys/block/<device>/queue/scheduler
# echo 0 > /sys/block/<device>/queue/rotational

Plus d’informations sont disponibles dans cet article sur les disques SSD et Linux.

Il existe aussi des supports de stockage moins courant, très onéreux, mais extrêmement rapides : ce sont les cartes Fusion-IO. Il s’agit de stockage en mémoire Flash sur support PCIe pouvant aller au delà de 6 To en volume de stockage, avec des temps d’accès et des débits bien supérieurs aux SSD. Leur utilisation reste cependant très limitée en raison du coût de cette technologie.


RAID

  • Différents niveaux de RAID
  • Les plus intéressants 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)
  • Attention à la qualité des cartes
  • Attention au cache
    • toujours activer le cache en lecture
    • activer le cache en écriture que si batterie présente (et supervisée)

Il existe différents niveaux de RAID. Le plus connu est le RAID 5 (il autorise de perdre un des disques sans interrompre le service, au prix d’une perte de capacité plus faible que le RAID 1). Cependant il est déconseillé pour les bases de données (PostgreSQL comme les autres) en raison de mauvaises performances en écriture, en temps normal et encore plus lors de la reconstruction d’un disque.

Il est nettement préférable de se baser sur du RAID 10, soit deux grappes de disques en RAID 1 (en miroir) rassemblés dans un RAID 0 : c’est tout aussi intéressant en termes de fiabilité, mais avec de bien meilleures performances en lecture et écriture. En contrepartie, à volumétrie égale, il nécessite plus de disques et est donc beaucoup plus cher que le RAID 5. Ainsi, il peut être préférable de choisir des disques SATA et mettre en œuvre un RAID 10 avec un budget moyen.

Il est à noter que le système et les journaux de transactions, n’ont pas besoin de RAID 10. Leur utilisation peut se satisfaire d’un simple RAID 1.

Le RAID 0 (simple addition de disques pour maximiser l’espace, sans aucune redondance) est évidemment à proscrire.

Les cartes RAID ne sont pas toutes aussi performantes et fiables. Les cartes intégrées aux cartes mères sont généralement de très mauvaise qualité. Il ne faut jamais transiger sur la qualité de la carte RAID.

La majorité des cartes RAID offre maintenant un système de cache de données en mémoire. Ce cache peut être simplement en lecture ou en lecture/écriture. Dans ce deuxième cas, ce cache étant volatile, la carte RAID doit posséder une batterie pour que les données en cache ne disparaissent pas en cas de coupure de courant. Ceci est obligatoire pour des raisons de fiabilité du service. Les meilleures cartes RAID permettent de superviser l’état de la batterie et désactivent le cache en écriture par mesure de sécurité si la batterie est vide ou morte.


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

Les SAN sont très appréciés en entreprise. Ils permettent de fournir le stockage pour plusieurs machines de manière fiable. Bien configurés, ils permettent d’atteindre de bonnes performances. Il est cependant important de comprendre les problèmes qu’ils peuvent poser.

Certains SAN ne permettent pas de sélectionner les disques placés dans un volume logique. Ils peuvent placer différentes partitions du même disque dans plusieurs volumes logiques. C’est un problème quand il devient impossible de dire si deux volumes logiques utilisent les mêmes disques. En effet, PostgreSQL permet de répartir des objets (tables ou index) sur plusieurs tablespaces différents. Cela n’a un intérêt en termes de performances que s’il s’agit de disques physiquement différents.

De même, certaines grappes de disques (eg. RAID GROUP) accueillent trop de volumes logiques pour de multiples serveurs (virtualisés ou non). Les performances des différents volumes dépendent alors directement de l’activité des autres serveurs connectés aux mêmes grappes.

Les SAN utilisent des systèmes de cache. L’avertissement concernant les cartes RAID et leur batterie vaut aussi pour les SAN qui proposent un cache en écriture.

Les SAN ne sont pas attachés directement au serveur. L’accès aux données accusera donc en plus une pénalité due à la latence réseau. Il est ainsi important de bien considérer son architecture réseau. Les équipements choisis doivent avoir une latence la plus faible possible, un débit important et les chemins entre les serveurs et la baie SAN multipliés.

Ces différentes considérations et problématiques (et beaucoup d’autres) font de la gestion de baies SAN un métier à part entière. Il faut y consacrer du temps de mise en œuvre, de configuration et de supervision important. En contrepartie de cette complexité et de leurs coûts, les SAN apportent beaucoup en fonctionnalités (snapshot, réplication, virtualisation…), en performances et en souplesse.

De plus, les disques étant distants et la technologie onéreuse, la tentation est grande d’utiliser un NAS, avec par exemple un accès NFS aux partitions. Il faut l’éviter, pour des raisons de performance et de fiabilité. Utilisez plutôt iSCSI, toujours peu performant, mais plus fiable et moins complexe.


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

L’utilisation de machines virtuelles n’est pas recommandée avec PostgreSQL. En effet, la couche de virtualisation cache totalement les ressources physiques au système, ce qui rend l’investigation et l’optimisation des performances beaucoup plus difficiles qu’avec des serveurs physiques dédiés.

Il est néanmoins possible, et très courant, d’utiliser des machines virtuelles avec PostgreSQL. Leur configuration doit alors être orientée vers la stabilité des performances. Cette configuration est complexe et difficile à suivre dans le temps. Les différentes parties de la plate-forme (virtualisation, système et bases de données) sont généralement administrées par des équipes techniques différentes, ce qui rend le diagnostic et la résolution de problèmes de performances plus difficiles. Les outils de supervision de chacun sont séparés et les informations plus difficiles à corréler.

Les solutions de virtualisation proposent généralement des fonctionnalités d’overcommit : les ressources allouées ne sont pas réservées à la machine virtuelle, la somme des ressources de l’ensemble des machines virtuelles peut donc être supérieure aux capacités du matériel. Dans ce cas, les machines peuvent ne pas disposer des ressources qu’elles croient avoir en cas de forte charge. Cette fonctionnalité est bien plus dangereuse avec PostgreSQL car la configuration du serveur est basée sur la mémoire disponible sur la VM. Si PostgreSQL utilise de la mémoire alors qu’elle se trouve en swap sur l’hyperviseur, les performances seront médiocres, et l’administrateur de bases de données aura de grandes difficultés à trouver la cause du problème du point de vue de la VM. Par conséquent, il est fortement conseillé de dédier un minimum de ressources aux VM PostgreSQL, et de superviser constamment l’overcommit du côté de l’hyperviseur pour éviter ce trashing.

Il est généralement conseillé d’utiliser au moins 4 cœurs physiques. En fonction de la complexité des requêtes, du volume de données, de la puissance du CPU, un cœur physique sert en moyenne de 1 à 20 requêtes simultanées. L’ordonnancement des cœurs par les hyperviseurs a pour conséquence qu’une machine virtuelle est en « pause » tant que l’hyperviseur ne dispose pas de l’ensemble des vCPU alloués à la machine virtuelle pour la faire tourner. Dans le cas d’une configuration contenant des machines avec très peu de vCPU et d’une autre avec un nombre de vCPU plus important, la VM avec beaucoup de vCPU risque de bénéficier de moins de cycles processeurs lors des périodes de forte charge. Ainsi, les petites VM sont plus faciles à ordonnancer que les grosses, et une perte de puissance due à l’ordonnancement est possible dans ce cas. Cet effet, appelé Steal Time dans différents outils système (top, sysstat…), se mesure en temps processeur où la VM a un processus en attente d’exécution, mais où l’hyperviseur utilise ce temps processeur pour une autre tâche. C’est pourquoi il faut veiller à configurer les VM pour éviter ce phénomène, avec un nombre de vCPU inférieurs au nombre de cœurs physiques réel sur l’hyperviseur.

Le point le plus négatif de la virtualisation de serveurs de bases de données concerne la performance des disques. La mutualisation des disques pose généralement des problèmes de performances car les disques sont utilisés pour des profils d’I/O généralement différents. Le RAID 5 est réputé offrir le meilleur rapport performance/coût… sauf pour les bases de données, qui effectuent de nombreux accès aléatoires. De ce fait, le RAID 10 est préconisé car il est plus performant sur les accès aléatoires en écriture pour un nombre de disques équivalent. Avec la virtualisation, peu de disques, mais de grande capacité, sont généralement prévus sur les hyperviseurs ; or cela implique un coût supérieur pour l’utilisation de RAID 10 et des performances inférieures sur les SGDB qui tirent de meilleures performances des disques lorsqu’ils sont nombreux.

Enfin, les solutions de virtualisation effectuent du Thin Provisioning sur les disques pour minimiser les pertes d’espace. Pour cela, les blocs sont alloués et initialisés à la demande, ce qui apporte une latence particulièrement perceptible au niveau de l’écriture des journaux de transaction (in fine, cela détermine le nombre maximum de commits en écriture par seconde possible). Il est donc recommandé de configurer les disques de PostgreSQL en Thick Provisionning.

De plus, dans le cas de disques virtualisés, bien veiller à ce que l’hyperviseur respecte les appels de synchronisation des caches disques (appel système sync).

De préférence, dans la mesure du possible, évitez de passer par la couche de virtualisation pour les disques et préférez des attachements SAN, plus sûrs et performants.


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

Il est aussi recommandé d’utiliser la même source NTP sur les OS invité (VM) et l’hôte ESXi afin d’éviter l’effet dit de time drifts. Il faut être attentif à ce problème des tops d’horloge. Si une VM manque des tops d’horloges sous une forte charge ou autre raison, elle va percevoir le temps qui passe comme étant plus lent qu’il ne l’est réellement. Par exemple, un OS invité avec un top d’horloge à 1 ms attendra 1000 tops d’horloge pour une simple seconde. Si 100 tops d’horloge sont perdus, alors 1100 tops d’horloge seront délivrés avant que la VM ne considère qu’une seconde soit passée. C’est ce qu’on appelle le time drift.

Il est recommandé d’utiliser le contrôleur vSCSI VMware Paravirtual (aka PVSCSI). Ce contrôleur est intégré à la virtualisation et a été conçu pour supporter de très hautes bandes passantes avec un coût minimal, c’est le driver le plus performant. De même pour le driver réseau il faut privilégier l’adaptateur réseau paravirtualisé de type VMXNET3 pour avoir les meilleures performances.

Un aspect très important de la configuration de la mémoire des machines virtuelles est l’accès mémoire non uniforme (NUMA). Cet accès permet d’accélérer l’accès mémoire en partitionnant la mémoire physique de telle sorte que chaque coeur d’un socket dispose de sa propre mémoire. Par exemple, avec un système à 16 cœurs et 128 Go de RAM, chaque cœur ou nœud possède 8 Go de mémoire physique.

Si une VM est configurée pour utiliser 12 Go de RAM, le système doit utiliser la mémoire d’un autre nœud. Le franchissement de la limite NUMA peut réduire les performances virtuelles jusqu’à 8 %, une bonne pratique consiste à configurer une VM pour utiliser les ressources d’un seul nœud NUMA.

Pour approfondir : Fiche KB préconisations pour VMWare


Système d’exploitation

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

Le choix du système d’exploitation n’est pas anodin. Les développeurs de PostgreSQL ont fait le choix de bien segmenter les rôles entre le système et le SGBD. Ainsi, PostgreSQL requiert que le système travaille de concert avec lui dans la gestion des accès disques, l’ordonnancement, etc.

PostgreSQL est principalement développé sur et pour Linux. Il fonctionne aussi sur d’autres systèmes, mais n’aura pas forcément les mêmes performances. De plus, la configuration du système et sa fiabilité jouent un grand rôle dans les performances et la robustesse de l’ensemble. Il est donc nécessaire de bien maîtriser ces points-là pour avancer dans l’optimisation.


Choix du système d’exploitation

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

PostgreSQL est écrit pour être le plus portable possible. Un grand nombre de choix dans son architecture a été fait en fonction de cette portabilité. Il est donc disponible sur la majorité des systèmes : Linux, BSD, Windows, Solaris, HPUX, etc. Cette portabilité est vérifiée en permanence avec la ferme de construction (BuildFarm, https://buildfarm.postgresql.org/).

Cela étant dit, il est malgré tout principalement développé sous Linux et la majorité des utilisateurs travaillent aussi avec Linux. Ce système est probablement le plus ouvert de tous, permettant ainsi une meilleure compréhension de ses mécaniques internes et ainsi une meilleure interaction. Ainsi, Linux est certainement le système le plus fonctionnel et performant avec PostgreSQL. La distribution Linux a généralement peu d’importance en ce qui concerne les performances. Les deux distributions les plus fréquemment utilisées sont Red Hat (et ses dérivés CentOS, Scientific Linux) et Debian.

Un autre système souvent utilisé est Windows. Ce dernier est beaucoup moins performant avec PostgreSQL que Linux. Cela est principalement dû à sa gestion assez mauvaise de la mémoire partagée. Cela a pour conséquence qu’il est difficile d’avoir un cache disque important pour PostgreSQL sous Windows.

De plus, vous ne pouvez pas démarrer PostgreSQL en tant que service si vous avez besoin de plus de 125 connexions pour des problématiques d’espace mémoire attribuée à un processus non-interactif. Le seul moyen de contourner ce problème sera de le lancer en mode interactif, depuis la ligne de commande. La limite théorique est alors repoussée à 750 connexions (plus d’information sur le wiki PostgreSQL).

Sous Windows, il est fortement recommandé de placer le paramètre update_process_title à off pour obtenir de bonnes performances. D’ailleurs, c’est la valeur par défaut depuis la version 9.6 :


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

Il est préférable de ne pas fonctionner avec une très ancienne version du noyau Linux. Les dernières versions sont les plus stables, les plus performantes, les plus compatibles avec les derniers matériels. Ce sont aussi celles qui proposent le plus de fonctionnalités intéressantes, comme la gestion complète du système de fichiers ext4, les control groups, une supervision avancée (avec perf et bpf), etc.

Le mieux est d’utiliser la version proposée par votre distribution Linux et de mettre à jour le noyau quand cela s’avère possible.

Le compiler vous-même peut dans certains cas vous apporter un plus en termes de performances. Mais ce plus est difficilement quantifiable et est assorti d’un gros inconvénient : avoir à gérer soi-même les mises à jour, la recompilation en cas d’oubli d’un pilote, etc.


Configuration du noyau

  • En plus du choix du noyau, certains paramètres nécessitent une configuration personnalisée
    • gestion du cache disque système
    • gestion de la sur-allocation de mémoire
    • taille et comportement du swap
    • affinité entre les cœurs et les espaces mémoire
    • scheduler processeur
    • huge pages

Le noyau, comme tout logiciel, est configurable. Certaines configurations sont particulièrement importantes pour PostgreSQL.


Contrôle du cache disque système

  • Gestion de l’écriture des dirty pages
  • Paramètres
    • vm.dirty_ratio
    • vm.dirty_background_ratio
    • vm.dirty_bytes
    • vm.dirty_background_bytes
  • Plus nécessaire depuis la version 9.6 (*_flush_after)

La gestion de l’écriture des dirty pages (pages modifiées en mémoire mais non synchronisées) du cache disque système s’effectue à travers les paramètres vm.dirty_ratio, vm.dirty_background_ratio, vm.dirty_bytes et vm.dirty_background_bytes.

vm.dirty_ratio exprime le pourcentage de pages mémoire modifiées à atteindre avant que les processus écrivent eux-mêmes les données du cache sur disque afin de les libérer. Ce comportement est à éviter. vm.dirty_background_ratio définit le pourcentage de pages mémoire modifiées forçant le noyau à commencer l’écriture les données du cache système en tâche de fond. Ce processus est beaucoup plus léger et à encourager. Ce dernier est alors seul à écrire alors que dans le premier cas, plusieurs processus tentent de vider le cache système en même temps. Ce comportement provoque alors un encombrement de la bande passante des disques dans les situations de forte charge en écriture, surtout lors des opérations provoquant des synchronisations de données modifiées en cache sur le disque, comme l’appel à fsync. Celui-ci est utilisé par PostgreSQL lors des checkpoints, ce qui peut provoquer des latences supplémentaires à ces moments-là.

Avant la version 9.6, pour réduire les conséquences de ce phénomène, il est conseillé d’abaisser vm.dirty_ratio à 10 et vm.dirty_background_ratio à 5. Ainsi, lors de fortes charges en écriture, nous demandons au noyau de reporter plus régulièrement son cache disque sur l’espace de stockage mais pour une volumétrie plus faible. Ainsi, l’encombrement de la bande passante vers les disques sera moins long si ceux-ci ne sont pas capables d’absorber ces écritures rapidement. Dans les situations où la quantité de mémoire physique est importante, ces paramètres peuvent même être encore abaissés à 2 et 1 respectivement. Ainsi, avec 32 Go de mémoire, ils représentent 640 Mo et 320 Mo de données à synchroniser, ce qui peut nécessiter plusieurs secondes d’écritures en fonction de la configuration disque utilisée.

Dans les cas plus extrêmes, 1 % de la mémoire représentent des volumétries trop importantes (par exemple, 1,3 Go pour 128 Go de mémoire physique). Les paramètres vm.dirty_bytes et vm.dirty_background_bytes permettent alors de contrôler ces mêmes comportements, mais en fonction d’une quantité de dirty pages exprimée en octets et non plus en pourcentage de la mémoire disponible. Notez que ces paramètres ne sont pas complémentaires entre eux. Le dernier paramètre ayant été positionné prend le pas sur le précédent.

Enfin, plus ces valeurs sont basses, plus les synchronisations seront fréquentes, plus la durée des opérations VACUUM et REINDEX, qui déclenchent beaucoup d’écritures sur disque, augmentera.

Depuis la version 9.6, ces options ne sont plus nécessaires grâce à ces nouveaux paramètres de PostgreSQL :

  • bgwriter_flush_after (512 ko par défaut) : lorsque plus de bgwriter_flush_after octets sont écrits sur disque par le background writer, le moteur tente de forcer la synchronisation sur disque ;
  • backend_flush_after (désactivé par défaut) : force la synchronisation sur disque lorsqu’un processus a écrit plus de backend_flush_after octets ; il est préférable d’éviter ce comportement, c’est pourquoi la valeur par défaut est 0 (désactivation) ;
  • wal_writer_flush_after (1 Mo par défaut) : quantité de données à partir de laquelle le wal writer synchronise les blocs sur disque ;
  • checkpoint_flush_after (256 ko par défaut) : lorsque plus de checkpoint_flush_after octets sont écrits sur disque lors d’un checkpoint, le moteur tente de forcer la synchronisation sur disque.

Configuration du OOM

  • Supervision de la sur-allocation par le noyau
  • Si cas critique, l’OOM fait un kill -9 du processus
  • Désactiver l’OOM pour un serveur dédié :
    • vm.overcommit_memory = 2
    • vm.overcommit_ratio = 80

Certaines applications réservent souvent plus de mémoire que nécessaire. Plusieurs optimisations noyau permettent aussi d’économiser de l’espace mémoire. Ainsi, par défaut, le noyau Linux s’autorise à allouer aux processus plus de mémoire qu’il n’en dispose réellement, le risque de réellement utiliser cette mémoire étant faible. On appelle ce comportement l’Overcommit Memory. Si celui-ci peut être intéressant dans certains cas d’utilisation, il peut devenir dangereux dans le cadre d’un serveur PostgreSQL dédié.

Effectivement, si le noyau arrive réellement à court de mémoire, il décide alors de tuer certains processus en fonction de leur impact sur le système. Il est alors fort probable que ce soit un processus PostgreSQL qui soit tué. Dans ce cas, les transactions en cours seront annulées, et une perte de données est parfois possible en fonction de la configuration de PostgreSQL. Une corruption est par contre plutôt exclue.

Il est possible de modifier ce comportement grâce aux paramètres vm.overcommit_memory et vm.overcommit_ratio du fichier /etc/sysctl.conf. En plaçant vm.overcommit_memory à 2, le noyau désactivera complètement l’overcommit memory. La taille maximum de mémoire utilisable par les applications se calcule alors grâce à la formule suivante :

(RAM * vm.overcommit_ratio / 100) + SWAP

Attention, la valeur par défaut du paramètre vm.overcommit_ratio est 50. Ainsi, sur un système avec 32 Go de mémoire et 2 Go de swap, nous obtenons seulement 18 Go de mémoire allouable ! Ne pas oublier de modifier ce paramètre ; avec vm.overcommit_ratio positionné à 75, nous obtenons 26 Go de mémoire utilisable par les applications sur les 32 Go disponibles. (Il existe un paramètre équivalent exprimé en kilooctets, overcommit_kbytes, mais il faut penser à l’adapter si on ajoute de la RAM).

Avoir un tel paramétrage permet de garantir qu’il y aura toujours au moins 20 % du total de la RAM disponible pour le cache disque, qui est très bénéfique à PostgreSQL.


Configuration du swap

  • Taille du swap
    • pas plus de 2 Go
  • Contrôler son utilisation
    • vm.swappiness : descendre à 10

Il convient de déterminer la taille du swap de façon judicieuse. En effet, le swap n’est plus que rarement utilisé sur un système moderne, et 2 Go suffisent amplement en temps normal. Avoir trop de swap a tendance à aggraver la situation dans un contexte où la mémoire devient rare : le système finit par s’effondrer à force de swapper et dé-swapper un nombre de processus trop élevé par rapport à ce qu’il est capable de gérer. Ne pas avoir de swap est un autre problème : cela ne permet pas de s’apercevoir d’une surconsommation de mémoire. Il convient donc de créer un espace de swap de 2 Go sur la machine.

Le paramètre vm.swappiness contrôle le comportement du noyau vis-à-vis de l’utilisation du swap. Plus ce pourcentage est élevé, plus le système a tendance à swapper facilement. Un système hébergeant une base de données ne doit swapper qu’en dernière extrémité. La valeur par défaut de 60 doit donc être abaissée à 10 pour éviter l’utilisation du swap dans la majorité des cas.


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

Attention, ne pas confondre multi-cœurs et multi-sockets ! Chaque processeur physique occupe un socket et peut contenir plusieurs cœurs. Le nombre de processeurs physiques peut être trouvé grâce au nombre d’identifiants dans le label physical id du fichier /proc/cpuinfo. Par exemple, sur un serveur bi-processeur :

root@serveur:~# grep "^physical id" /proc/cpuinfo | sort -u | wc -l
2

Plus simplement, si la commande lscpu est présente, cette information est représentée par le champ “CPU socket(s)” :

root@serveur:~# lscpu | grep -i socket
Cœur(s) par socket :                    2
Socket(s) :                             1

Sur une architecture NUMA (Non Uniform Memory Access), il existe une notion de distance entre les sockets processeurs et les « zones » mémoire (bancs de mémoire). La zone mémoire la plus proche d’un socket est alors définie comme sa zone « locale ». Il est plus coûteux pour les cœurs d’un processeur d’accéder aux zones mémoire distantes, ce qui implique des temps d’accès plus importants, et des débits plus faibles.

Le noyau Linux détecte ce type d’architecture au démarrage. Si le coût d’accès à une zone distante est trop important, il décide d’optimiser le travail en mémoire depuis chaque socket, privilégiant plus ou moins fortement les allocations et accès dans la zone de mémoire locale. Le paramètre vm.zone_reclaim_mode est alors supérieur à 0. Les processus étant exécutés sur un cœur processeur donné, ces derniers héritent de cette affinité processeur/zone mémoire. Le processus préfère alors libérer de l’espace dans sa zone mémoire locale si nécessaire plutôt que d’utiliser un espace mémoire distant libre, sapant par là même le travail de cache.

Si ce type d’optimisation peut être utile dans certains cas, il ne l’est pas dans un contexte de serveur de base de données où tout y est fait pour que les accès aux fichiers de données soient réalisés en mémoire, au travers des caches disque PostgreSQL ou système. Or, comme expliqué, les mécanismes du cache disque système sont impactés par les optimisations de vm.zone_reclaim_mode. Cette optimisation peut alors aboutir à une sous-utilisation de la mémoire, pénalisant notamment le cache avec un ratio d’accès moins important côté système. De plus, elles peuvent provoquer des variations aléatoires des performances en fonction du socket où un processus serveur est exécuté et des zones mémoire qu’il utilise.

Ainsi, sur des architectures multi-sockets, il est conseillé de désactiver ce paramètre en positionnant vm.zone_reclaim_mode à 0.

Pour illustrer les conséquences de cela, un test avec pg_dump sur une architecture NUMA montre les performances suivantes :

  • avec zone_reclaim_mode à 1, temps de dump : 20 h, CPU utilisé par le COPY : 3 à 5 %
  • avec zone_reclaim_mode à 0, temps de dump : 2 h, CPU utilisé par le COPY : 95 à 100 %

Le problème a été diagnostiqué à l’aide de l’outil système perf. Ce dernier a permis de mettre en évidence que la fonction find_busiest_group représentait le gros de l’activité du serveur. Dans le noyau Linux, cette fonction est utilisée en environnement multi-processeurs pour équilibrer la charge entre les différents processeurs.

Pour plus de détails, voir :


Configuration du scheduler processeur

  • Réduire la propension du kernel à migrer les processus
    • kernel.sched_migration_cost_ns = 5000000 (sched_migration_cost pour les noyaux <3.6)
  • Désactiver le regroupement par session TTY
    • kernel.sched_autogroup_enabled = 0

Depuis le noyau 2.6.23 l’ordonnanceur de tâches est le CFS (Completely Fair Scheduler). Celui-ci est en charge de distribuer les ressources aux différents processus de manière équitable. Lorsqu’un processus est en exécution depuis plus de kernel.sched_migration_cost_ns, celui-ci peut être migré afin de laisser la place à un autre processus. Lorsque de nombreux processus demandent des ressources, la gestion de l’ordonnancement et la migration des processus peuvent devenir pénalisantes.

Il est donc recommandé d’augmenter significativement cette valeur. Par exemple à 5 ms (5 000 000 ns).

L’ordonnanceur regroupe les processus par session (TTY) afin d’avoir un meilleur temps de réponse « perçu ». Dans le cas de PostgreSQL, l’ensemble des processus sont lancés par une seule session TTY. Ces derniers seraient alors dans un même groupe et pourraient être privés de ressources (allouées pour d’autres sessions).

Sans regroupement de processus :
        [proc PG. 1 | proc PG. 2 | proc PG. 3 | procPG . 4 | proc. 5 | proc. 6]
Avec regroupement de processus :
        [proc PG. 1, 2, 3, 4  |     proc. 5       |     proc. 6      ]

Pour désactiver ce comportement, il faut passer le paramètre kernel.sched_autogroup_enabled à 0.


Huge Pages

  • Utiliser des pages mémoire de 2 Mo au lieu de 4 ko
  • Réduction de la consommation mémoire des processus
  • Garantie shared buffers non swappés
  • vm.nr_overcommit_hugepages=x
  • huge_pages=on|off|try

Les systèmes d’exploitation utilisent un système de mémoire virtuelle : chaque contexte d’exécution (comme un processus) utilise un plan d’adressage virtuel, et c’est le processeur qui s’occupe de réaliser la correspondance entre l’adressage virtuel et l’adressage réel. Chaque processus fournit donc la correspondance entre les deux plans d’adressage, dans ce qu’on appelle une table de pagination.

Les processeurs modernes permettent d’utiliser plusieurs tailles de page mémoire simultanément. Pour les processeurs Intel/AMD, les tailles de page possibles sont 4 ko, 2 Mo et 1 Go.

Les pages de 4 ko sont les plus souples, car offrant une granularité plus fine. Toutefois, pour des grandes zones mémoire contiguës, il est plus économique d’utiliser des tailles de pages plus élevées : il faudra 262 144 entrées pour 1 Go de mémoire avec des pages de 4 ko, contre 512 entrées pour des pages de 2 Mo.

Permettre à PostgreSQL d’utiliser des Huge Pages réduit donc la consommation mémoire de chaque processus : en effet, chaque processus PostgreSQL dispose de sa propre table de pagination. Pour des shared buffers de 8 Go, chaque processus gaspille 16 Mo de mémoire rien que pour cette table, contre une centaine de ko pour des pages de 2 Mo. Cette mémoire pourra être utilisée à meilleur escient (work_mem par exemple, ou tout simplement du cache système).

Pour utiliser les Huge Pages :

  • huge_pages doit être positionné à try (essayer, et utiliser des pages de 4 ko si le système n’arrive pas à fournir les pages de 2 Mo) ou on : exiger des Huge Pages ;
  • vm.nr_overcommit_hugepages doit être suffisamment grand pour contenir les shared buffers et les autres zones mémoire partagées (tableau de verrous, etc.). Compter 10 % de plus que ce qui est défini pour shared_buffers devrait être suffisant, mais il n’est pas interdit de mettre des valeurs supérieures, puisque Linux créera avec ce système les Huge Pages à la volée (et les détruira à l’extinction de PostgreSQL). Sur un système hébergeant plusieurs instances, il faudra additionner toutes les zones mémoire de toutes les instances. La valeur de ce paramètre est en pages de la taille de Huge Page par défaut (valeur de Hugepagesize dans /proc/meminfo, habituellement 2 Mo).

Si vous souhaitez en apprendre plus sur le sujet des Huge Pages, un article détaillé est disponible dans la base de connaissances Dalibo.


Comment les configurer

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

Tous les paramètres expliqués ci-dessus sont à placer dans le fichier /etc/sysctl.conf. Ainsi, à chaque redémarrage du serveur, Linux va récupérer le paramétrage et l’appliquer.

Sur les systèmes Linux modernes, un répertoire /etc/sysctl.d existe où tout fichier ayant l’extension .conf est lu et pris en compte. Ces fichiers ont la même syntaxe que /etc/sysctl.conf. Il est ainsi préconisé d’y créer un ou plusieurs fichiers pour vos configurations spécifiques afin que ces dernières ne soient pas accidentellement écrasées lors d’une mise à jour système par exemple.

Il est possible d’appliquer vos modifications sans redémarrer tout le système grâce à la commande suivante :

$ sysctl -p

Choix du système de fichiers

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

Quel que soit le système d’exploitation, les systèmes de fichiers ne manquent pas. Linux en est la preuve avec pas moins d’une dizaine de systèmes de fichiers. Le choix peut paraître compliqué mais il se révèle fort simple : il est préférable d’utiliser le système de fichiers préconisé par votre distribution Linux. Ce système est à la base de tous les tests des développeurs de la distribution : il a donc plus de chances d’avoir moins de bugs, tout en proposant plus de performances. Les instances de production PostgreSQL utilisent de fait soit ext4, soit XFS.

En 2016, un benchmark sur Linux de Tomas Vondra de différents système de fichiers montrait que ext4 et XFS ont des performantes équivalentes. Si OpenZFS est un système de fichier proposant un panel impressionnant de fonctionnalité (dont : checksum, compression, gestion de snapshot), les performances en écriture sont cependant bien moins bonnes qu’avec ext4 ou XFS. De plus, il est plus complexe à mettre en place et à administrer. Btrfs est également peu performant.

Pour Windows, la question ne se pose pas. Le système VFAT n’est pas suffisamment stable pour qu’il puisse être utilisé avec PostgreSQL. De plus, il ne connaît pas le concept des liens symboliques, important lors de la création de tablespaces avec PostgreSQL. La seule solution disponible sous Windows est donc NTFS. L’installeur fourni par EnterpriseDB dispose d’une protection qui empêche l’installation d’une instance PostgreSQL sur une partition VFAT.

Quant à Solaris, ZFS est un système très intéressant grâce à son panel fonctionnel et son mécanisme de Copy On Write permettant de faire une copie des fichiers sans arrêter PostgreSQL (snapshot).

NFS peut sembler intéressant, vu ses fonctionnalités : facilité de mise en oeuvre, administration centralisée du stockage, mutualisation des espaces. Cependant, ce système de fichiers est source de nombreux problèmes avec PostgreSQL.
Si la base tient en mémoire et que les latences possibles ne sont pas importantes, on peut éventuellement utiliser NFS. Il faut la garantie que les opérations sont synchrones. Si ce n’est pas le cas, une panne sur la baie peut entraîner une corruption des données. Au minimum, l’option sync doit être présente côté serveur et les options hard, proto=tcp, noac et nointr doivent être présentes côté client.
Si vous souhaitez en apprendre plus sur le sujet des options pour NFS, un article détaillé est disponible dans la base de connaissances Dalibo, et la documentation de PostgreSQL à partir de la version 12.

Par contre, NFS est totalement déconseillé dans les environnements critiques avec PostgreSQL. Greg Smith, contributeur très connu, spécialisé dans l’optimisation de PostgreSQL, parle plus longuement des soucis de NFS avec PostgreSQL. En fait, il y a des dizaines d’exemples de gens ayant eu des problèmes avec NFS. Les problèmes de performance sont quasi-systématiques, et les problèmes de fiabilité fréquents, et compliqués à diagnostiquer (comme illustré dans ce mail, où le problème venait du noyau Linux).


Configuration du système de fichiers

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

Quel que soit le système de fichiers choisi, il est possible de le configurer lors du montage, via le fichier /etc/fstab.

Certaines options sont intéressantes en termes de performances. Ainsi, noatime évite l’écriture de l’horodatage du dernier accès au fichier. nodiratime fait de même au niveau du répertoire. Depuis plusieurs années maintenant, nodiratime est inclus dans noatime.

L’option dir_index permet de modifier la méthode de recherche des fichiers dans un répertoire en utilisant un index spécifique pour accélérer cette opération. L’outil tune2fs permet de s’assurer que cette fonctionnalité est activée ou non. Par exemple, pour une partition /dev/sda1 :

# tune2fs -l /dev/sda1 | grep features
Filesystem features:      has_journal resize_inode **dir_index** filetype
                          needs_recovery sparse_super large_file

dir_index est activé par défaut sur ext3 et ext4. Il ne pourrait être absent que si le système de fichiers était originellement un système ext2, qui aurait été mal migré.

Pour l’activer, il faut utiliser l’outil tune2fs. Par exemple :

# tune2fs -O dir_index /dev/sda1

Enfin, il reste à créer ces index à l’aide de la commande e2fsck :

# e2fsck -D /dev/sda1

Les options data=writeback et nobarrier sont souvent citées comme optimisation potentielle. Le mode writeback de journalisation des ext3 et ext4 est à éviter. Effectivement, dans certains cas rares, en cas d’interruption brutale, certains fichiers peuvent conserver des blocs fantômes ayant été normalement supprimés juste avant le crash.

L’option nobarrier peut être utilisée, mais avec précaution. Cette dernière peut apporter une différence significative en termes de performance, mais elle met en péril vos données en cas de coupure soudaine où les caches disques, RAID ou baies sont alors perdus. Cette option ne peut être utilisée qu’à la seule condition que tous ces différents caches soient sécurisés par une batterie.


Serveur de bases de données

  • Version
  • Configuration
  • Emplacement des fichiers

Après avoir vu le matériel et le système d’exploitation, il est temps de passer au serveur de bases de données. Lors d’une optimisation, il est important de vérifier trois points essentiels :

  • la version de PostgreSQL ;
  • sa configuration (uniquement le fichier postgresql.conf) ;
  • et l’emplacement des fichiers (journaux de transactions, tables, index, statistiques).

Version

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

Il est généralement conseillé de passer à une version majeure plus récente qu’à partir du moment où les fonctionnalités proposées sont suffisamment intéressantes. C’est un bon conseil en soi mais il faut aussi se rappeler qu’un gros travail est fait pour améliorer le planificateur. Ces améliorations peuvent être une raison suffisante pour changer de version majeure.

Voici quelques exemples frappants :

  • La version 9.0 dispose d’une optimisation du planificateur lui permettant de supprimer une jointure LEFT JOIN si elle est inutile pour l’obtention du résultat. C’est une optimisation particulièrement bienvenue pour tous les utilisateurs d’ORM.
  • La version 9.1 dispose du SSI (Serializable Snapshot Isolation). Il s’agit d’une implémentation très performante du mode d’isolation sérialisée. Ce mode permet d’éviter l’utilisation des SELECT FOR UPDATE.
  • La version 9.2 dispose d’un grand nombre d’améliorations du planificateur et des processus postgres qui en font une version exceptionnelle pour les performances, notamment les parcours d’index seuls.
  • La version 9.6 propose la parallélisation de l’exécution de certaines requêtes.

Compiler soi-même PostgreSQL ne permet pas de gagner réellement en performance. Même s’il peut y avoir un gain, ce dernier ne peut être que mineur et difficilement identifiable. La compilation n’a un impact réellement identifié que sur les architecture Itanium (IA-32 et IA-64) avec le compilateur propriétaire Intel (icc), cf le site d’Intel.

Dans certains cas, ce compilateur apporte de meilleures performances au niveau de PostgreSQL. On a observé jusqu’à 10 % de gain par rapport à une compilation « classique » avec gcc. Il faut toutefois prendre deux éléments importants en compte avant de remplacer les binaires de PostgreSQL par des binaires recompilés avec icc :

  • la taille des fichiers recompilés est nettement plus grande ;
  • la compilation avec icc est moins documentée et moins testée qu’avec gcc.

Il est donc nécessaire de préparer avec soin, de documenter la procédure de compilation et de réaliser des tests approfondis avant de mettre une version recompilée de PostgreSQL dans un environnement de production.


Configuration - mémoire

  • shared_buffers
  • wal_buffers
  • work_mem
  • hash_mem_multiplier
  • maintenance_work_mem

Ces quatre paramètres concernent tous la quantité de mémoire que PostgreSQL utilisera pour ses différentes opérations.

shared_buffers permet de configurer la taille du cache disque de PostgreSQL. Chaque fois qu’un utilisateur veut extraire des données d’une table (par une requête SELECT) ou modifier les données d’une table (par exemple avec une requête UPDATE), PostgreSQL doit d’abord lire les lignes impliquées et les mettre dans son cache disque. Cette lecture prend du temps. Si ces lignes sont déjà dans le cache, l’opération de lecture n’est plus utile, ce qui permet de renvoyer plus rapidement les données à l’utilisateur. Ce cache est commun à tous les processus PostgreSQL, il n’existe donc qu’en un exemplaire. Généralement, il faut lui donner une grande taille, tout en conservant malgré tout la majorité de la mémoire pour le cache disque du système, à priori plus efficace pour de grosses quantités de données. Le pourcentage généralement préconisé est de 25 % de la mémoire totale pour un serveur dédié. Donc, par exemple, pour un serveur contenant 8 Go de mémoire, nous configurerons le paramètre shared_buffers à 2 Go. Néanmoins, on veillera à ne pas dépasser 8 Go. Des études ont montré que les performances décroissaient avec plus de mémoire.

PostgreSQL dispose d’un autre cache disque. Ce dernier concerne les journaux de transactions. Il est généralement bien plus petit que shared_buffers mais, si le serveur est multi-processeurs et qu’il y a de nombreuses connexions simultanées au serveur PostgreSQL, il est important de l’augmenter. Le paramètre en question s’appelle wal_buffers. Plus cette mémoire est importante, plus les transactions seront conservées en mémoire avant le COMMIT. À partir du moment où le COMMIT d’une transaction arrive, toutes les modifications effectuées dans ce cache par cette transaction sont enregistrées dans le fichier du journal de transactions. La valeur par défaut est de -1, ce qui correspond à un calcul automatique au démarrage de PostgreSQL. Avec les tailles de shared_buffers actuelles, il vaut généralement la taille d’un journal de transaction (16 Mo par défaut).

Deux autres paramètres de configuration de la mémoire sont essentiels pour de bonnes performances, mais eux sont valables par processus. work_mem est utilisé comme mémoire de travail pour les tris et les hachages. S’il est nécessaire d’utiliser plus de mémoire, le contenu de cette mémoire est stocké sur disque pour permettre la réutilisation de la mémoire. Par exemple, si une jointure demande à stocker 52 Mo en mémoire alors que le paramètre work_mem vaut 10 Mo, à chaque utilisation de 10 Mo, cette partie de mémoire sera copiée sur disque, ce qui fait en gros 50 Mo écrit sur disque pour cette jointure. Si, par contre, le paramètre work_mem vaut 60 Mo, aucune écriture n’aura lieu sur disque, ce qui accélérera forcément l’opération de jointure. Cette mémoire est utilisée par chaque processus du serveur PostgreSQL, de manière indépendante. Suivant la complexité des requêtes, il est même possible qu’un processus utilise plusieurs fois cette mémoire (par exemple si une requête fait une jointure et un tri). Il faut faire très attention à la valeur à donner à ce paramètre et le mettre en relation avec le nombre maximum de connexions (paramètre max_connections). Si la valeur est trop petite, cela forcera des écritures sur le disque par PostgreSQL. Si elle est trop grande, cela pourrait faire swapper le serveur. Généralement, une valeur entre 10 et 50 Mo est concevable. Au-delà de 100 Mo, il y a probablement un problème ailleurs : des tris sur de trop gros volumes de données, une mémoire insuffisante, un manque d’index (utilisé pour les tris), etc. Des valeurs vraiment grandes ne sont valables que sur des systèmes d’infocentre.

Lorsque l’optimiseur sélectionne les nœuds d’exécution, il compare son estimation de la mémoire utilisée par la table de hachage à la valeur du paramètre work_mem. Si cette estimation est supérieure, il passera plutôt par un agrégat par tri. Si elle est inférieure ou égale, il passera par un agrégat par hachage. Cependant, lorsque le plan est donné à l’exécuteur, et que ce dernier exécute l’agrégat par hachage, si jamais l’estimation était mauvaise et qu’il faut plus de mémoire que prévu, plus que ce que permet le paramètre work_mem, l’exécuteur n’a pas d’autres solutions que d’allouer plus de mémoire, du moins jusque PostgreSQL 12 compris. Cette opération ne peut pas passer sur disque. Dans beaucoup de cas, cela n’est pas bien grave. Mais dans certains cas, on peut se trouver à allouer beaucoup trop de mémoire. Au mieux, cela interdit aux autres processus d’en allouer. Au pire, il ne reste plus de mémoire pour réaliser quoi que ce soit. La version 13 améliore cela en permettant à l’exécuteur d’allouer plus de mémoire, mais si cela représente trop de mémoire, l’exécuteur peut utiliser des fichiers temporaires sur disque. La mémoire maximale considérée par le planificateur est toujours la valeur du paramètre work_mem. L’exécuteur, lui, avant d’envoyer les données dans des fichiers temporaires, se permet de consommer une mémoire correspondant à work_mem multiplié par hash_mem_multiplier (1 par défaut).

Quant à maintenance_work_mem, il est aussi utilisé par chaque processus PostgreSQL réalisant une opération particulière : un VACUUM, une création d’index ou l’ajout d’une clé étrangère. Comme il est peu fréquent que ces opérations soient effectuées en simultané, la valeur de ce paramètre est très souvent bien supérieure à celle du paramètre work_mem. Sa valeur se situe fréquemment entre 128 Mo et 1 Go, voire plus.


Configuration - planificateur

  • effective_cache_size
  • random_page_cost

Le planificateur dispose de plusieurs paramètres de configuration. Les deux principaux sont effective_cache_size et random_page_cost.

Le premier permet d’indiquer la taille du cache disque du système d’exploitation. Ce n’est donc pas une mémoire que PostgreSQL va allouer, c’est plutôt une simple indication de ce qui est disponible en dehors de la mémoire taillée par le paramètre shared_buffers. Le planificateur se base sur ce paramètre pour évaluer les chances de trouver des pages de données en mémoire. Une valeur plus importante aura tendance à faire en sorte que le planificateur privilégie l’utilisation des index, alors qu’une valeur plus petite aura l’effet inverse. Généralement, il se positionne à 2/3 de la mémoire d’un serveur pour un serveur dédié.

Une meilleure estimation est possible en parcourant les statistiques du système d’exploitation. Sur les systèmes Unix, ajoutez les nombres buffers+cached provenant des outils top ou free. Sur Windows, voir la partie « System Cache » dans l’onglet « Performance » du gestionnaire des tâches. Par exemple, sur un portable avec 2 Go de mémoire, il est possible d’avoir ceci :

$ free
             total       used       free     shared    buffers     cached
Mem:       2066152    1525916     540236          0     190580     598536
-/+ buffers/cache:     736800    1329352
Swap:      1951856          0    1951856

Soit 789 166 ko, résultat de l’addition de 190 580 (colonne buffers) et 598 536 (colonne cached).

Le paramètre random_page_cost permet de faire appréhender au planificateur le fait qu’une lecture aléatoire (autrement dit avec déplacement de la tête de lecture) est autrement plus coûteuse qu’une lecture séquentielle. Par défaut, la lecture aléatoire a un coût 4 fois plus important que la lecture séquentielle. Ce n’est qu’une estimation, cela n’a pas à voir directement avec la vitesse des disques. Ça le prend en compte, mais ça prend aussi en compte l’effet du cache. Cette estimation peut être revue. Si elle est revue à la baisse, les parcours aléatoires seront moins coûteux et, par conséquent, les parcours d’index seront plus facilement sélectionnés. Si elle est revue à la hausse, les parcours aléatoires coûteront encore plus cher, ce qui risque d’annuler toute possibilité d’utiliser un index. La valeur 4 est une estimation basique. En cas d’utilisation de disques rapides, il ne faut pas hésiter à descendre un peu cette valeur (entre 2 et 3 par exemple). Si les données tiennent entièrement en cache ou sont stockées sur des disques SSD, il est même possible de descendre encore plus cette valeur.


Configuration - parallélisation

  • max_worker_processes
  • max_parallel_workers
  • max_parallel_workers_per_gather
  • min_parallel_table_scan_size
  • min_parallel_index_scan_size

À partir de la version 9.6, un processus PostgreSQL peut se faire aider d’autres processus pour exécuter une seule et même requête. Le nombre de processus utilisables pour une requête dépend de la valeur du paramètre max_parallel_workers_per_gather (à 2 par défaut). Si plusieurs processus veulent paralléliser l’exécution de leur requête, le nombre de processus d’aide ne pourra pas dépasser la valeur du paramètre max_parallel_workers (8 par défaut).

Il est à noter que ce nombre ne peut pas dépasser la valeur du paramètre max_worker_processes (par défaut à 8). De plus, avant la version 10, le paramètre max_parallel_workers n’existait pas et tout se basait sur le paramètre max_worker_processes.

La parallélisation peut se faire sur différentes parties d’une requête, comme un parcours de table ou d’index, une jointure ou un calcul d’agrégat. Dans le cas d’un parcours, la parallélisation n’est possible que si la table ou l’index est suffisamment volumineux pour qu’une telle action soit intéressante au niveau des performances. Le volume déclencheur dépend de la valeur du paramètre min_parallel_table_scan_size, dont la valeur par défaut est de 8 Mo, pour une table et de la valeur du paramètre min_parallel_index_scan_size pour un index (512 ko par défaut).

Voici comment le moteur détermine le nombre de workers à exécuter :

  • Taille de la relation = T
  • min_parallel_table_scan_size = S (dans le cas d’une table)
    • si T < S => pas de worker
    • si T > S => on utilise un worker
    • si T > Sx3 => on utilise 1 worker supplémentaire (total 2 processus)
    • si T > Sx3x3 => on utilise 2 workers supplémentaires (total 3)
    • si T > Sx3^3 => on utilise 3 workers supplémentaires (total 4)
    • etc.

Configuration - WAL

  • fsync (on !)
  • min_wal_size (80 Mo) / max_wal_size (1 Go) (>= 9.6)
    • checkpoint_segments (nombre journaux, jusque 9.5)
  • checkpoint_timeout (5 min, ou plus)
  • checkpoint_completion_target (passer à 0.9)

fsync est le paramètre qui assure que les données sont non seulement écrites mais aussi forcées sur disque. En fait, quand PostgreSQL écrit dans des fichiers, cela passe par des appels système pour le noyau qui, pour des raisons de performances, conserve dans un premier temps les données dans un cache. En cas de coupure de courant, si ce cache n’est pas vidé sur disque, il est possible que des données enregistrées par un COMMIT implicite ou explicite n’aient pas atteint le disque et soient donc perdues une fois le serveur redémarré, ou pire, que des données aient été modifiées dans des fichiers de données, sans avoir été auparavant écrites dans le journal. Cela entraînera des incohérences dans les fichiers de données au redémarrage. Il est donc essentiel que les données enregistrées dans les journaux de transactions soient non seulement écrites, mais que le noyau soit forcé de les écrire réellement sur disque. Cette opération s’appelle fsync, et est activé par défaut (on). C’est essentiel pour la fiabilité, même si cela impacte très négativement les performances en écriture en cas de nombreuses transactions. Il est donc obligatoire en production de conserver ce paramètre activé. Pour accélérer de très gros imports ou restaurations, on peut le passer exceptionnellement à off, et l’on n’oubliera pas de revenir à on.

Chaque bloc modifié dans le cache disque de PostgreSQL doit être écrit sur disque au bout d’un certain temps. Le premier paramètre concerné est checkpoint_timeout, qui permet de déclencher un CHECKPOINT au moins toutes les X minutes (5 par défaut). Pour lisser des grosses écritures, on le monte fréquemment à 15 minutes voire plus. Cela peut aussi avoir l’intérêt de réduire un peu la taille des journaux générés : en effet, PostgreSQL écrit un bloc modifié intégralement dans les journaux lors de sa première modification après un checkpoint et une fois que ce bloc intégral est enregistré, il n’écrit plus que des deltas du bloc correspondant aux modifications réalisées.

Tout surplus d’activité doit aussi être géré. Un surplus d’activité engendrera des journaux de transactions supplémentaires. Le meilleur moyen dans ce cas est de préciser au bout de combien de journaux traités il faut lancer un CHECKPOINT.

À partir de la version 9.5, on utilise ces deux paramètres :

  • min_wal_size : quantité de WAL conservés pour le recyclage (par défaut 80 Mo) ;
  • max_wal_size : quantité maximale de WAL avant un checkpoint (par défaut 1 Go, mais on peut monter beaucoup plus haut).

Le nom du paramètre max_wal_size peut porter à confusion : le volume de WAL peut dépasser largement max_wal_size en cas de forte activité ou de retard de l’archivage, ce n’est en aucun cas une valeur plafond.

Avant la version 9.5, le paramètre équivalent à min_wal_size et max_wal_size se nommait checkpoint_segments et était exprimé en nombre de journaux de 16 Mo.

Un checkpoint déclenché par atteinte des seuils max_wal_size ou checkpoint_segments apparaît dans postgresql.log. Si cela arrive trop fréquemment, il est conseillé d’augmenter ces paramètres.

checkpoint_completion_target permet de lisser les écritures du checkpoint pour éviter de saturer les disques par de grosses écritures au détriment des requêtes des utilisateurs. On le monte généralement à 0.9 (soit 90 % de checkpoint_timeout, donc 4 minutes et demi par défaut).


Configuration - statistiques

  • track_activities
  • track_counts
  • track_functions et track_io_timing

Ces quatre paramètres ne permettent pas de gagner en performances. En fait, ils vont même faire un peu perdre, car ils ajoutent une activité supplémentaire de récupération de statistiques sur l’activité des processus de PostgreSQL. track_counts permet de compter, par exemple, le nombre de transactions validées et annulées, le nombre de blocs lus dans le cache de PostgreSQL et en dehors, le nombre de parcours séquentiels (par table) et d’index (par index). La charge supplémentaire n’est généralement pas importante mais elle est là. Cependant, les informations que cela procure sont essentielles pour travailler sur les performances et pour avoir un système de supervision (là-aussi, la base pour de l’optimisation ultérieure).

Les deux premiers paramètres sont activés par défaut. Les désactiver peut vous faire un peu gagner en performance mais les informations que vous perdrez vous empêcheront d’aller très loin en matière d’optimisation.

D’autres paramètres, désactivés par défaut, permettent d’aller plus loin. track_functions permet de récupérer des informations sur l’utilisation des routines stockées. track_io_timing réalise un chronométrage des opérations de lecture et écriture disque ; il complète les champs blk_read_time et blk_write_time dans pg_stat_database et pg_stat_statements et les plans d’exécutions appelés avec EXPLAIN (ANALYZE,BUFFERS). Avant de l’activer sur une machine peu performante, vérifiez l’impact avec l’outil pg_test_timing.


Configuration - autovacuum

  • autovacuum

L’autovacuum doit être activé. Ce processus supplémentaire coûte un peu en performances, mais il s’acquitte de deux tâches importantes pour les performances : éviter la fragmentation dans les tables et index, et mettre à jour les statistiques sur les données.

Sa configuration est généralement trop basse pour être suffisamment efficace.


Emplacement des fichiers de données

  • Séparer les objets suivant leur utilisation
  • Tablespaces
  • Quelques stratégies
    • séparer tables et index
    • séparer archives et données vivantes
  • Configuration possible des tablespaces
    • seq_page_cost, random_page_cost
    • effective_io_concurrency, maintenance_io_concurrency

Il est possible de séparer les objets SQL dans des disques différents. Par défaut, PostgreSQL se charge du placement des objets sur le disque. Tout a lieu dans le répertoire des données, mais il est possible de créer des répertoires de stockage supplémentaires. Le nom de ces répertoires, au niveau SQL, est tablespace.

Pour placer un objet dans un tablespace, il faut créer ce tablespace si ce n’est pas déjà fait, puis créer les objets avec la clause TABLESPACE, ou les déplacer s’ils existent déjà. Voici un exemple complet :

# mkdir /opt/tablespace1
# chown postgres:postgres /opt/tablespace1
# chmod 700 /opt/tablespace1
# sudo -iu postgres psql
postgres =# CREATE TABLESPACE grosdisque LOCATION '/opt/tablespace1';

postgres=# \db
           List of tablespaces
    Name    |  Owner   |     Location     
------------+----------+------------------
 grosdisque | postgres | /opt/tablespace1
 pg_default | postgres | 
 pg_global  | postgres | 
(3 rows)

postgres=# CREATE TABLE tablefroide (i int) TABLESPACE grosdisque ;
CREATE TABLE

postgres=# \d tablefroide 
            Table "public.tablefroide"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 i      | integer |           |          | 
Tablespace: "grosdisque"

postgres=# ALTER TABLE tablefroide SET TABLESPACE pg_default ;
ALTER TABLE

L’idée est de séparer les objets suivant leur utilisation. Une configuration assez souvent utilisée est de placer les tables dans un tablespace et les index dans un autre. Cela permet des écritures quasi simultanées sur différents fichiers.

La seule configuration possible au niveau des tablespaces se situe au niveau des paramètres seq_page_cost, random_page_cost, effective_io_concurrency et maintenance_io_concurrency. Les deux premiers sont utilisés par le planificateur pour évaluer le temps de récupération d’une page séquentielle et d’une page aléatoire. C’est habituellement intéressant avec les SSD qui ont normalement une vitesse sensiblement équivalente pour les accès séquentiels et aléatoires, contrairement aux disques magnétiques.

ALTER TABLESPACE disque_ssd SET ( random_page_cost = 1 );

Quant à effective_io_concurrency, il a pour but d’indiquer le nombre d’opérations disques possibles en même temps pour un client (prefetch). Le défaut vaut 1. Dans le cas d’un système disque utilisant un RAID matériel, il faut le configurer en fonction du nombre de disques utiles dans le RAID (n s’il s’agit d’un RAID 1 ou RAID 10, n-1 s’il s’agit d’un RAID 5). Avec du SSD, il est possible de monter encore bien au-delà de cette valeur, étant donné la rapidité de ce type de disque. La valeur maximale est de 1000. Cependant, seuls les parcours Bitmap Scan sont impactés par la configuration de ce paramètre. (Attention, à partir de la version 13, le principe reste le même, mais la valeur exacte de ce paramètre doit être 2 à 5 fois plus élevée qu’auparavant, selon la formule des notes de version).

Toujours à partir de la version 13, un nouveau paramètre apparaît : maintenance_io_concurrency. Il a le même but que effective_io_concurrency, mais pour les opérations de maintenance, non les requêtes. Celles-ci peuvent ainsi se voir accorder plus de ressources qu’une simple requête. Le défaut est de 10, et il faut penser à le monter aussi si on adapte effective_io_concurrency.

Attention, pour des raisons de sécurité et de fiabilité, les répertoires choisis pour les données de votre instance ne doivent pas être à la racine d’un point de montage. Que ce soit le répertoire PGDATA , le répertoire pg_wal ou les éventuels tablespaces. Si un ou plusieurs points de montage sont dédiés à l’utilisation de PostgreSQL, positionnez toujours les données dans un sous-répertoire, voire deux niveaux en dessous du point de montage (eg. <point de montage>/<version majeure>/<nom instance>).

À ce propos, voir :


Emplacement des journaux de transactions

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

Chaque donnée modifiée est écrite une première fois dans les journaux de transactions et une deuxième fois dans les fichiers de données. Cependant, les écritures dans ces deux types de fichiers sont très différentes. Les opérations dans les journaux de transactions sont uniquement des écritures séquentielles, sur de petits fichiers (d’une taille de 16 Mo par défaut), alors que celles des fichiers de données sont des lectures et des écritures fortement aléatoires, sur des fichiers bien plus gros (au maximum 1 Go). Du fait d’une utilisation très différente, avoir un système disque pour l’un et un système disque pour l’autre permet de gagner énormément en performances. Il faut donc pouvoir les séparer.

Avant la version 8.3, il est nécessaire d’arrêter PostgreSQL, de déplacer le répertoire des journaux de transactions, de créer un lien vers ce répertoire, et enfin de redémarrer PostgreSQL. Voici un exemple qui montre le déplacement dans /pgwal.

# systemctl stop postgresql-12
# cd /var/lib/pgsql/12/data
# mv pg_wal /pgwal
# ln -s /pgwal pg_wal
# ls -l pg_wal
lrwxrwxrwx. 1 root root 6 Sep 18 16:07 pg_wal -> /pgwal
# systemctl start postgresql-12

Il est aussi possible de faire en sorte que la commande initdb le fasse elle-même. Pour cela, il faut utiliser l’option -X :

$ initdb -X /pgxlog

Cependant le résultat est le même. Un lien symbolique existe dans le répertoire de données pour que PostgreSQL retrouve le répertoire des journaux de transactions.


Emplacement des fichiers statistiques

  • Placer les fichiers statistiques sur un autre disque
    • et de préférence sur un montage en RAM
  • Option stats_temp_directory

PostgreSQL met à disposition différents compteurs statistiques via des vues. Ces vues utilisent des métriques stockées dans des fichiers de statistiques, mis à jour par le processus stats collector. Ces fichiers sont localisés dans un répertoire pointé par le paramètre stats_temp_directory. Par défaut, les fichiers sont stockés dans le sous-répertoire pg_stat_tmp du répertoire principal des données. Habituellement, cela ne pose pas de difficultés, mais sous une forte charge, il peut entraîner une forte activité disque. Dans de tels cas, le processus stats collector apparaît parmi les processus les plus consommateurs d’I/O avec iotop.

Lorsque le problème se pose, il est recommandé de déplacer ces fichiers dans un RAM-disk. Cette opération peut être réalisée à chaud en suivant la procédure suivante. Attention cependant, depuis PostgreSQL 9.4, le module pg_stat_statements sauvegarde le texte des requêtes également à cet emplacement, sans limite de taille pour la taille des requêtes. L’espace occupé par ces statistiques peut donc être très important. Le RAM-disk fera donc au moins 64, voire 128 Mo, pour tenir compte de ce changement.

Le point de montage employé doit être placé à l’extérieur du PGDATA, tout comme les répertoires des tablespaces.

Cela est fait par défaut par les paquets Debian, qui font pointer pg_stat_tmp vers /run, qui est un tmpfs.

Voici la procédure à suivre sur CentOS 7 pour mettre en place un RAM-disk pour le répertoire des fichiers statistiques :

  • création du point de montage (en tant qu’utilisateur postgres) :
$ mkdir /var/lib/pgsql/12/pg_stat_tmpfs
  • création et montage du système de fichiers :
# mount -o \
    auto,nodev,nosuid,noexec,noatime,mode=0700,size=256M,uid=postgres,gid=postgres \
    -t tmpfs tmps /var/lib/pgsql/12/pg_stat_tmpfs
# mount
...
tmps on /var/lib/pgsql/12/pg_stat_tmpfs type tmpfs
       (rw,nosuid,nodev,noexec,noatime,seclabel,size=262144k,mode=700,uid=26,gid=26)
  • modification de la configuration PostgreSQL dans postgresql.conf :
stats_temp_directory = '/var/lib/pgsql/12/pg_stat_tmpfs'
  • recharger la configuration de PostgreSQL :
postgres=# SELECT pg_reload_conf() ;

postgres=# SHOW stats_temp_directory ;
 stats_temp_directory
----------------------
 /var/lib/pgsql/12/pg_stat_tmpfs
  • vérifier dans postgresql.conf que le changement de paramètre a bien été pris en compte et qu’il n’y a pas d’erreur :
LOG:  received SIGHUP, reloading configuration files
LOG:  parameter "stats_temp_directory" changed to "/var/lib/pgsql/12/pg_stat_tmpfs"
  • ajouter au fichier /etc/fstab la ligne suivante pour que la modification survive au prochain redémarrage :
tmpfs /var/lib/pgsql/12/pg_stat_tmpfs tmpfs auto,nodev,nosuid,noexec,
      noatime,uid=postgres,gid=postgres,mode=0700,size=256M  0 0

Outils

  • pgtune
  • pgbench
  • postgresqltuner.pl

Nous allons discuter de trois outils.

Le premier, pgtune, est un petit script permettant d’obtenir rapidement et simplement une configuration un peu plus optimisée que celle proposée par la commande initdb.

Le second est livré avec les sources de PostgreSQL. pgbench a pour but de permettre la réalisation de benchmarks simples pour un serveur PostgreSQL.

Enfin, le dernier, postgresqltuner.pl, est un script permettant de vérifier la configuration matérielle, système et PostgreSQL d’un serveur.


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

Le site du projet en ruby se trouve sur github.

pgtune est capable de trouver la quantité de mémoire disponible sur le système. À partir de cette information et de quelques règles internes, il arrive à déduire une configuration bien meilleure que la configuration par défaut. Il est important de lui indiquer le type d’utilisation principale : Web, datawarehouse, mixed, etc.

Commençons par une configuration pour une utilisation par une application web sur une machine avec 8 Go de RAM, 2 CPU, un stockage mécanique (HDD) :

max_connections = 200
shared_buffers = 2GB
effective_cache_size = 6GB
maintenance_work_mem = 512MB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 4
effective_io_concurrency = 2
work_mem = 10485kB
min_wal_size = 1GB
max_wal_size = 2GB
max_worker_processes = 2
max_parallel_workers_per_gather = 1
max_parallel_workers = 2

Une application web, c’est beaucoup d’utilisateurs qui exécutent de petites requêtes simples, très rapides, non consommatrices. Du coup, le nombre de connexions a été doublé par rapport à sa valeur par défaut. Le paramètre work_mem est augmenté mais raisonnablement par rapport à la mémoire totale et au nombre de connexions. Le paramètre shared_buffers se trouve au quart de la mémoire, alors que le paramètre effective_cache_size est au deux tiers évoqué précédemment. Le paramètre wal_buffers est aussi augmenté, il arrive à 16 Mo. Il peut y avoir beaucoup de transactions en même temps, mais elles seront généralement peu coûteuses en écriture, d’où le fait que les paramètres min_wal_size, max_wal_size et checkpoint_completion_target sont augmentés mais là aussi très raisonnablement. La parallélisation est activée, sans excès.

Voyons maintenant avec un profil OLTP (OnLine Transaction Processing) :

max_connections = 300
shared_buffers = 2GB
effective_cache_size = 6GB
maintenance_work_mem = 512MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 4
effective_io_concurrency = 2
work_mem = 6990kB
min_wal_size = 2GB
max_wal_size = 4GB
max_worker_processes = 2
max_parallel_workers_per_gather = 1
max_parallel_workers = 2

Une application OLTP doit gérer un plus grand nombre d’utilisateurs. Ils font autant d’opérations de lecture que d’écriture. Tout cela est transcrit dans la configuration. Un grand nombre d’utilisateurs simultanés veut dire une valeur importante pour le paramètre max_connections (maintenant à 300). De ce fait, le paramètre work_mem ne peut plus avoir une valeur si importante. Sa valeur est donc baissée tout en restant fortement au-dessus de la valeur par défaut. Due au fait qu’il y aura plus d’écritures, la taille du cache des journaux de transactions (wal_buffers) est augmentée. Il faudra essayer de tout faire passer par les checkpoints, d’où la valeur maximale pour checkpoint_completion_target, et des valeurs encore augmentées pour min_wal_size et max_wal_size. Quant à shared_buffers et effective_cache_size, ils restent aux valeurs définies ci-dessus (respectivement un quart et deux tiers de la mémoire).

Et enfin avec un profil entrepôt de données (datawarehouse) sur une machine moins modeste avec 32 Go de RAM, 8 cœurs, et un disque SSD :

max_connections = 40
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 500
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 26214kB
min_wal_size = 4GB
max_wal_size = 8GB
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

Pour un entrepôt de données, il y a généralement peu d’utilisateurs à un instant t, mais qui exécutent des requêtes complexes sur une grosse volumétrie. Du coup, la configuration change en profondeur cette fois. Le paramètre max_connections est diminué très fortement. Cela permet d’allouer beaucoup de mémoire aux tris et hachages (paramètre work_mem à 26 Mo). shared_buffers et effective_cache_size suivent les règles habituelles. Les entrepôts de données ont souvent des scripts d’import de données (batchs) : cela nécessite de pouvoir écrire rapidement de grosses quantités de données, autrement dit une augmentation conséquente du paramètre wal_buffers et des min_wal_size/max_wal_size. Du fait de la grosse volumétrie des bases dans ce contexte, une valeur importante pour le maintenance_work_mem est essentielle pour que les créations d’index et les VACUUM se fassent rapidement. De même, la valeur du default_statistics_target est sérieusement augmentée car le nombre de lignes des tables est conséquent et nécessite un échantillon plus important pour avoir des statistiques précises sur les données des tables. random_page_cost, auparavant à sa valeur par défaut, descend à 1.1 pour tenir compte des performances d’un SSD. C’est le cas aussi pour effective_io_concurrency. Enfin, la configuration de la machine autorise une parallélisation importante, généralement bienvenue pour du décisionnel.

Évidemment, tout ceci n’est qu’une recommandation générale, et ne doit servir que de point de départ. Chaque paramètre peut être affiné. L’expérimentation permettra de se diriger vers une configuration plus personnalisée.


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

pgbench est un outil disponible avec les modules contrib de PostgreSQL depuis de nombreuses années. Son but est de faciliter la mise en place de benchmarks simples et rapides. Des solutions plus complètes sont disponibles, mais elles sont aussi bien plus complexes.

pgbench travaille soit à partir d’un schéma de base qu’il crée et alimente lui-même, soit à partir d’une base déjà existante. Dans ce dernier cas, les requêtes SQL à exécuter sont à fournir à pgbench.

Il existe donc principalement deux modes d’utilisation de pgbench : le mode initialisation quand on veut utiliser le schéma et le scénario par défaut, et le mode benchmarks.

pgbench est en fort développement ces derniers temps. La version 9.5 apporte de nombreuses nouvelles fonctionnalités pour cet outil.


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

La documentation complète est sur https://docs.postgresql.fr/current/pgbench.html. L’auteur principal, Fabien Coelho, a fait une présentation complète, en français, à la PG Session #9 de 2017.

L’option -i demande à pgbench de créer un schéma et de le peupler de données dans la base indiquée (à créer au préalable). La base ainsi créée est composée de 4 tables : pgbench_history, pgbench_tellers, pgbench_accounts et pgbench_branches. Dans ce mode, l’option -s permet alors d’indiquer un facteur d’échelle permettant de maîtriser la volumétrie de la base de donnée. Ce facteur est un multiple de 100 000 lignes dans la table pgbench_accounts. Pour que le test soit significatif, il est important que la taille de la base dépasse fortement la quantité de mémoire disponible.

Une fois créée, il est possible de réaliser différents tests avec cette base de données en faisant varier plusieurs paramètres tels que le nombre de transactions, le nombre de clients, le type de requêtes (simple, étendue, préparée) ou la durée du test de charge.

Quelques exemples. Le plus simple :

  • création de la base et peuplement par pgbench :
$ createdb benchs

$ pgbench -i -s 2 benchs
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
creating tables...
100000 of 200000 tuples (50%) done (elapsed 0.08 s, remaining 0.08 s)
200000 of 200000 tuples (100%) done (elapsed 0.26 s, remaining 0.00 s)
vacuum...
set primary keys...
done.
  • benchmarks sur cette base :
$ pgbench benchs
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 2
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
latency average = 2.732 ms
tps = 366.049857 (including connections establishing)
tps = 396.322853 (excluding connections establishing)
  • nouveau test avec 10 clients et 200 transactions pour chacun :
$ pgbench -c 10 -t 200 benchs
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 2
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 200
number of transactions actually processed: 2000/2000
latency average = 19.716 ms
tps = 507.204902 (including connections establishing)
tps = 507.425131 (excluding connections establishing)
  • changement de la configuration avec fsync=off, et nouveau test avec les mêmes options que précédemment :
$ pgbench -c 10 -t 200 benchs
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 2
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 200
number of transactions actually processed: 2000/2000
latency average = 2.361 ms
tps = 4234.926931 (including connections establishing)
tps = 4272.412154 (excluding connections establishing)
  • toujours avec les mêmes options, mais en effectuant le test durant 10 secondes :
$ pgbench -c 10 -T 10 benchs
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 2
query mode: simple
number of clients: 10
number of threads: 1
duration: 10 s
number of transactions actually processed: 45349
latency average = 2.207 ms
tps = 4531.835068 (including connections establishing)
tps = 4534.070449 (excluding connections establishing)

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

L’outil pgbench est capable de travailler avec une base de données existante. Cette fonctionnalité permet ainsi de tester les performances dans un contexte plus représentatif de la ou les bases présentes dans une instance.

Pour effectuer de tels tests, il faut créer un ou plusieurs scripts SQL contenant les requêtes à exécuter sur la base de donnée. Avant la 9.6, chaque requête doit être écrite sur UNE seule ligne, sinon le point-virgule ; habituel convient. Un script peut contenir plusieurs requêtes. Toutes les requêtes du fichier seront exécutées dans leur ordre d’apparition. Si plusieurs scripts SQL sont indiqués, chaque transaction sélectionne le fichier à exécuter de façon aléatoire. Enfin, il est possible d’utiliser des variables dans vos scripts SQL afin de faire varier le groupe de données manipulé dans vos tests. Ce dernier point est essentiel afin d’éviter les effets de cache ou encore pour simuler la charge lorsqu’un sous-ensemble des données de la base est utilisé en comparaison avec la totalité de la base (en utilisant un champ de date par exemple).

Par exemple, le script exécuté par défaut par pgbench pour son test TPC-B en mode requête « simple », sur sa propre base, est le suivant (extrait de la page de manuel de pgbench) :

\set aid random(1, 100000 * :scale)
\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)
BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
END;

Ici, la variable :scale a comme valeur celle indiquée lors de l’initialisation de la base de données.


Outil postgresqltuner.pl

Ci-dessous figure un exemple de sortie en v11 sur une machine virtuelle avec un SSD. Les conseils sont généralement pertinents. Il conseille notamment d’installer l’extension pg_stat_statements et d’attendre un certain temps avant d’utiliser l’outil.

-bash-4.2$ ./postgresqltuner.pl --ssd

postgresqltuner.pl version 1.0.1
Checking if OS commands are available on /var/run/postgresql...
[OK]      OS command OK
Connecting to /var/run/postgresql:5432 database template1 with user postgres...
[OK]      User used for report has superuser rights
=====  OS information  =====
[INFO]    OS: linux Version: 3.10.0-693.17.1.el7.x86_64
          Arch: x86_64-linux-thread-multi
[INFO]    OS total memory: 3.70 GB
[OK]      vm.overcommit_memory is good: no memory overcommitment
[INFO]    Running in kvm hypervisor
[INFO]    Currently used I/O scheduler(s): mq-deadline
=====  General instance informations  =====
-----  Version  -----
[OK]      You are using latest major 11.5
-----  Uptime  -----
[INFO]    Service uptime:  44s
[WARN]    Uptime is less than 1 day. postgresqltuner.pl result may not be accurate
-----  Databases  -----
[INFO]    Database count (except templates): 2
[INFO]    Database list (except templates): postgres postgis
-----  Extensions  -----
[INFO]    Number of activated extensions: 1
[INFO]    Activated extensions: plpgsql
[WARN]    Extensions pg_stat_statements is disabled in database template1
-----  Users  -----
[OK]      No user account will expire in less than 7 days
[OK]      No user with password=username
[OK]      Password encryption is enabled
-----  Connection information  -----
[INFO]    max_connections: 100
[INFO]    current used connections: 6 (6.00%)
[INFO]    3 connections are reserved for super user (3.00%)
[INFO]    Average connection age:  36s
[BAD]     Average connection age is less than 1 minute.
          Use a connection pooler to limit new connection/seconds
-----  Memory usage  -----
[INFO]    configured work_mem: 4.00 MB
[INFO]    Using an average ratio of work_mem buffers by connection of 150%
          (use --wmp to change it)
[INFO]    total work_mem (per connection): 6.00 MB
[INFO]    shared_buffers: 128.00 MB
[INFO]    Track activity reserved size: 0.00 B
[WARN]    maintenance_work_mem is less or equal default value.
          Increase it to reduce maintenance tasks time
[INFO]    Max memory usage:
                  shared_buffers (128.00 MB)
                + max_connections * work_mem *
                  average_work_mem_buffers_per_connection 
                  (100 * 4.00 MB * 150 / 100 = 600.00 MB)
                + autovacuum_max_workers * maintenance_work_mem 
                  (3 * 64.00 MB = 192.00 MB)
                + track activity size (0.00 B)
                = 920.00 MB
[INFO]    effective_cache_size: 4.00 GB
[INFO]    Size of all databases: 29.55 MB
[WARN]    shared_buffer is too big for the total databases size, memory is lost
[INFO]    PostgreSQL maximum memory usage: 24.28% of system RAM
[WARN]    Max possible memory usage for PostgreSQL is less than 60% of 
          system total RAM. On a dedicated host you can increase PostgreSQL
          buffers to optimize performances.
[INFO]    max memory+effective_cache_size is 132.37% of total RAM
[WARN]    the sum of max_memory and effective_cache_size is too high,
          the planner can find bad plans if system cache is smaller than expected
-----  Huge pages  -----
[BAD]     No Huge Pages available on the system
[BAD]     huge_pages disabled in PostgreSQL
[INFO]    Hugepagesize is 2048 kB
[INFO]    HugePages_Total 0 pages
[INFO]    HugePages_Free 0 pages
[INFO]    Suggested number of Huge Pages: 195
          (Consumption peak: 398868 / Huge Page size: 2048)
-----  Logs  -----
[OK]      log_hostname is off: no reverse DNS lookup latency
[WARN]    log of long queries is deactivated. It will be more
          difficult to optimize query performances
[OK]      log_statement=none
-----  Two phase commit  -----
[OK]      Currently no two phase commit transactions
-----  Autovacuum  -----
[OK]      autovacuum is activated.
[INFO]    autovacuum_max_workers: 3
-----  Checkpoint  -----
[WARN]    checkpoint_completion_target(0.5) is low
-----  Disk access  -----
[OK]      fsync is on
[OK]      synchronize_seqscans is on
-----  WAL  -----
-----  Planner  -----
[OK]      cost settings are defaults
[WARN]    With SSD storage, set random_page_cost=seq_page_cost
          to help planner use more index scan
[BAD]     some plan features are disabled: enable_partitionwise_aggregate,
          enable_partitionwise_join
=====  Database information for database template1  =====
-----  Database size  -----
[INFO]    Database template1 total size: 7.88 MB
[INFO]    Database template1 tables size: 4.85 MB (61.55%)
[INFO]    Database template1 indexes size: 3.03 MB (38