Re: CPU utilization vs. IO wait, shared buffers?

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
vacuums.

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.

Thanks again.

>
>> 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.
>