DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=QAuz7IdVoApXjrDHb+eZkjNb23C+/awiZMFvDYYdEm4mpKzzb6RlzHR9JNwR3lyeCl01WnaQw6pMX0tDy+HvBc01QZ/PAKOzeeHmgBXmXC0wWf8YY3lKsShYr2v17zKoIywoC1SNzYVF2jQyTuuWWhZC0ftM4pnbHy4YxlnccE8=
Message-ID: <dcc563d10710160843q3b67e618ua302eb9f1d09b650@mail.gmail.com>
Date: Tue, 16 Oct 2007 10:43:34 -0500
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: "=?ISO-8859-1?Q?St=E9phane_Schildknecht?="
	<stephane.schildknecht@postgresqlfr.org>
Subject: Re: Vacuum goes worse
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
In-Reply-To: <4714D817.30701@postgresqlfr.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <47147AD8.9010704@postgresqlfr.org>
	<15640.1192544768@sss.pgh.pa.us> <4714D817.30701@postgresqlfr.org>

On 10/16/07, St=E9phane Schildknecht
<stephane.schildknecht@postgresqlfr.org> wrote:
> Tom Lane a =E9crit :
> > =3D?ISO-8859-1?Q?St=3DE9phane_Schildknecht?=3D <stephane.schildknecht@p=
ostgresqlfr.org> writes:
> >
> >> For some times, we have a vacuuming process on a specific table that
> >> goes slower and slower. In fact, it took some 3 minutes a month ago, a=
nd
> >> now it take almost 20 minutes. But, if one day it take so many time, i=
t
> >> is possible that on the day after it will only take 4 minutes...
> >>
> >
> >
> >> I know the table in concern had 450000 tuples two months ago and now h=
as
> >> more than 700000 tuples in it.
> >>
> >
> > The real question is how often do rows get updated?  I suspect you
> > probably need to vacuum this table more than once a day.
> >
> >
>
> To be honest, I suspect it too. But, I have been told by people using
> that database they can't do vacuum more frequently than once in a day as
> it increases the time to achieve concurrent operations.
> That's also why they don't want to hear about autovacuum.

Sounds like somebody there is operating on the belief that vacuums
always cost the same amount i/o wise.  With the vacuum_cost_delay
setting Tim mentioned this is not true.  Their concern shouldn't be
with how you accomplish your job, but with you meeting certain
performance criteria, and with vacuum cost delay, it is quite possible
to vacuum midday with affecting the db too much.

> And finally that's why I'm looking for everything I can monitor to
> obtain information to convince them they're wrong and I'm right ;-)

Good luck with that.  I still have a boss who thinks "vacuum's not
fast enough".  His last experience with pgsql was in the 7.2 days.
Generally he's a pretty smart guy, but he's convinced himself that
PostgreSQL 8.3 and 7.2 are pretty much the same beasts.

> That's also why I am so disappointed vacuum doesn't give me these 4
> hints lines.

What kind of error, or output, does it give you at the end?  Any hint
as to why they're missing?