Re: heavily contended lwlocks with long wait queues scale badly

On Thu, Jan 18, 2024 at 7:17 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
>
> On Tue, Jan 16, 2024 at 03:11:48PM +0900, Michael Paquier wrote:
> > I'd like to apply that, just let me know if you have any comments
> > and/or objections.
>
> And done on 12~15.

Hi Michael, just to reassure you that it is a good thing. We have a
customer who reported much better performance on 16.x than on 13~15 in
very heavy duty LWLock/lockmanager scenarios (ofc, before that was
committed/released), so I gave it a try here today to see how much can
be attributed to that single commit.

Given:
# $s=10, $p=10,100, DURATION=10s, m=prepared,simple, no reruns , just
single $DURATION run to save time
pgbench -i -s $s --partitions $p $DBNAME
ALTER TABLE pgbench_accounts ADD COLUMN aid_parent INT;
UPDATE pgbench_accounts SET aid_parent = aid
CREATE INDEX ON pgbench_accounts(aid_parent)

pgbench -n -M $m -T $DURATION -c $c -j $c -f join.sql $DBNAME

join.sql was:
\set aid random(1, 100000 * :scale)
select * from pgbench_accounts pa join pgbench_branches pb on pa.bid =
pb.bid where pa.aid_parent = :aid;

see attached results.The benefits are observable (at least when active
working sessions >= VCPUs [threads not cores]) and give up to ~2.65x
boost in certain cases at least for this testcase. Hopefully others
will find it useful.

-J.