Message-ID: <8c4a5f06-7476-4646-bb8a-6581a26b0650@enterprisedb.com>
Date: Sun, 18 Feb 2024 17:56:58 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Thoughts about NUM_BUFFER_PARTITIONS
Content-Language: en-US
To: Li Japin <japinli@hotmail.com>
Cc: wenhui qiu <qiuwenhuifx@gmail.com>, Heikki Linnakangas <hlinnaka@iki.fi>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
References: <CAGjGUAKk7tkw2en37EwPkXk1guLe=bLsrMizW-5LMLtH+12dwA@mail.gmail.com>
 <a1444b5b-3e8f-44ca-8834-6d99183522f8@iki.fi>
 <CAGjGUAKeTV2SUmWO90dqZgQjpqYUEMOL1==1fDVFc=+6ZJ8hhQ@mail.gmail.com>
 <7335b43e-6d79-4e8c-a33b-e4e864a0a46c@enterprisedb.com>
 <5C838C5E-D89C-495C-A0E2-87FC683462CD@hotmail.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: <5C838C5E-D89C-495C-A0E2-87FC683462CD@hotmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/8c4a5f06-7476-4646-bb8a-6581a26b0650%40enterprisedb.com>
Precedence: bulk

On 2/18/24 03:30, Li Japin wrote:
> 
> 
>> On Feb 10, 2024, at 20:15, Tomas Vondra <tomas.vondra@enterprisedb.com> wrote:
>>
>> On 2/8/24 14:27, wenhui qiu wrote:
>>> Hi Heikki Linnakangas
>>>    I think the larger shared buffer  higher the probability of multiple
>>> backend processes accessing the same bucket slot BufMappingLock
>>> simultaneously, （   InitBufTable(NBuffers + NUM_BUFFER_PARTITIONS); When I
>>> have free time, I want to do this test. I have seen some tests, but the
>>> result report is in Chinese
>>>
>>
>> I think Heikki is right this is unrelated to the amount of RAM. The
>> partitions are meant to reduce the number of lock collisions when
>> multiple processes try to map a buffer concurrently. But the machines
>> got much larger in this regard too - in 2006 the common CPUs had maybe
>> 2-4 cores, now it's common to have CPUs with ~100 cores, and systems
>> with multiple of them. OTOH the time spent holing the partition lock
>> should be pretty low, IIRC we pretty much just pin the buffer before
>> releasing it, and the backend should do plenty other expensive stuff. So
>> who knows how many backends end up doing the locking at the same time.
>>
>> OTOH, with 128 partitions it takes just 14 backends to have 50% chance
>> of a conflict, so with enough cores ... But how many partitions would be
>> enough? With 1024 partitions it still takes only 38 backends to get 50%
>> chance of a collision. Better, but considering we now have hundreds of
>> cores, not sure if sufficient.
>>
>> (Obviously, we probably want much lower probability of a collision, I
>> only used 50% to illustrate the changes).
>>
> 
> I find it seems need to change MAX_SIMUL_LWLOCKS if we enlarge the NUM_BUFFER_PARTITIONS,
> I didn’t find any comments to describe the relation between MAX_SIMUL_LWLOCKS and
> NUM_BUFFER_PARTITIONS, am I missing someghing?

IMHO the relationship is pretty simple - MAX_SIMUL_LWLOCKS needs to be
higher than NUM_BUFFER_PARTITIONS, so that the backend can acquire all
the partition locks if needed.

There's other places that acquire a bunch of locks, and all of them need
to be careful not to exceed MAX_SIMUL_LWLOCKS. For example gist has
GIST_MAX_SPLIT_PAGES.


regards

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