pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_buffercache view
7+ messages / 2 participants
[nested] [flat]
* pg_buffercache view
@ 2007-04-24 10:04 Sorin N. Ciolofan <ciolofan@ics.forth.gr>
2007-04-24 13:02 ` Re: [GENERAL] pg_buffercache view Bill Moran <wmoran@potentialtech.com>
0 siblings, 1 reply; 7+ messages in thread
From: Sorin N. Ciolofan @ 2007-04-24 10:04 UTC (permalink / raw)
To: pgsql-admin; +Cc: pgsql-general@postgresql.org
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
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?
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?
Is there any way to know at a certain moment with precision how much shared
memory expressed in Mb is used?
With best regards,
Sorin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [GENERAL] pg_buffercache view
2007-04-24 10:04 pg_buffercache view Sorin N. Ciolofan <ciolofan@ics.forth.gr>
@ 2007-04-24 13:02 ` Bill Moran <wmoran@potentialtech.com>
2007-04-24 13:24 ` Re: [GENERAL] pg_buffercache view Sorin N. Ciolofan <ciolofan@ics.forth.gr>
2007-04-26 10:01 ` Re: [GENERAL] pg_buffercache view Sorin N. Ciolofan <ciolofan@ics.forth.gr>
0 siblings, 2 replies; 7+ messages in thread
From: Bill Moran @ 2007-04-24 13:02 UTC (permalink / raw)
To: Sorin N. Ciolofan <ciolofan@ics.forth.gr>; +Cc: pgsql-admin; pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [GENERAL] pg_buffercache view
2007-04-24 10:04 pg_buffercache view Sorin N. Ciolofan <ciolofan@ics.forth.gr>
2007-04-24 13:02 ` Re: [GENERAL] pg_buffercache view Bill Moran <wmoran@potentialtech.com>
@ 2007-04-24 13:24 ` Sorin N. Ciolofan <ciolofan@ics.forth.gr>
1 sibling, 0 replies; 7+ messages in thread
From: Sorin N. Ciolofan @ 2007-04-24 13:24 UTC (permalink / raw)
To: 'Bill Moran' <wmoran@potentialtech.com>; +Cc: pgsql-admin; pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [GENERAL] pg_buffercache view
2007-04-24 10:04 pg_buffercache view Sorin N. Ciolofan <ciolofan@ics.forth.gr>
2007-04-24 13:02 ` Re: [GENERAL] pg_buffercache view Bill Moran <wmoran@potentialtech.com>
@ 2007-04-26 10:01 ` Sorin N. Ciolofan <ciolofan@ics.forth.gr>
2007-04-26 12:32 ` Re: [GENERAL] pg_buffercache view Bill Moran <wmoran@potentialtech.com>
1 sibling, 1 reply; 7+ messages in thread
From: Sorin N. Ciolofan @ 2007-04-26 10:01 UTC (permalink / raw)
To: pgsql-general@postgresql.org; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [GENERAL] pg_buffercache view
2007-04-24 10:04 pg_buffercache view Sorin N. Ciolofan <ciolofan@ics.forth.gr>
2007-04-24 13:02 ` Re: [GENERAL] pg_buffercache view Bill Moran <wmoran@potentialtech.com>
2007-04-26 10:01 ` Re: [GENERAL] pg_buffercache view Sorin N. Ciolofan <ciolofan@ics.forth.gr>
@ 2007-04-26 12:32 ` Bill Moran <wmoran@potentialtech.com>
2007-04-26 13:05 ` Re: [GENERAL] pg_buffercache view Sorin N. Ciolofan <ciolofan@ics.forth.gr>
0 siblings, 1 reply; 7+ messages in thread
From: Bill Moran @ 2007-04-26 12:32 UTC (permalink / raw)
To: Sorin N. Ciolofan <ciolofan@ics.forth.gr>; +Cc: pgsql-general@postgresql.org; pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [GENERAL] pg_buffercache view
2007-04-24 10:04 pg_buffercache view Sorin N. Ciolofan <ciolofan@ics.forth.gr>
2007-04-24 13:02 ` Re: [GENERAL] pg_buffercache view Bill Moran <wmoran@potentialtech.com>
2007-04-26 10:01 ` Re: [GENERAL] pg_buffercache view Sorin N. Ciolofan <ciolofan@ics.forth.gr>
2007-04-26 12:32 ` Re: [GENERAL] pg_buffercache view Bill Moran <wmoran@potentialtech.com>
@ 2007-04-26 13:05 ` Sorin N. Ciolofan <ciolofan@ics.forth.gr>
2007-04-26 13:28 ` Re: [GENERAL] pg_buffercache view Bill Moran <wmoran@potentialtech.com>
0 siblings, 1 reply; 7+ messages in thread
From: Sorin N. Ciolofan @ 2007-04-26 13:05 UTC (permalink / raw)
To: 'Bill Moran' <wmoran@potentialtech.com>; +Cc: pgsql-general@postgresql.org; pgsql-admin
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
[mailto:pgsql-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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [GENERAL] pg_buffercache view
2007-04-24 10:04 pg_buffercache view Sorin N. Ciolofan <ciolofan@ics.forth.gr>
2007-04-24 13:02 ` Re: [GENERAL] pg_buffercache view Bill Moran <wmoran@potentialtech.com>
2007-04-26 10:01 ` Re: [GENERAL] pg_buffercache view Sorin N. Ciolofan <ciolofan@ics.forth.gr>
2007-04-26 12:32 ` Re: [GENERAL] pg_buffercache view Bill Moran <wmoran@potentialtech.com>
2007-04-26 13:05 ` Re: [GENERAL] pg_buffercache view Sorin N. Ciolofan <ciolofan@ics.forth.gr>
@ 2007-04-26 13:28 ` Bill Moran <wmoran@potentialtech.com>
0 siblings, 0 replies; 7+ messages in thread
From: Bill Moran @ 2007-04-26 13:28 UTC (permalink / raw)
To: Sorin N. Ciolofan <ciolofan@ics.forth.gr>; +Cc: pgsql-general@postgresql.org; pgsql-admin
In response to "Sorin N. Ciolofan" <ciolofan@ics.forth.gr>:
>
> 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?
They'll be reused the next time a query needs the same data, or discarded
when the buffer space is needed for something else.
> Suppose that now a methodA2() invocation occurs and this method works with
> database A, too. Will be the 2850 buffers reused
yes
> or will postgre throw an
> "out of shared memory" exception?
no
> What happens if a methodB() invocation occurs, assuming that this method
> tries to work with database B?
Buffers will be allocated/reallocate/reused as best fits the usage pattern
of the server.
> How Postgres decides the allocation of shared_buffers?
They're "buffers".
They follow the design of just about any other type of buffer:
http://foldoc.org/index.cgi?query=buffer&action=Search
"Buffers are used to decouple processes so that the reader and writer may
operate at different speeds or on different sized blocks of data."
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-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/
>
>
--
Bill Moran
http://www.potentialtech.com
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-04-26 13:28 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-24 10:04 pg_buffercache view Sorin N. Ciolofan <ciolofan@ics.forth.gr>
2007-04-24 13:02 ` Bill Moran <wmoran@potentialtech.com>
2007-04-24 13:24 ` Sorin N. Ciolofan <ciolofan@ics.forth.gr>
2007-04-26 10:01 ` Sorin N. Ciolofan <ciolofan@ics.forth.gr>
2007-04-26 12:32 ` Bill Moran <wmoran@potentialtech.com>
2007-04-26 13:05 ` Sorin N. Ciolofan <ciolofan@ics.forth.gr>
2007-04-26 13:28 ` Bill Moran <wmoran@potentialtech.com>
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