pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance Tuning and Disk Cache
5+ messages / 5 participants
[nested] [flat]
* Performance Tuning and Disk Cache
@ 2007-03-18 12:45 Barry Moore <barry.moore@genetics.utah.edu>
0 siblings, 3 replies; 5+ messages in thread
From: Barry Moore @ 2007-03-18 12:45 UTC (permalink / raw)
To: pgsql-performance
I have a very slow query that I'm trying to tune. I think my
performance tuning is being complicated by the system's page cache.
If a run the query after the system has been busy with other tasks
for quite a long time then the query can take up to 8-10 minutes to
complete. If I then rerun the same query it will complete in a
couple of seconds.
Does anyone know how I can repeatedly run the same query in the
"worst case scenario" of no postgres data in the disk cache (e.g.,
clear the page cache or force it to be ignored)?
Thanks for any help.
Barry
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance Tuning and Disk Cache
@ 2007-03-18 13:18 hubert depesz lubaczewski <depesz@gmail.com>
parent: Barry Moore <barry.moore@genetics.utah.edu>
2 siblings, 0 replies; 5+ messages in thread
From: hubert depesz lubaczewski @ 2007-03-18 13:18 UTC (permalink / raw)
To: Barry Moore <barry.moore@genetics.utah.edu>; +Cc: pgsql-performance
On 3/18/07, Barry Moore <barry.moore@genetics.utah.edu> wrote:
> Does anyone know how I can repeatedly run the same query in the
> "worst case scenario" of no postgres data in the disk cache (e.g.,
> clear the page cache or force it to be ignored)?
try to disconnect from postgresql, reconnect, rerun the query.
if it doesn't help - you can try unmounting filesystem which contains
postgresql data, and remounting it again. of course with postgresql
shutdown.
depesz
--
http://www.depesz.com/ - nowy, lepszy depesz
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance Tuning and Disk Cache
@ 2007-03-18 15:26 David Boreham <david_list@boreham.org>
parent: Barry Moore <barry.moore@genetics.utah.edu>
2 siblings, 1 reply; 5+ messages in thread
From: David Boreham @ 2007-03-18 15:26 UTC (permalink / raw)
To: Barry Moore <barry.moore@genetics.utah.edu>; +Cc: pgsql-performance
Barry Moore wrote:
> I have a very slow query that I'm trying to tune. I think my
> performance tuning is being complicated by the system's page cache.
>
> If a run the query after the system has been busy with other tasks
> for quite a long time then the query can take up to 8-10 minutes to
> complete. If I then rerun the same query it will complete in a
> couple of seconds.
>
> Does anyone know how I can repeatedly run the same query in the
> "worst case scenario" of no postgres data in the disk cache (e.g.,
> clear the page cache or force it to be ignored)?
In my experience the only 100% reliable way to do this is to reboot the
machine.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance Tuning and Disk Cache
@ 2007-03-18 18:22 Rangarajan Vasudevan <ranga@asterdata.com>
parent: David Boreham <david_list@boreham.org>
0 siblings, 0 replies; 5+ messages in thread
From: Rangarajan Vasudevan @ 2007-03-18 18:22 UTC (permalink / raw)
To: pgsql-performance
If you are running on a Linux kernel, try /proc/sys/vm/drop_caches. I
believe the appropriate command is "echo 3 > /proc/sys/vm/drop_caches".
Since Postgres has its own cache of data, the above followed by a PG
restart should do what you are looking for.
Ranga
> Barry Moore wrote:
>
>> I have a very slow query that I'm trying to tune. I think my
>> performance tuning is being complicated by the system's page cache.
>>
>> If a run the query after the system has been busy with other tasks
>> for quite a long time then the query can take up to 8-10 minutes to
>> complete. If I then rerun the same query it will complete in a
>> couple of seconds.
>>
>> Does anyone know how I can repeatedly run the same query in the
>> "worst case scenario" of no postgres data in the disk cache (e.g.,
>> clear the page cache or force it to be ignored)?
>
> In my experience the only 100% reliable way to do this is to reboot the
> machine.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance Tuning and Disk Cache
@ 2007-03-18 18:49 Michael Stone <mstone+postgres@mathom.us>
parent: Barry Moore <barry.moore@genetics.utah.edu>
2 siblings, 0 replies; 5+ messages in thread
From: Michael Stone @ 2007-03-18 18:49 UTC (permalink / raw)
To: pgsql-performance
On Sun, Mar 18, 2007 at 06:45:34AM -0600, Barry Moore wrote:
>Does anyone know how I can repeatedly run the same query in the
>"worst case scenario" of no postgres data in the disk cache (e.g.,
>clear the page cache or force it to be ignored)?
Depends on your OS. On linux you can run:
echo 1 > /proc/sys/vm/drop_caches
Mike Stone
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-03-18 18:49 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-18 12:45 Performance Tuning and Disk Cache Barry Moore <barry.moore@genetics.utah.edu>
2007-03-18 13:18 ` hubert depesz lubaczewski <depesz@gmail.com>
2007-03-18 15:26 ` David Boreham <david_list@boreham.org>
2007-03-18 18:22 ` Rangarajan Vasudevan <ranga@asterdata.com>
2007-03-18 18:49 ` Michael Stone <mstone+postgres@mathom.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox