Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id B32029FBBF6; Tue, 24 Apr 2007 10:23:22 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 36307-08-3; Tue, 24 Apr 2007 10:23:17 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mailhost.ics.forth.gr (mailhost.ics.forth.gr [139.91.157.55]) by postgresql.org (Postfix) with ESMTP id 7259F9FBC2D; Tue, 24 Apr 2007 10:23:16 -0300 (ADT) Received: from mailhost.ics.forth.gr (mailhost.ics.forth.gr [139.91.157.55] (may be forged)) by mailhost.ics.forth.gr (8.13.8/ICS-FORTH/V10-Server-2.0.0) with ESMTP id l3ODNAxY025824; Tue, 24 Apr 2007 16:23:14 +0300 Received: from sappho.ics.forth.gr (unknown [127.0.0.1]) by mailhost.ics.forth.gr (Symantec Mail Security) with ESMTP id DA67416C061; Tue, 24 Apr 2007 16:23:09 +0300 (EEST) X-AuditID: 7f000001-aefcabb00000050a-c4-462e04bd75fb Received: from SHADOWFAX (unknown [139.91.183.33]) by mailhost.ics.forth.gr (Symantec Mail Security) with ESMTP id EEDED8E40FC; Tue, 24 Apr 2007 16:23:08 +0300 (EEST) From: "Sorin N. Ciolofan" To: "'Bill Moran'" Cc: , Subject: Re: [GENERAL] pg_buffercache view Date: Tue, 24 Apr 2007 16:24:02 +0300 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0066_01C7868C.F8C34A40" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 In-Reply-To: <20070424090254.037f7996.wmoran@potentialtech.com> Thread-Index: AceGcURhO4oUbay7Sxui6MWmD55UrwAATWrA X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 Message-Id: <20070424132308.EEDED8E40FC@mailhost.ics.forth.gr> X-Brightmail-Tracker: AAAAAA== X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/218 X-Sequence-Number: 25282 This is a multi-part message in MIME format. ------=_NextPart_000_0066_01C7868C.F8C34A40 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Bill Moran Sent: Tuesday, April 24, 2007 4:03 PM To: Sorin N. Ciolofan Cc: pgsql-admin@postgresql.org; pgsql-general@postgresql.org Subject: Re: [ADMIN] [GENERAL] pg_buffercache view In response to "Sorin N. Ciolofan" : > > 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 ------=_NextPart_000_0066_01C7868C.F8C34A40 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

 

   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

  =             &= nbsp;FROM pg_database d, pg_buffercache b

       &nbs= p;       WHERE d.X =3D = b.reldatabase

       &nbs= p;       GROUP BY b.reldatabase

       &nbs= p;       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@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Bill Moran
Sent: Tuesday, April 24, 2007 4:03 PM
To: Sorin N. Ciolofan
Cc: pgsql-admin@postgresql.org; pgsql-general@postgresql.org
Subject: Re: [ADMIN] [GENERAL] pg_buffercache view

 

In response to "Sorin N. Ciolofan" <ciolofan@ics.forth.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 =3D 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

------=_NextPart_000_0066_01C7868C.F8C34A40--