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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

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