Re: 8.2 Autovacuum BUG ? - Mailing list pgsql-performance

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