PostgreSQL Avancé

10 janvier 2020

Dalibo SCOP

Creative Commons BY-NC-SA

Fonctionnement interne

PostgreSQL


Introduction & objectifs

Présenter le fonctionnement de PostgreSQL en profondeur :

  • Les choix d’architecture
  • Le paramétrage
  • Et ce que cela implique

La présentation se fait par module fonctionnel du noyau PostgreSQL. Bien sûr, tous les modules interagissent ensemble, et les frontières ne sont pas toujours parfaitement nettes. N’hésitez pas à poser des questions !

Tous les paramètres du moteur ne sont pas abordés, même si ces modules permettent d’en présenter une forte majorité. La compréhension théorique apportée par ces deux modules doit permettre la compréhension de tous les paramètres.

Le fonctionnement des transactions sera traité dans un module de formation postérieur.


Au menu

  • Rappels sur l’installation
  • Les processus
  • Gestion de la mémoire
  • Les fichiers
  • Shared buffers
  • Journalisation
  • Statistiques
  • Optimiseur de requête
  • Gestion des connexions

Rappels sur l’installation

  • Plusieurs possibilités
    • paquets Linux précompilés
    • outils externes d’installation
    • code source
  • Chacun ses avantages et inconvénients
    • Dalibo recommande fortement les paquets précompilés

Nous recommandons très fortement l’utilisation des paquets Linux précompilés. Dans certains cas, il ne sera pas possible de faire autrement que de passer par des outils externes, comme l’installeur d’EntrepriseDB sous Windows.


Paquets précompilés

  • Paquets Debian ou Red Hat suivant la distribution utilisée
  • Préférence forte pour ceux de la communauté
  • Installation du paquet
    • installation des binaires
    • création de l’utilisateur postgres
    • initialisation d’une instance (Debian seulement)
    • lancement du serveur (Debian seulement)
  • Scripts disponibles pour Red Hat pour l’initialisation d’une instance et le lancement d’un serveur

Debian et Red Hat fournissent des paquets précompilés adaptés à leur distribution. Dalibo recommande d’installer les paquets de la communauté, ces derniers étant bien plus à jour que ceux des distributions.

L’installation d’un paquet provoque la création d’un utilisateur système nommé postgres et l’installation des binaires. Suivant les distributions, l’emplacement des binaires change. Habituellement, tout est placé dans /usr/pgsql-<version majeure> pour les distributions Red Hat et dans /usr/lib/postgresql/<version majeure> pour les distributions Debian.

Dans le cas d’une distribution Debian, une instance est immédiatement créée dans /var/lib/postgresql/<version majeure>/main. Elle est ensuite démarrée.

Dans le cas d’une distribution Red Hat, aucune instance n’est créée automatiquement. Il faudra utiliser un script (dont le nom dépend de la version de la distribution) pour créer l’instance, puis on pourra utiliser le script de démarrage pour lancer le serveur.


Installons PostgreSQL

  • Prenons un moment pour
    • installer PostgreSQL
    • créer une instance
    • démarrer l’instance
  • Pas de configuration spécifique pour l’instant

L’annexe ci-dessous décrit l’installation de PostgreSQL sans configuration particulière pour suivre le reste de la formation.


Les processus

  • PostgreSQL est :

    • multi-processus et non multi-thread
    • à mémoire partagée
    • client-serveur

L’architecture PostgreSQL est une architecture multi-processus et non multi-thread.

Cela signifie que chaque processus de PostgreSQL s’exécute dans un contexte mémoire isolé, et que la communication entre ces processus repose sur des mécanismes systèmes inter-processus : sémaphores, zones de mémoire partagée, sockets. Ceci s’oppose à l’architecture multi-thread, où l’ensemble du moteur s’exécute dans un seul processus, dans plusieurs threads (contextes) d’exécution, où tout est partagé par défaut.

Le principal avantage de cette architecture multi-processus est la stabilité : un processus, en cas de problème, ne corrompt que sa mémoire (ou la mémoire partagée), le plantage d’un processus n’affecte pas directement les autres. Son principal défaut est une allocation statique des ressources de mémoire partagée : elles ne sont pas redimensionnables à chaud.

Pour comparatif : Oracle sous Unix et DB2 sont multi-processus, et Oracle sous Windows, SQL Server et MySQL sont multi-threads.

Tous les processus de PostgreSQL accèdent à une zone de « mémoire partagée ». Cette zone contient les informations devant être partagées entre les clients, comme un cache de données, ou des informations sur l’état de chaque session par exemple.

PostgreSQL utilise une architecture client-serveur. On ne se connecte à PostgreSQL qu’à travers un protocole bien défini, on n’accède jamais aux fichiers de données.


Processus d’arrière-plan (1/2)

# ps f -e --format=pid,command | grep -E "postgres|postmaster"
 7769 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data
 7771  \_ postgres: logger
 7773  \_ postgres: checkpointer
 7774  \_ postgres: background writer
 7775  \_ postgres: walwriter
 7776  \_ postgres: autovacuum launcher
 7777  \_ postgres: stats collector
 7778  \_ postgres: logical replication launcher

(sous CentOS)

On constate que plusieurs processus sont présents dès le démarrage de PostgreSQL. Nous allons les détailler.

Il est à noter que la commande ps affiche un grand nombre d’informations sur le processus seulement si le paramètre update_process_title est activé (défaut sous Linux).

NB : sur Debian, le postmaster est nommé postgres comme ses processus fils.


Processus d’arrière plan (2/2)

  • Les processus présents au démarrage :

    • Un processus père, appelé le postmaster
    • background writer
    • checkpointer
    • walwriter
    • autovacuum launcher
    • stats collector
    • logical replication launcher
    • et d’autres selon la configuration…
  • Le postmaster est responsable de la supervision des autres processus, ainsi que de la prise en compte des connexions entrantes.
  • Le background writer et le checkpointer s’occupent d’effectuer les écritures en arrière plan, évitant ainsi aux sessions des utilisateurs de le faire. Nous y reviendrons dans la partie « Gestion de la mémoire ».
  • Le walwriter écrit le journal de transactions de façon anticipée, afin de limiter le travail de l’opération COMMIT. Nous y reviendrons dans la partie « Journalisation ».
  • L’ autovacuum launcher pilote les opérations d’« autovacuum ». Ceci sera expliqué en détail dans le module « Mécanique du moteur transactionnel ».
  • Le stats collector collecte les statistiques d’exécution du serveur. Nous y reviendrons dans la partie « Statistiques ».
  • Le logical replication launcher est un processus dédié à la réplication logique, activé par défaut à partir de la version 10.

Aucun de ces processus ne traite de requête pour le compte des utilisateurs. Ce sont des processus d’arrière-plan effectuant des tâches de maintenance.

Des processus supplémentaires peuvent apparaître, comme un walsender dans le cas où la base est le serveur primaire du cluster de réplication, un logger si PostgreSQL doit gérer lui-même les fichiers de traces (par défaut sous Red Hat, mais pas sous Debian), ou un archiver si l’instance est paramétrée pour générer des archives de ses journaux de transactions.


Processus par client

  • Pour chaque client, nous avons un processus :
    • créé à la connexion
    • dédié au client …
    • … et qui dialogue avec lui
    • détruit à la déconnexion
  • Un processus gère une requête
    • mais peut être aidé par d’autres processus (>= 9.6)
  • Le nombre de processus est régi par les paramètres :
    • max_connections (défaut : 100)
    • superuser_reserved_connections (3)
    • compromis nombre requêtes actives/nombre cœurs/complexité/mémoire

Pour chaque nouvelle session à l’instance, le processus postmaster crée un processus fils qui s’occupe de gérer cette session.

Ce processus reçoit les ordres SQL, les interprète, exécute les requêtes, trie les données, et enfin retourne les résultats. À partir de la version 9.6, dans certains cas, il peut demander le lancement d’autres processus pour l’aider dans l’exécution d’une requête en lecture seule (parallélisme).

Il y a un processus dédié à chaque connexion cliente, et ce processus est détruit à fin de cette connexion.

Le dialogue entre le client et ce processus respecte un protocole réseau bien défini. Le client n’a jamais accès aux données par un autre moyen que par ce protocole.

Le nombre maximum de connexions à la base simultanées, actives ou non, est limité par le paramètre max_connections. Le défaut est 100. Afin de permettre à l’administrateur de se connecter à l’instance si cette limite était atteinte, superuser_reserved_connections sont réservées aux superutilisateurs de l’instance.

Une prise en compte de la modification de ces deux paramètres impose un redémarrage complet de l’instance, puisqu’ils ont un impact sur la taille de la mémoire partagée entre les processus PostgreSQL.

La valeur 100 pour max_connections est généralement suffisante. Il peut être intéressant de la diminuer pour monter work_mem et autoriser plus de mémoire de tri. On peut l’augmenter pour qu’un plus grand nombre d’utilisateurs puisse se connecter en même temps.

Il s’agit aussi d’arbitrer entre le nombre de requêtes à exécuter à un instant t, le nombre de CPU disponibles, la complexité des requêtes, et le nombre de processus que peut gérer l’OS.

Cela est encore compliqué par le parallélisme et la limitation de la bande passante des disques.

Intercaler un « pooler » (le plus réputé est PgBouncer) entre les clients et l’instance peut se justifier dans certains cas :

  • connexions/déconnexions très fréquentes (la connexion a un coût) ;
  • centaines, voire milliers, de connexions généralement inactives.

Gestion de la mémoire

Structure de la mémoire sous PostgreSQL

  • Zone de mémoire partagée
  • Zone de chaque processus

Mémoire partagée

  • Implémentation
    • shared_memory_type
  • Zone de mémoire partagée :
    • shared_buffers
    • wal_buffers
    • Données de session (paramètres max_connections et track_activity_query_size)
    • Verrous (paramètres max_connections et max_locks_per_transaction)

La zone de mémoire partagée est allouée statiquement au démarrage de l’instance. Depuis la version 12, le type de mémoire partagée est configuré avec le paramètre shared_memory_type. Sous Linux, il s’agit par défaut de mmap pour la grosse partie et avec une très petite partie utilisant sysv (System V). Il est possible de basculer uniquement en sysv mais ceci n’est pas recommandé. Sous Windows, le type est windows. Avant la v12, on ne peut changer ces défauts.

Elle est calculée en fonction du dimensionnement des différentes zones :

  • shared_buffers : le cache des blocs du disque, partagé entre les différents processus :
  • wal_buffers : le tampon pour les journaux de transaction ;
  • les données de sessions : les principaux paramètres liés sont max_connections (défaut : 100) et track_activity_query_size (défaut : 1024)
  • les verrous : les paramètres sont max_connections et max_locks_per_transaction (défaut : 64).

Toute modification des paramètres régissant la mémoire partagée imposent un redémarrage de l’instance.

Nous verrons en détail l’utilité de chacune de ces zones dans les chapitres suivants.


Mémoire par processus

  • Par processus :
    • work_mem
    • maintenance_work_mem
    • temp_buffers
  • Pas de limite stricte à la consommation mémoire d’une session !

Chaque processus, en plus de la mémoire partagée à laquelle il accède en permanence, peut allouer de la mémoire pour ses besoins propres. L’allocation de cette mémoire est temporaire : elle est libérée dès qu’elle n’est plus utile, en fin de requête ou de session. Cette mémoire n’est utilisable que par le processus l’ayant allouée.

Cette mémoire est utilisée dans plusieurs contextes :

  • Pour des tris et hachages lors de l’exécution de requêtes, souvent suite à ORDER BY ou certaines jointure. Ce tri sera effectué à hauteur de work_mem en mémoire, puis sera poursuivi sur le disque au besoin.
  • Pour des opérations de maintenance : un CREATE INDEX ou un VACUUM par exemple. Ces besoins étant plus rares, mais plus gourmands en mémoire, on dispose d’un second paramètre maintenance_work_mem, habituellement plus grand que work_mem.
  • Pour la gestion de l’accès à des tables temporaires : afin de minimiser les appels systèmes dans le cas d’accès à des tables temporaires (locales à chaque session), chaque session peut allouer temp_buffers de cache dédié à ces tables.

