SEO

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/i.../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

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
[mailtogsql-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/i.../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

Hello!

Do you know which could be the reasons that could conduce an application to
not release the shared buffers, even after the application was shut down?
I noticed that only if a pg_ctl restart command is issued some of the
buffers are set free.

Thank you very much
With best regards,
Sorin



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

In response to "Sorin N. Ciolofan" <ciolofan@ics.forth.gr>:

>
> Hello!
>
> Do you know which could be the reasons that could conduce an application to
> not release the shared buffers, even after the application was shut down?
> I noticed that only if a pg_ctl restart command is issued some of the
> buffers are set free.

The reason would be "by design."

If the server flushes its cache every time the application restarts, the
cache isn't going to be very effective.

If PostgreSQL is using more shared buffers than you're comfortable with,
reduce the shared_buffers setting in the config. That will allow the OS
to decide how to use the memory instead.

--
Bill Moran
http://www.potentialtech.com

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

I don't know the algorithm on which Postgre uses the shared buffers but I'd
like to find the principles behind it. Let's assume the following scenario:
I've set shared_buffers=3000
At the starting of Postgres there are 115 buffers used by database A
After the execution of some processing caused by a java methodA1()
invocation, 2850 buffers are used by A.
What happens next if these 2850 buffers remains used even if the methodA1()
finished its execution?
Suppose that now a methodA2() invocation occurs and this method works with
database A, too. Will be the 2850 buffers reused or will postgre throw an
"out of shared memory" exception?
What happens if a methodB() invocation occurs, assuming that this method
tries to work with database B?
How Postgre decides the allocation of shared_buffers?


Thanks
Sorin

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailtogsql-general-owner@postgresql.org] On Behalf Of Bill Moran
Sent: Thursday, April 26, 2007 3:32 PM
To: Sorin N. Ciolofan
Cc: pgsql-general@postgresql.org; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] [GENERAL] pg_buffercache view

In response to "Sorin N. Ciolofan" <ciolofan@ics.forth.gr>:

>
> Hello!
>
> Do you know which could be the reasons that could conduce an application
to
> not release the shared buffers, even after the application was shut down?
> I noticed that only if a pg_ctl restart command is issued some of the
> buffers are set free.

The reason would be "by design."

If the server flushes its cache every time the application restarts, the
cache isn't going to be very effective.

If PostgreSQL is using more shared buffers than you're comfortable with,
reduce the shared_buffers setting in the config. That will allow the OS
to decide how to use the memory instead.

--
Bill Moran
http://www.potentialtech.com

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match