Increasing the shared memory
Hello!
I'd like to ask you if there is any Postgre configuration parameter (like
the ones defined in postgresql.conf file) that could be used for increasing
the shared memory for Postgre?
Thank you very much
With best regards,
Sorin
http://www.postgresql.org/docs/8.1/interactive/kernel-resources.html
On 4/2/07, Sorin N. Ciolofan <ciolofan@ics.forth.gr> wrote:
Hello!
I'd like to ask you if there is any Postgre configuration parameter (like
the ones defined in postgresql.conf file) that could be used for increasing
the shared memory for Postgre?Thank you very much
With best regards,
Sorin
--
marius@{bsd.com.ro, joy2share.com, vrajamarii.net, ispot.ro}
I guess shared_buffers (in postgresql.conf file) will help you here if you
have properly setup your kernel.SHMMAX value.
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
Show quoted text
On 4/2/07, Sorin N. Ciolofan <ciolofan@ics.forth.gr> wrote:
Hello!
I'd like to ask you if there is any Postgre configuration parameter (like
the ones defined in postgresql.conf file) that could be used for
increasing the shared memory for Postgre?Thank you very much
With best regards,
Sorin
Thanks,
I've a value of 1000 set for shared_buffers, does this means
that I use 8kbX1000=8Mb of Shared Mem?
The definition from the manual is quite confusing:
shared_buffers (integer)
Sets the amount of memory the database server uses for shared memory
buffers. The default is typically 32 megabytes (32MB), but may be less if
your kernel settings will not support it (as determined during initdb). This
setting must be at least 128 kilobytes and at least 16 kilobytes times
max_connections
<http://www.postgresql.org/docs/current/static/runtime-config-connection.htm
l#GUC-MAX-CONNECTIONS> .
What does the integer number represent? Number of shared buffers? If yes,
what size does each shared buffer have?
"The default is typically 32 megabytes" suggests that this integer could
also represent the number of megabytes?!?
In the postgresql.conf file is an ambiguous comment that could induce the
idea that each shared buffer has 8 kb.
So, which is the meaning of this integer?
Thanks.
S.
_____
From: Shoaib Mir [mailto:shoaibmir@gmail.com]
Sent: Monday, April 02, 2007 1:01 PM
To: Sorin N. Ciolofan
Cc: pgsql-general@postgresql.org; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Increasing the shared memory
I guess shared_buffers (in postgresql.conf file) will help you here if you
have properly setup your kernel.SHMMAX value.
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com )
On 4/2/07, Sorin N. Ciolofan <ciolofan@ics.forth.gr> wrote:
Hello!
I'd like to ask you if there is any Postgre configuration parameter (like
the ones defined in postgresql.conf file) that could be used for increasing
the shared memory for Postgre?
Thank you very much
With best regards,
Sorin
An extract from --> http://www.powerpostgresql.com/PerfList/ might help
you....
shared_buffers:
As a reminder: This figure is NOT the total memory PostgreSQL has to work
with. It is the block of dedicated memory PostgreSQL uses for active
operations, and should be a minority of your total RAM on the machine, since
PostgreSQL uses the OS disk cache as well. Unfortunately, the exact amount
of shared buffers required is a complex calculation of total RAM, database
size, number of connections, and query complexity. Thus it's better to go
with some rules of thumb in allocating, and monitor the server (particuarly
pg_statio views) to determine adjustments.
On dedicated servers, useful values seem to be between between 8MB and 400MB
(between 1000 and 50,000 for 8K page size). Factors which raise the desired
shared buffers are larger active portions of the database, large complex
queries, large numbers of simultaneous queries, long-running procedures or
transactions, more available RAM, and faster/more CPUs. And, of course,
other applications on the machine. Contrary to some expectations, allocating
much too much shared_buffers can actually lower peformance, due time
required for scanning. Here's some examples based on anecdotes and TPC tests
on Linux machines:
* Laptop, Celeron processor, 384MB RAM, 25MB database: 12MB/1500
* Athlon server, 1GB RAM, 10GB decision-support database: 120MB/15000
* Quad PIII server, 4GB RAM, 40GB, 150-connection heavy transaction
processing database: 240MB/30000
* Quad Xeon server, 8GB RAM, 200GB, 300-connection heavy transaction
processing database: 400MB/50000
Please note that increasing shared_buffers, and a few other memory
parameters, will require you to modify your operating system's System V
memory parameters. See the main PostgreSQL documentation for instructions on
this.
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
Show quoted text
On 4/2/07, Sorin N. Ciolofan <ciolofan@ics.forth.gr> wrote:
Thanks,
I've a value of 1000 set for shared_buffers, does this means
that I use 8kbX1000=8Mb of Shared Mem?The definition from the manual is quite confusing:
shared_buffers (integer)
Sets the amount of memory the database server uses for shared memory
buffers. The default is typically 32 megabytes (32MB), but may be less if
your kernel settings will not support it (as determined during initdb).
This setting must be at least 128 kilobytes and at least 16 kilobytes times
max_connections<http://www.postgresql.org/docs/current/static/runtime-config-connection.html#GUC-MAX-CONNECTIONS>.What does the integer number represent? Number of shared buffers? If yes,
what size does each shared buffer have?"The default is typically 32 megabytes" suggests that this integer could
also represent the number of megabytes?!?In the postgresql.conf file is an ambiguous comment that could induce the
idea that each shared buffer has 8 kb.So, which is the meaning of this integer?
Thanks.
S.
------------------------------
*From:* Shoaib Mir [mailto:shoaibmir@gmail.com]
*Sent:* Monday, April 02, 2007 1:01 PM
*To:* Sorin N. Ciolofan
*Cc:* pgsql-general@postgresql.org; pgsql-admin@postgresql.org
*Subject:* Re: [ADMIN] Increasing the shared memoryI guess shared_buffers (in postgresql.conf file) will help you here if you
have properly setup your kernel.SHMMAX value.--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com )On 4/2/07, *Sorin N. Ciolofan* <ciolofan@ics.forth.gr> wrote:
Hello!
I'd like to ask you if there is any Postgre configuration parameter (like
the ones defined in postgresql.conf file) that could be used for
increasing the shared memory for Postgre?Thank you very much
With best regards,
Sorin
In response to "Sorin N. Ciolofan" <ciolofan@ics.forth.gr>:
I've a value of 1000 set for shared_buffers, does this means
that I use 8kbX1000=8Mb of Shared Mem?The definition from the manual is quite confusing:
shared_buffers (integer)
Sets the amount of memory the database server uses for shared memory
buffers. The default is typically 32 megabytes (32MB), but may be less if
your kernel settings will not support it (as determined during initdb). This
setting must be at least 128 kilobytes and at least 16 kilobytes times
max_connections
<http://www.postgresql.org/docs/current/static/runtime-config-connection.htm
l#GUC-MAX-CONNECTIONS> .What does the integer number represent? Number of shared buffers? If yes,
what size does each shared buffer have?"The default is typically 32 megabytes" suggests that this integer could
also represent the number of megabytes?!?In the postgresql.conf file is an ambiguous comment that could induce the
idea that each shared buffer has 8 kb.So, which is the meaning of this integer?
Older versions of PostgreSQL, the number was the _number_ of shared buffers.
Each buffer is typically 8K, but this can be altered at compile time (I
believe) and the PGDG has no way to know for sure if whatever packaged version
you installed might have done so.
However, on more recent versions of Postgres (although I don't know exactly
what version first included this) you can specify this value as "M" or "G"
to specify a number of megabytes or gigabytes. Internally, this is still
converted to a number of 8K buffers, but it makes the config file easier to
read and understand.
--
Bill Moran
http://www.potentialtech.com
There is also a add on in contrib (pg_buffercache) that can be used to
give an indication of the number of buffers in use, this can be used to
help find a 'good' shared mem size for your configuration.
David.
Hello!
I've tried first to increase the number of shared buffers, I
doubled it, from 1000 to 2000 (16Mb)
Unfortunately this had no effect.
Then I increased the number of max_locks_per_transaction
from 64 to 128 (these shoul assure about 12 800 lock slots) considering
max_connections=100 and max_prepared_transaction=5 (Quote from the manual -
The shared lock table is created to track locks on max_locks_per_transaction
* (max_connections
<http://www.postgresql.org/docs/8.2/interactive/runtime-config-connection.ht
ml#GUC-MAX-CONNECTIONS> + max_prepared_transactions
<http://www.postgresql.org/docs/8.2/interactive/runtime-config-resource.html
#GUC-MAX-PREPARED-TRANSACTIONS> ) objects (e.g. tables);)
I've also restarted
This had also no effect. Because I can't see any difference
between the maximum input accepted for our application with the old
configuration and the maximum input accepted now, with the new
configuration. It looks like nothing happened.
Thanks
Sorin
_____
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Shoaib Mir
Sent: Monday, April 02, 2007 6:02 PM
To: Sorin N. Ciolofan
Cc: pgsql-general@postgresql.org; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Increasing the shared memory
An extract from --> http://www.powerpostgresql.com/PerfList/ might help
you....
shared_buffers:
As a reminder: This figure is NOT the total memory PostgreSQL has to work
with. It is the block of dedicated memory PostgreSQL uses for active
operations, and should be a minority of your total RAM on the machine, since
PostgreSQL uses the OS disk cache as well. Unfortunately, the exact amount
of shared buffers required is a complex calculation of total RAM, database
size, number of connections, and query complexity. Thus it's better to go
with some rules of thumb in allocating, and monitor the server (particuarly
pg_statio views) to determine adjustments.
On dedicated servers, useful values seem to be between between 8MB and 400MB
(between 1000 and 50,000 for 8K page size). Factors which raise the desired
shared buffers are larger active portions of the database, large complex
queries, large numbers of simultaneous queries, long-running procedures or
transactions, more available RAM, and faster/more CPUs. And, of course,
other applications on the machine. Contrary to some expectations, allocating
much too much shared_buffers can actually lower peformance, due time
required for scanning. Here's some examples based on anecdotes and TPC tests
on Linux machines:
* Laptop, Celeron processor, 384MB RAM, 25MB database: 12MB/1500
* Athlon server, 1GB RAM, 10GB decision-support database: 120MB/15000
* Quad PIII server, 4GB RAM, 40GB, 150-connection heavy transaction
processing database: 240MB/30000
* Quad Xeon server, 8GB RAM, 200GB, 300-connection heavy transaction
processing database: 400MB/50000
Please note that increasing shared_buffers, and a few other memory
parameters, will require you to modify your operating system's System V
memory parameters. See the main PostgreSQL documentation for instructions on
this.
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
In response to "Sorin N. Ciolofan" <ciolofan@ics.forth.gr>:
I've tried first to increase the number of shared buffers, I
doubled it, from 1000 to 2000 (16Mb)Unfortunately this had no effect.
The difference between 8M and and 16M of shared buffers is pretty minor.
Try bumping it up to 250M or so and see if that helps.
You could install the pg_buffercache addon and monitor your buffer usage
to see how much is actually being used.
However, if the problem is write performance (which I'm inferring from your
message that it is) then increasing shared_buffers isn't liable to make a
significant improvement, unless the inserts are doing a lot of querying as
well. With inserts, the speed is going to (most likely) be limited by the
speed of your disks. I may have missed this information in earlier posts,
did you provide details of you hardware configuration? Have you done tests
to find out what speed your disks are running? Have you monitored IO
during your inserts to see if the IO subsystem is maxed out?
Also, the original problem you were trying to solve has been trimmed from
this thread, which makes me wonder if any of my advice is relevant.
Then I increased the number of max_locks_per_transaction
from 64 to 128 (these shoul assure about 12 800 lock slots) considering
max_connections=100 and max_prepared_transaction=5 (Quote from the manual -
The shared lock table is created to track locks on max_locks_per_transaction
* (max_connections
<http://www.postgresql.org/docs/8.2/interactive/runtime-config-connection.ht
ml#GUC-MAX-CONNECTIONS> + max_prepared_transactions
<http://www.postgresql.org/docs/8.2/interactive/runtime-config-resource.html
#GUC-MAX-PREPARED-TRANSACTIONS> ) objects (e.g. tables);)I've also restarted
This had also no effect. Because I can't see any difference
between the maximum input accepted for our application with the old
configuration and the maximum input accepted now, with the new
configuration. It looks like nothing happened.Thanks
Sorin
_____
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Shoaib Mir
Sent: Monday, April 02, 2007 6:02 PM
To: Sorin N. Ciolofan
Cc: pgsql-general@postgresql.org; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Increasing the shared memoryAn extract from --> http://www.powerpostgresql.com/PerfList/ might help
you....shared_buffers:
As a reminder: This figure is NOT the total memory PostgreSQL has to work
with. It is the block of dedicated memory PostgreSQL uses for active
operations, and should be a minority of your total RAM on the machine, since
PostgreSQL uses the OS disk cache as well. Unfortunately, the exact amount
of shared buffers required is a complex calculation of total RAM, database
size, number of connections, and query complexity. Thus it's better to go
with some rules of thumb in allocating, and monitor the server (particuarly
pg_statio views) to determine adjustments.
On dedicated servers, useful values seem to be between between 8MB and 400MB
(between 1000 and 50,000 for 8K page size). Factors which raise the desired
shared buffers are larger active portions of the database, large complex
queries, large numbers of simultaneous queries, long-running procedures or
transactions, more available RAM, and faster/more CPUs. And, of course,
other applications on the machine. Contrary to some expectations, allocating
much too much shared_buffers can actually lower peformance, due time
required for scanning. Here's some examples based on anecdotes and TPC tests
on Linux machines:* Laptop, Celeron processor, 384MB RAM, 25MB database: 12MB/1500
* Athlon server, 1GB RAM, 10GB decision-support database: 120MB/15000
* Quad PIII server, 4GB RAM, 40GB, 150-connection heavy transaction
processing database: 240MB/30000
* Quad Xeon server, 8GB RAM, 200GB, 300-connection heavy transaction
processing database: 400MB/50000Please note that increasing shared_buffers, and a few other memory
parameters, will require you to modify your operating system's System V
memory parameters. See the main PostgreSQL documentation for instructions on
this.--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
--
Bill Moran
http://www.potentialtech.com
"Sorin N. Ciolofan" <ciolofan@ics.forth.gr> writes:
This had also no effect. Because I can't see any difference
between the maximum input accepted for our application with the old
configuration and the maximum input accepted now, with the new
configuration. It looks like nothing happened.
This is the first you've mentioned about *why* you wanted to increase the
settings, and what it sounds like to me is that you are increasing the
wrong thing. What's the actual problem?
regards, tom lane
I will simplify the things in order to describe when the error occurred:
The input of the application is some data which is read from files on disk,
processed and then inserted in the database in one transaction. This total
quantity of data represents an integer number of data files, n*q, where q is
a file which has always 60kb and n is the positive integer.
For n=23 and shared_buffers=1000 and max_locks_per_transaction=64 the
Postgres throws the following exception:
org.postgresql.util.PSQLException: ERROR: out of shared memory
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorI
mpl.java:1525)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.ja
va:1309)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j
ava:452)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2St
atement.java:340)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2State
ment.java:286)
at
gr.forth.ics.rdfsuite.rssdb.repr.SSRepresentation.createClassTable(SSReprese
ntation.java:1936)
at
gr.forth.ics.rdfsuite.rssdb.repr.SSRepresentation.store(SSRepresentation.jav
a:1783)
at
gr.forth.ics.rdfsuite.swkm.model.db.impl.RDFDB_Model.storeSchema(RDFDB_Model
.java:814)
at
gr.forth.ics.rdfsuite.swkm.model.db.impl.RDFDB_Model.store(RDFDB_Model.java:
525)
at
gr.forth.ics.rdfsuite.services.impl.ImporterImpl.storeImpl(ImporterImpl.java
:79)
... 50 more
For n=23 I estimated that we create and manipulate about 8000 tables.
One of the suggestion received here was that maybe there are not sufficient
locks slots per transaction, that's why I've increased the
max_locks_per_transaction (to 128) in order to be able to manipulate about
12 800 tables.
So, I doubled both shared_buffers and max_locks_per_transaction and for
n=23 I received the same error. I would expect to see a difference, even a
little one, for example from n=23 to n=24 but the maximum quantity of data
accepted was the same.
Thank you very much,
With best regards
Sorin
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, April 12, 2007 5:01 PM
To: Sorin N. Ciolofan
Cc: 'Shoaib Mir'; pgsql-general@postgresql.org; pgsql-admin@postgresql.org;
'Dimitris Kotzinos'
Subject: Re: [ADMIN] Increasing the shared memory
"Sorin N. Ciolofan" <ciolofan@ics.forth.gr> writes:
This had also no effect. Because I can't see any
difference
between the maximum input accepted for our application with the old
configuration and the maximum input accepted now, with the new
configuration. It looks like nothing happened.
This is the first you've mentioned about *why* you wanted to increase the
settings, and what it sounds like to me is that you are increasing the
wrong thing. What's the actual problem?
regards, tom lane
"Sorin N. Ciolofan" <ciolofan@ics.forth.gr> writes:
I will simplify the things in order to describe when the error occurred:
The input of the application is some data which is read from files on disk,
processed and then inserted in the database in one transaction. This total
quantity of data represents an integer number of data files, n*q, where q is
a file which has always 60kb and n is the positive integer.
For n=23 and shared_buffers=1000 and max_locks_per_transaction=64 the
Postgres throws the following exception:
org.postgresql.util.PSQLException: ERROR: out of shared memory
For n=23 I estimated that we create and manipulate about 8000 tables.
Okay, as far as I know the only user-causable way to get that message is
to run out of lock-table space, and a transaction does take a lock for
each table it touches, so I concur that raising
max_locks_per_transaction is an appropriate response. If you didn't see
any change in the maximum N you could handle then I wonder whether you
actually did raise it --- does "show max_locks_per_transaction" reflect
the intended new value?
Another possibility is that there's something about your code that makes
the number of locks involved very nonlinear in N. You could try
checking the number of rows in pg_locks immediately before commit at
some smaller values of N to confirm what the scaling is really like.
regards, tom lane
Dear all,
Thanks for your advices. I'd like to ask you where can I download the
pg_buffercache add-on and also where can I find some documentation about how
can I install it?
Thank you
Sorin
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Bill Moran
Sent: Thursday, April 12, 2007 4:14 PM
To: Sorin N. Ciolofan
Cc: 'Shoaib Mir'; pgsql-general@postgresql.org; pgsql-admin@postgresql.org;
'Dimitris Kotzinos'
Subject: Re: [GENERAL] [ADMIN] Increasing the shared memory
In response to "Sorin N. Ciolofan" <ciolofan@ics.forth.gr>:
I've tried first to increase the number of shared buffers,
I
doubled it, from 1000 to 2000 (16Mb)
Unfortunately this had no effect.
The difference between 8M and and 16M of shared buffers is pretty minor.
Try bumping it up to 250M or so and see if that helps.
You could install the pg_buffercache addon and monitor your buffer usage
to see how much is actually being used.
However, if the problem is write performance (which I'm inferring from your
message that it is) then increasing shared_buffers isn't liable to make a
significant improvement, unless the inserts are doing a lot of querying as
well. With inserts, the speed is going to (most likely) be limited by the
speed of your disks. I may have missed this information in earlier posts,
did you provide details of you hardware configuration? Have you done tests
to find out what speed your disks are running? Have you monitored IO
during your inserts to see if the IO subsystem is maxed out?
Also, the original problem you were trying to solve has been trimmed from
this thread, which makes me wonder if any of my advice is relevant.
Then I increased the number of max_locks_per_transaction
from 64 to 128 (these shoul assure about 12 800 lock slots) considering
max_connections=100 and max_prepared_transaction=5 (Quote from the manual
-
The shared lock table is created to track locks on
max_locks_per_transaction
* (max_connections
<http://www.postgresql.org/docs/8.2/interactive/runtime-config-connection.ht
ml#GUC-MAX-CONNECTIONS> + max_prepared_transactions
<http://www.postgresql.org/docs/8.2/interactive/runtime-config-resource.html
#GUC-MAX-PREPARED-TRANSACTIONS> ) objects (e.g. tables);)
I've also restarted
This had also no effect. Because I can't see any
difference
Show quoted text
between the maximum input accepted for our application with the old
configuration and the maximum input accepted now, with the new
configuration. It looks like nothing happened.
In response to "Sorin N. Ciolofan" <ciolofan@ics.forth.gr>:
Dear all,
Thanks for your advices. I'd like to ask you where can I download the
pg_buffercache add-on and also where can I find some documentation about how
can I install it?
It's part of the contrib directory that ships with the source tarball.
Depending on your OS and associated packaging system (which you don't
bother to mention) it's probably available via RPMs or DEBs or whatever.
For example, under FreeBSD it's in /usr/ports/databases/postgresql-contrib
--
Bill Moran
http://www.potentialtech.com
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
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
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
---------------------------(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
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
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?
