Skip site navigation (1) Skip section navigation (2)

Re: Checkpoint spikes

From: Richard Neill <rn214(at)hermes(dot)cam(dot)ac(dot)uk>
To: Greg Smith <greg(at)2ndquadrant(dot)com>
Cc: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>, Richard Neill <rn214(at)cam(dot)ac(dot)uk>, PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Checkpoint spikes
Date: 2009-12-08 10:07:28
Message-ID: 4B1E2560.4080403@hermes.cam.ac.uk (view raw or flat)
Thread:
Lists: pgsql-performance
Dear All,

Thanks for all your help so far. This page was particularly helpful:
http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm
(does the advice for 8.3 apply unchanged to 8.4?)

I'm still hitting issues with this though: sync is taking 7-10 seconds
and I need to get it down to nearer 3.

We're running a lightly-loaded system which has a realtime response 
requirement of 0.5 seconds all the time (with a few seconds permissible 
occasionally, but never exceeding 10).

So far, I've set checkpoint_segments to 128, timeout to 10min, and 
completion_target to 0.8. This helps, but not as much as I'd hoped.

But I haven't touched any of the other WAL or BG Writer settings.

Where should I look next?
   Should I be looking at the BG Writer settings,
   or should I look at the Linux VM configuration?
     (eg changing /proc/sys/vm/dirty_background_ratio from 5 to 1)

   Or would it be most useful to try to move the WAL to a different disk?


Latest messages:

# tail -f /var/log/postgresql/postgresql-8.4-main.log | grep check

2009-12-08 09:12:00 GMT LOG:  checkpoint starting: time
2009-12-08 09:20:09 GMT LOG:  checkpoint complete: wrote 51151 buffers 
(8.9%); 0 transaction log file(s) added, 0 removed, 23 recycled; 
write=479.669 s, sync=9.852 s, total=489.553 s

2009-12-08 09:22:00 GMT LOG:  checkpoint starting: time
2009-12-08 09:30:07 GMT LOG:  checkpoint complete: wrote 45772 buffers 
(7.9%); 0 transaction log file(s) added, 0 removed, 24 recycled; 
write=479.706 s, sync=7.337 s, total=487.120 s

2009-12-08 09:32:00 GMT LOG:  checkpoint starting: time
2009-12-08 09:40:09 GMT LOG:  checkpoint complete: wrote 47043 buffers 
(8.2%); 0 transaction log file(s) added, 0 removed, 22 recycled; 
write=479.744 s, sync=9.300 s, total=489.122 s

2009-12-08 09:42:00 GMT LOG:  checkpoint starting: time
2009-12-08 09:50:07 GMT LOG:  checkpoint complete: wrote 48210 buffers 
(8.4%); 0 transaction log file(s) added, 0 removed, 23 recycled; 
write=479.689 s, sync=7.707 s, total=487.416 s


Thanks a lot,

Richard



In response to

pgsql-performance by date

Next:From: Richard NeillDate: 2009-12-08 10:08:02
Subject: Re: Checkpoint spikes
Previous:From: Schmitz, DavidDate: 2009-12-08 09:59:51
Subject: Re: performance penalty between Postgresql 8.3.8 and 8.4.1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group