Using pg_buffercache monitor the situation of Shared_buffers

PostgreSQL 不像Oracle 那样提供强大的共享内存监控view, 如果需要监控 PostgreSQL 的 shared_buffer 情况,需要单独安装 pg_buffercache, 以下是详细过程。

安装 pg_buffercache 扩展

进入源码目录

1
cd /opt/soft_bak/postgresql-9.0.1/contrib/pg_buffercache

查看脚本 pg_buffercache.sql 内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
[postgres@pg_buffercache]$ cat pg_buffercache.sql  
/* $PostgreSQL: pgsql/contrib/pg_buffercache/pg_buffercache.sql.in,v 1.8 2008/08/14 12:56:41 heikki Exp $ */
-- Adjust this setting to control where the objects get created.
SET search_path = public;
-- Register the function.
CREATE OR REPLACE FUNCTION pg_buffercache_pages()
RETURNS SETOF RECORD
AS '$libdir/pg_buffercache', 'pg_buffercache_pages'
LANGUAGE C;
-- Create a view for convenient access.
CREATE VIEW pg_buffercache AS
SELECT P.* FROM pg_buffercache_pages() AS P
(bufferid integer, relfilenode oid, reltablespace oid, reldatabase oid,
relforknumber int2, relblocknumber int8, isdirty bool, usagecount int2);

-- Don't want these to be available at public.
REVOKE ALL ON FUNCTION pg_buffercache_pages() FROM PUBLIC;
REVOKE ALL ON pg_buffercache FROM PUBLIC;

备注:根据脚本内容,只是创建了一个 view

安装 pg_buffercache

1
2
3
4
5
6
[postgres@pg_buffercache]$ psql -h 127.0.0.1 -d skytf -f pg_buffercache.sql  
SET
CREATE FUNCTION
CREATE VIEW
REVOKE
REVOKE

由于 pg_buffercache 是系统VIEW,建议以 postgres 创建用户创建。

查看 pg_buffercache 结构,如下:

1
2
3
4
5
6
7
8
9
10
11
12
skytf=> \d pg_buffercache  
View "public.pg_buffercache"
Column | Type | Modifiers
----------------+----------+-----------
bufferid | integer |
relfilenode | oid |
reltablespace | oid |
reldatabase | oid |
relforknumber | smallint |
relblocknumber | bigint |
isdirty | boolean |
usagecount | smallint |

pg_buffercache 字段解释


备注:主要字段 relfilenode,isdirty,usagecount, 如下:

  • relfilenode: 是指表的文件id,与 pg_class.relfilenode 关联;
  • isdirty: 标记 shared_buffers 里的块是否为脏,如果被修改的块还未被刷新到硬盘里,则标记为 t, 否则,标记为 f;
  • usagecount: 指 shared_buffers 里的块被使用的次数。

查看部分数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
skytf=# select * from pg_buffercache limit 10;  
bufferid | relfilenode | reltablespace | reldatabase | relforknumber | relblocknumber | isdirty | usagecount
----------+-------------+---------------+-------------+---------------+----------------+---------+------------
1 | 14208525 | 1663 | 14205898 | 0 | 78465 | f | 0
2 | 11867 | 1664 | 0 | 0 | 0 | f | 5
3 | 14208525 | 1663 | 14205898 | 0 | 78466 | f | 0
4 | 14208525 | 1663 | 14205898 | 0 | 78467 | f | 0
5 | 14208525 | 1663 | 14205898 | 0 | 78468 | f | 0
6 | 14208525 | 1663 | 14205898 | 0 | 78469 | f | 0
7 | 14208525 | 1663 | 14205898 | 0 | 78470 | f | 0
8 | 14208525 | 1663 | 14205898 | 0 | 78471 | f | 0
9 | 14208525 | 1663 | 14205898 | 0 | 78472 | f | 0
10 | 14208525 | 1663 | 14205898 | 0 | 78473 | f | 0
(10 rows)

pg_buffercache 验证

创建测试表,并插入数据

