From: pavan95 <pavan.postgresdba@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: 8.2 Autovacuum BUG ?
Date: Wed, 24 Jan 2018 04:50:33 -0700 (MST)
Message-ID: <1516794633075-0.post@n3.nabble.com> (raw)
In-Reply-To: <CAGTBQpa-mTQ9qA18xVimrgTmrXZnFAnqKpzHYwCNtc=GEEd+5w@mail.gmail.com>
References: <1516616499481-0.post@n3.nabble.com>
<97fd02a3-c07c-6d27-6221-b50c2c8c13cc@a-kretschmer.de>
<1516708261537-0.post@n3.nabble.com>
<be4983c9-ae44-f068-ee02-d4b7e4c8d2bd@a-kretschmer.de>
<CACh9nsb5dF6wNC=iyaxGyrHZ212QkbtXtO36it9u-QhOFr6j2g@mail.gmail.com>
<CAGTBQpYhg_Ow3SBmUd4=ycepDp5eJin-ou1kBAasQ5EyuE-n+A@mail.gmail.com>
<CACh9nsYX23u1eLngVx2j5_0CBhUH=MJN9HE6iYOSgNrk5gfnFw@mail.gmail.com>
<CAGTBQpYkYXemTpuNwVMhV-EiwUBiBhiqO2rtAgFXxWy0kF15QA@mail.gmail.com>
<1516776879945-0.post@n3.nabble.com>
<CAGTBQpa-mTQ9qA18xVimrgTmrXZnFAnqKpzHYwCNtc=GEEd+5w@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pavan.postgresdba@gmail.com
Subject: Re: 8.2 Autovacuum BUG ?
In-Reply-To: <1516794633075-0.post@n3.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox