On Thu, Oct 30, 2008 at 3:41 PM, Oliver Johnson
<oliverjjohnson@gmail.com> wrote:
> Another thing to note, we have VACUUM ANALYZE running on an hourly
> interval and the switch from CPU to IO wait appears to always coincide
> with a vacuum.
Why are you not using autovacuum with appropriate wait parameters to
keep it out of your way? Autovacuum tends to make pretty good
decisions and you can adjust the aggressiveness with which it kicks in
if you need to.
> What might cause this shift?
>
> I have tried adjusting buffer_cache from 512 MB to 1024 MB, but this
> did not appear to have an impact.
Do you mean shared_buffers? It may well be that larger shared_buffers
aren't going to help if you're dealing with a largely random
transactional load. that said, 1G shared_buffers is not that big
nowadays. I'm assuming by your testing methods you're on a real db
server with several dozen gigs of ram...
> I also tried upping the work_mem from 1MB to 10MB, and this did not
> appear to have an impact either.
Look into upping your checkpoint_segments (64 or so is reasonable for
a large production server) and possibly increasing your
checkpoint_completion_target to something closer to 1.0 (0.7 to 0.8)
and see if that helps.