public inbox for [email protected]
help / color / mirror / Atom feedFrom: Robert Haas <[email protected]>
To: Tomas Vondra <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Andres Freund <[email protected]>
Subject: Re: scalability bottlenecks with (many) partitions (and more)
Date: Thu, 5 Sep 2024 12:25:27 -0400
Message-ID: <CA+TgmoamAOj5e4J7WcC9g10AskvVPk5D7JoAvWw8DFnpHMiSQA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<CA+TgmoabtPvTffKQiqKg9qOmyt-sYzjQW+TyFWf1CBN7M4hggQ@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<CA+TgmoZ-40G7-WoLSsEH+7FqUzrP_0ndCOkewJ7ifAO6MVx0EA@mail.gmail.com>
<[email protected]>
<CA+TgmobCBse3LYj9pfh-ELbP0VnffQS7jP_vNTGA7-KZP0gqDw@mail.gmail.com>
<[email protected]>
On Tue, Sep 3, 2024 at 12:19 PM Tomas Vondra <[email protected]> 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
view thread (12+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: scalability bottlenecks with (many) partitions (and more)
In-Reply-To: <CA+TgmoamAOj5e4J7WcC9g10AskvVPk5D7JoAvWw8DFnpHMiSQA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox