From: Scott Marlowe <scott.marlowe@gmail.com>
To: Oliver Johnson <oliverjjohnson@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: CPU utilization vs. IO wait, shared buffers?
Date: Thu, 30 Oct 2008 16:41:23 -0600
Message-ID: <dcc563d10810301541k1bb43ec2o95a87f128019b939@mail.gmail.com> (raw)
In-Reply-To: <db7814e50810301441h458aaf60k515548973a5a5297@mail.gmail.com>
References: <db7814e50810301441h458aaf60k515548973a5a5297@mail.gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scott.marlowe@gmail.com, oliverjjohnson@gmail.com
Subject: Re: CPU utilization vs. IO wait, shared buffers?
In-Reply-To: <dcc563d10810301541k1bb43ec2o95a87f128019b939@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox