From: Chris Browne <cbbrowne@acm.org>
Subject: Re: Investigating IO Saturation
Date: Tue, 24 Jan 2006 14:43:59 -0500
Organization: cbbrowne Computing Inc
Lines: 48
Message-ID: <60oe21o1hc.fsf@dba2.int.libertyrms.com>
References: <43D65736.9020905@ca.afilias.info>
 <19963.1138122458@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4.18 (linux)
Cancel-Lock: sha1:HREWQX3IVisBXBNXOqR/J5Bh9gU=
To: pgsql-performance@postgresql.org

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