Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eeJZc-0005Ry-TD for pgsql-performance@arkaria.postgresql.org; Wed, 24 Jan 2018 11:50:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eeJZc-00034R-Dq for pgsql-performance@arkaria.postgresql.org; Wed, 24 Jan 2018 11:50:36 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eeJZc-00034B-3Y for pgsql-performance@lists.postgresql.org; Wed, 24 Jan 2018 11:50:36 +0000 Received: from n3.nabble.com ([162.255.23.22]) by makus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1eeJZZ-0001qj-D6 for pgsql-performance@postgresql.org; Wed, 24 Jan 2018 11:50:34 +0000 Received: from n3.nabble.com (localhost [127.0.0.1]) by n3.nabble.com (Postfix) with ESMTP id 12E97ACDF47B for ; Wed, 24 Jan 2018 04:50:33 -0700 (MST) Date: Wed, 24 Jan 2018 04:50:33 -0700 (MST) From: pavan95 To: pgsql-performance@postgresql.org Message-ID: <1516794633075-0.post@n3.nabble.com> In-Reply-To: References: <1516616499481-0.post@n3.nabble.com> <97fd02a3-c07c-6d27-6221-b50c2c8c13cc@a-kretschmer.de> <1516708261537-0.post@n3.nabble.com> <1516776879945-0.post@n3.nabble.com> Subject: Re: 8.2 Autovacuum BUG ? MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Hello all, One more interesting observation made by me. I have ran the below query(s) on production: SELECT relname, age(relfrozenxid) as xid_age, pg_size_pretty(pg_table_size(oid)) as table_size FROM pg_class WHERE relkind = 'r' and pg_table_size(oid) > 1073741824 ORDER BY age(relfrozenxid) DESC ; relname | xid_age | table_size ------------------------------------------------------------+---------+------------ *hxxxxxxxxxx* | 7798262 | 3245 MB hrxxxxxxxxx | 7797554 | 4917 MB irxxxxxxxxxx | 7796771 | 2841 MB hr_xxxxxxxxxxxxxxxx | 7744262 | 4778 MB reimbxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | 6767712 | 1110 MB show autovacuum_freeze_max_age; autovacuum_freeze_max_age --------------------------- 200000000 (1 row) SELECT txid_current();---AT 15:09PM on 24th Jan 2018 txid_current -------------- 8204011 (1 row) Then I tried to perform *VACUUM FREEZE* on the *hxxxxxxxxxx*. To my wonder it had generated 107 archive log files, which is nearly 1.67GB. The verbose information of above *VACUUM FREEZE* is shown below: *x_db*=#VACUUM (FREEZE,VERBOSE) hxxxxxxxxxxx; INFO: vacuuming "public.hxxxxxxxxxxx" INFO: scanned index "hxxxxxxxxxxx_pkey" to remove 10984 row versions DETAIL: CPU 0.00s/0.01u sec elapsed 0.04 sec. INFO: scanned index "hxxxxxxxxxxx_x_email_from" to remove 10984 row versions DETAIL: CPU 0.00s/0.04u sec elapsed 0.12 sec. INFO: scanned index "hxxxxxxxxxxx_x_mobile" to remove 10984 row versions DETAIL: CPU 0.00s/0.03u sec elapsed 0.09 sec. INFO: scanned index "hxxxxxxxxxxx_x_pan" to remove 10984 row versions DETAIL: CPU 0.00s/0.02u sec elapsed 0.08 sec. INFO: scanned index "hxxxxxxxxxxx_x_ssn" to remove 10984 row versions DETAIL: CPU 0.00s/0.01u sec elapsed 0.04 sec. INFO: scanned index "hxxxxxxxxxxx_x_email_from_index" to remove 10984 row versions DETAIL: CPU 0.01s/0.03u sec elapsed 0.12 sec. INFO: scanned index "hxxxxxxxxxxx_x_vendor_id_index" to remove 10984 row versions DETAIL: CPU 0.00s/0.01u sec elapsed 0.04 sec. INFO: "hxxxxxxxxxxx": removed 10984 row versions in 3419 pages DETAIL: CPU 0.02s/0.02u sec elapsed 0.18 sec. INFO: index "hxxxxxxxxxxx_pkey" now contains 71243 row versions in 208 pages DETAIL: 2160 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "hxxxxxxxxxxx_x_email_from" now contains 71243 row versions in 536 pages DETAIL: 9386 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "hxxxxxxxxxxx_x_mobile" now contains 71243 row versions in 389 pages DETAIL: 8686 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "hxxxxxxxxxxx_x_pan" now contains 71243 row versions in 261 pages DETAIL: 8979 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "hxxxxxxxxxxx_x_ssn" now contains 71243 row versions in 257 pages DETAIL: 8979 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "hxxxxxxxxxxx_x_email_from_index" now contains 71243 row versions in 536 pages DETAIL: 8979 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "hxxxxxxxxxxx_x_vendor_id_index" now contains 71243 row versions in 257 pages DETAIL: 8979 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "hxxxxxxxxxxx": found 2597 removable, 71243 nonremovable row versions in 7202 out of 7202 pages DETAIL: 0 dead row versions cannot be removed yet. There were 10144 unused item pointers. 0 pages are entirely empty. CPU 0.21s/0.66u sec elapsed 3.21 sec. INFO: vacuuming "pg_toast.pg_toast_401161" ^CCancel request sent ERROR: canceling statement due to user request Note: Cancelled because it got struck over there and it seems to be overhead to DB in business hours. Now from this experiment is there something to suspect if I do VACUUM FREEZE on the database will it reduce my HUGE ARCHIVE LOG GENERATION? Please help. Thanks in Advance. Regards, Pavan -- Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html