1
2
3
4
skytf=> create table test_24 (id integer, name varchar(32));  
CREATE TABLE
skytf=> insert into test_24 select generate_series (1,10000),'francs';
INSERT 0 10000

表分析

1
2
skytf=> analyze test_24;  
ANALYZE

查询统计信息

1
2
3
4
5
6
7
8
9
10
skytf=> select relpages,reltuples from pg_class where relname='test_24';  
relpages | reltuples
----------+-----------
55 | 10000
(1 row)
skytf=> select oid,relfilenode,relname from pg_class where relname='test_24';
oid | relfilenode | relname
----------+-------------+---------
14280826 | 14280826 | test_24
(1 row)

另开一 session ,以 postgres用户连接 skytf库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
skytf=# select count(*) from pg_buffercache where relfilenode=14280826;  
count
-------
57
(1 row)

skytf=# select * from pg_buffercache where relfilenode =14280826 order by relblocknumber;
bufferid | relfilenode | reltablespace | reldatabase | relforknumber | relblocknumber | isdirty | usagecount
----------+-------------+---------------+-------------+---------------+----------------+---------+------------
12021 | 14280826 | 14203070 | 14203071 | 1 | 0 | f | 5
11983 | 14280826 | 14203070 | 14203071 | 0 | 0 | t | 5
12026 | 14280826 | 14203070 | 14203071 | 0 | 1 | t | 5
11988 | 14280826 | 14203070 | 14203071 | 1 | 2 | f | 5
12027 | 14280826 | 14203070 | 14203071 | 0 | 2 | t | 5
12028 | 14280826 | 14203070 | 14203071 | 0 | 3 | t | 5
12057 | 14280826 | 14203070 | 14203071 | 0 | 4 | t | 5
12058 | 14280826 | 14203070 | 14203071 | 0 | 5 | t | 5
12071 | 14280826 | 14203070 | 14203071 | 0 | 6 | t | 5
12076 | 14280826 | 14203070 | 14203071 | 0 | 7 | t | 5
12105 | 14280826 | 14203070 | 14203071 | 0 | 8 | t | 5
12117 | 14280826 | 14203070 | 14203071 | 0 | 9 | t | 5
12120 | 14280826 | 14203070 | 14203071 | 0 | 10 | t | 5
12123 | 14280826 | 14203070 | 14203071 | 0 | 11 | t | 5
12133 | 14280826 | 14203070 | 14203071 | 0 | 12 | t | 5
12134 | 14280826 | 14203070 | 14203071 | 0 | 13 | t | 5
12136 | 14280826 | 14203070 | 14203071 | 0 | 14 | t | 5
12137 | 14280826 | 14203070 | 14203071 | 0 | 15 | t | 5
12138 | 14280826 | 14203070 | 14203071 | 0 | 16 | t | 5
12139 | 14280826 | 14203070 | 14203071 | 0 | 17 | t | 5
12141 | 14280826 | 14203070 | 14203071 | 0 | 18 | t | 5
12143 | 14280826 | 14203070 | 14203071 | 0 | 19 | t | 5
12144 | 14280826 | 14203070 | 14203071 | 0 | 20 | t | 5
12145 | 14280826 | 14203070 | 14203071 | 0 | 21 | t | 5
12147 | 14280826 | 14203070 | 14203071 | 0 | 22 | t | 5
12151 | 14280826 | 14203070 | 14203071 | 0 | 23 | t | 5
12152 | 14280826 | 14203070 | 14203071 | 0 | 24 | t | 5
12153 | 14280826 | 14203070 | 14203071 | 0 | 25 | t | 5
12155 | 14280826 | 14203070 | 14203071 | 0 | 26 | t | 5
12156 | 14280826 | 14203070 | 14203071 | 0 | 27 | t | 5
12160 | 14280826 | 14203070 | 14203071 | 0 | 28 | t | 5
12161 | 14280826 | 14203070 | 14203071 | 0 | 29 | t | 5
12162 | 14280826 | 14203070 | 14203071 | 0 | 30 | t | 5
12163 | 14280826 | 14203070 | 14203071 | 0 | 31 | t | 5
12164 | 14280826 | 14203070 | 14203071 | 0 | 32 | t | 5
12166 | 14280826 | 14203070 | 14203071 | 0 | 33 | t | 5
12167 | 14280826 | 14203070 | 14203071 | 0 | 34 | t | 5
12168 | 14280826 | 14203070 | 14203071 | 0 | 35 | t | 5
12169 | 14280826 | 14203070 | 14203071 | 0 | 36 | t | 5
12170 | 14280826 | 14203070 | 14203071 | 0 | 37 | t | 5
12172 | 14280826 | 14203070 | 14203071 | 0 | 38 | t | 5
12173 | 14280826 | 14203070 | 14203071 | 0 | 39 | t | 5
12174 | 14280826 | 14203070 | 14203071 | 0 | 40 | t | 5
12175 | 14280826 | 14203070 | 14203071 | 0 | 41 | t | 5
12176 | 14280826 | 14203070 | 14203071 | 0 | 42 | t | 5
12179 | 14280826 | 14203070 | 14203071 | 0 | 43 | t | 5
12181 | 14280826 | 14203070 | 14203071 | 0 | 44 | t | 5
12183 | 14280826 | 14203070 | 14203071 | 0 | 45 | t | 5
12184 | 14280826 | 14203070 | 14203071 | 0 | 46 | t | 5
12185 | 14280826 | 14203070 | 14203071 | 0 | 47 | t | 5
12186 | 14280826 | 14203070 | 14203071 | 0 | 48 | t | 5
12188 | 14280826 | 14203070 | 14203071 | 0 | 49 | t | 5
12189 | 14280826 | 14203070 | 14203071 | 0 | 50 | t | 5
12192 | 14280826 | 14203070 | 14203071 | 0 | 51 | t | 5
12193 | 14280826 | 14203070 | 14203071 | 0 | 52 | t | 5
12194 | 14280826 | 14203070 | 14203071 | 0 | 53 | t | 5
12195 | 14280826 | 14203070 | 14203071 | 0 | 54 | t | 5
(57 rows)

