Message-ID: <b706d9ee-9b2b-48ad-9b0b-d4812416ecfc@postgrespro.ru>
Date: Fri, 17 Jan 2025 11:28:38 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Increase NUM_XLOGINSERT_LOCKS
To: Andres Freund <andres@anarazel.de>
Cc: "pgsql-hackers@lists.postgresql.org"
 <pgsql-hackers@lists.postgresql.org>, "Zhou, Zhiguo"
 <zhiguo.zhou@intel.com>, wenhui qiu <qiuwenhuifx@gmail.com>
References: <3b11fdc2-9793-403d-b3d4-67ff9a00d447@postgrespro.ru>
 <6ykez6chr5wfiveuv2iby236mb7ab6fqwpxghppdi5ugb4kdyt@lkrn4maox2wj>
Content-Language: en-US
From: Yura Sokolov <y.sokolov@postgrespro.ru>
In-Reply-To: <6ykez6chr5wfiveuv2iby236mb7ab6fqwpxghppdi5ugb4kdyt@lkrn4maox2wj>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/b706d9ee-9b2b-48ad-9b0b-d4812416ecfc%40postgrespro.ru>
Precedence: bulk

Excuse me, Andres, I've found I've pressed wrong button when I sent this 
letter first time, and it was sent only to you. So I'm sending the copy now.

Please, reply to this message with copy of your answer. Your answer is 
really valuable to be published in the list.

16.01.2025 18:36, Andres Freund wrote:
 > Hi,
 >
 > On 2025-01-16 16:52:46 +0300, Yura Sokolov wrote:
 >> Good day, hackers.
 >>
 >> Zhiguo Zhow proposed to transform xlog reservation to lock-free 
algorighm to
 >> increment NUM_XLOGINSERT_LOCKS on very huge (480vCPU) servers. [1]
 >>
 >> While I believe lock-free reservation make sense on huge server, it 
is hard
 >> to measure on small servers and personal computers/notebooks.
 >>
 >> But increase of NUM_XLOGINSERT_LOCKS have measurable performance 
gain (using
 >> synthetic test) even on my working notebook:
 >>
 >>    Ryzen-5825U (8 cores, 16 threads) limited to 2GHz , Ubuntu 24.04
 >
 > I've experimented with this in the past.
 >
 >
 > Unfortunately increasing it substantially can make the contention on the
 > spinlock *substantially* worse.
 >
 > c=80 && psql -c checkpoint -c 'select pg_switch_wal()' && pgbench -n 
-M prepared -c$c -j$c -f <(echo "SELECT pg_logical_emit_message(true, 
'test', repeat('0', 1024*1024));";) -P1 -T15
 >
 > On a 2x Xeon Gold 5215, with max_wal_size = 150GB and the workload 
ran a few
 > times to ensure WAL is already allocated.
 >
 > With
 > NUM_XLOGINSERT_LOCKS = 8:       1459 tps
 > NUM_XLOGINSERT_LOCKS = 80:      2163 tps

So, even in your test you have +50% gain from increasing 
NUM_XLOGINSERT_LOCKS.

(And that is why I'm keen on smaller increase, like upto 64, not 128).

 >
 > The main reason is that the increase in insert locks puts a lot more 
pressure
 > on the spinlock.

That it addressed by Zhiguo Zhow and me in other thread [1]. But 
increasing NUM_XLOGINSERT_LOCKS gives benefits right now (at least on 
smaller installations), and "lock-free reservation" should be measured 
against it.

 > Secondarily it's also that we spend more time iterating
 > through the insert locks when waiting, and that that causes a lot of 
cacheline
 > pingpong.

Waiting is done with LWLockWaitForVar, and there is no wait if 
`insertingAt` is in future. It looks very efficient in master branch code.

 > On much larger machines this gets considerably worse. IIRC I saw 
something
 > like an 8x regression on a large machine in the past, but I couldn't 
find the
 > actual numbers anymore, so I wouldn't want to bet on it.

I believe, it should be remeasured.

[1] 
https://postgr.es/m/flat/PH7PR11MB5796659F654F9BE983F3AD97EF142%40PH7PR11MB5796.namprd11.prod.outlook.com

------
regards
Yura