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

Hi,

On 2019-12-10 22:44:17 +0100, Jens-Wolfhard Schicke-Uffmann wrote:
> 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.)

I'd strongly recommend changing your architecture. There's *severe*
overhead in that many concurrent active connections (and some in such a
high max_connections setting). It's likely that you'd be much much
better off by putting in a query pooler in front that limits active
transaction to a significantly smaller number. There's only so many CPU
cores, so at some point adding more concurrency just increases the
overall amount of work that needs to be done (due to the overhead of
managing concurrency and context switches).

> > > 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.)

I think we'd need a CPU profile for starters. But that unfortunately
won't be possible on RDS...

Greetings,

Andres Freund