Comme elle n’est pas partagée, cette mémoire est totalement dynamique.

Il n’y a pas de limite globale à la mémoire pouvant être utilisée par ces paramètres ! Il est théoriquement possible que toutes les connexions (au nombre de max_connections) lancent simultanément des requêtes allouant plusieurs fois work_mem (si la requête en cours d’exécution nécessite plusieurs tris par exemple, ou si d’autres processus sont appelés à l’aide, notamment en cas de parallélisation de la requête). Certains plans d’exécution malheureux peuvent consommer aussi beaucoup plus que prévu. Il faut donc rester prudent sur les valeurs de ces paramètres, work_mem tout particulièrement, et superviser les conséquences d’une modification de celui-ci.


Les fichiers

  • Une instance est composée de fichiers :
    • Répertoire de données
    • Fichiers de configuration
    • Fichier PID
    • Tablespaces
    • Statistiques
    • Fichiers de trace

Une instance est composée de :

  • Un répertoire de données. Il contient les fichiers obligatoires au bon fonctionnement de l’instance (fichiers de données, journaux de transaction…)
  • Des fichiers de configuration. Selon la distribution ils sont stockés dans le répertoire de données (Red Hat, CentOS…) ou dans /etc/postgresql (Debian et dérivés).
  • Un fichier PID, qui permet de savoir si une instance est démarrée ou non, et donc à empêcher un second jeu de processus d’y accéder. Le paramètre external_pid_file permet d’indiquer un emplacement où PostgreSQL créera un second fichier de PID, généralement à l’extérieur de son répertoire de donnée.
  • Optionnellement, des tablespaces. Ce sont des espaces de stockage supplémentaires, stockés habituellement dans d’autres systèmes de fichiers.
  • Un fichier de statistiques d’exécution.
  • Un ou des fichiers de trace (journaux) de la base, si celle-ci n’utilise pas le mécanisme du système d’exploitation (syslog sous Unix, journal des événements sous Windows).

Répertoire de données

postgres$ ls $PGDATA
base              pg_ident.conf  pg_stat      pg_xact
current_logfiles  pg_logical     pg_stat_tmp  postgresql.auto.conf
global            pg_multixact   pg_subtrans  postgresql.conf
log               pg_notify      pg_tblspc    postmaster.opts
pg_commit_ts      pg_replslot    pg_twophase  postmaster.pid
pg_dynshmem       pg_serial      PG_VERSION
pg_hba.conf       pg_snapshots   pg_wal

Le répertoire de données est souvent appelé PGDATA, du nom de la variable d’environnement que l’on peut faire pointer vers lui pour simplifier l’utilisation de nombreux utilitaires PostgreSQL. On peut aussi le connaître, étant connecté à la base, en interrogeant le paramètre data_directory.

SHOW data_directory;

      data_directory
---------------------------
 /var/lib/pgsql/12/data
(1 row)

Il est recommandé de ne jamais créer ce répertoire PGDATA à la racine d’un point de montage, quel que soit le système d’exploitation et le système de fichier utilisé. Si un point de montage est dédié à l’utilisation de PostgreSQL, positionnez-le toujours dans un sous-répertoire, voire deux niveaux en dessous du point de montage. (eg. <point de montage>/<version majeure>/<nom instance>).

Voir à ce propos le chapitre Use of Secondary File Systems dans la documentation officielle : https://www.postgresql.org/docs/current/creating-cluster.html

Vous pouvez trouver une description de tous les fichiers et répertoires dans la documentation officielle.


Fichiers de données

  • base/ : contient les fichiers de données
    • un sous-répertoire par base de données
    • pgsql_tmp : fichiers temporaires
  • global/ : contient les objets globaux à toute l’instance

base/ contient les fichiers de données (tables, index, séquences). Il contient un sous-répertoire par base, le nom du répertoire étant l’OID de la base dans pg_database. Dans ces répertoires, on trouve un ou plusieurs fichiers par objet à stocker. Ils sont nommés ainsi :

  • Le nom de base du fichier correspond à l’attribut relfilenode de l’objet stocké, dans la table pg_class (une table, un index…). Il peut changer dans la vie de l’objet (par exemple lors d’un VACUUM FULL, un TRUNCATE…)
  • Si le nom est suffixé par un « . » suivi d’un chiffre, il s’agit d’un fichier d’extension de l’objet : un objet est découpé en fichiers de 1 Go maximum.
  • Si le nom est suffixé par _fsm, il s’agit du fichier stockant la Free Space Map (liste des blocs réutilisables).
  • Si le nom est suffixé par _vm, il s’agit du fichier stockant la Visibility Map (liste des blocs intégralement visibles, et donc ne nécessitant pas de traitement par VACUUM).

Un fichier base/1247/14356.1 est donc le second fichier de l’objet ayant relfilenode=14356 dans pg_class, dans la base pour laquelle OID=1247 dans la table pg_database.

Savoir identifier cette correspondance ne sert que dans des cas de récupération de base très endommagée. Vous n’aurez jamais, durant une exploitation normale, besoin d’obtenir cette correspondance. Si, par exemple, vous avez besoin de connaître la taille de la table test dans une base, il vous suffit d’exécuter :

CREATE TABLE test (id integer);
INSERT INTO test SELECT generate_series(1, 5000000);
SELECT pg_table_size('test');

 pg_table_size
---------------
     181305344

Néanmoins, il existe un utilitaire appelé oid2name dont le but est de faire la liaison entre nom de fichier et nom de l’objet PostgreSQL.

Le répertoire base peut aussi contenir un répertoire pgsql_tmp. Ce répertoire contient des fichiers temporaires utilisés pour stocker les résultats d’un tri ou d’un hachage. À partir de la version 12, il est possible de connaître facilement le contenu de ce répertoire en utilisant la fonction pg_ls_tmpdir(), ce qui peut permettre de suivre leur consommation.

Si l’on demande au sein d’une première session :

# SELECT * FROM generate_series(1,1e9) ORDER BY random() LIMIT 1 ;

on pourra suivre les fichiers temporaires depuis une autre session :

# SELECT * FROM pg_ls_tmpdir() ;
       name        |    size    |      modification
-------------------+------------+------------------------
 pgsql_tmp12851.16 | 1073741824 | 2019-09-02 15:43:27+02
 pgsql_tmp12851.11 | 1073741824 | 2019-09-02 15:42:32+02
 pgsql_tmp12851.7  | 1073741824 | 2019-09-02 15:41:49+02
 pgsql_tmp12851.5  | 1073741824 | 2019-09-02 15:41:29+02
 pgsql_tmp12851.9  | 1073741824 | 2019-09-02 15:42:11+02
 pgsql_tmp12851.0  | 1073741824 | 2019-09-02 15:40:36+02
 pgsql_tmp12851.14 | 1073741824 | 2019-09-02 15:43:06+02
 pgsql_tmp12851.4  | 1073741824 | 2019-09-02 15:41:19+02
 pgsql_tmp12851.13 | 1073741824 | 2019-09-02 15:42:54+02
 pgsql_tmp12851.3  | 1073741824 | 2019-09-02 15:41:09+02
 pgsql_tmp12851.1  | 1073741824 | 2019-09-02 15:40:47+02
 pgsql_tmp12851.15 | 1073741824 | 2019-09-02 15:43:17+02
 pgsql_tmp12851.2  | 1073741824 | 2019-09-02 15:40:58+02
 pgsql_tmp12851.8  | 1073741824 | 2019-09-02 15:42:00+02
 pgsql_tmp12851.12 | 1073741824 | 2019-09-02 15:42:43+02
 pgsql_tmp12851.10 | 1073741824 | 2019-09-02 15:42:21+02
 pgsql_tmp12851.6  | 1073741824 | 2019-09-02 15:41:39+02
 pgsql_tmp12851.17 |  546168976 | 2019-09-02 15:43:32+02
(18 lignes)

Le répertoire global/ contient notamment les objets globaux à toute une instance, comme la table des bases de données, celle des rôles ou celle des tablespaces. Avant la 9.3, il contient aussi le fichier des statistiques d’activité (pgstat.stat).


Gestion des transactions

  • pg_wal/ : journaux de transactions
    • pg_xlog/ avant la v10
    • sous-répertoire archive_status
    • nom : timeline, journal, segment
    • ex : 00000002 00000142 000000FF
  • pg_xact/ : état des transactions
    • pg_clog/ avant la v10
  • mais aussi : pg_commit_ts/, pg_multixact/,pg_serial/
    pg_snapshots/, pg_subtrans/, pg_twophase/
  • Ces fichiers sont vitaux !

Le répertoire pg_wal contient les journaux de transactions. Ces journaux garantissent la durabilité des données dans la base, en traçant toute modification devant être effectuée AVANT de l’effectuer réellement en base.

Les logs (journaux) contenus dans pg_wal ne doivent jamais être effacés. Ces fichiers sont cruciaux au bon fonctionnement de la base.

Par défaut, les fichiers des journaux font tous 16 Mo. Ils ont des noms sur 24 caractères, comme par exemple :

$ ls -l
total 2359320
...
-rw------- 1 postgres postgres 33554432 Mar 26 16:28 00000002000001420000007C
-rw------- 1 postgres postgres 33554432 Mar 26 16:28 00000002000001420000007D
-rw------- 1 postgres postgres 33554432 Mar 26 16:28 00000002000001420000007E
-rw------- 1 postgres postgres 33554432 Mar 26 16:28 00000002000001420000007F
-rw------- 1 postgres postgres 33554432 Mar 26 16:28 000000020000014300000000
-rw------- 1 postgres postgres 33554432 Mar 26 16:28 000000020000014300000001
-rw------- 1 postgres postgres 33554432 Mar 26 16:28 000000020000014300000002
-rw------- 1 postgres postgres 33554432 Mar 26 16:28 000000020000014300000003
...
-rw------- 1 postgres postgres 33554432 Mar 26 16:28 00000002000001430000001D
-rw------- 1 postgres postgres 33554432 Mar 26 16:28 00000002000001430000001E
-rw------- 1 postgres postgres 33554432 Mar 26 16:28 00000002000001430000001F
-rw------- 1 postgres postgres 33554432 Mar 26 16:28 000000020000014300000020
-rw------- 1 postgres postgres 33554432 Mar 26 16:25 000000020000014300000021
-rw------- 1 postgres postgres 33554432 Mar 26 16:25 000000020000014300000022
-rw------- 1 postgres postgres 33554432 Mar 26 16:25 000000020000014300000023
-rw------- 1 postgres postgres 33554432 Mar 26 16:25 000000020000014300000024
-rw------- 1 postgres postgres 33554432 Mar 26 16:25 000000020000014300000025
-rw------- 1 postgres postgres 33554432 Mar 26 16:26 000000020000014300000026
-rw------- 1 postgres postgres 33554432 Mar 26 16:26 000000020000014300000027
-rw------- 1 postgres postgres 33554432 Mar 26 16:26 000000020000014300000028
-rw------- 1 postgres postgres 33554432 Mar 26 16:25 000000020000014300000029
-rw------- 1 postgres postgres 33554432 Mar 26 16:26 00000002000001430000002A
-rw------- 1 postgres postgres 33554432 Mar 26 16:26 00000002000001430000002B
-rw------- 1 postgres postgres 33554432 Mar 26 16:26 00000002000001430000002C
-rw------- 1 postgres postgres 33554432 Mar 26 16:26 00000002000001430000002D
-rw------- 1 postgres postgres 33554432 Mar 26 16:26 00000002000001430000002E
-rw------- 1 postgres postgres 33554432 Mar 26 16:26 00000002000001430000002F
-rw------- 1 postgres postgres 33554432 Mar 26 16:26 000000020000014300000030
-rw------- 1 postgres postgres 33554432 Mar 26 16:26 000000020000014300000031
-rw------- 1 postgres postgres 33554432 Mar 26 16:25 000000020000014300000032
-rw------- 1 postgres postgres 33554432 Mar 26 16:26 000000020000014300000033
-rw------- 1 postgres postgres 33554432 Mar 26 16:25 000000020000014300000034
drwx------ 2 postgres postgres    16384 Mar 26 16:28 archive_status

