Introduction

Aujourd’hui, je vous propose un article complet sur le fonctionnement des deux principaux buffers de PostgreSQL, j’ai nommé :

• shared_buffer
• effective_cache_size (dont le fonctionnement est très lié avec le premier)

Pour rappel, le processus postmaster affecte 1 backend_process par transaction sans dépasser le paramètre [max_connections]. Tous ces backend_process partagent un même buffer pour lire ou écrire ses données, cet espace de travail partagé s’appelle le shared_buffer !

Ce buffer est un segment partagé entre différents processus (backend_process donc, mais pas uniquement) qui est stocké en RAM et dédié au stockage des pages de données qu’elles soit lues (clean page) ou modifiées (dirty page).

Qu’est ce qui est mis en cache ? 
PostgreSQL cache les objets suivants :

• Tables et leurs contenus (8Kb)
• Index (stockés également dans des pages de 8Kb)
• Plans d’exécution (qui seront détruit à la fermeture de la session)

Pourquoi imposer aux backend_process de lire ses pages en RAM et pas directement sur le disque ?
Tout simplement car les I/O disques sont un sujet majeur des performances de votre moteur de base de données et peuvent avoir un impact direct sur les mauvaises performances de ce dernier.

Pour mieux appréhender cet aspect, voici un tableau qui compare les performances (extrait de cet excellent article) et leur équivalence en prenant arbitrairement 1 seconde comme point de référence :

Type	Temps réel	Équivalence
1 cycle CPU	0.3 ns	1 s
cache CPU niveau 1	0,9 ns	3 s
cache CPU niveau 3	12,9 ns	43 s
mémoire vive	120 s	6 mins
Disque SSD	50-150 µs	2 à 6 jours
Disque mécanique	1 à 10 ms	1 à 12 mois

Conclusion :
Avec ce tableau, on comprend que même avec du stockage type SSD, on reste extrêmement loin des performances de la RAM et encore plus loin si on utilise un stockage « classique ». D’où l’importance de ce shared_buffer qui va permettre d’offrir d’excellentes performances aux transactions (via les backend_process) et décaler dans le temps les écritures sur disque.

Fonctionnement du shared_buffer

Napoléon disait : « Un croquis vaut mieux qu’un long discours », j’ai donc préparé ce schéma qui vous présente le pire des scénarios, à savoir un backend_process qui est contraint de lire ses pages de données sur le disque. Ce schéma simplifie volontairement la réalité en faisant abstraction de certains détails.

• étape 1 : le backend_process va chercher la page de données qu’il souhaite lire (clean page) dans le shared_buffer et uniquement dans ce dernier.
• étape 2 : Les pages sont indexées par buffer_id, dans le cas où la donnée n’est pas présente, le buffer va solliciter le disque grâce au process bg_writer
• étape 3 : avant d’aller lire le disque, un deuxième buffer entre en jeu : l’effective_cache_size. Ce dernier est un cache des données du disque et est géré uniquement par le système. PostgreSQL n’a aucune visibilité sur les données de ce cache, il connait uniquement sa taille et ne pourra se baser que sur une probabilité statistique d’y trouver les données requises (lors du plan d’exécution). De part sa nature « proche » du système, PostgreSQL s’appuie beaucoup sur ce buffer, c’est le plus important et celui qui aura le plus d’impact sur vos performances.
• étape 4 : si l’effective_cache_size n’a pas non plus la page demandée, on arrive dans le pire des scénarios, à savoir qu’on va devoir aller lire les données sur disque, gros impact (négatif) sur les performances de la transaction concernée.

ps 1 : fort heureusement, dans la réalité, les données requises par le backend_process sont déjà présentes dans le shared_buffer. On s’arrête dans > 90% des cas à l’étape 2/3. On en reparle en détails via le calcul du hit ratio.

