Re: Configurable FP_LOCK_SLOTS_PER_BACKEND

On Mon, Aug 7, 2023 at 6:51 AM Tomas Vondra
<tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
> The regression appears to be consistently ~3%, and v2 aimed to improve
> that - at least for the case with just 100 rows. It even gains ~5% in a
> couple cases. It's however a bit strange v2 doesn't really help the two
> larger cases.

To me, that's an absolutely monumental regression for a change like
this. Sure, lots of people have partitioned tables. But also, lots of
people don't. Penalizing very simple queries by 2-3% seems completely
over the top to me. I can't help wondering whether there's actually
something wrong with the test, or the coding, because that seems huge
to me.

I would also argue that the results are actually not that great,
because once you get past 64 partitions you're right back where you
started, or maybe worse off. To me, there's nothing magical about
cases between 16 and 64 relations that makes them deserve special
treatment - plenty of people are going to want to use hundreds of
partitions, and even if you only use a few dozen, this isn't going to
help as soon as you join two or three partitioned tables, and I
suspect it hurts whenever it doesn't help.

I think we need a design that scales better. I don't really know what
that would look like, exactly, but your idea of a hybrid approach
seems like it might be worth further consideration. We don't have to
store an infinite number of fast-path locks in an array that we search
linearly, and it might be better that if we moved to some other
approach we could avoid some of the regression. You mentioned a hash
table; a partially associative cache might also be worth considering,
like having an array of 1k slots but dividing it logically into 128
bins of 16 slots each and only allowing an OID to be recorded in the
bin whose low 7 bits match the low 7 bits of the OID.

But maybe first we need to understand where all the CPU cycles are
going, because maybe that's optimizing completely the wrong thing and,
again, it seems like an awfully large hit.

Of course, another thing we could do is try to improve the main lock
manager somehow. I confess that I don't have a great idea for that at
the moment, but the current locking scheme there is from a very, very
long time ago and clearly wasn't designed with modern hardware in
mind.

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