From: Sorin N. Ciolofan <ciolofan@ics.forth.gr>
To: 'Bill Moran' <wmoran@potentialtech.com>
Cc: pgsql-admin@postgresql.org
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] pg_buffercache view
Date: Tue, 24 Apr 2007 16:24:02 +0300
Message-ID: <20070424132308.EEDED8E40FC@mailhost.ics.forth.gr> (raw)
In-Reply-To: <20070424090254.037f7996.wmoran@potentialtech.com>
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" <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 = 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ciolofan@ics.forth.gr, wmoran@potentialtech.com, pgsql-general@postgresql.org
Subject: Re: [GENERAL] pg_buffercache view
In-Reply-To: <20070424132308.EEDED8E40FC@mailhost.ics.forth.gr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox