On Tue, May 26, 2009 at 5:57 PM, zxo102 ouyang <zxo102@gmail.com> wrote:
> Hi all,
> I have a table which has more than 10millions records in pgsql which is
> running on window 2003. During night, nobody search the database.
> In morning, when people start to the application, it will take more than 30
> seconds to get database back. After several times of same searching, the
> results can be returned in 8 seconds.
> Is this related to some kinds of process priority setting in window
> 2003? If so, how do I set the pgsql processes in highest priority?
There are two types of "look how we've optimized our OS and now your
database server runs like crap" common scenarios. The most common is
that the one where the OS has simply stopped caching your database
files because other things are happening. Not much you can do about
that one. The other is the VM in your OS slowly swapping out all of
pgsql's shared_buffers because they appear idle, making even more room
to cache files on the machine for processes that are not pgsql. Which
is why the other poster has asked what other things this server does.
If the OS is busy swapping out idle chunks of memory for more cache,
you can stop it in linux by adjusting the vm.swappiness setting. No
clue how to do that in windows, but googling on swappiness and windows
might help.
It's a good idea to put a database on its own server for these
reasons. Also, in the morning, have a cron job crank up that does
"select * from mybigtable" for each big table to load it into cache.
This is possibly made worse if you've lowered your random_page_cost to
near 1, and have effective_cache_size cranked up. Those settings are
likely right for your setup, but first thing in the morning they're
wrong. Actual random page cost really is 10 or more, and the
effective cache size means nothing because the kernel cache is full of
stuff that's NOT pgsql files. In which case the tendency towards
index access and not seq scan is really gonna cost you. Hence the
need for the select * from bigtable queries to prime the pump.