ps 2 : en cas d’écriture, le fonctionnement est légèrement différent. En effet, PostgreSQL (via le bg_writer) s’appuie sur un appel système de la fonction fsync() qui permet au moteur d’être certain que les données sont écrites sur disque et ne stagnent pas dans le cache (effective_cache_size donc). Les performances sont pénalisées, mais on évite une inconsistance de données et on respecte les propriétés A.C.I.D

Comment se vide le cache ? 
Il y a deux possibilités pour retirer (= flusher) des pages du shared_buffer :

• Une page est de type « dirty » (elle a donc été modifiée par un backend_process), son écriture se fera par l’un ou l’autre de ces processus :
  • le process checkpointer (il y a plusieurs règles que je ne détaillerai pas ici)
  • le process bgwriter (également plusieurs règles que je ne détaillerai pas ici)
• Une page est de type « clean » (elle n’a pas été modifiée). Il existe un mécanisme Least Recent Used (qui prend en compte le nombre de fois où une page a été lue par une requête SQL) et si son score tombe à 0, elle est éjectée du shared_buffer.

Inconvénients de ce mécanisme ? 
Premier inconvénient, ce fonctionnement a le défaut de ses qualités. Les pages de données qui ont été modifiées sont stockées temporairement dans le shared_buffer (dirty page), mais s’il y a une coupure du système (logiciel, électricité, etc …), tous le contenu du buffer est perdu (pour rappel, mémoire RAM = mémoire volatile). C’est là qu’intervient un autre mécanisme de PostgreSQL : la journalisation (appelé Write-Ahead-Logging), mais ça fera l’objet d’un autre article 😉

Deuxième inconvénient, de part la structure volatile des données dans le cache, l’algorithme LRU se construit sur des données très récentes et n’a pas toujours le recul suffisant pour prendre des bonnes décisions.

Configuration et optimisation

Configuration

Nous allons nous intéresser à la configuration de ces deux buffers. La configuration concerne toute l’instance, attention donc si vous avez plusieurs bases à l’intérieur. Les paramètres sont dans le fichier postgresql.conf (ici les valeurs par défaut)

shared_buffer = 32MB
effective_cache_size = 4GB

Voici les règles pour dimensionner correctement ces deux buffers :

• shared_buffer : la règle générale est d’affecter 1/4 de votre mémoire physique disponible (hors swap donc) en ne dépassant jamais les 8GO (1GO sous Windows). Au delà, vous vous exposez au risque de créer des doublons avec l’effective_cache_size, ce qui est contre-performant. Il est donc préférable de prendre le risque de le sous-dimensionner que l’inverse.
• effective_cache_size : ce paramètre permet de donner à PostgreSQL une estimation de la taille de ce buffer. Dans le cas où l’on est sur un serveur dédié à la base de données, on peut le monter assez haut car l’OS ne va pas consommer beaucoup. On peut identifier deux situations :
  • 3/4 de la mémoire physique disponible si on veut être « agressif », exemples de situation :
    • le serveur est dédié à PostgreSQL
    • base de données volumineuse
    • requêtes applicatives qui retournent beaucoup de données
  • 2/3 est le paramètre généralement recommandé pour une instance « classique »
  • 1/2 de la mémoire physique disponible si on a besoin d' »économiser », exemples de situation :
    • les mêmes données servent 80/90% des requêtes donc un cache réduit devrait suffire
    • on a beaucoup de connexions sur notre base et on veut privilégier d’autres buffers : (work_mem principalement)

Optimisation

PostgreSQL regorge de vues statistiques (pg_stat_*) vous permettant de comprendre le fonctionnement du moteur, je vous propose ici quelques requêtes/outils pour analyser le comportement de votre shared_buffer.

Visualiser le contenu de shared_buffer

Dans un souci d’amélioration des performances et de l’utilisation de votre cache, je vous conseille l’activation du complément : pg_buffercache

C’est un addon porté par la communauté et supporté officiellement depuis la 9.4. Vous retrouverez tout le contenu du shared_buffer en temps réel dans une nouvelle vue pg_buffercache, la recherche est indexée à partir de l’OID des tables et des bases de données (je vous conseille une jointure avec pg_class pour connaître un libellé)

