On Tue, Sep 3, 2024 at 12:19 PM Tomas Vondra <to...@vondra.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
