Re: scalability bottlenecks with (many) partitions (and more)

Hi,

I've spent quite a bit of time trying to identify cases where having
more fast-path lock slots could be harmful, without any luck. I started
with the EPYC machine I used for the earlier tests, but found nothing,
except for a couple cases unrelated to this patch, because it affects
even cases without the patch applied at all. More like random noise or
maybe some issue with the VM (or differences to the VM used earlier). I
pushed the results to githus [1] anyway, if anyone wants to look.

So I switched to my smaller machines, and ran a simple test on master,
with the hard-coded arrays, and with the arrays moves out of PGPROC (and
sized per max_locks_per_transaction).

I was looking for regressions, so I wanted to test a case that can't
benefit from fast-path locking, while paying the costs. So I decided to
do pgbench -S with 4 partitions, because that fits into the 16 slots we
had before, and scale 1 to keep everything in memory. And then did a
couple read-only runs, first with 64 locks/transaction (default), then
with 1024 locks/transaction.

Attached is a shell script I used to collect this - it creates and
removes clusters, so be careful. Should be fairly obvious what it tests
and how.

The results for max_locks_per_transaction=64 look like this (the numbers
are throughput):


  machine      mode  clients   master   built-in   with-guc
  ---------------------------------------------------------
       i5  prepared        1    14970      14991      14981
                           4    51638      51615      51388
             simple        1    14042      14136      14008
                           4    48705      48572      48457
     ------------------------------------------------------
     xeon  prepared        1    13213      13330      13170
                           4    49280      49191      49263
                          16   151413     152268     151560
             simple        1    12250      12291      12316
                           4    45910      46148      45843
                          16   141774     142165     142310

And compared to master

  machine      mode  clients   built-in    with-guc
  -------------------------------------------------
       i5  prepared        1    100.14%     100.08%
                           4     99.95%      99.51%
             simple        1    100.67%      99.76%
                           4     99.73%      99.49%
     ----------------------------------------------
     xeon  prepared        1    100.89%      99.68%
                           4     99.82%      99.97%
                          16    100.56%     100.10%
             simple        1    100.34%     100.54%
                           4    100.52%      99.85%
                          16    100.28%     100.38%

So, no difference whatsoever - it's +/- 0.5%, well within random noise.
And with max_locks_per_transaction=1024 the story is exactly the same:

  machine      mode  clients   master   built-in   with-guc
  ---------------------------------------------------------
       i5  prepared        1    15000      14928      14948
                           4    51498      51351      51504
             simple        1    14124      14092      14065
                           4    48531      48517      48351
     xeon  prepared        1    13384      13325      13290
                           4    49257      49309      49345
                          16   151668     151940     152201
             simple        1    12357      12351      12363
                           4    46039      46126      46201
                          16   141851     142402     142427


  machine      mode  clients   built-in    with-guc
  -------------------------------------------------
       i5  prepared        1     99.52%      99.65%
                           4     99.71%     100.01%
             simple        1     99.77%      99.58%
                           4     99.97%      99.63%
     xeon  prepared        1     99.56%      99.30%
                           4    100.11%     100.18%
                          16    100.18%     100.35%
             simple        1     99.96%     100.05%
                           4    100.19%     100.35%
                          16    100.39%     100.41%

with max_locks_per_transaction=1024, it's fair to expect the fast-path
locking to be quite beneficial. Of course, it's possible the GUC is set
this high because of some rare issue (say, to run pg_dump, which needs
to lock everything).

I did look at docs if anything needs updating, but I don't think so. The
SGML docs only talk about fast-path locking at fairly high level, not
about how many we have etc. Same for src/backend/storage/lmgr/README,
which is focusing on the correctness of fast-path locking, and that's
not changed by this patch.

I also cleaned up (removed) some of the Asserts checking that we got a
valid group / slot index. I don't think this really helped in practice,
once I added asserts to the macros.


Anyway, at this point I'm quite happy with this improvement. I didn't
have any clear plan when to commit this, but I'm considering doing so
sometime next week, unless someone objects or asks for some additional
benchmarks etc.

One thing I'm not quite sure about yet is whether to commit this as a
single change, or the way the attached patches do that, with the first
patch keeping the larger array in PGPROC and the second patch making it
separate and sized on max_locks_per_transaction ... Opinions?



regards

[1] https://github.com/tvondra/pg-lock-scalability-results

-- 
Tomas Vondra