Re: Contention on LWLock buffer_content, due to SHARED lock(?)

Hi,

On Tue, Dec 10, 2019 at 08:44:17AM -0800, Andres Freund wrote:
> > today I observed (on a r5.24xlarge AWS RDS instance, i.e. 96 logical
> > cores) lock contention on a buffer content lock due to taking of a
> > SHARED lock (I think):
> When you say "7000 active transactions" - do you mean to say that you
> have set max_connections to something higher than that, and you actually
> have that many concurrent transactions?
Yes, max connections was 20000, active connections around 7000 at that
time. Unfortunately, I don't have actual numbers of connections in
transactions for that point in time. (We were trying to establish
maximum performance of a larger system.)

> > Semantically, all that lock traffic was superfluous, as the
> > global_config row's key was in no danger of being changed.
> Well, postgres can't know that.
I am aware; it's just an argument for why it might be possible to
shove some optimization there.

> > 1. Does the above analysis sound about right?
> Hard to know without additional data.
What data would be worth recording next time? (Except number of
active transactions, obviously.)


> > 2. If so, would it be worthwhile to develop a solution?
> Possible, but I'm not sure it's worth the complexity.
>
> I'd definitely like to see a proper reproducer and profile for this,
> before investigating further.
I'll see if and when I can include this into my client's project
schedule. Might be a while, but I'll get back to you when I have
a reproducer + profile data (of an up-to-date vanilla Postgres,
not 10.7+AWS aurora patches).


> I think we'd need a very convincing use-case for improvements around the problem
> you outline.
Understood. I'll try to get an iron-clad profile of the problematic case
first.


Regards,
  Drahflow