Message-ID: <d9a55ded-a102-43e6-81fa-a04c378f91d4@vondra.me>
Date: Wed, 4 Sep 2024 17:32:55 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: scalability bottlenecks with (many) partitions (and more)
To: Matthias van de Meent <boekewurm+postgres@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Andres Freund <andres@anarazel.de>
References: <510b887e-c0ce-4a0c-a17a-2c6abb8d9a5c@enterprisedb.com>
 <CA+TgmoabtPvTffKQiqKg9qOmyt-sYzjQW+TyFWf1CBN7M4hggQ@mail.gmail.com>
 <9266c8df-0df7-4605-ba5a-b204c5e52586@enterprisedb.com>
 <52afd566-d110-43ea-ae72-69349d4414f6@vondra.me>
 <cba2406a-66bb-41ac-b1cf-bb898596e0e1@vondra.me>
 <CA+TgmoZ-40G7-WoLSsEH+7FqUzrP_0ndCOkewJ7ifAO6MVx0EA@mail.gmail.com>
 <b8c43eda-0c3f-4cb4-809b-841fa5c40ada@vondra.me>
 <CA+TgmobCBse3LYj9pfh-ELbP0VnffQS7jP_vNTGA7-KZP0gqDw@mail.gmail.com>
 <52f026c4-6c4e-4de2-bd78-609091d7c772@vondra.me>
 <CAEze2Wip6pN-e2ukGHhtCVBLfSQ91TVGOmiKx7MmAZBiStdsaw@mail.gmail.com>
Content-Language: en-US
From: Tomas Vondra <tomas@vondra.me>
In-Reply-To: <CAEze2Wip6pN-e2ukGHhtCVBLfSQ91TVGOmiKx7MmAZBiStdsaw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/d9a55ded-a102-43e6-81fa-a04c378f91d4%40vondra.me>
Precedence: bulk

On 9/4/24 16:25, Matthias van de Meent wrote:
> On Tue, 3 Sept 2024 at 18:20, Tomas Vondra <tomas@vondra.me> wrote:
>> FWIW the actual cost is somewhat higher, because we seem to need ~400B
>> for every lock (not just the 150B for the LOCK struct).
> 
> We do indeed allocate two PROCLOCKs for every LOCK, and allocate those
> inside dynahash tables. That amounts to (152+2*64+3*16=) 328 bytes in
> dynahash elements, and (3 * 8-16) = 24-48 bytes for the dynahash
> buckets/segments, resulting in 352-376 bytes * NLOCKENTS() being
> used[^1]. Does that align with your usage numbers, or are they
> significantly larger?
> 

I see more like ~470B per lock. If I patch CalculateShmemSize to log the
shmem allocated, I get this:

  max_connections=100 max_locks_per_transaction=1000 => 194264001
  max_connections=100 max_locks_per_transaction=2000 => 241756967

and (((241756967-194264001)/100/1000)) = 474

Could be alignment of structs or something, not sure.

>> At least based on a quick experiment. (Seems a bit high, right?).
> 
> Yeah, that does seem high, thanks for nerd-sniping me.
> 
> The 152 bytes of LOCK are mostly due to a combination of two
> MAX_LOCKMODES-sized int[]s that are used to keep track of the number
> of requested/granted locks of each level. As MAX_LOCKMODES = 10, these
> arrays use a total of 2*4*10=80 bytes, with the remaining 72 spent on
> tracking. MAX_BACKENDS sadly doesn't fit in int16, so we'll have to
> keep using int[]s, but that doesn't mean we can't improve this size:
> 
> ISTM that MAX_LOCKMODES is 2 larger than it has to be: LOCKMODE=0 is
> NoLock, which is never used or counted in these shared structures, and
> the max lock mode supported by any of the supported lock methods is
> AccessExclusiveLock (8). We can thus reduce MAX_LOCKMODES to 8,
> reducing size of the LOCK struct by 16 bytes.
> 
> If some struct- and field packing is OK, then we could further reduce
> the size of LOCK by an additional 8 bytes by resizing the LOCKMASK
> type from int to int16 (we only use the first MaxLockMode (8) + 1
> bits), and then storing the grant/waitMask fields (now 4 bytes total)
> in the padding that's present at the end of the waitProcs struct. This
> would depend on dclist not writing in its padding space, but I
> couldn't find any user that did so, and most critically dclist_init
> doesn't scribble in the padding with memset.
> 
> If these are both implemented, it would save 24 bytes, reducing the
> struct to 128 bytes. :) [^2]
> 
> I also checked PROCLOCK: If it is worth further packing the struct, we
> should probably look at whether it's worth replacing the PGPROC* typed
> fields with ProcNumber -based ones, potentially in both PROCLOCK and
> PROCLOCKTAG. When combined with int16-typed LOCKMASKs, either one of
> these fields being replaced with ProcNumber would allow a reduction in
> size by one MAXALIGN quantum, reducing the struct to 56 bytes, the
> smallest I could get it to without ignoring type alignments.
> 
> Further shmem savings can be achieved by reducing the "10% safety
> margin" added at the end of LockShmemSize, as I'm fairly sure the
> memory used in shared hashmaps doesn't exceed the estimated amount,
> and if it did then we should probably fix that part, rather than
> requesting that (up to) 10% overhead here.
> 
> Alltogether that'd save 40 bytes/lock entry on size, and ~35
> bytes/lock on "safety margin", for a saving of (up to) 19% of our
> current allocation. I'm not sure if these tricks would benefit with
> performance or even be a demerit, apart from smaller structs usually
> being better at fitting better in CPU caches.
> 

Not sure either, but it seems worth exploring. If you do an experimental
patch for the LOCK size reduction, I can get some numbers.

I'm not sure about the safety margins. 10% sure seems like quite a bit
of memory (it might not have in the past, but as the instances are
growing, that probably changed).


regards

-- 
Tomas Vondra