Quick Links

RE: Need information on how MM frees up disk space (vaccum) after scheduled DB cleanup by BGwCronScript/BGwLogCleaner

From:	M Tarkeshwar Rao <m(dot)tarkeshwar(dot)rao(at)ericsson(dot)com>
To:	"pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Cc:	Ankit Sharma <ankit(dot)sharma10(at)globallogic(dot)com>, Atul Parashar <atul(dot)parashar(at)ericsson(dot)com>
Subject:	RE: Need information on how MM frees up disk space (vaccum) after scheduled DB cleanup by BGwCronScript/BGwLogCleaner
Date:	2021-01-14 12:09:51
Message-ID:	AM6PR0702MB37835B07615D58EC3AB5E867AEA80@AM6PR0702MB3783.eurprd07.prod.outlook.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-performance

Hi,

We have got the result of the VACUUM (VERBOSE) as suggested, please find the output as following & suggest further.

But please note that this was done on an non production server where uncleaned data was there, although no dead tuples as it doesn’t run any configuration at present. However I can see it’s giving some error related to “stopping truncate” due to some lock conflict.

EMMPR01:~# psql -d postDb1 -p 5492 -h 101.103.109.99 mmsuper
Password for user mmsuper:
psql (9.4.9)
Type "help" for help.

postDb1# VACUUM (VERBOSE) audittraillogentry;
INFO: vacuuming "mmsuper.audittraillogentry"
INFO: scanned index "audittraillogentry_pkey" to remove 946137 row versions
DETAIL: CPU 11.46s/2.92u sec elapsed 40.43 sec.
INFO: scanned index "audit_intime_index" to remove 946137 row versions
DETAIL: CPU 18.46s/4.57u sec elapsed 60.16 sec.
INFO: scanned index "audit_outtime_index" to remove 946137 row versions
DETAIL: CPU 18.28s/4.53u sec elapsed 56.35 sec.
INFO: scanned index "audit_sourceid_index" to remove 946137 row versions
DETAIL: CPU 52.15s/12.12u sec elapsed 176.57 sec.
INFO: scanned index "audit_destid_index" to remove 946137 row versions
DETAIL: CPU 46.18s/11.21u sec elapsed 163.85 sec.
INFO: "audittraillogentry": removed 946137 row versions in 33096 pages
DETAIL: CPU 2.02s/0.54u sec elapsed 18.75 sec.
INFO: index "audittraillogentry_pkey" now contains 0 row versions in 815195 pages
DETAIL: 946137 index row versions were removed.
815155 index pages have been deleted, 801425 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.10 sec.
INFO: index "audit_intime_index" now contains 0 row versions in 1274980 pages
DETAIL: 946137 index row versions were removed.
1274868 index pages have been deleted, 1262921 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.07 sec.
INFO: index "audit_outtime_index" now contains 0 row versions in 1288204 pages
DETAIL: 946137 index row versions were removed.
1288086 index pages have been deleted, 1276659 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.07 sec.
INFO: index "audit_sourceid_index" now contains 0 row versions in 3711812 pages
DETAIL: 946137 index row versions were removed.
3711581 index pages have been deleted, 3700051 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO: index "audit_destid_index" now contains 0 row versions in 3234747 pages
DETAIL: 946137 index row versions were removed.
3234422 index pages have been deleted, 3216227 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "audittraillogentry": found 291165 removable, 0 nonremovable row versions in 137466 out of 21356455 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 5338303 unused item pointers.
0 pages are entirely empty.
CPU 152.39s/37.41u sec elapsed 549.50 sec.
INFO: "audittraillogentry": stopping truncate due to conflicting lock request
INFO: vacuuming "pg_toast.pg_toast_16413"
INFO: index "pg_toast_16413_index" now contains 0 row versions in 1 pages
DETAIL: 0 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: "pg_toast_16413": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
postDb1# SELECT pid, datname, usename, state, backend_xmin FROM pg_stat_activity WHERE backend_xmin IS NOT NULL ORDER BY age(backend_xmin) DESC;
pid |datname | usename | state | backend_xmin
-------+-----------------+---------+--------+--------------
23278 | postDb1 | super | active | 1327734444
31637 | postDb1 | super | active | 1327734444
2458 | postDb1 | super | active | 1327734444
11054 | postDb1 | super | active | 1327734444
12080 | postDb1 | super | active | 1327734444
14810 | postDb1 | super | active | 1327734444
19528 | postDb1 | super | active | 1327734444
16554 | postDb1 | super | active | 1327734444
23303 | postDb1 | super | active | 1327734444
19322 | postDb1 | super | active | 1327734444
25109 | postDb1 | super | active | 1327734444
17445 | postDb1 | super | active | 1327734444
(12 rows)

postDb1# SELECT slot_name, slot_type, database, xmin FROM pg_replication_slots ORDER BY age(xmin) DESC;
slot_name | slot_type | database | xmin
-----------+-----------+----------+------
(0 rows)

postDb1# SELECT gid, prepared, owner, database, transaction AS xmin FROM pg_prepared_xacts ORDER BY age(transaction) DESC;
gid | prepared | owner | database | xmin
-----+----------+-------+----------+------
(0 rows)

postDb1=#

Regards
Tarkeshwar

Browse pgsql-performance by date

	From	Date	Subject
Next Message	Don Seiler	2021-01-15 21:18:48	Re: High COMMIT times
Previous Message	Michael Lewis	2021-01-11 18:26:58	Re: How to deal with analyze gathering irrelevant stats