Qu’est-ce que pg_buffercache ?

L’extension pg_buffercache de PostgreSQL permet d’examiner en temps réel le contenu du cache partagé, qui est une zone mémoire où PostgreSQL stocke les blocs de données fréquemment consultés pour améliorer les performances des requêtes.

Elle offre une vue détaillée des tampons utilisés dans le cache et fournit des informations utiles pour analyser l’état du cache et optimiser les performances.

Fonctionnement 

Installation :

L’extension doit être installée avec la commande :

CREATE EXTENSION pg_buffercache;

Vue principale :

La vue pg_buffercache expose des informations sur chaque tampon du cache partagé. Chaque ligne correspond à un bloc de 8 Ko.

Colonnes principales :

• bufferid : Identifiant du tampon.

• relfilenode : Identifiant du fichier sur disque.

• reltablespace : OID du tablespace.

• reldatabase : OID de la base de données.

• relblocknumber : Numéro du bloc dans la relation.

• isdirty : Indique si le bloc a été modifié en mémoire.

• usagecount : Compteur d’accès (lié à l’algorithme clock-sweep).

• pinning_backends : Nombre de processus accédant au bloc.

Cas d’utilisation 

Analyse des performances :

• Identifier les relations (tables/index) les plus utilisées dans le cache.

• Déterminer si certains blocs sont fréquemment modifiés (isdirty) ou accédés (usagecount) pour ajuster les paramètres comme shared_buffers.

Diagnostic des ralentissements :

Vérifier si une table ou un index est évincé du cache par une autre charge de travail, ce qui peut causer des ralentissements

Optimisation de la mémoire :

Ajuster la taille du cache partagé (shared_buffers) en fonction de l’utilisation réelle observée via pg_buffercache.

Débogage avancé :

Comprendre comment PostgreSQL gère les tampons pour des tests ou des analyses approfondies.

Exemple d’utilisation 

Calcule le pourcentage de tampons utilisés dans le cache partagé :

WITH state AS (
SELECT
COUNT(*) FILTER (WHERE relfilenode IS NOT NULL) AS used,
COUNT(*) FILTER (WHERE relfilenode IS NULL) AS empty,
COUNT(*) AS total
FROM pg_buffercache
)
SELECT *, ROUND(used * 100.0 / total, 1) AS percent_used
FROM state;

• Si le pourcentage est faible, vous pouvez réduire la taille de shared_buffers.

• Si le cache est plein et que les performances sont limitées, augmentez la taille.

Identifier les relations les plus présentes dans le cache :

SELECT n.nspname AS schema, c.relname AS relation, COUNT(*) AS buffers
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database()))
GROUP BY schema, relation
ORDER BY buffers DESC
LIMIT 10;

• Cela permet de voir quelles relations sont prioritaires dans le cache.

Analyser les blocs « sales » (isdirty) :

SELECT COUNT(*) AS dirty_blocks
FROM pg_buffercache
WHERE isdirty;

• Un nombre élevé peut indiquer un besoin d’optimisation des paramètres comme checkpoint_segments.

Compter le nombre total de tampons utilisés :

SELECT COUNT(*) FROM pg_buffercache;

Optimisation de shared_buffers

Pour ajuster la valeur de shared_buffers, suivez ces étapes :

Étape 1 : Collecter des données avec pg_buffercache

• Exécutez la requête sur l’utilisation totale du cache.

• Analysez les relations les plus utilisées pour comprendre la taille du dataset actif.

Étape 2 : Ajuster la taille en fonction des résultats

• Si le cache est sous-utilisé : Réduisez la valeur de shared_buffers pour libérer de la mémoire pour d’autres processus.

• Si le cache est plein :

  • Augmentez progressivement la valeur jusqu’à observer des rendements décroissants.

  • Utilisez des outils comme usagecount pour prioriser les pages fréquemment accédées.

Étape 3 : Considérations matérielles

• Allouez entre 25% et 40% de la RAM totale à shared_buffers

• Si votre serveur manque de RAM, envisagez une augmentation matérielle ou une optimisation des autres paramètres comme work_mem.

Ces analyses vous permettent d’équilibrer l’utilisation mémoire entre PostgreSQL et votre système d’exploitation tout en maximisant les performances.