注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

Using pg_buffercache monitor the situation of Shared_buffers  

2011-03-18 18:08:17|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

 

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


1 安装 pg_buffercache 模块
--进入源码目录
cd /opt/soft_bak/postgresql-9.0.1/contrib/pg_buffercache

--查看脚本 pg_buffercache.sql 内容
[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
[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 结构
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 to monitor the situation of Shared_buffers - francs - My DBA LIFE

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


--查部分数据
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)

 

2 测试场景
--创建测试表,并插入数据
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

--表分析
skytf=> analyze test_24;
ANALYZE

--查询统计信息
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库
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 块信息。


3 常用查询
--Top relations in the cache
 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 百分比
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 作为监控手段。

  评论这张
 
阅读(32515)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016