La première partie d’un nom de fichier (ici 00000002) correspond à la timeline (« ligne de temps »), qui ne s’incrémente que lors d’une restauration de sauvegarde ou une bascule entre serveurs principal et secondaire. La deuxième partie (ici 00000142) correspond au numéro de journal à proprement parler, soit un ensemble de fichiers représentant 4 Go. La dernière partie correspond au numéro du segment au sein de ce journal. Selon la taille du segment fixée à l’initialisation, il peut aller de 00000000 à 000000FF (256 segments de 16 Mo, configuration par défaut, soit 4 Go), à 00000FFF (4096 segments de 1 Mo), ou à 0000007F (128 segments de 32 Mo, exemple ci-dessus), etc. Une fois ce maximum atteint, le numéro de journal au centre est incrémenté et les numéros de segments reprennent à zéro.

L’ordre d’écriture des journaux est numérique (en hexadécimal), et leur archivage doit suivre cet ordre. Il ne faut pas se fier à la date des fichiers pour le tri : pour des raisons de performances, PostgreSQL recycle généralement les fichiers en les renommant. Dans l’exemple ci-dessus, le dernier journal écrit est 000000020000014300000020 et non 000000020000014300000034. À partir de la v12, ce mécanisme peut toutefois être désactivé en passant wal_recycle à off (ce qui a un intérêt sur certains systèmes de fichiers comme ZFS).

Dans le cadre d’un archivage PITR et/ou d’une réplication par log shipping, le sous-répertoire pg_wal/archive_status indique l’état des journaux dans le contexte de l’archivage. Les fichiers .ready indiquent les journaux encore à archiver (normalement peu nombreux), les .done ceux déjà archivés.

À partir de la version 12, il est possible de connaître facilement le contenu de ce répertoire en utilisant la fonction pg_ls_archive_statusdir() :

# SELECT * FROM pg_ls_archive_statusdir() ORDER BY 1 ;
              name              | size |      modification      
--------------------------------+------+------------------------
 000000010000000000000067.done  |    0 | 2019-09-02 15:52:57+02
 000000010000000000000068.done  |    0 | 2019-09-02 15:52:57+02
 000000010000000000000069.done  |    0 | 2019-09-02 15:52:58+02
 00000001000000000000006A.ready |    0 | 2019-09-02 15:53:53+02
 00000001000000000000006B.ready |    0 | 2019-09-02 15:53:53+02
 00000001000000000000006C.ready |    0 | 2019-09-02 15:53:54+02
 00000001000000000000006D.ready |    0 | 2019-09-02 15:53:54+02
 00000001000000000000006E.ready |    0 | 2019-09-02 15:53:54+02
 00000001000000000000006F.ready |    0 | 2019-09-02 15:53:54+02
 000000010000000000000070.ready |    0 | 2019-09-02 15:53:55+02
 000000010000000000000071.ready |    0 | 2019-09-02 15:53:55+02
(11 lignes)

Le répertoire pg_xact contient l’état de toutes les transactions passées ou présentes sur la base (validées, annulées, en sous-transaction ou en cours), comme on le détaillera dans le module « Mécanique du moteur transactionnel ».

Les logs (journaux) contenus dans pg_xact ne doivent jamais être effacés. Ces fichiers sont cruciaux au bon fonctionnement de la base.

D’autres répertoires contiennent des fichiers essentiels à la gestion des transactions :

  • pg_commit_ts contient l’horodatage de la validation de chaque transaction ;
  • pg_multixact est utilisé dans l’implémentation des verrous partagés (SELECT xxx FOR SHARE) ;
  • pg_serial est utilisé dans l’implémentation de SSI (Serializable Snapshot Isolation) ;
  • pg_snapshots est utilisé pour stocker les snapshots exportés de transactions ;
  • pg_subtrans stocke l’imbrication des transactions lors de sous-transactions (les SAVEPOINTS) ;
  • pg_twophase est utilisé pour l’implémentation du Two-Phase Commit, aussi appelé transaction préparée, 2PC, ou transaction XA dans le monde Java par exemple.

La version 10 a été l’occasion du changement de nom de quelques répertoires pour des raisons de cohérence et réduire les risques de fausses manipulations. Jusqu’en 9.6, pg_wal s’appelait pg_xlog, pg_xact s’appelait pg_clog.

Les fonctions et outils ont été renommés en conséquence :

  • dans les noms de fonctions et d’outils, xlog a été remplacé par wal (par exemple pg_switch_xlog est devenue pg_switch_wal) ;
  • toujours dans les fonctions, location a été remplacé par lsn.

Gestion de la réplication

  • pg_logical/
  • pg_repslot/

pg_logical contient des informations sur la réplication logique.

pg_replslot contient des informations sur les slots de réplications.

Ces répertoires n’existent pas avant la 9.4.


Le répertoire des tablespaces

  • pg_tblspc/ : liens symboliques vers les répertoires contenant des tablespaces

pg_tblspc contient des liens symboliques vers les répertoires contenant des tablespaces. Chaque lien symbolique a comme nom l’OID du tablespace dans pg_tablespace.

Sous Windows, il ne s’agit pas de liens symboliques comme sous Unix, mais de Reparse Points, qu’on trouve parfois aussi nommés Junction Points dans la documentation de Microsoft.


Statistiques d’activité

  • pg_stat/
  • pg_stat_tmp/

pg_stat_tmp est le répertoire par défaut de stockage des statistiques d’exécution de PostgreSQL, comme les entrées-sorties ou les opérations de modifications sur les tables. Ces fichiers pouvant générer une grande quantité d’entrées-sorties, l’emplacement du répertoire peut être modifié avec le paramètre stats_temp_directory. Il est modifiable à chaud par édition du fichier de configuration puis demande de rechargement de la configuration au serveur PostgreSQL. À l’arrêt, les fichiers sont copiés dans le répertoire pg_stat/ (ou global/ avant la 9.3).

Exemple d’un répertoire de stockage des statistiques déplacé en tmpfs (défaut sous Debian) :

SHOW stats_temp_directory;

           stats_temp_directory
-----------------------------------------
 /var/run/postgresql/12-main.pg_stat_tmp
(1 row)

Autres répertoires

  • pg_dynshmem/
  • pg_notify/

pg_dynshmem est utilisé par les extensions utilisant de la mémoire partagée dynamique.

pg_notify est utilisé par le mécanisme de gestion de notification de PostgreSQL (LISTEN et NOTIFY) qui permettent de passer des messages de notification entre sessions.


Fichiers de configuration

  • pg_hba.conf
  • pg_ident.conf
  • postgresql.conf
  • postgresql.auto.conf

Les fichiers de configuration sont habituellement les 4 suivants :

  • postgresql.conf : il contient une liste de paramètres, sous la forme paramètre=valeur. Tous les paramètres énoncés précédemment sont modifiables (et présents) dans ce fichier.
  • pg_hba.conf : il contient les règles d’authentification à la base.
  • pg_ident.conf : il complète pg_hba.conf, quand on décide de se reposer sur un mécanisme d’authentification extérieur à la base (identification par le système ou par un annuaire par exemple).
  • postgresql.auto.conf : il stocke les paramètres de configuration fixés en utilisant la commande ALTER SYSTEM et surcharge donc postgresql.conf.

Autres fichiers

  • PG_VERSION : fichier contenant la version majeure de l’instance
  • postmaster.pid
    • nombreuses informations sur le processus père
    • fichier externe possible, paramètre external_pid_file
  • postmaster.opts

PG_VERSION est un fichier. Il contient en texte lisible la version majeure devant être utilisée pour accéder au répertoire (par exemple 9.6). On trouve ces fichiers PG_VERSION à de nombreux endroits de l’arborescence de PostgreSQL, par exemple dans chaque répertoire de base du répertoire PGDATA/base/ ou à la racine de chaque tablespace.

Le fichier postmaster.pid est créé au démarrage de PostgreSQL. PostgreSQL y indique le PID du processus père sur la première ligne, l’emplacement du répertoire des données sur la deuxième ligne et des informations sur le segment de mémoire partagée sur la troisième ligne. Par exemple :

~$ cat /var/lib/postgresql/12/data/postmaster.pid
7771
/var/lib/postgresql/12/data
1503584802
5432
/tmp
localhost
  5432001  54919263
ready

$ ps -HFC postgres
UID  PID    SZ     RSS PSR STIME TIME   CMD
pos 7771 0 42486 16536   3 16:26 00:00  /usr/local/pgsql/bin/postgres ↩
                                          -D /var/lib/postgresql/12/data
pos 7773 0 42486  4656   0 16:26 00:00  postgres: checkpointer
pos 7774 0 42486  5044   1 16:26 00:00  postgres: background writer
pos 7775 0 42486  8224   1 16:26 00:00  postgres: walwriter
pos 7776 0 42850  5640   1 16:26 00:00  postgres: autovacuum launcher
pos 7777 0  6227  2328   3 16:26 00:00  postgres: stats collector
pos 7778 0 42559  3684   0 16:26 00:00  postgres: logical replication launcher

$ ipcs -p |grep  7771
54919263   postgres   7771       10640

$ ipcs  | grep 54919263
0x0052e2c1 54919263   postgres   600        56         6

Le processus père de cette instance PostgreSQL a comme PID le 7771. Ce processus a bien réclamé une sémaphore d’identifiant 54919263. Cette sémaphore correspond à des segments de mémoire partagée pour un total de 56 octets. Le répertoire de données se trouve bien dans /var/lib/postgresql/12/data.

Le fichier postmaster.pid est supprimé lors de l’arrêt de PostgreSQL. Cependant, ce n’est pas le cas après un arrêt brutal. Dans ce genre de cas, PostgreSQL détecte le fichier et indique qu’il va malgré tout essayer de se lancer s’il ne trouve pas de processus en cours d’exécution avec ce PID. Il peut être placé ailleurs grâce au paramètre external_pid_file, c’est notamment le défaut sous Debian :

external_pid_file = '/var/run/postgresql/12-main.pid'

Quant au fichier postmaster.opts, il contient les arguments en ligne de commande correspondant au dernier lancement de PostgreSQL. Il n’est jamais supprimé. Par exemple :

$ cat $PGDATA/postmaster.opts
/usr/local/pgsql/bin/postgres "-D" "/var/lib/postgresql/12/data"

Paramètres en lecture seule

  • Dépendent d’options de compilation ou initdb
  • Quasiment jamais modifiés
  • Tailles de bloc ou de fichier
    • block_size : 8 ko
    • wal_block_size : 8 ko
    • segment_size : 1 Go
    • wal_segment_size : 16 Mo (option --wal-segsize d’initdb en v11)

Ces paramètres sont en lecture seule, mais peuvent être consultés par la commande SHOW, ou en interrogeant la vue pg_settings. On peut aussi obtenir l’information via la commande pg_controldata.

  • block_size est la taille d’un bloc de données de la base, par défaut 8192 octets ;
  • wal_block_size est la taille d’un bloc de journal, par défaut 8192 octets ;
  • segment_size est la taille maximum d’un fichier de données, par défaut 1 Go ;
  • wal_segment_size est la taille d’un fichier de journal de transactions, par défaut 16 Mo.

Ces paramètres sont tous fixés à la compilation, sauf wal_segment_size à partir de la version 11 : initdb accepte alors l’option --wal-segsize et l’on peut monter la taille des journaux de transactions à 1 Go. Cela n’a d’intérêt que pour des instances générant énormément de journaux.

Un moteur compilé avec des options non standards ne pourra pas ouvrir des fichiers n’ayant pas les mêmes valeurs pour ces options.


