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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bill Moran Sent: Tuesday, April 24, 2007 4:03 PM To: Sorin N. Ciolofan Cc: [EMAIL PROTECTED]; pgsql-general@postgresql.org Subject: Re: [ADMIN] [GENERAL] pg_buffercache view In response to "Sorin N. Ciolofan" <[EMAIL PROTECTED]>: > > 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