Thread: query slow only after reboot

Hi:

Our queries are extremely slow only after db server reboot, not after restart postgres db only. The difference is about 10 mins vs. 8 secs. Not acceptable. I have browsed around , set the postgres db parameters as suggested. Still the same.

Any suggestion on how to tackle the problem?

Thanks
wei

Wei Yan wrote:
> Hi:
>
> Our queries are extremely slow only after db server reboot, not after
> restart postgres db only. The difference is about 10 mins vs. 8 secs.
> Not acceptable. I have browsed around , set the postgres db parameters
> as suggested. Still the same.
>
> Any suggestion on how to tackle the problem?
>
What OS, amount of RAM, DB size...?

If the queries get progressively faster as you use the system then slow
again after a reboot, my initial guess would be that you are getting
more and more disk-cache hits the longer you use the system.

Cheers,
Steve

You can try using the pg_stat_io table and related stat tables to figure out which ones are responsible for all this I/O on startup.
Then, for the big offenders issue a select count(*) on those right at the start, to force the OS to read the pages into memory.  This won’t be effective if these are too much larger than RAM.  Also, running queries that do large index scans on tables/indexes heavily used can help.
Its a brute force preload, but my guess is your woes are caused by random I/O on tables and indexes that you can attempt to pre-cache by forcing sequential access on some of that data.  There are many other ways to read heavily accessed tables or indexes into OS memory on a freshly restarted system — even just reading the raw files with something like grep.




On 2/9/09 5:21 PM, "Wei Yan" <weiyan1@gmail.com> wrote:

Hi:

Our queries are extremely slow only after db server reboot, not after restart postgres db only. The difference is about 10 mins vs. 8 secs. Not acceptable. I have browsed around , set the postgres db parameters as suggested. Still the same.

Any suggestion on how to tackle the problem?

Thanks
wei

>>> Wei Yan <weiyan1@gmail.com> wrote:
> Our queries are extremely slow only after db server reboot

> Any suggestion on how to tackle the problem?

Others have addressed how you can try to prime your cache after a
reboot, so I'll mention the 800 pound gorilla sitting in the corner.
I suggest you don't reboot as often, and when you have to do so,
schedule it off-hours, when there is time for the cache to get
populated with minimal pain.

We basically don't reboot our database servers except when we upgrade
the kernel.  Even when we were running Windows as the OS on some
servers we were able to tune it so that reboots could be relatively
infrequent by aggressively disabling services and daemons down to the
bare minimum.

-Kevin