Dalibo SCOP
Formation | Module J1 |
Titre | Configuration du système et de l’instance |
Révision | 24.09 |
https://dali.bo/j1_pdf | |
EPUB | https://dali.bo/j1_epub |
HTML | https://dali.bo/j1_html |
Slides | https://dali.bo/j1_slides |
TP | https://dali.bo/j1_tp |
TP (solutions) | https://dali.bo/j1_solutions |
Vous trouverez en ligne les différentes versions complètes de ce document.
Cette formation est sous licence CC-BY-NC-SA. Vous êtes libre de la redistribuer et/ou modifier aux conditions suivantes :
Vous n’avez pas le droit d’utiliser cette création à des fins commerciales.
Si vous modifiez, transformez ou adaptez cette création, vous n’avez le droit de distribuer la création qui en résulte que sous un contrat identique à celui-ci.
Vous devez citer le nom de l’auteur original de la manière indiquée par l’auteur de l’œuvre ou le titulaire des droits qui vous confère cette autorisation (mais pas d’une manière qui suggérerait qu’ils vous soutiennent ou approuvent votre utilisation de l’œuvre). À chaque réutilisation ou distribution de cette création, vous devez faire apparaître clairement au public les conditions contractuelles de sa mise à disposition. La meilleure manière de les indiquer est un lien vers cette page web. Chacune de ces conditions peut être levée si vous obtenez l’autorisation du titulaire des droits sur cette œuvre. Rien dans ce contrat ne diminue ou ne restreint le droit moral de l’auteur ou des auteurs.
Le texte complet de la licence est disponible sur http://creativecommons.org/licenses/by-nc-sa/2.0/fr/legalcode
Cela inclut les diapositives, les manuels eux-mêmes et les travaux pratiques. Cette formation peut également contenir quelques images et schémas dont la redistribution est soumise à des licences différentes qui sont alors précisées.
PostgreSQL® Postgres® et le logo Slonik sont des marques déposées par PostgreSQL Community Association of Canada.
Ce document ne couvre que les versions supportées de PostgreSQL au moment de sa rédaction, soit les versions 12 à 16.
Sur les versions précédentes susceptibles d’être encore rencontrées en production, seuls quelques points très importants sont évoqués, en plus éventuellement de quelques éléments historiques.
Sauf précision contraire, le système d’exploitation utilisé est Linux.
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 !
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.
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… mais faire perdre encore plus sur les autres. Là aussi, tout travail d’optimisation doit être fait prudemment et ses effets surveillés sur une période représentative pour s’assurer que cette amélioration ne s’accompagne pas de quelques gros inconvénients.
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 :
PostgreSQL est un système multiprocessus. 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.
Ces processus ne sont pas multithreadés. Par conséquent, chaque requête exécutée est généralement traitée par un seul processus, sur un cœur de processeur. Mais dans certains cas, d’autres processus peuvent intervenir sur la même requête pour utiliser d’autres cœurs. Lorsque la requête est en lecture seule (et dans quelques rares cas en écriture) et que la parallélisation est activée, ce processus peut être aidé le temps de l’exécution de certains nœuds par un ou plusieurs processus appelés workers. Les détails figurent plus loin.
Parallélisation mise à part, plus vous voulez pouvoir exécuter de requêtes en simultané, plus vous devez avoir de processeurs (ou plus exactement de cœurs). On considère habituellement qu’un cœur peut traiter de 1 à 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’une instance pour une application web, il y a de fortes chances que le nombre de requêtes (simples) en parallèle soit assez élevé. Dans ce contexte, il faut prévoir un grand nombre de cœurs ou de processeurs. Par contre, sur un entrepôt de données, il y a généralement peu d’utilisateurs, mais des requêtes complexes et gourmandes en ressources, sur de gros jeux de données, mais ces requêtes sont, à priori, facilement parallélisables. Il est alors possible d’opter pour des processeurs avec une fréquence plus élevée (qui ont souvent moins de cœurs), mais plus le système aura de cœurs, plus sa capacité à pouvoir paralléliser les requêtes qui s’y prêtent sera élevé. Ainsi, la fréquence (et donc la puissance) des processeurs est un point important à considérer. Il peut faire la différence pour des requêtes 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.
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 :
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 multicœ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.
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.
Technologie | Temps d’accès | Débit en lecture |
---|---|---|
RAM | ~ 1 ns | ~ 5 Go/s |
NVMe | ~ 100 µs | ~ 3 Go/s |
SSD (SATA) | ~ 100 µs | ~ 300 Mo/s |
HDD SAS 15ktpm | ~ 1 ms | ~ 100 Mo/s |
HDD SATA | ~ 5 ms | ~ 100 Mo/s |
Les chiffres ci-dessus ne sont que des ordres de grandeurs : la technologie évolue constamment.
Il existe actuellement quatre types de modèles de disques :
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 10 000 en performance de temps d’accès entre les deux ! À l’autre bout de l’échelle se trouvent les disques magnétiques avec interface SATA. Leur faible performance en temps d’accès ne doit pas pour autant les disqualifier : leur prix est 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 (Serial Attached 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 à des endroits différents 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 des disques SSD (et encore plus NVMe) est un temps d’accès très rapide. Ils se démarquent des disques magnétiques (SAS comme 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.
Il y a quelques années, leur durée de vie était courte par rapport aux disques magnétiques dus essentiellement à la notion de TBW (Terabytes written), soit la quantité pouvant être écrite sur le SSD au cours de sa vie, puisque chaque zone mémoire du disque a un nombre maximal de cycles d’écriture ou d’effacement.
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.
Tous les disques ne se valent pas, il y a des gammes pour « grand public » et des gammes « entreprise ». Choisissez toujours des disques de la gamme entreprise qui ont une meilleure durabilité et fournissent des fonctionnalités bien supérieures aux disques non professionnels.
Sur le marché du SSD, il existe plusieurs technologies (eMLC, iSLC, QLC…), certains auront de meilleures performances en lecture, d’autres en écriture, d’autre encore une meilleure durée de vie en écriture. Il est donc important de bien lire la documentation technique des disques avant leur achat.
Certains disques SSD haut de gamme ont une interface en SAS 12 Gbit/s, permettant d’atteindre des débits en lecture très élevés (de l’ordre de 1,5 Go/s) mais leur prix limite leur utilisation (de l’ordre de 10 000 € pour un disque de 3,8 To, fin 2020). À titre de comparaison, l’interface SATA troisième génération a un débit théorique de 6 Gbits/s, soit environ 750 Mo/s. Ce qui est donc deux fois moins rapide, même avec un SSD.
Il existe aussi des supports de stockage moins courants, encore onéreux, mais extrêmement rapides : ce sont les cartes basées sur la technologie NVMe (comme par exemple ceux commercialisés par 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. On évoque des temps d’accès environ dix fois inférieurs et des débits presque dix fois supérieur à ce que l’on peut avoir sur une interface SATA standard. Leur utilisation reste cependant encore limitée en raison du coût de cette technologie.
Les disques bas de gamme mais rapides peuvent néanmoins 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 système d’exploitation pour optimiser l’utilisation des SSD. Par exemple sous Linux, les deux optimisations courantes dans le noyau sont :
# echo noop > /sys/block/<device>/queue/scheduler # echo 0 > /sys/block/<device>/queue/rotational
Il existe différents niveaux de RAID. Le plus connu est le RAID 5, qui autorise de perdre un des disques sans interrompre le service, au prix d’une perte de capacité plus faible que le RAID 1 (disques redondants en miroir). Cependant, le RAID 5 est plutôt 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.
Pour les performances en écriture, il est généralement préférable de se baser sur du RAID 10, soit deux grappes de disques en RAID 1 (en miroir) agrégé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. Pour réduire le budget, il peut être envisageable de choisir des disques SATA en RAID 10. Cela dit, un RAID 5 peut très bien fonctionner pour votre application. Le plus important est d’obtenir un RAID fiable avec de nombreux disques, surtout s’ils sont magnétiques.
Lors du choix du RAID, il est impératif de suivre les recommandations du constructeur par rapport à la compatibilité de la taille des disques et aux performances des différents modes de RAID. En effet, certains constructeurs déconseillent tel ou tel niveau de RAID par rapport à la capacité des disques ; par exemple, suivant l’algorithme implémenté, certains constructeurs conseilleront un RAID 6 plutôt qu’un RAID 5 si la capacité du disque dépasse une certaine taille. Ceci est justifié par exemple par une reconstruction plus rapide.
Lors de l’utilisation d’un RAID, il est important de prévoir un disque de hot spare. Il permet au système de reconstruire le RAID automatiquement et sans intervention humaine. Cela réduit la période pendant laquelle la grappe RAID est dans un mode dégradé.
Il est à noter que le système et les journaux de transactions n’ont pas besoin de RAID 10. Il y a peu de lectures, ils peuvent 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. En lecture, il faut évidemment toujours l’activer.
Par contre, la carte RAID doit posséder une batterie (ou équivalent) pour utiliser le cache en écriture : les données du cache ne doivent pas disparaître en cas de coupure de courant. Ceci est obligatoire pour des raisons de fiabilité du service. La majorité des 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 défaillante.
Pensez donc à toujours superviser l’état de vos contrôleurs RAID et de vos disques.
Le RAID soft, intégré à l’OS, qui gère directement les disques, a l’avantage d’un coût nul. Il est cependant déconseillé sur un serveur de production : les performances peuvent souffrir du partage du CPU avec les applications, surtout en RAID 5 ; une reconstruction d’un disque passe par le CPU et ralentit énormément la machine ; et surtout la fiabilité est impactée par l’impossibilité de rajouter une batterie.
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 bien 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.
Le débit n’est pas tout !
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 ! L’architecture et les équipements choisis doivent donc prévoir de multiples chemins entre serveur et baie, pour mener à une latence la plus faible possible, surtout pour la partition des journaux de transaction.
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 (snapshots, réplication, virtualisation…), en performances et en souplesse.
Dans un registre moins coûteux, la tentation est grande d’utiliser un simple 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, peu performant, mais plus fiable et moins complexe.
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.
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 socket dispose de sa propre mémoire. Par exemple, avec un système à 2 sockets et 128 Go de RAM, chaque socket ou nœud possède 64 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
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.
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é. La liste des plate-formes officiellement supportées ) comprend donc Linux, FreeBSD, OpenBSD, macOS, Windows, Solaris, etc. Cette portabilité est vérifiée en permanence avec la ferme de construction (BuildFarm), qui comprend même de vieilles versions de ces systèmes sur différentes architectures avec plusieurs compilateurs.
Cela étant dit, il est malgré tout principalement développé sous Linux et la majorité des utilisateurs, et surtout des développeurs, 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, Rocky Linux…) et Debian (et ses dérivés, notamment Ubuntu). Sauf exception, nous ne traiterons plus ici que de Linux.
Un autre système souvent utilisé est Windows. PostgreSQL est beaucoup moins performant lorsqu’il est installé sur ce dernier que sur 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.
Un autre problème connu avec les instances PostgreSQL sous Windows est lié à l’architecture multiprocessus, où chaque connexion à l’instance crée un processus. Avant Windows 2016, plus de 125 connexions simultanées peuvent mener à l’épuisement de la Desktop Heap Memory, réduite pour les services non interactifs, et à de surprenants problèmes de mémoire (message Out of memory dans les traces PostgreSQL et/ou les événements de Windows). Pour les détails, voir la KB295902 de Microsoft, cet article Technet et le wiki PostgreSQL.
Toujours sous Windows, il est fortement recommandé de laisser le
paramètre update_process_title
à off
(c’est le
défaut sous Windows, pas sous Linux). Le nom des processus ne sera plus
dynamique, mais cela est trop lourd sous Windows. Le wiki ci-dessus
pointe d’autres particularités et problèmes.
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.
Le noyau, comme tout logiciel, est configurable. Certaines configurations sont particulièrement importantes pour PostgreSQL.
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 noyau 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à.
Sur les versions de PostgreSQL précédant la 9.6, pour réduire les
conséquences de ce phénomène, il était systématiquement conseillé
d’abaisser vm.dirty_ratio
à 10 et
vm.dirty_background_ratio
à 5. Ainsi, lors de fortes
charges en écriture, le noyau reporte plus fréquemment son cache disque
sur l’espace de stockage, mais pour une volumétrie plus faible, et
l’encombrement de la bande passante vers les disques est 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.
Avec 32 Go de RAM, cela donne encore 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ésente une
volumétrie trop importante (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 sont
fréquentes, plus la durée des opérations VACUUM
et
REINDEX
, qui déclenchent beaucoup d’écritures sur disque,
augmente.
Depuis la version 9.6, ces options sont moins nécessaires grâce à ces paramètres propres à 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. Mais ces derniers paramètres ne concernent que les processus de
PostgreSQL. Or PostgreSQL n’est pas seul à écrire de gros fichiers
(exports pg_dump
, processus d’archivage, copies de
fichiers…). Le paramétrage des vm.dirty_bytes
conserve donc
un intérêt.
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.
Il est utile d’en conserver un peu pour swapper des processus
inactifs, ou le contenu de systèmes de fichiers tmpfs
(classiquement, /var/run
) et les journaux de
systemd-journald
selon la configuration de celui-ci. Ne pas
avoir de swap amène encore 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 au plus 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 (30 ou 60 suivant les distributions) doit donc être
abaissée à 10 pour éviter l’utilisation du swap dans la
majorité des cas.
Danger de l’overcommit :
Certaines applications réservent (commit) auprès du noyau 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 à réserver 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. Ce peut être intéressant dans certains cas d’utilisation, mais peut devenir dangereux dans le cadre d’un serveur PostgreSQL dédié, qui va réellement allouer (utiliser) toute la mémoire qu’il réservera. Typiquement, cela arrive lors de tris en mémoire trop gros, ou trop nombreux au même moment.
Quand le noyau arrive réellement à court de mémoire, il décide de tuer certains processus en fonction de leur impact sur le système (mécanisme de l’Out Of Memory Killer). Il est alors fort probable que ce soit un processus PostgreSQL qui soit tué. Il y a un risque de corruption de la mémoire partagée, donc par précaution toutes les transactions en cours sont annulées, et toute l’instance redémarre. Une perte de données est parfois possible en fonction de la configuration de PostgreSQL. Une corruption est par contre plutôt exclue.
De plus, le cache disque aura été purgé à cause de la consommation mémoire. Pire : le swap aura pu être rempli, entraînant un ralentissement général (swap storm) avant le redémarrage de l’instance.
Configuration de vm.overcommit_memory et vm.overcommit_ratio :
Il est possible de parer à ces problèmes grâce aux paramètres kernel
vm.overcommit_memory
et vm.overcommit_ratio
du
fichier /etc/sysctl.conf
(ou d’un fichier
dans/etc/sysctl.conf.d/
). Cela suppose que le serveur est
dédié exclusivement à PostgreSQL, car d’autres applications ont besoin
d’un overcommit laxiste. Le swap devra avoir été découragé
comme évoqué ci-dessus.
Pour désactiver complètement l’overcommit memory :
vm.overcommit_memory = 2
La taille maximum de mémoire réservable par les applications se calcule alors grâce à la formule suivante :
CommitLimit = (RAM * vm.overcommit_ratio / 100) + SWAP
Ce CommitLimit
ne doit pas dépasser 80 % de la RAM
physiquement présente pour en préserver 20 % pour l’OS et son cache.
vm.overcommit_ratio
est un pourcentage, entre 0 et 100.
Or, sa valeur par défaut vm.overcommit_ratio
est 50 : sur
un système avec 32 Go de mémoire et 2 Go de swap, nous
n’aurions alors que 32×50/100+2 = 18 Go de mémoire allouable ! Il faut
donc monter cette valeur :
vm.overcommit_ratio = 75
nous obtenons 32×75/100+2 = 26 Go de mémoire utilisable par les applications sur les 32 Go disponibles. 6 Go serviront pour le cache et l’OS (bien sûr, ce pourra être plus quand les processus PostgreSQL utiliseront moins de mémoire).
Les valeurs typiques de vm.overcommit_ratio
, sur des
machines correctement dotées en RAM, et avec un swap de 2 Go au
plus, vont de 70 à 80 (toujours en vue de réserver 20 % de RAM au cache
disque, ce pourrait être un peu moins).
(Alternativement, il existe un paramètre exprimé en kilooctets,
vm.overcommit_kbytes
, mais il faut penser à l’adapter lors
d’un ajout de RAM).
La prise en compte des fichiers de configuration modifiés se fait avec :
$ sudo sysctl --system
Exemple :
Une machine de 16 Go de RAM, 1,6 Go de swap possède cette configuration :
$ sysctl -a --pattern 'vm.overcommit.*'
vm.overcommit_memory = 2
vm.overcommit_ratio = 85
Extrait de la configuration mémoire résultante :
$ free -m
total used free shared buffers cached
Mem: 16087 15914 173 0 65 13194
-/+ buffers/cache: 2653 13433
Swap: 1699 0 1699
dalibo@srv-psql-02:~$ cat /proc/meminfo
MemTotal: 16473548 kB
MemFree: 178432 kB
Buffers: 67260 kB
…
SwapTotal: 1740796 kB
SwapFree: 1740696 kB
…
CommitLimit: 15743308 kB
Committed_AS: 6436004 kB …
Le CommitLimit
atteint 15 Go, laissant au cache et l’OS
une portion très réduite, mais évitant au moins un crash de l’instance.
Ici, Committed_AS
(valeur totale réservée à ce moment), est
très en deça.
Avec des huge pages :
Les choses se compliquent si l’on paramètre des huge pages (voir plus bas).
Exemple de saturation mémoire :
Avec la désactivation de la sur-allocation, l’instance ne plantera plus par défaut de mémoire. Les requêtes demandant trop de mémoire se verront refuser par le noyau une nouvelle réservation, et elles tomberont simplement en erreur. Cela peut se tester ainsi :
SET work_mem = '1000GB' ;
-- DANGEREUX ! Tri de 250 Go en RAM !
EXPLAIN (ANALYZE) SELECT i FROM generate_series (1,3e9) i
ORDER BY i DESC ;
Si le paramétrage ci-dessus a été appliqué, on obtiendra ceci dans la session :
postgres=# EXPLAIN (ANALYZE) SELECT i FROM generate_series (1,3e9) i
postgres-# ORDER BY i DESC ;
ERROR: out of memory DETAIL: Failed on request of size 23 in memory context "ExecutorState".
(les traces seront plus verbeuses). La session et l’instance fonctionnent ensuite normalement.
Sans paramétrage, ce serait plus brutal :
server closed the connection unexpectedly
This probably means the server terminated abnormally before or while processing the request.
et les traces indiqueraient ceci avant le redémarrage :
LOG: server process (PID 2429) was terminated by signal 9: Killed
Noter qu’une requête qui tombe en erreur n’est pas forcément celle
qui a consommé le plus de mémoire ; elle est juste celle qui a atteint
la première le CommitLimit
.
Pour plus de détails :
Principe des huge pages :
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 contigües, il est plus économique d’utiliser des tailles de pages plus élevées. Par exemple, 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.
Or, chaque processus PostgreSQL dispose de sa propre table de
pagination, qui va gonfler au fur et à mesure que ce processus va
accéder à différents blocs des shared buffers : pour des
shared buffers de 8 Go, chaque processus peut gaspiller 16 Mo
si les pages font 4 ko, contre une centaine de ko pour des pages de
2 Mo. Une ligne de /proc/meminfo
indique la mémoire
utilisée par les TLB :
PageTables: 1193040 kB
Sur des petites configurations (quelques Go de RAM et peu de
connexions), cela n’a pas beaucoup d’importance. Sinon, cette mémoire
pourrait être utilisée à meilleur escient (work_mem
par
exemple, ou tout simplement du cache système).
Paramétrer PostgreSQL pour les huge pages :
Dans postgresql.conf
, le défaut convient :
huge_pages = try
PostgreSQL se rabattra sur des pages de 4 ko si le système n’arrive
pas à fournir les pages de 2 Mo. La valeur on
permet de
refuser le démarrage si les huge pages demandées ne sont pas
disponibles (mauvais paramétrage, fragmentation mémoire…).
À partir de la version 14, il est possible de surcharger la
configuration système de la taille des huge pages avec le
paramètre huge_page_size
. Par défaut, PostgreSQL utilisera
la valeur du système d’exploitation.
Paramétrer les huge pages au niveau noyau :
On se limitera ici aux huge pages les plus courantes, celles
de 2 Mo (à vérifier sur la ligne Hugepagesize
de/proc/meminfo
).
Dans /etc/sysctl.d/
, définir le nombre de huge
pages vm.nr_overcommit_hugepages
: La valeur de ce
paramètre est en pages de la taille de huge page par défaut. Il
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). Une alternative est le paramètre
vm.nr_hugepages
qui crée des pages statiques (et dont
l’utilisation serait obligatoire pour des pages de 1 Go).
En conséquence, si shared_buffers
vaut 8 Go :
# HP dynamiques
vm.nr_overcommit_hugepages=4505 # 8192 / 2 * 1.10
# HP statiques
vm.nr_hugepages=0
NB : Sur un système hébergeant plusieurs instances, il faudra additionner toutes les zones mémoire de toutes les instances.
Un outil pratique pour gérer les huge pages est
hugeadm
.
Si l’on a paramétré la sur-allocation mémoire comme décrit ci-dessus,
le calcul change, car les huge pages n’entrent pas dans la
CommitLimit
. On a alors :
CommitLimit = ( taille RAM - HugePages_Total*Hugepagesize ) * overcommit_ratio/100 + taille swap
Les valeurs de vm.overcommit_ratio
sont alors
typiquement entre 60 et 72 (pour préserver 20% du cache, si le cache est
réduit).
Pour plus de détails : https://kb.dalibo.com/huge_pages
Désactivation des Transparent Huge Pages
Dans /proc/meminfo
, la ligne AnonHugePages
indique des huge pages allouées par le mécanisme de
Transparent Huge Pages: le noyau Linux a détecté une allocation
contiguë de mémoire et l’a convertie en huge pages,
indépendamment du mécanisme décrit plus haut. Hélas, les THP ne
s’appliquent pas à la mémoire partagée de PostgreSQL.
Les THP sont même contre-productives sur une base de données, à cause
de la latence engendrée par la réorganisation par le système
d’exploitation. Comme les THP sont activées par défaut, il faut les
désactiver au boot via /etc/crontab
:
@reboot root echo never > /sys/kernel/mm/transparent_hugepage/enabled
@reboot root echo never > /sys/kernel/mm/transparent_hugepage/defrag
ou encore dans la configuration de grub
:
transparent_hugepage=never
Dans /proc/meminfo
, la ligne AnonHugePages
doit donc valoir 0.
Attention, ne pas confondre multicœur et multisocket ! 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
on l’a vu, 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 multisockets, 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 :
zone_reclaim_mode
à 1, durée : 20 h, CPU utilisé
par le COPY
: 3 à 5 %zone_reclaim_mode
à 0, durée : 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 multiprocesseur pour équilibrer la charge entre les
différents processeurs.
Pour plus de détails, voir :
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 en la
passant de 0,5 à 5 ms (5 000 000 ns). L’option disparaît cependant du
noyau Linux 5.13 et suivants (donc de Rocky Linux 9 par exemple) et est
remplacée par /sys/kernel/debug/sched/migration_cost_ns
.
Par ailleurs, 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.
Tous les paramètres expliqués ci-dessus sont à placer dans le fichier
/etc/sysctl.conf
ou dans le répertoire
/etc/sysctl.d/
(où tout fichier ayant l’extension
.conf
est lu et pris en compte). 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. À chaque redémarrage du serveur, Linux
va récupérer le paramétrage et l’appliquer.
Il est possible d’appliquer vos modifications sans redémarrer tout le système grâce à la commande suivante :
# sysctl --system
de consulter les valeurs avec :
# sysctl -a
et de modifier un paramètre précis (jusqu’au prochain redémarrage) :
# sysctl -w vm.swappiness=10
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, qui sont donc les systèmes de fichiers recommandés.
En 2016, un benchmark sur Linux de Tomas Vondra de différents systèmes de fichiers montrait que ext4 et XFS ont des performantes équivalentes.
Autrefois réservé à 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). OpenZFS, son portage sous Linux/FreeBSD, entre autres, est un système de fichiers proposant un panel impressionnant de fonctionnalités (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 relativement répandu et bien intégré à Linux, et offre une partie des fonctionnalités de ZFS ; mais il est également peu performant avec PostgreSQL.
LVM permet de rassembler plusieurs partitions dans un même Volume Group, puis d’y tailler des partitions (Logical Volumes) qui seront autant de points de montage. LVM permet de changer les tailles des LV à volonté, d’ajouter ou supprimer des disques physiques à volonté dans les VG, ce qui simplifie l’administration au niveau PostgreSQL… De nos jours, l’impact en performance est négligeable pour la flexibilité apportée. Si l’on utilise les snapshots de LVM, il faudra vérifier l’impact sur les performances. LVM peut même gérer le RAID mais, dans l’idéal, il est préférable qu’une bonne carte RAID s’en charge en dessous.
NFS peut sembler intéressant, vu ses fonctionnalités : facilité de
mise en œuvre, 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 ceux de fiabilité fréquents, et compliqués à diagnostiquer (comme illustré dans ce mail, où le problème venait du noyau Linux).
Sous Windows, la question ne se pose pas : NTFS est le seul système de fichiers assez stable. L’installeur fourni par EnterpriseDB dispose d’une protection qui empêche l’installation d’une instance PostgreSQL sur une partition VFAT.
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
:
sudo 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 :
sudo tune2fs -O dir_index /dev/sda1
Enfin, il reste à créer ces index à l’aide de la commande
e2fsck
:
sudo 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.
Nous déconseillons fortement d’installer un antivirus sur un serveur PostgreSQL.
Si vous devez absolument installer un antivirus, il faut impérativement exclure de son analyse tous les répertoires, fichiers et processus de PostgreSQL.
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 :
postgresql.conf
) ;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 :
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.SELECT FOR UPDATE
.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.
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
:
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.
Ces quatre paramètres concernent tous la quantité de mémoire que PostgreSQL utilisera pour ses différentes opérations.
shared_buffers :
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 en mémoire partagée, et donc commun à tous les processus PostgreSQL. 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.
Pour dimensionner shared_buffers
sur un serveur dédié à
PostgreSQL, la documentation
officielle donne 25 % de la mémoire vive totale comme un bon point
de départ, et déconseille de dépasser 40 %, car le cache du système
d’exploitation est aussi utilisé.
Sur une machine dédiée de 32 Go de RAM, cela donne donc :
shared_buffers = 8GB
Le défaut de 128 Mo n’est donc pas adapté à un serveur sur une machine récente.
Suivant les cas, une valeur inférieure ou supérieure à 25 % sera encore meilleure pour les performances, mais il faudra tester avec votre charge (en lecture, en écriture, et avec le bon nombre de clients).
Le cache système limite la plupart du temps l’impact d’un mauvais
paramétrage de shared_buffers
, et il est moins grave de
sous-dimensionner un peu shared_buffers
que de le
sur-dimensionner.
Attention : une valeur élevée de shared_buffers
(au-delà
de 8 Go) nécessite de paramétrer finement le système d’exploitation
(Huge Pages notamment) et d’autres paramètres liés aux journaux
et checkpoints comme max_wal_size
. Il faut aussi
s’assurer qu’il restera de la mémoire pour le reste des opérations
(tri…) et donc adapter work_mem
.
Modifier shared_buffers
impose de redémarrer
l’instance.
wal_buffers :
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 multiprocesseur 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 16 Mo (la
taille par défaut d’un segment du journal de transaction).
Les processus de PostgreSQL ont accès à la mémoire partagée, définie
principalement par shared_buffers
, mais ils ont aussi leur
mémoire propre. Cette mémoire n’est utilisable que par le processus
l’ayant allouée.
work_mem
, qui
définit la taille maximale de la mémoire de travail d’un
ORDER BY
, de certaines jointures, pour la déduplication…
que peut utiliser un processus sur un nœud de requête, principalement
lors d’opérations de tri ou regroupement.maintenance_work_mem
qui est
la mémoire utilisable pour les opérations de maintenance lourdes :
VACUUM
, CREATE INDEX
, REINDEX
,
ajouts de clé étrangère…Cette mémoire liée au processus est rendue immédiatement après la fin de l’ordre concerné.
logical_decoding_work_mem
(défaut :
64 Mo), utilisable pour chacun des flux de réplication logique (s’il y
en a, ils sont rarement nombreux). Opérations de maintenance & maintenance_work_mem :
maintenance_work_mem
peut être monté à 256 Mo à 1 Go sur
les machines récentes, car il concerne des opérations lourdes. Leurs
consommations de RAM s’additionnent, mais en pratique ces opérations
sont rarement exécutées plusieurs fois simultanément.
Monter au-delà de 1 Go n’a d’intérêt que pour la création ou la réindexation de très gros index.
Paramétrage de work_mem :
Pour work_mem
, c’est beaucoup plus compliqué.
Si work_mem
est trop bas, beaucoup d’opérations de tri,
y compris nombre de jointures, ne s’effectueront pas en RAM. Par
exemple, si une jointure par hachage impose d’utiliser 100 Mo en
mémoire, mais que work_mem
vaut 10 Mo, PostgreSQL écrira
des dizaines de Mo sur disque à chaque appel de la jointure. Si, par
contre, le paramètre work_mem
vaut 120 Mo, aucune écriture
n’aura lieu sur disque, ce qui accélérera forcément la requête.
Trop de fichiers temporaires peuvent ralentir les opérations, voire
saturer le disque. Un work_mem
trop bas peut aussi
contraindre le planificateur à choisir des plans d’exécution moins
optimaux.
Par contre, si work_mem
est trop haut, et que trop de
requêtes le consomment simultanément, le danger est de saturer la RAM.
Il n’existe en effet pas de limite à la consommation des sessions de
PostgreSQL, ni globalement ni par session !
Or le paramétrage de l’overcommit sous Linux est par défaut très permissif, le noyau ne bloquera rien. La première conséquence de la saturation de mémoire est l’assèchement du cache système (complémentaire de celui de PostgreSQL), et la dégradation des performances. Puis le système va se mettre à swapper, avec à la clé un ralentissement général et durable. Enfin le noyau, à court de mémoire, peut être amené à tuer un processus de PostgreSQL. Cela mène à l’arrêt de l’instance, ou plus fréquemment à son redémarrage brutal avec coupure de toutes les connexions et requêtes en cours.
Toutefois, si l’administrateur paramètre correctement l’overcommit, Linux refusera d’allouer la RAM et la requête tombera en erreur, mais le cache système sera préservé, et PostgreSQL ne tombera pas.
Suivant la complexité des requêtes, il est possible qu’un processus
utilise plusieurs fois work_mem
(par exemple si une requête
fait une jointure et un tri, ou qu’un nœud est parallélisé). À
l’inverse, beaucoup de requêtes ne nécessitent aucune mémoire de
travail.
La valeur de work_mem
dépend donc beaucoup de la mémoire
disponible, des requêtes et du nombre de connexions actives.
Si le nombre de requêtes simultanées est important,
work_mem
devra être faible. Avec peu de requêtes
simultanées, work_mem
pourra être augmenté sans risque.
Il n’y a pas de formule de calcul miracle. Une première estimation courante, bien que très conservatrice, peut être :
work_mem
= mémoire /max_connections
On obtient alors, sur un serveur dédié avec 16 Go de RAM et 200 connexions autorisées :
work_mem = 80MB
Mais max_connections
est fréquemment surdimensionné, et
beaucoup de sessions sont inactives. work_mem
est alors
sous-dimensionné.
Plus finement, Christophe Pettus propose en première intention :
work_mem
= 4 × mémoire libre /max_connections
Soit, pour une machine dédiée avec 16 Go de RAM, donc 4 Go de shared buffers, et 200 connections :
work_mem = 240MB
Dans l’idéal, si l’on a le temps pour une étude, on montera
work_mem
jusqu’à voir disparaître l’essentiel des fichiers
temporaires dans les traces, tout en restant loin de saturer la RAM lors
des pics de charge.
En pratique, le défaut de 4 Mo est très conservateur, souvent insuffisant. Généralement, la valeur varie entre 10 et 100 Mo. Au-delà de 100 Mo, il y a souvent un problème ailleurs : des tris sur de trop gros volumes de données, une mémoire insuffisante, un manque d’index (utilisés pour les tris), etc. Des valeurs vraiment grandes ne sont valables que sur des systèmes d’infocentre.
Augmenter globalement la valeur du work_mem
peut parfois
mener à une consommation excessive de mémoire. Il est possible de ne la
modifier que le temps d’une session pour les besoins d’une requête ou
d’un traitement particulier :
SET work_mem TO '30MB' ;
hash_mem_multiplier :
À partir de PostgreSQL 13, un paramètre multiplicateur peut
s’appliquer à certaines opérations particulières (le hachage, lors de
jointures ou agrégations). Nommé hash_mem_multiplier
, il
vaut 1 par défaut en versions 13 et 14, et 2 à partir de la 15.
hash_mem_multiplier
permet de donner plus de RAM à ces
opérations sans augmenter globalement work_mem
.
Ajoutons qu’avant PostgreSQL 13, il y a parfois des problèmes dans
les calculs d’agrégats : lorsque l’optimiseur sélectionne les nœuds
d’exécution, il estime la mémoire à utiliser par la table de hachage Si
l’estimation est supérieure à work_mem
, il choisira plutôt
un agrégat par tri. Si elle est inférieure, il passera par un agrégat
par hachage. Il peut arriver que l’estimation soit mauvaise, et qu’il
faille plus de mémoire : l’exécuteur continuera d’en allouer au-delà de
work_mem
. Selon la quantité et le paramétrage du serveur,
cela peut passer inaperçu, mener à l’échec de la requête, interdire aux
autres processus d’en allouer, voire provoquer un swap ou l’arrêt de
l’instance. La version 13 corrige cela : lors d’un hachage, l’exécuteur
ne se permet de consommer la mémoire qu’à hauteur de
work_mem
× hash_mem_multiplier
(2 par défaut
dès la version 15, 1 auparavant), puis se rabat sur le disque si cela
reste insuffisant.
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 totale du cache disque
disponible pour une requête. Pour le configurer, il faut prendre en
compte le cache de PostgreSQL (shared_buffers
) et celui du
système d’exploitation. Ce n’est donc pas une mémoire que PostgreSQL va
allouer, mais plutôt une simple indication de ce qui est disponible. 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 116 Kio, résultat de l’addition de 190 580 (colonne
buffers
) et 598 536 (colonne cached
). Il faut
ensuite ajouter shared_buffers
à cette valeur.
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.
Par défaut, une requête possède un seul processus dédié sur le serveur, qui par défaut n’utilise qu’un seul cœur. Pour répartir la charge des grosses requêtes sur plusieurs cœurs, un processus PostgreSQL peut se faire aider d’autres processus durant l’exécution de certains nœuds.
Les parallel workers se répartissent les lignes issues, par exemple, d’un parcours. Un nœud est dédié à la récupération des résultats (gather). Il est opéré par le processus principal qui peut, s’il n’a rien à faire, participer au traitement réalisé par les parallel workers.
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.
La mise en place de la parallélisation a un coût. En conséquence, la parallélisation n’est possible sur un parcours que si la table ou l’index est suffisamment volumineux.
Le coût du transfert des lignes est aussi pris en compte. En conséquence, ce même parcours de table ou d’index ne sera pas forcément parallélisé s’il n’y a pas une clause de filtrage, par exemple.
En pratique, cette parallélisation n’a d’intérêt que si les performances sont contraintes d’abord par le CPU, et non par les disques.
Paramètre principaux :
Le nombre maximum de processus utilisables pour un nœud d’exécution
dépend de la valeur du paramètre
max_parallel_workers_per_gather
(à 2 par défaut). Ils ne
seront lancés que si la requête le nécessite.
Si plusieurs processus veulent paralléliser l’exécution de leur
requête au même moment, le nombre total de workers parallèles
simultanés ne pourra pas dépasser la valeur du paramètre
max_parallel_workers
(8 par défaut).
Ce nombre ne peut lui-même dépasser la valeur du paramètre
max_worker_processes
, nombre de processus d’arrière-plan.
(Avant PostgreSQL 10, le paramètre max_parallel_workers
n’existait pas et la limite se basait sur
max_worker_processes
.)
Impact de la volumétrie :
Le volume déclencheur dépend pour les tables de la valeur du
paramètre min_parallel_table_scan_size
(8 Mo par défaut) et
de celle de min_parallel_index_scan_size
(512 ko par
défaut) pour les index. Ces paramètres sont rarement modifiés. Le moteur
détermine ensuite ainsi le nombre de workers à lancer :
min_parallel_table_scan_size
= S (dans le cas d’une
table)
Si le processus ne peut lancer tous les workers qu’il a prévu, il poursuit sans message d’erreur avec ceux qu’il peut lancer.
Le coût induit par la mise en place du parallélisme est défini par
parallel_setup_cost
(1000 par défaut, rarement
modifié).
Il faut se rappeler que le processus principal traite lui aussi des
lignes, comme ses parallel workers. Il pourrait donc devenir un
goulet d’étranglement. Le paramètre
parallel_leader_participation
peut alors être passé à
off
afin qu’il ne s’occupe plus que de récupérer et traiter
le résultat des workers.
Exemple :
Le parcours suivant sur une table de 13 Go demande 7 parallel
workers (mention Planned). Cela est possible car on a
monté max_parallel_workers_per_gather
au moins à 7. Seuls 4
parallel workers ont été accordés : le seuil de
max_parallel_workers
a dû être dépassé à cause d’autres
requêtes. On note 5 boucles (loops) car le processus principal
participe aussi au parcours.
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM pgbench_accounts
WHERE bid = 55 ;
QUERY PLAN
----------------------------------------------------------------------------
Gather (actual time=1837.490..2019.284 rows=100000 loops=1)
Workers Planned: 7
Workers Launched: 4
-> Parallel Seq Scan on pgbench_accounts (actual time=1849.780..1904.057 rows=20000 loops=5)
Filter: (bid = 55)
Rows Removed by Filter: 19980000
Planning Time: 0.038 ms Execution Time: 2024.043 ms
L’option VERBOSE
donne plus de détails :
QUERY PLAN
----------------------------------------------------------------------------
Gather (actual time=1983.902..2124.019 rows=100000 loops=1)
Output: aid, bid, abalance, filler
Workers Planned: 7
Workers Launched: 4
-> Parallel Seq Scan on public.pgbench_accounts (actual time=2001.592..2052.496 rows=20000 loops=5)
Output: aid, bid, abalance, filler
Filter: (pgbench_accounts.bid = 55)
Rows Removed by Filter: 19980000
Worker 0: actual time=1956.263..2047.370 rows=16893 loops=1
Worker 1: actual time=1957.269..2043.763 rows=62464 loops=1
Worker 2: actual time=2055.270..2055.271 rows=0 loops=1
Worker 3: actual time=2055.577..2055.577 rows=0 loops=1
Query Identifier: 7891460439412068106
Planning Time: 0.067 ms Execution Time: 2130.117 ms
Mémoire :
Les processus parallélisés sont susceptibles d’utiliser chacun l’équivalent des ressources mémoire d’un processus.
Concrètement, chaque parallel worker d’un nœud consommant de
la mémoire est susceptible d’utiliser la quantité définie par
work_mem
. La parallélisation peut donc augmenter le besoin
en mémoire.
Paramétrage :
Le défaut de max_worker_processes
est 8. Ne descendez
pas plus bas, car les background workers sont de plus en plus
utilisés par les nouvelles fonctionnalités de PostgreSQL et les
extensions tierces, et modifier ce paramètre implique de redémarrer. Sur
les machines modernes, il peut être monté assez haut (bien au-delà du
nombre de cœurs).
Les autres paramètres peuvent être modifiés avec SET
au
sein d’une session.
Le choix de max_parallel_workers
dépend du nombre de
cœurs. Le défaut de 8 est trop bas pour la plupart des machines
récentes. La valeur de max_parallel_workers_per_gather
dépend du type des grosses requêtes et du nombre de requêtes tournant
simultanément. Les petites requêtes (OLTP) profiteront rarement du
parallélisme. Des configurations assez agressives sur de grosses
configurations vont bien au-delà du nombre de cœurs.
Cependant, il ne sert à rien de trop paralléliser si les disques ne suivent pas, ou si le nombre de cœurs est réduit. Et si ces paramètres sont trop hauts, il y a un risque que les grosses requêtes saturent les CPU au détriment des plus petites et d’autres processus.
Par contre, si le paramétrage est trop prudent, les CPU seront sous-utilisés. De nombreuses requêtes candidates au parallélisme peuvent se retrouver privées de worker, ce qui mènera à des plans suboptimaux. Là encore, la supervision et l’expérimentation prudente sont nécessaires.
Création et maintenance d’index :
La création d’index B-tree peut être aussi être parallélisée depuis
la version 11. Le paramètre
max_parallel_maintenance_workers
, par défaut à 2, indique
le nombre de workers utilisables lors de la création d’un
index, mais aussi de son nettoyage lors d’un VACUUM
. Le
gain de temps peut être appréciable. Cette opération étant assez rare,
le paramètre peut être monté assez haut. Les limites de
max_worker_processes
et max_parallel_workers
s’appliquent là encore.
Contrairement au cas de work_mem
qui peut être alloué
par chaque worker lors d’un tri,
maintenance_work_mem
est allouée une seule fois et partagé
entre les différents workers.
Référence :
La documentation a un chapitre entier sur le sujet du parallélisme.
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 quelle quantité de journaux
générés il faut lancer un CHECKPOINT
:
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 demie par défaut).
D’ailleurs, à partir de la version 14, il s’agit de la valeur par
défaut.
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. De plus, track_counts
est requis pour que
l’autovacuum puisse fonctionner.
D’autres paramètres, désactivés par défaut, permettent d’aller plus loin :
track_functions
à pl
ou all
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 les
tables pg_stat_database
et pg_stat_statements
(si cette
extension est installée). Il ajoute des traces suite à un
VACUUM
ou un ANALYZE
exécutés par le processus
autovacuum
Dans les plans d’exécutions (avec
EXPLAIN (ANALYZE,BUFFERS)
), il permet l’affichage du temps
passé à lire hors du cache de PostgreSQL (sur disque ou dans le cache de
l’OS) :
I/O Timings: read=2.062
Avant d’activer track_io_timing
sur une machine peu
performante, vérifiez avec l’outil pg_test_timing
que la
quasi-totalité des appels dure moins d’une nanoseconde.
La version 14 a ajouté le paramètre track_wal_io_timing
qui permet de suivre les performances des opérations de lecture et
écriture dans les WAL dans la vue pg_stat_wal
. Par défaut,
le paramètre est désactivé.
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.
Dans PGDATA, le sous-répertoire pg_tblspc
contient les
tablespaces, c’est-à-dire des espaces de stockage.
Sous Linux, ce sont des liens symboliques vers un simple répertoire
extérieur à PGDATA. Chaque lien symbolique a comme nom l’OID du
tablespace (table système pg_tablespace
). PostgreSQL y crée
un répertoire lié aux versions de PostgreSQL et du catalogue, et y place
les fichiers de données.
postgres=# \db+
Liste des tablespaces
Nom | Propriétaire | Emplacement | … | Taille |…
------------+--------------+-----------------------+---+---------+-
froid | postgres | /HDD/tbl/froid | | 3576 kB |
pg_default | postgres | | | 6536 MB | pg_global | postgres | | | 587 kB |
sudo ls -R /HDD/tbl/froid
/HDD/tbl/froid:
PG_15_202209061
/HDD/tbl/froid/PG_15_202209061:
5
/HDD/tbl/froid/PG_15_202209061/5:
142532 142532_fsm 142532_vm
Sous Windows, les liens sont à proprement parler des Reparse Points (ou Junction Points) :
postgres=# \db
Liste des tablespaces
Nom | Propriétaire | Emplacement
------------+--------------+-------------
pg_default | postgres |
pg_global | postgres | tbl1 | postgres | T:\TBL1
PS P:\PGDATA13> dir 'pg_tblspc/*' | ?{$_.LinkType} | select FullName,LinkType,Target
FullName LinkType Target-------- -------- ------
:\PGDATA13\pg_tblspc\105921 Junction {T:\TBL1} P
Par défaut, pg_tblspc/
est vide. N’existent alors que
les tablespaces pg_global
(sous-répertoire
global/
des objets globaux à l’instance) et
pg_default
(soit base/
).
Un tablespace, vu de PostgreSQL, est un espace de stockage des objets (tables et index principalement). Son rôle est purement physique, il n’a pas à être utilisé pour une séparation logique des tables (c’est le rôle des bases et des schémas), encore moins pour gérer des droits.
Pour le système d’exploitation, il s’agit juste d’un répertoire, déclaré ainsi :
CREATE TABLESPACE ssd LOCATION '/var/lib/postgresql/tbl_ssd';
L’idée est de séparer physiquement les objets suivant leur utilisation. Les cas d’utilisation des tablespaces dans PostgreSQL sont :