Обсуждение: vacuum freeze wait_event BufferPin

The vacuum needs a chance to get in and make changes to the table, but it's not being blocked at the traditional lock level that shows up in pg_blocking_pids. You can see what is going on with this:

select pid, mode, query_start, query from pg_locks join pg_stat_activity using (pid) where relation::regclass::text = 'mytable' order by 3;

That may show your vacuum process with a ShareUpdateExclusiveLock and some other processes with other locks, probably AccessShareLock. Those other pids need to all finish or be killed - and not have any overlap between them. In other words, that vacuum process needs to have exclusive access to the table for a split second, no matter if the other process locked the table before or after the vacuum started. One crude solution would be to cancel any other existing backends interested in that table:

Not a good long-term solution, but depending on how often the table is updated, you might have other options. Perhaps disable  autovacuum for this table and do a manual vacuum (e.g. in a cron script) that kills the other backends as per above, or runs during a time with not-constant reads on the table. Or have something that is able to pause the application. Or if this is a partitioned table that might get dropped in the future or at least not queried heavily, do not worry about vacuuming it now.

Cheers,

Greg

Those locks with no other context are somewhat  of a red herring. The important part is not that the AccessShare is somehow blocking ShareUpdateExclusive, but that the ShareUpdateExclusive process is NOT blocking new AccessShare processes! In the internals of postgres, vacuumlazy.c tries to grab a buffer lock (different concept from all the user-visible locks above). It politely tries to wait[1] until nobody else is grabbing it (aka pinning it), then proceeds. The problem is that other processes are allowed to come along and put a pin in it as well - the vacuum's shareupdateexclusive lock does not prevent that.

So the timeline is:

Process X runs a long select and pins the buffer

Process V runs a vacuum freeze and tries to lock the buffer. It detects other pins, so it waits. It assumes that whoever is holding the pin will release it someday.

Process Y runs another long select and also pins the buffer.

Process X ends, and removes its pins.

Process V still cannot move - it just knows there are still pins. Where they come from does not matter.

As long as there is at least one other process holding a pin, the vacuum freeze cannot continue[2].

That's my understanding of the code, anyway. This could be argued as a bug. I am not sure what a solution would be. Cancelling user queries just for a vacuum would not be cool, but we could maybe pause future pin-creating actions somehow?

For the time being, forcing a super-quick moment of no table access would seem to be your best bet, as described earlier.

Cheers,

Greg

[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/heap/vacuumlazy.c;hb=HEAD#l975

See backend/storage/buffer/bufmgr.c for LockBufferForCleanup()

[2] Quick duplication script:

drop table if exists foobar;
create table foobar as select 1 as id;
alter table foobar set (autovacuum_enabled = off);
update foobar set id = id;

Process 1:

begin; select *, pg_sleep(111111) from foobar;

Process 2:

vacuum(freeze,verbose) foobar; /* blocked */

Process 3:

Run in order. Kill Process 1 and Process 2 is still blocked. Kill Process 3 and Process 2 finished the vacuum.

Note that a regular vacuum (without a freeze) will not get blocked.

Cheers,

Greg