On Thu, Oct 30, 2008 at 4:41 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> On Thu, Oct 30, 2008 at 3:41 PM, Oliver Johnson
> <oliverjjohnson(at)gmail(dot)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.
Thanks for the quick feedback. I struggled with autovacuum in a past
life and developed a favor for explicit table level vacuums. Also,
vacuum'ing did not jump out to me as a culprit originally as there is
no significant impact (or indicators of duress) during the early day
You and Alan have brought up some good points, though. I turned
autovacuum on and increased the checkpoint_segments. I will let it
run over night and see how things look.
>> 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.
In response to
pgsql-performance by date
|Next:||From: Jeff Frost||Date: 2008-10-31 00:18:06|
|Subject: Index usage problem on 8.3.3|
|Previous:||From: Scott Marlowe||Date: 2008-10-30 22:41:23|
|Subject: Re: CPU utilization vs. IO wait, shared buffers?|