使用pg_buffercache查看缓存区缓存
PG提供了一个扩展pg_buffercache来查看缓存区的内容。
create database test; CREATE DATABASE create extension pg_buffercache ; CREATE EXTENSION
创建个测试数据库test,并且添加扩展。
psql -d test test=# select distinct reldatabase from pg_buffercache ; reldatabase ------------- 16394 13322 0 (4 rows)
在缓存区中找到两个数据库的内容,带0的记录表示缓存区未使用。
test=# \! oid2name All databases: Oid Database Name Tablespace ---------------------------------- 13322 postgres pg_default 13321 template0 pg_default 1 template1 pg_default 16394 test pg_default
通过SQL更直观的来看一下:
select c.relname, count(*) as buffers from pg_class c 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.relname order by 2 desc; relname | buffers -----------------------------------+--------- pg_operator | 14 pg_depend_reference_index | 12 pg_depend | 10 pg_rewrite | 6 pg_description | 6 pg_amop | 5
这些内容,都是数据字典视图。
test=# create table lsang(id serial,name varchar(20));
CREATE TABLE
test=# insert into lsang(name) values('Michael');
INSERT 0 1
test=# select * from lsang;
id | name
----+---------
1 | Michael
(1 row)
select
c.relname,
count(*) as buffers
from pg_class c
join pg_buffercache b
on b.relfilenode = c.relfilenode
inner join pg_database d
on (b.reldatabase = d.oid and d.datname = current_database())
where c.relname not like 'pg%'
group by c.relname
order by 2 desc;
relname | buffers
--------------+---------
lsang | 1
lsang_id_seq | 1
(2 rows)
创建表并插入数据,我们通过pg_buffercache能够查询到buffers。
我们来看看数据缓存区是否为脏的。
select c.relname, b.isdirty from pg_class c join pg_buffercache b on b.relfilenode = c.relfilenode inner join pg_database d on (b.reldatabase = d.oid and d.datname = current_database()) where c.relname not like 'pg%' ; relname | isdirty --------------+--------- lsang_id_seq | f lsang | f
注意,isdirty标记的为f:
test=# update lsang set name = 'Michael.Sang'; UPDATE 1
再次查询结果: relname | isdirty --------------+--------- lsang_id_seq | f lsang | t (2 rows)
结果告诉我们,缓存区是脏的,我们可以强制设置个检查点:
test=# checkpoint ; CHECKPOINT
重复上面查询:
relname | isdirty --------------+--------- lsang_id_seq | f lsang | f (2 rows)
这样缓存区就不再是脏的了。