Обсуждение: 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:The CHECKPOINT command will do this for you.
> 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.
This command will empty the PSM...
Best Regards,
Raghavendra
EnterpriseDB Corporation
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:The CHECKPOINT command will do this for you.
> 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.
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.
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 >>> 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. 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
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
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
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