Re: update query taking too long

Jean-David Beyer wrote:
> I have not used postgreSQL since I tried it once in about 1998 (when I found
> it unsatisfactory, but much has changed since then), but I am going to try
> it again. What would be a good checkpointing interval? I would guess 26
> seconds is too often. What considerations go into picking a checkpointing
> interval?

Welcome back.

Longer is better when the system is running. But on recovery, longer
checkpoint interval means a longer wait until the database is up again.
Longer checkpoint interval also means that more WAL needs to be kept
around, but that's not usually a concern on normal server hardware with
plenty of disk space.

> WAL_BUFFERS         The default is 8.

Increasing this can increase the performance of bulk load operations but
it doesn't make much difference otherwise.

> CHECKPOINT_SEGMENTS The default is 3. This would have been too low for the
>                     O.P. Would it make sense to start with a higher value
>                     or is this a good value and just not appropriate for
>                     the O.P.? Should CHECKPOINT_SEGMENTS be raised until
>                     the checkpointing is about half CHECKPOINT_TIMEOUT,
>                     e.g., 150 seconds while the dbms is running typical
>                     work?
> CHECKPOINT_TIMEOUT  The default is 300 seconds.

You have to decide if you want to use checkpoint_timeout or
checkpoint_segments as the primary means of controlling your checkpoint
interval. checkpoint_timeout is easier to understand and tune, so I
would suggest using that. Depending on how long recovery times you can
live with, set it to something like 15 minutes - 60 minutes. Then set
checkpoint_segments to a high value; it's purpose in this scheme is
basically to just protect you from running out of disk space on the
filesystem WAL is located in.

Note that unlike on DB2, the size of your transactions isn't limited by
the amount of transaction log you keep around; this is all about
performance.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com