Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from
 <pgsql-admin-owner+M46992=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
	id 1dmMS1-00077d-Rd
	for pgsql-admin@arkaria.postgresql.org; Mon, 28 Aug 2017 15:59:45 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.84_2)
	(envelope-from
 <pgsql-admin-owner+M46992=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
	id 1dmMS1-0004CZ-9C
	for pgsql-admin@arkaria.postgresql.org; Mon, 28 Aug 2017 15:59:45 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.84_2)
	(envelope-from <mail@joeconway.com>)
	id 1dmMS0-0004Bp-Hd
	for pgsql-admin@postgresql.org; Mon, 28 Aug 2017 15:59:44 +0000
Received: from 104-190-1-44.lightspeed.sndgca.sbcglobal.net ([104.190.1.44]
 helo=joeconway.com)
	by magus.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from <mail@joeconway.com>)
	id 1dmMRx-0007Lu-CR
	for pgsql-admin@postgresql.org; Mon, 28 Aug 2017 15:59:44 +0000
Received: from [192.168.1.106] (account jconway@joeconway.com [192.168.1.106]
 verified)
  by joeconway.com (CommuniGate Pro SMTP 6.1.4)
  with ESMTPSA id 19036638; Mon, 28 Aug 2017 08:59:38 -0700
Subject: Re: Why is my table not autovacuuming?
To: Don Seiler <don@seiler.us>, pgsql-admin <pgsql-admin@postgresql.org>
References: 
 <CAHJZqBAaXKi2+n4zNFYkjOCLY+ABpuC_BSMBKHfHfs0QqPNjbA@mail.gmail.com>
From: Joe Conway <mail@joeconway.com>
Openpgp: id=4EA5B4659ED59CC9538139A5332B7E68B699434A
Message-ID: <ea2474b3-82d8-3eb1-e781-a1ac54c7e11a@joeconway.com>
Date: Mon, 28 Aug 2017 08:59:37 -0700
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.2.1
MIME-Version: 1.0
In-Reply-To: 
 <CAHJZqBAaXKi2+n4zNFYkjOCLY+ABpuC_BSMBKHfHfs0QqPNjbA@mail.gmail.com>
Content-Type: multipart/signed; micalg=pgp-sha256;
 protocol="application/pgp-signature";
 boundary="QqCTsV2vml2aasdQo8AEvmflX2QEdCiKk"
List-Archive: <http://archives.postgresql.org/pgsql-admin>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-admin.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@postgresql.org>
List-Post: <mailto:pgsql-admin@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-admin>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
X-Mailing-List: pgsql-admin
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--QqCTsV2vml2aasdQo8AEvmflX2QEdCiKk
From: Joe Conway <mail@joeconway.com>
To: Don Seiler <don@seiler.us>, pgsql-admin <pgsql-admin@postgresql.org>
Message-ID: <ea2474b3-82d8-3eb1-e781-a1ac54c7e11a@joeconway.com>
Subject: Re: [ADMIN] Why is my table not autovacuuming?
References: <CAHJZqBAaXKi2+n4zNFYkjOCLY+ABpuC_BSMBKHfHfs0QqPNjbA@mail.gmail.com>
In-Reply-To: <CAHJZqBAaXKi2+n4zNFYkjOCLY+ABpuC_BSMBKHfHfs0QqPNjbA@mail.gmail.com>
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset=utf-8
Content-Language: en-US

On 08/28/2017 08:43 AM, Don Seiler wrote:
> PostgreSQL 9.2.18. I have a table with 71M rows (via select count). In
> pg_stat_activity, n_live_tups is 170K, n_dead_tups is slightly more than
> that (203K). The autovacuum parameter is on, the scale factor is 0.2
> (default) and threshold is 50 (default), autovacuum_max_workers is 3.
> There are no custom autovacuum thresholds set for this table. The
> last_autovacuum field is null.

<snip>

> Wondering why my autovacuum daemon appears to be acting lazily?

Autovac will kick in at (threshold + (scale_factor x 71M)), i.e. about
14.2 million dead rows. You are currently nowhere close to that.

That said, the default scale_factor is not good to such a large table.
Reduce it significantly. I often will find it necessary to use custom
values for large tables. One strategy I have used is scale_factor =3D 0
and threshold =3D <num_dead_rows_max_desired>.

Joe

--=20
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development


--QqCTsV2vml2aasdQo8AEvmflX2QEdCiKk
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2

iQIcBAEBCAAGBQJZpD3pAAoJEDfy90M199hlFhQP/ihqZi4LyyVvn20cqjiE6cK2
F/tJtpBemBZ/VZaVyWNpPToBIXt4vwdLkBaVHs95Fs+JTCbCCwZgJEApS5E8cWMZ
Po1yC8gW6MjhiUIyfTpH2ds4QjzWH/j8pjCd7NpCLxdic0TexnTGocBzTkm3ZSMx
e15qDbJyc45elTCPL0HYkef7+yM8lSE7Uv2aE1GyMBkoeMS4qSWErOSHsvzMIhW8
wq1LJ/+yoNWkkLHn2bhyh0fqXZxq2G6P5awcXZNQ3jDIB7pS7Xd+aeEFwJ6IYs6N
z1mYrOLUuZbffCbTrkF8Jzm9UDhsiMoOaqSDc25SaP9uUfcyxk3ssWfXA95+Rjj/
PH6KbNnkoSa0SkcRzIzEaflIrQmdm2vtET3eSrsh5fh/jQ4beoPogG/NT/RjlEaQ
oPNP6wetW6M5iYTshENt/5NBUqkDIFJt01NoCqZZ0HYOXDZlBMGGWpNCINlqsC/Q
3j/Tifrpa8lBMbN3WqAy9DwA9JgqG3TQ47FbEFBWtGp7IEghvFic47WANTx/oxyG
jXLyQJVLvCBz7M1mW5Somlu4KtXdInN+X6iECvjqFWK2vIeWTaXdLKYT3DPwSwcW
xcemIzo+vzINg3y+ecgxXDXZnl93HBXX1X7ykh8tAfwJyw6FhuEdyJmc9kYuRgch
lnUAumYlSygnD8dlK9KS
=Kj6Q
-----END PGP SIGNATURE-----

--QqCTsV2vml2aasdQo8AEvmflX2QEdCiKk--