Dynamic Catalog Views

raghavendra t wrote:

Hi All,

Could please guide me in knowing the Dynamic Catalog views which will
tell about the Buffers and Cache information using System Catalogs.

you mean, stuff like
http://www.postgresql.org/docs/current/static/catalog-pg-statistic.html ?

afaik, data about whats in the cache and buffers is not accessible via
any pg_catalog schema tables.

On 13 April 2010 05:31, raghavendra t <raagavendra.rao@gmail.com> wrote:

Hi All,

Could please guide me in knowing the Dynamic Catalog views which will tell
about the Buffers and Cache information using System Catalogs.

Look at pg_buffercache contrib.

http://www.postgresql.org/docs/8.4/interactive/pgbuffercache.html

--
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802

raghavendra t wrote:

Hi All,

Could please guide me in knowing the Dynamic Catalog views which will
tell about the Buffers and Cache information using System Catalogs.

you mean, stuff like
http://www.postgresql.org/docs/current/static/catalog-pg-statistic.html ?

afaik, data about whats in the cache and buffers is not accessible via
any pg_catalog schema tables.

It's not accessible by default, but there is contrib package called
'pg_buffercache':

http://www.postgresql.org/docs/8.4/interactive/pgbuffercache.html

I guess that might provide the information about buffers.

Tomas

Hi All,

Thank you for your support.

Yes, i tried with pg_buffercache, it is giving the information on the hit
ratio of shared_buffers. Can we get the dynamic information like in
oracle(v$) views. Same as in postgresql buffers(shared,temp,wal) &
cache(maintainence_work_me, effective_mem).

Thanks & Regards
Raghavendra

2010/4/13 <tv@fuzzy.cz>

raghavendra t wrote:

Can we get the dynamic information like in oracle(v$) views. Same as
in postgresql buffers(shared,temp,wal) & cache(maintainence_work_me,
effective_mem).

The information available in this area includes:

1) Look at buffer cache hit rates using pg_stat_user_tables,
pg_statio_user_tables, pg_stat_user_indexes, and pg_statio_user_indexes
2) Dig into pg_buffercache to find out what's sitting in RAM and how
well the usage count implementation is working for you
3) Monitor pg_stat_bgwriter to find out what checkpoints are doing
4) Turn on log_temp_files (8.3 or later) to log when work_mem is being
exceeded and sorts are going to disk.

That's really it; the rest of the sort of data that's available in the
v$ views isn't exposed in nearly as much detail in PostgreSQL yet.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us