From: Chris Browne <cbbrowne@acm.org>
To: pgsql-performance@postgresql.org
Subject: Re: Investigating IO Saturation
Date: Tue, 24 Jan 2006 14:43:59 -0500
Message-ID: <60oe21o1hc.fsf@dba2.int.libertyrms.com> (raw)
References: <43D65736.9020905@ca.afilias.info>
<19963.1138122458@sss.pgh.pa.us>
tgl@sss.pgh.pa.us (Tom Lane) writes:
> Brad Nicholson <bnichols@ca.afilias.info> writes:
>> I'm investigating a potential IO issue. We're running 7.4 on AIX 5.1.
>> During periods of high activity (reads, writes, and vacuums), we are
>> seeing iostat reporting 100% disk usage. I have a feeling that the
>> iostat numbers are misleading. I can make iostat usage jump from less
>> than 10% to greater than 95% by running a single vacuum against a
>> moderate sized table (no noticeable change in the other activity).
>
> That's not particularly surprising, and I see no reason to think that
> iostat is lying to you.
>
> More recent versions of PG include parameters that you can use to
> "throttle" vacuum's I/O demand ... but unthrottled, it's definitely
> an I/O hog.
I believe it's 7.4 where the cost-based vacuum parameters entered in,
so that would, in principle, already be an option.
[rummaging around...]
Hmm.... There was a patch for 7.4, but it's only "standard" as of
8.0...
> The vmstat numbers suggest that vacuum is not completely killing you,
> but you could probably get some improvement in foreground query
> performance by throttling it back. There are other good reasons to
> consider an update, anyway.
I'd have reservations about "throttling it back" because that would
lead to VACUUMs running, and holding transactions open, for 6 hours
instead of 2.
That is consistent with benchmarking; there was a report of the
default policy cutting I/O load by ~80% at the cost of vacuums taking
3x as long to complete.
The "real" answer is to move to 8.x, where VACUUM doesn't chew up
shared memory cache as it does in 7.4 and earlier.
But in the interim, we need to make sure we tilt over the right
windmills, or something of the sort :-).
--
output = reverse("gro.gultn" "@" "enworbbc")
http://www3.sympatico.ca/cbbrowne/linuxxian.html
"Women and cats will do as they please, and men and dogs should relax
and get used to the idea." -- Robert A. Heinlein
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: cbbrowne@acm.org
Subject: Re: Investigating IO Saturation
In-Reply-To: <60oe21o1hc.fsf@dba2.int.libertyrms.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