From: | pasman pasmański <pasman(dot)p(at)gmail(dot)com> |
---|---|
To: | PGSQL Performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: : PG9.0 - Checkpoint tuning and pg_stat_bgwriter |
Date: | 2011-10-04 14:15:58 |
Message-ID: | CAOWY8=Z0AwiUns7B9Sknd+fnaUJ8Q0gX76egzBPOBn2CszhnbA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
8 checkpoint segments is very small, try 50
2011/10/4, Venkat Balaji <venkat(dot)balaji(at)verse(dot)in>:
> 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(dot)balaji(at)verse(dot)in>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
>>
>>
>>
>>
>
--
------------
pasman
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2011-10-04 14:36:51 | Re: Window functions and index usage |
Previous Message | Anssi Kääriäinen | 2011-10-04 14:06:45 | Re: Window functions and index usage |