RE: Autovacuum not functioning for large tables but it is working for few other small tables.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux