From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
> Autovacuum is periodically processing all this 5k relations (because
> them are actively updated).
> And as far as most of this tables are small enough autovacuum complete
> processing of them almost in the same time.
> As a result autovacuum workers produce ~5k invalidation messages in
> short period of time.
How about trying CREATE/ALTER TABLE WITH (vacuum_truncate = off)? It's available since PG 12. It causes autovacuum to
nottruncate the relation. It's the relation truncation what produces those shared invalidation messages.
> But then we come to the next show stopper: NUM_LOCK_PARTITIONS.
> It is also hardcoded and can't be changed without recompilation:
>
> #define LOG2_NUM_LOCK_PARTITIONS 4
> #define NUM_LOCK_PARTITIONS (1 << LOG2_NUM_LOCK_PARTITIONS)
>
> Having just 16 LW-Locks greatly increase conflict probability (taken in
> account that there are 5k tables and totally about 25k relations).
> It cause huge lw-lock acquisition time for heap_open and planning stage
> of some queries is increased from milliseconds to several minutes!
> Koda!
The vacuum's relation truncation is also the culprit here, and it can be eliminated by the above storage parameter. It
acquiresAccess Exclusive lock on the relation. Without the strong Access Exclusive lock, just running DML statements
usethe fast path locking, which doesn't acquire the lock manager partition lock.
The long lwlock wait is a sad story. The victim is probably exclusive lockers. When someone holds a shared lock on a
lwlock,the exclusive locker has to wait. That's OK. However, if another share locker comes later, it acquires the
lwlockeven though there're waiting exclusive lockers. That's unfair, but this is the community decision.
Regards
Takayuki Tsunakawa