Re: update query taking too long

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux