On Tue, May 26, 2009 at 5:57 PM, zxo102 ouyang <zxo102@xxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
