8 checkpoint segments is very small, try 50 2011/10/4, 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 >> >> >> >> > -- ------------ pasman -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
