DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=x3TYIy0OZvWGc/kctzzIXXZFda9qp7GTzDdjQmOlqYAITLxcse1oIBJUU15yke8fAd
	uqQwRrOOMDJPAaKeAu1of6nV6h7bCO90GGiXxzkJUR0F6EOM5e/qfsyq1pl1sRVurWE3
	0HYVsug7UU7muR451C5JKCIwN6jr0CLEWBsSc=
MIME-Version: 1.0
In-Reply-To: <20091112111442.14377f1sre6xx2o8@www.aceinnovative.com>
References: <20091112093301.11953uc4r7pgqzy8@www.aceinnovative.com>
	<dcc563d10911120714i5e24e1das20f13a0cdc348dee@mail.gmail.com>
	<20091112111442.14377f1sre6xx2o8@www.aceinnovative.com>
Date: Thu, 12 Nov 2009 09:33:24 -0700
Message-ID: <dcc563d10911120833l14906539uae90ef5ff87e8913@mail.gmail.com>
Subject: Re: Manual vacs 5x faster than autovacs?
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Wayne Beaver <wayne@acedsl.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Thu, Nov 12, 2009 at 9:14 AM, Wayne Beaver <wayne@acedsl.com> wrote:
> Hmm, looks like I've been myth-busted here.
>
> $ top | grep -E '29343|31924|29840|PID'; echo
> =A0PID USER =A0 =A0 =A0PR =A0NI =A0VIRT =A0RES =A0SHR S %CPU %MEM =A0 =A0=
TIME+ =A0COMMAND
> 29840 postgres =A015 =A0 0 2150m 203m 194m S =A0 =A00 =A02.5 =A0 0:00.59 =
postmaster
> 29343 postgres =A015 =A0 0 2137m 360m 356m S =A0 =A01 =A04.5 =A0 0:00.92 =
postmaster
> 31924 postgres =A015 =A0 0 2135m =A073m =A070m S =A0 =A01 =A00.9 =A0 0:00=
.44 postmaster
>
> So my claims of resource-usage appear incorrect.
>
> I'd seen autovacs running for hours and had mis-attributed this to growin=
g
> query times on those tables =A0- my thought was that "shrinking" the tabl=
es
> "more quickly" could make them "more-optimized", more often. Sounds like =
I
> could be chasing the wrong symptoms, though.

Now it is quite possible that a slow autovac is causing your queries
to run slower.  And it's that autovac isn't keeping up.  One of the
verious serious shortcomings of autovac in 8.1 (or was it 8.0?  I
think it was 8.1 as well) was that it only had one worker thread.  So,
if it has a moderate to high cost delay, then it might be able to keep
up with the job and your tables will become bloated.

The problem isn't that autovac is stealing too many resources, it's
that it's not stealing enough.

The first quick fix is 8.3 which has more efficient vacuuming code and
the ability to run > 1 thread (it defaults to 3) so you can still keep
it "detuned" to stay out of the way, but with enough threads it can
hopefully keep up.

Of course, eventually you reach the point where as the work load rises
the ability of autovac to keep up is lost, and then you need more IO
period.  Whether pgsql or any other database, running out of io
bandwidth is only really solvable by more IO bandwidth.

So, what does iostat -x 10

say about utilization?