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@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 wrote:
On Tue, May 3, 2011 at 11:54 AM, raghu ram 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/
