Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id AED3B1337B96; Tue, 3 May 2011 11:38:49 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 71465-04; Tue, 3 May 2011 14:38:43 +0000 (UTC) X-Greylist: delayed 00:21:40.706701 by SQLgrey-1.7.6 X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from omr10.networksolutionsemail.com (omr10.networksolutionsemail.com [205.178.146.60]) by mail.postgresql.org (Postfix) with ESMTP id 72D2C1337B69; Tue, 3 May 2011 11:38:39 -0300 (ADT) Received: from cm-omr11 (mail.networksolutionsemail.com [205.178.146.50]) by omr10.networksolutionsemail.com (8.13.6/8.13.6) with ESMTP id p43EGwKe010470; Tue, 3 May 2011 10:16:58 -0400 X-Authenticated-IP: 205.178.146.50 Received: from [205.178.146.50] ([205.178.146.50:45905] helo=webmail60) by cm-omr11 (envelope-from ) (ecelerity 2.2.2.41 r(31179/31189)) with ESMTP id B9/EA-16033-A5E00CD4; Tue, 03 May 2011 10:16:58 -0400 Received: from 65.222.202.26 (mark@remingtondatabasesolutions.com [65.222.202.26]) by webmail60 (Netsol 11.2.30) with WEBMAIL id 22884; Tue, 03 May 2011 14:16:58 +0000 From: "Mark Johnson" To: "Raghavendra" , "Simon Riggs" Cc: "raghu ram" , "pgsql-admin" , "pgsql-general" , "pgsql novice" Importance: Normal Message-ID: X-Mailer: Network Solutions Webmail, Build 11.2.30 X-Originating-IP: [65.222.202.26] X-Forwarded-For: [(null)] Date: Tue, 03 May 2011 14:16:58 +0000 Subject: Re: Can we Flush the Postgres Shared Memory ? MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="--=_vm_0011_W3205622728_22884_1304432218" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.246 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_FONT_FACE_BAD=0.981, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, RCVD_NUMERIC_HELO=1.164 X-Spam-Level: X-Archive-Number: 201105/28 X-Sequence-Number: 37465 ----=_vm_0011_W3205622728_22884_1304432218 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 c= ommands. 1. In Oracle you can flush the redo buffer by issuing a COMMIT or by rota= ting 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) r= emoves sql and pl/sql statements from the shared library cache, (b) flush= es 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 othe= r replies it does not seem possible to flush the catalog cache in Postgre= SQL. 3. In Oracle when you request a flush of the buffer cache it signals a ch= eckpoint to ensure all dirty buffers are written out AND later it will re= move the dirty buffers from memory. This can take anywhere from a few sec= onds on very small systems to several minutes on VLDB systems, per my obs= ervations. The Oracle checkpoint is fast, and the SQL prompt comes back v= ery fast, but the removal of dirty buffers from memory runs in the backgr= ound 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 l= ong 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 t= o signal bgwriter to flush dirty pages to the operating system's cache an= d from there you will see a lazy write to disk (e.g., watch pdflush on li= nux), so immediately re-running a query will still get some caching benef= its eventhough the checkpoint is complete. There are operating system com= mands that you could use for that ("cat /proc/meminfo" to see what's ther= e, "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 m= ore 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 w= hole 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 wrote= : On Tue, May 3, 2011 at 11:54 AM, raghu ram wrot= e: > It may be a silly question, still out of curiosity I want to know, is t= here > 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/ ----=_vm_0011_W3205622728_22884_1304432218 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
 

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.<= /FONT>

1.  In Oracle yo= u can flush the redo buffer by issuing a COMMIT or by rotating the logs.&= nbsp; You can force a log switch in PostgreSQL using "select pg_switch_xl= og();".

2.  In Oracle when you flush the shared pool this does three thin= gs: (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 cat= alog cache in PostgreSQL.

3.  In Oracle wh= en you request a flush of the buffer cache it signals a checkpoint to ens= ure 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 v= ery 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 backgroun= d with a low priority over a long period of time.  If you are planni= ng to use "alter system flush buffer_cache" to clear memory in between te= sts 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 m= anually 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 pdfl= ush on linux), so immediately re-running a query will still get some cach= ing benefits eventhough the checkpoint is complete.  There are opera= ting 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 /p= roc/meminfo" one more time).  And, if you are using SAN consider arr= ay based caching as well.

Sincerely,

=

Mark R. Johnson
O= wner, Remington Database = Solutions, LL= C
Author, Oracle Database 10g: From Nuts to Soup 



The content= s of this email may not be copied or forwarded in part or in whole withou= t the express written consent of the author.

 
-----Original Message-----
= From: Raghavendra [mailto:raghavendra.rao@enterprisedb.com]
Sen= t: Tuesday, May 3, 2011 08:31 AM
To: 'Simon Riggs'
Cc= : 'raghu ram', 'pgsql-admin', 'pgsql-general', 'pgsql novice'
S= ubject: 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<= /A>> wrote:

> It may be a silly question, still out of curio= sity I want to know, is there
> any possible way to flush the Postg= res Shared Memory without restarting the
> cluster.
> In Orac= le, we can flush the SGA, can we get the same feature here..
> Than= ks in Advance.


The CHECKPOINT command will do this for y= ou.

----=_vm_0011_W3205622728_22884_1304432218--