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

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

From: "Oliver Johnson" <oliverjjohnson(at)gmail(dot)com>
To: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: CPU utilization vs. IO wait, shared buffers?
Date: 2008-10-30 23:38:14
Message-ID: db7814e50810301638l211befej2137f2d27a732edd@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.
>

In response to

pgsql-performance by date

Next:From: Jeff FrostDate: 2008-10-31 00:18:06
Subject: Index usage problem on 8.3.3
Previous:From: Scott MarloweDate: 2008-10-30 22:41:23
Subject: Re: CPU utilization vs. IO wait, shared buffers?

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