pg_buffercache : l’utilisation du cache de PostgreSQL¶
Informations¶
Ce module contrib permet de comprendre l’utilisation du cache par PostgreSQL. Il fournit une fonction SRF renvoyant l’ensemble des pages disque en cache. Une vue permet une utilisation plus simple. La page d’explication du manuel comprend un certain nombre d’informations, nous ne reviendrons pas là-dessus. Nous voulons plutôt montrer certains comportements internes de PostgreSQL via l’utilisation de ce module.
Mise en cache d’une table¶
Commençons par démarrer le serveur
Maintenant, créons une base de données.
Connectons-nous à la base et créons une table.
guillaume@laptop:~$ psql cache
Bienvenue dans psql 8.2.5, l'interface interactive de PostgreSQL.
Tapez: \copyright pour les termes de distribution
\h pour l'aide-mémoire des commandes SQL
\? pour l'aide-mémoire des commandes psql
\g ou point-virgule en fin d'instruction pour exécuter la requête
\q pour quitter
cache=# CREATE TABLE t (i int4);
CREATE TABLE
Récupérons son relfilenode qui nous permettra de filtrer les pages en cache pour n’avoir que celles correspondant à notre table t.
cache=# SELECT relfilenode FROM pg_class WHERE relname='t';
relfilenode
-------------
17625
(1 ligne)
Regardons si nous avons la table en cache.
cache=# SELECT * FROM pg_buffercache WHERE relfilenode=17625;
bufferid | relfilenode | reltablespace | reldatabase | relblocknumber | isdirty
----------+-------------+---------------+-------------+----------------+---------
(0 lignes)
Non, pas du tout. Ça paraît logique car la table a été créée mais elle n’a pas encore été lue ou écrite. Ajoutons un enregistrement dans cette table.
cache=# INSERT INTO t (i) VALUES (1);
INSERT 0 1
cache=# SELECT * FROM pg_buffercache WHERE relfilenode=17625;
bufferid | relfilenode | reltablespace | reldatabase | relblocknumber | isdirty
----------+-------------+---------------+-------------+----------------+---------
130 | 17625 | 1663 | 17613 | 0 | t
(1 ligne)
Nous voyons donc que notre table (relfilenode=17625) a une page en cache, la page 0 (donc la première) et que ce dernier est sale (traduction directe de dirty signifiant que la page est modifiée en mémoire).
Ajoutons un nouvel enregistrement.
cache=# INSERT INTO t (i) VALUES (2);
INSERT 0 1
cache=# SELECT * FROM pg_buffercache WHERE relfilenode=17625;
bufferid | relfilenode | reltablespace | reldatabase | relblocknumber | isdirty
----------+-------------+---------------+-------------+----------------+---------
130 | 17625 | 1663 | 17613 | 0 | t
(1 ligne)
Toujours une seule page, toujours sale. Ajoutons un troisième enregistrement.
cache=# INSERT INTO t (i) VALUES (3);
INSERT 0 1
cache=# SELECT * FROM pg_buffercache WHERE relfilenode=17625;
bufferid | relfilenode | reltablespace | reldatabase | relblocknumber | isdirty
----------+-------------+---------------+-------------+----------------+---------
130 | 17625 | 1663 | 17613 | 0 | t
(1 ligne)
Pareil. Maintenant, faisons en sorte que la table comprenne 500 lignes (le but étant d’avoir plus d’une page, nous devons insérer plus de 8 Ko de données… 500 lignes devraient nous fournir ça).
cache=# INSERT INTO t (i) SELECT x FROM generate_series(4, 500) AS x;
INSERT 0 497
cache=# SELECT * FROM pg_buffercache WHERE relfilenode=17625;
bufferid | relfilenode | reltablespace | reldatabase | relblocknumber | isdirty
----------+-------------+---------------+-------------+----------------+---------
130 | 17625 | 1663 | 17613 | 0 | t
136 | 17625 | 1663 | 17613 | 1 | t
137 | 17625 | 1663 | 17613 | 2 | t
(3 lignes)
Et voilà, trois pages, toutes sales. Nous pouvons remarquer que les numéros de pages se suivent, mais que ce n’est pas le cas pour le bufferid. C’est logique, le cache est utilisé pour d’autres tables, dont les tables systèmes.
Action du CHECKPOINT¶
Les pages sales du cache PostgreSQL sont vidées sur le disque au prochain CHECKPOINT. Comme il n’a pas l’air de vouloir se déclencher par notre petite activité, lançons nous-même un CHECKPOINT.
cache=# CHECKPOINT;
CHECKPOINT
cache=# SELECT * FROM pg_buffercache WHERE relfilenode=17625;
bufferid | relfilenode | reltablespace | reldatabase | relblocknumber | isdirty
----------+-------------+---------------+-------------+----------------+---------
130 | 17625 | 1663 | 17613 | 0 | f
136 | 17625 | 1663 | 17613 | 1 | f
137 | 17625 | 1663 | 17613 | 2 | f
(3 lignes)
Nos trois pages sont maintenant propres, c’est-à-dire sans modification.
Modification d’une ligne¶
Modifions une ligne au hasard.
cache=# UPDATE t SET i=3000 WHERE i=300;
UPDATE 1
cache=# SELECT * FROM pg_buffercache WHERE relfilenode=17625;
bufferid | relfilenode | reltablespace | reldatabase | relblocknumber | isdirty
----------+-------------+---------------+-------------+----------------+---------
130 | 17625 | 1663 | 17613 | 0 | t
136 | 17625 | 1663 | 17613 | 1 | t
137 | 17625 | 1663 | 17613 | 2 | t
(3 lignes)
Bizarrement, les trois pages sont considérées comme sales. Je peux comprendre que deux pages le soient (une pour modifier la partie correspondant à la future ligne morte, une autre pour insérer la nouvelle ligne vivante). Mais trois, aucune idée.
shared_buffers, la taille du cache¶
Le cache PostgreSQL pour les pages disque fait donc 24 Mo (c’est la valeur calculée par initdb pour le shared_buffers lors de la création du cluster).
Cherchons le nombre de pages enregistrables dans le cache PostgreSQL.
cache=# SELECT max(bufferid) FROM pg_buffercache;
max
------
3072
(1 ligne)
cache=# SELECT 24*1024*1024/(8*1024);
?column?
----------
3072
(1 ligne)
Il y a donc 3072 pages enregistrables. Ce nombre multiplié par 8 Ko donne 24 Mo, la valeur du shared_buffers… la boucle est bouclée !
Utilisation du cache¶
Recherchons maintenant le nombre de pages utilisées par notre table.
3, logique. Ajoutons plein d’enregistrements dans cette table.
Recherchons le nombre de pages sales et propres en cache pour cette table.
cache=# SELECT isdirty, count(*) FROM pg_buffercache WHERE relfilenode=17625 GROUP BY 1;
isdirty | count
---------+-------
f | 2513
t | 537
(2 lignes)
18% du cache pour cette table est sale… donc des CHECKPOINT se sont déclenchés automatiquement pendant l’insertion.
cache=# CHECKPOINT;
CHECKPOINT
cache=# SELECT isdirty, count(*) FROM pg_buffercache WHERE relfilenode
=17625 GROUP BY 1;
isdirty | count
---------+-------
f | 3050
(1 ligne)
Et un nouveau CHECKPOINT nous permet de voir que toutes les pages sont maintenant propres. Nous voyons aussi que la quasi-totalité (99.5%) du cache PostgreSQL est occupée par la table t. Le contenu de la table est pour l’instant en cache mais aux prochaines opérations, les pages les moins utilisées seront remplacées par les pages d’autres tables.
Parcours séquentiel¶
Arrêtons le serveur et relançons-le (ceci pour vider le cache).
guillaume@laptop:~$ pg_ctl stop
en attente de l'arrêt du serveur....LOG: arrêt en cours des traces
effectué
serveur arrêté
guillaume@laptop:~$ pg_ctl start
serveur en cours de démarrage
guillaume@laptop:~$ psql cache
Bienvenue dans psql 8.2.5, l'interface interactive de PostgreSQL.
Tapez: \copyright pour les termes de distribution
\h pour l'aide-mémoire des commandes SQL
\? pour l'aide-mémoire des commandes psql
\g ou point-virgule en fin d'instruction pour exécuter la requête
\q pour quitter
cache=# SELECT count(*) FROM pg_buffercache WHERE relfilenode=17625;
count
-------
0
(1 ligne)
Le cache est bien vide (au moins pour la table t). Récupérons un élément de cette table, de préférence vers la fin de la table.
cache=# SELECT * FROM t WHERE i=900000;
i
--------
900000
(1 ligne)
cache=# SELECT count(*) FROM pg_buffercache WHERE relfilenode=17625;
count
-------
3006
(1 ligne)
Comme nous n’avons pas d’index sur cette table, le moteur a fait un parcours séquentiel. Du coup, PostgreSQL a dû charger chaque page dans le cache jusqu’à trouver la bonne valeur. Un parcours séquentiel prend donc beaucoup de temps car
- il lit beaucoup de données sur disque ;
- il vide le cache des valeurs qui s’y trouvaient.
Parcours d’index¶
Nous allons créer un index, mais commençons par vider le cache.
guillaume@laptop:~$ pg_ctl stop
en attente de l'arrêt du serveur....LOG: arrêt en cours des traces
effectué
serveur arrêté
guillaume@laptop:~$ pg_ctl start
serveur en cours de démarrage
guillaume@laptop:~$ psql cache
Bienvenue dans psql 8.2.5, l'interface interactive de PostgreSQL.
Tapez: \copyright pour les termes de distribution
\h pour l'aide-mémoire des commandes SQL
\? pour l'aide-mémoire des commandes psql
\g ou point-virgule en fin d'instruction pour exécuter la requête
\q pour quitter
cache=# CREATE INDEX idx ON t (i);
CREATE INDEX
cache=# SELECT count(*) FROM pg_buffercache WHERE relfilenode=17625;
count
-------
2974
(1 ligne)
La création de l’index a demandé la lecture de la table complète, d’où le fait que le cache est pratiquement rempli.
Vidons de nouveau le cache avant d’essayer d’accéder à une valeur de la table avec un parcours d’index.
guillaume@laptop:~$ pg_ctl stop
en attente de l'arrêt du serveur....LOG: arrêt en cours des traces
effectué
serveur arrêté
guillaume@laptop:~$ pg_ctl start
serveur en cours de démarrage
guillaume@laptop:~$ psql cache
Bienvenue dans psql 8.2.5, l'interface interactive de PostgreSQL.
Tapez: \copyright pour les termes de distribution
\h pour l'aide-mémoire des commandes SQL
\? pour l'aide-mémoire des commandes psql
\g ou point-virgule en fin d'instruction pour exécuter la requête
\q pour quitter
cache=# SELECT * FROM t WHERE i=900000;
i
--------
900000
(1 ligne)
cache=# SELECT count(*) FROM pg_buffercache WHERE relfilenode=17625;
count
-------
1
(1 ligne)
cache=# SELECT * FROM pg_buffercache WHERE relfilenode=17625;
bufferid | relfilenode | reltablespace | reldatabase | relblocknumber | isdirty
----------+-------------+---------------+-------------+----------------+---------
84 | 17625 | 1663 | 17613 | 3964 | f
(1 ligne)
Nous n’avons plus qu’une seule lecture, la page 3964… mais comment a-t-il trouvé que la page 3964 contenait la bonne valeur ? Simplement en parcourant l’index. Cherchons son relfilenode pour voir l’occupation de l’index dans le cache.
cache=# SELECT relfilenode FROM pg_class WHERE relname='idx';
relfilenode
-------------
17628
(1 ligne)
cache=# SELECT count(*) FROM pg_buffercache WHERE relfilenode=17628;
count
-------
4
(1 ligne)
cache=# SELECT * FROM pg_buffercache WHERE relfilenode=17628;
bufferid | relfilenode | reltablespace | reldatabase | relblocknumber | isdirty
----------+-------------+---------------+-------------+----------------+---------
80 | 17628 | 1663 | 17613 | 0 | f
81 | 17628 | 1663 | 17613 | 361 | f
82 | 17628 | 1663 | 17613 | 1785 | f
83 | 17628 | 1663 | 17613 | 1973 | f
(4 lignes)
PostgreSQL a lu quatre pages de l’index : la page de la racine, une page branche, une deuxième page branche et enfin la page feuille. Ceci est dû à la construction d’un index de type Btree. Par défaut, la récupération d’une valeur dans un index demandera de lire quatre pages. Donc, en tout, nous n’avons lu que 5 pages au lieu d’environ 3000 pour un parcours séquentiel. Autrement dit, dans la majorité des cas, le parcours d’index est 600 fois plus rapide que le parcours séquentiel (ceci n’est pas totalement vrai car nous avons occulté les pages lues dans les tables systèmes, forcément plus nombreuses dans le cas où il existe plus d’objets).