Re: 8.2 Autovacuum BUG ?

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