Can we Flush the Postgres Shared Memory ?
Respected Committers,
It may be a silly question, still out of curiosity I want to know, is there
any possible way to flush the Postgres Shared Memory without restarting the
cluster.
In Oracle, we can flush the SGA, can we get the same feature here..
Thanks in Advance.
Regards
Raghu Ram
EnterpriseDB Corporation
On Tue, May 3, 2011 at 11:54 AM, raghu ram <raghuchennuru@gmail.com> wrote:
It may be a silly question, still out of curiosity I want to know, is there
any possible way to flush the Postgres Shared Memory without restarting the
cluster.
In Oracle, we can flush the SGA, can we get the same feature here..
Thanks in Advance.
The CHECKPOINT command will do this for you.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On Tue, May 3, 2011 at 5:37 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
On Tue, May 3, 2011 at 11:54 AM, raghu ram <raghuchennuru@gmail.com>
wrote:It may be a silly question, still out of curiosity I want to know, is
there
any possible way to flush the Postgres Shared Memory without restarting
the
cluster.
In Oracle, we can flush the SGA, can we get the same feature here..
Thanks in Advance.The CHECKPOINT command will do this for you.
This command will empty the PSM...
Best Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/
On Tue, May 3, 2011 at 6:01 PM, Raghavendra <
raghavendra.rao@enterprisedb.com> wrote:
On Tue, May 3, 2011 at 5:37 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
On Tue, May 3, 2011 at 11:54 AM, raghu ram <raghuchennuru@gmail.com>
wrote:It may be a silly question, still out of curiosity I want to know, is
there
any possible way to flush the Postgres Shared Memory without restarting
the
cluster.
In Oracle, we can flush the SGA, can we get the same feature here..
Thanks in Advance.The CHECKPOINT command will do this for you.
According to PostgreSQL documentation, whenever you execute "CHECKPOINT" in
the database,it will flush the modified data files presented in the Shared
Buffers retuned to the Disk.
http://www.postgresql.org/docs/9.0/static/sql-checkpoint.html
Is this clears the entire shared memory cache and same time,if i execute
fresh SQL statement, Data will be retuned from disk??
--Raghu Ram
On Tue, May 3, 2011 at 8:30 AM, raghu ram <raghuchennuru@gmail.com> wrote:
On Tue, May 3, 2011 at 6:01 PM, Raghavendra
<raghavendra.rao@enterprisedb.com> wrote:On Tue, May 3, 2011 at 5:37 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
On Tue, May 3, 2011 at 11:54 AM, raghu ram <raghuchennuru@gmail.com>
wrote:It may be a silly question, still out of curiosity I want to know, is
there
any possible way to flush the Postgres Shared Memory without restarting
the
cluster.
In Oracle, we can flush the SGA, can we get the same feature here..
Thanks in Advance.The CHECKPOINT command will do this for you.
According to PostgreSQL documentation, whenever you execute "CHECKPOINT" in
the database,it will flush the modified data files presented in the Shared
Buffers retuned to the Disk.
http://www.postgresql.org/docs/9.0/static/sql-checkpoint.html
Is this clears the entire shared memory cache and same time,if i execute
fresh SQL statement, Data will be retuned from disk??
no it will not, or at least there is no guarantee it will be. the
only way to reset the buffers in that sense is to restart the database
(and even then they might not be read from disk, because they could
sit in the o/s cache). to force a read from the drive you'd have to
reboot the server, or at least shut it down and use a lot of memory
for some other purpose.
merlin
On Tue, May 3, 2011 at 6:01 PM, Raghavendra <
raghavendra.rao@enterprisedb.com> wrote:On Tue, May 3, 2011 at 5:37 PM, Simon Riggs <simon@2ndquadrant.com>
wrote:On Tue, May 3, 2011 at 11:54 AM, raghu ram <raghuchennuru@gmail.com>
wrote:It may be a silly question, still out of curiosity I want to know, is
there
any possible way to flush the Postgres Shared Memory without
restarting
thecluster.
In Oracle, we can flush the SGA, can we get the same feature here..
Thanks in Advance.The CHECKPOINT command will do this for you.
According to PostgreSQL documentation, whenever you execute "CHECKPOINT"
in
the database,it will flush the modified data files presented in the Shared
Buffers retuned to the Disk.http://www.postgresql.org/docs/9.0/static/sql-checkpoint.html
Is this clears the entire shared memory cache and same time,if i execute
fresh SQL statement, Data will be retuned from disk??
No. Checkpoint means all dirty buffers are written to the datafiles, it
does not mean emptying the shared buffers. Checkpoints happen regularly so
this would have an unwanted impact on performance.
And besides that, there's a page cache maintained by the OS (not sure if
you're running Linux or Windows). So even when the block does not exist in
the shared buffers, it may be in the page cache (thus not read from the
drive).
Dropping the page cache is quite simple (http://linux-mm.org/Drop_Caches),
emptying the shared buffers is not that simple - I guess the easiest way
is to restart the db.
What are you trying to achieve? Why do you need this?
Tomas
On Tue, May 3, 2011 at 2:30 PM, raghu ram <raghuchennuru@gmail.com> wrote:
The CHECKPOINT command will do this for you.
According to PostgreSQL documentation, whenever you execute "CHECKPOINT" in
the database,it will flush the modified data files presented in the Shared
Buffers retuned to the Disk.
http://www.postgresql.org/docs/9.0/static/sql-checkpoint.html
Is this clears the entire shared memory cache and same time,if i execute
fresh SQL statement, Data will be retuned from disk??
No, but then you'd need to flush OS buffers and all disk caches as
well to make that effective.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
2011/5/3 Merlin Moncure <mmoncure@gmail.com>:
On Tue, May 3, 2011 at 8:30 AM, raghu ram <raghuchennuru@gmail.com> wrote:
On Tue, May 3, 2011 at 6:01 PM, Raghavendra
<raghavendra.rao@enterprisedb.com> wrote:On Tue, May 3, 2011 at 5:37 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
On Tue, May 3, 2011 at 11:54 AM, raghu ram <raghuchennuru@gmail.com>
wrote:It may be a silly question, still out of curiosity I want to know, is
there
any possible way to flush the Postgres Shared Memory without restarting
the
cluster.
In Oracle, we can flush the SGA, can we get the same feature here..
Thanks in Advance.The CHECKPOINT command will do this for you.
According to PostgreSQL documentation, whenever you execute "CHECKPOINT" in
the database,it will flush the modified data files presented in the Shared
Buffers retuned to the Disk.
http://www.postgresql.org/docs/9.0/static/sql-checkpoint.html
Is this clears the entire shared memory cache and same time,if i execute
fresh SQL statement, Data will be retuned from disk??no it will not, or at least there is no guarantee it will be. the
only way to reset the buffers in that sense is to restart the database
(and even then they might not be read from disk, because they could
sit in the o/s cache). to force a read from the drive you'd have to
reboot the server, or at least shut it down and use a lot of memory
for some other purpose.
with linux, you can : "echo 3 > /proc/sys/vm/drop_caches" for the OS cache
merlin
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
On Tue, May 3, 2011 at 8:55 AM, Cédric Villemain
<cedric.villemain.debian@gmail.com> wrote:
2011/5/3 Merlin Moncure <mmoncure@gmail.com>:
no it will not, or at least there is no guarantee it will be. the
only way to reset the buffers in that sense is to restart the database
(and even then they might not be read from disk, because they could
sit in the o/s cache). to force a read from the drive you'd have to
reboot the server, or at least shut it down and use a lot of memory
for some other purpose.with linux, you can : "echo 3 > /proc/sys/vm/drop_caches" for the OS cache
yeah -- good point. aside: does that also drop cache on the drive/raid card?
merlin
2011/5/3 Merlin Moncure <mmoncure@gmail.com>:
On Tue, May 3, 2011 at 8:55 AM, Cédric Villemain
<cedric.villemain.debian@gmail.com> wrote:2011/5/3 Merlin Moncure <mmoncure@gmail.com>:
no it will not, or at least there is no guarantee it will be. the
only way to reset the buffers in that sense is to restart the database
(and even then they might not be read from disk, because they could
sit in the o/s cache). to force a read from the drive you'd have to
reboot the server, or at least shut it down and use a lot of memory
for some other purpose.with linux, you can : "echo 3 > /proc/sys/vm/drop_caches" for the OS cache
yeah -- good point. aside: does that also drop cache on the drive/raid card?
no -- good point too ! (damn! how SAN users will do...maybe EMC or
other are good enough to provide some control panel for that ? )
and as I read on the link provided by Tomas, it is better to issue a
'sync' before trying to drop cache (I do that sometime, but postgresql
flush its write before shutdown, so I expected the dirty pages in OS
cache not to be relative to postgresql files.)
--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
Is there a particular one of Oracle's memory clearning features you want to use in PostgreSQL? In Oracle you cannot flush the entire SGA without a restart, but you can flush three parts of the SGA using three separate commands.
1. In Oracle you can flush the redo buffer by issuing a COMMIT or by rotating the logs. You can force a log switch in PostgreSQL using "select pg_switch_xlog();".
2. In Oracle when you flush the shared pool this does three things: (a) removes sql and pl/sql statements from the shared library cache, (b) flushes the dictionary cache of object info and security data, and (c) flushes the query result cache (11g only). I am relatively new to PostgreSQL and have not seen an equivalent in PostgreSQL to these things. Based on other replies it does not seem possible to flush the catalog cache in PostgreSQL.
3. In Oracle when you request a flush of the buffer cache it signals a checkpoint to ensure all dirty buffers are written out AND later it will remove the dirty buffers from memory. This can take anywhere from a few seconds on very small systems to several minutes on VLDB systems, per my observations. The Oracle checkpoint is fast, and the SQL prompt comes back very fast, but the removal of dirty buffers from memory runs in the background with a low priority over a long period of time. If you are planning to use "alter system flush buffer_cache" to clear memory in between tests you actually have no way to know when memory is clear except to wait a long time and then assume all is well (yes, this is also true with ASM and direct i/o to raw devices). In PostgreSQL, you can checkpoint manually to signal bgwriter to flush dirty pages to the operating system's cache and from there you will see a lazy write to disk (e.g., watch pdflush on linux), so immediately re-running a query will still get some caching benefits eventhough the checkpoint is complete. There are operating system commands that you could use for that ("cat /proc/meminfo" to see what's there, "sync" to write dirty pages to disk, then "echo 3 > /proc/sys/vm/drop_caches" to remove the now clean pages, and then "cat /proc/meminfo" one more time). And, if you are using SAN consider array based caching as well.
Sincerely,
Mark R. Johnson
Owner, Remington Database Solutions, LLC
Author, Oracle Database 10g: From Nuts to Soup
The contents of this email may not be copied or forwarded in part or in whole without the express written consent of the author.
-----Original Message-----
From: Raghavendra [mailto:raghavendra.rao@enterprisedb.com]
Sent: Tuesday, May 3, 2011 08:31 AM
To: 'Simon Riggs'
Cc: 'raghu ram', 'pgsql-admin', 'pgsql-general', 'pgsql novice'
Subject: Re: [ADMIN] Can we Flush the Postgres Shared Memory ?
On Tue, May 3, 2011 at 5:37 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
On Tue, May 3, 2011 at 11:54 AM, raghu ram <raghuchennuru@gmail.com> wrote:
It may be a silly question, still out of curiosity I want to know, is there
any possible way to flush the Postgres Shared Memory without restarting the
cluster.
In Oracle, we can flush the SGA, can we get the same feature here..
Thanks in Advance.
The CHECKPOINT command will do this for you.
This command will empty the PSM...
Best Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/
Import Notes
Resolved by subject fallback
On Tue, May 3, 2011 at 3:16 PM, Mark Johnson
<mark@remingtondatabasesolutions.com> wrote:
The contents of this email may not be copied or forwarded in part or in
whole without the express written consent of the author.
Pleased to meet you Mark.
If you post here, the above disclaimer is not effective. Right now
your words are being copied across the internet...
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On Tue, May 03, 2011 at 03:33:34PM +0100, Simon Riggs wrote:
The contents of this email may not be copied or forwarded in part or in
whole without the express written consent of the author.Pleased to meet you Mark.
If you post here, the above disclaimer is not effective. Right now
your words are being copied across the internet...
By typing / selecting a public list address "written consent
of the author" can be assumed to exist implicitely ;-)
Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
On Tue, May 03, 2011 at 03:33:34PM +0100, Simon Riggs wrote:
If you post here, the above disclaimer is not effective. Right now
your words are being copied across the internet...
By typing / selecting a public list address "written consent
of the author" can be assumed to exist implicitely ;-)
Nonetheless, corporate lawyers who insist on such disclaimers on all
email are idiots, and make their company's employees look like idiots
as well. Every disclaimer on obviously-public mail hastens the day
when such disclaimers will have no legal force whatsoever (if indeed
there's any left to them now).
regards, tom lane
On 03/05/2011 16:08, Tom Lane wrote:
Karsten Hilbert<Karsten.Hilbert@gmx.net> writes:
On Tue, May 03, 2011 at 03:33:34PM +0100, Simon Riggs wrote:
If you post here, the above disclaimer is not effective. Right now
your words are being copied across the internet...By typing / selecting a public list address "written consent
of the author" can be assumed to exist implicitely ;-)Nonetheless, corporate lawyers who insist on such disclaimers on all
email are idiots, and make their company's employees look like idiots
as well. Every disclaimer on obviously-public mail hastens the day
when such disclaimers will have no legal force whatsoever (if indeed
there's any left to them now).
I don't want to start a flame war, but did they every have any legal
force in the first place?
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
On 3 May 2011 16:49, Raymond O'Donnell <rod@iol.ie> wrote:
I don't want to start a flame war, but did they every have any legal force
in the first place?
No.
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services
Yes, understood and agreed. My mail server adds it automatically. I can manually remove it prior to sending to the mail list.
-Mark
-----Original Message-----
From: Simon Riggs [mailto:simon@2ndQuadrant.com]
Sent: Tuesday, May 3, 2011 10:33 AM
To: 'Mark Johnson'
Cc: 'pgsql-admin', 'pgsql-general'
Subject: Re: [ADMIN] Can we Flush the Postgres Shared Memory ?
On Tue, May 3, 2011 at 3:16 PM, Mark Johnson wrote: > The contents of this email may not be copied or forwarded in part or in > whole without the express written consent of the author. Pleased to meet you Mark. If you post here, the above disclaimer is not effective. Right now your words are being copied across the internet... -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Import Notes
Resolved by subject fallback
On 5/3/2011 7:33 AM, Simon Riggs wrote:
Pleased to meet you Mark.
If you post here, the above disclaimer is not effective. Right now
your words are being copied across the internet...
I believe our community needs to move past posting replies like this. It
isn't even relevant to the context of his question and makes us look
like a bunch of ideological buffoons.
JD