The pg_buffercache module provides a means for examining what's happening in the shared buffer cache in real time.
1. create extension
lhl@localhost:~$ psql mydb
psql (9.5rc1)
Type "help" for help.
mydb=#
mydb=#
mydb=# create database lhl;
CREATE DATABASE
mydb=# \c lhl
You are now connected to database "lhl" as user "lhl".
lhl=#
lhl=#
lhl=# create extension pg_buffercache ;
CREATE EXTENSION
lhl=# \dx
List of installed extensions
Name | Version | Schema | Description
----------------+---------+------------+---------------------------------
pg_buffercache | 1.1 | public | examine the shared buffer cache
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
lhl=# \d+ public.pg_buffercache
View "public.pg_buffercache"
Column | Type | Modifiers | Storage | Description
------------------+----------+-----------+---------+-------------
bufferid | integer | | plain |
relfilenode | oid | | plain |
reltablespace | oid | | plain |
reldatabase | oid | | plain |
relforknumber | smallint | | plain |
relblocknumber | bigint | | plain |
isdirty | boolean | | plain |
usagecount | smallint | | plain |
pinning_backends | integer | | plain |
View definition:
SELECT p.bufferid,
p.relfilenode,
p.reltablespace,
p.reldatabase,
p.relforknumber,
p.relblocknumber,
p.isdirty,
p.usagecount,
p.pinning_backends
FROM pg_buffercache_pages() p(bufferid integer, relfilenode oid, reltablespace oid, reldatabase oid, relforknumber smallint, relblocknumber bigint, isdirty boolean, usagecount smallint, pinning_backends integer);
2. parameters
lhl=# select setting from pg_settings where name='block_size';
setting
---------
32768
(1 row)
lhl=# select setting from pg_settings where name='shared_buffers';
setting
---------
16384
(1 row)
lhl=# show block_size;
block_size
------------
32768
(1 row)
lhl=# show shared_buffers;
shared_buffers
----------------
512MB
(1 row)
lhl=# select (select setting from pg_settings where name='shared_buffers')::integer * (select setting from pg_settings where name='block_size')::integer /1024/1024 ;
?column?
----------
512
(1 row)
lhl=# select count(1) from pg_buffercache;
count
-------
16384
(1 row)
3. shared buffer cache in real time
注意网上有些资料将(select setting from pg_settings where name='block_size')直接写为 8192，
在block_size 是默认的8k情况下，没问题的，
但一旦编译PostgreSQL时更改了block_size就会得到不正确的结果，所以读取pg_settings的数值是最为靠谱，更通用的方式
lhl=# SELECT
c.relname,
pg_size_pretty(count(*) * (select setting from pg_settings where name='block_size')::integer ) as buffered,
round(100.0 * count(*) /
(SELECT setting FROM pg_settings
WHERE name='shared_buffers')::integer,1)
AS buffers_percent,
round(100.0 * count(*) * (select setting from pg_settings where name='block_size')::integer /
pg_relation_size(c.oid),1)
AS percent_of_relation
FROM pg_class c
INNER JOIN pg_buffercache b
ON b.relfilenode = c.relfilenode
INNER JOIN pg_database d
ON (b.reldatabase = d.oid AND d.datname = current_database())
GROUP BY c.oid,c.relname
ORDER BY 3 DESC
LIMIT 10;
relname | buffered | buffers_percent | percent_of_relation
----------------------------------+----------+-----------------+---------------------
pg_depend_reference_index | 288 kB | 0.1 | 64.3
pg_opclass_oid_index | 64 kB | 0.0 | 100.0
pg_index_indrelid_index | 64 kB | 0.0 | 100.0
pg_transform_type_lang_index | 32 kB | 0.0 | 100.0
pg_namespace_oid_index | 64 kB | 0.0 | 100.0
pg_depend | 256 kB | 0.0 | 57.1
pg_statistic_relid_att_inh_index | 64 kB | 0.0 | 100.0
pg_cast_source_target_index | 64 kB | 0.0 | 100.0
pg_depend_depender_index | 160 kB | 0.0 | 38.5
pg_operator_oid_index | 64 kB | 0.0 | 100.0
(10 rows)
其中， buffered总和 <= shared_buffers
参考链接
1. http://www.postgresql.org/docs/9.5/static/pgbuffercache.html
