DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=iabWIxtiKzn9qNiwm+OXOJYkZ3ItnToOqc8I69+AfeazZIpROAXV6AXw94U/JBLMCD
	lT6uuDUXFw/zZunSwnW10tg/wEEr6p9tQuUFWEO0l2f5KG5/efZZV9EZ7rlFOSWFIsia
	pIz7iw+smSVi5ryzhojBal5gF65uf/DyO2t3s=
Message-ID: <db7814e50810301638l211befej2137f2d27a732edd@mail.gmail.com>
Date: Thu, 30 Oct 2008 17:38:14 -0600
From: "Oliver Johnson" <oliverjjohnson@gmail.com>
To: "Scott Marlowe" <scott.marlowe@gmail.com>
Subject: Re: CPU utilization vs. IO wait, shared buffers?
Cc: pgsql-performance@postgresql.org
In-Reply-To: <dcc563d10810301541k1bb43ec2o95a87f128019b939@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <db7814e50810301441h458aaf60k515548973a5a5297@mail.gmail.com>
	<dcc563d10810301541k1bb43ec2o95a87f128019b939@mail.gmail.com>

On Thu, Oct 30, 2008 at 4:41 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> 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.

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