|
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----- 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 |