postgresql.conf

Fichier principal de configuration :

  • emplacement : PGDATA ou pg_ctl --config_file
  • format clé = 'valeur'
  • sections, commentaires (redémarrage !)
  • inclusion externe  : include, include_if_exists
  • surcharge :
    • postgresql.auto.conf
    • paramètres de pg_ctl
    • ALTER DATABASE|ROLE ... SET paramètre = ... et session
  • Consulter : SHOW, pg_settings, pg_file_settings

C’est le fichier le plus important. Il contient le paramétrage de l’instance. PostgreSQL le cherche au démarrage dans le PGDATA (c’est le cas général, notamment sous Red Hat/CentOS) ou grâce au paramètre --config_file fourni à pg_ctl (les outils Debian le font car le fichier est dans /etc/postgresql/<version>/<nom instance>). Dans le doute on peut consulter la valeur de config_file :

# SHOW config_file;

               config_file
---------------------------------------------
 /var/lib/postgresql/12/data/postgresql.conf

Le format est un paramètre par ligne, sous le format clé = valeur. Les commentaires commencent par « # » (croisillon) et les chaînes de caractères doivent être encadrées de « ’ » (single quote). Par exemple :

data_directory = '/var/lib/postgresql/12/main'
listen_addresses = 'localhost'
port = 5432
shared_buffers = 128MB

Les valeurs de ce fichier ne seront pas forcément les valeurs actives !

En effet, si des options sont passées en arguments à pg_ctl, elles seront prises en priorité par rapport à celles du fichier de configuration. Le fichier postgresql.auto.conf contient le résultat des commandes ALTER SYSTEM SET paramètre = valeur (il est d’abord utilisé par les administrateurs n’ayant pas accès au système de fichiers).

On peut aussi surcharger les options modifiables à chaud par utilisateur, par base, et par combinaison « utilisateur+base », avec par exemple :

ALTER ROLE nagios SET log_min_duration_statement TO '1min';
ALTER DATABASE dwh SET work_mem TO '1GB';
ALTER ROLE patron IN DATABASE dwh SET work_mem TO '2GB';

(Ces surcharges sont visibles dans la table pg_db_role_setting.)

Ainsi, l’ordre des surcharges est le suivant :

paramètre par défaut
  -> postgresql.conf
    -> ALTER SYSTEM SET (postgresql.auto.conf)
      -> option de pg_ctl / postmaster
        -> paramètre par base
          -> paramètre par rôle
            -> paramètre base+rôle
              -> paramètre dans la chaîne de connexion
                -> paramètre de session (SET)
                  -> paramètre de transaction (SET LOCAL)

La meilleure source d’information est la vue pg_settings :

SELECT name,source,context,setting,boot_val,reset_val
FROM pg_settings
WHERE name IN ('client_min_messages', 'wal_keep_segments', 'wal_segment_size');

        name         |  source  | context  | setting  | boot_val | reset_val
---------------------+----------+----------+----------+----------+-----------
 client_min_messages | default  | user     | notice   | notice   | notice
 wal_keep_segments   | default  | sighup   | 0        | 0        | 0
 wal_segment_size    | override | internal | 16777216 | 16777216 | 16777216

On constate par exemple que dans la session ayant effectué la requête, client_min_messages a été modifié à la valeur debug. On peut aussi voir le contexte dans lequel le paramètre est modifiable : le client_min_messages est modifiable par l’utilisateur dans sa session. Le wal_keep_segments seulement par sighup, c’est-à-dire par un pg_ctl reload, et le wal_segment_size n’est pas modifiable après l’initialisation de l’instance.

De nombreuses autres colonnes sont disponibles dans pg_settings, comme une description détaillée du paramètre, l’unité de la valeur, ou le fichier et la ligne d’où proviennent le paramètre. Depuis la version 9.5, une nouvelle colonne est apparue, nommée pending_restart. Elle indique si un paramètre a été modifié mais nécessite un redémarrage pour être appliqué.

On peut aussi inclure d’autres fichiers dans le fichier postgresql.conf, par la syntaxe

include 'nom_fichier'

Ce fichier est alors inclus à l’endroit où la directive include est positionnée. Si le fichier n’existe pas, une erreur FATAL est levée. La clause include_if_exists ne fait que notifier l’absence du fichier, mais poursuit la lecture du fichier de base.

À partir de la version 9.5, il existe aussi la vue pg_file_settings. Elle indique la configuration présente dans les fichiers de configuration (pas celle active !). Elle peut être utile lorsque la configuration est répartie dans plusieurs fichiers. Par exemple, suite à un ALTER SYSTEM, les paramètres sont ajoutés dans postgresql.auto.conf mais un rechargement de la configuration n’est pas forcément suffisant pour qu’ils soient pris en compte :

ALTER SYSTEM SET work_mem TO '16MB' ;
ALTER SYSTEM SET max_connections TO 200 ;

SELECT pg_reload_conf() ;
 pg_reload_conf
----------------
 t
SELECT * FROM pg_file_settings
WHERE name IN ('work_mem','max_connections')
ORDER BY name ;

-[ RECORD 1 ]-------------------------------------------------
sourcefile | /var/lib/postgresql/12/data/postgresql.conf
sourceline | 64
seqno      | 2
name       | max_connections
setting    | 100
applied    | f
error      |
-[ RECORD 2 ]-------------------------------------------------
sourcefile | /var/lib/postgresql/12/data/postgresql.auto.conf
sourceline | 4
seqno      | 17
name       | max_connections
setting    | 200
applied    | f
error      | setting could not be applied
-[ RECORD 3 ]-------------------------------------------------
sourcefile | /var/lib/postgresql/12/data/postgresql.auto.conf
sourceline | 3
seqno      | 16
name       | work_mem
setting    | 16MB
applied    | t
error      |

pg_hba.conf et pg_ident.conf

  • Authentification multiple, suivant l’utilisateur, la base et la source de la connexion.
    • pg_hba.conf (Host Based Authentication)
    • pg_ident.conf : si mécanisme externe d’authentification
    • paramètres : hba_file et ident_file

L’authentification est paramétrée au moyen du fichier pg_hba.conf. Dans ce fichier, pour une tentative de connexion à une base donnée, pour un utilisateur donné, pour un transport (IP, IPV6, Socket Unix, SSL ou non), et pour une source donnée, ce fichier permet de spécifier le mécanisme d’authentification attendu.

Si le mécanisme d’authentification s’appuie sur un système externe (LDAP, Kerberos, Radius…), des tables de correspondances entre utilisateur de la base et utilisateur demandant la connexion peuvent être spécifiées dans pg_ident.conf.

Ces noms de fichiers ne sont que les noms par défaut. Ils peuvent tout à fait être remplacés en spécifiant de nouvelles valeurs de hba_file et ident_file dans postgresql.conf (les installations Red Hat et Debian utilisent là aussi des emplacements différents, comme pour postgresql.conf).


Tablespaces

  • Espace de stockage d’objets, pas de sens logique
  • Simple répertoire hors de PGDATA + lien symbolique
  • Pour répartir I/O et volumétrie
  • Pour quotas (par le FS)
  • Utilisation selon des droits

Un tablespace, vu de PostgreSQL, est un espace de stockage des objets (tables et indexes 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 schémas).

Vu du système d’exploitation, il s’agit d’un répertoire impérativement placé hors dePGDATA. Certains outils poseraient problème sinon. De ce fait, à partir de la version 9.5, PostgreSQL renvoie un avertissement si ce conseil n’est pas suivi :

postgres=# CREATE TABLESPACE ts1 LOCATION '/var/lib/postgresql/10/data/ts1';
WARNING:  tablespace location should not be inside the data directory
CREATE TABLESPACE

Il est aussi déconseillé de mettre un numéro de version dans le chemin du tablespace ! il est géré par PostgreSQL même dans le tablespace, et il en tient notamment compte dans les migrations avec pg_upgrade.

Les tablespaces sont visibles dans la table système pg_tablespace, ou dans psql avec \db+. Ils sont stockés au niveau du système de fichiers, sous forme de liens symboliques (ou de reparse points sous Windows), dans le répertoire PGDATA/pg_tblspc. Exemple :

# \db
                    Liste des tablespaces
    Nom     | Propriétaire |              Emplacement               
------------+--------------+----------------------------------------
 froid      | postgres     | /HDD/tbl/froid
 chaud      | postgres     | /SSD/tbl/chaud 
 pg_default | postgres     | 
 pg_global  | postgres     | 
# ls -l /HDD/tbl/froid
drwxr--r-- 3 postgres postgres 20 oct.  23 18:15 PG_12_201909212

Les cas d’utilisation des tablespaces dans PostgreSQL sont :

  • la répartition des entrées-sorties, mais c’est de moins en moins courant (les SAN ou la virtualisation ne permettent plus d’agir sur un disque à la fois) ;
  • le déport des tris vers un volume dédié ;
  • la séparation entre données froides et chaudes sur des disques de performances différentes ;
  • les quotas : PostgreSQL ne disposant pas d’un système de quotas, les tablespaces peuvent permettre de contourner cette limitation : une transaction voulant étendre un fichier sera alors annulée avec l’erreur cannot extend file.

Tablespaces : mise en place

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

CREATE DATABASE nom TABLESPACE 'chaud';

ALTER DATABASE nom SET default_tablespace TO 'chaud';

GRANT CREATE ON TABLESPACE chaud TO un_utilisateur ;

ALTER TABLE ma_table SET TABLESPACE chaud ;  -- verrou !

Les ordres ci-dessus permettent de :

  • créer un tablespace simplement en indiquant son emplacement dans le système de fichiers du serveur ;
  • créer une base de données dont le tablespace par défaut sera celui indiqué ;
  • modifier le tablespace par défaut d’une base ;
  • donner le droit de créer des tables dans un tablespace à un utilisateur (c’est nécessaire avant de l’utiliser) ;
  • déplacer une table dans un tablespace (Attention ! la table est totalement verrouillée le temps du déplacement).

Tablespaces : paramètres liés

  • default_tablespace
  • temp_tablespaces
  • performances :
    • seq_page_cost, random_page_cost, effective_io_concurrency

Le paramètre default_tablespace (défini sur un utilisateur, une base, le temps d’une session…) permet d’utiliser un autre tablespace par défaut.

On peut définir un ou plusieurs tablespaces pour les opérations de tri, dans le paramètre temp_tablespaces (il faudra donner des droits dessus aux utilisateurs avec GRANT CREATE ON TABLESPACE ... TO ... pour qu’ils soient utilisables). Si plusieurs sont paramétrés, ils seront utilisés de façon aléatoire à chaque création d’objet temporaire, afin de répartir la charge. Un gros avantage est de ne plus risquer de saturer la partition du PGDATA en cas de fichiers temporaires énormes.

Reste le cas des disques de performances différentes (selon le type de disque SSD/SAS/SATA) : on précisera alors des valeurs de effective_io_concurrency, seq_page_cost et random_page_cost différentes sur chaque tablespace.

Par exemple sur un SSD :

# ALTER TABLESPACE chaud SET ( random_page_cost = 1 );
# ALTER TABLESPACE chaud SET ( effective_io_concurrency = 100 );

Les fichiers de traces (journaux)

  • Fichiers texte traçant l’activité
  • Très paramétrables
  • Gestion des fichiers soit :
    • Par PostgreSQL
    • Délégués au système d’exploitation (syslog, eventlog)

Le paramétrage des journaux est très fin. Ils sont traités dans un prochain chapitre.

Si logging_collector est activé, c’est-à-dire que PostgreSQL collecte lui-même ses traces, l’emplacement de ces journaux se paramètre grâce à log_directory, le répertoire où les stocker, et log_filename, le nom de fichier à utiliser, ce nom pouvant utiliser des échappements comme %d pour le jour de la date, par exemple. Les droits attribués au fichier sont précisés par le paramètre log_file_mode.

