[PERFORM] Performance Tuning and Disk Cache
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Performance Tuning and Disk Cache
On 3/18/07, Barry Moore [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Performance Tuning and Disk Cache
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
Re: [PERFORM] Performance Tuning and Disk Cache
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Performance Tuning and Disk Cache
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
