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

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