Un exemple pour log_filename avec date et heure serait :

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

La liste des échappements pour le paramètre log_filename est disponible dans la page de manuel de la fonction strftime sur la plupart des plateformes de type UNIX.


Shared buffers

  • Shared buffers ou blocs de mémoire partagée
    • Partage les blocs entre les processus
    • Cache en lecture ET écriture
    • Double emploi partiel avec le cache du système (voir effective_cache_size)
    • Importants pour les performances
  • Dimensionnement en première intention :
    • 1/4 RAM
    • max 8 Go

PostgreSQL dispose de son propre mécanisme de cache. Toute donnée lue l’est de ce cache. Si la donnée n’est pas dans le cache, le processus devant effectuer cette lecture l’y recopie avant d’y accéder dans le cache.

L’unité de travail du cache est le bloc (de 8 ko par défaut) de données. C’est-à-dire qu’un processus charge toujours un bloc dans son entier quand il veut lire un enregistrement. Chaque bloc du cache correspond donc exactement à un bloc d’un fichier d’un objet. Cette information est d’ailleurs, bien sûr, stockée en en-tête du bloc de cache.

Tous les processus accèdent à ce cache unique. C’est la zone la plus importante, par la taille, de la mémoire partagée. Toute modification de données est tracée dans le journal de transaction, puis modifiée dans ce cache. Elle n’est pas écrite sur le disque par le processus effectuant la modification. Tout accès à un bloc nécessite la prise de verrous. Un pin lock, qui est un simple compteur, indique qu’un processus se sert du buffer, et qu’il n’est donc pas réutilisable. C’est un verrou potentiellement de longue durée. Il existe de nombreux autres verrous, de plus courte durée, pour obtenir le droit de modifier le contenu d’un buffer, d’un enregistrement dans un buffer, le droit de recycler un buffer… mais tous ces verrous n’apparaissent pas dans la table pg_locks, car ils sont soit de très courte durée, soit partagés (comme le spin lock). Il est donc très rare qu’ils soient sources de contention, mais le diagnostic d’une contention à ce niveau est difficile.

Les lectures et écritures de PostgreSQL passent toutefois toujours par le cache du système. Les deux caches risquent donc de stocker les mêmes informations. Les algorithmes d’éviction sont différents entre le système et PostgreSQL, PostgreSQL disposant de davantage d’informations sur l’utilisation des données, et le type d’accès qui y est fait. La redondance est donc habituellement limitée.

Dimensionner correctement ce cache est important pour de nombreuses raisons.

Un cache trop petit :

  • ralentit l’accès aux données, car des données importantes risquent de ne plus s’y trouver ;
  • force l’écriture de données sur le disque, ralentissant les sessions qui auraient pu n’effectuer que des opérations en mémoire ;
  • limite le regroupement d’écritures, dans le cas où un bloc viendrait à être modifié plusieurs fois.

Un cache trop grand :

  • limite l’efficacité du cache système en augmentant la redondance de données entre les deux caches ;
  • peut ralentir PostgreSQL, car la gestion des shared_buffers a un coût en processeur.

Ce paramétrage du cache est malgré tout moins critique que sur de nombreux autres SGBD : le cache système limite la plupart du temps l’impact d’un mauvais paramétrage de shared_buffers, et il est donc préférable de sous-dimensionner shared_buffers que de le sur-dimensionner. Les 128 Mo sont sur ce point très conservateurs.

En première approche, la règle habituelle est de positionner shared_buffers à 1/4 de la mémoire totale du serveur au maximum, en se limitant à 8 Go. Ce peut être moins sous Windows. L’idéal est de mesurer l’impact avec des tests réalistes.

Un cache supplémentaire est disponible pour PostgreSQL : celui du système d’exploitation. Il est donc intéressant de préciser à PostgreSQL la taille approximative du cache, ou du moins de la part du cache qu’occupera PostgreSQL. Le paramètre effective_cache_size n’a pas besoin d’être très précis, mais il permet une meilleure estimation des coûts par le moteur. On le paramètre habituellement aux alentours des 2/3 de la taille du cache du système d’exploitation, pour un serveur dédié.


Notions essentielles

  • Buffer pin
  • Buffer dirty/clean
  • Compteur d’utilisation (usagecount)
  • Clocksweep

Les principales notions à connaître pour comprendre le mécanisme de gestion du cache de PostgreSQL sont :

  • Buffer pin : chaque processus voulant accéder à un buffer (un bloc du cache) doit d’abord en forcer le maintien en cache (to pin signifie épingler). Chaque processus accédant à un buffer incrémente ce compteur, et le décrémente quand il a fini. Un buffer dont le pin est différent de 0 ne peut donc pas être recyclé.
  • Buffer dirty/clean : un buffer est soit propre (clean), soit sale (dirty). Il est sale si son contenu dans le cache ne correspond pas à son contenu sur disque (il a été modifié dans le cache, mais pas encore resynchronisé). La différence fondamentale est qu’un buffer propre peut être supprimé du cache sans plus de formalité, alors qu’un buffer sale doit d’abord être resynchronisé, ce qui est bien plus coûteux.
  • Compteur d’utilisation : à chaque fois qu’un processus a fini de se servir d’un buffer (quand il enlève son pin), ce compteur est incrémenté (à hauteur de 5 dans l’implémentation actuelle). Un autre mécanisme décrémente ce compteur. Seuls un buffer dont ce compteur est à zéro peut voir son contenu remplacé par un nouveau bloc.
  • Clocksweep : on parle aussi d’algorithme de balayage. Un processus ayant besoin de charger un bloc de données dans le cache doit trouver un buffer disponible. Soit il y a encore des buffers vides (cela arrive principalement au démarrage d’une instance), soit il faut libérer un buffer. L’algorithme clocksweep parcourt la liste des buffers de façon cyclique à la recherche d’un buffer dont le compteur d’utilisation est à zéro. Tout buffer visité voit son compteur décrémenté de un. Le système effectue autant de passes que nécessaire sur tous les blocs jusqu’à trouver un buffer à 0. Ce clocksweep est effectué par chaque processus, au moment où ce dernier a besoin d’un nouveau buffer.

Contenu du cache

2 extensions en « contrib » :

  • pg_buffercache
  • pg_prewarm

Deux extensions sont livrées dans les contribs de PostgreSQL qui impactent le cache.

pg_buffercache permet de consulter le contenu du cache (à utiliser de manière très ponctuelle). La requête suivante indique les objets non système de la base en cours, présents dans le cache et s’ils sont dirty ou pas :

pgbench=# CREATE EXTENSION pg_buffercache ;

pgbench=# SELECT
    relname,
    isdirty,
    count(bufferid) AS blocs,
    pg_size_pretty(count(bufferid) * current_setting ('block_size')::int) AS taille
FROM pg_buffercache b
INNER JOIN pg_class c ON c.relfilenode = b.relfilenode
WHERE relname NOT LIKE 'pg\_%'
GROUP BY
        relname,
        isdirty
ORDER BY 1, 2 ;
        relname        | isdirty | blocs | taille  
-----------------------+---------+-------+---------
 pgbench_accounts      | f       |  8398 | 66 MB
 pgbench_accounts      | t       |  4622 | 36 MB
 pgbench_accounts_pkey | f       |  2744 | 21 MB
 pgbench_branches      | f       |    14 | 112 kB
 pgbench_branches      | t       |     2 | 16 kB
 pgbench_branches_pkey | f       |     2 | 16 kB
 pgbench_history       | f       |   267 | 2136 kB
 pgbench_history       | t       |   102 | 816 kB
 pgbench_tellers       | f       |    13 | 104 kB
 pgbench_tellers_pkey  | f       |     2 | 16 kB

L’extension pg_prewarm permet de précharger un objet dans le cache de PostgreSQL (s’il y tient, bien sûr) :

=# CREATE EXTENSION pg_prewarm ;
=# SELECT pg_prewarm ('nom_table_ou_index', 'buffer') ;

Il permet même de recharger dès le démarrage le contenu du cache lors d’un arrêt (voir la documentation).


Synchronisation en arrière plan

  • Le Background Writer synchronise les buffers :
    • de façon anticipée
    • une portion des pages à synchroniser
    • paramètres : bgwriter_delay, bgwriter_lru_maxpages, bgwriter_lru_multiplier et bgwriter_flush_after
  • Le checkpointer synchronise les buffers :
    • lors des checkpoints
    • synchronise toutes les dirty pages

Afin de limiter les attentes des sessions interactives, PostgreSQL dispose de deux processus, le Background Writer et le Checkpointer, tous deux essayant d’effectuer de façon asynchrone les écritures des buffers sur le disque. Le but étant que les temps de traitement ressentis par les utilisateurs soient les plus courts possibles, et d’essayer de lisser les écritures sur de plus grandes plages de temps (pour ne pas saturer les disques).

Le Background Writer est donc responsable d’un mécanisme :

Il anticipe les besoins de buffers des sessions. À intervalle régulier, il se réveille et synchronise un nombre de buffers proportionnel à l’activité sur l’intervalle précédent, dans ceux qui seront examinés par les sessions pour les prochaines allocations. Quatre paramètres régissent son comportement :

  • bgwriter_delay (défaut : 200 ms) : la fréquence à laquelle se réveille le Background Writer.
  • bgwriter_lru_maxpages (défaut : 100) : le nombre maximum de pages pouvant être écrites sur chaque tour d’activité. Ce paramètre permet d’éviter que le Background Writer ne veuille synchroniser trop de pages si l’activité des sessions est trop intense : dans ce cas, autant les laisser effectuer elles-mêmes les synchronisations, étant donné que la charge est forte.
  • bgwriter_lru_multiplier (defaut : 2) : le coefficient multiplicateur utilisé pour calculer le nombre de buffers à libérer par rapport aux demandes d’allocation sur la période précédente.
  • bgwriter_flush_after (défaut : 512 ko sous Linux, 0 ou désactivé ailleurs) : à partir de quelle quantité de données écrites une synchronisation sur disque est demandée.

Pour les paramètres bgwriter_lru_maxpages et bgwriter_lru_multiplier, lru signifie Least Recently Used que l’on pourrait traduire par « moins récemment utilisé ». Ainsi, pour ce mécanisme, le Background Writer synchronisera les pages du cache qui ont été utilisées le moins récemment.

Le paramètre bgwriter_flush_after définit le seuil au-delà duquel le moteur demandera la synchronisation des données en cache vers les fichiers sur disque.

Le Checkpointer est responsable d’un autre mécanisme :

Il synchronise tous les buffers dirty lors des checkpoints. Son rôle est d’effectuer cette synchronisation, en évitant de saturer les disques. Le paramètre checkpoint_completion_target (par défaut 0.5) est la cible en temps entre deux checkpoints à viser pour l’accomplissement de chaque checkpoint. Par exemple, à 0.5, un checkpoint devrait se terminer à la moitié du temps séparant habituellement deux checkpoints.

La synchronisation sur disque ne se fait qu’à partir d’une certaine quantité de données écrites, dépendant du paramètre checkpointer_flush_after (par défaut 256 ko sous Linux, 0 ou désactivé ailleurs).

Les valeurs par défaut sont la plupart du temps satisfaisantes. Nous verrons plus tard que passer checkpoint_completion_target à 0.9 (sa valeur maximale) améliore parfois les performances, sans ralentissement à craindre.

Nous expliquerons plus en détail le fonctionnement d’un checkpoint dans le chapitre suivant.

Noter qu’avant la 9.2 les deux rôles étaient assurés par le seul Background Writer.


Journalisation

  • Garantir la durabilité des données
  • Base encore cohérente après :
    • Un arrêt brutal des processus
    • Un crash machine
  • WAL : Write Ahead Logging
  • Écriture des modifications dans un journal avant de les effectuer.

