Re: simple update queries take a long time - postgres 8.3.1

On Tue, 1 Apr 2008, mark wrote:

>> current settings all default
>> #checkpoint_segments = 3
>> #checkpoint_timeout = 5min
>> #checkpoint_completion_target = 0.5
>> #checkpoint_warning = 30s
>
> this is what I have on pg_stat_bgwriter ; how much should I increase
> checkpoint_segment & checkpoint_completion_target to? thanks a lot!
>
> postgres=# select * from pg_stat_bgwriter;
> checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean |
> 42 | 1051 | 8035125 | 21954 |
> maxwritten_clean | buffers_backend | buffers_alloc
> 42 | 241519 | 10394696

That means that 96% of the time, you are hitting a checkpoint because of
activity in less than 5 minutes after the previous one. And 97% of the
dirty buffers written out are being done by the checkpoint process. Your
system is spending its entire life doing checkpoint work and it's no
wonder response time is slow quite regularly.

Increasing checkpoint_segments uses up more disk space for the WAL files
and increases the amount of time it will take to recover from a crash.
If neither of those two things matter to you, you can easily bump that
parameter up to 100 or more. I'd suggest starting with an order of
magnitude change and increase from 3 to 30. You have to restart the
server to make that change stick. It's impossible to predict how much
that will change your situation, to know if that's enough or you really
need to increase it further.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD