Dalibo SCOP
Formation | Formation PERF1 |
Titre | PostgreSQL Performances |
Révision | 25.03 |
ISBN | N/A |
https://dali.bo/perf1_pdf | |
EPUB | https://dali.bo/perf1_epub |
HTML | https://dali.bo/perf1_html |
Slides | https://dali.bo/perf1_slides |
Vous trouverez en ligne les différentes versions complètes de ce document. Les solutions de TP ne figurent pas forcément dans la version imprimée, mais sont dans les versions numériques (PDF ou HTML).
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 13 à 17.
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 | ~ 25 Go/s |
NVMe | ~ 10 µs | ~ 10 Go/s |
SSD (SATA) | ~ 100 µs | ~ 550 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 plusieurs types de disques :
Les disques ont deux caractéristiques principales : le débit (throughput) et la latence, ou temps d’accès.
Les temps d’accès sont très importants pour une base de données. Ils conditionnent les performances des accès aléatoires, c’est-à-dire de blocs isolés, notamment lors de l’utilisation d’index. Ils sont également très importants lors de la synchronisation sur disque. 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). Le débit en écriture est bien sûr important, selon l’utilisation.
Dans le tableau ci-dessous, il est flagrant que la mémoire est 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 pour les gros volumes, et il reste possible 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 existe plusieurs classes de disques SATA, et il est crucial de privilégier ceux conçus pour un usage en datacenter. Par exemple, les disques SATA de classe Desktop ont souvent un délai (timeout) supérieur à 30 secondes lors de clusters défectueux nécessitant une réallocation. Cela peut entraîner une instabilité du système (disques marqués hors ligne plus fréquemment, pannes…). Il est donc essentiel de vérifier la classe des disques utilisés et de choisir exclusivement des modèles spécifiquement labellisés Enterprise-class.
Il est préconisé de se tourner vers des disques SAS (Serial Attached SCSI). De par leur conception matérielle différente, les disques SAS offrent des temps d’accès plus bas et une fiabilité supérieure, par rapport aux disques SATA. Le protocole de communication est aussi différent. Les disques SAS sont spécialement adaptés pour fonctionner 24 h/24, et ont un MTBF (temps moyen entre pannes) nettement plus élevé. Ils s’imposent comme un choix privilégié pour les systèmes de gestion de bases de données. Mais si le budget ne le permet pas, des disques SATA (de classe Enterprise) en plus grand nombre, au sein d’une solution RAID matérielle, 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 par une durée d’accès à une page aléatoire très rapide, se rapprochant de celle d’une donnée contiguë (ou séquentielle). C’est parfait pour accéder à des index. Et aujourd’hui, la qualité et la fiabilité des SSD, par rapport aux disques magnétiques, ne sont plus des critères discriminants.
Les disques SSD tendent donc à devenir la norme pour les bases de données quand les performances comptent.
Sur le marché du SSD, il existe plusieurs technologies (SLC, eMLC, iSLC, QLC…). Certaines auront de meilleures performances en lecture, d’autres en écriture, d’autres encore une meilleure durée de vie en écriture, ou un prix plus bas. Il est donc important de bien lire la documentation technique des disques avant leur achat.
Les disques NVMe sont une évolution des SSD, et des protocoles associés, permettant d’exploiter à fond le parallélisme des lectures et écritures. Le débit peut dépasser les 10 Go/s. À l’inverse, l’interface SATA troisième génération n’a qu’un débit théorique de 6 Gbit/s, soit environ 750 Mo/s ; et le SAS-4. sature à 22,5 Gbit/s (environ 2 Go/s). Ces interfaces conviennent encore pour des disques physiques, mais brident n’importe quel SSD au niveau du bus de données.
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 Comme pour les disques mécaniques, les unités de stockage mémoire (SSD, NVMe…) se déclinent en plusieurs classes : celles destinées au grand public et aux stations de travail, et celles conçues pour les environnements professionnels (serveurs). Il est indispensable de toujours privilégier les disques de classe « Entreprise », qui offrent une meilleure durabilité et intègrent des fonctionnalités indispensables pour un usage serveur (comme des fonctionnalités SMART avancées).
Des disques bas de gamme mais rapides 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 système d’exploitation pour optimiser l’utilisation des SSD. Sous Linux, vérifier que les disques SSD NVMe et SSD rapides sont bien configurés ainsi :
# Désactivation de la réorganisation des accès
echo none > /sys/block/<device>/queue/scheduler
# Rotational : 1 pour disques classiques, 0 pour SSD 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 si les performances sont critiques. En effet, les autres machines virtuelles peuvent accaparer des ressources. De plus, 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. Une configuration performante à un moment peut se dégrader dans le temps avec la montée en charge d’autres machines que PostgreSQL.
Mémoire :
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.
CPU :
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, voire plus.
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, et superviser le steal.
Disques :
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 souvent 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 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.
Éviter les snapshots sur les bases critiques.
Enfin, 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, éviter donc de passer par la couche de virtualisation pour les disques et préférez des attachements SAN, plus sûrs et performants.
Horloge :
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.
Paravirtualisation :
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.
NUMA :
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 80 Go de RAM, le système doit utiliser la mémoire d’un autre nœud. Le franchissement de la limite NUMA peut donc réduire les performances. Une bonne pratique consiste à limiter les VM aux 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.
PostgreSQL utilise une partie de la mémoire du serveur pour son propre cache. Il profite aussi du cache de Linux. Les processus de PostgreSQL ne consomment eux-mêmes que peu de RAM, ou n’en utilisent que brièvement, par exemple le temps d’un tri de données. De plus, PostgreSQL doit être paramétré pour travailler sur disque pour éviter de consommer trop de mémoire. Enfin, aucun outil ou logiciel consommateur de RAM n’est censé fonctionner sur un serveur de base de données. En principe, un gros swap n’a donc pas d’utilité sur un serveur PostgreSQL.
Avoir trop de swap a même tendance à aggraver la situation dans un contexte où la mémoire devient rare, par exemple avec une requête qui en réclame trop : le système finit par s’effondrer à force de swapper et déswapper. (La configuration de la surréservation ci-dessous évitera ce souci.)
Un petit swap reste utile pour y stocker des processus oubliés comme
il peut en traîner sur un serveur, des démons inactifs, le contenu de
systèmes de fichiers tmpfs
(classiquement,
/var/run
) ou encore les journaux de
systemd-journald
selon la configuration de celui-ci. Il
peut arriver exceptionnellement que des processus backend de
PostgreSQL occupent beaucoup de mémoire tout en étant longtemps
inactifs : un plus gros swap pourrait alors se justifier.
À l’inverse, ne pas avoir du tout de swap peut mener à un gaspillage de RAM, et à un autre problème : l’admnistrateur n’a pas d’indice d’une surconsommation de mémoire.
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. Il ne faut pas descendre à 0, ce qui désactiverait
complètement le swap.
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 surallocation, 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. C’est donc la configuration par défaut sous Linux. 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).
Un autre intérêt des huge pages est qu’elles ne peuvent pas finir dans le swap.
Nous allons donc prévoir des huge pages pour toute la mémoire partagée de l’instance.
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. C’est d’ailleurs ce qui se passe sur beaucoup d’instances avec la configuration système par défaut.
(À 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 s’exprime 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. Il n’est pas
interdit de mettre des valeurs supérieures, puisque Linux crée les
huge pages à la volée à concurrence de
vm.nr_overcommit_hugepages
(et les détruira à l’extinction
de PostgreSQL). Prévoir large évite de recalculer le nombre de pages si
l’on modifie le paramétrage de la mémoire partagée de PostgreSQL
(d’autres paramètres que shared_buffers
ou
max_connections
entrent en compte).
Une alternative est le paramètre vm.nr_hugepages
, qui
crée des pages statiques (et leur utilisation est obligatoire pour des
pages de 1 Go). Ces pages sont créées une fois pour toutes par Linux, et
cette mémoire est gaspillée si PostgreSQL échoue à l’utiliser.
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
À partir de la version 15, le paramètre
shared_memory_size
permet de connaître la taille complète
de mémoire partagée allouée (un peu supérieure à
shared_buffers
en pratique). Dans le cadre de l’utilisation
des Huge Pages, il est possible de consulter le paramètre
shared_memory_size_in_huge_pages
pour connaître le nombre
de pages mémoires nécessaires (mais on ne peut savoir ici si elles sont
utilisées) :
postgres=# \dconfig shared*
Liste des paramètres de configuration
Paramètre | Valeur
----------------------------------+---------------------------------
shared_buffers | 8GB
shared_memory_size | 8479MB
shared_memory_size_in_huge_pages | 4240 …
NB : Sur un système hébergeant plusieurs instances, il faudra additionner toutes les zones mémoire de toutes les instances.
Enfin, si l’on a paramétré la surallocation mémoire comme décrit
ci-dessus, son 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 (avec ¼ de RAM pour la mémoire partagée et
pour préserver 20% du cache de Linux).
Vérifier la prise en compte des huge pages :
/proc/meminfo
indique les pages réservées et
utilisées.
PostgreSQL peut refuser le démarrage si les huge pages nécessaires ne sont pas toutes disponibles (mauvais paramétrage, changement de paramétrage sans ajustement des paramètres système, fragmentation mémoire…) avec ce paramétrage :
huge_pages = on
Cela permet d’éviter un mode dégradé pas évident à détecter.
On peut aussi vouloir démarrer tout en supervisant le problème
potentiel. À partir de PostgreSQL 17,
SHOW huge_pages_status
doit renvoyer on
ou
off
selon que PostgreSQL utilise bien les huge
pages ou non.
Un outil pratique de Linux, mais optionnel, pour gérer les huge
pages est hugeadm
.
Pour plus de détails :
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 :
L’ordonnanceur de tâches de Linux 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). À partir de Linux 5.13, ce
paramètre est remplacé par
/sys/kernel/debug/sched/migration_cost_ns
.
Sur Rocky Linux 9 ou Debian 12, il faudra donc prévoir dans
/etc/crontab
:
@reboot echo 5000000 > /sys/kernel/debug/sched/migration_cost_ns
Sur les versions précédentes de ces distributions, ce sera dans
/etc/sysctl.conf.d/
:
kernel.sched_migration_cost_ns=5000000
Par ailleurs, l’ordonnanceur regroupe les processus par session (TTY) afin d’avoir un meilleur temps de réponse « perçu », notamment sur une machine de bureau. 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, paramétrer dans
/etc/sysctl.conf.d/
:
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.
La documentation officielle conseille ceci pour dimensionnershared_buffers
:
Un bon point de départ est 25 % de la mémoire vive totale. Ne pas 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,
voire plus sur les machines récentes, car il concerne des opérations
lourdes (indexation, nettoyage des index par VACUUM
…).
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.
autovacuum_work_mem
est la mémoire que s’autorise à
prendre l’autovacuum pour les nettoyages d’index, et ce pour chaque
worker de l’autovacuum (3 maximum par défaut). Par défaut, ce
paramètre reprend maintenance_work_mem
, et est généralement
laissé tel quel.
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 (voir https://dali.bo/j1_html#configuration-de-la-surréservation-mémoire), 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 :
hash_mem_multiplier
est un paramètre multiplicateur, qui
peut s’appliquer à certaines opérations (le hachage, lors de jointures
ou agrégations). Par défaut, il vaut 1 en versions 13 et 14, et 2 à
partir de la 15. Le seuil de consommation fixé par work_mem
est alors multiplié d’autant. hash_mem_multiplier
permet de
donner plus de RAM à ces opérations sans augmenter globalement
work_mem
. Il peut lui aussi être modifié dans une session.
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.
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. (Avant PostgreSQL 13, sans cette
limite, l’exécuteur pouvait allouer au-delà de work_mem
en
cas de mauvaise estimation, jusqu’à saturer la RAM.)
PostgreSQL possède des paramètres liés aux disques. Ils peuvent se paramétrer globalement ou au niveau des tablespaces, nous les verrons à ce moment.
Depuis PostgreSQL 17 existe io_combine_limit
qui permet
de réduire le nombre d’appels système en regroupant les blocs demandés.
(C’est aussi le début d’une API visant, à terme, à introduire une part
de direct I/O dans PostgreSQL.) En version 17 ne sont ainsi
concernés que les parcours séquentiels, ANALYZE
et
l’extension pg_prewarm
. Il n’est pas conseillé de modifier
ce paramètre sans tests sérieux préalables.
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 à ⅔ 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.
Le paramétrage lié à la journalisation nécessite de rappeler son principe.
Les journaux de transactions (appelés souvent WAL) sont une garantie contre les pertes de données. Il s’agit d’une technique standard de journalisation appliquée à toutes les transactions, pour garantir l’intégrité (la base reste cohérente quoiqu’il arrive) et la durabilité (ce qui est validé ne sera pas perdu).
Ainsi lors d’une modification de donnée, l’écriture au niveau du disque se fait généralement en deux temps :
COMMIT
;Ainsi en cas de crash :
Les écritures dans le journal se font de façon séquentielle, donc
sans grand déplacement de la tête d’écriture (sur un disque dur
classique, c’est l’opération la plus coûteuse). De plus, comme nous
n’écrivons que dans un seul fichier de transactions, la synchronisation
sur disque, lors d’un COMMIT
, peut se faire sur ce seul
fichier, si le système de fichiers le supporte. Concrètement, ces
journaux sont des fichiers de 16 Mo par défaut, avec des noms comme
0000000100000026000000AF
, dans le répertoire
pg_wal/
de l’instance PostgreSQL (répertoire souvent sur
une partition dédiée).
L’écriture définitive dans les fichiers de données est asynchrone, et généralement lissée, ce qui est meilleur pour les performances qu’une écriture immédiate. Cette opération est appelée « checkpoint » et périodique (5 minutes par défaut, ou plus).
Divers paramètres et fonctionnalités peuvent altérer ce comportement par défaut, par exemple pour des raisons de performances.
À côté de la sécurité et des performances, le mécanisme des journaux de transactions est aussi utilisé pour des fonctionnalités très intéressantes, comme le PITR et la réplication physique, basés sur le rejeu des informations stockées dans ces journaux.
Pour plus d’informations :
fsync
est le paramètre qui assure que les données sont
bien écrites physiquement sur le disque (synchronisation). En effet,
PostgreSQL écrit dans ses fichiers via des appels système au noyau ; et
celui-ci, 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ée par défaut (on
).
Il est donc obligatoire en production de conserver ce paramètre activé. C’est essentiel pour la fiabilité, même si cela impacte très négativement les performances en écriture en cas de nombreuses transactions.
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, au cours d’une opération
périodique nommée checkpoint. La périodicité par défaut est de
5 minutes. Pour lisser des grosses écritures, on monte fréquemment le
paramètre checkpoint_timeout
à 15 minutes, voire plus.
Cette augmentation peut avoir l’intérêt de réduire 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 journaux dans le répertoire
pg_wal/
peut dépasser largement max_wal_size
en cas de forte activité ou de retard de l’archivage.
max_wal_size
n’est en aucun cas une valeur plafond.
Un checkpoint déclenché par atteinte du seuil
max_wal_size
apparaît dans postgresql.log
. Si
cela arrive trop fréquemment, il est conseillé d’augmenter les deux
paramètres max_wal_size
et
checkpoint_timeout
.
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 | /mnt/hdd/pg | | 3576 kB |
pg_default | postgres | | | 6536 MB | pg_global | postgres | | | 587 kB |
sudo ls -R /mnt/hdd/pg
/mnt/hdd/pg:
PG_15_202209061
/mnt/hdd/pg/PG_15_202209061:
5
/mnt/hdd/pg/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 '/mnt/ssd/pg';
L’idée est de séparer physiquement les objets suivant leur utilisation. Les cas d’utilisation des tablespaces dans PostgreSQL sont :
cannot extend file
.Sans un réel besoin physique, il n’y a pas besoin de créer des tablespaces, et de complexifier l’administration.
Un tablespace n’est pas adapté à une séparation logique des objets.
Si vous tenez à distinguer les fichiers de chaque base sans besoin
physique, rappelez-vous que PostgreSQL crée déjà un sous-répertoire par
base de données dans PGDATA/base/
.
PostgreSQL ne connaît pas de notion de tablespace en lecture seule, ni de tablespace transportable entre deux bases ou deux instances.
Il y a quelques pièges à éviter à la définition d’un tablespace :
Pour des raisons de sécurité et de fiabilité, le répertoire choisi
ne doit pas être à la racine d’un point de montage.
(Cela vaut aussi pour les répertoires PGDATA ou
pg_wal
).
Positionnez toujours les données dans un sous-répertoire, par exemple
dans /mnt/ssd/pg
plutôt que directement dans le point de
montage /mnt/ssd
. (Voir Utilisation
de systèmes de fichiers secondaires dans la documentation
officielle, ou le bug à
l’origine de ce conseil.)
Surtout, le tablespace doit impérativement être placé hors de PGDATA. Certains outils poseraient problème sinon.
Si ce conseil n’est pas suivi, PostgreSQL crée le tablespace mais renvoie un avertissement :
WARNING: tablespace location should not be inside the data directory CREATE TABLESPACE
Il est aussi déconseillé de mettre le numéro de version de PostgreSQL
dans le chemin du tablespace. PostgreSQL le gère déjà à l’intérieur du
tablespace. Cela évite des incohérences dans les noms des chemins si
vous migrez plus tard avec pg_upgrade
.
Le répertoire du tablespace doit exister et les accès ouverts et restreints à l’utilisateur système sous lequel tourne l’instance (en général postgres sous Linux, Network Service sous Windows) :
# /mnt/ssd/ doit exister (point de montage d'un SSD par exemple)
# chown postgres:postgres /mnt/ssd/pg
# chmod 700 /mnt/ssd/pg
Les ordres SQL plus haut permettent de :
Quelques choses à savoir :
La table ou l’index est totalement verrouillé le temps du déplacement.
Par défaut, les nouveaux index ne sont pas créés
automatiquement dans le même tablespace que la table, mais en fonction
de default_tablespace
.
Les index existants ne « suivent » pas automatiquement une table déplacée, il faut les déplacer séparément.
Depuis PostgreSQL 14, il est possible de préciser un tablespace
de réindexation lors d’une réindexation
(REINDEX … TABLESPACE …
).
Les tablespaces des tables sont visibles dans la vue système
pg_tables
, dans \d+
sous psql, et dans
pg_indexes
pour les index :
SELECT schemaname, indexname, tablespace
FROM pg_indexes
WHERE tablename = 'ma_table';
schemaname | indexname | tablespace
------------+--------------+------------
public | matable_idx | chaud public | matable_pkey |
Le paramètre default_tablespace
permet d’utiliser un
autre tablespace que celui par défaut dans PGDATA. En plus du
postgresql.conf
, il peut être défini au niveau rôle, base,
ou le temps d’une session :
ALTER DATABASE erp_prod SET default_tablespace TO ssd ; -- base
ALTER DATABASE erp_archives SET default_tablespace TO froid ; -- base
ALTER ROLE etl SET default_tablespace TO ssd ; -- niveau rôle
ALTER ROLE audit IN DATABASE erp_prod SET default_tablespace TO froid ; -- niveau rôle dans une base
SET default_tablespace TO ssd ; -- session
Les opérations de tri et les tables temporaires peuvent être
déplacées vers un ou plusieurs tablespaces dédiés grâce au paramètre
temp_tablespaces
. Le premier intérêt est de dédier aux tris
une partition rapide (SSD, disque local…). Un autre est de ne plus
risquer de saturer la partition du PGDATA en cas de fichiers temporaires
énormes dans base/pgsql_tmp/
.
Ne jamais utiliser de RAM disque (comme tmpfs
) pour des
tablespaces de tri : la mémoire de la machine ne doit servir qu’aux
applications et outils, au cache de l’OS, et aux tris en RAM. Favorisez
ces derniers en jouant sur work_mem
.
En cas de redémarrage, ce tablespace ne serait d’ailleurs plus utilisable. Un RAM disque est encore plus dangereux pour les tablespaces de données, bien sûr.
Il faudra ouvrir les droits aux utilisateurs ainsi :
GRANT CREATE ON TABLESPACE ssd_tri1 TO dupont ;
Plusieurs tablespaces temporaires peuvent être paramétrés. Noter que la déclaration se fait sans guillemet. Chaque transaction en choisira un de façon aléatoire à la création d’un objet temporaire, puis utilisera alternativement les autres pour chaque nouveau fichier. C’est un bon moyen de lisser la charge :
Si un des tablespaces temporaires sature, la requête tombe en erreur immédiatement : PostgreSQL ne regarde pas si autre tablespace temporaire a de la place libre.
Il vaut donc mieux regrouper les espaces disponibles dans un même système de fichiers, et n’avoir qu’un grand tablespace temporaire.
Dans le cas de disques de performances différentes, il faut adapter les paramètres concernés aux caractéristiques du tablespace si la valeur par défaut ne convient pas. Ce sont des paramètres classiques qui ne seront pas décrits en détail ici :
seq_page_cost
(coût d’accès à un bloc pendant un
parcours, défaut 1) ;random_page_cost
(coût d’accès à un bloc isolé, défaut
4) ;effective_io_concurrency
(nombre d’I/O simultanées,
défaut 1) et maintenance_io_concurrency
(idem, pour une
opération de maintenance, défaut 10).Notamment :
effective_io_concurrency
a pour but d’indiquer le nombre
d’opérations disques possibles en même temps pour un client
(prefetch). Seuls les parcours Bitmap Scan sont
impactés par ce paramètre. Selon la documentation,
pour 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, n-1 s’il s’agit d’un RAID 5 ou 6, n/2 s’il s’agit d’un RAID 10).
Avec du SSD, il est possible de monter à plusieurs centaines, étant
donné la rapidité de ce type de disque. Ces valeurs doivent être
réduites sur un système très chargé. Une valeur excessive mène au
gaspillage de CPU. Le défaut deffective_io_concurrency
est
seulement de 1, et la valeur maximale est 1000.
(Avant la version 13, le principe était le même, mais la valeur exacte de ce paramètre devait être 2 à 5 fois plus basse comme le précise la formule des notes de version de la version 13.)
maintenance_io_concurrency
est similaire à
effective_io_concurrency
, mais pour les opérations de
maintenance. Celles‑ci peuvent ainsi se voir accorder plus de ressources
qu’une simple requête. Il faut penser à le monter aussi si on adapte
effective_io_concurrency
.
Par exemple, un système paramétré pour des disques classiques aura comme paramètres par défaut :
random_page_cost = 4
effective_io_concurrency = 1
maintenance_io_concurrency = 10
et un tablespace sur un SSD les surchargera ainsi :
ALTER TABLESPACE ssd SET ( random_page_cost = 1 );
ALTER TABLESPACE ssd SET ( effective_io_concurrency = 500,
= 500 ) ; maintenance_io_concurrency
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.
La commande initdb
permet de créer une instance en
positionnant le répertoire dédié aux journaux de transaction en dehors
du répertoire de données. Pour cela, il faut utiliser l’option
-X
ou --waldir
:
$ initdb -X /montage/17/pgwal
Un lien symbolique est créé dans le répertoire de données pour que PostgreSQL retrouve le répertoire des journaux de transactions.
Si l’on souhaite modifier une instance existante, 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
/montage/17/pgwal
.
# systemctl stop postgresql-17
# cd /var/lib/pgsql/17/data
# mv pg_wal /montage/17/pgwal
# ln -s /montage/17/pgwal pg_wal
# ls -l pg_wal
lrwxrwxrwx. 1 root root 6 Sep 18 16:07 pg_wal -> /montage/17/pgwal # systemctl start postgresql-17
PostgreSQL met à disposition différents compteurs statistiques via des vues.
Avant la version 15, les vues système utilisent des métriques
stockées dans des fichiers de statistiques. Ces fichiers sont mis à jour
par le processus stats collector
. Ils 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 apparaître une forte activité disque à cause du
processus stats collector
.
Lorsque le problème se pose, il est recommandé de déplacer ces
fichiers en RAM avec la procédure suivante. Attention, le module
pg_stat_statements
, s’il est installé, 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 disque 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
.
Sur Debian, Ubuntu et dérivés, cela est fait par défaut :
stats_temp_directory
pointe vers un répertoire dans
/run
, qui est un tmpfs
.
Sur Rocky Linux 8, la procédure est :
$ mkdir /var/lib/pgsql/17/pg_stat_tmpfs
# mount -o \
auto,nodev,nosuid,noexec,noatime,mode=0700,size=256M,uid=postgres,gid=postgres \
-t tmpfs tmpfs /var/lib/pgsql/17/pg_stat_tmpfs
# mount
...
tmpfs on /var/lib/pgsql/17/pg_stat_tmpfs type tmpfs (rw,nosuid,nodev,noexec,noatime,seclabel,size=262144k,mode=700,uid=26,gid=26)
postgresql.conf
:stats_temp_directory = '/var/lib/pgsql/17/pg_stat_tmpfs'
SELECT pg_reload_conf() ;
SHOW stats_temp_directory ;
stats_temp_directory
---------------------- /var/lib/pgsql/17/pg_stat_tmpfs
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/17/pg_stat_tmpfs"
/etc/fstab
la ligne suivante pour
que la modification survive au prochain redémarrage : tmpfs /var/lib/pgsql/17/pg_stat_tmpfs tmpfs auto,nodev,nosuid,noexec, noatime,uid=postgres,gid=postgres,mode=0700,size=256M 0 0
Depuis la version 15, le paramètre stats_temp_directory
n’existe plus, car les informations statistiques sont conservées en
mémoire partagée. À l’arrêt de l’instance, elles sont enregistrées sur
disque dans le répertoire pg_stat
. Comme
pg_stat
et pg_stat_tmp/
restent utilisés par
pg_stat_statements
et d’autres extensions, le principe du
lien symbolique vers tmpfs
reste valable en version 15.
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.
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.
Ci-dessous figure un exemple de sortie en version 11 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.45%)
----- Tablespace location -----
[OK] No tablespace in PGDATA
----- Shared buffer hit rate -----
[INFO] shared_buffer_heap_hit_rate: 99.31%
[INFO] shared_buffer_toast_hit_rate: 0.00%
[INFO] shared_buffer_tidx_hit_rate: 58.82%
[INFO] shared_buffer_idx_hit_rate: 99.63%
[OK] Shared buffer idx hit rate is very good
----- Indexes -----
[OK] No invalid indexes
[OK] No unused indexes
----- Procedures -----
[OK] No procedures with default costs
===== Configuration advice =====
----- checkpoint -----
[MEDIUM] Your checkpoint completion target is too low.
Put something nearest from 0.8/0.9 to balance your writes better
during the checkpoint interval
----- extension -----
[LOW] Enable pg_stat_statements in database template1 to collect statistics
on all queries (not only queries longer than log_min_duration_statement
in logs)
----- hugepages -----
[LOW] Change Huge Pages size from 2MB to 1GB
[MEDIUM] Enable huge_pages in PostgreSQL to consume system Huge Pages
[MEDIUM] set vm.nr_hugepages=195 in /etc/sysctl.conf and run sysctl -p
to reload it. This will allocate huge pages (may require system reboot).
----- planner ----- [MEDIUM] Set random_page_cost=seq_page_cost on SSD disks
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.
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 :
$ 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.
$ 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)
$ 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)
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)
$ 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)
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;
Si la base de donnée utilisée est celle fournie avec pgbench, la
variable :scale
sera valorisée à partir du nombre de lignes
présentes dans la relation pgbench_branches
. Si vous
utilisez une autre base de donnée, il faut indiquer l’échelle avec
l’option -s
lorsque l’on exécute le test.
L’installation est détaillée ici pour Rocky Linux 8 et 9 (similaire à Red Hat et à d’autres variantes comem Oracle Linux et Fedora), et Debian/Ubuntu.
Elle ne dure que quelques minutes.
ATTENTION : Red Hat, CentOS, Rocky Linux fournissent
souvent par défaut des versions de PostgreSQL qui ne sont plus
supportées. Ne jamais installer les packages postgresql
,
postgresql-client
et postgresql-server
!
L’utilisation des dépôts du PGDG est fortement conseillée.
Installation du dépôt communautaire :
Les dépôts de la communauté sont sur https://yum.postgresql.org/. Les commandes qui suivent sont inspirées de celles générées par l’assistant sur https://www.postgresql.org/download/linux/redhat/, en précisant :
Les commandes sont à lancer sous root :
# dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms\
/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# dnf -qy module disable postgresql
Installation de PostgreSQL 17 (client, serveur, librairies, extensions) :
# dnf install -y postgresql17-server postgresql17-contrib
Les outils clients et les librairies nécessaires seront automatiquement installés.
Une fonctionnalité avancée optionnelle, le JIT (Just In Time compilation), nécessite un paquet séparé.
# dnf install postgresql17-llvmjit
Création d’une première instance :
Il est conseillé de déclarer PG_SETUP_INITDB_OPTIONS
,
notamment pour mettre en place les sommes de contrôle et forcer les
traces en anglais :
# export PGSETUP_INITDB_OPTIONS='--data-checksums --lc-messages=C'
# /usr/pgsql-17/bin/postgresql-17-setup initdb # cat /var/lib/pgsql/17/initdb.log
Ce dernier fichier permet de vérifier que tout s’est bien passé et doit finir par :
Success. You can now start the database server using:
/usr/pgsql-17/bin/pg_ctl -D /var/lib/pgsql/17/data/ -l logfile start
Chemins :
Objet | Chemin |
---|---|
Binaires | /usr/pgsql-17/bin |
Répertoire de l’utilisateur postgres | /var/lib/pgsql |
PGDATA par défaut |
/var/lib/pgsql/17/data |
Fichiers de configuration | dans PGDATA/ |
Traces | dans PGDATA/log |
Configuration :
Modifier postgresql.conf
est facultatif pour un premier
lancement.
Commandes d’administration habituelles :
Démarrage, arrêt, statut, rechargement à chaud de la configuration, redémarrage :
# systemctl start postgresql-17
# systemctl stop postgresql-17
# systemctl status postgresql-17
# systemctl reload postgresql-17 # systemctl restart postgresql-17
Test rapide de bon fonctionnement et connexion à psql :
# systemctl --all |grep postgres # sudo -iu postgres psql
Démarrage de l’instance au lancement du système d’exploitation :
# systemctl enable postgresql-17
Ouverture du firewall pour le port 5432 :
Voir si le firewall est actif :
# systemctl status firewalld
Si c’est le cas, autoriser un accès extérieur :
# firewall-cmd --zone=public --add-port=5432/tcp --permanent
# firewall-cmd --reload # firewall-cmd --list-all
(Rappelons que listen_addresses
doit être également
modifié dans postgresql.conf
.)
Création d’autres instances :
Si des instances de versions majeures différentes doivent
être installées, il faut d’abord installer les binaires pour chacune
(adapter le numéro dans dnf install …
) et appeler le script
d’installation de chaque version. l’instance par défaut de chaque
version vivra dans un sous-répertoire numéroté de
/var/lib/pgsql
automatiquement créé à l’installation. Il
faudra juste modifier les ports dans les postgresql.conf
pour que les instances puissent tourner simultanément.
Si plusieurs instances d’une même version majeure (forcément
de la même version mineure) doivent cohabiter sur le même serveur, il
faut les installer dans des PGDATA
différents.
/var/lib/pgsqsl/17/
(ou
l’équivalent pour d’autres versions majeures).Pour créer une seconde instance, nommée par exemple infocentre :
# cp /lib/systemd/system/postgresql-17.service \ /etc/systemd/system/postgresql-17-infocentre.service
Environment=PGDATA=/var/lib/pgsql/17/infocentre
# export PGSETUP_INITDB_OPTIONS='--data-checksums --lc-messages=C' # /usr/pgsql-17/bin/postgresql-17-setup initdb postgresql-17-infocentre
Option 2 : restauration d’une sauvegarde : la procédure dépend de votre outil.
Adaptation de
/var/lib/pgsql/17/infocentre/postgresql.conf
(port
surtout).
Commandes de maintenance de cette instance :
# systemctl [start|stop|reload|status] postgresql-17-infocentre # systemctl [enable|disable] postgresql-17-infocentre
Sauf précision, tout est à effectuer en tant qu’utilisateur root.
Référence : https://apt.postgresql.org/
Installation du dépôt communautaire :
L’installation des dépôts du PGDG est prévue dans le paquet Debian :
# apt update
# apt install -y gnupg2 postgresql-common # /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
Ce dernier ordre créera le fichier du dépôt
/etc/apt/sources.list.d/pgdg.list
adapté à la distribution
en place.
Installation de PostgreSQL 17 :
La méthode la plus propre consiste à modifier la configuration par défaut avant l’installation :
Dans /etc/postgresql-common/createcluster.conf
,
paramétrer au moins les sommes de contrôle et les traces en
anglais :
initdb_options = '--data-checksums --lc-messages=C'
Puis installer les paquets serveur et clients et leurs dépendances :
# apt install postgresql-17 postgresql-client-17
La première instance est automatiquement créée, démarrée et déclarée
comme service à lancer au démarrage du système. Elle porte un nom (par
défaut main
).
Elle est immédiatement accessible par l’utilisateur système postgres.
Chemins :
Objet | Chemin |
---|---|
Binaires | /usr/lib/postgresql/17/bin/ |
Répertoire de l’utilisateur postgres | /var/lib/postgresql |
PGDATA de l’instance par défaut | /var/lib/postgresql/17/main |
Fichiers de configuration | dans
/etc/postgresql/17/main/ |
Traces | dans
/var/log/postgresql/ |
Configuration
Modifier postgresql.conf
est facultatif pour un premier
essai.
Démarrage/arrêt de l’instance, rechargement de configuration :
Debian fournit ses propres outils, qui demandent en paramètre la version et le nom de l’instance :
# pg_ctlcluster 17 main [start|stop|reload|status|restart]
Démarrage de l’instance avec le serveur :
C’est en place par défaut, et modifiable dans
/etc/postgresql/17/main/start.conf
.
Ouverture du firewall :
Debian et Ubuntu n’installent pas de firewall par défaut.
Statut des instances du serveur :
# pg_lsclusters
Test rapide de bon fonctionnement et connexion à psql :
# systemctl --all |grep postgres # sudo -iu postgres psql
Destruction d’une instance :
# pg_dropcluster 17 main
Création d’autres instances :
Ce qui suit est valable pour remplacer l’instance par défaut par une autre, par exemple pour mettre les checksums en place :
/etc/postgresql-common/createcluster.conf
permet de mettre
en place tout d’entrée les checksums, les messages en anglais,
le format des traces ou un emplacement séparé pour les journaux :initdb_options = '--data-checksums --lc-messages=C'
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
waldir = '/var/lib/postgresql/wal/%v/%c/pg_wal'
# pg_createcluster 17 infocentre
Il est également possible de préciser certains paramètres du fichier
postgresql.conf
, voire les chemins des fichiers (il est
conseillé de conserver les chemins par défaut) :
# pg_createcluster 17 infocentre \
--port=12345 \
--datadir=/PGDATA/17/infocentre \
--pgoption shared_buffers='8GB' --pgoption work_mem='50MB' \ -- --data-checksums --waldir=/ssd/postgresql/17/infocentre/journaux
adapter au besoin
/etc/postgresql/17/infocentre/postgresql.conf
;
démarrage :
# pg_ctlcluster 17 infocentre start
Par défaut, l’instance n’est accessible que par l’utilisateur système
postgres, qui n’a pas de mot de passe. Un détour par
sudo
est nécessaire :
$ sudo -iu postgres psql
psql (17.0)
Type "help" for help. postgres=#
Ce qui suit permet la connexion directement depuis un utilisateur du système :
Pour des tests (pas en production !), il suffit de passer à
trust
le type de la connexion en local dans le
pg_hba.conf
:
local all postgres trust
La connexion en tant qu’utilisateur postgres
(ou tout
autre) n’est alors plus sécurisée :
dalibo:~$ psql -U postgres
psql (17.0)
Type "help" for help. postgres=#
Une authentification par mot de passe est plus sécurisée :
pg_hba.conf
, paramétrer une authentification par
mot de passe pour les accès depuis localhost
(déjà en place
sous Debian) :
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
# IPv6 local connections: host all all ::1/128 scram-sha-256
(Ne pas oublier de recharger la configuration en cas de modification.)
postgres
de
l’instance :
dalibo:~$ sudo -iu postgres psql
psql (17.0)
Type "help" for help.
postgres=# \password
Enter new password for user "postgres":
Enter it again:
postgres=# quit
dalibo:~$ psql -h localhost -U postgres
Password for user postgres:
psql (17.0)
Type "help" for help. postgres=#
.pgpass
dans le répertoire personnel doit contenir
les informations sur cette connexion :localhost:5432:*:postgres:motdepassetrèslong
Ce fichier doit être protégé des autres utilisateurs :
$ chmod 600 ~/.pgpass
psql
, on peut définir ces
variables d’environnement dans la session voire dans
~/.bashrc
:export PGUSER=postgres
export PGDATABASE=postgres
export PGHOST=localhost
Rappels :
/var/lib/pgsql/17/data/log
ou
/var/log/postgresql/
) ;pg_hba.conf
ou
postgresql.conf
impliquant de recharger la configuration
peut être réalisée par une de ces trois méthodes en fonction du
système : root:~# systemctl reload postgresql-17
root:~# pg_ctlcluster 17 main reload
postgres:~$ psql -c 'SELECT pg_reload_conf()'
pgbench est un outil de test livré avec PostgreSQL. Son but est de faciliter la mise en place de benchmarks simples et rapides. Par défaut, il installe une base très simple, génère une activité plus ou moins intense et calcule le nombre de transactions par seconde et la latence. C’est ce qui sera fait ici dans cette introduction. On peut aussi lui fournir ses propres scripts.
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’outil est installé avec les paquets habituels de PostgreSQL, client ou serveur suivant la distribution.
Sur les distributions à paquets RPM (RockyLinux…), l’outil n’est pas dans le chemin par défaut, il faudra fournir le chemin complet (qui ne sera pas répété ici):
/usr/pgsql-17/bin/pgbench
Il est préférable de créer un rôle non privilégié dédié, qui possédera la base de donnée :
CREATE ROLE pgbench LOGIN PASSWORD 'unmotdepassebienc0mplexe';
CREATE DATABASE pgbench OWNER pgbench ;
Le pg_hba.conf
doit éventuellement être adapté. La base
par défaut s’initialise ainsi (ajouter --port
et
--host
au besoin) :
pgbench -U -d pgbench --initialize --scale=100 pgbench
--scale
permet de faire varier proportionnellement la
taille de la base. À 100, la base pèsera 1,5 Go, avec 10 millions de
lignes dans la table principale pgbench_accounts
:
pgbench@pgbench=# \d+
Liste des relations
Schéma | Nom | Type | Propriétaire | Taille | Description
--------+------------------+-------+--------------+---------+-------------
public | pg_buffercache | vue | postgres | 0 bytes |
public | pgbench_accounts | table | pgbench | 1281 MB |
public | pgbench_branches | table | pgbench | 40 kB |
public | pgbench_history | table | pgbench | 0 bytes | public | pgbench_tellers | table | pgbench | 80 kB |
Pour simuler une activité de 20 clients simultanés, répartis sur 4 processeurs, pendant 100 secondes :
pgbench -U pgbench -c 20 -j 4 -T100 pgbench
Pour afficher, ajouter --debug
:
UPDATE pgbench_accounts SET abalance = abalance + -3455 WHERE aid = 3789437;
SELECT abalance FROM pgbench_accounts WHERE aid = 3789437;
UPDATE pgbench_tellers SET tbalance = tbalance + -3455 WHERE tid = 134;
UPDATE pgbench_branches SET bbalance = bbalance + -3455 WHERE bid = 78;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
VALUES (134, 78, 3789437, -3455, CURRENT_TIMESTAMP);
À la fin, s’affichent notamment le nombre de transactions (avec et
sans le temps de connexion) et la durée moyenne d’exécution du point de
vue du client (latency
) :
scaling factor: 100
query mode: simple
number of clients: 20
number of threads: 4
duration: 10 s
number of transactions actually processed: 20433
latency average = 9.826 ms
tps = 2035.338395 (including connections establishing) tps = 2037.198912 (excluding connections establishing)
Modifier le paramétrage est facile grâce à la variable
d’environnement PGOPTIONS
:
PGOPTIONS='-c synchronous_commit=off -c commit_siblings=20' \
pgbench -U pgbench -c 20 -j 4 -T100 pgbench 2>/dev/null
latency average = 6.992 ms
tps = 2860.465176 (including connections establishing) tps = 2862.964803 (excluding connections establishing)
Des tests rigoureux doivent durer bien sûr beaucoup plus longtemps que 100 s, par exemple pour tenir compte des effets de cache, des checkpoints périodiques, etc.
La version en ligne des solutions de ces TP est disponible sur https://dali.bo/j1_solutions.
Créer une base pgbench sur laquelle seront effectués les premiers tests, et l’initialiser avec 13,5 millions de lignes dans la table
pgbench_accounts
:# en tant qu'utilisateur postgres createdb pgbench /usr/pgsql-17/bin/pgbench -i -s 135 pgbench
Simuler l’utilisation de la base pgbench par 3 clients simultanés, chacun effectuant par exemple 1000 transactions :
# en tant que postgres /usr/pgsql-17/bin/pgbench -c 3 -t 1000 -n -P1 pgbench
Simuler la même chose, en ajoutant le paramètre
-C
pour forcer une connexion à chaque transaction. Comparer la latence et le nombre de transactions par seconde.
On veut simuler l’utilisation de la base pgbench par 3 utilisateurs effectuant 10 fois la sélection des comptes dont le solde est positif dans la table
pgbench_accounts
.Créer un fichier
query.sql
contenant la requête suivante :SELECT aid,bid,abalance FROM pgbench_accounts WHERE abalance > 0;
Lancer la commande :
/usr/pgsql-17/bin/pgbench -c 3 -t 10 -n -P1 -f query.sql pgbench
Positionner le paramètre
fsync
àoff
dans le fichierpostgresql.conf
et redémarrer PostgreSQL.
Simuler l’utilisation de la base pgbench par 3 clients simultanés, chacun effectuant par exemple 1000 transactions :
/usr/pgsql-17/bin/pgbench -c 3 -t 1000 -n -P1 pgbench
Comparer avec le premier test précédent.
Rétablir
fsync
àon
, puis relancer PostgreSQL.
Refaire le même test avec
synchronous_commit
àoff
dans la session :PGOPTIONS='-c synchronous_commit=off' \ /usr/pgsql-17/bin/pgbench -c 3 -t 1000 pgbench
Ce TP étant complexe, allez directement suivre la partie Solution.
Créer une base pgbench sur laquelle seront effectués les premiers tests, et l’initialiser avec 13,5 millions de lignes dans la table
pgbench_accounts
:# en tant qu'utilisateur postgres createdb pgbench /usr/pgsql-17/bin/pgbench -i -s 135 pgbench
Noter que le nom de la base (ici en dernier paramètre) ne doit pas
être précédé de -d
.
La base obtenue fait 2 Go.
Simuler l’utilisation de la base pgbench par 3 clients simultanés, chacun effectuant par exemple 1000 transactions :
# en tant que postgres /usr/pgsql-17/bin/pgbench -c 3 -t 1000 -n -P1 pgbench
…
progress: 1.0 s, 1081.9 tps, lat 2.743 ms stddev 3.450, 0 failed
progress: 2.0 s, 1127.0 tps, lat 2.653 ms stddev 1.112, 0 failed
…
number of transactions per client: 1000
number of transactions actually processed: 3000/3000
number of failed transactions: 0 (0.000%)
latency average = 2.691 ms
latency stddev = 2.933 ms
initial connection time = 8.860 ms tps = 1111.524022 (without initial connection time)
(Les chiffres peuvent bien sûr varier fortement selon la machine et la configuration.)
De vrais tests devraient être beaucoup plus longs !
Simuler la même chose, en ajoutant le paramètre
-C
pour forcer une connexion à chaque transaction. Comparer la latence et le nombre de transactions par seconde.
L’exécution est ici beaucoup plus longue :
/usr/pgsql-17/bin/pgbench -c 3 -t 1000 -n -P1 -C pgbench
…
progress: 7.0 s, 360.8 tps, lat 6.083 ms stddev 1.569, 0 failed
progress: 8.0 s, 342.4 tps, lat 6.392 ms stddev 1.943, 0 failed
…
number of transactions actually processed: 3000/3000
number of failed transactions: 0 (0.000%)
latency average = 6.021 ms
latency stddev = 1.876 ms
average connection time = 1.855 ms tps = 358.071199 (including reconnection times)
La connexion systématique a diminué les performances des deux tiers.
Ce serait encore pire s’il y avait un réseau entre PostgreSQL et client
pgbench
.
On veut simuler l’utilisation de la base pgbench par 3 utilisateurs effectuant 10 fois la sélection des comptes dont le solde est positif dans la table
pgbench_accounts
.Créer un fichier
query.sql
contenant la requête suivante :SELECT aid,bid,abalance FROM pgbench_accounts WHERE abalance > 0;
Lancer la commande :
/usr/pgsql-17/bin/pgbench -c 3 -t 10 -n -P1 -f query.sql pgbench
…
latency average = 1578.937 ms
latency stddev = 13.462 ms
initial connection time = 5.789 ms tps = 1.899930 (without initial connection time)
On voit ainsi que pgbench
permet de créer ses propres
requêtes de test.
pgbench
peut servir à mesurer l’impact d’un paramètre de
configuration sur les performances du système.
Positionner le paramètre
fsync
àoff
dans le fichierpostgresql.conf
et redémarrer PostgreSQL.
fsync = off
(Ne jamais faire cela en production, bien sûr.)
Relancer :
systemctl restart postgresql-17
Simuler l’utilisation de la base pgbench par 3 clients simultanés, chacun effectuant par exemple 1000 transactions :
/usr/pgsql-17/bin/pgbench -c 3 -t 1000 -n -P1 pgbench
Comparer avec le premier test précédent.
…
latency average = 0.424 ms
latency stddev = 0.173 ms
initial connection time = 6.861 ms tps = 6837.809439 (without initial connection time)
On est sur des valeurs beaucoup plus élevés que le premier test. La
synchronisation à chaque COMMIT
est très coûteuse et on l’a
désactivée. Mais fsync
à off
provoquerait une
corruption en cas d’arrêt brutal et ne peut être conservé.
Rétablir
fsync
àon
, puis relancer PostgreSQL.
fsync = on
sudo systemctl restart postgresql-17
Refaire le même test avec
synchronous_commit
àoff
dans la session :PGOPTIONS='-c synchronous_commit=off' \ /usr/pgsql-17/bin/pgbench -c 3 -t 1000 pgbench
La variable d’environnement est un moyen de changer la configuration
depuis le shell quand on ne peut changer la configuration de la session
avec SET
.
…
latency average = 0.473 ms
initial connection time = 13.312 ms tps = 6344.453045 (without initial connection time)
Le paramètre synchronous_commit
à off
« regroupe » les synchronisations, et permet de récupérer une bonne
partie des performances perdues avec fsync
à
on
, si l’application le permet : en cas d’arrêt brutal, il
reste le risque de perdre jusqu’à 600 ms de données pourtant committées
(mais sans risque de corruption). Cette perte de données est parfois
acceptable, parfois non. Ce choix peut être opéré transaction par
transaction en changeant synchronous_commit
dans la
session.
Noter à nouveau qu’un test rigoureux devrait durer beaucoup plus longtemps, sur plus de transactions, et tenir compte des effets de cache.
Ce TP a été testé sur une machine avec 3,5 Go de RAM et pas de swap à l’origine. Adaptez à votre machine.
Avec
free -m
, vérifiez que vous avez du swap. Si ce n’est pas le cas, créez un fichier de swap de 1 Go.
Si free -m
renvoie ceci, vous n’avez pas de
swap :
total used free shared buff/cache available
Mem: 3647 199 1984 53 1463 3168 Swap: 0 0 0
Comme il est conseillé d’avoir un swap minimal, le créer ainsi :
sudo fallocate --length 1GiB /swapfile
sudo chmod 0600 /swapfile
sudo mkswap /swapfile
sudo swapon /swapfile
sudo swapon --summary /swapfile
Au final nous obtenons ceci :
free -m
$ free -m
total used free shared buff/cache available
Mem: 3647 285 308 148 3053 2982 Swap: 1023 0 1023
Dans une autre session, générer de l’activité avec
pgbench
. (Si elle n’est pas déjà là, créer une base pgbench de taille 135 ou supérieure.) Laisser tourner indéfiniment dans cette session.
Pour créer la base pgbench au besoin :
# en tant qu'utilisateur postgres ; durée totale environ 1 minute
createdb --echo pgbench
/usr/pgsql-17/bin/pgbench pgbench -i -s 135
Pour générer de l’activité (ici 3 clients) :
# en tant qu'utilisateur postgres
# (on peut monter au-delà de 3 clients)
/usr/pgsql-17/bin/pgbench pgbench -c3 -T900 -P1 -n
pgbench (17.1)
progress: 1.0 s, 322.0 tps, lat 9.070 ms stddev 5.105, 0 failed
progress: 2.0 s, 315.0 tps, lat 9.511 ms stddev 3.501, 0 failed
progress: 3.0 s, 311.0 tps, lat 9.582 ms stddev 10.133, 0 failed
progress: 4.0 s, 349.0 tps, lat 8.647 ms stddev 3.215, 0 failed progress: 5.0 s, 325.0 tps, lat 9.199 ms stddev 4.445, 0 failed
Dans une nouvelle session, surveiller en permanence la mémoire :
while true ; do sleep 1 ; free -m ; done
total used free shared buff/cache available
Mem: 3647 263 110 143 3274 3008 Swap: 1023 54 969
En simplifiant :
total
indique les 3,5 Go de RAM ;used
correspond au système et à ce qu’utilise
PostgreSQL
shared_buffers
(valeur par défaut
ici) ;available
est la mémoire disponible, donc le
cache.Noter que la base de données (2 Go) tient dans le cache de Linux mais pas dans les shared buffers.
Consulter la configuration en place de l’overcommit.
sudo sysctl vm
…
vm.overcommit_memory = 0
vm.overcommit_ratio = 50
…
Ces valeurs sont celles par défaut sur les distributions courantes.
Dans une session
psql
, générer un tri en RAM bien plus gros que la mémoire disponible. Surveiller l’évolution sur la RAM et le débit des transactions de la sessionpgbench
(attendre une minute ou deux suivant la machine).
sudo -iu postgres psql
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 ;
Quels sont les symptômes dans les différentes sessions et les traces de PostgreSQL et du système ?
Les symptômes sont nombreux et n’apparaissent pas systématiquement :
la connexion au serveur a été coupée de façon inattendue
Le serveur s'est peut-être arrêté anormalement avant ou durant le
traitement de la requête.
La connexion au serveur a été perdue. Tentative de réinitialisation : Échec.
La connexion au serveur a été perdue. Tentative de réinitialisation : Échec. !?>
pgbench
ralentit fortement, et tombe
généralement en erreur aussi :
…
progress: 34.1 s, 1.7 tps, lat 627.322 ms stddev 0.038, 0 failed
progress: 35.1 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
progress: 36.8 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
progress: 38.5 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
progress: 39.0 s, 5.7 tps, lat 5782.505 ms stddev 151.744, 0 failed
progress: 41.5 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
progress: 42.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
…
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database
and repeat your command.
pgbench: error: client 0 aborted in command 5 (SQL) of script 0;
perhaps the backend died while processing
…
pgbench: error: client 2 aborted in command 10 (SQL) of script 0;
perhaps the backend died while processing
pgbench: error: client 1 aborted in command 10 (SQL) of script 0;
perhaps the backend died while processing
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 135
query mode: simple
number of clients: 3
number of threads: 1
maximum number of tries: 1
duration: 900 s
number of transactions actually processed: 5432
number of failed transactions: 0 (0.000%)
latency average = 58.651 ms
latency stddev = 1579.195 ms
initial connection time = 17.279 ms
tps = 51.090504 (without initial connection time) pgbench: error: Run was aborted; the above results are incomplete.
Noter la mention « The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory ».
free
indique que la mémoire utilisable
(available
) s’est réduite, ainsi que cache
, ce
qui est normal car used
indique que presque toute la RAM
est utilisée.
total used free shared buff/cache available
Mem: 3647 3331 103 139 213 26 Swap: 1023 960 63
Noter que le swap s’est rempli avec nos données triées, alors que nous avions demandé un tri en mémoire.
Cette opération d’écriture dans le swap indique que la mémoire est déjà saturée, et le cache déjà réduit. L’écriture va également alourdir les entrées-sorties.
Un cache surdimensionné allonge encore la durée du ralentissement.
Avec des disques mécaniques, c’est encore plus long.
postgresql.log
:
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: unexpected EOF on client connection with an open transaction
LOG: unexpected EOF on client connection with an open transaction
LOG: could not send data to client: Broken pipe
FATAL: connection to client lost
LOG: server process (PID 21071) was terminated by signal 9: Killed
DETAIL: Failed process was running: EXPLAIN (ANALYZE) SELECT i
FROM generate_series (1,3e9) i
ORDER BY i DESC ;
LOG: terminating any other active server processes
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted; last known up at 2024-05-06 19:11:52 CEST
FATAL: the database system is in recovery mode
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 0/E80042A8
… LOG: database system is ready to accept connections
On y lit :
l’arrêt brutal (rien de moins que kill -9
) du
processus 21071 consommateur de RAM ;
puis un redémarrage spontané de PostgreSQL.
Du côté du noyau :
sudo dmesg
# alternative :
# sudo journalctl -k -n 130
…
Adding 1048572k swap on /swapfile. Priority:-2 extents:95 across:3491508k FS
postgres invoked oom-killer: gfp_mask=0x6200ca(GFP_HIGHUSER_MOVABLE), order=0,
oom_score_adj=0
…
Mem-Info:
active_anon:60815 inactive_anon:818069 isolated_anon:0
active_file:0 inactive_file:56 isolated_file:0
unevictable:0 dirty:0 writeback:0
slab_reclaimable:7732 slab_unreclaimable:10962
mapped:35416 shmem:35635 pagetables:5149 bounce:0
free:20749 free_pcp:884 free_cma:0
…
…
106213 total pagecache pages
69943 pages in swap cache
Swap cache stats: add 517156, delete 447213, find 7357/9783
Free swap = 0kB
Total swap = 1048572kB
1047069 pages RAM
0 pages HighMem/MovableOnly
113258 pages reserved
0 pages hwpoisoned
Tasks state (memory values in pages):
uid tgid total_vm rss pgtables_bytes swapents oom_score_adj name
0 625 24466 720 237568 236 0 systemd-journal
0 654 24758 380 200704 334 -1000 systemd-udevd
32 728 16814 134 172032 48 0 rpcbind
0 730 32730 102 139264 113 -1000 auditd
0 732 12163 93 147456 2 0 sedispatch
2 771 77274 171 225280 105 0 rngd
81 773 16170 169 147456 60 -900 dbus-daemon
0 774 33817 138 167936 38 0 qemu-ga
0 775 31244 115 143360 47 0 irqbalance
0 776 20886 297 200704 283 0 systemd-logind
992 779 35021 137 176128 66 0 chronyd
0 817 149191 503 401408 232 0 NetworkManager
0 823 192145 2200 442368 2485 0 tuned
0 833 78725 87 192512 79 0 gssproxy
998 879 419423 436 352256 1309 0 polkitd
0 1050 70813 378 204800 2075 0 rsyslogd
0 1076 58908 161 106496 55 0 crond
0 1077 54903 25 65536 4 0 agetty
0 1078 54813 27 73728 3 0 agetty
0 1828 19164 38 184320 196 -1000 sshd
1001 5607 22393 244 212992 98 0 systemd
1001 5610 58736 212 327680 1021 0 (sd-pam)
1001 5650 60090 106 106496 129 0 tmux: server
1001 5651 56270 135 81920 1 0 bash
0 13055 34207 119 307200 210 0 sshd
1001 13058 34207 134 307200 193 0 sshd
1001 13059 56270 3 77824 141 0 bash
0 16555 94770 50 98304 35 0 gpg-agent
0 16593 94770 48 98304 36 0 gpg-agent
0 16632 94770 48 98304 36 0 gpg-agent
0 16671 94770 48 98304 35 0 gpg-agent
0 16710 94770 48 114688 35 0 gpg-agent
0 16749 94770 83 102400 1 0 gpg-agent
26 17768 125350 2076 299008 267 -1000 postgres
26 17769 88301 45 229376 318 0 postgres
26 17770 125484 29199 536576 321 0 postgres
26 17771 125383 32478 528384 300 0 postgres
26 17773 125383 1171 253952 306 0 postgres
26 17774 125752 398 278528 349 0 postgres
26 17775 125747 216 262144 394 0 postgres
1001 18313 83277 41 282624 227 0 sudo
26 18315 56227 138 90112 18 0 bash
0 18739 34207 164 303104 166 0 sshd
1001 18744 34207 162 299008 166 0 sshd
1001 18745 56270 84 86016 61 0 bash
0 20944 34207 306 315392 23 0 sshd
1001 20947 34207 285 307200 42 0 sshd
1001 20948 56270 86 73728 59 0 bash
1001 21015 83277 151 290816 116 0 sudo
26 21017 68623 227 172032 14 0 psql
26 21071 1231817 765295 8413184 245955 0 postgres
1001 21284 55992 151 86016 0 0 watch
26 21303 14128 180 147456 0 0 pgbench
26 21305 125886 15729 544768 412 0 postgres
26 21306 125886 16012 544768 415 0 postgres
26 21307 125886 15635 544768 413 0 postgres
1001 21444 55992 151 73728 0 0 watch
1001 21445 10459 58 114688 0 0 free
26 21450 125383 191 245760 249 0 postgres
0 21459 12214 49 118784 0 0 sshd
oom-kill:constraint=CONSTRAINT_NONE,nodemask=(null),cpuset=/,mems_allowed=0,
global_oom,task_memcg=/system.slice/postgresql-17.service,task=postgres,
pid=21071,uid=26
Out of memory: Killed process 21071 (postgres) total-vm:4927268kB,
anon-rss:3057476kB, file-rss:4kB, shmem-rss:3700kB, UID:26 pgtables:8216kB oom_score_adj:0
On trouvera ci-dessus :
La cause première est l’épuisement complet de la mémoire, Linux n’ayant jamais refusé de réservation mémoire. Une fois RAM et swap épuisés, il a bien fallu éliminer un processus. Sur une machine dédiée à PostgreSQL, c’est forcément un de ses processus.
Ici, le processus principal est protégé (noter ci-dessus le score de -1000, qui provient de la définition du service). Comme le processus principal était encore là, il a constaté la disparition brutale de son processus fils, et a tout redémarré par peur d’une corruption de la mémoire partagée.
L’arrêt des connexions (session psql
et
pgbench
) est la conséquence.
Changer le paramétrage mémoire pour éviter le plantage.
La nouvelle configuration mémoire est à mettre en place ainsi :
sudo vi /etc/sysctl.conf
# alternativement : un fichier /etc/sysctl.d/99-overcommit.conf
Dans ce fichier :
# Protection d'environ 10% de la mémoire
vm.overcommit_memory=2
vm.overcommit_ratio=60
# réduction de la propension à swapper
vm.swappiness=10
Sur des machines mieux dotées en RAM et avec peu de swap,
vm.overcommit_ratio
vaut généralement 80.
# activation de la nouvelle configuration
sudo sysctl --system
…
* Applying /etc/sysctl.conf ...
vm.swappiness = 10
vm.overcommit_memory = 2 vm.overcommit_ratio = 60
Consultation de la nouvelle configuration mémoire :
cat /proc/meminfo
MemTotal: 3735244 kB
MemFree: 2598780 kB
MemAvailable: 3204396 kB
Buffers: 0 kB
Cached: 967368 kB
SwapCached: 7396 kB
Active: 158296 kB
Inactive: 843860 kB
Active(anon): 24392 kB
Inactive(anon): 190468 kB
Active(file): 133904 kB
Inactive(file): 653392 kB
Unevictable: 0 kB
Mlocked: 0 kB
SwapTotal: 1048572 kB
SwapFree: 996324 kB
Dirty: 4 kB
Writeback: 0 kB
AnonPages: 29708 kB
Mapped: 188868 kB
Shmem: 180072 kB
KReclaimable: 32156 kB
Slab: 74836 kB
SReclaimable: 32156 kB
SUnreclaim: 42680 kB
KernelStack: 2576 kB
PageTables: 9228 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
WritebackTmp: 0 kB
CommitLimit: 3289716 kB
Committed_AS: 674624 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 17640 kB
VmallocChunk: 0 kB
Percpu: 1888 kB
HardwareCorrupted: 0 kB
AnonHugePages: 2048 kB
ShmemHugePages: 0 kB
ShmemPmdMapped: 0 kB
FileHugePages: 0 kB
FilePmdMapped: 0 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
Hugetlb: 0 kB
DirectMap4k: 156152 kB
DirectMap2M: 4032512 kB DirectMap1G: 2097152 kB
On y voit notamment :
grep -E '^(Swap|Commit)' /proc/meminfo
SwapCached: 7396 kB
SwapTotal: 1048572 kB
SwapFree: 996324 kB
CommitLimit: 3289716 kB Committed_AS: 679524 kB
CommitLimit
(l’allocation maximale) est bien le résultat
du calcul :
CommitLimit = RAM × 60 % + swap = 3735244 × 60 % + 1048572 = 3289718
(des écarts de quelques kilooctets sont normaux).
Base inactive, il est normal que la RAM allouée
(CommittedAS
) soit basse.
Relancer la requête consommatrice de RAM en suivant à nouveau la consommation mémoire et les traces. Ne pas hésiter à tester plusieurs fois.
Le traçage de la mémoire peut se faire de plusieurs manières :
while true ; do sleep 1 ; free -m ; done
total used free shared buff/cache available
Mem: 3647 134 2536 175 976 3128 Swap: 1023 51 972
while true; do sleep 1; watch -n 0.5 "grep -E '^(Swap|Commit)' /proc/meminfo";done
ou
sar -rh 1
Le swap est à peu près vide, la moitié de la mémoire complètement libre.
On relance l’activité pgbench
:
# en tant qu'utilisateur postgres
/usr/pgsql-17/bin/pgbench pgbench -c3 -T900 -P1 -n
Sous psql
:
SET work_mem = '1000GB' ;
EXPLAIN (ANALYZE) SELECT i FROM generate_series (1,3e9) i
ORDER BY i DESC ;
Ce dernier ordre tombe avec cette seule erreur :
ERROR: out of memory DÉTAIL : Failed on request of size 23 in memory context "ExecutorState".
Par contre la session reste utilisable.
pgbench
continue de tourner normalement :
progress: 215.0 s, 242.0 tps, lat 18.192 ms stddev 64.768, 0 failed
progress: 216.0 s, 223.9 tps, lat 13.435 ms stddev 8.988, 0 failed
progress: 217.0 s, 252.1 tps, lat 11.852 ms stddev 4.051, 0 failed progress: 218.0 s, 251.0 tps, lat 11.960 ms stddev 4.252, 0 failed
Il est difficile de capturer des valeurs juste avant l’erreur avec un
pas d’une seconde, mais par exemple /proc/meminfo
contient
ceci :
SwapCached: 6072 kB
SwapTotal: 1048572 kB
SwapFree: 981316 kB
CommitLimit: 3289716 kB Committed_AS: 3144000 kB
Committed_AS
(mémoire consommée et swap) est
alors très proche de CommitLimit
.
Ou encore, free -m
a retourné ceci :
total used free shared buff/cache available
Mem: 3647 2405 159 164 1083 868 Swap: 1023 65 958
ou encore avec sar -rh 1
(sortie modifiée pour
l’affichage) :
kbmemfree kbavail kbmemused %memused kbbuffers kbcached kbcommit %commit…
1,0G 3,0G 2,6G 71,8% 0,0k 2,4G 707,2M 15,1%…
1,0G 3,0G 2,6G 71,9% 0,0k 2,4G 707,2M 15,1%…
…
595,7M 2,9G 3,0G 83,7% 0,0k 2,7G 852,0M 18,2%…
449,5M 2,8G 3,1G 87,7% 0,0k 2,7G 1004,0M 21,5%…
…
kbmemfree kbavail kbmemused %memused kbbuffers kbcached kbcommit %commit…
101,9M 1,8G 3,5G 97,2% 0,0k 2,0G 2,0G 43,1%…
99,8M 1,7G 3,5G 97,3% 0,0k 1,9G 2,1G 45,1%…
105,4M 1,6G 3,5G 97,1% 0,0k 1,8G 2,2G 47,3%…
96,8M 1,4G 3,5G 97,3% 0,0k 1,7G 2,5G 55,1%…
106,0M 1,3G 3,5G 97,1% 0,0k 1,6G 2,6G 57,5%…
103,1M 1,2G 3,5G 97,2% 0,0k 1,4G 2,7G 60,0%…
103,7M 1,1G 3,5G 97,2% 0,0k 1,3G 2,8G 62,4%…
115,2M 1019,1M 3,4G 96,8% 0,0k 1,2G 3,0G 64,8%… 102,9M 925,4M 3,5G 97,2% 0,0k 1,1G 3,0G 66,7%…
Ces outils n’ont pas forcément les mêmes calculs de répartition de la mémoire, mais on peut constater dans les trois cas :
Dans les traces de PostgreSQL apparaît la répartition mémoire du processus tombé en erreur :
TopPortalContext: 8192 total in 1 blocks; 7664 free (0 chunks); 528 used
PortalHoldContext: 24624 total in 2 blocks; 7400 free (0 chunks); 17224 used
PortalContext: 16384 total in 5 blocks; 5464 free (1 chunks); 10920 used: <unnamed>
ExecutorState: 2553282608 total in 254 blocks; 2224 free (12 chunks); 2553280384 used
SRF multi-call context: 1024 total in 1 blocks; 504 free (1 chunks); 520 used
TupleSort main: 32816 total in 2 blocks; 4928 free (0 chunks); 27888 used
TupleSort sort: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
Caller tuples: 8192 total in 1 blocks (0 chunks); 7984 free (0 chunks); 208 used
Table function arguments: 8192 total in 1 blocks; 7856 free (0 chunks); 336 used
ExprContext: 8192 total in 1 blocks; 7904 free (0 chunks); 288 used
Relcache by OID: 16384 total in 2 blocks; 3584 free (2 chunks); 12800 used
CacheMemoryContext: 524288 total in 7 blocks; 98664 free (0 chunks); 425624 used
index info: 2048 total in 2 blocks; 584 free (2 chunks); 1464 used: pg_db_role_setting_databaseid_rol_index
…
index info: 2048 total in 2 blocks; 912 free (0 chunks); 1136 used: pg_authid_rolname_index
WAL record construction: 50200 total in 2 blocks; 6376 free (0 chunks); 43824 used
PrivateRefCount: 8192 total in 1 blocks; 2648 free (0 chunks); 5544 used
MdSmgr: 8192 total in 1 blocks; 7688 free (0 chunks); 504 used
LOCALLOCK hash: 8192 total in 1 blocks; 592 free (0 chunks); 7600 used
GUCMemoryContext: 24576 total in 2 blocks; 12240 free (4 chunks); 12336 used
GUC hash table: 32768 total in 3 blocks; 12704 free (5 chunks); 20064 used
Timezones: 104112 total in 2 blocks; 2648 free (0 chunks); 101464 used
ErrorContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
Grand total: 2554573880 bytes in 448 blocks; 346152 free (151 chunks); 2554227728 used
ERROR: out of memory
DETAIL: Failed on request of size 23 in memory context "ExecutorState".
STATEMENT: EXPLAIN (ANALYZE) SELECT i FROM generate_series (1,3e9) i ORDER BY i DESC ;
Noter à la fin le GrandTotal
à 2,5 Go.
Contrairement à la configuration par défaut, la session s’est vu
refuser par le noyau la réservation de mémoire au moment où
CommittedAS
dépassait CommitLimit
. PostgreSQL
sait gérer ce genre d’erreur et a arrêté la requête, sans tout
redémarrer.
Ce module a pour but de faire une présentation très rapide de
l’optimiseur et des plans d’exécution. Il contient surtout une
introduction sur la commande EXPLAIN
et sur différents
outils en relation.
Lorsque le serveur récupère la requête, un ensemble de traitements est réalisé.
Tout d’abord, le parser va réaliser une analyse syntaxique de la requête.
Puis le rewriter va réécrire, si nécessaire, la requête. Pour cela, il prend en compte les règles, les vues non matérialisées et les fonctions SQL.
Si une règle demande de changer la requête, la requête envoyée est remplacée par la nouvelle.
Si une vue non matérialisée est utilisée, la requête qu’elle contient est intégrée dans la requête envoyée. Il en est de même pour une fonction SQL intégrable.
Ensuite, le planner va générer l’ensemble des plans d’exécutions. Il calcule le coût de chaque plan, puis il choisit le plan le moins coûteux, donc le plus intéressant.
Enfin, l’executer exécute la requête.
Pour cela, il doit commencer par récupérer les verrous nécessaires sur les objets ciblés. Une fois les verrous récupérés, il exécute la requête.
Une fois la requête exécutée, il envoie les résultats à l’utilisateur.
Plusieurs goulets d’étranglement sont visibles ici. Les plus importants sont :
En général, le principal souci pour les performances sur ce type
d’instructions est donc l’obtention des verrous et l’exécution réelle de
la requête. Il existe quelques ordres (comme TRUNCATE
ou
COPY
) exécutés beaucoup plus directement.
Les moteurs de base de données utilisent un langage SQL qui permet à l’utilisateur de décrire le résultat qu’il souhaite obtenir, mais pas la manière. C’est à la base de données de se débrouiller pour obtenir ce résultat le plus rapidement possible.
Le but de l’optimiseur est assez simple. Pour une requête, il existe de nombreux plans d’exécution possibles. Il va donc énumérer tous les plans d’exécution possibles (sauf si cela représente vraiment trop de plans auquel cas, il ne prendra en compte qu’une partie des plans possibles).
Pour calculer le « coût » d’un plan, PostgreSQL dispose d’informations sur les données (des statistiques), d’une configuration (réalisée par l’administrateur de bases de données) et d’un ensemble de règles inscrites en dur.
À la fin de l’énumération et du calcul de coût, il ne lui reste plus qu’à sélectionner le plan qui a le plus petit coût.
Le coût d’un plan est une valeur calculée sans unité ni signification physique.
La requête en exemple permet de récupérer des informations sur tous les employés dont le nom commence par la lettre B en triant les employés par leur service.
Un moteur de bases de données peut récupérer les données de plusieurs façons :
employes
en
filtrant les enregistrements d’après leur nom, puis trier les données
grâce à un algorithme ;nom
pour trouver plus rapidement les enregistrements de la
table employes
satisfaisant le filtre 'B%'
,
puis trier les données grâce à un algorithme ;num_service
pour récupérer les enregistrements déjà triés par service, et ne
retourner que ceux vérifiant le prédicat
nom like 'B%'
.Et ce ne sont que quelques exemples, car il serait possible d’avoir un index utilisable à la fois pour le tri et le filtre par exemple.
Donc la requête décrit le résultat à obtenir, et le planificateur va
chercher le meilleur moyen pour parvenir à ce résultat. Pour ce travail,
il dispose d’un certain nombre d’opérations de base. Ces opérations
travaillent sur des ensembles de lignes, généralement un ou deux. Chaque
opération renvoie un seul ensemble de lignes. Le planificateur peut
combiner ces opérations suivant certaines règles. Une opération peut
renvoyer l’ensemble de résultats de deux façons : d’un coup (par exemple
le tri) ou petit à petit (par exemple un parcours séquentiel). Le
premier cas utilise plus de mémoire, et peut nécessiter d’écrire des
données temporaires sur disque. Le deuxième cas aide à accélérer des
opérations comme les curseurs, les sous-requêtes IN
et
EXISTS
, la clause LIMIT
, etc.
Pour exécuter une requête, le planificateur va utiliser des opérations. Pour lire des lignes, il peut utiliser un parcours de table (une lecture complète du fichier), un parcours d’index ou encore d’autres types de parcours. Ce sont généralement les premières opérations utilisées.
Pour joindre les tables, l’ordre dans lequel ce sera fait est très
important. Pour la jointure elle-même, il existe plusieurs méthodes
différentes. Il existe aussi plusieurs algorithmes d’agrégation de
lignes. Un tri peut être nécessaire pour une jointure, une agrégation,
ou pour un ORDER BY
, et là encore il y a plusieurs
algorithmes possibles, ou des techniques pour éviter de le faire.
L’optimiseur statistique de PostgreSQL utilise un modèle de calcul de coût. Les coûts calculés sont des indications arbitraires de la charge nécessaire pour répondre à une requête. Chaque facteur de coût représente une unité de travail : lecture d’un bloc, manipulation d’une ligne en mémoire, application d’un opérateur sur un champ.
En plus du coût unitaire de traitement d’une ligne, il faut connaître
le nombre total de lignes à traiter pour calculer le coût total.
L’optimiseur a donc besoin d’informations sur les données, comme par
exemple le nombre de blocs et de lignes d’une table, les valeurs les
plus fréquentes et leur fréquence, pour chaque colonne de chaque table.
Ces statistiques sur les données sont calculées lors de l’exécution de
la commande SQL ANALYZE
.
Le processus autovacuum permet de rafraîchir régulièrement les statistiques.
Le propriétaire de la table ou un superutilisateur peut également le
faire manuellement. À partir de PostgreSQL 17, il est possible de créer
un utilisateur dédié à la maintenance, qui, sans avoir de droits de
lecture sur les tables, peut exécuter ces commandes. Cet utilisateur
pourra intervenir soit sur toutes les tables s’il possède le rôle
pg_maintain
, soit sur des tables spécifiques si un
GRANT MAINTAIN
lui a été accordé.
Des statistiques périmées ou pas assez fines sont une source fréquente de plans non optimaux !
L’exemple crée une table et lui ajoute 1000 lignes. Chaque ligne a
une valeur différente dans les colonnes c1
et
c2
(de 1 à 1000).
SELECT * FROM t1 ;
c1 | c2
------+------
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
6 | 6
…
996 | 996
997 | 997
998 | 998
999 | 999
1000 | 1000 (1000 lignes)
Dans cette requête :
EXPLAIN SELECT * FROM t1 WHERE c1=1 ;
nous savons qu’un SELECT
filtrant sur la valeur 1 pour
la colonne c1
ne ramènera qu’une ligne. Grâce aux
statistiques relevées par la commande ANALYZE
exécutée
juste avant, l’optimiseur estime lui aussi qu’une seule ligne sera
récupérée. Une ligne sur 1000, c’est un bon ratio pour faire un parcours
d’index. C’est donc ce que recommande l’optimiseur.
La même table, mais avec 1000 lignes ne contenant plus que la valeur
1. Un SELECT
filtrant sur cette valeur 1 ramènera dans ce
cas toutes les lignes. L’optimiseur s’en rend compte et décide qu’un
parcours séquentiel de la table est préférable à un parcours d’index.
C’est donc ce que recommande l’optimiseur.
Dans cet exemple, l’ordre ANALYZE
garantit que les
statistiques sont à jour (le démon autovacuum n’est pas forcément assez
rapide).
Le coût du parcours de table était de 21,5 pour la récupération des
1000 lignes, donc un coût bien supérieur au coût du parcours d’index,
qui lui était de 8,29, mais pour une seule ligne. On pourrait se
demander le coût du parcours d’index pour 1000 lignes. À titre
expérimental, on peut désactiver (ou plus exactement désavantager) le
parcours de table en configurant le paramètre
enable_seqscan
à off
.
En faisant cela, on s’aperçoit que le plan passe finalement par un parcours d’index, tout comme le premier. Par contre, le coût n’est plus de 8,29, mais de 57,77, donc supérieur au coût du parcours de table. C’est pourquoi l’optimiseur avait d’emblée choisi un parcours de table. Un index n’est pas forcément le chemin le plus court.
L’optimiseur transforme une grosse action (exécuter une requête) en plein de petites actions unitaires (trier un ensemble de données, lire une table, parcourir un index, joindre deux ensembles de données, etc). Ces petites actions sont liées les unes aux autres. Par exemple, pour exécuter cette requête :
SELECT * FROM une_table ORDER BY une_colonne;
peut se faire en deux actions :
Mais ce n’est qu’une des possibilités.
Les nœuds correspondent à des unités de traitement qui réalisent des opérations simples sur un ou deux ensembles de données : lecture d’une table, jointures entre deux tables, tri d’un ensemble, etc. Si le plan d’exécution était une recette, chaque nœud serait une étape de la recette.
Les nœuds peuvent produire et consommer des données.
Pour récupérer le plan d’exécution d’une requête, il suffit
d’utiliser la commande EXPLAIN
. Cette commande est suivie
de la requête pour laquelle on souhaite le plan d’exécution.
Seul le plan sélectionné est affichable. Les plans ignorés du fait de leur coût trop important ne sont pas récupérables. Ceci est dû au fait que les plans en question peuvent être abandonnés avant d’avoir été totalement développés si leur coût partiel est déjà supérieur à celui de plans déjà considérés.
Cette requête va récupérer tous les enregistrements de t1 pour lesquels la valeur de la colonne c2 est inférieure à 10. Les enregistrements sont triés par rapport à la colonne c1.
L’optimiseur envoie ce plan à l’exécuteur. Ce dernier voit qu’il a
une opération de tri à effectuer (nœud Sort
). Pour cela, il
a besoin de données que le nœud suivant va lui donner. Il commence donc
l’opération de lecture (nœud SeqScan
). Il envoie chaque
enregistrement valide au nœud Sort
pour que ce dernier les
trie.
Chaque nœud dispose d’un certain nombre d’informations placées soit
sur la même ligne entre des parenthèses, soit sur la ou les lignes du
dessous. La différence entre une ligne de nœud et une ligne
d’informations est que la ligne de nœud contient une flèche au début
(->
). Par exemple, le nœud Sort
contient
des informations entre des parenthèses et une information supplémentaire
sur la ligne suivante indiquant la clé de tri (la colonne
c1
). Par contre, la troisième ligne n’est pas une ligne
d’informations du nœud Sort
mais un nouveau nœud
(SeqScan
).
Chaque nœud montre les coûts estimés dans le premier groupe de
parenthèses. cost
est un couple de deux coûts : la première
valeur correspond au coût pour récupérer la première ligne (souvent nul
dans le cas d’un parcours séquentiel) ; la deuxième valeur correspond au
coût pour récupérer toutes les lignes (elle dépend essentiellement de la
taille de la table lue, mais aussi d’opération de filtrage).
rows
correspond au nombre de lignes que le planificateur
pense récupérer à la sortie de ce nœud. Dans le cas d’une nouvelle table
traitée par ANALYZE
, les versions antérieures à la version
14 calculaient une valeur probable du nombre de lignes en se basant sur
la taille moyenne d’une ligne et sur une table faisant 10 blocs. La
version 14 corrige cela en ayant une meilleure idée du nombre de lignes
d’une nouvelle table. width
est la largeur en octets de la
ligne.
Les informations supplémentaires dépendent de beaucoup d’éléments.
Elles peuvent différer suivant le type de nœud, les options de la
commande EXPLAIN
, et certains paramètres de configuration.
De même la version de PostgreSQL joue un rôle majeur : les nouvelles
versions peuvent apporter des informations supplémentaires pour que le
plan soit plus lisible et que l’utilisateur soit mieux informé.
Le but de cette option est d’obtenir les informations sur l’exécution réelle de la requête.
Avec ANALYZE
, la requête est réellement exécutée !
Attention donc aux
INSERT
/UPDATE
/DELETE
. N’oubliez
pas non plus qu’un SELECT
peut appeler des fonctions qui
écrivent dans la base. Dans le doute, pensez à englober l’appel dans une
transaction que vous annulerez après coup.
Quatre nouvelles informations apparaissent dans un nouveau bloc de parenthèses. Elles sont toutes liées à l’exécution réelle de la requête :
actual time
rows
est le nombre de lignes réellement
récupérées ;loops
est le nombre d’exécutions de ce nœud, soit dans
le cadre d’une jointure, soit dans le cadre d’une requête
parallélisée.Multiplier la durée par le nombre de boucles pour obtenir la durée réelle d’exécution du nœud !
L’intérêt de cette option est donc de trouver l’opération qui prend du temps dans l’exécution de la requête, mais aussi de voir les différences entre les estimations et la réalité (notamment au niveau du nombre de lignes).
BUFFERS
fait apparaître le nombre de blocs
(buffers) impactés par chaque nœud du plan d’exécution, en
lecture comme en écriture.
Il est conseillé de l’activer systématiquement. La quantité de blocs manipulés par une requête est souvent surprenante, et souvent la cause d’une mauvaise performance.
shared read=5
en bas signifie que 5 blocs ont été
trouvés et lus hors du cache de PostgreSQL (shared
buffers). 5 blocs est ici la taille de t1
sur le
disque. Le cache de l’OS est peut-être intervenu, ce n’est pas visible
ici. Un peu plus haut, shared hit=3 read=5
indique que 3
blocs ont été lus dans ce cache, et 5 autres toujours hors du cache. Les
valeurs exactes dépendent donc de l’état du cache. Si on relance la
requête, pour une telle petite table, les relectures se feront
uniquement en shared hit
.
BUFFERS
compte aussi les blocs de fichiers ou tables
temporaires (temp
ou local
), ou les blocs
écrits sur disque (written
).
EXPLAIN (ANALYZE, BUFFERS)
n’affiche que des données
réelles, pas des estimations. EXPLAIN (BUFFERS)
sans
ANALYZE
peut être utilisé, mais il ne montre que les blocs
utilisés par la planification, plutôt que accédés à l’exécution. Ces
blocs sont surtout des appels aux tables systèmes, et sont moins
nombreux, voire absents, quand on appelle la requête une deuxième fois,
ou plus, dans la même session.
Désactivée par défaut et nécessitant l’option ANALYZE
,
l’option WAL
permet d’obtenir le nombre d’enregistrements
et le nombre d’octets écrits dans les journaux de transactions.
(Rappelons que les écritures dans les fichiers de données se font
généralement plus tard, en arrière-plan.) Il est conseillé de l’activer
systématiquement, même pour les SELECT
.
Cette option, apparue avec PostgreSQL 17, force la « sérialisation »
du résultat de la requête, et met ainsi en ainsi en évidence sa
participation dansle temps d’exécution total de la requête et volumétrie
de données a transférer. Sans cette option,
EXPLAIN (ANALYZE)
peut afficher un résultat trop optimiste
par rapport aux requêtes réelles. L’exemple montre l’impact de la
lecture (pas forcément voulue) de la partie TOAST de la table, où sont
relégués les gros champs textes ou binaires, à cause du
SELECT *
. Le coût réseau n’est toutefois pas inclus.
L’option GENERIC_PLAN
n’est malheureusement pas
disponible avant PostgreSQL 16. Elle est pourtant très pratique quand on
cherche le plan d’une requête préparée sans connaître ses paramètres, ou
pour savoir quel est le plan générique que prévoit PostgreSQL pour une
requête préparée.
En effet, les plans des requêtes préparées ne sont pas forcément
recalculés à chaque appel avec les paramètres exacts (le système est
assez complexe et dépend du paramètre plan_cache_mode
). La
requête ne peut être exécutée sans vraie valeur de paramètre, donc
l’option ANALYZE
est inutilisable, mais en activant
GENERIC_PLAN
on peut tout de même voir le plan générique
que PostgreSQL peut choisir (SUMMARY ON
affiche en plus le
temps de planification) :
EXPLAIN (GENERIC_PLAN, SUMMARY ON)
SELECT * FROM t1 WHERE c1 < $1 ;
QUERY PLAN
-----------------------------------------------------------------------
Index Scan using t1_c1_idx on t1 (cost=0.15..14.98 rows=333 width=8)
Index Cond: (c1 < $1) Planning Time: 0.195 ms
C’est effectivement le plan qui serait optimal pour
$1
=1. Mais pour la valeur 1000, qui ramène toute la table,
un Seq Scan serait plus pertinent.
Désactivée par défaut, l’option SETTINGS
permet
d’obtenir la liste des paramètres influant sur la planification et qui
ne sont pas à leur valeur par défaut pour la session ou la requête en
cours. Il est conseillé de l’activer systématiquement. Elle est pratique
quand il faut transmettre le plan à un collègue ou un prestataire qui
n’a pas forcément accès à la machine et à sa configuration.
Ces options sont moins utilisées, mais certaines restent intéressantes dans des cas précis.
Option VERBOSE
N’hésitez pas à utiliser l’option VERBOSE
pour afficher
des informations supplémentaires comme :
postgres_fdw
notamment).Dans l’exemple suivant, le nom du schéma est ajouté au nom de la
table. La nouvelle ligne Output
indique la liste des
colonnes de l’ensemble de données en sortie du nœud.
EXPLAIN (VERBOSE) SELECT * FROM t1 WHERE c2<10 ORDER BY c1 ;
QUERY PLAN
----------------------------------------------------------------
Sort (cost=21.64..21.67 rows=9 width=8)
Output: c1, c2
Sort Key: t1.c1
-> Seq Scan on public.t1 (cost=0.00..21.50 rows=9 width=8)
Output: c1, c2 Filter: (t1.c2 < 10)
Option MEMORY
À partir de PostgreSQL 17, cette option affiche la mémoire consommée par le planificateur. Ce n’est utile que pour les requêtes compliquées. Cet exemple montre que même pour une requête très basique, un peu de mémoire est utilisée uniquement pour la planification :
EXPLAIN (MEMORY) SELECT relname FROM pg_class ;
QUERY PLAN
------------------------------------------------------------
Seq Scan on pg_class (cost=0.00..27.27 rows=527 width=64)
Planning: Memory: used=9kB allocated=16kB
Option COSTS
Cette option est activée par défaut. Il peut être intéressant de la désactiver pour n’avoir que le plan.
EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE c2<10 ORDER BY c1 ;
QUERY PLAN
---------------------------
Sort
Sort Key: c1
-> Seq Scan on t1 Filter: (c2 < 10)
Option TIMING
Cette option est activée par défaut. Il peut être intéressant de le désactiver sur les systèmes où le chronométrage prend beaucoup de temps et allonge inutilement la durée d’exécution de la requête. Mais de ce fait, le résultat devient beaucoup moins intéressant.
EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t1 WHERE c2<10 ORDER BY c1 ;
QUERY PLAN
---------------------------------------------------------------------------------
Sort (cost=21.64..21.67 rows=9 width=8) (actual rows=9 loops=1)
Sort Key: c1
Sort Method: quicksort Memory: 25kB
-> Seq Scan on t1 (cost=0.00..21.50 rows=9 width=8) (actual rows=9 loops=1)
Filter: (c2 < 10)
Rows Removed by Filter: 991
Planning Time: 0.155 ms Execution Time: 0.381 ms
Option SUMMARY
Elle permet d’afficher ou non le résumé final indiquant la durée de
la planification et de l’exécution. Un EXPLAIN
simple
n’affiche pas le résumé par défaut (la durée de planification est
pourtant parfois importante). Par contre, un
EXPLAIN ANALYZE
l’affiche par défaut.
EXPLAIN (SUMMARY ON) SELECT * FROM t1 WHERE c2<10 ORDER BY c1;
QUERY PLAN
---------------------------------------------------------
Sort (cost=21.64..21.67 rows=9 width=8)
Sort Key: c1
-> Seq Scan on t1 (cost=0.00..21.50 rows=9 width=8)
Filter: (c2 < 10) Planning Time: 0.185 ms
EXPLAIN (ANALYZE, SUMMARY OFF) SELECT * FROM t1 WHERE c2<10 ORDER BY c1;
QUERY PLAN
---------------------------------------------------------
Sort (cost=21.64..21.67 rows=9 width=8)
(actual time=0.343..0.346 rows=9 loops=1)
Sort Key: c1
Sort Method: quicksort Memory: 25kB
-> Seq Scan on t1 (cost=0.00..21.50 rows=9 width=8)
(actual time=0.031..0.331 rows=9 loops=1)
Filter: (c2 < 10) Rows Removed by Filter: 991
Option FORMAT
L’option FORMAT
permet de préciser le format du texte en
sortie. Par défaut, il s’agit du format texte habituel, mais il est
possible de choisir un format semi-structuré parmi JSON, XML et YAML.
Les formats semi-structurés sont utilisés principalement par des outils
d’analyse comme explain.dalibo.com, car le
contenu est plus facile à analyser, et même un peu plus complet. Voici
ce que donne la commande EXPLAIN
avec le format JSON :
psql -X -AtX \
'EXPLAIN (FORMAT JSON) SELECT * FROM t1 WHERE c2<10 ORDER BY c1' | jq '.[]' -c
{
"Plan": {
"Node Type": "Sort",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 34.38,
"Total Cost": 34.42,
"Plan Rows": 18,
"Plan Width": 8,
"Sort Key": [
"c1"
],
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "t1",
"Alias": "t1",
"Startup Cost": 0,
"Total Cost": 34,
"Plan Rows": 18,
"Plan Width": 8,
"Filter": "(c2 < 10)"
}
]
}
}
La configuration du paramètre track_io_timing
permet de
demander le chronométrage des opérations d’entrée/sortie disque. Sur ce
plan, nous pouvons voir que 14 blocs ont été lus en dehors du cache de
PostgreSQL et que cela a pris 0,388 ms pour les lire (ils étaient
certainement dans le cache du système d’exploitation).
Cette information permet de voir si le temps d’exécution de la requête est dépensé surtout dans la demande de blocs au système d’exploitation (donc hors du cache de PostgreSQL) ou dans l’exécution même de la requête (donc interne à PostgreSQL).
Lorsqu’une requête s’exécute lentement, cela peut être un problème
dans le plan. La sortie de EXPLAIN
peut apporter quelques
informations qu’il faut savoir décoder.
Par exemple, une différence importante entre le nombre estimé de lignes et le nombre réel de lignes laisse un doute sur les statistiques présentes. Soit elles n’ont pas été réactualisées récemment, soit l’échantillon n’est pas suffisamment important pour que les statistiques donnent une vue proche du réel du contenu de la table.
Les boucles sont à surveiller. Par exemple, un accès à une ligne par
un index est généralement très rapide, mais répété des millions de fois
à cause d’une boucle, le total est parfois plus long qu’une lecture
complète de la table indexée. C’est notamment l’enjeu du réglage entre
seq_page_cost
et random_page_cost
.
L’option BUFFERS
d’EXPLAIN
permet également
de mettre en valeur les opérations d’entrées/sorties lourdes. Cette
option affiche notamment le nombre de blocs lus en/hors du cache de
PostgreSQL. Sachant qu’un bloc fait généralement 8 kilo-octets, il est
aisé de déterminer le volume de données manipulé par une requête.
Nous n’allons pas détailler tous les nœuds existants, mais évoquer simplement les plus importants. Une analyse plus poussée des nœuds et une référence complète sont disponibles dans les modules J2 et J6.
Plusieurs types d’objets peuvent être parcourus. Pour chacun, l’optimiseur peut choisir entre plusieurs types de parcours.
Les tables passent par un Seq Scan qui est une lecture simple de la table, bloc par bloc, ligne par ligne. Ce parcours peut filtrer les données mais ne les triera pas. Une variante parallélisée existe sous le nom de Parallel Seq Scan.
Les index disposent de plusieurs parcours, principalement suivant la quantité d’enregistrements à récupérer :
Ces différents parcours sont parallélisables. Ils ont dans ce cas le mot Parallel ajouté en début du nom du nœud.
Enfin, il existe des parcours moins fréquents, comme les parcours de fonction (Function Scan) ou de valeurs (Values Scan).
Trois nœuds existent pour les jointures.
Le Nested Loop est utilisé pour toutes les conditions de jointure n’utilisant pas l’opérateur d’égalité. Il est aussi utilisé quand un des deux ensembles de données renvoie très peu de données.
Le Hash Join est certainement le nœud le plus commun. Il est utilisé un peu dans tous les cas, sauf si les deux ensembles de données arrivent déjà triés. Dans ce cas, il est préférable de passer par un Merge Join qui réclame deux ensembles de données déjà triés.
Les Semi Join et Anti Join sont utilisés dans des cas très particuliers et peu fréquents.
De même il existe plusieurs algorithmes d’agrégation qui s’occupent des sommes, des moyennes, des regroupements divers, etc. Ils sont souvent parallélisables.
Un grand nombre de petites opérations ont leur propre nœud, comme le
tri avec Sort et Incremental Sort, la limite de lignes
(LIMIT
) avec Limit, la clause
DISTINCT
avec Unique), etc. Elles prennent
généralement un ensemble de données et renvoient un autre ensemble de
données issu du traitement du premier.
Le groupe des nœuds Append, Except et Intersect ne se comporte pas ainsi. Notamment, Append est le seul nœud à prendre potentiellement plus de deux ensembles de données en entrée.
Apparu avec PostgreSQL 14, le nœud Memoize est un cache de résultat qui permet d’optimiser les performances d’autres nœuds en mémorisant des données qui risquent d’être accédées plusieurs fois de suite. Pour le moment, ce nœud n’est utilisable que pour les données de l’ensemble interne d’un Nested Loop.
L’analyse de plans complexes devient très vite fastidieuse. Nous n’avons vu ici que des plans d’une dizaine de lignes au maximum, mais les plans de requêtes réellement problématiques peuvent faire plusieurs centaines, voire milliers de lignes. L’analyse manuelle devient impossible. Des outils ont été créés pour mieux visualiser les parties intéressantes des plans.
pgAdmin propose depuis très longtemps un affichage graphique de
l’EXPLAIN
. Cet affichage est intéressant car il montre
simplement l’ordre dans lequel les opérations sont effectuées. Chaque
nœud est représenté par une icône. Les flèches entre chaque nœud
indiquent où sont envoyés les flux de données, la taille de la flèche
précisant la volumétrie des données.
Les statistiques ne sont affichées qu’en survolant les nœuds.
Voici un exemple d’un EXPLAIN
graphique réalisé par
pgAdmin 4. En cliquant sur un nœud, un message affiche les informations
statistiques sur le nœud.
Hubert Lubaczewski est un contributeur très connu dans la communauté PostgreSQL. Il publie notamment un grand nombre d’articles sur les nouveautés des prochaines versions. Cependant, il est aussi connu pour avoir créé un site web d’analyse des plans d’exécution, explain.depesz.com.
Il suffit d’aller sur ce site, de coller le résultat d’un
EXPLAIN ANALYZE
, et le site affichera le plan d’exécution
avec des codes couleurs pour bien distinguer les nœuds performants des
autres.
Le code couleur est simple : blanc indique que tout va bien, jaune est inquiétant, marron est plus inquiétant, et rouge très inquiétant.
Plutôt que d’utiliser le service web, il est possible d’installer ce site en local :
Cet exemple montre l’affichage d’un plan sur le site explain.depesz.com.
Voici la signification des différentes colonnes :
Sur une exécution de 600 ms, un tiers est passé à lire la table avec un parcours séquentiel.
À l’origine, pev (PostgreSQL Explain Visualizer) est un outil libre offrant un affichage graphique du plan d’exécution et pointant le nœud le plus coûteux, le plus long, le plus volumineux, etc. Utilisable en ligne, il n’est hélas plus maintenu depuis plusieurs années.
explain.dalibo.com en est
un fork, très étendu et activement maintenu par Pierre Giraud
de Dalibo. Les plans au format texte comme JSON sont acceptés. Les
versions récentes de PostgreSQL sont supportées, avec leurs
spécificités : nouvelles options d’EXPLAIN
, nouveaux types
de nœuds… Tout se passe en ligne. Les plans peuvent être partagés. Si
vous ne souhaitez pas qu’ils soient stockés chez Dalibo, utilisez la
version strictement locale de pev2.
Le code est sous licence PostgreSQL. Techniquement, c’est un composant VueJS qui peut être intégré à vos propres outils.
explain.dalibo.com permet de repérer d’un coup d’œil les parties les plus longues du plan, celles utilisant le plus de lignes, les écarts d’estimation, les dérives du temps de planification… Les nœuds peuvent être repliés. Plusieurs modes d’affichage sont disponibles.
Un grand nombre de plans d’exemple sont disponibles sur le site.
Cette introduction à l’optimiseur de PostgreSQL permet de comprendre comment il fonctionne et sur quoi il se base. Cela permet de pointer certains des problèmes. C’est aussi un prérequis indispensable pour voir plus tard l’intérêt des différents index et nœuds d’exécution de PostgreSQL.
La version en ligne des solutions de ces TP est disponible sur https://dali.bo/j0_solutions.
Tous les TP se basent sur la configuration par défaut de PostgreSQL, sauf précision contraire.
Créer une base machines et y générer les données comme indiqué ci-dessous. L’exécution peut durer une minute ou deux suivant la machine.
curl -kL https://dali.bo/tp_machines_donnees -o machines_donnees.sql createdb machines psql machines < machines_donnees.sql
La base machines contiendra alors deux tables :
machines
est une liste de machines ;donnees
contient des données horodatées de quelques capteurs de ces machines, entre janvier et août 2023.
Nettoyage et mise à jour des statistiques :
ANALYZE machines,donnees; VACUUM
Quelles sont les tailles des tables ?
Pour simplifier certains plans, désactivons le parallélisme et la compilation à la volée :
SET max_parallel_workers_per_gather TO 0 ; SET jit TO off ;
Requêtes sur les périodes :
Quel est le plan prévu pour récupérer les données du 31 janvier dans la table
donnees
?EXPLAIN SELECT * FROM donnees WHERE horodatage = '2023-01-31'::date ;
Quel est le plan prévu pour récupérer les données du mois de janvier dans la table
donnees
?EXPLAIN SELECT * FROM donnees WHERE horodatage BETWEEN '2023-01-01'::date AND '2023-01-31'::date ;
Quel est le plan prévu pour cette variante de la requête sur janvier ?
EXPLAIN SELECT * FROM donnees WHERE to_char (horodatage, 'YYYYMM') = '202301';
Pourquoi est-il différent ? Comparer avec le précédant en utilisant
EXPLAIN ANALYZE
.
Quel est le plan pour la même requête, cette fois sur deux mois ?
EXPLAIN SELECT * FROM donnees WHERE horodatage BETWEEN '2023-03-01'::date AND '2023-04-30'::date;
Relancer avec
EXPLAIN (ANALYZE)
.
Jointure :
Quel est le plan prévu pour cette jointure sur toutes les données d’une machine ?
EXPLAIN SELECT * FROM donnees INNER JOIN machines USING (id_machine) WHERE machines.code = 'E4DA3B' AND type = 5;
Quel est le plan prévu pour la requête suivante, qui récupère toutes les données d’après juillet pour un type de machines donné ? Quelles en sont les 3 étapes ?
EXPLAIN SELECT description, horodatage, valeur1 FROM donnees INNER JOIN machines USING (id_machine) WHERE machines.type = 1 AND donnees.horodatage > '2023-07-01' ;
- Créer une base de données nommée
magasin
.
- Importer le jeu de données d’exemple :
La base magasin (dump de 96 Mo, pour 667 Mo sur le disque au final) peut être téléchargée et restaurée comme suit dans une nouvelle base magasin :
createdb magasin
curl -kL https://dali.bo/tp_magasin -o /tmp/magasin.dump
pg_restore -d magasin /tmp/magasin.dump
# le message sur public préexistant est normal
rm -- /tmp/magasin.dump
Les données sont dans deux schémas, magasin et
facturation. Penser au search_path
.
Pour ce TP, figer les paramètres suivants :
SET max_parallel_workers_per_gather to 0;
SET seq_page_cost TO 1 ;
SET random_page_cost TO 4 ;
- Le schéma à utiliser se nomme également
magasin
.- Consulter les tables.
- Lancer un
ANALYZE
sur la base.
Le but est de chercher une personne nommée Moris Russel dans la table
contacts
par les champsprenom
etnom
.
- Quel est le plan qu’utilisera PostgreSQL pour le trouver ?
- À combien de résultats le planificateur s’attend-il ?
- Afficher le résultat.
- Quel est le plan réellement exécuté ?
- Rechercher la même personne par son
contact_id
.- Quel est le plan ?
- La requête suivante recherche tous les fournisseurs résidents d’Hollywood.
SELECT c.nom, c.prenom FROM contacts c INNER JOIN fournisseurs f ON (f.contact_id = c.contact_id) WHERE c.ville = 'Hollywood' ;
- Quel est le plan prévu ?
- Que donne-t-il à l’exécution ?
Créer une base machines et y générer les données comme indiqué ci-dessous. L’exécution peut durer une minute ou deux suivant la machine.
curl -kL https://dali.bo/tp_machines_donnees -o machines_donnees.sql createdb machines psql machines < machines_donnees.sql
La base machines contiendra alors deux tables :
machines
est une liste de machines ;donnees
contient des données horodatées de quelques capteurs de ces machines, entre janvier et août 2023.
Nettoyage et mise à jour des statistiques :
ANALYZE machines,donnees; VACUUM
Cette opération est à faire systématiquement sur des tables récentes, ou au moindre doute. L’autovacuum n’est parfois pas assez rapide pour effectuer ces opérations.
Quelles sont les tailles des tables ?
Sous psql
:
=# \dt+
Liste des relations
Schéma | Nom | Type | Propriétaire | … | Taille | …
--------+-----------------+-------+--------------+----+---------+--
public | donnees | table | postgres | | 284 MB | public | machines | table | postgres | | 112 kB |
Quant aux nombres de lignes :
SELECT count (*) FROM machines ;
count
------- 1000
SELECT count (*) FROM donnees ;
count
--------- 4950225
Tout plan d’exécution dépend de la configuration de PostgreSQL. Sauf précision contraire, nous partons toujours de la configuration par défaut.
Pour simplifier certains plans, désactivons le parallélisme et la compilation à la volée :
SET max_parallel_workers_per_gather TO 0 ; SET jit TO off ;
Requêtes sur les périodes :
Quel est le plan prévu pour récupérer les données du 31 janvier dans la table
donnees
?EXPLAIN SELECT * FROM donnees WHERE horodatage = '2023-01-31'::date ;
Le plan prévu est :
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using donnees_horodatage_idx on donnees (cost=0.43..8.64 rows=12 width=30) Index Cond: (horodatage = '2023-01-31'::date)
Il existe un index sur le critère, il est naturel qu’il soit utilisé.
Quel est le plan prévu pour récupérer les données du mois de janvier dans la table
donnees
?EXPLAIN SELECT * FROM donnees WHERE horodatage BETWEEN '2023-01-01'::date AND '2023-01-31'::date ;
Le plan prévu est le même, au critère près :
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using donnees_horodatage_idx on donnees (cost=0.43..933.87 rows=28722 width=30) Index Cond: ((horodatage >= '2023-01-01'::date) AND (horodatage <= '2023-01-31'::date))
Noter la réécriture du BETWEEN
sous forme
d’inégalités.
Quel est le plan prévu pour cette variante de la requête sur janvier ?
EXPLAIN SELECT * FROM donnees WHERE to_char (horodatage, 'YYYYMM') = '202301';
Pourquoi est-il différent ? Comparer avec le précédant en utilisant
EXPLAIN ANALYZE
.
Le plan cette fois est un parcours de table. L’index est ignoré, toute la table est lue :
QUERY PLAN
------------------------------------------------------------------
Seq Scan on donnees (cost=0.00..110652.25 rows=24751 width=30) Filter: (to_char(horodatage, 'YYYYMM'::text) = '202301'::text)
Si le parallélisme est activé, il existe une variante parallélisée de ce plan :
QUERY PLAN
-------------------------------------------------------------------------------
Gather (cost=1000.00..70812.96 rows=24751 width=30)
Workers Planned: 2
-> Parallel Seq Scan on donnees (cost=0.00..67337.86 rows=10313 width=30) Filter: (to_char(horodatage, 'YYYYMM'::text) = '202301'::text)
La raison du changement de plan est le changement du critère. C’est
évident pour un humain, mais PostgreSQL ne fait pas l’équivalence entre
les deux formulations du critère sur le mois de janvier. Or il n’y a pas
d’index sur la fonction to_char(horodatage, 'YYYYMM')
(il
serait possible d’en créer un).
Si l’on compare les deux plans en les exécutant réellement, avec
EXPLAIN (ANALYZE)
, on obtient pour la variante avec
BETWEEN
:
EXPLAIN (ANALYZE,BUFFERS)
SELECT * FROM donnees
WHERE horodatage BETWEEN '2023-01-01'::date AND '2023-01-31'::date ;
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using donnees_horodatage_idx on donnees (cost=0.43..933.75 rows=28716 width=30) (actual time=0.060..9.405 rows=19600 loops=1)
Index Cond: ((horodatage >= '2023-01-01'::date) AND (horodatage <= '2023-01-31'::date))
Buffers: shared hit=6 read=191
Planning:
Buffers: shared hit=8
Planning Time: 0.072 ms Execution Time: 10.472 ms
et pour la variante avec to_char
:
EXPLAIN (ANALYZE,BUFFERS)
SELECT * FROM donnees
WHERE to_char (horodatage, 'YYYYMM') = '202301';
QUERY PLAN
-------------------------------------------------------------------------------
Seq Scan on donnees (cost=0.00..110652.25 rows=24751 width=30) (actual time=0.013..1503.631 rows=19600 loops=1)
Filter: (to_char(horodatage, 'YYYYMM'::text) = '202301'::text)
Rows Removed by Filter: 4930625
Buffers: shared hit=16063 read=20336
Planning Time: 0.025 ms Execution Time: 1504.379 ms
La dernière ligne indique 10 ms pour la variante avec
BETWEEN
contre 1,5 s pour la variante avec
to_char
: l’utilisation de l’index est nettement plus
intéressante que le parcours complet de la table. Le plan indique aussi
que beaucoup plus de blocs (buffers) ont été lus.
Quel est le plan pour la même requête, cette fois sur deux mois ?
EXPLAIN SELECT * FROM donnees WHERE horodatage BETWEEN '2023-03-01'::date AND '2023-04-30'::date;
Relancer avec
EXPLAIN (ANALYZE)
.
On s’attend au même plan que pour la recherche sur janvier, mais PostgreSQL prévoit cette fois un parcours complet :
QUERY PLAN
-------------------------------------------------------------------------------
Seq Scan on donnees (cost=0.00..110652.25 rows=4184350 width=30) Filter: ((horodatage >= '2023-03-01'::date) AND (horodatage <= '2023-04-30'::date))
En effet, il y a beaucoup plus de lignes à récupérer sur mars-avril
qu’en janvier. La mention rows
indique l’estimation des
lignes ramenées et indique 4,2 millions de lignes sur les 4,9 de la
table ! Le plus efficace est donc de lire directement la table. Les
statistiques permettent donc à PostgreSQL de changer de stratégie
suivant les volumétries attendues.
Une exécution réelle indique que cette estimation est bonne, et dure logiquement à peu près aussi longtemps que le parcours complet ci-dessus :
EXPLAIN (ANALYZE)
SELECT * FROM donnees
WHERE horodatage BETWEEN '2023-03-01'::date AND '2023-04-30'::date ;
QUERY PLAN
-------------------------------------------------------------------------------
Seq Scan on donnees (cost=0.00..110652.25 rows=4184350 width=30) (actual time=160.385..1255.020 rows=4182160 loops=1)
Filter: ((horodatage >= '2023-03-01'::date) AND (horodatage <= '2023-04-30'::date))
Rows Removed by Filter: 768065
Planning Time: 0.470 ms Execution Time: 1378.383 ms
Jointure :
Quel est le plan prévu pour cette jointure sur toutes les données d’une machine ?
EXPLAIN SELECT * FROM donnees INNER JOIN machines USING (id_machine) WHERE machines.code = 'E4DA3B' AND type = 5;
Le plan est :
QUERY PLAN
-------------------------------------------------------------------------------
Nested Loop (cost=0.71..5033.11 rows=4950 width=75)
-> Index Scan using machines_type_code_key on machines (cost=0.28..8.29 rows=1 width=49)
Index Cond: ((type = 5) AND ((code)::text = 'E4DA3B'::text))
-> Index Scan using donnees_id_machine_idx on donnees (cost=0.43..4124.77 rows=90004 width=30) Index Cond: (id_machine = machines.id_machine)
Il s’agit :
machines
par l’index sur
machines (type, code)
(cet index marque l’unicité) ;donnees
, toujours par l’index sur le
champ indexé id_machine
.Quel est le plan prévu pour la requête suivante, qui récupère toutes les données d’après juillet pour un type de machines donné ? Quelles en sont les 3 étapes ?
EXPLAIN SELECT description, horodatage, valeur1 FROM donnees INNER JOIN machines USING (id_machine) WHERE machines.type = 1 AND donnees.horodatage > '2023-07-01' ;
QUERY PLAN
-------------------------------------------------------------------------------
Hash Join (cost=30.67..8380.56 rows=138788 width=47)
Hash Cond: (donnees.id_machine = machines.id_machine)
-> Index Scan using donnees_horodatage_idx on donnees (cost=0.43..7671.54 rows=257492 width=16)
Index Cond: (horodatage > '2023-07-01 00:00:00+02'::timestamp with time zone)
-> Hash (cost=23.50..23.50 rows=539 width=39)
-> Seq Scan on machines (cost=0.00..23.50 rows=539 width=39) Filter: (type = 1)
Il s’agit ici d’une jointure en hash join, courante dans les jointures brassant beaucoup de lignes.
PostgreSQL commence par un parcours complet de machines
(type = 1
concerne la plupart des machines ). Puis il crée
une « table de hachage » à partir des id_machine
des lignes
résultantes. Il parcoure donnees
en se basant sur l’index
sur la date. Les lignes résultantes seront comparées au contenu de la
table de hachage pour savoir s’il faut garder les valeurs.
- Créer une base de données nommée
magasin
.
Si l’on est connecté à la base, en tant que superutilisateur postgres :
CREATE DATABASE magasin;
Alternativement, depuis le shell, en tant qu’utilisateur système postgres :
postgres$ createdb --echo magasin
SELECT pg_catalog.set_config('search_path', '', false);
CREATE DATABASE magasin;
- Importer le jeu de données d’exemple :
La base magasin (dump de 96 Mo, pour 667 Mo sur le disque au final) peut être téléchargée et restaurée comme suit dans une nouvelle base magasin :
createdb magasin
curl -kL https://dali.bo/tp_magasin -o /tmp/magasin.dump
pg_restore -d magasin /tmp/magasin.dump
# le message sur public préexistant est normal
rm -- /tmp/magasin.dump
Les données sont dans deux schémas, magasin et
facturation. Penser au search_path
.
Pour ce TP, figer les paramètres suivants :
SET max_parallel_workers_per_gather to 0;
SET seq_page_cost TO 1 ;
SET random_page_cost TO 4 ;
- Le schéma à utiliser se nomme également
magasin
.- Consulter les tables.
Le schéma par défaut public
ne contient effectivement
aucune table intéressante.
\dn
Liste des schémas
Nom | Propriétaire
-------------+--------------
facturation | postgres
magasin | postgres public | postgres
SET search_path to magasin ;
\dt+
Liste des relations
Schéma | Nom | Type | Propriétaire | Persistence | Taille | D…
---------+----------------------+-------+--------------+-------------+------------+---
magasin | clients | table | postgres | permanent | 8248 kB |
magasin | commandes | table | postgres | permanent | 79 MB |
magasin | conditions_reglement | table | postgres | permanent | 16 kB |
magasin | contacts | table | postgres | permanent | 24 MB |
magasin | etats_retour | table | postgres | permanent | 16 kB |
magasin | fournisseurs | table | postgres | permanent | 840 kB |
magasin | lignes_commandes | table | postgres | permanent | 330 MB |
magasin | lots | table | postgres | permanent | 74 MB |
magasin | modes_expedition | table | postgres | permanent | 16 kB |
magasin | modes_reglement | table | postgres | permanent | 16 kB |
magasin | numeros_sequence | table | postgres | permanent | 16 kB |
magasin | pays | table | postgres | permanent | 16 kB |
magasin | pays_transporteurs | table | postgres | permanent | 8192 bytes |
magasin | produit_fournisseurs | table | postgres | permanent | 216 kB |
magasin | produits | table | postgres | permanent | 488 kB |
magasin | regions | table | postgres | permanent | 16 kB |
magasin | transporteurs | table | postgres | permanent | 16 kB | magasin | types_clients | table | postgres | permanent | 16 kB |
Conseils pour la suite :
Préciser \timing on
dans psql
pour
afficher les temps d’exécution de la recherche.
Pour rendre les plans plus lisibles, désactiver le JIT et le parallélisme :
SET jit TO off ;
SET max_parallel_workers_per_gather TO 0 ;
- Lancer un
ANALYZE
sur la base.
ANALYZE ;
Le but est de chercher une personne nommée Moris Russel dans la table
contacts
par les champsprenom
etnom
.
- Quel est le plan qu’utilisera PostgreSQL pour le trouver ?
- À combien de résultats le planificateur s’attend-il ?
EXPLAIN SELECT * FROM contacts WHERE nom ='Russel' AND prenom = 'Moris' ;
QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on contacts (cost=0.00..4693.07 rows=1 width=298) Filter: (((nom)::text = 'Russel'::text) AND ((prenom)::text = 'Moris'::text))
La table sera entièrement parcourue (Seq Scan). PostgreSQL pense qu’il trouvera une ligne.
- Afficher le résultat.
SELECT * FROM contacts WHERE nom ='Russel' AND prenom = 'Moris' ;
-[ RECORD 1 ]----------------------------------------
contact_id | 26452
login | Russel_Moris
passwd | 9f81a90c36dd3c60ff06f3c800ae4c1b
email | ubaldo@hagenes-kulas-and-oberbrunner.mo
nom | Russel
prenom | Moris
adresse1 | 02868 Norris Greens
adresse2 | ¤
code_postal | 62151
ville | Laguna Beach
code_pays | CA
telephone | {"+(05) 4.45.08.11.03"}
Temps : 34,091 ms
La requête envoie bien une ligne, et l’obtenir a pris 34 ms sur cette machine avec SSD.
- Quel est le plan réellement exécuté ?
Il faut relancer la requête :
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM contacts
WHERE nom ='Russel' AND prenom = 'Moris' ;
QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on contacts (cost=0.00..4693.07 rows=1 width=297)
(actual time=3.328..16.789 rows=1 loops=1)
Filter: (((nom)::text = 'Russel'::text) AND ((prenom)::text = 'Moris'::text))
Rows Removed by Filter: 110004
Buffers: shared hit=3043
Planning Time: 0.052 ms Execution Time: 16.848 ms
PostgreSQL a à nouveau récupéré une ligne. Ici, cela n’a pris que 17 ms.
La table a été parcourue entièrement, et 110 004 lignes ont été rejetées. La ligne shared hit indique que 3043 blocs de 8 ko ont été lus dans le cache de PostgreSQL. La requête précédente a apparemment suffi à charger la table entière en cache (il n’y a pas de shared read).
- Rechercher la même personne par son
contact_id
.- Quel est le plan ?
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM contacts WHERE contact_id = 26452 ;
QUERY PLAN
---------------------------------------------------------------------------------
Index Scan using contacts_pkey on contacts (cost=0.42..8.44 rows=1 width=297)
(actual time=0.057..0.058 rows=1 loops=1)
Index Cond: (contact_id = 26452)
Buffers: shared hit=4 read=3
Planning:
Buffers: shared hit=6 read=3
Planning Time: 0.137 ms Execution Time: 0.081 ms
PostgreSQL estime correctement trouver une ligne. Cette fois, il s’agit d’un Index Scan, en l’occurrence sur l’index de la clé primaire. Le résultat est bien plus rapide : 137 µs pour planifier, 81 µs pour exécuter.
Les blocs lus se répartissent entre read et hit :
une partie était en mémoire, notamment ceux liés à la table, puisque la
table aussi a été interrogée (l’index ne contient que les données de
contact_id
) ; mais l’index n’était pas en mémoire.
- La requête suivante recherche tous les fournisseurs résidents d’Hollywood.
SELECT c.nom, c.prenom FROM contacts c INNER JOIN fournisseurs f ON (f.contact_id = c.contact_id) WHERE c.ville = 'Hollywood' ;
- Quel est le plan prévu ?
- Que donne-t-il à l’exécution ?
Le plan simplifié est :
EXPLAIN (COSTS OFF)
SELECT c.nom, c.prenom
FROM contacts c INNER JOIN fournisseurs f ON (f.contact_id = c.contact_id)
WHERE c.ville = 'Hollywood' ;
QUERY PLAN
-----------------------------------------------------
Merge Join
Merge Cond: (c.contact_id = f.contact_id)
-> Index Scan using contacts_pkey on contacts c
Filter: ((ville)::text = 'Hollywood'::text)
-> Sort
Sort Key: f.contact_id -> Seq Scan on fournisseurs f
Il consiste à parcourir intégralement la table
fournisseurs
(Seq Scan), à trier sa colonne
contact_id
, et à effectuer une jointure de type Merge
Join avec la clé primaire de la table contacts
. En
effet, un Merge Join s’effectue entre deux ensembles triés :
l’index l’est déjà, mais fournisseurs.contact_id
ne l’est
pas.
Noter qu’aucune donnée n’est récupérée de fournisseurs
.
Il est pourtant nécessaire de la joindre à contacts
car de
nombreux contacts ne sont pas des fournisseurs.
Exécutée, cette requête renvoie le plan suivant :
EXPLAIN (ANALYZE,BUFFERS)
SELECT c.nom, c.prenom FROM contacts c
INNER JOIN fournisseurs f ON (f.contact_id = c.contact_id)
WHERE c.ville = 'Hollywood' ;
QUERY PLAN
---------------------------------------------------------------------------------
Merge Join (cost=864.82..1469.89 rows=31 width=14)
(actual time=5.079..11.063 rows=32 loops=1)
Merge Cond: (c.contact_id = f.contact_id)
Buffers: shared hit=7 read=464
-> Index Scan using contacts_pkey on contacts c
(cost=0.42..6191.54 rows=346 width=22)
(actual time=0.029..4.842 rows=33 loops=1)
Filter: ((ville)::text = 'Hollywood'::text)
Rows Removed by Filter: 11971
Buffers: shared hit=7 read=364
-> Sort (cost=864.39..889.39 rows=10000 width=8)
(actual time=5.044..5.559 rows=10000 loops=1)
Sort Key: f.contact_id
Sort Method: quicksort Memory: 853kB
Buffers: shared read=100
-> Seq Scan on fournisseurs f (cost=0.00..200.00 rows=10000 width=8)
(actual time=0.490..2.960 rows=10000 loops=1)
Buffers: shared read=100
Planning:
Buffers: shared hit=4
Planning Time: 0.150 ms Execution Time: 11.174 ms
Ce plan est visible graphiquement sur https://explain.dalibo.com/plan/dum :
Le Seq Scan sur fournisseurs
lit 10 000 lignes
(100 blocs, hors du cache), ce qui était prévu. Cela prend 2,96 ms. Le
nœud Sort trie les contact_id
et consomme 853 ko
en mémoire. Il renvoie bien sûr aussi 10 000 lignes, et il commence à le
faire au bout de 5,04 ms.
La jointure peut commencer. Il s’agit de parcourir simultanément
l’ensemble que l’on vient de trier d’une part, et l’index
contacts_pkey
d’autre part. À cette occasion, le nœud
Index Scan va filtrer les lignes récupérées en comparant à la
valeur de ville
, et en exclue 11 971. Au final, le parcours
de l’index sur contacts
renvoie 33 lignes, et non les 346
estimées au départ (valeur dérivée de l’estimation du nombre de lignes
où la ville est « Hollywood »). Si l’on regarde les coûts calculés,
c’est cette étape qui est la plus lourde (6191).
En haut, on peut lire qu’au total 464 blocs ont été lus hors du cache, et 7 dedans. Ces valeurs varient bien sûr en fonction de l’activité précédente sur la base. Au final, 32 lignes sont retournées, ce qui était attendu.
Le temps écoulé est de 11,17 ms. La majorité de ce temps s’est déroulé pendant le Merge Join (11,0-5,0 = 6 ms), dont l’essentiel est constitué par le parcours de l’index.
Photo de Maksym Kaharlytskyi, Unsplash licence
Les index ne sont pas des objets qui font partie de la théorie relationnelle. Ils sont des objets physiques qui permettent d’accélérer l’accès aux données. Et comme ils ne sont que des moyens d’optimisation des accès, les index ne font pas non plus partie de la norme SQL. C’est d’ailleurs pour cette raison que la syntaxe de création d’index est si différente d’une base de données à une autre.
La création des index est à la charge du développeur ou du DBA, leur création n’est pas automatique, sauf exception.
Pour Markus Winand, c’est d’abord au développeur de poser les index, car c’est lui qui sait comment ses données sont utilisées. Un DBA d’exploitation n’a pas cette connaissance, mais il connaît généralement mieux les différents types d’index et leurs subtilités, et voit comment les requêtes réagissent en production. Développeur et DBA sont complémentaires dans l’analyse d’un problème de performance.
Le site de Markus Winand, Use the index, Luke, propose une version en ligne de son livre SQL Performance Explained, centré sur les index B-tree (les plus courants). Une version française est par ailleurs disponible sous le titre SQL : au cœur des performances.
Les index ne changent pas le résultat d’une requête, mais l’accélèrent. L’index permet de pointer l’endroit de la table où se trouve une donnée, pour y accéder directement. Parfois c’est toute une plage de l’index, voire sa totalité, qui sera lue, ce qui est généralement plus rapide que lire toute la table.
Le cas le plus favorable est l’Index Only Scan : toutes les données nécessaires sont contenues dans l’index, lui seul sera lu et PostgreSQL ne lira pas la table elle-même.
PostgreSQL propose différentes formes d’index :
WHERE
;La création des index est à la charge du développeur. Seules exceptions : ceux créés automatiquement quand on déclare des contraintes de clé primaire ou d’unicité. La création est alors automatique.
Les contraintes de clé étrangère imposent qu’il existe déjà une clé primaire sur la table pointée, mais ne crée pas d’index sur la table portant la clé.
L’index est une structure de données qui permet d’accéder rapidement à l’information recherchée. À l’image de l’index d’un livre, pour retrouver un thème rapidement, on préférera utiliser l’index du livre plutôt que lire l’intégralité du livre jusqu’à trouver le passage qui nous intéresse. Dans une base de données, l’index a un rôle équivalent. Plutôt que de lire une table dans son intégralité, la base de données utilisera l’index pour ne lire qu’une faible portion de la table pour retrouver les données recherchées.
Pour la requête d’exemple (avec une table de 20 millions de lignes), on remarque que l’optimiseur n’utilise pas le même chemin selon que l’index soit présent ou non. Sans index, PostgreSQL réalise un parcours séquentiel de la table :
EXPLAIN SELECT * FROM test WHERE id = 10000;
QUERY PLAN
----------------------------------------------------------------------
Gather (cost=1000.00..193661.66 rows=1 width=4)
Workers Planned: 2
-> Parallel Seq Scan on test (cost=0.00..192661.56 rows=1 width=4)
Filter: (id = 10000)
Lorsqu’il est présent, PostgreSQL l’utilise car l’optimiseur estime que son parcours ne récupérera qu’une seule ligne sur les 20 millions que compte la table :
EXPLAIN SELECT * FROM test WHERE id = 10000;
QUERY PLAN
----------------------------------------------------------------------------
Index Only Scan using idx_test_id on test (cost=0.44..8.46 rows=1 width=4)
Index Cond: (id = 10000)
Mais l’index n’accélère pas seulement la simple lecture de données, il permet également d’accélérer les tris et les agrégations, comme le montre l’exemple suivant sur un tri :
EXPLAIN SELECT id FROM test
WHERE id BETWEEN 1000 AND 1200 ORDER BY id DESC;
QUERY PLAN
--------------------------------------------------------------------------------
Index Only Scan Backward using idx_test_id on test
(cost=0.44..12.26 rows=191 width=4)
Index Cond: ((id >= 1000) AND (id <= 1200))
La présence d’un index ralentit les écritures sur une table. En effet, il faut non seulement ajouter ou modifier les données dans la table, mais il faut également maintenir le ou les index de cette table.
Les index dégradent surtout les temps de réponse des insertions. Les
mises à jour et les suppressions (UPDATE
et
DELETE
) tirent en général parti des index pour retrouver
les lignes concernées par les modifications. Le coût de maintenance de
l’index est secondaire par rapport au coût de l’accès aux données.
Soit une table test2
telle que :
CREATE TABLE test2 (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
INTEGER,
valeur
commentaire TEXT );
La table est chargée avec pour seul index présent celui sur la clé primaire :
INSERT INTO test2 (valeur, commentaire)
SELECT i, 'commentaire ' || i FROM generate_series(1, 10000000) i;
INSERT 0 10000000 Durée : 35253,228 ms (00:35,253)
Un index supplémentaire est créé sur une colonne de type entier :
CREATE INDEX idx_test2_valeur ON test2 (valeur);
INSERT INTO test2 (valeur, commentaire)
SELECT i, 'commentaire ' || i FROM generate_series(1, 10000000) i;
INSERT 0 10000000 Durée : 44410,775 ms (00:44,411)
Un index supplémentaire est encore créé, mais cette fois sur une colonne de type texte :
CREATE INDEX idx_test2_commentaire ON test2 (commentaire);
INSERT INTO test2 (valeur, commentaire)
SELECT i, 'commentaire ' || i FROM generate_series(1, 10000000) i;
INSERT 0 10000000 Durée : 207075,335 ms (03:27,075)
On peut comparer ces temps à l’insertion dans une table similaire dépourvue d’index :
CREATE TABLE test3 AS SELECT * FROM test2;
INSERT INTO test3 (valeur, commentaire)
SELECT i, 'commentaire ' || i FROM generate_series(1, 10000000) i;
INSERT 0 10000000 Durée : 14758,503 ms (00:14,759)
La table test2
a été vidée préalablement pour chaque
test.
Enfin, la place disque utilisée par ces index n’est pas négligeable :
\di+ *test2*
Liste des relations
Schéma | Nom | Type | Propriétaire | Table | Taille | …
--------+-----------------------+-------+--------------+-------+--------+-
public | idx_test2_commentaire | index | postgres | test2 | 387 MB |
public | idx_test2_valeur | index | postgres | test2 | 214 MB | public | test2_pkey | index | postgres | test2 | 214 MB |
SELECT pg_size_pretty(pg_relation_size('test2')),
'test2')) ; pg_size_pretty(pg_indexes_size(
pg_size_pretty | pg_size_pretty
----------------+---------------- 574 MB | 816 MB
Pour ces raisons, on ne posera pas des index systématiquement avant de se demander s’ils seront utilisés. L’idéal est d’étudier les plans de ses requêtes et de chercher à optimiser.
Création d’un index :
Bien sûr, la durée de création de l’index dépend fortement de la taille de la table. PostgreSQL va lire toutes les lignes et trier les valeurs rencontrées. Ce peut être lourd et impliquer la création de fichiers temporaires.
Si l’on utilise la syntaxe classique, toutes les écritures sur la table sont bloquées (mises en attente) pendant la durée de la création de l’index (verrou ShareLock). Les lectures restent possibles, mais cette contrainte est parfois rédhibitoire pour les grosses tables.
Clause CONCURRENTLY :
Ajouter le mot clé CONCURRENTLY
permet de rendre la
table accessible en écriture. Malheureusement, cela nécessite au minimum
deux parcours de la table, et donc alourdit et ralentit la construction
de l’index. Dans quelques cas défavorables (entre autres l’interruption
de la création de l’index), la création échoue et l’index existe mais
est invalide :
pgbench=# \d pgbench_accounts
Table « public.pgbench_accounts »
Colonne | Type | Collationnement | NULL-able | Par défaut
----------+---------------+-----------------+-----------+------------
aid | integer | | not null |
bid | integer | | |
abalance | integer | | |
filler | character(84) | | |
Index :
"pgbench_accounts_pkey" PRIMARY KEY, btree (aid)
"pgbench_accounts_bid_idx" btree (bid) INVALID
L’index est inutilisable et doit être supprimé et recréé, ou bien réindexé. Pour les détails, voir la documentation officielle.
Une supervision peut détecter des index invalides avec cette requête, qui ne doit jamais rien ramener :
SELECT indexrelid::regclass AS index, indrelid::regclass AS table
FROM pg_index
WHERE indisvalid = false ;
Réindexation :
Comme les tables, les index sont soumis à la fragmentation. Celle-ci peut cependant monter assez haut sans grande conséquence pour les performances. De plus, le nettoyage des index est une des étapes des opérations de VACUUM.
Une reconstruction de l’index est automatique lors d’un
VACUUM FULL
de la table.
Certaines charges provoquent une fragmentation assez élevée, typiquement les tables gérant des files d’attente. Une réindexation reconstruit totalement l’index. Voici quelques variantes de l’ordre :
INDEX pgbench_accounts_bid_idx ; -- un seul index
REINDEX TABLE pgbench_accounts ; -- tous les index de la table
REINDEX DATABASE pgbench ; -- tous ceux de la base, avec détails REINDEX (VERBOSE)
Il existe là aussi une clause CONCURRENTLY
:
INDEX CONCURRENTLY pgbench_accounts_bid_idx ; REINDEX (VERBOSE)
(En cas d’échec, on trouvera là aussi des index invalides, suffixés
avec _ccnew
, à côté des index préexistants toujours
fonctionnels et que PostgreSQL n’a pas détruits.)
Paramètres :
La rapidité de création d’un index dépend essentiellement de la
mémoire accordée, définie dans maintenance_work_mem
. Si
elle ne suffit pas, le tri se fera dans des fichiers temporaires plus
lents. Sur les serveurs modernes, le défaut de 64 Mo est ridicule, et on
peut monter aisément à :
SET maintenance_work_mem = '2GB' ;
Attention de ne pas saturer la mémoire en cas de création simultanée
de nombreux gros index (lors d’une restauration avec
pg_restore
notamment).
Si le serveur est bien doté en CPU, la parallélisation de la création d’index peut apporter un gain en temps appréciable. La valeur par défaut est :
SET max_parallel_maintenance_workers = 2 ;
et devrait même être baissée sur les plus petites configurations.
Par défaut un CREATE INDEX
créera un index de type
B-tree, de loin le plus courant. Il est stocké sous forme d’arbre
équilibré, avec de nombreux avantages :
Toutefois les B-tree ne permettent de répondre qu’à des questions très simples, portant sur la colonne indexée, et uniquement sur des opérateurs courants (égalité, comparaison). Cela couvre tout de même la majorité des cas.
Contrainte d’unicité et index :
Un index peut être déclaré UNIQUE
pour provoquer une
erreur en cas d’insertion de doublons. Mais on préférera généralement
déclarer une contrainte d’unicité (notion fonctionnelle), qui
techniquement, entraînera la création d’un index.
Par exemple, sur cette table personne
:
CREATE TABLE personne (id int, nom text); $
$ \d personne
Table « public.personne »
Colonne | Type | Collationnement | NULL-able | Par défaut
---------+---------+-----------------+-----------+------------
id | integer | | |
nom | text | | |
on peut créer un index unique :
CREATE UNIQUE INDEX ON personne (id); $
$ \d personne
Table « public.personne »
Colonne | Type | Collationnement | NULL-able | Par défaut
---------+---------+-----------------+-----------+------------
id | integer | | |
nom | text | | |
Index :
"personne_id_idx" UNIQUE, btree (id)
La contrainte d’unicité est alors implicite. La suppression de l’index se fait sans bruit :
DROP INDEX personne_id_idx;
Définissons une contrainte d’unicité sur la colonne plutôt qu’un index :
ALTER TABLE personne ADD CONSTRAINT unique_id UNIQUE (id);
$ \d personne
Table « public.personne »
Colonne | Type | Collationnement | NULL-able | Par défaut
---------+---------+-----------------+-----------+------------
id | integer | | |
nom | text | | |
Index :
"unique_id" UNIQUE CONSTRAINT, btree (id)
Un index est également créé. La contrainte empêche sa suppression :
DROP INDEX unique_id ;
ERREUR: n'a pas pu supprimer index unique_id car il est requis par contrainte
unique_id sur table personne
ASTUCE : Vous pouvez supprimer contrainte unique_id sur table personne à la
place.
Le principe est le même pour les clés primaires.
Indexation avancée :
Il faut aussi savoir que PostgreSQL permet de créer des index B-tree :
D’autres types d’index que B-tree existent, destinés à certains types de données ou certains cas d’optimisation précis.