ITPub博客

首页 > Linux操作系统 > Linux操作系统 > pg_buffercache

pg_buffercache

原创 Linux操作系统 作者:jolly_liu 时间:2011-04-29 14:57:29 0 删除 编辑
Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONEpg_buffercache

pg_buffercache提供了实时查询shared buffer cache的手段。

在Postgres Plus Advanced Server中使用入下:

edb=# \i /opt/PostgresPlus/8.4AS/share/contrib/pg_buffercache.sql

edb=# select * from pg_buffercache;

 bufferid | relfilenode | reltablespace | reldatabase | relforknumber | relblocknumber | isdirty | usagecount

----------+-------------+---------------+-------------+---------------+----------------+---------+------------

        1 |      176178 |          1663 |       24586 |             0 |              9 | f       |          5

        2 |      176179 |          1663 |       24586 |             0 |              6 | f       |          5

        3 |      176180 |          1663 |       24586 |             0 |              6 | f       |          5

        4 |      176180 |          1663 |       24586 |             0 |              5 | f       |          5

        5 |      176180 |          1663 |       24586 |             0 |              2 | f       |          5

        6 |      176180 |          1663 |       24586 |             0 |              1 | f       |          5

        7 |      176180 |          1663 |       24586 |             0 |              4 | f       |          5

        8 |        1262 |          1664 |           0 |             0 |              0 | f       |          5

        9 |        1260 |          1664 |           0 |             0 |              0 | f       |          5

       10 |        1261 |          1664 |           0 |             0 |              0 | f       |          1

 

Name

Type

References

Description

bufferid

integer

 

ID, in the range 1..shared_buffers

relfilenode

oid

pg_class.relfilenode

Filenode number of the relation

reltablespace

oid

pg_tablespace.oid

Tablespace OID of the relation

reldatabase

oid

pg_database.oid

Database OID of the relation

relblocknumber

bigint

 

Page number within the relation

relforknumber

smallint

 

Fork number within the relation

isdirty

boolean

 

Is the page dirty?

usagecount

smallint

 

Page LRU count

 

1)查看本库中各个表所占用的buffer大小

edb=# select c.relname,count(*) as buffers                   

from pg_buffercache b,pg_class c                             

where b.relfilenode=c.relfilenode                            

and b.reldatabase =(select oid from pg_database where datname=current_database())                                          

group by c.relname                                           

order by 2 desc;

 

             relname             | buffers

---------------------------------+---------

 pg_attribute                    |      42

 pg_proc                         |      33

 pg_operator                     |      19

 pg_proc_proname_args_nsp_index  |      15

 pg_class                        |      14

 pg_attribute_relid_attnum_index |      11

 pg_proc_oid_index               |      10

 pg_depend_reference_index       |       9

 pg_type                         |       9

 pg_statistic                    |       7

 

2)查看脏块的比例

edb=# select count(*) from pg_buffercache a  where a.isdirty='t';

edb=# select count(*) from pg_buffercache a ;

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21778816/viewspace-694112/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 逻辑结构
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2010-04-08

  • 博文量
    5
  • 访问量
    9170