Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id CCD349FB5C7; Tue, 24 Apr 2007 10:03:27 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 36760-03-3; Tue, 24 Apr 2007 10:03:10 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.potentialtech.com (internet.potentialtech.com [66.167.251.6]) by postgresql.org (Postfix) with ESMTP id BC8799FBC3E; Tue, 24 Apr 2007 10:03:01 -0300 (ADT) Received: from vanquish.pgh.priv.collaborativefusion.com (pr40.pitbpa0.pub.collaborativefusion.com [206.210.89.202]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.potentialtech.com (Postfix) with ESMTP id F1121EBC78; Tue, 24 Apr 2007 09:02:59 -0400 (EDT) Date: Tue, 24 Apr 2007 09:02:54 -0400 From: Bill Moran To: "Sorin N. Ciolofan" Cc: , Subject: Re: [GENERAL] pg_buffercache view Message-Id: <20070424090254.037f7996.wmoran@potentialtech.com> In-Reply-To: <20070424100322.7E7EE8E40FC@mailhost.ics.forth.gr> References: <20070418095419.169bc6cc.wmoran@potentialtech.com> <20070424100322.7E7EE8E40FC@mailhost.ics.forth.gr> X-Mailer: Sylpheed 2.3.1 (GTK+ 2.10.11; i386-portbld-freebsd6.1) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/217 X-Sequence-Number: 25281 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