La journalisation, sous PostgreSQL, permet de garantir l’intégrité des fichiers, et la durabilité des opérations :

  • L’intégrité : quoi qu’il arrive, excepté la perte des disques de stockage bien sûr, la base reste cohérente. Un arrêt d’urgence ne corrompra pas la base.
  • Toute donnée validée (COMMIT) est écrite. Un arrêt d’urgence ne va pas la faire disparaître.

Pour cela, le mécanisme est relativement simple : toute modification affectant un fichier sera d’abord écrite dans le journal. Les modifications affectant les vrais fichiers de données ne sont écrites que dans les shared buffers. Elles seront écrites de façon asynchrone, soit par un processus recherchant un buffer libre, soit par le Background Writer, soit par le Checkpointer.

Les écritures dans le journal, bien que synchrones, sont relativement performantes, car elles sont séquentielles (moins de déplacement de têtes pour les disques).


Checkpoint

  • Point de reprise
  • À partir d’où rejouer le journal ?
  • Données écrites au moins au niveau du checkpoint
  • Déclenchement :
    • checkpoint_timeout (périodique)
    • max_wal_size
  • Dilution des écritures
    • checkpoint_completion_target

PostgreSQL trace les modifications de données dans les journaux WAL. Si le système ou la base sont arrêtés brutalement, il faut que PostgreSQL puisse appliquer le contenu des journaux non traités sur les fichiers de données. Il a donc besoin de savoir à partir d’où rejouer ces données. Ce point est ce qu’on appelle un checkpoint, ou point de reprise.

  • Toute entrée dans les journaux est idempotente, c’est-à-dire qu’elle peut être appliquée plusieurs fois, sans que le résultat final ne soit changé. C’est nécessaire, au cas où la récupération serait interrompue, ou si un fichier sur lequel la reprise est effectuée était plus récent que l’entrée qu’on souhaite appliquer.
  • Tout fichier de journal antérieur à l’avant-dernier point de reprise valide (ou au dernier à partir de la version 11) peut être supprimé ou recyclé, car il n’est plus nécessaire à la récupération.
  • PostgreSQL a besoin de fichiers de données qui contiennent toutes les données jusqu’au point de reprise. Ils peuvent être plus récents et contenir des informations supplémentaires, ce n’est pas un problème.
  • Un checkpoint n’est pas un « instantané » cohérent de l’ensemble des fichiers. C’est simplement l’endroit à partir duquel on doit rejouer les journaux. Il faut donc pouvoir garantir que tous les buffers dirty dans le cache au démarrage du checkpoint auront été synchronisés sur le disque quand le checkpoint sera terminé, et donc marqué comme dernier checkpoint valide. Un checkpoint peut donc durer plusieurs minutes, sans que cela ne bloque l’activité.
  • C’est le processus checkpointer qui est responsable de l’écriture des buffers devant être synchronisés durant un checkpoint.

Les paramètres suivants ont une influence sur les checkpoints :

  • 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 (Attention : le terme peut porter à confusion, le volume de WAL peut dépasser max_wal_size en cas de forte activité, ce n’est pas une valeur plafond.) ;
  • checkpoint_timeout : le temps maximum en secondes entre deux checkpoints. Par défaut, 300 secondes ;
  • checkpoint_completion_target : la fraction de l’espace entre deux checkpoints que doit prendre l’écriture d’un checkpoint. Par défaut, 0.5, ce qui signifie que PostgreSQL ajuste le débit en écriture pour que le checkpoint se termine au bout de la moitié de checkpoint_timeout en cherchant à ne pas dépasser max_wal_size. La valeur préconisée est 0.9, car elle permet de lisser davantage les écritures dues aux checkpoints dans le temps ;
  • checkpoint_warning : si deux checkpoints sont rapprochés d’un intervalle de temps inférieur à celui-ci, un message d’avertissement sera écrit dans le journal. Par défaut, 30 secondes ;
  • checkpointer_flush_after : quantité de données écrites à partir de laquelle une synchronisation sur disque est demandée.

Les paramètres min_wal_size et max_wal_size sont apparus avec la version 9.5. Auparavant existait le paramètre checkpoint_segments. Il avait pour valeur le nombre de segments de journaux maximum entre deux checkpoints. Sa valeur par défaut était de 3 (soit 48 Mo) mais une valeur comprise entre 10 et 20 était généralement recommandée.

Le paramétrage du système d’exploitation peut aussi avoir une influence sur les checkpoints. Le processus checkpointer envoie ses écritures au système d’exploitation au fil de l’eau, mais doit effectuer un appel fsync (demande de synchronisation sur disque) pour les fichiers de la base, à la fin du checkpoint. Si le système a mis beaucoup de données en cache, cette dernière phase peut déclencher un fort pic d’activité. Avant PostgreSQL 9.6, ceci se paramètre sous Linux en abaissant les valeurs des sysctl vm.dirty_* ; les versions suivantes forcent des synchronisations plus fréquentes.


WAL buffers : journalisation en mémoire

  • Réduire les appels à fsync
  • Mutualiser les écritures entre transactions
  • Un processus d’arrière plan
  • Paramètres notables :
    • wal_buffers
    • wal_writer_flush_after
    • wal_compression
    • synchronous_commit
  • Attention : fsync = on, sinon corruption !

La journalisation s’effectue par écriture dans les journaux de transactions. Toutefois, afin de ne pas effectuer des écritures synchrones pour chaque opération dans les fichiers de journaux, les écritures sont préparées dans des tampons (buffers) en mémoire. Les processus écrivent donc leur travail de journalisation dans des buffers, ou WAL buffers. Ceux-ci sont vidés quand une session demande validation de son travail (COMMIT), qu’il n’y a plus de buffer disponible, ou que le WAL Writer se réveille (wal_writer_delay).

Écrire un bloc ou plusieurs séquentiels de façon synchrone sur un disque a le même coût à peu de chose près. Ce mécanisme permet donc de réduire fortement les demandes d’écriture synchrone sur le journal, et augmente donc les performances.

Afin d’éviter qu’un processus n’ait tous les buffers à écrire à l’appel de COMMIT, et que cette opération ne dure trop longtemps, un processus d’arrière plan appelé WAL Writer écrit à intervalle régulier tous les buffers à synchroniser de WAL buffers.

Les paramètres relatifs à ceci sont :

  • wal_buffers : taille des _WAL buffers, soit par défaut 1/32e de shared_buffers avec un maximum de 16 Mo (la taille d’un segment), des valeurs supérieures pouvant être intéressantes pour les très grosses charges ;
  • wal_writer_delay (défaut : 200 ms) : intervalle auquel le WAL Writer se réveille pour écrire les buffers non synchronisés ;
  • wal_writer_flush_after (défaut : 1 Mo) : au-delà de cette valeur, les journaux écrits sont synchronisés sur disque pour éviter l’accumulation dans le cache de l’OS ;
  • wal_sync_method : appel système à utiliser pour demander l’écriture synchrone (sauf très rare exception, PostgreSQL détecte tout seul le bon appel système à utiliser) ;
  • synchronous_commit (défaut : on) : la validation de la transaction en cours doit-elle déclencher une écriture synchrone dans le journal ? Le défaut permet de garantir la pérennité des données dès la fin du COMMIT. Mais ce paramètre de session peut être modifié à la volée par une commande SET, et passé à off si on peut accepter une perte de données de 3 × wal_writer_delay (600 ms) ou de wal_writer_flush_after octets écrits. On accélère ainsi notablement les flux de petites transactions. La base restera, quoi qu’il arrive, cohérente.
  • full_page_writes : doit-on réécrire une image complète d’une page suite à sa première modification après un checkpoint ? Sauf cas très particulier, comme un système de fichiers Copy On Write comme ZFS ou btrfs, ce paramètre doit rester à on pour éviter des corruptions de données ;
  • wal_compression (9.5+) : compresse les blocs complets enregistrés dans les journaux de transactions, réduisant le volume des WAL et la charge en écriture sur les disques. Le rejeu des WAL est aussi plus rapide, ce qui accélère la réplication et la reprise après un crash. Le prix est une augmentation de la consommation en CPU ;
  • commit_delay, commit_siblings : mécanisme de regroupement de transactions. Si on a au moins commit_siblings transactions en cours, attendre jusqu’à commit_delay (en microsecondes) au moment de valider une transaction pour permettre à d’autres transactions de s’y rattacher. Ce mécanisme est désactivé par défaut, accroît la latence de certaines transactions, et n’apporte un gain de performance global qu’avec de nombreuses transactions en parallèle.
  • fsync : doit-on réellement effectuer les écritures synchrones ? Le défaut est on et il est très fortement conseillé de le laisser ainsi en production. Avec off, les performances en écritures sont certes très accélérées, mais en cas d’arrêt d’urgence de l’instance, les données seront totalement corrompues ! Il n’est donc intéressant de le passer à off que très temporairement, pendant le chargement initial d’une nouvelle instance par exemple, sans oublier de revenir à on. D’autres paramètres et techniques existent pour accélérer les écritures et sans corrompre votre instance, si vous êtes prêt à perdre certaines données non critiques : synchronous_commità off, les tables unlogged

Archivage : conservation des journaux

  • Récupération à partir d’une vieille sauvegarde
  • Sauvegarde à chaud
  • Sauvegarde en continu
  • Paramètres : wal_level, archive_mode, archive_command et archive_timeout

Les journaux permettent de rejouer, suite à un arrêt brutal de la base, toutes les modifications depuis le dernier checkpoint. Les journaux devenus obsolète depuis le dernier checkpoint (l’avant-dernier avant la version 11) sont à terme recyclés ou supprimés, car ils ne sont plus nécessaires à la réparation de la base.

Le but de l’archivage est de stocker ces journaux, afin de pouvoir rejouer leur contenu, non plus depuis le dernier checkpoint, mais depuis une sauvegarde, bien plus ancienne. Le mécanisme d’archivage permet donc de repartir d’une sauvegarde binaire de la base (c’est-à-dire des fichiers, pas un pg_dump), et de réappliquer le contenu des journaux archivés.

Ce mécanisme permet par ailleurs d’effectuer une sauvegarde base ouverte (c’est-à-dire pendant que les fichiers de la base sont en cours de modification). Il suffit de rejouer tous les journaux depuis le checkpoint précédent la sauvegarde jusqu’à la fin de la sauvegarde. L’application de ces journaux permet de rendre à nouveau cohérents les fichiers de données, même si ils ont été sauvegardés en cours de modification.

Ce mécanisme permet aussi de fournir une sauvegarde continue de la base. En effet, rien n’oblige à rejouer tout ce qui se trouve dans l’archive. Lors de la récupération, on peut préciser le point exact (en temps ou en numéro de transaction) où l’on souhaite arrêter la récupération. Une base en archivage peut donc être restaurée à n’importe quel point dans le temps. On parle aussi de PITR (Point In Time Recovery, récupération à un point dans le temps) dans la documentation.

Ce mécanisme permet enfin de créer une copie de la base de production, en transférant les fichiers archivés et en les appliquant sur cette seconde base. Il suffit de restaurer une sauvegarde à chaud de la base de production sur le serveur dit « de standby », puis d’appliquer les journaux sur cette base de standby au fur et à mesure de leur génération.

Tout ceci est revu dans le module « Point In Time Recovery ».

Les paramètres associés sont :

  • wal_level : minimal (défaut jusqu’en version 9.6 incluse), replica (défaut en 10) ou logical, suivant ce qu’on souhaite faire des journaux : juste récupérer suite à un crash, les archiver ou alimenter une instance en réplication, ou utiliser de la réplication logique.
  • archive_mode : (off par défaut) pour activer l’archivage ;
  • archive_command : la commande pour archiver un journal (souvent à base de rsync ou fournie par un outil de sauvegarde PITR) ;
  • archive_timeout : au bout de combien de temps PostgreSQL force un changement de journal, même s’il n’est pas fini, afin de l’archiver.

