|
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# \dt+ List of relations Schema | Name | Type | Owner | Size | Description ---------+------------------------------+-------+---------+------------+------------- Schema1 | auditlogentry | table | super | 0 bytes | Schema1 | audittraillogentry | table | super | 163 GB | Schema1 | audittraillogentry_temp_join | table | super | 8192 bytes | Schema1 | cdrdetails | table | super | 909 MB | Schema1 | cdrlogentry | table | super | 8192 bytes | Schema1 | consolidatorlogentry | table | super | 24 kB | Schema1 | datalostchecklog | table | super | 0 bytes | Schema1 | eventlogentry | table | super | 56 kB | Schema1 | fileddtable_file | table | super | 0 bytes | Schema1 | filescksumcollected | table | super | 27 MB | Schema1 | filescollected | table | super | 0 bytes | Schema1 | inserviceperformance | table | super | 4552 kB | Schema1 | iostatlogentry | table | super | 0 bytes | Schema1 | loggedalarmentry | table | super | 21 MB | Schema1 | matchinglogentry | table | super | 8192 bytes | Schema1 | nrtrde_nerfile | table | super | 8192 bytes | Schema1 | nrtrde_tmp_nrin | table | super | 0 bytes | Schema1 | prstatlogentry | table | super | 0 bytes | Schema1 | statisticlogentry | table | super | 4400 kB | Schema1 | statisticupgradehistory | table | super | 40 kB | Schema1 | tpmcdrlog | table | super | 0 bytes | Schema1 | upgradehistory | table | super | 40 kB | Schema1 | vmstatlogentry | table | super | 0 bytes | (23 rows) postDb1# select * from audittraillogentry order by outtime ASC limit 5; event | innodeid | innodename | sourceid | intime | outnodeid | outnodename | destinationid | outtime | bytes | cdrs | tableindex | noofsubfilesinfile | rec ordsequencenumberlist -------+----------+------------+----------+--------+-----------+-------------+---------------+---------+-------+------+------------+--------------------+---- ---------------------- (0 rows) 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=# \dt+ List of relations Schema | Name | Type | Owner | Size | Description ---------+------------------------------+-------+---------+------------+------------- Schema1 | auditlogentry | table | super | 0 bytes | Schema1 | audittraillogentry | table | super | 163 GB | Schema1 | audittraillogentry_temp_join | table | super | 8192 bytes | Schema1 | cdrdetails | table | super | 909 MB | Schema1 | cdrlogentry | table | super | 8192 bytes | Schema1 | consolidatorlogentry | table | super | 24 kB | Schema1 | datalostchecklog | table | super | 0 bytes | Schema1 | eventlogentry | table | super | 56 kB | Schema1 | fileddtable_file | table | super | 0 bytes | Schema1 | filescksumcollected | table | super | 27 MB | Schema1 | filescollected | table | super | 0 bytes | Schema1 | inserviceperformance | table | super | 4552 kB | Schema1 | iostatlogentry | table | super | 0 bytes | Schema1 | loggedalarmentry | table | super | 21 MB | Schema1 | matchinglogentry | table | super | 8192 bytes | Schema1 | nrtrde_nerfile | table | super | 8192 bytes | Schema1 | nrtrde_tmp_nrin | table | super | 0 bytes | Schema1 | prstatlogentry | table | super | 0 bytes | Schema1 | statisticlogentry | table | super | 4400 kB | Schema1 | statisticupgradehistory | table | super | 40 kB | Schema1 | tpmcdrlog | table | super | 0 bytes | Schema1 | upgradehistory | table | super | 40 kB | Schema1 | vmstatlogentry | table | super | 0 bytes | (23 rows) postDb1=# Regards Tarkeshwar |
