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

Hi all,

We have facing some discrepancy in Postgresql database related to the autovacuum functionality.
By default autovacuum was enable on Postgres which is used to remove the dead tuples from the database.

We have observed autovaccum cleaning dead rows from table_A but same was not functioning correctly for table_B which have a large size(100+GB) in comparision to table_A.

All the threshold level requirements for autovacuum was meet and there are about Million’s of dead tuples but autovacuum was unable to clear them, which cause performance issue on production server.

Is autovacuum not working against large sized tables or Is there any parameters which need to set to make autovacuum functioning?

Any suggestions?

Regards
Tarkeshwar

Hi,

We have facing some discrepancy in Postgresql database related to the autovacuum functionality.

By default autovacuum was enable on Postgres which is used to remove the dead tuples from the database.

We have observed autovaccum cleaning dead rows from table_A but same was not functioning correctly for table_B which have a large size(100+GB) in comparision to table_A.

All the threshold level requirements for autovacuum was meet and there are about Million’s of dead tuples but autovacuum was unable to clear them, which cause performance issue on production server.

Is autovacuum not working against large sized tables or Is there any parameters which need to set to make autovacuum functioning?

Do you have autovacuum logging enabled in this server? If so, would be
good if you could share them here.

Having the output from logs of autovacuum for these tables would give
some insights on where the problem might reside.

--
Martín Marqués
It’s not that I have something to hide,
it’s that I have nothing I want you to see

Absolutely check the logs, or do a manual vacuum verbose with setting cost
delay and cost limit (and maintenance work mem) the same as the values for
auto vacuum runs. It should work out the same and you could time it for a
period when the system is more lightly used it applicable.

If you have many very large indexes on the tables with a high number of
tuples and bloat, that may be slowing the execution particularly if your
allowed work memory for the operation doesn't allow a single pass of the
index.

If you are on PG12+, you can reindex concurrently and then run vacuum and
see how it goes.

Freezing will automatically happen according to settings, but if it is near
the threshold then it could be that autovacuum is doing more work scanning
old data. A manual vacuum freeze would mitigate that. That may not be
significant though.

For your larger tables, or system in general, turning down your scale
factor settings will qualify tables for autovacuum sooner. If it hurts, you
aren't doing it often enough.

Also, reducing cost delays may be needed to pause for less time in the
middle of autovacuum executions. The default changed from 20ms to 2ms with
PG12 but if your I/O system can handle it, lower may be prudent to get the
work done more quickly.

On Wed, Dec 16, 2020 at 6:55 AM M Tarkeshwar Rao <
m.tarkeshwar.rao@ericsson.com> wrote:

...

All the threshold level requirements for autovacuum was meet and there are
about Million’s of dead tuples but autovacuum was unable to clear them,
which cause performance issue on production server.

It might be helpful for us to see what data you are looking at to reach
this conclusion.

Is autovacuum not working against large sized tables or Is there any
parameters which need to set to make autovacuum functioning?

Autovacuum is not inherently broken for large tables. But vacuuming them
takes longer than for small tables. If it is frequently interrupted by
things like CREATE INDEX, ALTER TABLE, or database shutdown and restart,
then it might never get through the entire table without interruption. If
it is getting interrupted, you should see messages in the log file about
it. You can also check pg_stat_user_tables to see when it was last
successfully (to completion) auto vacuumed, and on new enough versions you
can look in pg_stat_progress_vacuum to monitor the vacuuming while it
occurs.

Cheers,

Jeff

On 12/16/20 12:55 PM, M Tarkeshwar Rao wrote:

Hi all,

We have facing some discrepancy in Postgresql database related to the
autovacuum functionality.

By default autovacuum was enable on Postgres which is used to remove the
dead tuples from the database.

We have observed autovaccum cleaning dead rows from *table_A* but same
was not functioning correctly for *table_B* which have a large
size(100+GB) in comparision to table_A.

All the threshold level requirements for autovacuum was meet and there
are about Million’s of  dead tuples but autovacuum was unable to clear
them, which cause performance issue on production server.

Is autovacuum not working against large sized tables or Is there any
parameters which  need to set to make autovacuum functioning?

No, autovacuum should work for tables with any size. The most likely
explanation is that the rows in the large table were deleted more
recently and there is a long-running transaction blocking the cleanup.
Or maybe not, hard to say with the info you provided.

A couple suggestions:

1) enable logging for autovacuum by setting

log_autovacuum_min_duration = 10ms (or similar low value)

2) check that the autovacuum is actually executed on the large table
(there's last_autovacuum in pg_stat_all_tables)

3) try running VACUUM VERBOSE on the large table, it may tell you that
the rows can't be cleaned up yet.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hi all,

As we know, the VACUUM VERBOSE output has a lot of dependencies from production end and is indefinite as of now. We don’t have any clue till now on why exactly the auto-vacuum is not working for the table. So we need to have a work around to move ahead for the time being.

Can you please suggest any workaround so that we can resolve the issue or any other way by which we can avoid this situation?

Regards
Tarkeshwar

