Re: heavily contended lwlocks with long wait queues scale badly

On 1/16/24 1:11 AM, Michael Paquier wrote:
> On Thu, Jan 11, 2024 at 09:47:33AM -0500, Jonathan S. Katz wrote:
>> I have similar data sources to Nathan/Michael and I'm trying to avoid piling
>> on, but one case that's interesting occurred after a major version upgrade
>> from PG10 to PG14 on a database supporting a very active/highly concurrent
>> workload. On inspection, it seems like backpatching would help this
>> particularly case.
>>
>> With 10/11 EOL, I do wonder if we'll see more of these reports on upgrade to
>> < PG16.
>>
>> (I was in favor of backpatching prior; opinion is unchanged).
>
> Hearing nothing, I have prepared a set of patches for v12~v15,
> checking all the lwlock paths for all the branches. At the end the
> set of changes look rather sane to me regarding the queue handlings.
>
> I have also run some numbers on all the branches, and the test case
> posted upthread falls off dramatically after 512 concurrent
> connections at the top of all the stable branches :(
>
> For example on REL_12_STABLE with and without the patch attached:
> num v12 v12+patch
> 1 29717.151665 29096.707588
> 2 63257.709301 61889.476318
> 4 127921.873393 124575.901330
> 8 231400.571662 230562.725174
> 16 343911.185351 312432.897015
> 32 291748.985280 281011.787701
> 64 268998.728648 269975.605115
> 128 297332.597018 286449.176950
> 256 243902.817657 240559.122309
> 512 190069.602270 194510.718508
> 768 58915.650225 165714.707198
> 1024 39920.950552 149433.836901
> 2048 16922.391688 108164.301054
> 4096 6229.063321 69032.338708
>
> I'd like to apply that, just let me know if you have any comments
> and/or objections.

Wow. All I can say is that my opinion remains unchanged on going forward
with backpatching.

Looking at the code, I understand an argument for not backpatching given
we modify the struct, but this does seem low-risk/high-reward and should
help PostgreSQL to run better on this higher throughput workloads.

Thanks,

Jonathan