Skip site navigation (1) Skip section navigation (2)

Re: [GENERAL] pg_buffercache view

From: "Sorin N(dot) Ciolofan" <ciolofan(at)ics(dot)forth(dot)gr>
To: "'Bill Moran'" <wmoran(at)potentialtech(dot)com>
Cc: <pgsql-admin(at)postgresql(dot)org>, <pgsql-general(at)postgresql(dot)org>
Subject: Re: [GENERAL] pg_buffercache view
Date: 2007-04-24 13:24:02
Message-ID: 20070424132308.EEDED8E40FC@mailhost.ics.forth.gr (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-general
 

   Dear Mr. Bill Moran,

 

 Thank you for your answer. 

 

1) To be more clear I would like to construct a query using the reldatabase
column. In that query you quoted I can't identify the reldatabase column. I
want a query that will help me to list how many buffers are used by each
database 

 

Maybe something like:

 

SELECT d.datname, count(*) AS buffers

               FROM pg_database d, pg_buffercache b

               WHERE d.X = b.reldatabase

               GROUP BY b.reldatabase

               ORDER BY 2 DESC LIMIT 10;

 

I would like, if possible, to know which is the name of this X which
corresponds to reldatabase column

 

2) I don't know exactly which is the modality the buffers are used. Is it
possible that all buffers to be used at let's say 5% of their capacity? In
this case I see in pg_buffercache that all the shared memory is used (since
all the buffers are used) but in reality only 5% from it is actually used.

 

With best regards,

Sorin

 

-----Original Message-----
From: pgsql-admin-owner(at)postgresql(dot)org
[mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Bill Moran
Sent: Tuesday, April 24, 2007 4:03 PM
To: Sorin N. Ciolofan
Cc: pgsql-admin(at)postgresql(dot)org; pgsql-general(at)postgresql(dot)org
Subject: Re: [ADMIN] [GENERAL] pg_buffercache view

 

In response to "Sorin N. Ciolofan" <ciolofan(at)ics(dot)forth(dot)gr>:

> 

>  Dear all,

> 

> About the pg_buffercache view:

> I couldn't find the description for this view in the manual at

> http://www.postgresql.org/docs/8.2/interactive/catalogs.html

> However I found the readme file provided in the /contrib./pg_buffercache
of

> the source code for version 8.2.3

 

Since pg_buffercache is contributed software, it's not documented in the

official PostgreSQL docs.

 

> Here it's written the following description:

> 

>    Column         |  references          | Description

>  

>
----------------+----------------------+------------------------------------

>    bufferid       |                      | Id, 1..shared_buffers.

>    relfilenode    | pg_class.relfilenode | Refilenode of the relation.

>    reltablespace  | pg_tablespace.oid    | Tablespace oid of the relation.

>    reldatabase    | pg_database.oid      | Database for the relation.

>    relblocknumber |                      | Offset of the page in the

> relation.

>    isdirty        |                      | Is the page dirty?

> 

> I've 2 questions:

> 1)

> I was not able to find the field "oid" from pg_database view. Could you

> please tell me what is the actual name of the column for which reldatabase

> is reffering to?

 

At the end of the README is an example query that I think answers your

question:

SELECT c.relname, count(*) AS buffers

               FROM pg_class c, pg_buffercache b

               WHERE b.relfilenode = c.relfilenode

               GROUP BY c.relname

               ORDER BY 2 DESC LIMIT 10;

 

 

> 2)

> In readme file is also written:

> "Unused buffers are shown with all fields null except buffered". 

> A "used" buffer means that is used 100% or could it be filled only

> partially?

 

Yes.  The buffer is either "used" or "not used", but pg_buffercache doesn't

know what percentage of it is used.  >0% is used.  0% is not used.

 

> Is there any way to know at a certain moment with precision how much
shared

> memory expressed in Mb is used?

 

The precision is +/- 1 buffer.  I expect that trying to get more precision
out

of the system will result in considerable performance degradation as the

data is collected and/or tracked.

 

-- 

Bill Moran

http://www.potentialtech.com

 

---------------------------(end of broadcast)---------------------------

TIP 2: Don't 'kill -9' the postmaster

In response to

pgsql-admin by date

Next:From: Brad NicholsonDate: 2007-04-24 18:17:17
Subject: Re: Replication Multi Master Asyncronous
Previous:From: Bill MoranDate: 2007-04-24 13:02:54
Subject: Re: [GENERAL] pg_buffercache view

pgsql-general by date

Next:From: Richard HuxtonDate: 2007-04-24 13:41:55
Subject: Re: Problem Backing Up a DB
Previous:From: David FleglDate: 2007-04-24 13:06:42
Subject: Re: hi

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group