Hello,
Sorry. I should have put some more details in the email.
I have got a situation where in i see the production system is loaded with
the checkpoints and at-least 1000+ buffers are being written for every
checkpoint.
Checkpoint occurs every 3 to 4 minutes and every checkpoint takes 150
seconds minimum to write off the buffers and 150+ seconds for checkpoint
syncing. A warning messages can be seen in the dbserver logs "checkpoint
occuring too frequently".
I had a look at the pg_stat_bgwriter as well. Below is what i see.
select * from pg_stat_bgwriter;
checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean |
maxwritten_clean | buffers_backend | buffers_alloc
-------------------+-----------------+--------------------+---------------+------------------+-----------------+---------------------------------------------------------------
9785 | 36649 | 493002109 |
282600872 | 1276056 | 382124461 | 7417638175
(1 row)
I am thinking of increasing the checkpoint_segments.
Below are our current settings -
checkpoint_segments = 8
checkpoint_timeout = 5 mins
checkpoint_completion_target = 0.5
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 100
bgwriter_lru_multiplier = 2
Looking forward for suggestions.
Thanks
VB
On Thu, Sep 29, 2011 at 12:40 PM, Venkat Balaji <[email protected]>wrote:
> Hello Everyone,
>
> We are experience a huge drop in performance for one of our production
> servers.
>
> I suspect this is because of high IO due to frequent Checkpoints. Attached
> is the excel sheet with checkpoint information we tracked.
>
> Below is the configuration we have
>
> checkpoint_segments = default
> checkpoint_timeout = default
>
> I suspect archive data generation to be around 250 MB.
>
> Please share your thoughts !
>
> Thanks
> VB
>
>
>
>