备注:pg_buffercache 每行记录的是一个 block 块信息。

pg_buffercahce 常用查询

Top relations in the cache

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SELECT  
c.relname,
count(*) AS buffers
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.relname
ORDER BY 2 DESC
LIMIT 10;
relname | buffers
-------------------+---------
test_1 | 5411
postgres_log | 665
test_18 | 437
postgres_log_pkey | 142
pg_toast_2619 | 91
test_19 | 59
test_20 | 57
test_15 | 55
pg_statistic | 20
pg_operator | 13
(10 rows)

查数据表缓存占用 shared_buffers 百分比

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
SELECT  
c.relname,
pg_size_pretty(count(*) * 8192) as buffered,
round(100.0 * count(*) /
(SELECT setting FROM pg_settings
WHERE name='shared_buffers')::integer,1)
AS buffers_percent,
round(100.0 * count(*) * 8192 /
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
----------------------------------+----------+-----------------+---------------------
test_1 | 42 MB | 4.1 | 100.1
postgres_log | 5320 kB | 0.5 | 100.3
test_18 | 3496 kB | 0.3 | 100.9
pg_toast_2619 | 728 kB | 0.1 | 104.6
postgres_log_pkey | 1136 kB | 0.1 | 100.0
pg_depend | 80 kB | 0.0 | 23.3
pg_namespace_oid_index | 16 kB | 0.0 | 100.0
pg_operator_oid_index | 32 kB | 0.0 | 100.0
pg_statistic_relid_att_inh_index | 40 kB | 0.0 | 100.0
pg_constraint_oid_index | 16 kB | 0.0 | 100.0
(10 rows)

总结

  1. Postgresql 没有提供系统VIEW来监控 shared_buffer 情况,需要单独安装 pg_buffercache 模块;
  2. 由于在查询 pg_buffercache 视图时会对系统存在一定影响,所以不建议频繁地查看 pg_buffercache 作为监控手段。

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%