-----Original Message-----
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
Sent: Thursday, December 17, 2020 7:16 AM
To: M Tarkeshwar Rao <m.tarkeshwar.rao@ericsson.com>; pgsql-performance@postgresql.org
Cc: Neeraj Gupta G <neeraj.g.gupta@ericsson.com>; Atul Parashar <atul.parashar@ericsson.com>; Shishir Singh <shishir.singh@globallogic.com>; Ankit Sharma <ankit.sharma10@globallogic.com>
Subject: Re: Autovacuum not functioning for large tables but it is working for few other small tables.

On 12/16/20 12:55 PM, M Tarkeshwar Rao wrote:

Hi all,

We have facing some discrepancy in Postgresql database related to the
autovacuum functionality.

By default autovacuum was enable on Postgres which is used to remove
the dead tuples from the database.

We have observed autovaccum cleaning dead rows from *table_A* but same
was not functioning correctly for *table_B* which have a large
size(100+GB) in comparision to table_A.

All the threshold level requirements for autovacuum was meet and there
are about Million’s of  dead tuples but autovacuum was unable to clear
them, which cause performance issue on production server.

Is autovacuum not working against large sized tables or Is there any
parameters which  need to set to make autovacuum functioning?

No, autovacuum should work for tables with any size. The most likely explanation is that the rows in the large table were deleted more recently and there is a long-running transaction blocking the cleanup.
Or maybe not, hard to say with the info you provided.

A couple suggestions:

1) enable logging for autovacuum by setting

log_autovacuum_min_duration = 10ms (or similar low value)

2) check that the autovacuum is actually executed on the large table (there's last_autovacuum in pg_stat_all_tables)

3) try running VACUUM VERBOSE on the large table, it may tell you that the rows can't be cleaned up yet.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hi all,

As we know, the VACUUM VERBOSE output has a lot of dependencies from production end and is indefinite as of now. We don’t have any clue till now on why exactly the auto-vacuum is not working for the table. So we need to have a work around to move ahead for the time being.

Can you please suggest any workaround so that we can resolve the issue or any other way by which we can avoid this situation?

Regards
Tarkeshwar

-----Original Message-----
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
Sent: Thursday, December 17, 2020 7:16 AM
To: M Tarkeshwar Rao <m.tarkeshwar.rao@ericsson.com>; pgsql-performance@postgresql.org
Cc: Neeraj Gupta G <neeraj.g.gupta@ericsson.com>; Atul Parashar <atul.parashar@ericsson.com>; Shishir Singh <shishir.singh@globallogic.com>; Ankit Sharma <ankit.sharma10@globallogic.com>
Subject: Re: Autovacuum not functioning for large tables but it is working for few other small tables.

On 12/16/20 12:55 PM, M Tarkeshwar Rao wrote:

Hi all,

We have facing some discrepancy in Postgresql database related to the
autovacuum functionality.

By default autovacuum was enable on Postgres which is used to remove
the dead tuples from the database.

We have observed autovaccum cleaning dead rows from *table_A* but same
was not functioning correctly for *table_B* which have a large
size(100+GB) in comparision to table_A.

All the threshold level requirements for autovacuum was meet and there
are about Million’s of  dead tuples but autovacuum was unable to clear
them, which cause performance issue on production server.

Is autovacuum not working against large sized tables or Is there any
parameters which  need to set to make autovacuum functioning?

No, autovacuum should work for tables with any size. The most likely explanation is that the rows in the large table were deleted more recently and there is a long-running transaction blocking the cleanup.
Or maybe not, hard to say with the info you provided.

A couple suggestions:

1) enable logging for autovacuum by setting

log_autovacuum_min_duration = 10ms (or similar low value)

2) check that the autovacuum is actually executed on the large table (there's last_autovacuum in pg_stat_all_tables)

3) try running VACUUM VERBOSE on the large table, it may tell you that the rows can't be cleaned up yet.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

By the way, please do not top-post (reply above, quoting the full email
after) in these groups.

On Fri, Jan 8, 2021 at 5:00 AM M Tarkeshwar Rao <
m.tarkeshwar.rao@ericsson.com> wrote:

Hi all,

As we know, the VACUUM VERBOSE output has a lot of dependencies from
production end and is indefinite as of now.

What do you mean by this statement?

We don’t have any clue till now on why exactly the auto-vacuum is not
working for the table. So we need to have a work around to move ahead for
the time being.

Can you please suggest any workaround so that we can resolve the issue or
any other way by which we can avoid this situation?

Have you tried any of the suggestions already given 3+ weeks ago? Do you
have answers to any of the questions posed by me or the other three people
who responded?

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

On Fri, 2021-02-19 at 10:51 +0000, M Tarkeshwar Rao wrote:

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
[and so on, the same 6 indexes are repeatedly scanned]

PostgreSQL performs VACUUM in batches of "maintenance_work_mem" size
of tuple identifiers. If that parameter is small, the indexes have
to be scanned often.

Try increasing "maintenance_work_mem" to 1GB (if you have enough RAM),
that will make it faster.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com