Plus de détails ici.

Calculer le hit cache

Physiquement, on peut se représenter le shared_buffer par un tableau avec des cases de 8Kb. Chaque « case » (= une page) contient elle-même des metadatas (qui renseignent sur le contenu de la page). Ainsi quand le buffer lit le « tableau », il va parcourir uniquement les metadatas, quand il trouve la bonne page, cela déclenche un « hit ».

Avec la requête suivante, nous allons calculer ce « hit cache », à minima, vous devez dépasser les 90% et idéalement être autour des 99%.

SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit) as heap_hit, sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio FROM pg_statio_user_tables;

Quelques précisions sur cette requête :

• heap_blks_read : c’est le nombre de fois où un bloc a été chargé en pour être lu.
• heap_blks_hit : c’est le nombre de « hits » qu’on a réalisé, le bloc était déjà en mémoire.

Vérifier le ratio d’écriture des backend

Sur PostgreSQL, une écriture des dirty pages doit venir soit :

• du process checkpoint (il y a deux règles que je ne détaillerai pas ici)
• du process bgwriter (également plusieurs règles que je ne détaillerai pas ici)

En dernier recours, c’est le backend_process qui va déclencher lui-même le flush du buffer pour faire de la place par rapport à ses propres besoins. La requête suivante va nous permettre de calculer le pourcentage de fois où cela se produit, il est impératif que ce pourcentage soit le plus bas possible car on dégrade fortement les performances.

select (100 * buffers_backend) / (buffers_backend + buffers_clean + buffers_checkpoint) AS checkpoints_req_pct from pg_stat_bgwriter;

Si vous identifiez un souci avec l’un ou l’autre de ces indicateurs, alors plusieurs pistes :

• vérifier votre configuration (cf chapitre précédent)
• vérifier vos index. Si des index sont manquants, le plan d’exécution va déclencher un seq scan qui va devoir chercher toutes les pages concernées et potentiellement remplir le cache avec un volume énorme de pages pour satisfaire une requête.
• augmenter la mémoire physique disponible et redimensionner vos buffers en conséquence
• analyse applicative via les plans d’exécution pour identifier des améliorations lors de la lecture, exemples (shared read étant une lecture sur disque / shared hit étant dans le cache) :

cas 1, les pages sont absentes des buffers :

easyteam_test=# explain (analyze,buffers) select * from utilisateurs order by userid limit 10;  
  Limit  (cost=0.42..1.93 rows=10 width=219) (actual time=28.099..81.529 rows=20 loops=1)
    Buffers: shared read=13
    ->  Index Scan using userid_idx on utilisateurs (cost=0.42..150979.46 rows=1000000 width=200)
  Planning time: 0.124 ms
  Execution time: 95.465 ms
 (6 rows)

cas 2, les pages sont déjà présentes dans les buffers :

easyteam_test=# explain (analyze,buffers) select * from utilisateurs order by userid limit 10; 
  Limit  (cost=0.42..1.93 rows=10 width=219) (actual time=0.050..0.052 rows=20 loops=1)
    Buffers: shared hit=13
    ->  Index Scan using userid_idx on utilisateurs (cost=0.42..150979.46 rows=1000000 width=200)
  Planning time: 0.103 ms
  Execution time: 0.055 ms
 (6 rows)

Vous constatez également l’écart énorme de temps d’exécution entre les deux scénarions. 95ms / 0,05ms

Attention : les statistiques dans les vues pg_stat sont stockées depuis le dernier reset :

select stats_reset from pg_stat_database;

Si besoin, vous pouvez les réinitialiser, il est conseillé de le faire après un changement de configuration par exemple.

pg_stat_reset();
pg_stat_reset_shared('bgwriter');

Sources :

• Dalibo
• PostgreSQL WIKI
• Coding Horror