Re: : PG9.0 - Checkpoint tuning and pg_stat_bgwriter

[Venkat Balaji <venkat.balaji@xxxxxxxx>]

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 <venkat.balaji@xxxxxxxx> 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