1. 云图网首页
  2. 技术专区
  3. 数据库

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 字段解释

Using pg_buffercache monitor the situation of Shared_buffers
备注：主要字段 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 作为监控手段。

原创文章，作者：745907710，如若转载，请注明出处：https://blog.ytso.com/236381.html

赞 (0)
0
上一篇 2022年1月24日
下一篇 2022年1月24日

相关推荐

发表回复

登录后才能评论