Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dmNuL-0003m8-GY for pgsql-admin@arkaria.postgresql.org; Mon, 28 Aug 2017 17:33:05 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dmNuL-00069s-2s for pgsql-admin@arkaria.postgresql.org; Mon, 28 Aug 2017 17:33:05 +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 ) id 1dmNsW-0002YU-Cw for pgsql-admin@postgresql.org; Mon, 28 Aug 2017 17:31:12 +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 ) id 1dmNsS-0000zM-S1 for pgsql-admin@postgresql.org; Mon, 28 Aug 2017 17:31:11 +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 19037049; Mon, 28 Aug 2017 10:31:06 -0700 Subject: Re: Why is my table not autovacuuming? To: Don Seiler Cc: pgsql-admin References: <06d800f7-b1c7-91ab-0f1b-769f9a5860d1@joeconway.com> From: Joe Conway Openpgp: id=4EA5B4659ED59CC9538139A5332B7E68B699434A Message-ID: Date: Mon, 28 Aug 2017 10:31:05 -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: Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="UkRVsxK1xpGTgcVp7TxiMMdgfsOTPejhn" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --UkRVsxK1xpGTgcVp7TxiMMdgfsOTPejhn From: Joe Conway To: Don Seiler Cc: pgsql-admin Message-ID: Subject: Re: [ADMIN] Why is my table not autovacuuming? References: <06d800f7-b1c7-91ab-0f1b-769f9a5860d1@joeconway.com> In-Reply-To: Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Content-Language: en-US On 08/28/2017 09:49 AM, Don Seiler wrote: > I just find it very curious how pg_stat_all_tables.n_live_tups can be so > very very far off in this case unless stats tracking was somehow > disabled during a large bulk load or something. I'll keep an eye on thing= s. If you have a dev machine where you can run an experiment, try the following: (warning: sorry but this is quite long...) 8<-------------- createdb test psql test 8<-------------- create table test(id int); insert into test select g.i from generate_series(1,1000000) as g(i); -- wait a while for autovacuum to run select pg_sleep(90); \x select reltuples from pg_class where relname =3D 'test'; -[ RECORD 1 ]---- reltuples | 1e+06 select * from pg_stat_all_tables where relname =3D 'test'; -[ RECORD 1 ]-------+------------------------------ relid | 16385 schemaname | public relname | test seq_scan | 0 seq_tup_read | 0 idx_scan | idx_tup_fetch | n_tup_ins | 1000000 n_tup_upd | 0 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 1000000 n_dead_tup | 0 n_mod_since_analyze | 0 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | 2017-08-28 10:10:55.046554-07 vacuum_count | 0 autovacuum_count | 0 analyze_count | 0 autoanalyze_count | 1 8<-------------- -- quit postgres -- do an *immediate* shutdown -- can be done a few ways, but for example see -- https://www.postgresql.org/docs/9.6/static/app-pg-ctl.html -- restart postgres 8<-------------- psql test 8<-------------- \x select reltuples from pg_class where relname =3D 'test'; -[ RECORD 1 ]---- reltuples | 1e+06 select * from pg_stat_all_tables where relname =3D 'test'; -[ RECORD 1 ]-------+------- relid | 16385 schemaname | public relname | test seq_scan | 0 seq_tup_read | 0 idx_scan | idx_tup_fetch | n_tup_ins | 0 n_tup_upd | 0 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 0 n_dead_tup | 0 n_mod_since_analyze | 0 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | 0 autovacuum_count | 0 analyze_count | 0 autoanalyze_count | 0 update test set id =3D id + 10000000 where id < 50000; -- wait a while for autovacuum to run select pg_sleep(90); select * from pg_stat_all_tables where relname =3D 'test'; -[ RECORD 1 ]-------+-------- relid | 16385 schemaname | public relname | test seq_scan | 1 seq_tup_read | 1000000 idx_scan | idx_tup_fetch | n_tup_ins | 0 n_tup_upd | 49999 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 0 n_dead_tup | 49999 n_mod_since_analyze | 49999 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | 0 autovacuum_count | 0 analyze_count | 0 autoanalyze_count | 0 update test set id =3D id + 10000000 where id < 100000; -- wait a while for autovacuum to run select pg_sleep(90); -- will still not trigger autoanalyze because defaults are -- 50 + .1 x 1000000 =3D 100050 -- but we have only changed 100000 select * from pg_stat_all_tables where relname =3D 'test'; -[ RECORD 1 ]-------+-------- relid | 16385 schemaname | public relname | test seq_scan | 2 seq_tup_read | 2000000 idx_scan | idx_tup_fetch | n_tup_ins | 0 n_tup_upd | 99999 n_tup_del | 0 n_tup_hot_upd | 48 n_live_tup | 0 n_dead_tup | 99999 n_mod_since_analyze | 99999 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | 0 autovacuum_count | 0 analyze_count | 0 autoanalyze_count | 0 -- one more time should trigger autoanalyze -- however not enough to trigger autovac which will be -- 50 + .2 x 1000000 =3D 200050 update test set id =3D id + 10000000 where id < 150000; -- wait a while for autovacuum to run select pg_sleep(90); -- no we have changed 150000 select * from pg_stat_all_tables where relname =3D 'test'; -[ RECORD 1 ]-------+------------------------------ relid | 16385 schemaname | public relname | test seq_scan | 3 seq_tup_read | 3000000 idx_scan | idx_tup_fetch | n_tup_ins | 0 n_tup_upd | 149999 n_tup_del | 0 n_tup_hot_upd | 113 n_live_tup | 1000000 n_dead_tup | 149951 n_mod_since_analyze | 0 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | 2017-08-28 10:26:28.691209-07 vacuum_count | 0 autovacuum_count | 0 analyze_count | 0 autoanalyze_count | 1 -- one more time should trigger autovac -- 50 + .2 x 1000000 =3D 200050 update test set id =3D id + 10000000 where id < 250000; -- wait a while for autovacuum to run select pg_sleep(90); -- no we have changed 150000 select * from pg_stat_all_tables where relname =3D 'test'; -[ RECORD 1 ]-------+------------------------------ relid | 16385 schemaname | public relname | test seq_scan | 4 seq_tup_read | 4000000 idx_scan | idx_tup_fetch | n_tup_ins | 0 n_tup_upd | 249999 n_tup_del | 0 n_tup_hot_upd | 178 n_live_tup | 1000000 n_dead_tup | 0 n_mod_since_analyze | 100000 last_vacuum | last_autovacuum | 2017-08-28 10:29:37.164542-07 last_analyze | last_autoanalyze | 2017-08-28 10:26:28.691209-07 vacuum_count | 0 autovacuum_count | 1 analyze_count | 0 autoanalyze_count | 1 --=20 Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development --UkRVsxK1xpGTgcVp7TxiMMdgfsOTPejhn 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 iQIcBAEBCAAGBQJZpFNaAAoJEDfy90M199hlVNIP/0mAzOfoBBnk0fmTR6tK6vZl coHmypqIIHvUsj/RvQT2+RFQDKZ/mmBMxO5LNjoBG1yyWpjTKAXFaUDV/5cIZHie QfAdZQZLDWQEK84Nn0IOruW+G7EurA3RDuos6girVw/rSetDqAr5NOmHv8gMdfoJ SP9Gbj9+OSll15ViIC8JDpXvwhhsXKdNRyVdd4sx8fy1p2lXfKbmDHOpNgkcMT8A NzySAyY5FSD5QQ6aUMja6gYWCUmCOgawrr9f/1L0khGmH/OwMdRVLsdewpW14ciB itzCO2VjSyYKb6HFWQQt8iNpqRIwmNNwH5DQoX0nXaGQCQTmvLbj9REKSn4VoViL 0UVEtoclaFrWxvLvx4aGX7u2J4T9Qi0m4qdKTs2ifNX4V+qt75BHvc0CSMids0gQ KdieghGmmiZ+6h9Bc5jsolf9Mto6QjcWB/q/rX3LsrThdnJHEtnhwwvxOKWknmQK wCRCcxa+yhI0gB/7sxuVGJCJksoJnDH9i/FPoaXm65a8BSI6dmoL6XUpFaFVcbr7 0t4N/fWNjwSYm6A7mFWY19D5lfIlSkoMeBVUDZxcgo1bZPDCWzQnckye1/toUpll V/N/csp11igKKIOxPOmhInvQI4sj9CwTEgvLz2nQzQ1B4w1I6xruQZXNWM2epFfz KK5DNHtsaphK8UG1ZleS =eVPf -----END PGP SIGNATURE----- --UkRVsxK1xpGTgcVp7TxiMMdgfsOTPejhn--