Re: autovacuum hung on simple tables

On Mon, 2022-11-28 at 04:05 +0000, senor wrote:
> I'm a little late getting back to this but still have no solution.
>
> I had inadvertently executed a vacuum freeze on a table that pg_stat_activity
> indicated was currently being autovacuumed. The manual vacuum succeeded while
> leaving the previous autovacuum still showing active in pg_stat_activity.

No, that cannot be. VACUUM processes are mutually exclusive.
It could have been another autovacuum worker (different "pid").

> Manual vacuum always completes, never stalls, but also often does not appear
> in pg_stat_progress_vacuum unless it's a longer process.

It *does* appear in "pg_stat_progress_vacuum", but perhaps you are too slow
to catch it.

> It appears the process completes the vacuum but does not register that fact.
> relfrozenxid of the main table is what would be expected but an associated
> toast table was still very old. Cancelling all pending vacuums of the table
> and manually running vacuum completes in a few seconds and both the main
> relation and toast are updated as expected with last vacuum time updated
> in pg_stat_all_tables.

Obviously autovacuum devided to process the main table, but not the TOAST
table. That is normal. Manual VACUUM processes both, unless you specify
the option PROCESS_TOAST OFF.

> Autoacuum and autovacuum analyze both get hung.

No, they don't; not unless they are anti-wraparound autovacuum runs and you
are holding high locks on the table in a long running transaction.
They may be slow to complete, because in v11 autovacuum by default is very
slow indeed, as "autovacuum_vacuum_cost_delay" is 20ms.

> I often see the same table listed multiple times in pg_stat_activity with
> different pids and:
> state: active
> backend_type: autovacuum worker
> wait_event_type: blank
> state_change: 10-15 seconds after backend_start - about how long it takes
> to manually run vacuum on most tables.

That can be parallel workers that are used to scan indexes.

> What exactly does autovacuum rely on in the stats file? I ran strace on a
> hung autovacuum process and saw a repeated read of
> /run/postgresql/db_16384.stat (tempfs). The file is 740MB which is about
> the same as other similar installations I've reviewed. I'm lacking in overall
> experience in this though.

I don't know either, and I am too lazy to read the code on that, but I suspect
that it has no connection to your problem.

> One final oddity:
> I ran a query for oldest relfrozenxid and redirected to file. The query took
> around a minute. A few seconds after it finished, I queried for 'active' in
> pg_stat_activity and the oldest relfrozenxid query was still listed.
> A few seconds later it had cleared.

That sounds weird and is hard to believe.
Are the disk or the CPU under extreme stress?

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