Date: Thu, 2 Oct 2014 20:35:26 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Robert Haas <robertmhaas@gmail.com>,
 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>,
 Amit Kapila <amit.kapila16@gmail.com>,
 Haribabu Kommi <kommi.haribabu@gmail.com>,
 "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: Per table autovacuum vacuum cost limit behaviour
 strange
Message-ID: <20141003003526.GE28859@tamriel.snowman.net>
References: 
 <CAA4eK1KDAUP=g59ku_0ekDoRjaGfEamyadQn5rJVt3j5oErLXg@mail.gmail.com>
 <53686F22.8030300@catalyst.net.nz>
 <20140826161911.GE6343@eldon.alvh.no-ip.org>
 <CA+Tgmoav37msTP0pVsrEYz4LBYU6SETMZGYTgPo5AEOC0yCMbA@mail.gmail.com>
 <20140930221644.GR5311@eldon.alvh.no-ip.org>
 <20141002135412.GX5311@eldon.alvh.no-ip.org>
 <CA+TgmobQokthgWmv1C9vjvpz=K9koovy6O+nWZ9rV8gwC8ioRw@mail.gmail.com>
 <20141002150336.GO28859@tamriel.snowman.net>
 <20141002153705.GY5311@eldon.alvh.no-ip.org>
 <20141002215950.GB5311@eldon.alvh.no-ip.org>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol="application/pgp-signature"; boundary="jxJZfy9MYHgKPwT/"
Content-Disposition: inline
In-Reply-To: <20141002215950.GB5311@eldon.alvh.no-ip.org>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org


--jxJZfy9MYHgKPwT/
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

* Alvaro Herrera (alvherre@2ndquadrant.com) wrote:
> Alvaro Herrera wrote:
> > Basically, if you are on 9.3.5 or earlier any per-table options for
> > autovacuum cost delay will misbehave (meaning: any such table will be
> > processed with settings flattened according to balancing of the standard
> > options, _not_ the configured ones).  If you are on 9.3.6 or newer they
> > will behave as described in the docs.
>=20
> Another thing to note is that if you have configured a table to have
> cost_limit *less* than the default (say 150 instead of the default 200),
> the balance system will again break that and process the table at 200
> instead; in other words, the balancing system has completely broken the
> ability to tweak the cost system for individual tables in autovacuum.

That's certainly pretty ugly.

> With the v5 patch, the example tables above will be vacuumed at exactly
> 5000 and 150 instead.  The more complex patch I produced earlier would
> have them vacuumed at something like 4900 and 100 instead, so you
> wouldn't exceed the total of 5000.  I think there is some value to that
> idea, but it seems the complexity of managing this is too high.

Agreed.

> I am rather surprised that nobody has reported this problem before.  I
> am now of the mind that this is clearly a bug that should be fixed all
> the way back.

I'm coming around to that also, however, should we worry about users who
set per-table settings and then simply forgot about them?  I suppose
that won't matter too much unless the table is really active, and if it
is, they've probably already set it to zero.

	Thanks,

		Stephen

--jxJZfy9MYHgKPwT/
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBAgAGBQJULe9OAAoJEO1sijiDR2RVqoIP/jpBnv7jrja2+eWnjbJp7pTA
jOuiEjzGLUXjs5irUoMEJvThUgoishthw9qslgXVZotQ1P8uNz6xiPQVyTxtNBb6
vYQGcw3pC71bKmwualabUb1jPe3/bazfw3VHMQzNl+qk1hqOsGYqeFDbt+1leoUG
SZmKUg0jkqmJ0GxJI1KRsLWTsgw/652qX0IqhBxBHHuEPiYnTHFIBjZMoRpgu8Tu
0v4N2TjqnUddoxzmPJpvp6c+N7ASc3Ko+Db7Jx+ipEST9YDjwWKMPXX/mBpTLUnG
u70ErvbLHM9eVKrC98UeYbR3pe7T83TIN45VIjbsPgWfy1tRaj03iRGorAH57oJ8
t4xxJh532qaQulgtOS29/vxEvXQ47VPAtl6CgcEfokrmNxigQnJNR2BvbPrwKIdx
jowAbaPEHGuQNDYAZcZ2VDF9Hp4lrGKeydaVtaRSnx22Ymm+0uK3/onBFyzHId6Q
fzK+NaDFBD/qTHxpHVGxA7WuWL5Auf5rPh9i0Nf9zQd8RCCb4ZYBYPfVj5CyLTZK
MDGgMkRjakSYK+8XeIPNGDiSHC/EzPkkN6uAj6S3uHpOYNAJp8goQK0uG6yJdUjA
xt9ovS8MQI4s+U1XI1nGR2R/Q5gs2dovbB/jWXLNOM4YtR8Xl02+e+SfZ7qzJ1Uc
tch1oREghNq2xoahk/K8
=wfZc
-----END PGP SIGNATURE-----

--jxJZfy9MYHgKPwT/--