Re: Configurable FP_LOCK_SLOTS_PER_BACKEND - Mailing list pgsql-hackers

Thanks for sharing this!

I have not yet written a reproducer since we see this daily in production.  I have a sketch of a few ways that I think will reproduce the behavior we're observing, but haven't had time to implement it.

I'm not sure if we're seeing this behavior in production, but it's definitely an interesting find.  Currently we are running postgres 12.11, with an upcoming upgrade to 15 planned.  Good to know there's a potential improvement waiting in 16.  I noticed that in LWLockAcquire the call to LWLockDequeueSelf occurs (https://github.com/postgres/postgres/blob/REL_12_11/src/backend/storage/lmgr/lwlock.c#L1218) directly between the unsuccessful attempt to immediately acquire the lock and reporting the backend's wait event.  The distinctive indicators we have been using for this pathology are that "lock_manager" wait_event and its associated USDT probe (https://github.com/postgres/postgres/blob/REL_12_11/src/backend/storage/lmgr/lwlock.c#L1236-L1237), both of which occur after whatever overhead is incurred by LWLockDequeueSelf.  As you mentioned in your commit message, that overhead is hard to detect.  My first impression is that whatever overhead it incurs is in addition to what we are investigating.

 

Great questions, but we ruled that out.  The deadlock_timeout is 5 seconds, so frequently hitting that would massively violate SLO and would alert the on-call engineers.  The pg_locks view is scraped a couple times per minute for metrics collection, but the lock_manager lwlock contention can be observed thousands of times every second, typically with very short durations.  The following example (captured just now) shows the number of times per second over a 10-second window that any 1 of the 16 "lock_manager" lwlocks was contended:

msmiley@patroni-main-2004-103-db-gprd.c.gitlab-production.internal:~$ sudo ./bpftrace -e 'usdt:/usr/lib/postgresql/12/bin/postgres:lwlock__wait__start /str(arg0) == "lock_manager"/ { @[arg1] = count(); } interval:s:1 { print(@); clear(@); } interval:s:10 { exit(); }'
Attaching 5 probes...
@[0]: 12122
@[0]: 12888
@[0]: 13011
@[0]: 13348
@[0]: 11461
@[0]: 10637
@[0]: 10892
@[0]: 12334
@[0]: 11565
@[0]: 11596

Typically that contention only lasts a couple microseconds.  But the long tail can sometimes be much slower.  Details here: https://gitlab.com/gitlab-com/gl-infra/scalability/-/issues/2301#note_1365159507.

Yes, there are unrelated obstacles to doing so -- that's a separate can of worms, unfortunately.  But in this pathology, even if we used prepared statements, the backend would still need to reacquire the same locks during each executing transaction.  So in terms of lock acquisition rate, whether it's via the planner or executor doing it, the same relations have to be locked.