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

On Tue, Sep 3, 2024 at 12:19 PM Tomas Vondra <tomas(at)vondra(dot)me> wrote:
> > Doing some worst case math, suppose somebody has max_connections=1000
> > (which is near the upper limit of what I'd consider a sane setting)
> > and max_locks_per_transaction=10000 (ditto). The product is 10
> > million, so every 10 bytes of storage each a gigabyte of RAM. Chewing
> > up 15GB of RAM when you could have chewed up only 0.5GB certainly
> > isn't too great. On the other hand, those values are kind of pushing
> > the limits of what is actually sane. If you imagine
> > max_locks_per_transaction=2000 rather than
> > max_locks_per_connection=10000, then it's only 3GB and that's
> > hopefully not a lot on the hopefully-giant machine where you're
> > running this.
>
> Yeah, although I don't quite follow the math. With 1000/10000 settings,
> why would that eat 15GB of RAM? I mean, that's 1.5GB, right?

Oh, right.

> FWIW the actual cost is somewhat higher, because we seem to need ~400B
> for every lock (not just the 150B for the LOCK struct). At least based
> on a quick experiment. (Seems a bit high, right?).

Hmm, yes, that's unpleasant.

> Perhaps. I agree we'll probably need something more radical soon, not
> just changes that aim to fix some rare exceptional case (which may be
> annoying, but not particularly harmful for the complete workload).
>
> For example, if we did what you propose, that might help when very few
> transactions need a lot of locks. I don't mind saving memory in that
> case, ofc. but is it a problem if those rare cases are a bit slower?
> Shouldn't we focus more on cases where many locks are common? Because
> people are simply going to use partitioning, a lot of indexes, etc?
>
> So yeah, I agree we probably need a more fundamental rethink. I don't
> think we can just keep optimizing the current approach, there's a limit
> of fast it can be. Whether it's not locking individual partitions, or
> not locking some indexes, ... I don't know.

I don't know, either. We don't have to decide right now; it's just
something to keep in mind.

--
Robert Haas
EDB: http://www.enterprisedb.com