Avant la version 9.6, il existait deux niveaux intermédiaires pour le paramètre wal_level : archive et hot_standby. Le premier permettait seulement l’archivage, le second permettait en plus d’avoir un serveur secondaire en lecture seule. Ces deux valeurs ont été fusionnées en replica avec la version 9.6. Les anciennes valeurs sont toujours acceptées, et remplacées silencieusement par la nouvelle valeur.


Streaming Replication

  • Appliquer les journaux :

    • Non plus fichier par fichier
    • Mais entrée par entrée (en flux continu)
    • Instance Standby très proche de la production
    • Paramètres : max_wal_senders, wal_keep_segments, wal_sender_delay et wal_level

Le mécanisme de Streaming Replication a été une des nouveautés majeures de la version 9.0. Il n’est plus nécessaire d’attendre qu’un fichier de journal soit généré entièrement sur le serveur primaire pour appliquer son contenu sur le serveur secondaire.

Celui-ci se connecte sur le primaire, et lui demande à intervalle régulier de lui envoyer tout ce qui a été généré depuis la dernière interrogation.

Voici les paramètres concernés :

  • max_wal_senders : le nombre maximum de processus d’envoi de journaux démarrés sur le primaire (1 par serveur secondaire).
  • wal_keep_segments : le nombre de journaux à garder en ligne pour pouvoir les envoyer aux secondaires, même si ils ne sont plus nécessaires à la récupération sur le primaire (ils sont antérieurs au dernier checkpoint) ;
  • wal_sender_delay : la fréquence à laquelle le primaire se réveille pour envoyer les nouvelles entrées de journal aux secondaires ;
  • wal_level : doit être au moins à archive (avant la 9.6) ou replica PostgreSQL 9.6 et après), pour pouvoir être envoyé aux secondaires.

Une configuration supplémentaire doit se faire sur le serveur secondaire, indiquant comment récupérer les fichiers de l’archive, et comment se connecter au primaire pour récupérer des journaux. Elle a lieu dans le fichier recovery.conf jusqu’à la version 12, puis dans le fichier postgresql.conf.


Hot Standby

  • Base de Standby accessible en lecture
  • Peut basculer en lecture/écriture sans redémarrage (sessions conservées)
  • Peut fonctionner avec la Streaming Replication
  • Paramètres
    • sur le serveur secondaire : hot_standby, max_standby_archive_delay, max_standby_streaming_delay

    • sur le serveur primaire : wal_level

L’autre importante nouveauté de PostgreSQL 9.0 a été la possibilité pour une base de Standby d’être accessible en lecture seule, alors qu’elle applique les journaux provenant de l’instance primaire. Il est possible que des modifications devant être appliquées par les journaux entrent en conflit avec les requêtes en cours. Le retard acceptable est déclaré par la configuration.

Voici les paramètres concernés :

  • hot_standby : on ou off Active ou non la fonctionnalité lorsque la base est en récupération.
  • max_standby_archive_delay : quel est le délai maximum acceptable dans l’application de journaux venant de l’archive? Passé ce délai, les requêtes gênantes sur le secondaire seront automatiquement annulées.
  • max_standby_streaming_delay : quel est le délai maximum acceptable dans l’application de journaux venant par le mécanisme de Streaming Replication? Passé ce délai, les requêtes gênantes sur le secondaire seront automatiquement annulées.

Et sur le serveur primaire :

  • wal_level : doit être à replica afin de générer quelques informations supplémentaires à destination de la base de Standby.

Là aussi, une configuration supplémentaire doit se faire sur le serveur secondaire.


Statistiques

  • Collecte de deux types de statistiques différents :
    • Statistiques d’activité
    • Statistiques sur les données

PostgreSQL collecte deux types de statistiques différentes :

  • Les statistiques d’activité : elles permettent de mesurer l’activité de la base. Notamment :
    • Combien de fois cette table a-t-elle été parcourue séquentiellement ?
    • Combien de blocs ont été trouvés dans le cache pour ce parcours d’index, et combien ont du être demandés au système d’exploitation ?
    • Quelles sont les requêtes en cours d’exécution ?
    • Combien de buffers ont été écrits par le background writer ? Par les processus eux-mêmes ? durant un checkpoint?
  • Les statistiques sur les données : elles sont utilisées par l’optimiseur de requêtes dans sa recherche du meilleur plan d’exécution. Elles contiennent entre autres les informations suivantes :
    • taille des tables ;
    • taille moyenne d’un enregistrement de table ;
    • taille moyenne d’un attribut.

Statistiques sur l’activité

  • Collectées par chaque session durant son travail
  • Remontées au stats collector
  • Stockées régulièrement dans un fichier, consultable par des vues systèmes
  • Paramètres :
    • track_activities, track_activity_query_size, track_counts, track_io_timing et track_functions
    • update_process_title et stats_temp_directory

Chaque session collecte des statistiques, dès qu’elle effectue une opération.

Ces informations, si elles sont transitoires, comme la requête en cours, sont directement stockées dans la mémoire partagée de PostgreSQL.

Si elles doivent être agrégées et stockées, elles sont remontées au processus responsable de cette tâche, le Stats Collector.

Voici les paramètres concernés :

  • track_activities : les processus doivent-ils mettre à jour leur activité dans pg_stat_activity? (on par défaut)
  • track_activity_query_size : quelle est la taille maximum du texte de requête pouvant être stocké dans pg_stat_activity (1024 caractères par défaut, à monter vers 10 000 si les requêtes sont longues ; cela nécessite un redémarrage) ;
  • track_counts : les processus doivent-ils collecter des informations sur leur activité ? (on par défaut)
  • track_io_timing : les processus doivent-ils collecter des informations de chronométrage sur les lectures et écritures ? Ce paramètre complète les champs blk_read_time et blk_write_time dans pg_stat_database, pg_stat_statements (mêmes champs) et les plans d’exécutions appelés avec EXPLAIN (ANALYZE,BUFFERS) (off par défaut ; avant de l’activer sur une machine peu performante, vérifiez l’impact avec l’outil pg_test_timing)
  • track_functions : les processus doivent-ils aussi collecter des informations sur l’exécution des routines stockées (none par défaut, pl pour ne tracer que les routines en langages procéduraux, all pour tracer aussi les routines en C et en SQL) ;
  • update_process_title : le titre du processus (visible par exemple avec ps -ef sous Unix) sera modifié si cette option est à on (défaut sous Unix ; mettre à off sous Windows pour des raisons de performance) ;
  • stats_temp_directory : le fichier de statistiques pouvant être source de contention (il peut devenir gros, et est réécrit fréquemment), il peut être stocké ailleurs que dans le répertoire de l’instance PostgreSQL, par exemple sur un ramdisk ou tmpfs (c’est le défaut sous Debian).

Statistiques d’activité collectées

  • Accès logiques (INSERT, SELECT…) par table et index
  • Accès physiques (blocs) par table, index et séquence
  • Activité du Background Writer
  • Activité par base
  • Liste des sessions et informations sur leur activité

Vues système

  • Supervision / métrologie

  • Diagnostiquer

  • Vues système :

    • pg_stat_user_*
    • pg_statio_user_*
    • pg_stat_activity : requêtes
    • pg_stat_bgwriter
    • pg_locks

PostgresSQL propose de nombreuses vues, accessibles en SQL, pour obtenir des informations sur son fonctionnement interne. On peut donc avoir des informations sur le fonctionnement des bases, des processus d’arrière plan, des tables, les requêtes en cours…

Pour les statistiques aux objets, le système fournit à chaque fois trois vues différentes :

  • Une pour tous les objets du type. Elle contient all dans le nom, pg_statio_all_tables par exemple.
  • Une pour uniquement les objets systèmes. Elle contient sys dans le nom, pg_statio_sys_tables par exemple.
  • Une pour uniquement les objets non-systèmes. Elle contient user dans le nom, pg_statio_user_tables par exemple.

Les accès logiques aux objets (tables, index et routines) figurent dans les vues pg_stat_xxx_tables, pg_stat_xxx_indexes et pg_stat_user_functions.

Les accès physiques aux objets sont visibles dans les vues pg_statio_xxx_tables, pg_statio_xxx_indexes, et pg_statio_xxx_sequences.

Des statistiques globales par base sont aussi disponibles, dans pg_stat_database : le nombre de transactions validées et annulées, le nombre de sessions en cours, et quelques statistiques sur les accès physiques et en cache, ainsi que sur les opérations logiques.

pg_stat_bgwriter stocke les statistiques d’écriture des buffers des Background Writer, Checkpointer et des sessions elles-mêmes.

pg_stat_activity est une des vues les plus utilisées et est souvent le point de départ d’une recherche : elle donne des informations sur les processus en cours sur l’instance, que ce soit des processus en tâche de fond ou des processus backends associés aux clients : numéro de processus, adresse et port, date de début d’ordre, de transaction, de session, requête en cours, état, ordre SQL et nom de l’application si elle l’a renseigné. (Noter qu’avant la version 10, cette vue n’affichait que les processus backend ; à partir de la version 10 apparaissent des workers, le checkpointer, le walwriter…).

=# select datname, pid, usename, application_name, backend_start, state, backend_type, query 
          from pg_stat_activity \gx
-[ RECORD 1 ]----+---------------------------------------------------------------------
datname          | ¤
pid              | 26378
usename          | ¤
application_name | 
backend_start    | 2019-10-24 18:25:28.236776+02
state            | ¤
backend_type     | autovacuum launcher
query            | 
-[ RECORD 2 ]----+---------------------------------------------------------------------
datname          | ¤
pid              | 26380
usename          | postgres
application_name | 
backend_start    | 2019-10-24 18:25:28.238157+02
state            | ¤
backend_type     | logical replication launcher
query            | 
-[ RECORD 3 ]----+---------------------------------------------------------------------
datname          | pgbench
pid              | 22324
usename          | test_performance
application_name | pgbench
backend_start    | 2019-10-28 10:26:51.167611+01
state            | active
backend_type     | client backend
query            | UPDATE pgbench_accounts SET abalance = abalance + -3810 WHERE aid = 91273;
-[ RECORD 4 ]----+---------------------------------------------------------------------
datname          | postgres
pid              | 22429
usename          | postgres
application_name | psql
backend_start    | 2019-10-28 10:27:09.599426+01
state            | active
backend_type     | client backend
query            | select datname, pid, usename, application_name, backend_start, state,
                   backend_type, query from pg_stat_activity 
-[ RECORD 5 ]----+---------------------------------------------------------------------
datname          | pgbench
pid              | 22325
usename          | test_performance
application_name | pgbench
backend_start    | 2019-10-28 10:26:51.172585+01
state            | active
backend_type     | client backend
query            | UPDATE pgbench_accounts SET abalance = abalance + 4360 WHERE aid = 407881;
-[ RECORD 6 ]----+---------------------------------------------------------------------
datname          | pgbench
pid              | 22326
usename          | test_performance
application_name | pgbench
backend_start    | 2019-10-28 10:26:51.178514+01
state            | active
backend_type     | client backend
query            | UPDATE pgbench_accounts SET abalance = abalance + 2865 WHERE aid = 8138;
-[ RECORD 7 ]----+---------------------------------------------------------------------
datname          | ¤
pid              | 26376
usename          | ¤
application_name | 
backend_start    | 2019-10-24 18:25:28.235574+02
state            | ¤
backend_type     | background writer
query            | 
-[ RECORD 8 ]----+---------------------------------------------------------------------
datname          | ¤
pid              | 26375
usename          | ¤
application_name | 
backend_start    | 2019-10-24 18:25:28.235064+02
state            | ¤
backend_type     | checkpointer
query            | 
-[ RECORD 9 ]----+---------------------------------------------------------------------
datname          | ¤
pid              | 26377
usename          | ¤
application_name | 
backend_start    | 2019-10-24 18:25:28.236239+02
state            | ¤
backend_type     | walwriter
query            | 

Cette vue fournie aussi des informations sur ce que chaque session attend. Pour les détails sur wait_event_type (type d’événement en attente) et wait_event (nom de l’événement en attente), voir le tableau des événements d’attente.

