|
Hi, Please find the Vacuum(verbose) output. Can you please suggest what is the reason? How can we avoid these scenarios? The customer tried to run the VACUUM(verbose) last night, but it was running continuously for 5 hours without any visible progress. So they had to abort it as it was going to exhaust their maintenance window. db_Server14=# VACUUM (VERBOSE) audittraillogentry; INFO: vacuuming "mmsuper.audittraillogentry" INFO: scanned index "audittraillogentry_pkey" to remove 11184539 row versions DETAIL: CPU 25.24s/49.11u sec elapsed 81.33 sec INFO: scanned index "audit_intime_index" to remove 11184539 row versions DETAIL: CPU 23.27s/59.28u sec elapsed 88.63 sec INFO: scanned index "audit_outtime_index" to remove 11184539 row versions DETAIL: CPU 27.02s/55.10u sec elapsed 92.04 sec INFO: scanned index "audit_sourceid_index" to remove 11184539 row versions DETAIL: CPU 110.81s/72.29u sec elapsed 260.71 sec INFO: scanned index "audit_destid_index" to remove 11184539 row versions DETAIL: CPU 100.49s/87.03u sec elapsed 265.00 sec INFO: "audittraillogentry": removed 11184539 row versions in 247622 pages DETAIL: CPU 3.23s/0.89u sec elapsed 6.64 sec INFO: scanned index "audittraillogentry_pkey" to remove 11184545 row versions DETAIL: CPU 25.73s/45.72u sec elapsed 86.59 sec INFO: scanned index "audit_intime_index" to remove 11184545 row versions DETAIL: CPU 34.65s/56.52u sec elapsed 113.52 sec INFO: scanned index "audit_outtime_index" to remove 11184545 row versions DETAIL: CPU 35.55s/61.96u sec elapsed 113.89 sec INFO: scanned index "audit_sourceid_index" to remove 11184545 row versions DETAIL: CPU 120.60s/75.17u sec elapsed 286.78 sec INFO: scanned index "audit_destid_index" to remove 11184545 row versions DETAIL: CPU 111.87s/93.74u sec elapsed 295.05 sec INFO: "audittraillogentry": removed 11184545 row versions in 1243407 pages DETAIL: CPU 20.35s/6.45u sec elapsed 71.61 sec INFO: scanned index "audittraillogentry_pkey" to remove 11184547 row versions DETAIL: CPU 21.84s/43.36u sec elapsed 71.72 sec INFO: scanned index "audit_intime_index" to remove 11184547 row versions DETAIL: CPU 33.37s/57.07u sec elapsed 99.50 sec INFO: scanned index "audit_outtime_index" to remove 11184547 row versions DETAIL: CPU 35.08s/60.08u sec elapsed 110.08 sec INFO: scanned index "audit_sourceid_index" to remove 11184547 row versions DETAIL: CPU 117.72s/72.75u sec elapsed 256.31 sec INFO: scanned index "audit_destid_index" to remove 11184547 row versions DETAIL: CPU 103.46s/77.43u sec elapsed 247.23 sec INFO: "audittraillogentry": removed 11184547 row versions in 268543 pages DETAIL: CPU 4.36s/1.35u sec elapsed 9.61 sec INFO: scanned index "audittraillogentry_pkey" to remove 11184521 row versions DETAIL: CPU 26.64s/45.46u sec elapsed 80.51 sec INFO: scanned index "audit_intime_index" to remove 11184521 row versions DETAIL: CPU 35.05s/59.11u sec elapsed 111.23 sec INFO: scanned index "audit_outtime_index" to remove 11184521 row versions DETAIL: CPU 32.98s/56.41u sec elapsed 105.93 sec INFO: scanned index "audit_sourceid_index" to remove 11184521 row versions DETAIL: CPU 117.13s/71.14u sec elapsed 254.33 sec INFO: scanned index "audit_destid_index" to remove 11184521 row versions DETAIL: CPU 99.93s/81.77u sec elapsed 241.83 sec INFO: "audittraillogentry": removed 11184521 row versions in 268593 pages DETAIL: CPU 3.49s/1.14u sec elapsed 6.87 sec INFO: scanned index "audittraillogentry_pkey" to remove 11184534 row versions DETAIL: CPU 22.73s/42.41u sec elapsed 69.12 sec INFO: scanned index "audit_intime_index" to remove 11184534 row versions DETAIL: CPU 36.78s/68.04u sec elapsed 121.60 sec INFO: scanned index "audit_outtime_index" to remove 11184534 row versions DETAIL: CPU 31.11s/52.88u sec elapsed 93.93 sec INFO: scanned index "audit_sourceid_index" to remove 11184534 row versions DETAIL: CPU 117.95s/72.65u sec elapsed 247.44 sec INFO: scanned index "audit_destid_index" to remove 11184534 row versions DETAIL: CPU 104.25s/82.63u sec elapsed 248.43 sec INFO: "audittraillogentry": removed 11184534 row versions in 268598 pages DETAIL: CPU 3.74s/1.17u sec elapsed 9.45 sec INFO: scanned index "audittraillogentry_pkey" to remove 11184546 row versions DETAIL: CPU 21.24s/40.72u sec elapsed 68.78 sec INFO: scanned index "audit_intime_index" to remove 11184546 row versions DETAIL: CPU 34.29s/56.72u sec elapsed 99.63 sec INFO: scanned index "audit_outtime_index" to remove 11184546 row versions DETAIL: CPU 33.83s/60.99u sec elapsed 105.22 sec INFO: scanned index "audit_sourceid_index" to remove 11184546 row versions DETAIL: CPU 114.26s/70.11u sec elapsed 239.56 sec INFO: scanned index "audit_destid_index" to remove 11184546 row versions DETAIL: CPU 100.73s/73.28u sec elapsed 228.37 sec INFO: "audittraillogentry": removed 11184546 row versions in 268538 pages DETAIL: CPU 3.80s/1.18u sec elapsed 7.79 sec INFO: scanned index "audittraillogentry_pkey" to remove 11184523 row versions DETAIL: CPU 25.78s/47.23u sec elapsed 77.60 sec INFO: scanned index "audit_intime_index" to remove 11184523 row versions DETAIL: CPU 35.39s/56.45u sec elapsed 103.70 sec INFO: scanned index "audit_outtime_index" to remove 11184523 row versions DETAIL: CPU 31.16s/52.24u sec elapsed 90.21 sec INFO: scanned index "audit_sourceid_index" to remove 11184523 row versions DETAIL: CPU 114.71s/70.03u sec elapsed 260.11 sec INFO: scanned index "audit_destid_index" to remove 11184523 row versions DETAIL: CPU 105.71s/76.33u sec elapsed 228.59 sec INFO: "audittraillogentry": removed 11184523 row versions in 268611 pages DETAIL: CPU 3.40s/1.17u sec elapsed 7.10 sec INFO: scanned index "audittraillogentry_pkey" to remove 11184554 row versions DETAIL: CPU 22.80s/39.22u sec elapsed 67.26 sec INFO: scanned index "audit_intime_index" to remove 11184554 row versions DETAIL: CPU 35.38s/57.31u sec elapsed 106.01 sec INFO: scanned index "audit_outtime_index" to remove 11184554 row versions DETAIL: CPU 34.15s/54.73u sec elapsed 97.79 sec INFO: scanned index "audit_sourceid_index" to remove 11184554 row versions DETAIL: CPU 118.37s/71.55u sec elapsed 243.34 sec INFO: scanned index "audit_destid_index" to remove 11184554 row versions DETAIL: CPU 100.43s/72.41u sec elapsed 252.42 sec INFO: "audittraillogentry": removed 11184554 row versions in 268590 pages DETAIL: CPU 4.40s/1.34u sec elapsed 9.00 sec INFO: scanned index "audittraillogentry_pkey" to remove 11184533 row versions DETAIL: CPU 25.01s/40.12u sec elapsed 72.19 sec INFO: scanned index "audit_intime_index" to remove 11184533 row versions DETAIL: CPU 34.13s/52.89u sec elapsed 93.53 sec INFO: scanned index "audit_outtime_index" to remove 11184533 row versions DETAIL: CPU 31.29s/50.04u sec elapsed 88.22 sec INFO: scanned index "audit_sourceid_index" to remove 11184533 row versions DETAIL: CPU 119.38s/66.95u sec elapsed 257.04 sec INFO: scanned index "audit_destid_index" to remove 11184533 row versions DETAIL: CPU 102.33s/74.23u sec elapsed 230.70 sec INFO: "audittraillogentry": removed 11184533 row versions in 268627 pages DETAIL: CPU 3.94s/1.28u sec elapsed 7.74 sec INFO: scanned index "audittraillogentry_pkey" to remove 11184536 row versions DETAIL: CPU 22.67s/38.49u sec elapsed 66.67 sec INFO: scanned index "audit_intime_index" to remove 11184536 row versions DETAIL: CPU 37.17s/61.79u sec elapsed 107.70 sec INFO: scanned index "audit_outtime_index" to remove 11184536 row versions DETAIL: CPU 32.23s/51.13u sec elapsed 90.93 sec INFO: scanned index "audit_sourceid_index" to remove 11184536 row versions DETAIL: CPU 117.68s/70.04u sec elapsed 239.51 sec INFO: scanned index "audit_destid_index" to remove 11184536 row versions DETAIL: CPU 103.82s/72.82u sec elapsed 228.64 sec INFO: "audittraillogentry": removed 11184536 row versions in 268597 pages DETAIL: CPU 4.01s/1.34u sec elapsed 8.74 sec INFO: scanned index "audittraillogentry_pkey" to remove 11184533 row versions DETAIL: CPU 26.34s/39.03u sec elapsed 70.76 sec INFO: scanned index "audit_intime_index" to remove 11184533 row versions DETAIL: CPU 35.98s/53.60u sec elapsed 99.27 sec INFO: scanned index "audit_outtime_index" to remove 11184533 row versions DETAIL: CPU 32.57s/50.71u sec elapsed 90.61 sec INFO: scanned index "audit_sourceid_index" to remove 11184533 row versions DETAIL: CPU 122.50s/64.66u sec elapsed 254.06 sec INFO: scanned index "audit_destid_index" to remove 11184533 row versions DETAIL: CPU 100.87s/78.60u sec elapsed 237.31 sec INFO: "audittraillogentry": removed 11184533 row versions in 268643 pages DETAIL: CPU 4.01s/1.23u sec elapsed 7.69 sec INFO: scanned index "audittraillogentry_pkey" to remove 11184535 row versions DETAIL: CPU 22.65s/36.84u sec elapsed 61.70 sec INFO: scanned index "audit_intime_index" to remove 11184535 row versions DETAIL: CPU 37.86s/59.20u sec elapsed 104.94 sec INFO: scanned index "audit_outtime_index" to remove 11184535 row versions DETAIL: CPU 32.06s/48.99u sec elapsed 88.31 sec INFO: scanned index "audit_sourceid_index" to remove 11184535 row versions DETAIL: CPU 120.01s/69.92u sec elapsed 245.13 sec INFO: scanned index "audit_destid_index" to remove 11184535 row versions DETAIL: CPU 102.99s/69.48u sec elapsed 216.71 sec INFO: "audittraillogentry": removed 11184535 row versions in 268574 pages DETAIL: CPU 4.27s/1.41u sec elapsed 9.40 sec INFO: scanned index "audittraillogentry_pkey" to remove 11184545 row versions DETAIL: CPU 26.12s/39.21u sec elapsed 71.64 sec INFO: scanned index "audit_intime_index" to remove 11184545 row versions DETAIL: CPU 35.67s/52.12u sec elapsed 95.95 sec INFO: scanned index "audit_outtime_index" to remove 11184545 row versions DETAIL: CPU 32.68s/47.59u sec elapsed 86.58 sec INFO: scanned index "audit_sourceid_index" to remove 11184545 row versions DETAIL: CPU 118.72s/64.51u sec elapsed 249.14 sec INFO: scanned index "audit_destid_index" to remove 11184545 row versions DETAIL: CPU 103.10s/76.75u sec elapsed 248.05 sec INFO: "audittraillogentry": removed 11184545 row versions in 268662 pages DETAIL: CPU 3.69s/1.18u sec elapsed 7.75 sec INFO: scanned index "audittraillogentry_pkey" to remove 11184521 row versions DETAIL: CPU 22.80s/35.86u sec elapsed 61.23 sec INFO: scanned index "audit_intime_index" to remove 11184521 row versions DETAIL: CPU 35.79s/53.76u sec elapsed 97.45 sec INFO: scanned index "audit_outtime_index" to remove 11184521 row versions DETAIL: CPU 33.41s/46.93u sec elapsed 93.18 sec INFO: scanned index "audit_sourceid_index" to remove 11184521 row versions DETAIL: CPU 117.29s/66.18u sec elapsed 224.79 sec INFO: scanned index "audit_destid_index" to remove 11184521 row versions DETAIL: CPU 104.67s/68.33u sec elapsed 226.39 sec INFO: "audittraillogentry": removed 11184521 row versions in 268576 pages DETAIL: CPU 3.76s/1.08u sec elapsed 7.49 sec INFO: scanned index "audittraillogentry_pkey" to remove 11184525 row versions DETAIL: CPU 25.06s/39.94u sec elapsed 70.43 sec INFO: scanned index "audit_intime_index" to remove 11184525 row versions DETAIL: CPU 35.01s/50.04u sec elapsed 94.04 sec INFO: scanned index "audit_outtime_index" to remove 11184525 row versions DETAIL: CPU 31.41s/45.69u sec elapsed 84.37 sec INFO: scanned index "audit_sourceid_index" to remove 11184525 row versions DETAIL: CPU 118.28s/63.16u sec elapsed 244.28 sec INFO: scanned index "audit_destid_index" to remove 11184525 row versions DETAIL: CPU 105.60s/73.95u sec elapsed 227.47 sec INFO: "audittraillogentry": removed 11184525 row versions in 268660 pages DETAIL: CPU 3.91s/1.25u sec elapsed 7.51 sec INFO: scanned index "audittraillogentry_pkey" to remove 11184538 row versions DETAIL: CPU 23.79s/34.59u sec elapsed 62.01 sec INFO: scanned index "audit_intime_index" to remove 11184538 row versions DETAIL: CPU 36.86s/51.24u sec elapsed 99.10 sec INFO: scanned index "audit_outtime_index" to remove 11184538 row versions DETAIL: CPU 34.95s/53.11u sec elapsed 98.44 sec INFO: scanned index "audit_sourceid_index" to remove 11184538 row versions DETAIL: CPU 115.09s/62.14u sec elapsed 229.85 sec INFO: scanned index "audit_destid_index" to remove 11184538 row versions DETAIL: CPU 107.02s/65.97u sec elapsed 218.05 sec INFO: "audittraillogentry": removed 11184538 row versions in 268584 pages DETAIL: CPU 3.46s/1.30u sec elapsed 7.03 sec INFO: scanned index "audittraillogentry_pkey" to remove 11184546 row versions DETAIL: CPU 23.68s/33.59u sec elapsed 60.67 sec INFO: scanned index "audit_intime_index" to remove 11184546 row versions DETAIL: CPU 39.63s/54.93u sec elapsed 106.66 sec INFO: scanned index "audit_outtime_index" to remove 11184546 row versions DETAIL: CPU 32.55s/44.43u sec elapsed 84.53 sec INFO: scanned index "audit_sourceid_index" to remove 11184546 row versions DETAIL: CPU 122.49s/63.49u sec elapsed 235.39 sec INFO: scanned index "audit_destid_index" to remove 11184546 row versions DETAIL: CPU 108.09s/69.68u sec elapsed 227.05 sec INFO: "audittraillogentry": removed 11184546 row versions in 269472 pages DETAIL: CPU 4.32s/1.33u sec elapsed 8.72 sec INFO: scanned index "audittraillogentry_pkey" to remove 11184536 row versions DETAIL: CPU 23.70s/32.98u sec elapsed 62.22 sec INFO: scanned index "audit_intime_index" to remove 11184536 row versions DETAIL: CPU 35.77s/46.57u sec elapsed 88.27 sec INFO: scanned index "audit_outtime_index" to remove 11184536 row versions DETAIL: CPU 32.59s/43.16u sec elapsed 82.06 sec INFO: scanned index "audit_sourceid_index" to remove 11184536 row versions DETAIL: CPU 126.27s/60.18u sec elapsed 258.72 sec INFO: scanned index "audit_destid_index" to remove 11184536 row versions DETAIL: CPU 112.57s/65.24u sec elapsed 232.06 sec INFO: "audittraillogentry": removed 11184536 row versions in 269319 pages DETAIL: CPU 3.73s/1.29u sec elapsed 7.58 sec INFO: scanned index "audittraillogentry_pkey" to remove 11184538 row versions DETAIL: CPU 23.22s/32.16u sec elapsed 60.22 sec INFO: scanned index "audit_intime_index" to remove 11184538 row versions DETAIL: CPU 38.42s/51.43u sec elapsed 101.53 sec INFO: scanned index "audit_outtime_index" to remove 11184538 row versions DETAIL: CPU 33.29s/42.79u sec elapsed 88.70 sec INFO: scanned index "audit_sourceid_index" to remove 11184538 row versions DETAIL: CPU 124.04s/62.06u sec elapsed 230.83 sec INFO: scanned index "audit_destid_index" to remove 11184538 row versions DETAIL: CPU 105.41s/64.14u sec elapsed 223.93 sec INFO: "audittraillogentry": removed 11184538 row versions in 269384 pages DETAIL: CPU 3.69s/1.11u sec elapsed 7.79 sec INFO: scanned index "audittraillogentry_pkey" to remove 11184520 row versions DETAIL: CPU 26.60s/34.89u sec elapsed 64.47 sec INFO: scanned index "audit_intime_index" to remove 11184520 row versions DETAIL: CPU 36.01s/45.24u sec elapsed 88.69 sec INFO: scanned index "audit_outtime_index" to remove 11184520 row versions DETAIL: CPU 33.00s/41.31u sec elapsed 83.02 sec INFO: scanned index "audit_sourceid_index" to remove 11184520 row versions DETAIL: CPU 124.80s/58.92u sec elapsed 246.98 sec INFO: scanned index "audit_destid_index" to remove 11184520 row versions DETAIL: CPU 106.35s/71.38u sec elapsed 249.67 sec INFO: "audittraillogentry": removed 11184520 row versions in 269050 pages DETAIL: CPU 3.74s/1.16u sec elapsed 8.87 sec INFO: scanned index "audittraillogentry_pkey" to remove 11184523 row versions DETAIL: CPU 21.95s/30.36u sec elapsed 59.88 sec INFO: scanned index "audit_intime_index" to remove 11184523 row versions DETAIL: CPU 33.84s/42.86u sec elapsed 88.67 sec INFO: scanned index "audit_outtime_index" to remove 11184523 row versions DETAIL: CPU 35.71s/44.46u sec elapsed 95.35 sec INFO: scanned index "audit_sourceid_index" to remove 11184523 row versions DETAIL: CPU 120.51s/61.81u sec elapsed 249.04 sec INFO: scanned index "audit_destid_index" to remove 11184523 row versions DETAIL: CPU 103.16s/62.69u sec elapsed 231.34 sec INFO: "audittraillogentry": removed 11184523 row versions in 266741 pages DETAIL: CPU 4.27s/1.24u sec elapsed 8.26 sec INFO: scanned index "audittraillogentry_pkey" to remove 11184551 row versions DETAIL: CPU 25.89s/37.48u sec elapsed 69.65 sec INFO: scanned index "audit_intime_index" to remove 11184551 row versions DETAIL: CPU 35.74s/43.70u sec elapsed 100.58 sec INFO: scanned index "audit_outtime_index" to remove 11184551 row versions DETAIL: CPU 31.45s/40.14u sec elapsed 84.00 sec db_Server14=# 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 -------+----------------+----------+--------+-------------- 73583 | fm_db_Server14 | mmsuper | active | 63548809 31359 | fm_db_Server14 | postgres | active | 63548812 52761 | fm_db_Server14 | mmsuper | active | 63548814 53197 | fm_db_Server14 | mmsuper | active | 63548815 53409 | fm_db_Server14 | mmsuper | active | 63548815 38917 | fm_db_Server14 | mmsuper | active | 63548818 (6 rows) db_Server14=# SELECT slot_name, slot_type, database, xmin FROM pg_replication_slots ORDER BY age(xmin) DESC; slot_name | slot_type | database | xmin -----------+-----------+----------+------ (0 rows) db_Server14=# SELECT gid, prepared, owner, database, transaction AS xmin FROM pg_prepared_xacts ORDER BY age(transaction) DESC; gid | prepared | owner | database | xmin -----+----------+-------+----------+------ (0 rows) Regards Tarkeshwar |
