| From: | "Zhou, Zhiguo" <zhiguo(dot)zhou(at)intel(dot)com> |
|---|---|
| To: | Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru>, Andres Freund <andres(at)anarazel(dot)de> |
| Cc: | "pgsql-hackers(at)lists(dot)postgresql(dot)org" <pgsql-hackers(at)lists(dot)postgresql(dot)org>, "Li, Tianyou" <tianyou(dot)li(at)intel(dot)com>, "Kim, Andrew" <andrew(dot)kim(at)intel(dot)com> |
| Subject: | Re: Increase NUM_XLOGINSERT_LOCKS |
| Date: | 2026-11-19 15:23:22 |
| Message-ID: | CY8PR11MB78980C35DACF535AFA3845ABEF792@CY8PR11MB7898.namprd11.prod.outlook.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi Yura, Andres and all,
I would like to follow up on the insertion-lock discussion. Andres's
large-record counterexample remains relevant: increasing the lock count
can reduce throughput, and removing the reservation spinlock does not
eliminate the cost of checking and waiting for insertions.
Attached is the v1 wal_insert_locks GUC patch for CF 6193, based on
master 4280abf. Instead of v0's fixed increase to 64, it keeps the default
at 8 and accepts powers of two from 4 to 128 at server start. Selection
uses a bitmask, addressing the earlier concern about integer division
in the hot path. Invalid values are rejected rather than rounded.
The upper bound is below
MAX_SIMUL_LWLOCKS (200), since exclusive WAL operations acquire all
insertion locks.
This patch applies independently and does not require CAS128. The
CAS128 reservation patch is being submitted separately in its existing
thread [1], tracked by CF 5633. Each CF entry has its own attachment.
Andrew Kim reviewed and tested both patches off-list on 5ffc821,
including the GUC validation hook. This patch carries his Reviewed-by
and Tested-by trailers.
With the separate CAS128 patch applied, his HammerDB comparison with
unmodified master8 measured -0.2% at the default 8 locks and +40.4% at
64 locks. These are combined CAS128 + GUC results. He used Intel Xeon
CPUs, 2 sockets / 384 threads, GCC 14.2 with -O2 -mcx16, 250 warehouses /
256 VUs, 2 minutes ramp-up and 3 minutes measurement, and 3 mirrored
blocks. WAL was on tmpfs, with no checkpoints in the measured windows.
The reservation post includes the compile-time-64 and earlier hash/CAS
comparisons.
The 1 MiB regression remains with higher lock counts. A separate
writer diagnostic limits both the wait and actual write target to the
same batch boundary; I would like to discuss that independently.
Would a bounded startup setting be preferable to increasing the fixed
default, given the gains and counterexamples? I would appreciate review
of the setting, its bounds and its validation hook here; CAS128 review
can continue in [1].
Regards,
Zhiguo
[1] https://postgr.es/m/flat/27caec92-1c53-4855-9cea-3427af099c28(at)intel(dot)com
________________________________________
From: Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru>
Sent: Saturday, January 18, 2025 7:53 PM
To: Andres Freund <andres(at)anarazel(dot)de>
Cc: pgsql-hackers(at)lists(dot)postgresql(dot)org <pgsql-hackers(at)lists(dot)postgresql(dot)org>; Zhou, Zhiguo <zhiguo(dot)zhou(at)intel(dot)com>; wenhui qiu <qiuwenhuifx(at)gmail(dot)com>
Subject: Re: Increase NUM_XLOGINSERT_LOCKS
Since it seems Andres missed my request to send answer's copy,
here it is:
On 2025-01-16 18:55:47 +0300, Yura Sokolov wrote:
> 16.01.2025 18:36, Andres Freund пишет:
>> 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).
Oops, I swapped the results around when reformatting the results, sorry!
It's
the opposite way. I.e. increasing the locks hurts.
Here's that issue fixed and a few more NUM_XLOGINSERT_LOCKS. This is a
slightly different disk (the other seems to have to go the way of the dodo),
so the results aren't expected to be exactly the same.
NUM_XLOGINSERT_LOCKS TPS
1 2583
2 2524
4 2711
8 2788
16 1938
32 1834
64 1865
128 1543
>>
>> 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.
I know that there's that thread, I just don't see how we can increase
NUM_XLOGINSERT_LOCKS due to the regressions it can cause.
>> 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.
But LWLockWaitForVar is called from WaitXLogInsertionsToFinish, which just
iterates over all locks.
Greetings,
Andres Freund
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-wal-insert-locks-4280abf.patch | application/octet-stream | 14.8 KB |
| From | Date | Subject | |
|---|---|---|---|
| Previous Message | Nathan Bossart | 2026-11-19 15:21:49 | Re: Autovacuum freeze scores wrap around for just-vacuumed tables |