# SELECT datname, pid, wait_event_type, wait_event, query FROM pg_stat_activity 
  WHERE backend_type='client backend' AND wait_event IS NOT NULL \gx
-[ RECORD 1 ]---+----------------------------------------------------------------------------------------------------------
datname         | pgbench
pid             | 1590
state           | idle in transaction
wait_event_type | Client
wait_event      | ClientRead
query           | UPDATE pgbench_accounts SET abalance = abalance + 1438 WHERE aid = 747101;
-[ RECORD 2 ]---+----------------------------------------------------------------------------------------------------------
datname         | pgbench
pid             | 1591
state           | idle
wait_event_type | Client
wait_event      | ClientRead
query           | END;
-[ RECORD 3 ]---+----------------------------------------------------------------------------------------------------------
datname         | pgbench
pid             | 1593
state           | idle in transaction
wait_event_type | Client
wait_event      | ClientRead
query           | INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (3, 4, 870364, -703, CURRENT_TIMESTAMP);
-[ RECORD 4 ]---+----------------------------------------------------------------------------------------------------------
datname         | postgres
pid             | 1018
state           | idle in transaction
wait_event_type | Client
wait_event      | ClientRead
query           | delete from t1 ;
-[ RECORD 5 ]---+----------------------------------------------------------------------------------------------------------
datname         | postgres
pid             | 1457
state           | active
wait_event_type | Lock
wait_event      | transactionid
query           | delete  from t1 ;

Des vues plus spécialisées existent :

pg_stat_replication donne des informations sur les serveurs secondaires connectés. Les statistiques sur les conflits entre application de la réplication et requêtes en lecture seule sont disponibles dans pg_stat_database_conflicts.

pg_stat_ssl donne des informations sur les connexions SSL : version SSL, suite de chiffrement, nombre de bits pour l’algorithme de chiffrement, compression, Distinguished Name (DN) du certificat client.

pg_locks permet de voir les verrous posés sur les tables.

pg_stat_progress_vacuum, pg_stat_progress_create_index et pg_stat_progress_cluster donnent respectivment des informations sur la progression des vacuums, des créations d’index, et des commandes de VACUUM FULL et CLUSTER.

pg_stat_archiver donne des informations sur l’archivage des wals et notamment sur les erreurs d’archivage.


Statistiques sur les données

  • Statistiques sur les données : pg_stats
    • Collectées par échantillonnage (default_statistics_target)
    • Table par table (et pour certains index)
    • Colonne par colonne
    • Pour de meilleurs plans d’exécution
  • ALTER TABLE matable ALTER COLUMN macolonne SET statistics 300;
  • Statistiques multicolonnes sur demande
    • CREATE STATISTICS

Afin de calculer les plans d’exécution des requêtes au mieux, le moteur a besoin de statistiques sur les données qu’il va interroger. Il est très important pour lui de pouvoir estimer la sélectivité d’une clause WHERE, l’augmentation ou la diminution du nombre d’enregistrements entraînée par une jointure, tout cela afin de déterminer le coût approximatif d’une requête, et donc de choisir un bon plan d’exécution.

Les statistiques sont collectées dans la table pg_statistic. La vue pg_stats affiche le contenu de cette table système de façon plus accessible.

Les statistiques sont collectées sur :

  • chaque colonne de chaque table ;
  • les index fonctionnels.

Les statistiques sont calculées sur un échantillon égal à 300 fois le paramètre STATISTICS de la colonne (ou, s’il n’est pas précisé, du paramètre default_statistics_target, 100 par défaut).

La vue pg_stats affiche les statistiques collectées :

\d pg_stats

                     View "pg_catalog.pg_stats"
         Column         |   Type   | Collation | Nullable | Default
------------------------+----------+-----------+----------+---------
 schemaname             | name     |           |          |
 tablename              | name     |           |          |
 attname                | name     |           |          |
 inherited              | boolean  |           |          |
 null_frac              | real     |           |          |
 avg_width              | integer  |           |          |
 n_distinct             | real     |           |          |
 most_common_vals       | anyarray |           |          |
 most_common_freqs      | real[]   |           |          |
 histogram_bounds       | anyarray |           |          |
 correlation            | real     |           |          |
 most_common_elems      | anyarray |           |          |
 most_common_elem_freqs | real[]   |           |          |
 elem_count_histogram   | real[]   |           |          |
  • inherited : la statistique concerne-t-elle un objet utilisant l’héritage (table parente, dont héritent plusieurs tables).
  • null_frac : fraction d’enregistrements nuls.
  • avg_width : taille moyenne de cet attribut dans l’échantillon collecté.
  • n_distinct : si positif, nombre de valeurs distinctes, si négatif, fraction de valeurs distinctes pour cette colonne dans la table. On peut forcer le nombre de valeurs distinctes, si on constate que la collecte des statistiques n’y arrive pas : ALTER TABLE xxx ALTER COLUMN yyy SET (n_distinct = -0.5) ; ANALYZE xxx; par exemple indique à l’optimiseur que chaque valeur apparaît statistiquement deux fois.
  • most_common_vals et most_common_freqs : les valeurs les plus fréquentes de la table, et leur fréquence. Le nombre de valeurs collecté est au maximum celle indiquée par le paramètre STATISTICS de la colonne, ou à défaut par default_statistics_target. Le défaut de 100 échantillons sur 30 000 lignes peut être modifié par ALTER TABLE matable ALTER COLUMN macolonne SET statistics 300 ; (avec une évolution proportionnelle du nombre de lignes consultées) sachant que le temps de planification augmente exponentiellement et qu’il vaut mieux ne pas dépasser la valeur 1000 ;
  • histogram_bounds : les limites d’histogramme sur la colonne. Les histogrammes permettent d’évaluer la sélectivité d’un filtre par rapport à sa valeur précise. Ils permettent par exemple à l’optimiseur de déterminer que 4,3 % des enregistrements d’une colonne noms commencent par un A, ou 0,2 % par AL. Le principe est de regrouper les enregistrements triés dans des groupes de tailles approximativement identiques, et de stocker les limites de ces groupes (on ignore les most_common_vals, pour lesquelles on a déjà une mesure plus précise). Le nombre d’histogram_bounds est calculé de la même façon que les most_common_vals.
  • correlation : le facteur de corrélation statistique entre l’ordre physique et l’ordre logique des enregistrements de la colonne. Il vaudra par exemple 1 si les enregistrements sont physiquement stockés dans l’ordre croissant, -1 si ils sont dans l’ordre décroissant, ou 0 si ils sont totalement aléatoirement répartis. Ceci sert à affiner le coût d’accès aux enregistrements.
  • most_common_elems et most_common_elems_freqs : les valeurs les plus fréquentes si la colonne est un tableau (NULL dans les autres cas), et leur fréquence. Le nombre de valeurs collecté est au maximum celle indiquée par le paramètre STATISTICS de la colonne, ou à défaut par default_statistics_target.
  • elem_count_histogram : les limites d’histogramme sur la colonne si elle est de type tableau.

Les statistiques par défaut sont calculées colonne par colonne. Parfois, il est intéressant de calculer des statistiques sur un ensemble de colonnes. Dans ce cas, il faut créer un objet statistique en indiquant les colonnes à traiter et le type de statistiques à calculer (voir la documentation de CREATE STATISTICS).


Optimiseur

  • SQL est un langage déclaratif :

    • Décrit le résultat attendu (projection, sélection, jointure, etc.)…
    • … mais pas comment l’obtenir
    • C’est le rôle de l’optimiseur

Le langage SQL décrit le résultat souhaité. Par exemple :

SELECT path, filename
FROM file
JOIN path ON (file.pathid=path.pathid)
WHERE path LIKE '/usr/%'

Cet ordre décrit le résultat souhaité. On ne précise pas au moteur comment accéder aux tables path et file (par index ou parcours complet par exemple), ni comment effectuer la jointure (il existe plusieurs méthodes pour PostgreSQL). C’est à l’optimiseur de prendre la décision, en fonction des informations qu’il possède.

Les informations les plus importantes pour lui, dans le contexte de cette requête, seront :

  • quelle fraction de la table path est ramenée par le critère path LIKE '/usr/%’ ?
  • y a-t-il un index utilisable sur cette colonne ?
  • y a-t-il des index sur file.pathid, sur path.pathid ?
  • quelles sont les tailles des deux tables ?

On comprend bien que la stratégie la plus efficace ne sera pas la même suivant les informations retournées par toutes ces questions…

Il pourrait être intéressant de charger les deux tables séquentiellement, supprimer les enregistrements de path ne correspondant pas à la clause LIKE, trier les deux jeux d’enregistrements et fusionner les deux jeux de données triés (c’est appelé un merge join). Cependant, si les tables sont assez volumineuses, et que le LIKE est très discriminant (il ramène peu d’enregistrements de la table path), la stratégie d’accès sera totalement différente : on pourrait préférer récupérer les quelques enregistrements de path correspondant au LIKE par un index, puis pour chacun de ces enregistrements, aller chercher les informations correspondantes dans la table file (c’est appelé un nested loop).


Optimisation par les coûts

  • L’optimiseur évalue les coûts respectifs des différents plans
  • Il calcule tous les plans possibles tant que c’est possible
  • Le coût de planification exhaustif est exponentiel par rapport au nombre de jointures de la requête
  • Il peut falloir d’autres stratégies
  • Paramètres principaux :
    • seq_page_cost, random_page_cost, cpu_tuple_cost, cpu_index_tuple_cost, cpu_operator_cost
    • parallel_setup_cost, parallel_tuple_cost
    • effective_cache_size

Afin de choisir un bon plan, le moteur essaye des plans d’exécution. Il estime, pour chacun de ces plans, le coût associé. Afin d’évaluer correctement ces coûts, il utilise plusieurs informations :

  • Les statistiques sur les données, qui lui permettent d’estimer le nombre d’enregistrements ramenés par chaque étape du plan et le nombre d’opérations de lecture à effectuer pour chaque étape de ce plan
  • Des informations de paramétrage lui permettant d’associer un coût arbitraire à chacune des opérations à effectuer. Ces informations sont les suivantes :
    • seq_page_cost (1 par défaut) : coût de la lecture d’une page disque de façon séquentielle (au sein d’un parcours séquentiel de table par exemple) ;
    • random_page_cost (4 par défaut) : coût de la lecture d’une page disque de façon aléatoire (lors d’un accès à une page d’index par exemple) ;
    • cpu_tuple_cost (0,01 par défaut) : coût de traitement par le processeur d’un enregistrement de table ;
    • cpu_index_tuple_cost (0,005 par défaut) : coût de traitement par le processeur d’un enregistrement d’index ;
    • cpu_operator_cost (0,0025 par défaut) : coût de traitement par le processeur de l’exécution d’un opérateur.

Ce sont les coûts relatifs de ces différentes opérations qui sont importants : l’accès à une page de façon aléatoire est par défaut 4 fois plus coûteux que de façon séquentielle, du fait du déplacement des têtes de lecture sur un disque dur. Ceci prend déjà en considération un potentiel effet du cache. Sur une base fortement en cache, on peut donc être tenté d’abaisser le random_page_cost à 3, voire 2,5, ou des valeurs encore bien moindres dans le cas de bases totalement en mémoire.

Le cas des disques SSD est particulièrement intéressant. Ces derniers n’ont pas à proprement parler de tête de lecture. De ce fait, comme les paramètres seq_page_cost et random_page_cost sont principalement là pour différencier un accès direct et un accès après déplacement de la tête de lecture, la différence de configuration entre ces deux paramètres n’a pas lieu d’être si les index sont placés sur des disques SSD. Dans ce cas, une configuration très basse et pratiquement identique (voire identique) de ces deux paramètres est intéressante.

Quant à effective_io_concurrency, il a pour but d’indiquer le nombre d’opérations disques possibles en même temps (prefetch). Dans le cas d’un système disque utilisant un RAID matériel, il faut le configurer en fonction du nombre de disques u