Обсуждение: Help: how to speed up query after db server reboot

Hi:

Looks like after postgres db server reboot, first query is very slow (10+mins). After the system cache built, query is pretty fast.
Now the question is how to speed up the first query slow issue?

Any pointers?

Thanks
wei

On Wed, Sep 2, 2009 at 00:01, Wei Yan<weiyan1@gmail.com> wrote:
> Hi:
>
> Looks like after postgres db server reboot, first query is very slow
> (10+mins). After the system cache built, query is pretty fast.
> Now the question is how to speed up the first query slow issue?
>
> Any pointers?

Schedule a run of a couple of representative queries right as the
database has started? That should pre-populate the cache before your
users get there, hopefully.


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Magnus Hagander <magnus@hagander.net> writes:
> On Wed, Sep 2, 2009 at 00:01, Wei Yan<weiyan1@gmail.com> wrote:
>> Looks like after postgres db server reboot, first query is very slow
>> (10+mins). After the system cache built, query is pretty fast.
>> Now the question is how to speed up the first query slow issue?

> Schedule a run of a couple of representative queries right as the
> database has started? That should pre-populate the cache before your
> users get there, hopefully.

I wonder if VACUUMing his key tables would be a good answer.  I bet that
a lot of the problem is swapping in indexes in a slow random-access
fashion.  In recent-model Postgres, VACUUM will do a sequential scan of
the indexes (at least for btree) which should be a much more efficient
way of bringing them into kernel cache.

            regards, tom lane