Re: Configurable FP_LOCK_SLOTS_PER_BACKEND - Mailing list pgsql-hackers

On 8/7/23 19:05, Robert Haas wrote:
> On Mon, Aug 7, 2023 at 6:51 AM Tomas Vondra
> <tomas.vondra@enterprisedb.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'm the first to admit the coding (in my patches) is far from perfect,
and this may easily be a consequence of that. My motivation was to get
some quick measurements for the "bad case".

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

That's true, but doesn't that apply to any cache that can overflow? You
could make the same argument about the default value of 16 slots - why
not to have just 8?

FWIW I wasn't really suggesting we should increase the value to 64, I
was just trying to get a better idea of the costs at play (fast-path
cache maintenance and regular locking).

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

Yes, I agree. I don't know if this particular design would be the right
one (1000 elements seems a bit too much for something included right in
PGPROC). But yeah, something that flips from linear search to something
else would be reasonable.

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

Right. We're mostly just guessing what the issue is.

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

No idea, but I'd bet some of the trade offs may need re-evaluation.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company