Re: [RFC] Lock-free XLog Reservation from WAL

Hi all,

I am reaching out to solicit your insights and comments on a recent proposal regarding the "Lock-free XLog Reservation from WAL." We have identified some challenges with the current WAL insertions, which require space reservations in the WAL buffer which involve updating two shared-memory statuses in XLogCtlInsert: CurrBytePos (the start position of the current XLog) and PrevBytePos (the prev-link to the previous XLog). Currently, the use of XLogCtlInsert.insertpos_lck ensures consistency but introduces lock contention, hindering the parallelism of XLog insertions.

To address this issue, we propose the following changes:

1. Removal of PrevBytePos: This will allow increments of the CurrBytePos (a single uint64 field) to be implemented with an atomic operation (fetch_add).
2. Updating Prev-Link of next XLog: Based on the fact that the prev-link of the next XLog always points to the head of the current Xlog,we will slightly exceed the reserved memory range of the current XLog to update the prev-link of the next XLog, regardless of which backend acquires the next memory space. The next XLog inserter will wait until its prev-link is updated for CRC calculation before starting its own XLog copy into the WAL.
3. Breaking Sequential Write Convention: Each backend will update the prev-link of its next XLog first, then return to the header position for the current log insertion. This change will reduce the dependency of XLog writes on previous ones (compared with the sequential writes).
4. Revised GetXLogBuffer: To support #3, we need update this function to separate the LSN it intends to access from the LSN it expects to update in the insertingAt field.
5. Increase NUM_XLOGINSERT_LOCKS: With the above changes, increasing NUM_XLOGINSERT_LOCKS, for example to 128, could effectively enhance the parallelism.

The attached patch could pass the regression tests (make check, make check-world), and in the performance test of this POC on SPR (480 vCPU) shows that this optimization could help the TPCC benchmark better scale with the core count and as a result the performance with full cores enabled could be improved by 2.04x.

Before we proceed with further patch validation and refinement work, we are eager to hear the community's thoughts and comments on this optimization so that we can confirm our current work aligns with expectations.

Hi all,

I am reaching out to solicit your insights and comments on a recent proposal regarding the "Lock-free XLog Reservation from WAL." We have identified some challenges with the current WAL insertions, which require space reservations in the WAL buffer which involve updating two shared-memory statuses in XLogCtlInsert: CurrBytePos (the start position of the current XLog) and PrevBytePos (the prev-link to the previous XLog). Currently, the use of XLogCtlInsert.insertpos_lck ensures consistency but introduces lock contention, hindering the parallelism of XLog insertions.

To address this issue, we propose the following changes:

1. Removal of PrevBytePos: This will allow increments of the CurrBytePos (a single uint64 field) to be implemented with an atomic operation (fetch_add).
2. Updating Prev-Link of next XLog: Based on the fact that the prev-link of the next XLog always points to the head of the current Xlog,we will slightly exceed the reserved memory range of the current XLog to update the prev-link of the next XLog, regardless of which backend acquires the next memory space. The next XLog inserter will wait until its prev-link is updated for CRC calculation before starting its own XLog copy into the WAL.
3. Breaking Sequential Write Convention: Each backend will update the prev-link of its next XLog first, then return to the header position for the current log insertion. This change will reduce the dependency of XLog writes on previous ones (compared with the sequential writes).
4. Revised GetXLogBuffer: To support #3, we need update this function to separate the LSN it intends to access from the LSN it expects to update in the insertingAt field.
5. Increase NUM_XLOGINSERT_LOCKS: With the above changes, increasing NUM_XLOGINSERT_LOCKS, for example to 128, could effectively enhance the parallelism.

The attached patch could pass the regression tests (make check, make check-world), and in the performance test of this POC on SPR (480 vCPU) shows that this optimization could help the TPCC benchmark better scale with the core count and as a result the performance with full cores enabled could be improved by 2.04x.

Before we proceed with further patch validation and refinement work, we are eager to hear the community's thoughts and comments on this optimization so that we can confirm our current work aligns with expectations.

This message is a duplicate of PH7PR11MB5796659F654F9BE983F3AD97EF142(at)PH7PR11MB5796(dot)namprd11(dot)prod(dot)outlook(dot)com(dot) Please consider dropping this thread and review the original one instead.

Sorry for your inconvenience.

-----Original Message-----
From: Zhou, Zhiguo <zhiguo(dot)zhou(at)intel(dot)com>
Sent: Thursday, January 2, 2025 3:20 PM
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: [RFC] Lock-free XLog Reservation from WAL

Hi all,

I am reaching out to solicit your insights and comments on a recent proposal regarding the "Lock-free XLog Reservation from WAL." We have identified some challenges with the current WAL insertions, which require space reservations in the WAL buffer which involve updating two shared-memory statuses in XLogCtlInsert: CurrBytePos (the start position of the current XLog) and PrevBytePos (the prev-link to the previous XLog). Currently, the use of XLogCtlInsert.insertpos_lck ensures consistency but introduces lock contention, hindering the parallelism of XLog insertions.

To address this issue, we propose the following changes:

1. Removal of PrevBytePos: This will allow increments of the CurrBytePos (a single uint64 field) to be implemented with an atomic operation (fetch_add).
2. Updating Prev-Link of next XLog: Based on the fact that the prev-link of the next XLog always points to the head of the current Xlog,we will slightly exceed the reserved memory range of the current XLog to update the prev-link of the next XLog, regardless of which backend acquires the next memory space. The next XLog inserter will wait until its prev-link is updated for CRC calculation before starting its own XLog copy into the WAL.
3. Breaking Sequential Write Convention: Each backend will update the prev-link of its next XLog first, then return to the header position for the current log insertion. This change will reduce the dependency of XLog writes on previous ones (compared with the sequential writes).
4. Revised GetXLogBuffer: To support #3, we need update this function to separate the LSN it intends to access from the LSN it expects to update in the insertingAt field.
5. Increase NUM_XLOGINSERT_LOCKS: With the above changes, increasing NUM_XLOGINSERT_LOCKS, for example to 128, could effectively enhance the parallelism.

The attached patch could pass the regression tests (make check, make check-world), and in the performance test of this POC on SPR (480 vCPU) shows that this optimization could help the TPCC benchmark better scale with the core count and as a result the performance with full cores enabled could be improved by 2.04x.

Before we proceed with further patch validation and refinement work, we are eager to hear the community's thoughts and comments on this optimization so that we can confirm our current work aligns with expectations.

02.01.2025 10:05, Zhou, Zhiguo wrote:
> Hi all,
>
> I am reaching out to solicit your insights and comments on a recent proposal regarding the "Lock-free XLog Reservation from WAL." We have identified some challenges with the current WAL insertions, which require space reservations in the WAL buffer which involve updating two shared-memory statuses in XLogCtlInsert: CurrBytePos (the start position of the current XLog) and PrevBytePos (the prev-link to the previous XLog). Currently, the use of XLogCtlInsert.insertpos_lck ensures consistency but introduces lock contention, hindering the parallelism of XLog insertions.
>
> To address this issue, we propose the following changes:
>
> 1. Removal of PrevBytePos: This will allow increments of the CurrBytePos (a single uint64 field) to be implemented with an atomic operation (fetch_add).
> 2. Updating Prev-Link of next XLog: Based on the fact that the prev-link of the next XLog always points to the head of the current Xlog,we will slightly exceed the reserved memory range of the current XLog to update the prev-link of the next XLog, regardless of which backend acquires the next memory space. The next XLog inserter will wait until its prev-link is updated for CRC calculation before starting its own XLog copy into the WAL.
> 3. Breaking Sequential Write Convention: Each backend will update the prev-link of its next XLog first, then return to the header position for the current log insertion. This change will reduce the dependency of XLog writes on previous ones (compared with the sequential writes).
> 4. Revised GetXLogBuffer: To support #3, we need update this function to separate the LSN it intends to access from the LSN it expects to update in the insertingAt field.
> 5. Increase NUM_XLOGINSERT_LOCKS: With the above changes, increasing NUM_XLOGINSERT_LOCKS, for example to 128, could effectively enhance the parallelism.
>
> The attached patch could pass the regression tests (make check, make check-world), and in the performance test of this POC on SPR (480 vCPU) shows that this optimization could help the TPCC benchmark better scale with the core count and as a result the performance with full cores enabled could be improved by 2.04x.
>
> Before we proceed with further patch validation and refinement work, we are eager to hear the community's thoughts and comments on this optimization so that we can confirm our current work aligns with expectations.

Good day, Zhiguo.

Idea looks great.

Minor issue:
- you didn't remove use of `insertpos_lck` from `ReserveXLogSwitch`.

I initially thought it became un-synchronized against
`ReserveXLogInsertLocation`, but looking closer I found it is
synchronized with `WALInsertLockAcquireExclusive`.
Since there are no other `insertpos_lck` usages after your patch, I
don't see why it should exists and be used in `ReserveXLogSwitch`.

Still I'd prefer to see CAS loop in this place to be consistent with
other non-locking access. And it will allow to get rid of
`WALInsertLockAcquireExclusive`, (though probably it is not a big issue).

Major issue:
- `SetPrevRecPtr` and `GetPrevRecPtr` do non-atomic write/read with on
platforms where MAXALIGN != 8 or without native 64 load/store. Branch
with 'memcpy` is rather obvious, but even pointer de-referencing on
"lucky case" is not safe either.

I have no idea how to fix it at the moment.

Readability issue:
- It would be good to add `Assert(ptr >= upto)` into `GetXLogBuffer`.
I had hard time to recognize `upto` is strictly not in the future.
- Certainly, final version have to have fixed and improved comments.
Many patch's ideas are strictly non-obvious. I had hard time to
recognize patch is not a piece of ... (excuse me for the swear sentence).

Indeed, patch is much better than it looks on first sight.
I came with alternative idea yesterday, but looking closer to your patch
today I see it is superior to mine (if atomic access will be fixed).

----

regards,
Yura Sokolov aka funny-falcon

Hi
Thank you for your path，NUM_XLOGINSERT_LOCKS increase to 128，I think it
will be challenged，do we make it guc ？

On Fri, 3 Jan 2025 at 20:36, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru> wrote:

> 02.01.2025 10:05, Zhou, Zhiguo wrote:
> > Hi all,
> >
> > I am reaching out to solicit your insights and comments on a recent
> proposal regarding the "Lock-free XLog Reservation from WAL." We have
> identified some challenges with the current WAL insertions, which require
> space reservations in the WAL buffer which involve updating two
> shared-memory statuses in XLogCtlInsert: CurrBytePos (the start position of
> the current XLog) and PrevBytePos (the prev-link to the previous XLog).
> Currently, the use of XLogCtlInsert.insertpos_lck ensures consistency but
> introduces lock contention, hindering the parallelism of XLog insertions.
> >
> > To address this issue, we propose the following changes:
> >
> > 1. Removal of PrevBytePos: This will allow increments of the CurrBytePos
> (a single uint64 field) to be implemented with an atomic operation
> (fetch_add).
> > 2. Updating Prev-Link of next XLog: Based on the fact that the prev-link
> of the next XLog always points to the head of the current Xlog,we will
> slightly exceed the reserved memory range of the current XLog to update the
> prev-link of the next XLog, regardless of which backend acquires the next
> memory space. The next XLog inserter will wait until its prev-link is
> updated for CRC calculation before starting its own XLog copy into the WAL.
> > 3. Breaking Sequential Write Convention: Each backend will update the
> prev-link of its next XLog first, then return to the header position for
> the current log insertion. This change will reduce the dependency of XLog
> writes on previous ones (compared with the sequential writes).
> > 4. Revised GetXLogBuffer: To support #3, we need update this function to
> separate the LSN it intends to access from the LSN it expects to update in
> the insertingAt field.
> > 5. Increase NUM_XLOGINSERT_LOCKS: With the above changes, increasing
> NUM_XLOGINSERT_LOCKS, for example to 128, could effectively enhance the
> parallelism.
> >
> > The attached patch could pass the regression tests (make check, make
> check-world), and in the performance test of this POC on SPR (480 vCPU)
> shows that this optimization could help the TPCC benchmark better scale
> with the core count and as a result the performance with full cores enabled
> could be improved by 2.04x.
> >
> > Before we proceed with further patch validation and refinement work, we
> are eager to hear the community's thoughts and comments on this
> optimization so that we can confirm our current work aligns with
> expectations.
>
> Good day, Zhiguo.
>
> Idea looks great.
>
> Minor issue:
> - you didn't remove use of `insertpos_lck` from `ReserveXLogSwitch`.
>
> I initially thought it became un-synchronized against
> `ReserveXLogInsertLocation`, but looking closer I found it is
> synchronized with `WALInsertLockAcquireExclusive`.
> Since there are no other `insertpos_lck` usages after your patch, I
> don't see why it should exists and be used in `ReserveXLogSwitch`.
>
> Still I'd prefer to see CAS loop in this place to be consistent with
> other non-locking access. And it will allow to get rid of
> `WALInsertLockAcquireExclusive`, (though probably it is not a big issue).
>
> Major issue:
> - `SetPrevRecPtr` and `GetPrevRecPtr` do non-atomic write/read with on
> platforms where MAXALIGN != 8 or without native 64 load/store. Branch
> with 'memcpy` is rather obvious, but even pointer de-referencing on
> "lucky case" is not safe either.
>
> I have no idea how to fix it at the moment.
>
> Readability issue:
> - It would be good to add `Assert(ptr >= upto)` into `GetXLogBuffer`.
> I had hard time to recognize `upto` is strictly not in the future.
> - Certainly, final version have to have fixed and improved comments.
> Many patch's ideas are strictly non-obvious. I had hard time to
> recognize patch is not a piece of ... (excuse me for the swear sentence).
>
> Indeed, patch is much better than it looks on first sight.
> I came with alternative idea yesterday, but looking closer to your patch
> today I see it is superior to mine (if atomic access will be fixed).
>
> ----
>
> regards,
> Yura Sokolov aka funny-falcon
>
>
>

Hi Yura and Wenhui,

Thanks for kindly reviewing this work!

On 1/3/2025 9:01 PM, wenhui qiu wrote:
> Hi
>     Thank you for your path，NUM_XLOGINSERT_LOCKS increase to 128，I
> think it will be challenged，do we make it guc ？
>

I noticed there have been some discussions (for example, [1] and its
responses) about making NUM_XLOGINSERT_LOCKS a GUC, which seems to be a
controversial proposal. Given that, we may first focus on the lock-free
XLog reservation implementation, and leave the increase of
NUM_XLOGINSERT_LOCKS for a future patch, where we would provide more
quantitative evidence for the various implementations. WDYT?

> On Fri, 3 Jan 2025 at 20:36, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru
> <mailto:y(dot)sokolov(at)postgrespro(dot)ru>> wrote:
>
> Good day, Zhiguo.
>
> Idea looks great.
>
> Minor issue:
> - you didn't remove use of `insertpos_lck` from `ReserveXLogSwitch`.
>
> I initially thought it became un-synchronized against
> `ReserveXLogInsertLocation`, but looking closer I found it is
> synchronized with `WALInsertLockAcquireExclusive`.
> Since there are no other `insertpos_lck` usages after your patch, I
> don't see why it should exists and be used in `ReserveXLogSwitch`.
>
> Still I'd prefer to see CAS loop in this place to be consistent with
> other non-locking access. And it will allow to get rid of
> `WALInsertLockAcquireExclusive`, (though probably it is not a big
> issue).
>

Exactly, it should be safe to remove `insertpos_lck`. And I agree with
you on getting rid of `WALInsertLockAcquireExclusive` with CAS loop
which should significantly reduce the synchronization cost here
especially when we intend to increase NUM_XLOGINSERT_LOCKS. I will try
it in the next version of patch.

> Major issue:
> - `SetPrevRecPtr` and `GetPrevRecPtr` do non-atomic write/read with on
> platforms where MAXALIGN != 8 or without native 64 load/store. Branch
> with 'memcpy` is rather obvious, but even pointer de-referencing on
> "lucky case" is not safe either.
>
> I have no idea how to fix it at the moment.
>

Indeed, non-atomic write/read operations can lead to safety issues in
some situations. My initial thought is to define a bit near the
prev-link to flag the completion of the update. In this way, we could
allow non-atomic or even discontinuous write/read operations on the
prev-link, while simultaneously guaranteeing its atomicity through
atomic operations (as well as memory barriers) on the flag bit. What do
you think of this as a viable solution?

> Readability issue:
> - It would be good to add `Assert(ptr >= upto)` into `GetXLogBuffer`.
> I had hard time to recognize `upto` is strictly not in the future.
> - Certainly, final version have to have fixed and improved comments.
> Many patch's ideas are strictly non-obvious. I had hard time to
> recognize patch is not a piece of ... (excuse me for the swear
> sentence).

Thanks for the suggestion and patience. It's really more readable after
inserting the assertion, I will fix it and improve other comments in the
following patches.

> Indeed, patch is much better than it looks on first sight.
> I came with alternative idea yesterday, but looking closer to your
> patch
> today I see it is superior to mine (if atomic access will be fixed).
>
> ----
>
> regards,
> Yura Sokolov aka funny-falcon
>
>

Regards,
Zhiguo

[1] https://www.postgresql.org/message-id/2266698.1704854297%40sss.pgh.pa.us

HI Zhiguo
Thank you for your reply ,Then you'll have to prove that 128 is the
optimal value, otherwise they'll have a hard time agreeing with you on this
patch.

Thanks

On Mon, Jan 6, 2025 at 2:46 PM Zhou, Zhiguo <zhiguo(dot)zhou(at)intel(dot)com> wrote:

> Hi Yura and Wenhui,
>
> Thanks for kindly reviewing this work!
>
> On 1/3/2025 9:01 PM, wenhui qiu wrote:
> > Hi
> > Thank you for your path，NUM_XLOGINSERT_LOCKS increase to 128，I
> > think it will be challenged，do we make it guc ？
> >
>
> I noticed there have been some discussions (for example, [1] and its
> responses) about making NUM_XLOGINSERT_LOCKS a GUC, which seems to be a
> controversial proposal. Given that, we may first focus on the lock-free
> XLog reservation implementation, and leave the increase of
> NUM_XLOGINSERT_LOCKS for a future patch, where we would provide more
> quantitative evidence for the various implementations. WDYT?
>
>
> > On Fri, 3 Jan 2025 at 20:36, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru
> > <mailto:y(dot)sokolov(at)postgrespro(dot)ru>> wrote:
> >
> > Good day, Zhiguo.
> >
> > Idea looks great.
> >
> > Minor issue:
> > - you didn't remove use of `insertpos_lck` from `ReserveXLogSwitch`.
> >
> > I initially thought it became un-synchronized against
> > `ReserveXLogInsertLocation`, but looking closer I found it is
> > synchronized with `WALInsertLockAcquireExclusive`.
> > Since there are no other `insertpos_lck` usages after your patch, I
> > don't see why it should exists and be used in `ReserveXLogSwitch`.
> >
> > Still I'd prefer to see CAS loop in this place to be consistent with
> > other non-locking access. And it will allow to get rid of
> > `WALInsertLockAcquireExclusive`, (though probably it is not a big
> > issue).
> >
>
> Exactly, it should be safe to remove `insertpos_lck`. And I agree with
> you on getting rid of `WALInsertLockAcquireExclusive` with CAS loop
> which should significantly reduce the synchronization cost here
> especially when we intend to increase NUM_XLOGINSERT_LOCKS. I will try
> it in the next version of patch.
>
>
> > Major issue:
> > - `SetPrevRecPtr` and `GetPrevRecPtr` do non-atomic write/read with
> on
> > platforms where MAXALIGN != 8 or without native 64 load/store. Branch
> > with 'memcpy` is rather obvious, but even pointer de-referencing on
> > "lucky case" is not safe either.
> >
> > I have no idea how to fix it at the moment.
> >
>
> Indeed, non-atomic write/read operations can lead to safety issues in
> some situations. My initial thought is to define a bit near the
> prev-link to flag the completion of the update. In this way, we could
> allow non-atomic or even discontinuous write/read operations on the
> prev-link, while simultaneously guaranteeing its atomicity through
> atomic operations (as well as memory barriers) on the flag bit. What do
> you think of this as a viable solution?
>
>
> > Readability issue:
> > - It would be good to add `Assert(ptr >= upto)` into `GetXLogBuffer`.
> > I had hard time to recognize `upto` is strictly not in the future.
> > - Certainly, final version have to have fixed and improved comments.
> > Many patch's ideas are strictly non-obvious. I had hard time to
> > recognize patch is not a piece of ... (excuse me for the swear
> > sentence).
>
> Thanks for the suggestion and patience. It's really more readable after
> inserting the assertion, I will fix it and improve other comments in the
> following patches.
>
>
> > Indeed, patch is much better than it looks on first sight.
> > I came with alternative idea yesterday, but looking closer to your
> > patch
> > today I see it is superior to mine (if atomic access will be fixed).
> >
> > ----
> >
> > regards,
> > Yura Sokolov aka funny-falcon
> >
> >
>
> Regards,
> Zhiguo
>
>
> [1]
> https://www.postgresql.org/message-id/2266698.1704854297%40sss.pgh.pa.us
>
>

Maybe we could leave the NUM_XLOGINSERT_LOCKS unchanged in this patch,
as it is not a hard dependency of the lock-free algorithm. And when this
patch has been fully accepted, we could then investigate the more proper
way of increasing NUM_XLOGINSERT_LOCKS. WDYT?

On 1/6/2025 4:35 PM, wenhui qiu wrote:
> HI Zhiguo
>     Thank you for your reply ,Then you'll have to prove that 128 is the
> optimal value, otherwise they'll have a hard time agreeing with you on
> this patch.
>
> Thanks
>
> On Mon, Jan 6, 2025 at 2:46 PM Zhou, Zhiguo <zhiguo(dot)zhou(at)intel(dot)com
> <mailto:zhiguo(dot)zhou(at)intel(dot)com>> wrote:
>
> Hi Yura and Wenhui,
>
> Thanks for kindly reviewing this work!
>
> On 1/3/2025 9:01 PM, wenhui qiu wrote:
> > Hi
> >      Thank you for your path，NUM_XLOGINSERT_LOCKS increase to
> 128，I
> > think it will be challenged，do we make it guc ？
> >
>
> I noticed there have been some discussions (for example, [1] and its
> responses) about making NUM_XLOGINSERT_LOCKS a GUC, which seems to be a
> controversial proposal. Given that, we may first focus on the lock-free
> XLog reservation implementation, and leave the increase of
> NUM_XLOGINSERT_LOCKS for a future patch, where we would provide more
> quantitative evidence for the various implementations. WDYT?
>
>
> > On Fri, 3 Jan 2025 at 20:36, Yura Sokolov
> <y(dot)sokolov(at)postgrespro(dot)ru <mailto:y(dot)sokolov(at)postgrespro(dot)ru>
> > <mailto:y(dot)sokolov(at)postgrespro(dot)ru
> <mailto:y(dot)sokolov(at)postgrespro(dot)ru>>> wrote:
> >
> >     Good day, Zhiguo.
> >
> >     Idea looks great.
> >
> >     Minor issue:
> >     - you didn't remove use of `insertpos_lck` from
> `ReserveXLogSwitch`.
> >
> >     I initially thought it became un-synchronized against
> >     `ReserveXLogInsertLocation`, but looking closer I found it is
> >     synchronized with `WALInsertLockAcquireExclusive`.
> >     Since there are no other `insertpos_lck` usages after your
> patch, I
> >     don't see why it should exists and be used in
> `ReserveXLogSwitch`.
> >
> >     Still I'd prefer to see CAS loop in this place to be
> consistent with
> >     other non-locking access. And it will allow to get rid of
> >     `WALInsertLockAcquireExclusive`, (though probably it is not a big
> >     issue).
> >
>
> Exactly, it should be safe to remove `insertpos_lck`. And I agree with
> you on getting rid of `WALInsertLockAcquireExclusive` with CAS loop
> which should significantly reduce the synchronization cost here
> especially when we intend to increase NUM_XLOGINSERT_LOCKS. I will try
> it in the next version of patch.
>
>
> >     Major issue:
> >     - `SetPrevRecPtr` and `GetPrevRecPtr` do non-atomic write/
> read with on
> >     platforms where MAXALIGN != 8 or without native 64 load/
> store. Branch
> >     with 'memcpy` is rather obvious, but even pointer de-
> referencing on
> >     "lucky case" is not safe either.
> >
> >     I have no idea how to fix it at the moment.
> >
>
> Indeed, non-atomic write/read operations can lead to safety issues in
> some situations. My initial thought is to define a bit near the
> prev-link to flag the completion of the update. In this way, we could
> allow non-atomic or even discontinuous write/read operations on the
> prev-link, while simultaneously guaranteeing its atomicity through
> atomic operations (as well as memory barriers) on the flag bit. What do
> you think of this as a viable solution?
>
>
> >     Readability issue:
> >     - It would be good to add `Assert(ptr >= upto)` into
> `GetXLogBuffer`.
> >     I had hard time to recognize `upto` is strictly not in the
> future.
> >     - Certainly, final version have to have fixed and improved
> comments.
> >     Many patch's ideas are strictly non-obvious. I had hard time to
> >     recognize patch is not a piece of ... (excuse me for the swear
> >     sentence).
>
> Thanks for the suggestion and patience. It's really more readable after
> inserting the assertion, I will fix it and improve other comments in
> the
> following patches.
>
>
> >     Indeed, patch is much better than it looks on first sight.
> >     I came with alternative idea yesterday, but looking closer to
> your
> >     patch
> >     today I see it is superior to mine (if atomic access will be
> fixed).
> >
> >     ----
> >
> >     regards,
> >     Yura Sokolov aka funny-falcon
> >
> >
>
> Regards,
> Zhiguo
>
>
> [1] https://www.postgresql.org/message-
> id/2266698.1704854297%40sss.pgh.pa.us <https://www.postgresql.org/
> message-id/2266698.1704854297%40sss.pgh.pa.us>
>

HI Zhiguo
> Maybe we could leave the NUM_XLOGINSERT_LOCKS unchanged in this patch,
> as it is not a hard dependency of the lock-free algorithm. And when this
> patch has been fully accepted, we could then investigate the more proper
> way of increasing NUM_XLOGINSERT_LOCKS. WDYT?
If the value is not a strong dependency, then the best way is not to change
it.

Thanks

On Mon, Jan 6, 2025 at 4:49 PM Zhou, Zhiguo <zhiguo(dot)zhou(at)intel(dot)com> wrote:

> Maybe we could leave the NUM_XLOGINSERT_LOCKS unchanged in this patch,
> as it is not a hard dependency of the lock-free algorithm. And when this
> patch has been fully accepted, we could then investigate the more proper
> way of increasing NUM_XLOGINSERT_LOCKS. WDYT?
>
> On 1/6/2025 4:35 PM, wenhui qiu wrote:
> > HI Zhiguo
> > Thank you for your reply ,Then you'll have to prove that 128 is the
> > optimal value, otherwise they'll have a hard time agreeing with you on
> > this patch.
> >
> > Thanks
> >
> > On Mon, Jan 6, 2025 at 2:46 PM Zhou, Zhiguo <zhiguo(dot)zhou(at)intel(dot)com
> > <mailto:zhiguo(dot)zhou(at)intel(dot)com>> wrote:
> >
> > Hi Yura and Wenhui,
> >
> > Thanks for kindly reviewing this work!
> >
> > On 1/3/2025 9:01 PM, wenhui qiu wrote:
> > > Hi
> > > Thank you for your path，NUM_XLOGINSERT_LOCKS increase to
> > 128，I
> > > think it will be challenged，do we make it guc ？
> > >
> >
> > I noticed there have been some discussions (for example, [1] and its
> > responses) about making NUM_XLOGINSERT_LOCKS a GUC, which seems to
> be a
> > controversial proposal. Given that, we may first focus on the
> lock-free
> > XLog reservation implementation, and leave the increase of
> > NUM_XLOGINSERT_LOCKS for a future patch, where we would provide more
> > quantitative evidence for the various implementations. WDYT?
> >
> >
> > > On Fri, 3 Jan 2025 at 20:36, Yura Sokolov
> > <y(dot)sokolov(at)postgrespro(dot)ru <mailto:y(dot)sokolov(at)postgrespro(dot)ru>
> > > <mailto:y(dot)sokolov(at)postgrespro(dot)ru
> > <mailto:y(dot)sokolov(at)postgrespro(dot)ru>>> wrote:
> > >
> > > Good day, Zhiguo.
> > >
> > > Idea looks great.
> > >
> > > Minor issue:
> > > - you didn't remove use of `insertpos_lck` from
> > `ReserveXLogSwitch`.
> > >
> > > I initially thought it became un-synchronized against
> > > `ReserveXLogInsertLocation`, but looking closer I found it is
> > > synchronized with `WALInsertLockAcquireExclusive`.
> > > Since there are no other `insertpos_lck` usages after your
> > patch, I
> > > don't see why it should exists and be used in
> > `ReserveXLogSwitch`.
> > >
> > > Still I'd prefer to see CAS loop in this place to be
> > consistent with
> > > other non-locking access. And it will allow to get rid of
> > > `WALInsertLockAcquireExclusive`, (though probably it is not a
> big
> > > issue).
> > >
> >
> > Exactly, it should be safe to remove `insertpos_lck`. And I agree
> with
> > you on getting rid of `WALInsertLockAcquireExclusive` with CAS loop
> > which should significantly reduce the synchronization cost here
> > especially when we intend to increase NUM_XLOGINSERT_LOCKS. I will
> try
> > it in the next version of patch.
> >
> >
> > > Major issue:
> > > - `SetPrevRecPtr` and `GetPrevRecPtr` do non-atomic write/
> > read with on
> > > platforms where MAXALIGN != 8 or without native 64 load/
> > store. Branch
> > > with 'memcpy` is rather obvious, but even pointer de-
> > referencing on
> > > "lucky case" is not safe either.
> > >
> > > I have no idea how to fix it at the moment.
> > >
> >
> > Indeed, non-atomic write/read operations can lead to safety issues in
> > some situations. My initial thought is to define a bit near the
> > prev-link to flag the completion of the update. In this way, we could
> > allow non-atomic or even discontinuous write/read operations on the
> > prev-link, while simultaneously guaranteeing its atomicity through
> > atomic operations (as well as memory barriers) on the flag bit. What
> do
> > you think of this as a viable solution?
> >
> >
> > > Readability issue:
> > > - It would be good to add `Assert(ptr >= upto)` into
> > `GetXLogBuffer`.
> > > I had hard time to recognize `upto` is strictly not in the
> > future.
> > > - Certainly, final version have to have fixed and improved
> > comments.
> > > Many patch's ideas are strictly non-obvious. I had hard time
> to
> > > recognize patch is not a piece of ... (excuse me for the swear
> > > sentence).
> >
> > Thanks for the suggestion and patience. It's really more readable
> after
> > inserting the assertion, I will fix it and improve other comments in
> > the
> > following patches.
> >
> >
> > > Indeed, patch is much better than it looks on first sight.
> > > I came with alternative idea yesterday, but looking closer to
> > your
> > > patch
> > > today I see it is superior to mine (if atomic access will be
> > fixed).
> > >
> > > ----
> > >
> > > regards,
> > > Yura Sokolov aka funny-falcon
> > >
> > >
> >
> > Regards,
> > Zhiguo
> >
> >
> > [1] https://www.postgresql.org/message-
> > id/2266698.1704854297%40sss.pgh.pa.us <https://www.postgresql.org/
> > message-id/2266698.1704854297%40sss.pgh.pa.us>
> >
>
>

> On 6 Jan 2025, at 09:46, Zhou, Zhiguo <zhiguo(dot)zhou(at)intel(dot)com> wrote:
>
> Hi Yura and Wenhui,
>
> Thanks for kindly reviewing this work!
>
> On 1/3/2025 9:01 PM, wenhui qiu wrote:
>> Hi
>> Thank you for your path，NUM_XLOGINSERT_LOCKS increase to 128，I think it will be challenged，do we make it guc ？
>
> I noticed there have been some discussions (for example, [1] and its responses) about making NUM_XLOGINSERT_LOCKS a GUC, which seems to be a controversial proposal. Given that, we may first focus on the lock-free XLog reservation implementation, and leave the increase of NUM_XLOGINSERT_LOCKS for a future patch, where we would provide more quantitative evidence for the various implementations. WDYT?
>
>
>> On Fri, 3 Jan 2025 at 20:36, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru <mailto:y(dot)sokolov(at)postgrespro(dot)ru><mailto:y(dot)sokolov(at)postgrespro(dot)ru>> wrote:
>> Good day, Zhiguo.
>> Idea looks great.
>> Minor issue:
>> - you didn't remove use of `insertpos_lck` from `ReserveXLogSwitch`.
>> I initially thought it became un-synchronized against
>> `ReserveXLogInsertLocation`, but looking closer I found it is
>> synchronized with `WALInsertLockAcquireExclusive`.
>> Since there are no other `insertpos_lck` usages after your patch, I
>> don't see why it should exists and be used in `ReserveXLogSwitch`.
>> Still I'd prefer to see CAS loop in this place to be consistent with
>> other non-locking access. And it will allow to get rid of
>> `WALInsertLockAcquireExclusive`, (though probably it is not a big
>> issue).
>
> Exactly, it should be safe to remove `insertpos_lck`. And I agree with you on getting rid of `WALInsertLockAcquireExclusive` with CAS loop which should significantly reduce the synchronization cost here especially when we intend to increase NUM_XLOGINSERT_LOCKS. I will try it in the next version of patch.
>
>
>> Major issue:
>> - `SetPrevRecPtr` and `GetPrevRecPtr` do non-atomic write/read with on
>> platforms where MAXALIGN != 8 or without native 64 load/store. Branch
>> with 'memcpy` is rather obvious, but even pointer de-referencing on
>> "lucky case" is not safe either.
>> I have no idea how to fix it at the moment.
>
> Indeed, non-atomic write/read operations can lead to safety issues in some situations. My initial thought is to define a bit near the prev-link to flag the completion of the update. In this way, we could allow non-atomic or even discontinuous write/read operations on the prev-link, while simultaneously guaranteeing its atomicity through atomic operations (as well as memory barriers) on the flag bit. What do you think of this as a viable solution?
>
>
>> Readability issue:
>> - It would be good to add `Assert(ptr >= upto)` into `GetXLogBuffer`.
>> I had hard time to recognize `upto` is strictly not in the future.
>> - Certainly, final version have to have fixed and improved comments.
>> Many patch's ideas are strictly non-obvious. I had hard time to
>> recognize patch is not a piece of ... (excuse me for the swear
>> sentence).
>
> Thanks for the suggestion and patience. It's really more readable after inserting the assertion, I will fix it and improve other comments in the following patches.
>
>
>> Indeed, patch is much better than it looks on first sight.
>> I came with alternative idea yesterday, but looking closer to your
>> patch
>> today I see it is superior to mine (if atomic access will be fixed).
>
> [1] https://www.postgresql.org/message-id/2266698.1704854297%40sss.pgh.pa.us

Good day, Zhiguo.

Here’s my attempt to organise link to previous record without messing with xlog buffers:
- link is stored in lock-free hash table instead.

I don’t claim it is any better than using xlog buffers.
It is just alternative vision.

Some tricks in implementation:
- Relying on byte-position nature, it could be converted to 32 bit unique
value with `(uint32)(pos ^ (pos>>32))`. Certainly it is not totally unique,
but it is certainly unique among 32GB consecutive log.
- PrevBytePos could be calculated as a difference between positions, and
this difference is certainly less than 4GB, so it also could be stored as 32
bit value (PrevSize).
- Since xlog records are aligned we could use lowest bit of PrevSize as a lock.
- While Cuckoo Hashing could suffer from un-solvable cycle conflicts, this implementation relies on concurrent deleters which will eventually break such cycles if any.

I have a version without 32bit conversion trick, and it is a bit lighter on atomic instructions count, but it performs badly in absence of native 64bit atomics.

——
regards
Yura Sokolov aka funny-falcon


On 1/7/2025 10:49 AM, Юрий Соколов wrote:
>
>> On 6 Jan 2025, at 09:46, Zhou, Zhiguo <zhiguo(dot)zhou(at)intel(dot)com> wrote:
>>
>> Hi Yura and Wenhui,
>>
>> Thanks for kindly reviewing this work!
>>
>> On 1/3/2025 9:01 PM, wenhui qiu wrote:
>>> Hi
>>> Thank you for your path，NUM_XLOGINSERT_LOCKS increase to 128，I
>>> think it will be challenged，do we make it guc ？
>>
>> I noticed there have been some discussions (for example, [1] and its
>> responses) about making NUM_XLOGINSERT_LOCKS a GUC, which seems to be
>> a controversial proposal. Given that, we may first focus on the lock-
>> free XLog reservation implementation, and leave the increase of
>> NUM_XLOGINSERT_LOCKS for a future patch, where we would provide more
>> quantitative evidence for the various implementations. WDYT?
>>
>>
>>> On Fri, 3 Jan 2025 at 20:36, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru
>>> <mailto:y(dot)sokolov(at)postgrespro(dot)ru><mailto:y(dot)sokolov(at)postgrespro(dot)ru
>>> <mailto:y(dot)sokolov(at)postgrespro(dot)ru>>> wrote:
>>>    Good day, Zhiguo.
>>>    Idea looks great.
>>>    Minor issue:
>>>    - you didn't remove use of `insertpos_lck` from `ReserveXLogSwitch`.
>>>    I initially thought it became un-synchronized against
>>>    `ReserveXLogInsertLocation`, but looking closer I found it is
>>>    synchronized with `WALInsertLockAcquireExclusive`.
>>>    Since there are no other `insertpos_lck` usages after your patch, I
>>>    don't see why it should exists and be used in `ReserveXLogSwitch`.
>>>    Still I'd prefer to see CAS loop in this place to be consistent with
>>>    other non-locking access. And it will allow to get rid of
>>>    `WALInsertLockAcquireExclusive`, (though probably it is not a big
>>>    issue).
>>
>> Exactly, it should be safe to remove `insertpos_lck`. And I agree with
>> you on getting rid of `WALInsertLockAcquireExclusive` with CAS loop
>> which should significantly reduce the synchronization cost here
>> especially when we intend to increase NUM_XLOGINSERT_LOCKS. I will try
>> it in the next version of patch.
>>
>>
>>>    Major issue:
>>>    - `SetPrevRecPtr` and `GetPrevRecPtr` do non-atomic write/read with on
>>>    platforms where MAXALIGN != 8 or without native 64 load/store. Branch
>>>    with 'memcpy` is rather obvious, but even pointer de-referencing on
>>>    "lucky case" is not safe either.
>>>    I have no idea how to fix it at the moment.
>>
>> Indeed, non-atomic write/read operations can lead to safety issues in
>> some situations. My initial thought is to define a bit near the prev-
>> link to flag the completion of the update. In this way, we could allow
>> non-atomic or even discontinuous write/read operations on the prev-
>> link, while simultaneously guaranteeing its atomicity through atomic
>> operations (as well as memory barriers) on the flag bit. What do you
>> think of this as a viable solution?
>>
>>
>>>    Readability issue:
>>>    - It would be good to add `Assert(ptr >= upto)` into `GetXLogBuffer`.
>>>    I had hard time to recognize `upto` is strictly not in the future.
>>>    - Certainly, final version have to have fixed and improved comments.
>>>    Many patch's ideas are strictly non-obvious. I had hard time to
>>>    recognize patch is not a piece of ... (excuse me for the swear
>>>    sentence).
>>
>> Thanks for the suggestion and patience. It's really more readable
>> after inserting the assertion, I will fix it and improve other
>> comments in the following patches.
>>
>>
>>>    Indeed, patch is much better than it looks on first sight.
>>>    I came with alternative idea yesterday, but looking closer to your
>>>    patch
>>>    today I see it is superior to mine (if atomic access will be fixed).
>>
>> [1]https://www.postgresql.org/message-
>> id/2266698.1704854297%40sss.pgh.pa.us <https://www.postgresql.org/
>> message-id/2266698.1704854297%40sss.pgh.pa.us>
>
> Good day, Zhiguo.
>
> Here’s my attempt to organise link to previous record without messing
> with xlog buffers:
> - link is stored in lock-free hash table instead.
>
> I don’t claim it is any better than using xlog buffers.
> It is just alternative vision.
>
> Some tricks in implementation:
> - Relying on byte-position nature, it could be converted to 32 bit unique
>   value with `(uint32)(pos ^ (pos>>32))`. Certainly it is not totally
> unique,
>   but it is certainly unique among 32GB consecutive log.
> - PrevBytePos could be calculated as a difference between positions, and
>   this difference is certainly less than 4GB, so it also could be
> stored as 32
>   bit value (PrevSize).
> - Since xlog records are aligned we could use lowest bit of PrevSize as
> a lock.
> - While Cuckoo Hashing could suffer from un-solvable cycle conflicts,
> this implementation relies on concurrent deleters which will eventually
> break such cycles if any.
>
> I have a version without 32bit conversion trick, and it is a bit lighter
> on atomic instructions count, but it performs badly in absence of native
> 64bit atomics.
>
> ——
> regards
> Yura Sokolov aka funny-falcon

Good day, Yura!

Your implementation based on the lock-free hash table is truly
impressive! One of the aspects I particularly admire is how your
solution doesn't require breaking the current convention of XLog
insertion, whose revision is quite error-prone and ungraceful. My minor
concern is that the limited number of entries (256) in the hash table
would be a bottleneck for parallel memory reservation, but I believe
this is not a critical issue.

I will soon try to evaluate the performance impact of your patch on my
device with the TPCC benchmark and also profile it to see if there are
any changes that could be made to further improve it.

BTW, do you have a plan to merge this patch to the master branch? Thanks!

Regards,
Zhiguo

09.01.2025 19:03, Zhou, Zhiguo пишет:
> On 1/7/2025 10:49 AM, Юрий Соколов wrote:
>>
>>> On 6 Jan 2025, at 09:46, Zhou, Zhiguo <zhiguo(dot)zhou(at)intel(dot)com> wrote:
>>>
>>> Hi Yura and Wenhui,
>>>
>>> Thanks for kindly reviewing this work!
>>>
>>> On 1/3/2025 9:01 PM, wenhui qiu wrote:
>>>> Hi
>>>> Thank you for your path，NUM_XLOGINSERT_LOCKS increase to 128，I
>>>> think it will be challenged，do we make it guc ？
>>>
>>> I noticed there have been some discussions (for example, [1] and its
>>> responses) about making NUM_XLOGINSERT_LOCKS a GUC, which seems to be
>>> a controversial proposal. Given that, we may first focus on the lock-
>>> free XLog reservation implementation, and leave the increase of
>>> NUM_XLOGINSERT_LOCKS for a future patch, where we would provide more
>>> quantitative evidence for the various implementations. WDYT?
>>>
>>>
>>>> On Fri, 3 Jan 2025 at 20:36, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru
>>>> <mailto:y(dot)sokolov(at)postgrespro(dot)ru><mailto:y(dot)sokolov(at)postgrespro(dot)ru
>>>> <mailto:y(dot)sokolov(at)postgrespro(dot)ru>>> wrote:
>>>>    Good day, Zhiguo.
>>>>    Idea looks great.
>>>>    Minor issue:
>>>>    - you didn't remove use of `insertpos_lck` from `ReserveXLogSwitch`.
>>>>    I initially thought it became un-synchronized against
>>>>    `ReserveXLogInsertLocation`, but looking closer I found it is
>>>>    synchronized with `WALInsertLockAcquireExclusive`.
>>>>    Since there are no other `insertpos_lck` usages after your patch, I
>>>>    don't see why it should exists and be used in `ReserveXLogSwitch`.
>>>>    Still I'd prefer to see CAS loop in this place to be consistent with
>>>>    other non-locking access. And it will allow to get rid of
>>>>    `WALInsertLockAcquireExclusive`, (though probably it is not a big
>>>>    issue).
>>>
>>> Exactly, it should be safe to remove `insertpos_lck`. And I agree
>>> with you on getting rid of `WALInsertLockAcquireExclusive` with CAS
>>> loop which should significantly reduce the synchronization cost here
>>> especially when we intend to increase NUM_XLOGINSERT_LOCKS. I will
>>> try it in the next version of patch.
>>>
>>>
>>>>    Major issue:
>>>>    - `SetPrevRecPtr` and `GetPrevRecPtr` do non-atomic write/read
>>>> with on
>>>>    platforms where MAXALIGN != 8 or without native 64 load/store.
>>>> Branch
>>>>    with 'memcpy` is rather obvious, but even pointer de-referencing on
>>>>    "lucky case" is not safe either.
>>>>    I have no idea how to fix it at the moment.
>>>
>>> Indeed, non-atomic write/read operations can lead to safety issues in
>>> some situations. My initial thought is to define a bit near the prev-
>>> link to flag the completion of the update. In this way, we could
>>> allow non-atomic or even discontinuous write/read operations on the
>>> prev- link, while simultaneously guaranteeing its atomicity through
>>> atomic operations (as well as memory barriers) on the flag bit. What
>>> do you think of this as a viable solution?
>>>
>>>
>>>>    Readability issue:
>>>>    - It would be good to add `Assert(ptr >= upto)` into
>>>> `GetXLogBuffer`.
>>>>    I had hard time to recognize `upto` is strictly not in the future.
>>>>    - Certainly, final version have to have fixed and improved comments.
>>>>    Many patch's ideas are strictly non-obvious. I had hard time to
>>>>    recognize patch is not a piece of ... (excuse me for the swear
>>>>    sentence).
>>>
>>> Thanks for the suggestion and patience. It's really more readable
>>> after inserting the assertion, I will fix it and improve other
>>> comments in the following patches.
>>>
>>>
>>>>    Indeed, patch is much better than it looks on first sight.
>>>>    I came with alternative idea yesterday, but looking closer to your
>>>>    patch
>>>>    today I see it is superior to mine (if atomic access will be fixed).
>>>
>>> [1]https://www.postgresql.org/message-
>>> id/2266698.1704854297%40sss.pgh.pa.us <https://www.postgresql.org/
>>> message-id/2266698.1704854297%40sss.pgh.pa.us>
>>
>> Good day, Zhiguo.
>>
>> Here’s my attempt to organise link to previous record without messing
>> with xlog buffers:
>> - link is stored in lock-free hash table instead.
>>
>> I don’t claim it is any better than using xlog buffers.
>> It is just alternative vision.
>>
>> Some tricks in implementation:
>> - Relying on byte-position nature, it could be converted to 32 bit unique
>>    value with `(uint32)(pos ^ (pos>>32))`. Certainly it is not totally
>> unique,
>>    but it is certainly unique among 32GB consecutive log.
>> - PrevBytePos could be calculated as a difference between positions, and
>>    this difference is certainly less than 4GB, so it also could be
>> stored as 32
>>    bit value (PrevSize).
>> - Since xlog records are aligned we could use lowest bit of PrevSize
>> as a lock.
>> - While Cuckoo Hashing could suffer from un-solvable cycle conflicts,
>> this implementation relies on concurrent deleters which will
>> eventually break such cycles if any.
>>
>> I have a version without 32bit conversion trick, and it is a bit
>> lighter on atomic instructions count, but it performs badly in absence
>> of native 64bit atomics.
>>
>> ——
>> regards
>> Yura Sokolov aka funny-falcon
>
>
> Good day, Yura!
>
> Your implementation based on the lock-free hash table is truly
> impressive! One of the aspects I particularly admire is how your
> solution doesn't require breaking the current convention of XLog
> insertion, whose revision is quite error-prone and ungraceful.

That is main benefit of my approach. Though it is not strictly better
than yours.

> My minor
> concern is that the limited number of entries (256) in the hash table
> would be a bottleneck for parallel memory reservation, but I believe
> this is not a critical issue.

If you consider hash-table fillrate, than 256 is quite enough for 128
concurrent inserters.

But I agree 8 items on cache line could lead to false-sharing.
Items could be stretched to 16 bytes (and then CurrPosId could be fully
unique), so there's just 4 entry per cache line.

>
> I will soon try to evaluate the performance impact of your patch on my
> device with the TPCC benchmark and also profile it to see if there are
> any changes that could be made to further improve it.

It would be great. On my notebook (Mac Air M1) I don't see any benefits
neither from mine, nor from yours patch ))
My colleague will also test it on 20 core virtual machine (but
backported to v15).

> BTW, do you have a plan to merge this patch to the master branch? Thanks!

I'm not committer )) We are both will struggle to make something
committed for many months ;-)

BTW, your version could make alike trick for guaranteed atomicity:
- change XLogRecord's `XLogRecPtr xl_prev` to `uint32 xl_prev_offset`
and store offset to prev record's start.

Since there are two limits:

#define XLogRecordMaxSize (1020 * 1024 * 1024)
#define WalSegMaxSize 1024 * 1024 * 1024

offset to previous record could not be larger than 2GB.

Yes, it is format change, that some backup utilities will have to adopt.
But it saves 4 bytes in XLogRecord (that could be spent to store
FullTransactionId instead of TransactionId) and it is better compressible.

And your version than will not need the case when this value is split
among two buffers (since MAXALIGN is not less than 4), and PostgreSQL
already relies on 4 byte read/write atomicity (in some places even
without use of pg_atomic_uint32).

----

regards
Sokolov Yura aka funny-falcon

On Fri, 10 Jan 2025 at 13:42, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>
> BTW, your version could make alike trick for guaranteed atomicity:
> - change XLogRecord's `XLogRecPtr xl_prev` to `uint32 xl_prev_offset`
> and store offset to prev record's start.

-1, I don't think that is possible without degrading what our current
WAL system protects against.

For intra-record torn write protection we have the checksum, but that
same protection doesn't cover the multiple WAL records on each page.
That is what the xl_prev pointer is used for - detecting that this
part of the page doesn't contain the correct data (e.g. the data of a
previous version of this recycled segment).
If we replaced xl_prev with just an offset into the segment, then this
protection would be much less effective, as the previous version of
the segment realistically used the same segment offsets at the same
offsets into the file.

To protect against torn writes while still only using record segment
offsets, you'd have zero and then fsync any segment before reusing it,
which would severely reduce the benefits we get from recycling
segments.
Note that we can't expect the page header to help here, as write tears
can happen at nearly any offset into the page - not just 8k intervals
- and so the page header is not always representative of the origins
of all bytes on the page - only the first 24 (if even that).

Kind regards,

Matthias van de Meent

10.01.2025 19:53, Matthias van de Meent пишет:
> On Fri, 10 Jan 2025 at 13:42, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>>
>> BTW, your version could make alike trick for guaranteed atomicity:
>> - change XLogRecord's `XLogRecPtr xl_prev` to `uint32 xl_prev_offset`
>> and store offset to prev record's start.
>
> -1, I don't think that is possible without degrading what our current
> WAL system protects against.
>
> For intra-record torn write protection we have the checksum, but that
> same protection doesn't cover the multiple WAL records on each page.
> That is what the xl_prev pointer is used for - detecting that this
> part of the page doesn't contain the correct data (e.g. the data of a
> previous version of this recycled segment).
> If we replaced xl_prev with just an offset into the segment, then this
> protection would be much less effective, as the previous version of
> the segment realistically used the same segment offsets at the same
> offsets into the file.

Well, to protect against "torn write" it is enough to have "self-lsn"
field, not "prev-lsn". So 8 byte "self-lsn" + "offset-to-prev" would work.

But this way header will be increased by 4 bytes compared to current
one, not decreased.

Just thought:
If XLogRecord alignment were stricter (for example, 32 bytes), then LSN
could mean not byte-offset, but 32byte-offset. Then low 32bits of LSN
will cover 128GB of WAL logs. For most installations re-use distance for
WAL segments doubdfully longer than 128GB. But I believe, there are some
with larger one. So it is not reliable.

> To protect against torn writes while still only using record segment
> offsets, you'd have zero and then fsync any segment before reusing it,
> which would severely reduce the benefits we get from recycling
> segments.
> Note that we can't expect the page header to help here, as write tears
> can happen at nearly any offset into the page - not just 8k intervals
> - and so the page header is not always representative of the origins
> of all bytes on the page - only the first 24 (if even that).

-----

regards,
Yura

Good day, Yura!

On 1/10/2025 8:42 PM, Yura Sokolov wrote:
> If you consider hash-table fillrate, than 256 is quite enough for 128
> concurrent inserters.

The profile of your patch didn't show significant hotspots in the hash
table functions, so I believe the 256 entries should be enough.

>>
>> I will soon try to evaluate the performance impact of your patch on my
>> device with the TPCC benchmark and also profile it to see if there are
>> any changes that could be made to further improve it.
>
> It would be great. On my notebook (Mac Air M1) I don't see any benefits
> neither from mine, nor from yours patch ))
> My colleague will also test it on 20 core virtual machine (but
> backported to v15).
>

I've tested the performance impact of our patches on an Intel Sapphire
Rapids device with 480 vCPUs using a HammerDB TPC-C workload (256 VUs).
The results show a 72.3% improvement (average of 3 rounds, RSD: 1.5%)
with your patch and a 76.0% boost (average of 3 rounds, RSD: 2.95%) with
mine, applied to the latest codebase. This optimization is most
effective on systems with over 64 cores, as our core-scaling experiments
suggest minimal impact on lower-core setups like your notebook or a
20-core VM.

>
>> BTW, do you have a plan to merge this patch to the master branch? Thanks!
>
> I'm not committer )) We are both will struggle to make something
> committed for many months ;-)
>
> BTW, your version could make alike trick for guaranteed atomicity:
> - change XLogRecord's `XLogRecPtr xl_prev` to `uint32 xl_prev_offset`
> and store offset to prev record's start.
>
> Since there are two limits:
>
>     #define XLogRecordMaxSize    (1020 * 1024 * 1024)
>     #define WalSegMaxSize 1024 * 1024 * 1024
>
> offset to previous record could not be larger than 2GB.
>
> Yes, it is format change, that some backup utilities will have to adopt.
> But it saves 4 bytes in XLogRecord (that could be spent to store
> FullTransactionId instead of TransactionId) and it is better compressible.
>
> And your version than will not need the case when this value is split
> among two buffers (since MAXALIGN is not less than 4), and PostgreSQL
> already relies on 4 byte read/write atomicity (in some places even
> without use of pg_atomic_uint32).
>
> ----
>
> regards
> Sokolov Yura aka funny-falcon

Thanks for the great suggestion!

I think we've arrived at a critical juncture where we need to decide
which patch to move forward with for our optimization efforts. I've
evaluated the pros and cons of my implementation:

Pros:
-Achieves an additional 4% performance improvement.

Cons:
-Breaks the current convention of XLog insertions.
-TAP tests are not fully passed, requiring time to resolve.
-May necessitate changes to the format and backup tools, potentially
leading to backward compatibility issues.

Given these considerations, I believe your implementation is superior to
mine. I'd greatly appreciate it if you could share your insights on this
matter.

Regards,
Zhiguo

14.01.2025 17:49, Zhou, Zhiguo пишет:
> Good day, Yura!
>
> On 1/10/2025 8:42 PM, Yura Sokolov wrote:
>> If you consider hash-table fillrate, than 256 is quite enough for 128
>> concurrent inserters.
>
> The profile of your patch didn't show significant hotspots in the hash
> table functions, so I believe the 256 entries should be enough.
>
>>>
>>> I will soon try to evaluate the performance impact of your patch on
>>> my device with the TPCC benchmark and also profile it to see if there
>>> are any changes that could be made to further improve it.
>>
>> It would be great. On my notebook (Mac Air M1) I don't see any
>> benefits neither from mine, nor from yours patch ))
>> My colleague will also test it on 20 core virtual machine (but
>> backported to v15).
>>
>
> I've tested the performance impact of our patches on an Intel Sapphire
> Rapids device with 480 vCPUs using a HammerDB TPC-C workload (256 VUs).
> The results show a 72.3% improvement (average of 3 rounds, RSD: 1.5%)
> with your patch and a 76.0% boost (average of 3 rounds, RSD: 2.95%) with
> mine, applied to the latest codebase. This optimization is most
> effective on systems with over 64 cores, as our core-scaling experiments
> suggest minimal impact on lower-core setups like your notebook or a 20-
> core VM.
>
>>
>>> BTW, do you have a plan to merge this patch to the master branch?
>>> Thanks!
>>
>> I'm not committer )) We are both will struggle to make something
>> committed for many months ;-)
>>
>> BTW, your version could make alike trick for guaranteed atomicity:
>> - change XLogRecord's `XLogRecPtr xl_prev` to `uint32 xl_prev_offset`
>> and store offset to prev record's start.
>>
>> Since there are two limits:
>>
>>      #define XLogRecordMaxSize    (1020 * 1024 * 1024)
>>      #define WalSegMaxSize 1024 * 1024 * 1024
>>
>> offset to previous record could not be larger than 2GB.
>>
>> Yes, it is format change, that some backup utilities will have to adopt.
>> But it saves 4 bytes in XLogRecord (that could be spent to store
>> FullTransactionId instead of TransactionId) and it is better
>> compressible.
>>
>> And your version than will not need the case when this value is split
>> among two buffers (since MAXALIGN is not less than 4), and PostgreSQL
>> already relies on 4 byte read/write atomicity (in some places even
>> without use of pg_atomic_uint32).
>>
>> ----
>>
>> regards
>> Sokolov Yura aka funny-falcon
>
> Thanks for the great suggestion!
>
> I think we've arrived at a critical juncture where we need to decide
> which patch to move forward with for our optimization efforts. I've
> evaluated the pros and cons of my implementation:
>
> Pros:
> -Achieves an additional 4% performance improvement.
>
> Cons:
> -Breaks the current convention of XLog insertions.
> -TAP tests are not fully passed, requiring time to resolve.
> -May necessitate changes to the format and backup tools, potentially
> leading to backward compatibility issues.
>
> Given these considerations, I believe your implementation is superior to
> mine. I'd greatly appreciate it if you could share your insights on this
> matter.

Good day, Zhiguo.

Excuse me, I feel sneaky a bit, but I've started another thread just
about increase of NUM_XLOGINSERT_LOCK, because I can measure its effect
even on my working notebook (it is another one: Ryzen 5825U limited to
@2GHz).

http://postgr.es/m/flat/3b11fdc2-9793-403d-b3d4-67ff9a00d447%40postgrespro.ru

-----

regards
Yura Sokolov aka funny-falcon

On 1/16/2025 10:00 PM, Yura Sokolov wrote:
>
> Good day, Zhiguo.
>
> Excuse me, I feel sneaky a bit, but I've started another thread just
> about increase of NUM_XLOGINSERT_LOCK, because I can measure its effect
> even on my working notebook (it is another one: Ryzen 5825U limited to
> @2GHz).
>
> http://postgr.es/m/flat/3b11fdc2-9793-403d-
> b3d4-67ff9a00d447%40postgrespro.ru
>
> -----
>
> regards
> Yura Sokolov aka funny-falcon
>
>

Good day, Yura!

Thank you for keeping me informed. I appreciate your proactive approach
and understand the importance of exploring different angles for
optimization. Your patch is indeed fundamental to our ongoing work on
the lock-free xlog reservation, and I'm eager to see how it can further
enhance our efforts.

I will proceed to test the performance impact of your latest patch when
combined with the lock-free xlog reservation patch. This will help us
determine if there's potential for additional optimization.
Concurrently, with your permission, I'll try to refine the
hash-table-based implementation for your further review. WDYT?

Regards,
Zhiguo

17.01.2025 17:00, Zhou, Zhiguo пишет:
>
>
> On 1/16/2025 10:00 PM, Yura Sokolov wrote:
>>
>> Good day, Zhiguo.
>>
>> Excuse me, I feel sneaky a bit, but I've started another thread just
>> about increase of NUM_XLOGINSERT_LOCK, because I can measure its
>> effect even on my working notebook (it is another one: Ryzen 5825U
>> limited to @2GHz).
>>
>> http://postgr.es/m/flat/3b11fdc2-9793-403d-
>> b3d4-67ff9a00d447%40postgrespro.ru
>>
>> -----
>>
>> regards
>> Yura Sokolov aka funny-falcon
>>
>>
>
> Good day, Yura!
>
> Thank you for keeping me informed. I appreciate your proactive approach
> and understand the importance of exploring different angles for
> optimization. Your patch is indeed fundamental to our ongoing work on
> the lock-free xlog reservation, and I'm eager to see how it can further
> enhance our efforts.

> I will proceed to test the performance impact of your latest patch when
> combined with the lock-free xlog reservation patch. This will help us
> determine if there's potential for additional optimization.
> Concurrently, with your permission, I'll try to refine the hash-table-
> based implementation for your further review. WDYT?

Certainly.

And I will sent my version of 64bit operations on hash-table entries...

tomorrow.

Today is 3am at the moment...

I was doing "removal of WALBufMappingLock" [1]
and I want to sleep a lot...

[1]
https://postgr.es/m/flat/39b39e7a-41b4-4f34-b3f5-db735e74a723%40postgrespro.ru

-----
regards
Yura Sokolov aka funny-falcon

17.01.2025 17:00, Zhou, Zhiguo пишет:
>
>
> On 1/16/2025 10:00 PM, Yura Sokolov wrote:
>>
>> Good day, Zhiguo.
>>
>> Excuse me, I feel sneaky a bit, but I've started another thread just
>> about increase of NUM_XLOGINSERT_LOCK, because I can measure its
>> effect even on my working notebook (it is another one: Ryzen 5825U
>> limited to @2GHz).
>>
>> http://postgr.es/m/flat/3b11fdc2-9793-403d-
>> b3d4-67ff9a00d447%40postgrespro.ru
>>
>> -----
>>
>> regards
>> Yura Sokolov aka funny-falcon
>>
>>
>
> Good day, Yura!
>
> Thank you for keeping me informed. I appreciate your proactive approach
> and understand the importance of exploring different angles for
> optimization. Your patch is indeed fundamental to our ongoing work on
> the lock-free xlog reservation, and I'm eager to see how it can further
> enhance our efforts.
>
> I will proceed to test the performance impact of your latest patch when
> combined with the lock-free xlog reservation patch. This will help us
> determine if there's potential for additional optimization.
> Concurrently, with your permission, I'll try to refine the hash-table-
> based implementation for your further review. WDYT?
>

Good day, Zhiguo

Here's version of "hash-table reservation" with both 32bit and 64bit
operations (depending on PG_HAVE_ATOMIC_U64_SIMULATION, or may be
switched by hand).

64bit version uses other protocol with a bit lesser atomic operations. I
suppose it could be a bit faster. But I can't prove it now.

btw, you wrote:

>> Major issue:
>> - `SetPrevRecPtr` and `GetPrevRecPtr` do non-atomic write/read
with on
>> platforms where MAXALIGN != 8 or without native 64 load/store.
Branch
>> with 'memcpy` is rather obvious, but even pointer de-referencing on
>> "lucky case" is not safe either.
>>
>> I have no idea how to fix it at the moment.
>>
>
> Indeed, non-atomic write/read operations can lead to safety issues in
> some situations. My initial thought is to define a bit near the
> prev-link to flag the completion of the update. In this way, we could
> allow non-atomic or even discontinuous write/read operations on the
> prev-link, while simultaneously guaranteeing its atomicity through
> atomic operations (as well as memory barriers) on the flag bit. What
> do you think of this as a viable solution?

There is a way to order operations:
- since SetPrevRecPtr stores start of record as LSN, its lower 32bits
are certainly non-zero (record could not start at the beginning of a page).
- so SetPrevRecPtr should write high 32bits, issue write barrier, and
then write lower 32bits,
- and then GetPrevRecPtr should first read lower 32bits, and if it is
not zero, then issue read barrier and read upper 32bits.

This way you will always read correct prev-rec-ptr on platform without
64bit atomics. (because MAXALING >= 4 and PostgreSQL requires 4 byte
atomicity for several years).

------
regards
Yura Sokolov aka funny-falcon

On 1/19/2025 10:56 PM, Yura Sokolov wrote:
> 17.01.2025 17:00, Zhou, Zhiguo пишет:
>>
>>
>> On 1/16/2025 10:00 PM, Yura Sokolov wrote:
>>>
>>> Good day, Zhiguo.
>>>
>>> Excuse me, I feel sneaky a bit, but I've started another thread just
>>> about increase of NUM_XLOGINSERT_LOCK, because I can measure its
>>> effect even on my working notebook (it is another one: Ryzen 5825U
>>> limited to @2GHz).
>>>
>>> http://postgr.es/m/flat/3b11fdc2-9793-403d-
>>> b3d4-67ff9a00d447%40postgrespro.ru
>>>
>>> -----
>>>
>>> regards
>>> Yura Sokolov aka funny-falcon
>>>
>>>
>>
>> Good day, Yura!
>>
>> Thank you for keeping me informed. I appreciate your proactive
>> approach and understand the importance of exploring different angles
>> for optimization. Your patch is indeed fundamental to our ongoing work
>> on the lock-free xlog reservation, and I'm eager to see how it can
>> further enhance our efforts.
>>
>> I will proceed to test the performance impact of your latest patch
>> when combined with the lock-free xlog reservation patch. This will
>> help us determine if there's potential for additional optimization.
>> Concurrently, with your permission, I'll try to refine the hash-table-
>> based implementation for your further review. WDYT?
>>
>
> Good day, Zhiguo
>
> Here's version of "hash-table reservation" with both 32bit and 64bit
> operations (depending on PG_HAVE_ATOMIC_U64_SIMULATION, or may be
> switched by hand).
>
> 64bit version uses other protocol with a bit lesser atomic operations. I
> suppose it could be a bit faster. But I can't prove it now.
>
> btw, you wrote:
>
> >> Major issue:
> >>     - `SetPrevRecPtr` and `GetPrevRecPtr` do non-atomic write/read
> with on
> >>     platforms where MAXALIGN != 8 or without native 64 load/store.
> Branch
> >>     with 'memcpy` is rather obvious, but even pointer de-referencing on
> >>     "lucky case" is not safe either.
> >>
> >>     I have no idea how to fix it at the moment.
> >>
> >
> > Indeed, non-atomic write/read operations can lead to safety issues in
> > some situations. My initial thought is to define a bit near the
> > prev-link to flag the completion of the update. In this way, we could
> > allow non-atomic or even discontinuous write/read operations on the
> > prev-link, while simultaneously guaranteeing its atomicity through
> > atomic operations (as well as memory barriers) on the flag bit. What
> > do you think of this as a viable solution?
>
> There is a way to order operations:
> - since SetPrevRecPtr stores start of record as LSN, its lower 32bits
> are certainly non-zero (record could not start at the beginning of a page).
> - so SetPrevRecPtr should write high 32bits, issue write barrier, and
> then write lower 32bits,
> - and then GetPrevRecPtr should first read lower 32bits, and if it is
> not zero, then issue read barrier and read upper 32bits.
>
> This way you will always read correct prev-rec-ptr on platform without
> 64bit atomics. (because MAXALING >= 4 and PostgreSQL requires 4 byte
> atomicity for several years).
>
> ------
> regards
> Yura Sokolov aka funny-falcon

Good day, Yura.

Thank you for your patch! It has been incredibly helpful and serves as a
great guide for my revisions. I particularly appreciate your insight
into writing the prev-rec-ptr atomically. It's a brilliant approach, and
I will definitely try implementing it in my development work. Besides,
please take some well-deserved rest. Thanks!

Regards,
Zhiguo

On Sun, 19 Jan 2025 at 17:56, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
> 17.01.2025 17:00, Zhou, Zhiguo пишет:
>> On 1/16/2025 10:00 PM, Yura Sokolov wrote:
>>>
>>> Good day, Zhiguo.
>>>
>>> Excuse me, I feel sneaky a bit, but I've started another thread
>>> just about increase of NUM_XLOGINSERT_LOCK, because I can measure
>>> its effect even on my working notebook (it is another one: Ryzen
>>> 5825U limited to @2GHz).
>>>
>>> http://postgr.es/m/flat/3b11fdc2-9793-403d-
>>> b3d4-67ff9a00d447%40postgrespro.ru
>>>
>>> -----
>>>
>>> regards
>>> Yura Sokolov aka funny-falcon
>>>
>>>
>> Good day, Yura!
>> Thank you for keeping me informed. I appreciate your proactive
>> approach and understand the importance of exploring different angles
>> for optimization. Your patch is indeed fundamental to our ongoing
>> work on the lock-free xlog reservation, and I'm eager to see how it
>> can further enhance our efforts.
>> I will proceed to test the performance impact of your latest patch
>> when combined with the lock-free xlog reservation patch. This will
>> help us determine if there's potential for additional
>> optimization. Concurrently, with your permission, I'll try to refine
>> the hash-table- based implementation for your further review. WDYT?
>>
>
> Good day, Zhiguo
>
> Here's version of "hash-table reservation" with both 32bit and 64bit
> operations (depending on PG_HAVE_ATOMIC_U64_SIMULATION, or may be
> switched by hand).
>
> 64bit version uses other protocol with a bit lesser atomic
> operations. I suppose it could be a bit faster. But I can't prove it
> now.
>
> btw, you wrote:
>
>>> Major issue:
>>> - `SetPrevRecPtr` and `GetPrevRecPtr` do non-atomic write/read
> with on
>>> platforms where MAXALIGN != 8 or without native 64
> load/store. Branch
>>> with 'memcpy` is rather obvious, but even pointer de-referencing on
>>> "lucky case" is not safe either.
>>>
>>> I have no idea how to fix it at the moment.
>>>
>>
>> Indeed, non-atomic write/read operations can lead to safety issues in
>> some situations. My initial thought is to define a bit near the
>> prev-link to flag the completion of the update. In this way, we could
>> allow non-atomic or even discontinuous write/read operations on the
>> prev-link, while simultaneously guaranteeing its atomicity through
>> atomic operations (as well as memory barriers) on the flag bit. What
>> do you think of this as a viable solution?
>
> There is a way to order operations:
> - since SetPrevRecPtr stores start of record as LSN, its lower 32bits
> are certainly non-zero (record could not start at the beginning of a
> page).
> - so SetPrevRecPtr should write high 32bits, issue write barrier, and
> then write lower 32bits,
> - and then GetPrevRecPtr should first read lower 32bits, and if it is
> not zero, then issue read barrier and read upper 32bits.
>
> This way you will always read correct prev-rec-ptr on platform without
> 64bit atomics. (because MAXALING >= 4 and PostgreSQL requires 4 byte
> atomicity for several years).
>

Hi, Yura Sokolov

Thanks for updating the patch.
I test the v2 patch using BenchmarkSQL 1000 warehouse, and here is the tpmC
result:

case | min | avg | max
--------------------+------------+------------+--------------
master (patched) | 988,461.89 | 994,916.50 | 1,000,362.40
master (44b61efb79) | 857,028.07 | 863,174.59 | 873,856.92

The patch provides a significant improvement.

I just looked through the patch, here are some comments.

1.
The v2 patch can't be applied cleanly.

Applying: Lock-free XLog Reservation using lock-free hash-table
.git/rebase-apply/patch:33: trailing whitespace.

.git/rebase-apply/patch:37: space before tab in indent.
{
.git/rebase-apply/patch:38: space before tab in indent.
int i;
.git/rebase-apply/patch:39: trailing whitespace.

.git/rebase-apply/patch:46: space before tab in indent.
LWLockReleaseClearVar(&WALInsertLocks[i].l.lock,
warning: squelched 4 whitespace errors
warning: 9 lines add whitespace errors.

2.
And there is a typo:

+ * PrevLinksHash is a lock-free hash table based on Cuckoo algorith. It is
+ * mostly 4 way: for every element computed two positions h1, h2, and

s/algorith/algorithm/g

--
Regrads,
Japin Li

22.01.2025 09:09, Japin Li пишет:
> On Sun, 19 Jan 2025 at 17:56, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>> 17.01.2025 17:00, Zhou, Zhiguo пишет:
>>> On 1/16/2025 10:00 PM, Yura Sokolov wrote:
>>>>
>>>> Good day, Zhiguo.
>>>>
>>>> Excuse me, I feel sneaky a bit, but I've started another thread
>>>> just about increase of NUM_XLOGINSERT_LOCK, because I can measure
>>>> its effect even on my working notebook (it is another one: Ryzen
>>>> 5825U limited to @2GHz).
>>>>
>>>> http://postgr.es/m/flat/3b11fdc2-9793-403d-
>>>> b3d4-67ff9a00d447%40postgrespro.ru
>>>>
>>>> -----
>>>>
>>>> regards
>>>> Yura Sokolov aka funny-falcon
>>>>
>>>>
>>> Good day, Yura!
>>> Thank you for keeping me informed. I appreciate your proactive
>>> approach and understand the importance of exploring different angles
>>> for optimization. Your patch is indeed fundamental to our ongoing
>>> work on the lock-free xlog reservation, and I'm eager to see how it
>>> can further enhance our efforts.
>>> I will proceed to test the performance impact of your latest patch
>>> when combined with the lock-free xlog reservation patch. This will
>>> help us determine if there's potential for additional
>>> optimization. Concurrently, with your permission, I'll try to refine
>>> the hash-table- based implementation for your further review. WDYT?
>>>
>>
>> Good day, Zhiguo
>>
>> Here's version of "hash-table reservation" with both 32bit and 64bit
>> operations (depending on PG_HAVE_ATOMIC_U64_SIMULATION, or may be
>> switched by hand).
>>
>> 64bit version uses other protocol with a bit lesser atomic
>> operations. I suppose it could be a bit faster. But I can't prove it
>> now.
>>
>> btw, you wrote:
>>
>>>> Major issue:
>>>> - `SetPrevRecPtr` and `GetPrevRecPtr` do non-atomic write/read
>> with on
>>>> platforms where MAXALIGN != 8 or without native 64
>> load/store. Branch
>>>> with 'memcpy` is rather obvious, but even pointer de-referencing on
>>>> "lucky case" is not safe either.
>>>>
>>>> I have no idea how to fix it at the moment.
>>>>
>>>
>>> Indeed, non-atomic write/read operations can lead to safety issues in
>>> some situations. My initial thought is to define a bit near the
>>> prev-link to flag the completion of the update. In this way, we could
>>> allow non-atomic or even discontinuous write/read operations on the
>>> prev-link, while simultaneously guaranteeing its atomicity through
>>> atomic operations (as well as memory barriers) on the flag bit. What
>>> do you think of this as a viable solution?
>>
>> There is a way to order operations:
>> - since SetPrevRecPtr stores start of record as LSN, its lower 32bits
>> are certainly non-zero (record could not start at the beginning of a
>> page).
>> - so SetPrevRecPtr should write high 32bits, issue write barrier, and
>> then write lower 32bits,
>> - and then GetPrevRecPtr should first read lower 32bits, and if it is
>> not zero, then issue read barrier and read upper 32bits.
>>
>> This way you will always read correct prev-rec-ptr on platform without
>> 64bit atomics. (because MAXALING >= 4 and PostgreSQL requires 4 byte
>> atomicity for several years).
>>
>
> Hi, Yura Sokolov
>
> Thanks for updating the patch.
> I test the v2 patch using BenchmarkSQL 1000 warehouse, and here is the tpmC
> result:
>
> case | min | avg | max
> --------------------+------------+------------+--------------
> master (patched) | 988,461.89 | 994,916.50 | 1,000,362.40
> master (44b61efb79) | 857,028.07 | 863,174.59 | 873,856.92
>
> The patch provides a significant improvement.
>
> I just looked through the patch, here are some comments.
>
> 1.
> The v2 patch can't be applied cleanly.
>
> Applying: Lock-free XLog Reservation using lock-free hash-table
> .git/rebase-apply/patch:33: trailing whitespace.
>
> .git/rebase-apply/patch:37: space before tab in indent.
> {
> .git/rebase-apply/patch:38: space before tab in indent.
> int i;
> .git/rebase-apply/patch:39: trailing whitespace.
>
> .git/rebase-apply/patch:46: space before tab in indent.
> LWLockReleaseClearVar(&WALInsertLocks[i].l.lock,
> warning: squelched 4 whitespace errors
> warning: 9 lines add whitespace errors.
>
> 2.
> And there is a typo:
>
> + * PrevLinksHash is a lock-free hash table based on Cuckoo algorith. It is
> + * mostly 4 way: for every element computed two positions h1, h2, and
>
> s/algorith/algorithm/g

Hi, Japin

Thank you a lot for measuring and comments.

May I ask you to compare not only against master, but against straight
increase of NUM_XLOGINSERT_LOCKS to 128 as well?
This way the profit from added complexity will be more obvious: does it
pay for self or not.

-------

regards
Yura

On Wed, 22 Jan 2025 at 10:25, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
> 22.01.2025 09:09, Japin Li пишет:
>> Hi, Yura Sokolov
>> Thanks for updating the patch.
>> I test the v2 patch using BenchmarkSQL 1000 warehouse, and here is the tpmC
>> result:
>> case | min | avg | max
>> --------------------+------------+------------+--------------
>> master (patched) | 988,461.89 | 994,916.50 | 1,000,362.40
>> master (44b61efb79) | 857,028.07 | 863,174.59 | 873,856.92
>> The patch provides a significant improvement.
>> I just looked through the patch, here are some comments.
>> 1.
>> The v2 patch can't be applied cleanly.
>> Applying: Lock-free XLog Reservation using lock-free hash-table
>> .git/rebase-apply/patch:33: trailing whitespace.
>> .git/rebase-apply/patch:37: space before tab in indent.
>> {
>> .git/rebase-apply/patch:38: space before tab in indent.
>> int i;
>> .git/rebase-apply/patch:39: trailing whitespace.
>> .git/rebase-apply/patch:46: space before tab in indent.
>> LWLockReleaseClearVar(&WALInsertLocks[i].l.lock,
>> warning: squelched 4 whitespace errors
>> warning: 9 lines add whitespace errors.
>> 2.
>> And there is a typo:
>> + * PrevLinksHash is a lock-free hash table based on Cuckoo
>> algorith. It is
>> + * mostly 4 way: for every element computed two positions h1, h2, and
>> s/algorith/algorithm/g
>
> Hi, Japin
>
> Thank you a lot for measuring and comments.
>
> May I ask you to compare not only against master, but against straight
> increase of NUM_XLOGINSERT_LOCKS to 128 as well?
> This way the profit from added complexity will be more obvious: does
> it pay for self or not.

The above test already increases NUM_XLOGINSERT_LOCKS to 64; I will try 128
and update the result later.

--
Regrads,
Japin Li

22.01.2025 10:54, Japin Li пишет:
> On Wed, 22 Jan 2025 at 10:25, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>> 22.01.2025 09:09, Japin Li пишет:
>>> Hi, Yura Sokolov
>>> Thanks for updating the patch.
>>> I test the v2 patch using BenchmarkSQL 1000 warehouse, and here is the tpmC
>>> result:
>>> case | min | avg | max
>>> --------------------+------------+------------+--------------
>>> master (patched) | 988,461.89 | 994,916.50 | 1,000,362.40
>>> master (44b61efb79) | 857,028.07 | 863,174.59 | 873,856.92
>>> The patch provides a significant improvement.
>>> I just looked through the patch, here are some comments.
>>> 1.
>>> The v2 patch can't be applied cleanly.
>>> Applying: Lock-free XLog Reservation using lock-free hash-table
>>> .git/rebase-apply/patch:33: trailing whitespace.
>>> .git/rebase-apply/patch:37: space before tab in indent.
>>> {
>>> .git/rebase-apply/patch:38: space before tab in indent.
>>> int i;
>>> .git/rebase-apply/patch:39: trailing whitespace.
>>> .git/rebase-apply/patch:46: space before tab in indent.
>>> LWLockReleaseClearVar(&WALInsertLocks[i].l.lock,
>>> warning: squelched 4 whitespace errors
>>> warning: 9 lines add whitespace errors.
>>> 2.
>>> And there is a typo:
>>> + * PrevLinksHash is a lock-free hash table based on Cuckoo
>>> algorith. It is
>>> + * mostly 4 way: for every element computed two positions h1, h2, and
>>> s/algorith/algorithm/g
>>
>> Hi, Japin
>>
>> Thank you a lot for measuring and comments.
>>
>> May I ask you to compare not only against master, but against straight
>> increase of NUM_XLOGINSERT_LOCKS to 128 as well?
>> This way the profit from added complexity will be more obvious: does
>> it pay for self or not.
>
> The above test already increases NUM_XLOGINSERT_LOCKS to 64;

Ok, that is good.
Did you just increased number of locks, or applied
"several-attempts-to-lock"
from [1] as well? It will be interesting how it affects performance in this
case. And it is orthogonal to "lock-free reservation", so they could
applied simultaneously.

> I will try 128 and update the result later.

Thank you.

[1]
https://www.postgresql.org/message-id/3b11fdc2-9793-403d-b3d4-67ff9a00d447%40postgrespro.ru

------
regards
Yura

On Wed, 22 Jan 2025 at 11:22, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
> 22.01.2025 10:54, Japin Li пишет:
>> On Wed, 22 Jan 2025 at 10:25, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>>> 22.01.2025 09:09, Japin Li пишет:
>>>> Hi, Yura Sokolov
>>>> Thanks for updating the patch.
>>>> I test the v2 patch using BenchmarkSQL 1000 warehouse, and here is the tpmC
>>>> result:
>>>> case | min | avg | max
>>>> --------------------+------------+------------+--------------
>>>> master (patched) | 988,461.89 | 994,916.50 | 1,000,362.40
>>>> master (44b61efb79) | 857,028.07 | 863,174.59 | 873,856.92
>>>> The patch provides a significant improvement.
>>>> I just looked through the patch, here are some comments.
>>>> 1.
>>>> The v2 patch can't be applied cleanly.
>>>> Applying: Lock-free XLog Reservation using lock-free hash-table
>>>> .git/rebase-apply/patch:33: trailing whitespace.
>>>> .git/rebase-apply/patch:37: space before tab in indent.
>>>> {
>>>> .git/rebase-apply/patch:38: space before tab in indent.
>>>> int i;
>>>> .git/rebase-apply/patch:39: trailing whitespace.
>>>> .git/rebase-apply/patch:46: space before tab in indent.
>>>> LWLockReleaseClearVar(&WALInsertLocks[i].l.lock,
>>>> warning: squelched 4 whitespace errors
>>>> warning: 9 lines add whitespace errors.
>>>> 2.
>>>> And there is a typo:
>>>> + * PrevLinksHash is a lock-free hash table based on Cuckoo
>>>> algorith. It is
>>>> + * mostly 4 way: for every element computed two positions h1, h2, and
>>>> s/algorith/algorithm/g
>>>
>>> Hi, Japin
>>>
>>> Thank you a lot for measuring and comments.
>>>
>>> May I ask you to compare not only against master, but against straight
>>> increase of NUM_XLOGINSERT_LOCKS to 128 as well?
>>> This way the profit from added complexity will be more obvious: does
>>> it pay for self or not.
>> The above test already increases NUM_XLOGINSERT_LOCKS to 64;
>
> Ok, that is good.
> Did you just increased number of locks, or applied
> "several-attempts-to-lock"
> from [1] as well? It will be interesting how it affects performance in this
> case. And it is orthogonal to "lock-free reservation", so they could
> applied simultaneously.

I apply the following two patches:

1. Lock-free XLog Reservation using lock-free hash-table
2. Increase NUM_XLOGINSERT_LOCKS to 64

I noticed the patch from the [1]. However, I haven't tested it independently.

--
Regrads,
Japin Li

On Wed, 22 Jan 2025 at 16:49, Japin Li <japinli(at)hotmail(dot)com> wrote:
> On Wed, 22 Jan 2025 at 11:22, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>> 22.01.2025 10:54, Japin Li пишет:
>>> On Wed, 22 Jan 2025 at 10:25, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>>>> 22.01.2025 09:09, Japin Li пишет:
>>>>> Hi, Yura Sokolov
>>>>> Thanks for updating the patch.
>>>>> I test the v2 patch using BenchmarkSQL 1000 warehouse, and here is the tpmC
>>>>> result:
>>>>> case | min | avg | max
>>>>> --------------------+------------+------------+--------------
>>>>> master (patched) | 988,461.89 | 994,916.50 | 1,000,362.40
>>>>> master (44b61efb79) | 857,028.07 | 863,174.59 | 873,856.92
>>>>> The patch provides a significant improvement.
>>>>> I just looked through the patch, here are some comments.
>>>>> 1.
>>>>> The v2 patch can't be applied cleanly.
>>>>> Applying: Lock-free XLog Reservation using lock-free hash-table
>>>>> .git/rebase-apply/patch:33: trailing whitespace.
>>>>> .git/rebase-apply/patch:37: space before tab in indent.
>>>>> {
>>>>> .git/rebase-apply/patch:38: space before tab in indent.
>>>>> int i;
>>>>> .git/rebase-apply/patch:39: trailing whitespace.
>>>>> .git/rebase-apply/patch:46: space before tab in indent.
>>>>> LWLockReleaseClearVar(&WALInsertLocks[i].l.lock,
>>>>> warning: squelched 4 whitespace errors
>>>>> warning: 9 lines add whitespace errors.
>>>>> 2.
>>>>> And there is a typo:
>>>>> + * PrevLinksHash is a lock-free hash table based on Cuckoo
>>>>> algorith. It is
>>>>> + * mostly 4 way: for every element computed two positions h1, h2, and
>>>>> s/algorith/algorithm/g
>>>>
>>>> Hi, Japin
>>>>
>>>> Thank you a lot for measuring and comments.
>>>>
>>>> May I ask you to compare not only against master, but against straight
>>>> increase of NUM_XLOGINSERT_LOCKS to 128 as well?
>>>> This way the profit from added complexity will be more obvious: does
>>>> it pay for self or not.
>>> The above test already increases NUM_XLOGINSERT_LOCKS to 64;
>>
>> Ok, that is good.
>> Did you just increased number of locks, or applied
>> "several-attempts-to-lock"
>> from [1] as well? It will be interesting how it affects performance in this
>> case. And it is orthogonal to "lock-free reservation", so they could
>> applied simultaneously.
>
> I apply the following two patches:
>
> 1. Lock-free XLog Reservation using lock-free hash-table

Hi, Yura Sokolov

When I try to test the performance by only applying the Lock-free XLog
Reservation patch, there is an error:

2025-01-22 20:06:49.976 CST [1271602] PANIC: stuck spinlock detected at LinkAndFindPrevPos, /home/postgres/postgres/build/../src/backend/access/transam/xlog.c:1425
2025-01-22 20:06:49.976 CST [1271602] STATEMENT: UPDATE bmsql_customer SET c_balance = c_balance - $1, c_ytd_payment = c_ytd_payment + $2, c_payment_cnt = c_payment_cnt + 1 WHERE c_w_id = $3 AND c_d_id = $4 AND c_id = $5
2025-01-22 20:06:50.078 CST [1271748] PANIC: stuck spinlock detected at LinkAndFindPrevPos, /home/postgres/postgres/build/../src/backend/access/transam/xlog.c:1425

However, it does not always occur.

--
Regrads,
Japin Li

22.01.2025 15:37, Japin Li пишет:
> On Wed, 22 Jan 2025 at 16:49, Japin Li <japinli(at)hotmail(dot)com> wrote:
>> On Wed, 22 Jan 2025 at 11:22, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>>> 22.01.2025 10:54, Japin Li пишет:
>>>> On Wed, 22 Jan 2025 at 10:25, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>>>>> 22.01.2025 09:09, Japin Li пишет:
>>>>>> Hi, Yura Sokolov
>>>>>> Thanks for updating the patch.
>>>>>> I test the v2 patch using BenchmarkSQL 1000 warehouse, and here is the tpmC
>>>>>> result:
>>>>>> case | min | avg | max
>>>>>> --------------------+------------+------------+--------------
>>>>>> master (patched) | 988,461.89 | 994,916.50 | 1,000,362.40
>>>>>> master (44b61efb79) | 857,028.07 | 863,174.59 | 873,856.92
>>>>>> The patch provides a significant improvement.
>>>>>> I just looked through the patch, here are some comments.
>>>>>> 1.
>>>>>> The v2 patch can't be applied cleanly.
>>>>>> Applying: Lock-free XLog Reservation using lock-free hash-table
>>>>>> .git/rebase-apply/patch:33: trailing whitespace.
>>>>>> .git/rebase-apply/patch:37: space before tab in indent.
>>>>>> {
>>>>>> .git/rebase-apply/patch:38: space before tab in indent.
>>>>>> int i;
>>>>>> .git/rebase-apply/patch:39: trailing whitespace.
>>>>>> .git/rebase-apply/patch:46: space before tab in indent.
>>>>>> LWLockReleaseClearVar(&WALInsertLocks[i].l.lock,
>>>>>> warning: squelched 4 whitespace errors
>>>>>> warning: 9 lines add whitespace errors.
>>>>>> 2.
>>>>>> And there is a typo:
>>>>>> + * PrevLinksHash is a lock-free hash table based on Cuckoo
>>>>>> algorith. It is
>>>>>> + * mostly 4 way: for every element computed two positions h1, h2, and
>>>>>> s/algorith/algorithm/g
>>>>>
>>>>> Hi, Japin
>>>>>
>>>>> Thank you a lot for measuring and comments.
>>>>>
>>>>> May I ask you to compare not only against master, but against straight
>>>>> increase of NUM_XLOGINSERT_LOCKS to 128 as well?
>>>>> This way the profit from added complexity will be more obvious: does
>>>>> it pay for self or not.
>>>> The above test already increases NUM_XLOGINSERT_LOCKS to 64;
>>>
>>> Ok, that is good.
>>> Did you just increased number of locks, or applied
>>> "several-attempts-to-lock"
>>> from [1] as well? It will be interesting how it affects performance in this
>>> case. And it is orthogonal to "lock-free reservation", so they could
>>> applied simultaneously.
>>
>> I apply the following two patches:
>>
>> 1. Lock-free XLog Reservation using lock-free hash-table
>
> Hi, Yura Sokolov
>
> When I try to test the performance by only applying the Lock-free XLog
> Reservation patch, there is an error:
>
> 2025-01-22 20:06:49.976 CST [1271602] PANIC: stuck spinlock detected at LinkAndFindPrevPos, /home/postgres/postgres/build/../src/backend/access/transam/xlog.c:1425
> 2025-01-22 20:06:49.976 CST [1271602] STATEMENT: UPDATE bmsql_customer SET c_balance = c_balance - $1, c_ytd_payment = c_ytd_payment + $2, c_payment_cnt = c_payment_cnt + 1 WHERE c_w_id = $3 AND c_d_id = $4 AND c_id = $5
> 2025-01-22 20:06:50.078 CST [1271748] PANIC: stuck spinlock detected at LinkAndFindPrevPos, /home/postgres/postgres/build/../src/backend/access/transam/xlog.c:1425
>
> However, it does not always occur.

Oh, thank you!

I believe, I know why it happens: I was in hurry making v2 by
cherry-picking internal version. I reverted some changes in
CalcCuckooPositions manually and forgot to add modulo PREV_LINKS_HASH_CAPA.

Here's the fix:

pos->pos[0] = hash % PREV_LINKS_HASH_CAPA;
- pos->pos[1] = pos->pos[0] + 1;
+ pos->pos[1] = (pos->pos[0] + 1) % PREV_LINKS_HASH_CAPA;
pos->pos[2] = (hash >> 16) % PREV_LINKS_HASH_CAPA;
- pos->pos[3] = pos->pos[2] + 2;
+ pos->pos[3] = (pos->pos[2] + 2) % PREV_LINKS_HASH_CAPA;

Any way, here's v3:
- excess file "v0-0001-Increase..." removed. I believe it was source of
white-space apply warnings.
- this mistake fixed
- more clear slots strategies + "8 positions in two cache-lines" strategy.

You may play with switching PREV_LINKS_HASH_STRATEGY to 2 or 3 and see
if it affects measurably.

-------
regards
Yura Sokolov aka funny-falcon

22.01.2025 10:54, Japin Li wrote:
> On Wed, 22 Jan 2025 at 10:25, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>> 22.01.2025 09:09, Japin Li пишет:
>>> Hi, Yura Sokolov
>>> Thanks for updating the patch.
>>> I test the v2 patch using BenchmarkSQL 1000 warehouse, and here is the tpmC
>>> result:
>>> case | min | avg | max
>>> --------------------+------------+------------+--------------
>>> master (patched) | 988,461.89 | 994,916.50 | 1,000,362.40
>>> master (44b61efb79) | 857,028.07 | 863,174.59 | 873,856.92
>>> The patch provides a significant improvement.
>>> I just looked through the patch, here are some comments.
>>> 1.
>>> The v2 patch can't be applied cleanly.
>>> Applying: Lock-free XLog Reservation using lock-free hash-table
>>> .git/rebase-apply/patch:33: trailing whitespace.
>>> .git/rebase-apply/patch:37: space before tab in indent.
>>> {
>>> .git/rebase-apply/patch:38: space before tab in indent.
>>> int i;
>>> .git/rebase-apply/patch:39: trailing whitespace.
>>> .git/rebase-apply/patch:46: space before tab in indent.
>>> LWLockReleaseClearVar(&WALInsertLocks[i].l.lock,
>>> warning: squelched 4 whitespace errors
>>> warning: 9 lines add whitespace errors.
>>> 2.
>>> And there is a typo:
>>> + * PrevLinksHash is a lock-free hash table based on Cuckoo
>>> algorith. It is
>>> + * mostly 4 way: for every element computed two positions h1, h2, and
>>> s/algorith/algorithm/g
>>
>> Hi, Japin
>>
>> Thank you a lot for measuring and comments.
>>
>> May I ask you to compare not only against master, but against straight
>> increase of NUM_XLOGINSERT_LOCKS to 128 as well?
>> This way the profit from added complexity will be more obvious: does
>> it pay for self or not.
>
> The above test already increases NUM_XLOGINSERT_LOCKS to 64; I will try 128
> and update the result later.

Oh, I see: I forgot that I removed increase of NUM_XLOGINSERT_LOCKS from
v2 patch.

On Wed, 22 Jan 2025 at 17:02, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
> I believe, I know why it happens: I was in hurry making v2 by
> cherry-picking internal version. I reverted some changes in
> CalcCuckooPositions manually and forgot to add modulo
> PREV_LINKS_HASH_CAPA.
>
> Here's the fix:
>
> pos->pos[0] = hash % PREV_LINKS_HASH_CAPA;
> - pos->pos[1] = pos->pos[0] + 1;
> + pos->pos[1] = (pos->pos[0] + 1) % PREV_LINKS_HASH_CAPA;
> pos->pos[2] = (hash >> 16) % PREV_LINKS_HASH_CAPA;
> - pos->pos[3] = pos->pos[2] + 2;
> + pos->pos[3] = (pos->pos[2] + 2) % PREV_LINKS_HASH_CAPA;
>
> Any way, here's v3:
> - excess file "v0-0001-Increase..." removed. I believe it was source
> of white-space apply warnings.
> - this mistake fixed
> - more clear slots strategies + "8 positions in two cache-lines" strategy.
>
> You may play with switching PREV_LINKS_HASH_STRATEGY to 2 or 3 and see
> if it affects measurably.

Thanks for your quick fixing. I will retest it tomorrow.

--
Regrads,
Japin Li

On Wed, 22 Jan 2025 at 22:44, Japin Li <japinli(at)hotmail(dot)com> wrote:
> On Wed, 22 Jan 2025 at 17:02, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>> I believe, I know why it happens: I was in hurry making v2 by
>> cherry-picking internal version. I reverted some changes in
>> CalcCuckooPositions manually and forgot to add modulo
>> PREV_LINKS_HASH_CAPA.
>>
>> Here's the fix:
>>
>> pos->pos[0] = hash % PREV_LINKS_HASH_CAPA;
>> - pos->pos[1] = pos->pos[0] + 1;
>> + pos->pos[1] = (pos->pos[0] + 1) % PREV_LINKS_HASH_CAPA;
>> pos->pos[2] = (hash >> 16) % PREV_LINKS_HASH_CAPA;
>> - pos->pos[3] = pos->pos[2] + 2;
>> + pos->pos[3] = (pos->pos[2] + 2) % PREV_LINKS_HASH_CAPA;
>>
>> Any way, here's v3:
>> - excess file "v0-0001-Increase..." removed. I believe it was source
>> of white-space apply warnings.
>> - this mistake fixed
>> - more clear slots strategies + "8 positions in two cache-lines" strategy.
>>
>> You may play with switching PREV_LINKS_HASH_STRATEGY to 2 or 3 and see
>> if it affects measurably.
>
> Thanks for your quick fixing. I will retest it tomorrow.

Hi, Yura Sokolov

Here is my test result of the v3 patch:

| case | min | avg | max |
|-------------------------------+------------+------------+------------|
| master (44b61efb79) | 865,743.55 | 871,237.40 | 874,492.59 |
| v3 | 857,020.58 | 860,180.11 | 864,355.00 |
| v3 PREV_LINKS_HASH_STRATEGY=2 | 853,187.41 | 855,796.36 | 858,436.44 |
| v3 PREV_LINKS_HASH_STRATEGY=3 | 863,131.97 | 864,272.91 | 865,396.42 |

It seems there are some performance decreases :( or something I missed?

--
Regrads,
Japin Li

23.01.2025 11:46, Japin Li пишет:
> On Wed, 22 Jan 2025 at 22:44, Japin Li <japinli(at)hotmail(dot)com> wrote:
>> On Wed, 22 Jan 2025 at 17:02, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>>> I believe, I know why it happens: I was in hurry making v2 by
>>> cherry-picking internal version. I reverted some changes in
>>> CalcCuckooPositions manually and forgot to add modulo
>>> PREV_LINKS_HASH_CAPA.
>>>
>>> Here's the fix:
>>>
>>> pos->pos[0] = hash % PREV_LINKS_HASH_CAPA;
>>> - pos->pos[1] = pos->pos[0] + 1;
>>> + pos->pos[1] = (pos->pos[0] + 1) % PREV_LINKS_HASH_CAPA;
>>> pos->pos[2] = (hash >> 16) % PREV_LINKS_HASH_CAPA;
>>> - pos->pos[3] = pos->pos[2] + 2;
>>> + pos->pos[3] = (pos->pos[2] + 2) % PREV_LINKS_HASH_CAPA;
>>>
>>> Any way, here's v3:
>>> - excess file "v0-0001-Increase..." removed. I believe it was source
>>> of white-space apply warnings.
>>> - this mistake fixed
>>> - more clear slots strategies + "8 positions in two cache-lines" strategy.
>>>
>>> You may play with switching PREV_LINKS_HASH_STRATEGY to 2 or 3 and see
>>> if it affects measurably.
>>
>> Thanks for your quick fixing. I will retest it tomorrow.
>
> Hi, Yura Sokolov
>
> Here is my test result of the v3 patch:
>
> | case | min | avg | max |
> |-------------------------------+------------+------------+------------|
> | master (44b61efb79) | 865,743.55 | 871,237.40 | 874,492.59 |
> | v3 | 857,020.58 | 860,180.11 | 864,355.00 |
> | v3 PREV_LINKS_HASH_STRATEGY=2 | 853,187.41 | 855,796.36 | 858,436.44 |
> | v3 PREV_LINKS_HASH_STRATEGY=3 | 863,131.97 | 864,272.91 | 865,396.42 |
>
> It seems there are some performance decreases :( or something I missed?
>

Hi, Japin.
(Excuse me for duplicating message, I found I sent it only to you first
time).

v3 (as well as v2) doesn't increase NUM_XLOGINSERT_LOCKS itself.
With only 8 in-progress inserters spin-lock is certainly better than any
more complex solution.

You need to compare "master" vs "master + NUM_XLOGINSERT_LOCKS=64" vs
"master + NUM_XLOGINSERT_LOCKS=64 + v3".

And even this way I don't claim "Lock-free reservation" gives any profit.

That is why your benchmarking is very valuable! It could answer, does we
need such not-small patch, or there is no real problem at all?

----
regards
Yura Sokolov aka funny-falcon

On Thu, 23 Jan 2025 at 15:03, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
> 23.01.2025 11:46, Japin Li пишет:
>> On Wed, 22 Jan 2025 at 22:44, Japin Li <japinli(at)hotmail(dot)com> wrote:
>>> On Wed, 22 Jan 2025 at 17:02, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>>>> I believe, I know why it happens: I was in hurry making v2 by
>>>> cherry-picking internal version. I reverted some changes in
>>>> CalcCuckooPositions manually and forgot to add modulo
>>>> PREV_LINKS_HASH_CAPA.
>>>>
>>>> Here's the fix:
>>>>
>>>> pos->pos[0] = hash % PREV_LINKS_HASH_CAPA;
>>>> - pos->pos[1] = pos->pos[0] + 1;
>>>> + pos->pos[1] = (pos->pos[0] + 1) % PREV_LINKS_HASH_CAPA;
>>>> pos->pos[2] = (hash >> 16) % PREV_LINKS_HASH_CAPA;
>>>> - pos->pos[3] = pos->pos[2] + 2;
>>>> + pos->pos[3] = (pos->pos[2] + 2) % PREV_LINKS_HASH_CAPA;
>>>>
>>>> Any way, here's v3:
>>>> - excess file "v0-0001-Increase..." removed. I believe it was source
>>>> of white-space apply warnings.
>>>> - this mistake fixed
>>>> - more clear slots strategies + "8 positions in two cache-lines" strategy.
>>>>
>>>> You may play with switching PREV_LINKS_HASH_STRATEGY to 2 or 3 and see
>>>> if it affects measurably.
>>>
>>> Thanks for your quick fixing. I will retest it tomorrow.
>> Hi, Yura Sokolov
>> Here is my test result of the v3 patch:
>> | case | min | avg | max
>> |
>> |-------------------------------+------------+------------+------------|
>> | master (44b61efb79) | 865,743.55 | 871,237.40 | 874,492.59 |
>> | v3 | 857,020.58 | 860,180.11 | 864,355.00 |
>> | v3 PREV_LINKS_HASH_STRATEGY=2 | 853,187.41 | 855,796.36 | 858,436.44 |
>> | v3 PREV_LINKS_HASH_STRATEGY=3 | 863,131.97 | 864,272.91 | 865,396.42 |
>> It seems there are some performance decreases :( or something I
>> missed?
>>
>
> Hi, Japin.
> (Excuse me for duplicating message, I found I sent it only to you
> first time).
>
Never mind!

> v3 (as well as v2) doesn't increase NUM_XLOGINSERT_LOCKS itself.
> With only 8 in-progress inserters spin-lock is certainly better than any
> more complex solution.
>
> You need to compare "master" vs "master + NUM_XLOGINSERT_LOCKS=64" vs
> "master + NUM_XLOGINSERT_LOCKS=64 + v3".
>
> And even this way I don't claim "Lock-free reservation" gives any profit.
>
> That is why your benchmarking is very valuable! It could answer, does
> we need such not-small patch, or there is no real problem at all?
>

Thanks for your explanation. I will test it based on [1].

[1] https://www.postgresql.org/message-id/ME0P300MB0445471ABC855D0FA6FF0CA5B6E02%40ME0P300MB0445.AUSP300.PROD.OUTLOOK.COM
--
Regrads,
Japin Li

On Thu, 23 Jan 2025 at 21:44, Japin Li <japinli(at)hotmail(dot)com> wrote:
> On Thu, 23 Jan 2025 at 15:03, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>> 23.01.2025 11:46, Japin Li пишет:
>>> On Wed, 22 Jan 2025 at 22:44, Japin Li <japinli(at)hotmail(dot)com> wrote:
>>>> On Wed, 22 Jan 2025 at 17:02, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>>>>> I believe, I know why it happens: I was in hurry making v2 by
>>>>> cherry-picking internal version. I reverted some changes in
>>>>> CalcCuckooPositions manually and forgot to add modulo
>>>>> PREV_LINKS_HASH_CAPA.
>>>>>
>>>>> Here's the fix:
>>>>>
>>>>> pos->pos[0] = hash % PREV_LINKS_HASH_CAPA;
>>>>> - pos->pos[1] = pos->pos[0] + 1;
>>>>> + pos->pos[1] = (pos->pos[0] + 1) % PREV_LINKS_HASH_CAPA;
>>>>> pos->pos[2] = (hash >> 16) % PREV_LINKS_HASH_CAPA;
>>>>> - pos->pos[3] = pos->pos[2] + 2;
>>>>> + pos->pos[3] = (pos->pos[2] + 2) % PREV_LINKS_HASH_CAPA;
>>>>>
>>>>> Any way, here's v3:
>>>>> - excess file "v0-0001-Increase..." removed. I believe it was source
>>>>> of white-space apply warnings.
>>>>> - this mistake fixed
>>>>> - more clear slots strategies + "8 positions in two cache-lines" strategy.
>>>>>
>>>>> You may play with switching PREV_LINKS_HASH_STRATEGY to 2 or 3 and see
>>>>> if it affects measurably.
>>>>
>>>> Thanks for your quick fixing. I will retest it tomorrow.
>>> Hi, Yura Sokolov
>>> Here is my test result of the v3 patch:
>>> | case | min | avg | max
>>> |
>>> |-------------------------------+------------+------------+------------|
>>> | master (44b61efb79) | 865,743.55 | 871,237.40 | 874,492.59 |
>>> | v3 | 857,020.58 | 860,180.11 | 864,355.00 |
>>> | v3 PREV_LINKS_HASH_STRATEGY=2 | 853,187.41 | 855,796.36 | 858,436.44 |
>>> | v3 PREV_LINKS_HASH_STRATEGY=3 | 863,131.97 | 864,272.91 | 865,396.42 |
>>> It seems there are some performance decreases :( or something I
>>> missed?
>>>
>>
>> Hi, Japin.
>> (Excuse me for duplicating message, I found I sent it only to you
>> first time).
>>
> Never mind!
>
>> v3 (as well as v2) doesn't increase NUM_XLOGINSERT_LOCKS itself.
>> With only 8 in-progress inserters spin-lock is certainly better than any
>> more complex solution.
>>
>> You need to compare "master" vs "master + NUM_XLOGINSERT_LOCKS=64" vs
>> "master + NUM_XLOGINSERT_LOCKS=64 + v3".
>>
>> And even this way I don't claim "Lock-free reservation" gives any profit.
>>
>> That is why your benchmarking is very valuable! It could answer, does
>> we need such not-small patch, or there is no real problem at all?
>>

Hi, Yura Sokolov

Here is the test result compared with NUM_XLOGINSERT_LOCKS and the v3 patch.

| case | min | avg | max | rate% |
|-----------------------+--------------+--------------+--------------+-------|
| master (4108440) | 891,225.77 | 904,868.75 | 913,708.17 | |
| lock 64 | 1,007,716.95 | 1,012,013.22 | 1,018,674.00 | 11.84 |
| lock 64 attempt 1 | 1,016,716.07 | 1,017,735.55 | 1,019,328.36 | 12.47 |
| lock 64 attempt 2 | 1,015,328.31 | 1,018,147.74 | 1,021,513.14 | 12.52 |
| lock 128 | 1,010,147.38 | 1,014,128.11 | 1,018,672.01 | 12.07 |
| lock 128 attempt 1 | 1,018,154.79 | 1,023,348.35 | 1,031,365.42 | 13.09 |
| lock 128 attempt 2 | 1,013,245.56 | 1,018,984.78 | 1,023,696.00 | 12.61 |
| lock 64 v3 | 1,010,893.30 | 1,022,787.25 | 1,029,200.26 | 13.03 |
| lock 64 attempt 1 v3 | 1,014,961.21 | 1,019,745.09 | 1,025,511.62 | 12.70 |
| lock 64 attempt 2 v3 | 1,015,690.73 | 1,018,365.46 | 1,020,200.57 | 12.54 |
| lock 128 v3 | 1,012,653.14 | 1,013,637.09 | 1,014,358.69 | 12.02 |
| lock 128 attempt 1 v3 | 1,008,027.57 | 1,016,849.87 | 1,024,597.15 | 12.38 |
| lock 128 attempt 2 v3 | 1,020,552.04 | 1,024,658.92 | 1,027,855.90 | 13.24 |

--
Regrads,
Japin Li

24.01.2025 12:07, Japin Li пишет:
> On Thu, 23 Jan 2025 at 21:44, Japin Li <japinli(at)hotmail(dot)com> wrote:
>> On Thu, 23 Jan 2025 at 15:03, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>>> 23.01.2025 11:46, Japin Li пишет:
>>>> On Wed, 22 Jan 2025 at 22:44, Japin Li <japinli(at)hotmail(dot)com> wrote:
>>>>> On Wed, 22 Jan 2025 at 17:02, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>>>>>> I believe, I know why it happens: I was in hurry making v2 by
>>>>>> cherry-picking internal version. I reverted some changes in
>>>>>> CalcCuckooPositions manually and forgot to add modulo
>>>>>> PREV_LINKS_HASH_CAPA.
>>>>>>
>>>>>> Here's the fix:
>>>>>>
>>>>>> pos->pos[0] = hash % PREV_LINKS_HASH_CAPA;
>>>>>> - pos->pos[1] = pos->pos[0] + 1;
>>>>>> + pos->pos[1] = (pos->pos[0] + 1) % PREV_LINKS_HASH_CAPA;
>>>>>> pos->pos[2] = (hash >> 16) % PREV_LINKS_HASH_CAPA;
>>>>>> - pos->pos[3] = pos->pos[2] + 2;
>>>>>> + pos->pos[3] = (pos->pos[2] + 2) % PREV_LINKS_HASH_CAPA;
>>>>>>
>>>>>> Any way, here's v3:
>>>>>> - excess file "v0-0001-Increase..." removed. I believe it was source
>>>>>> of white-space apply warnings.
>>>>>> - this mistake fixed
>>>>>> - more clear slots strategies + "8 positions in two cache-lines" strategy.
>>>>>>
>>>>>> You may play with switching PREV_LINKS_HASH_STRATEGY to 2 or 3 and see
>>>>>> if it affects measurably.
>>>>>
>>>>> Thanks for your quick fixing. I will retest it tomorrow.
>>>> Hi, Yura Sokolov
>>>> Here is my test result of the v3 patch:
>>>> | case | min | avg | max
>>>> |
>>>> |-------------------------------+------------+------------+------------|
>>>> | master (44b61efb79) | 865,743.55 | 871,237.40 | 874,492.59 |
>>>> | v3 | 857,020.58 | 860,180.11 | 864,355.00 |
>>>> | v3 PREV_LINKS_HASH_STRATEGY=2 | 853,187.41 | 855,796.36 | 858,436.44 |
>>>> | v3 PREV_LINKS_HASH_STRATEGY=3 | 863,131.97 | 864,272.91 | 865,396.42 |
>>>> It seems there are some performance decreases :( or something I
>>>> missed?
>>>>
>>>
>>> Hi, Japin.
>>> (Excuse me for duplicating message, I found I sent it only to you
>>> first time).
>>>
>> Never mind!
>>
>>> v3 (as well as v2) doesn't increase NUM_XLOGINSERT_LOCKS itself.
>>> With only 8 in-progress inserters spin-lock is certainly better than any
>>> more complex solution.
>>>
>>> You need to compare "master" vs "master + NUM_XLOGINSERT_LOCKS=64" vs
>>> "master + NUM_XLOGINSERT_LOCKS=64 + v3".
>>>
>>> And even this way I don't claim "Lock-free reservation" gives any profit.
>>>
>>> That is why your benchmarking is very valuable! It could answer, does
>>> we need such not-small patch, or there is no real problem at all?
>>>
>
> Hi, Yura Sokolov
>
> Here is the test result compared with NUM_XLOGINSERT_LOCKS and the v3 patch.
>
> | case | min | avg | max | rate% |
> |-----------------------+--------------+--------------+--------------+-------|
> | master (4108440) | 891,225.77 | 904,868.75 | 913,708.17 | |
> | lock 64 | 1,007,716.95 | 1,012,013.22 | 1,018,674.00 | 11.84 |
> | lock 64 attempt 1 | 1,016,716.07 | 1,017,735.55 | 1,019,328.36 | 12.47 |
> | lock 64 attempt 2 | 1,015,328.31 | 1,018,147.74 | 1,021,513.14 | 12.52 |
> | lock 128 | 1,010,147.38 | 1,014,128.11 | 1,018,672.01 | 12.07 |
> | lock 128 attempt 1 | 1,018,154.79 | 1,023,348.35 | 1,031,365.42 | 13.09 |
> | lock 128 attempt 2 | 1,013,245.56 | 1,018,984.78 | 1,023,696.00 | 12.61 |
> | lock 64 v3 | 1,010,893.30 | 1,022,787.25 | 1,029,200.26 | 13.03 |
> | lock 64 attempt 1 v3 | 1,014,961.21 | 1,019,745.09 | 1,025,511.62 | 12.70 |
> | lock 64 attempt 2 v3 | 1,015,690.73 | 1,018,365.46 | 1,020,200.57 | 12.54 |
> | lock 128 v3 | 1,012,653.14 | 1,013,637.09 | 1,014,358.69 | 12.02 |
> | lock 128 attempt 1 v3 | 1,008,027.57 | 1,016,849.87 | 1,024,597.15 | 12.38 |
> | lock 128 attempt 2 v3 | 1,020,552.04 | 1,024,658.92 | 1,027,855.90 | 13.24 |

Sorry for pause, it was my birthday, so I was on short vacation.

So, in total:
- increasing NUM_XLOGINSERT_LOCKS to 64 certainly helps
- additional lock attempts seems to help a bit in this benchmark,
but it helps more in other (rather synthetic) benchmark [1]
- my version of lock-free reservation looks to help a bit when
applied alone, but look strange in conjunction with additional
lock attempts.

I don't see small improvement from my version of Lock-Free reservation
(1.1% = 1023/1012) pays for its complexity at the moment.

Probably, when other places will be optimized/improved, it will pay
more.

Or probably Zhiguo Zhou's version will perform better.

I think, we could measure theoretical benefit by completely ignoring
fill of xl_prev. I've attached patch "Dumb-lock-free..." so you could
measure. It passes almost all "recovery" tests, though fails two
strictly dependent on xl_prev.

[1]
https://postgr.es/m/3b11fdc2-9793-403d-b3d4-67ff9a00d447%40postgrespro.ru

------

regards
Yura

24.01.2025 12:07, Japin Li wrote:
> On Thu, 23 Jan 2025 at 21:44, Japin Li <japinli(at)hotmail(dot)com> wrote:
>> On Thu, 23 Jan 2025 at 15:03, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>>> 23.01.2025 11:46, Japin Li пишет:
>>>> On Wed, 22 Jan 2025 at 22:44, Japin Li <japinli(at)hotmail(dot)com> wrote:
>>>>> On Wed, 22 Jan 2025 at 17:02, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>>>>>> I believe, I know why it happens: I was in hurry making v2 by
>>>>>> cherry-picking internal version. I reverted some changes in
>>>>>> CalcCuckooPositions manually and forgot to add modulo
>>>>>> PREV_LINKS_HASH_CAPA.
>>>>>>
>>>>>> Here's the fix:
>>>>>>
>>>>>> pos->pos[0] = hash % PREV_LINKS_HASH_CAPA;
>>>>>> - pos->pos[1] = pos->pos[0] + 1;
>>>>>> + pos->pos[1] = (pos->pos[0] + 1) % PREV_LINKS_HASH_CAPA;
>>>>>> pos->pos[2] = (hash >> 16) % PREV_LINKS_HASH_CAPA;
>>>>>> - pos->pos[3] = pos->pos[2] + 2;
>>>>>> + pos->pos[3] = (pos->pos[2] + 2) % PREV_LINKS_HASH_CAPA;
>>>>>>
>>>>>> Any way, here's v3:
>>>>>> - excess file "v0-0001-Increase..." removed. I believe it was source
>>>>>> of white-space apply warnings.
>>>>>> - this mistake fixed
>>>>>> - more clear slots strategies + "8 positions in two cache-lines" strategy.
>>>>>>
>>>>>> You may play with switching PREV_LINKS_HASH_STRATEGY to 2 or 3 and see
>>>>>> if it affects measurably.
>>>>>
>>>>> Thanks for your quick fixing. I will retest it tomorrow.
>>>> Hi, Yura Sokolov
>>>> Here is my test result of the v3 patch:
>>>> | case | min | avg | max
>>>> |
>>>> |-------------------------------+------------+------------+------------|
>>>> | master (44b61efb79) | 865,743.55 | 871,237.40 | 874,492.59 |
>>>> | v3 | 857,020.58 | 860,180.11 | 864,355.00 |
>>>> | v3 PREV_LINKS_HASH_STRATEGY=2 | 853,187.41 | 855,796.36 | 858,436.44 |
>>>> | v3 PREV_LINKS_HASH_STRATEGY=3 | 863,131.97 | 864,272.91 | 865,396.42 |
>>>> It seems there are some performance decreases :( or something I
>>>> missed?
>>>>
>>>
>>> Hi, Japin.
>>> (Excuse me for duplicating message, I found I sent it only to you
>>> first time).
>>>
>> Never mind!
>>
>>> v3 (as well as v2) doesn't increase NUM_XLOGINSERT_LOCKS itself.
>>> With only 8 in-progress inserters spin-lock is certainly better than any
>>> more complex solution.
>>>
>>> You need to compare "master" vs "master + NUM_XLOGINSERT_LOCKS=64" vs
>>> "master + NUM_XLOGINSERT_LOCKS=64 + v3".
>>>
>>> And even this way I don't claim "Lock-free reservation" gives any profit.
>>>
>>> That is why your benchmarking is very valuable! It could answer, does
>>> we need such not-small patch, or there is no real problem at all?
>>>
>
> Hi, Yura Sokolov
>
> Here is the test result compared with NUM_XLOGINSERT_LOCKS and the v3 patch.
>
> | case | min | avg | max | rate% |
> |-----------------------+--------------+--------------+--------------+-------|
> | master (4108440) | 891,225.77 | 904,868.75 | 913,708.17 | |
> | lock 64 | 1,007,716.95 | 1,012,013.22 | 1,018,674.00 | 11.84 |
> | lock 64 attempt 1 | 1,016,716.07 | 1,017,735.55 | 1,019,328.36 | 12.47 |
> | lock 64 attempt 2 | 1,015,328.31 | 1,018,147.74 | 1,021,513.14 | 12.52 |
> | lock 128 | 1,010,147.38 | 1,014,128.11 | 1,018,672.01 | 12.07 |
> | lock 128 attempt 1 | 1,018,154.79 | 1,023,348.35 | 1,031,365.42 | 13.09 |
> | lock 128 attempt 2 | 1,013,245.56 | 1,018,984.78 | 1,023,696.00 | 12.61 |
> | lock 64 v3 | 1,010,893.30 | 1,022,787.25 | 1,029,200.26 | 13.03 |
> | lock 64 attempt 1 v3 | 1,014,961.21 | 1,019,745.09 | 1,025,511.62 | 12.70 |
> | lock 64 attempt 2 v3 | 1,015,690.73 | 1,018,365.46 | 1,020,200.57 | 12.54 |
> | lock 128 v3 | 1,012,653.14 | 1,013,637.09 | 1,014,358.69 | 12.02 |
> | lock 128 attempt 1 v3 | 1,008,027.57 | 1,016,849.87 | 1,024,597.15 | 12.38 |
> | lock 128 attempt 2 v3 | 1,020,552.04 | 1,024,658.92 | 1,027,855.90 | 13.24 |

By the way, I think I did a mistake by removing "pad" field in
XLogCtlInsert, and it could affect result in bad way.

So I've attached v4 with changes:
- "pad" field were returned to separate CurrBytePos from following
fields, and static assert were added.
- default PREV_LINKS_HASH_STRATEGY were changed to 3 as it shows less
regression on not modified NUM_XLOGINSERT_LOCKS=8

Though I beg you to test "Dumb-lock-free..." patch from previous letter
first. And only if it shows some promising results, then spent time on
v4.

------

regards
Yura

On 1/26/2025 10:59 PM, Yura Sokolov wrote:
> 24.01.2025 12:07, Japin Li пишет:
>> On Thu, 23 Jan 2025 at 21:44, Japin Li <japinli(at)hotmail(dot)com> wrote:
>>> On Thu, 23 Jan 2025 at 15:03, Yura Sokolov <y(dot)sokolov(at)postgrespro(dot)ru>
>>> wrote:
>>>> 23.01.2025 11:46, Japin Li пишет:
>>>>> On Wed, 22 Jan 2025 at 22:44, Japin Li <japinli(at)hotmail(dot)com> wrote:
>>>>>> On Wed, 22 Jan 2025 at 17:02, Yura Sokolov
>>>>>> <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>>>>>>> I believe, I know why it happens: I was in hurry making v2 by
>>>>>>> cherry-picking internal version. I reverted some changes in
>>>>>>> CalcCuckooPositions manually and forgot to add modulo
>>>>>>> PREV_LINKS_HASH_CAPA.
>>>>>>>
>>>>>>> Here's the fix:
>>>>>>>
>>>>>>>           pos->pos[0] = hash % PREV_LINKS_HASH_CAPA;
>>>>>>> -       pos->pos[1] = pos->pos[0] + 1;
>>>>>>> +       pos->pos[1] = (pos->pos[0] + 1) % PREV_LINKS_HASH_CAPA;
>>>>>>>           pos->pos[2] = (hash >> 16) % PREV_LINKS_HASH_CAPA;
>>>>>>> -       pos->pos[3] = pos->pos[2] + 2;
>>>>>>> +       pos->pos[3] = (pos->pos[2] + 2) % PREV_LINKS_HASH_CAPA;
>>>>>>>
>>>>>>> Any way, here's v3:
>>>>>>> - excess file "v0-0001-Increase..." removed. I believe it was source
>>>>>>>     of white-space apply warnings.
>>>>>>> - this mistake fixed
>>>>>>> - more clear slots strategies + "8 positions in two cache-lines"
>>>>>>> strategy.
>>>>>>>
>>>>>>> You may play with switching PREV_LINKS_HASH_STRATEGY to 2 or 3
>>>>>>> and see
>>>>>>> if it affects measurably.
>>>>>>
>>>>>> Thanks for your quick fixing.  I will retest it tomorrow.
>>>>> Hi, Yura Sokolov
>>>>> Here is my test result of the v3 patch:
>>>>> | case                          | min        | avg        | max
>>>>> |
>>>>> |-------------------------------+------------+------------
>>>>> +------------|
>>>>> | master (44b61efb79)           | 865,743.55 | 871,237.40 |
>>>>> 874,492.59 |
>>>>> | v3                            | 857,020.58 | 860,180.11 |
>>>>> 864,355.00 |
>>>>> | v3 PREV_LINKS_HASH_STRATEGY=2 | 853,187.41 | 855,796.36 |
>>>>> 858,436.44 |
>>>>> | v3 PREV_LINKS_HASH_STRATEGY=3 | 863,131.97 | 864,272.91 |
>>>>> 865,396.42 |
>>>>> It seems there are some performance decreases :( or something I
>>>>> missed?
>>>>>
>>>>
>>>> Hi, Japin.
>>>> (Excuse me for duplicating message, I found I sent it only to you
>>>> first time).
>>>>
>>> Never mind!
>>>
>>>> v3 (as well as v2) doesn't increase NUM_XLOGINSERT_LOCKS itself.
>>>> With only 8 in-progress inserters spin-lock is certainly better than
>>>> any
>>>> more complex solution.
>>>>
>>>> You need to compare "master" vs "master + NUM_XLOGINSERT_LOCKS=64" vs
>>>> "master + NUM_XLOGINSERT_LOCKS=64 + v3".
>>>>
>>>> And even this way I don't claim "Lock-free reservation" gives any
>>>> profit.
>>>>
>>>> That is why your benchmarking is very valuable! It could answer, does
>>>> we need such not-small patch, or there is no real problem at all?
>>>>
>>
>> Hi, Yura Sokolov
>>
>> Here is the test result compared with NUM_XLOGINSERT_LOCKS and the v3
>> patch.
>>
>> | case                  | min          | avg          | max          |
>> rate% |
>> |-----------------------+--------------+--------------+--------------
>> +-------|
>> | master (4108440)      | 891,225.77   | 904,868.75   | 913,708.17   |
>>        |
>> | lock 64               | 1,007,716.95 | 1,012,013.22 | 1,018,674.00 |
>> 11.84 |
>> | lock 64 attempt 1     | 1,016,716.07 | 1,017,735.55 | 1,019,328.36 |
>> 12.47 |
>> | lock 64 attempt 2     | 1,015,328.31 | 1,018,147.74 | 1,021,513.14 |
>> 12.52 |
>> | lock 128              | 1,010,147.38 | 1,014,128.11 | 1,018,672.01 |
>> 12.07 |
>> | lock 128 attempt 1    | 1,018,154.79 | 1,023,348.35 | 1,031,365.42 |
>> 13.09 |
>> | lock 128 attempt 2    | 1,013,245.56 | 1,018,984.78 | 1,023,696.00 |
>> 12.61 |
>> | lock 64 v3            | 1,010,893.30 | 1,022,787.25 | 1,029,200.26 |
>> 13.03 |
>> | lock 64 attempt 1 v3  | 1,014,961.21 | 1,019,745.09 | 1,025,511.62 |
>> 12.70 |
>> | lock 64 attempt 2 v3  | 1,015,690.73 | 1,018,365.46 | 1,020,200.57 |
>> 12.54 |
>> | lock 128 v3           | 1,012,653.14 | 1,013,637.09 | 1,014,358.69 |
>> 12.02 |
>> | lock 128 attempt 1 v3 | 1,008,027.57 | 1,016,849.87 | 1,024,597.15 |
>> 12.38 |
>> | lock 128 attempt 2 v3 | 1,020,552.04 | 1,024,658.92 | 1,027,855.90 |
>> 13.24 |

The data looks really interesting and I recognize the need for further
investigation. I'm not very familiar with BenchmarkSQL but we've done
similar tests with HammerDB/TPCC by solely increasing
NUM_XLOGINSERT_LOCKS from 8 to 128, and we observed a significant
performance drop of ~50% and the cycle ratio of spinlock acquisition
(s_lock) rose to over 60% of the total, which is basically consistent
with the previous findings in [1].

Could you please share the details of your test environment, including
the device, configuration, and test approach, so we can collaborate on
understanding the differences?
>
> Sorry for pause, it was my birthday, so I was on short vacation.
>
> So, in total:
> - increasing NUM_XLOGINSERT_LOCKS to 64 certainly helps
> - additional lock attempts seems to help a bit in this benchmark,
>   but it helps more in other (rather synthetic) benchmark [1]
> - my version of lock-free reservation looks to help a bit when
>   applied alone, but look strange in conjunction with additional
>   lock attempts.
>
> I don't see small improvement from my version of Lock-Free reservation
> (1.1% = 1023/1012) pays for its complexity at the moment.

Due to limited hardware resources, I only had the opportunity to measure
the performance impact of your v1 patch of the lock-free hash table with
64 NUM_XLOGINSERT_LOCKS and the two lock attempt patch. I observed an
improvement of *76.4%* (RSD: 4.1%) when combining them together on the
SPR with 480 vCPUs. I understand that your test devices may not have as
many cores, which might be why this optimization brings an unnoticeable
impact. However, I don't think this is an unreal problem. In fact, this
issue was raised by our customer who is trying to deploy Postgres on
devices with hundreds of cores, and I believe the resolution of this
performance issue would result in real impacts.

>
> Probably, when other places will be optimized/improved, it will pay
> more.
>
> Or probably Zhiguo Zhou's version will perform better.
>

Our primary difference lies in the approach to handling the prev-link,
either via the hash table or directly within the XLog buffer. During my
analysis, I didn't identify significant hotspots in the hash table
functions, leading me to believe that both implementations should
achieve comparable performance improvements.

Following your advice, I revised my implementation to update the
prev-link atomically and resolved the known TAP tests. However, I
encountered the last failure in the recovery/t/027_stream_regress.pl
test. Addressing this issue might require a redesign of the underlying
writing convention of XLog, which I believe is not necessary, especially
since your implementation already achieves the desired performance
improvements without suffering from the test failures. I think we may
need to focus on your implementation in the next phase.

> I think, we could measure theoretical benefit by completely ignoring
> fill of xl_prev. I've attached patch "Dumb-lock-free..." so you could
> measure. It passes almost all "recovery" tests, though fails two
> strictly dependent on xl_prev.

I currently don't have access to the high-core-count device, but I plan
to measure the performance impact of your latest patch and the
"Dump-lock-free..." patch once I regain access.
>
> [1] https://postgr.es/m/3b11fdc2-9793-403d-
> b3d4-67ff9a00d447%40postgrespro.ru
>
> ------
>
> regards
> Yura

Hi Yura and Japin,

Thanks so much for your recent patch works and discussions which
inspired me a lot! I agree with you that we need to:
- Align the test approach and environment
- Address the motivation and necessity of this optimization
- Further identify the optimization opportunities after applying Yura's
patch

WDYT?

[1]
https://www.postgresql.org/message-id/6ykez6chr5wfiveuv2iby236mb7ab6fqwpxghppdi5ugb4kdyt%40lkrn4maox2wj

Regards,
Zhiguo

On Mon, 27 Jan 2025 at 17:30, "Zhou, Zhiguo" <zhiguo(dot)zhou(at)intel(dot)com> wrote:
> On 1/26/2025 10:59 PM, Yura Sokolov wrote:
>> 24.01.2025 12:07, Japin Li пишет:
>>> On Thu, 23 Jan 2025 at 21:44, Japin Li <japinli(at)hotmail(dot)com> wrote:
>>>> On Thu, 23 Jan 2025 at 15:03, Yura Sokolov
>>>> <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>>>>> 23.01.2025 11:46, Japin Li пишет:
>>>>>> On Wed, 22 Jan 2025 at 22:44, Japin Li <japinli(at)hotmail(dot)com> wrote:
>>>>>>> On Wed, 22 Jan 2025 at 17:02, Yura Sokolov
>>>>>>> <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>>>>>>>> I believe, I know why it happens: I was in hurry making v2 by
>>>>>>>> cherry-picking internal version. I reverted some changes in
>>>>>>>> CalcCuckooPositions manually and forgot to add modulo
>>>>>>>> PREV_LINKS_HASH_CAPA.
>>>>>>>>
>>>>>>>> Here's the fix:
>>>>>>>>
>>>>>>>>           pos->pos[0] = hash % PREV_LINKS_HASH_CAPA;
>>>>>>>> -       pos->pos[1] = pos->pos[0] + 1;
>>>>>>>> +       pos->pos[1] = (pos->pos[0] + 1) % PREV_LINKS_HASH_CAPA;
>>>>>>>>           pos->pos[2] = (hash >> 16) % PREV_LINKS_HASH_CAPA;
>>>>>>>> -       pos->pos[3] = pos->pos[2] + 2;
>>>>>>>> +       pos->pos[3] = (pos->pos[2] + 2) % PREV_LINKS_HASH_CAPA;
>>>>>>>>
>>>>>>>> Any way, here's v3:
>>>>>>>> - excess file "v0-0001-Increase..." removed. I believe it was source
>>>>>>>>     of white-space apply warnings.
>>>>>>>> - this mistake fixed
>>>>>>>> - more clear slots strategies + "8 positions in two
>>>>>>>> cache-lines" strategy.
>>>>>>>>
>>>>>>>> You may play with switching PREV_LINKS_HASH_STRATEGY to 2 or 3
>>>>>>>> and see
>>>>>>>> if it affects measurably.
>>>>>>>
>>>>>>> Thanks for your quick fixing.  I will retest it tomorrow.
>>>>>> Hi, Yura Sokolov
>>>>>> Here is my test result of the v3 patch:
>>>>>> | case                          | min        | avg        | max
>>>>>> |
>>>>>> |-------------------------------+------------+------------
>>>>>> +------------|
>>>>>> | master (44b61efb79)           | 865,743.55 | 871,237.40 |
>>>>>> 874,492.59 |
>>>>>> | v3                            | 857,020.58 | 860,180.11 |
>>>>>> 864,355.00 |
>>>>>> | v3 PREV_LINKS_HASH_STRATEGY=2 | 853,187.41 | 855,796.36 |
>>>>>> 858,436.44 |
>>>>>> | v3 PREV_LINKS_HASH_STRATEGY=3 | 863,131.97 | 864,272.91 |
>>>>>> 865,396.42 |
>>>>>> It seems there are some performance decreases :( or something I
>>>>>> missed?
>>>>>>
>>>>>
>>>>> Hi, Japin.
>>>>> (Excuse me for duplicating message, I found I sent it only to you
>>>>> first time).
>>>>>
>>>> Never mind!
>>>>
>>>>> v3 (as well as v2) doesn't increase NUM_XLOGINSERT_LOCKS itself.
>>>>> With only 8 in-progress inserters spin-lock is certainly better
>>>>> than any
>>>>> more complex solution.
>>>>>
>>>>> You need to compare "master" vs "master + NUM_XLOGINSERT_LOCKS=64" vs
>>>>> "master + NUM_XLOGINSERT_LOCKS=64 + v3".
>>>>>
>>>>> And even this way I don't claim "Lock-free reservation" gives any
>>>>> profit.
>>>>>
>>>>> That is why your benchmarking is very valuable! It could answer, does
>>>>> we need such not-small patch, or there is no real problem at all?
>>>>>
>>>
>>> Hi, Yura Sokolov
>>>
>>> Here is the test result compared with NUM_XLOGINSERT_LOCKS and the
>>> v3 patch.
>>>
>>> | case                  | min          | avg          |
>>> max          | rate% |
>>> |-----------------------+--------------+--------------+--------------
>>> +-------|
>>> | master (4108440)      | 891,225.77   | 904,868.75   |
>>> 913,708.17   |        |
>>> | lock 64               | 1,007,716.95 | 1,012,013.22 |
>>> 1,018,674.00 | 11.84 |
>>> | lock 64 attempt 1     | 1,016,716.07 | 1,017,735.55 |
>>> 1,019,328.36 | 12.47 |
>>> | lock 64 attempt 2     | 1,015,328.31 | 1,018,147.74 |
>>> 1,021,513.14 | 12.52 |
>>> | lock 128              | 1,010,147.38 | 1,014,128.11 |
>>> 1,018,672.01 | 12.07 |
>>> | lock 128 attempt 1    | 1,018,154.79 | 1,023,348.35 |
>>> 1,031,365.42 | 13.09 |
>>> | lock 128 attempt 2    | 1,013,245.56 | 1,018,984.78 |
>>> 1,023,696.00 | 12.61 |
>>> | lock 64 v3            | 1,010,893.30 | 1,022,787.25 |
>>> 1,029,200.26 | 13.03 |
>>> | lock 64 attempt 1 v3  | 1,014,961.21 | 1,019,745.09 |
>>> 1,025,511.62 | 12.70 |
>>> | lock 64 attempt 2 v3  | 1,015,690.73 | 1,018,365.46 |
>>> 1,020,200.57 | 12.54 |
>>> | lock 128 v3           | 1,012,653.14 | 1,013,637.09 |
>>> 1,014,358.69 | 12.02 |
>>> | lock 128 attempt 1 v3 | 1,008,027.57 | 1,016,849.87 |
>>> 1,024,597.15 | 12.38 |
>>> | lock 128 attempt 2 v3 | 1,020,552.04 | 1,024,658.92 |
>>> 1,027,855.90 | 13.24 |
>
> The data looks really interesting and I recognize the need for further
> investigation. I'm not very familiar with BenchmarkSQL but we've done
> similar tests with HammerDB/TPCC by solely increasing
> NUM_XLOGINSERT_LOCKS from 8 to 128, and we observed a significant
> performance drop of ~50% and the cycle ratio of spinlock acquisition
> (s_lock) rose to over 60% of the total, which is basically consistent
> with the previous findings in [1].
>
> Could you please share the details of your test environment, including
> the device, configuration, and test approach, so we can collaborate on
> understanding the differences?

Sorry for the late reply. I'm on my vacation.

I use Hygon C86 7490 64-core, it has 8 NUMA nodes with 1.5T memory, and
I use 0-3 run the database, and 4-7 run the BenchmarkSQL.

Here is my database settings:

listen_addresses = '*'
max_connections = '1050'
shared_buffers = '100GB'
work_mem = '64MB'
maintenance_work_mem = '512MB'
max_wal_size = '50GB'
min_wal_size = '10GB'
random_page_cost = '1.1'
wal_buffers = '1GB'
wal_level = 'minimal'
max_wal_senders = '0'
wal_sync_method = 'open_datasync'
wal_compression = 'lz4'
track_activities = 'off'
checkpoint_timeout = '1d'
checkpoint_completion_target = '0.95'
effective_cache_size = '300GB'
effective_io_concurrency = '32'
update_process_title = 'off'
password_encryption = 'md5'
huge_pages = 'on'

>> Sorry for pause, it was my birthday, so I was on short vacation.
>> So, in total:
>> - increasing NUM_XLOGINSERT_LOCKS to 64 certainly helps
>> - additional lock attempts seems to help a bit in this benchmark,
>>   but it helps more in other (rather synthetic) benchmark [1]
>> - my version of lock-free reservation looks to help a bit when
>>   applied alone, but look strange in conjunction with additional
>>   lock attempts.
>> I don't see small improvement from my version of Lock-Free
>> reservation
>> (1.1% = 1023/1012) pays for its complexity at the moment.
>
> Due to limited hardware resources, I only had the opportunity to
> measure the performance impact of your v1 patch of the lock-free hash
> table with 64 NUM_XLOGINSERT_LOCKS and the two lock attempt patch. I
> observed an improvement of *76.4%* (RSD: 4.1%) when combining them
> together on the SPR with 480 vCPUs. I understand that your test
> devices may not have as many cores, which might be why this
> optimization brings an unnoticeable impact. However, I don't think
> this is an unreal problem. In fact, this issue was raised by our
> customer who is trying to deploy Postgres on devices with hundreds of
> cores, and I believe the resolution of this performance issue would
> result in real impacts.
>
>> Probably, when other places will be optimized/improved, it will pay
>> more.
>> Or probably Zhiguo Zhou's version will perform better.
>>
>
> Our primary difference lies in the approach to handling the prev-link,
> either via the hash table or directly within the XLog buffer. During
> my analysis, I didn't identify significant hotspots in the hash table
> functions, leading me to believe that both implementations should
> achieve comparable performance improvements.
>
> Following your advice, I revised my implementation to update the
> prev-link atomically and resolved the known TAP tests. However, I
> encountered the last failure in the recovery/t/027_stream_regress.pl
> test. Addressing this issue might require a redesign of the underlying
> writing convention of XLog, which I believe is not necessary,
> especially since your implementation already achieves the desired
> performance improvements without suffering from the test failures. I
> think we may need to focus on your implementation in the next phase.
>
>> I think, we could measure theoretical benefit by completely ignoring
>> fill of xl_prev. I've attached patch "Dumb-lock-free..." so you could
>> measure. It passes almost all "recovery" tests, though fails two
>> strictly dependent on xl_prev.
>

> I currently don't have access to the high-core-count device, but I
> plan to measure the performance impact of your latest patch and the
> "Dump-lock-free..." patch once I regain access.
>> [1] https://postgr.es/m/3b11fdc2-9793-403d-
>> b3d4-67ff9a00d447%40postgrespro.ru
>> ------
>> regards
>> Yura
>
> Hi Yura and Japin,
>
> Thanks so much for your recent patch works and discussions which
> inspired me a lot! I agree with you that we need to:
> - Align the test approach and environment
> - Address the motivation and necessity of this optimization
> - Further identify the optimization opportunities after applying
> Yura's patch
>
> WDYT?
>
> [1]
> https://www.postgresql.org/message-id/6ykez6chr5wfiveuv2iby236mb7ab6fqwpxghppdi5ugb4kdyt%40lkrn4maox2wj
>
> Regards,
> Zhiguo

--
Regrads,
Japin Li

On 2/5/2025 4:32 PM, Japin Li wrote:
> On Mon, 27 Jan 2025 at 17:30, "Zhou, Zhiguo" <zhiguo(dot)zhou(at)intel(dot)com> wrote:
>> On 1/26/2025 10:59 PM, Yura Sokolov wrote:
>>> 24.01.2025 12:07, Japin Li пишет:
>>>> On Thu, 23 Jan 2025 at 21:44, Japin Li <japinli(at)hotmail(dot)com> wrote:
>>>>> On Thu, 23 Jan 2025 at 15:03, Yura Sokolov
>>>>> <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>>>>>> 23.01.2025 11:46, Japin Li пишет:
>>>>>>> On Wed, 22 Jan 2025 at 22:44, Japin Li <japinli(at)hotmail(dot)com> wrote:
>>>>>>>> On Wed, 22 Jan 2025 at 17:02, Yura Sokolov
>>>>>>>> <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>>>>>>>>> I believe, I know why it happens: I was in hurry making v2 by
>>>>>>>>> cherry-picking internal version. I reverted some changes in
>>>>>>>>> CalcCuckooPositions manually and forgot to add modulo
>>>>>>>>> PREV_LINKS_HASH_CAPA.
>>>>>>>>>
>>>>>>>>> Here's the fix:
>>>>>>>>>
>>>>>>>>>           pos->pos[0] = hash % PREV_LINKS_HASH_CAPA;
>>>>>>>>> -       pos->pos[1] = pos->pos[0] + 1;
>>>>>>>>> +       pos->pos[1] = (pos->pos[0] + 1) % PREV_LINKS_HASH_CAPA;
>>>>>>>>>           pos->pos[2] = (hash >> 16) % PREV_LINKS_HASH_CAPA;
>>>>>>>>> -       pos->pos[3] = pos->pos[2] + 2;
>>>>>>>>> +       pos->pos[3] = (pos->pos[2] + 2) % PREV_LINKS_HASH_CAPA;
>>>>>>>>>
>>>>>>>>> Any way, here's v3:
>>>>>>>>> - excess file "v0-0001-Increase..." removed. I believe it was source
>>>>>>>>>     of white-space apply warnings.
>>>>>>>>> - this mistake fixed
>>>>>>>>> - more clear slots strategies + "8 positions in two
>>>>>>>>> cache-lines" strategy.
>>>>>>>>>
>>>>>>>>> You may play with switching PREV_LINKS_HASH_STRATEGY to 2 or 3
>>>>>>>>> and see
>>>>>>>>> if it affects measurably.
>>>>>>>>
>>>>>>>> Thanks for your quick fixing.  I will retest it tomorrow.
>>>>>>> Hi, Yura Sokolov
>>>>>>> Here is my test result of the v3 patch:
>>>>>>> | case                          | min        | avg        | max
>>>>>>> |
>>>>>>> |-------------------------------+------------+------------
>>>>>>> +------------|
>>>>>>> | master (44b61efb79)           | 865,743.55 | 871,237.40 |
>>>>>>> 874,492.59 |
>>>>>>> | v3                            | 857,020.58 | 860,180.11 |
>>>>>>> 864,355.00 |
>>>>>>> | v3 PREV_LINKS_HASH_STRATEGY=2 | 853,187.41 | 855,796.36 |
>>>>>>> 858,436.44 |
>>>>>>> | v3 PREV_LINKS_HASH_STRATEGY=3 | 863,131.97 | 864,272.91 |
>>>>>>> 865,396.42 |
>>>>>>> It seems there are some performance decreases :( or something I
>>>>>>> missed?
>>>>>>>
>>>>>>
>>>>>> Hi, Japin.
>>>>>> (Excuse me for duplicating message, I found I sent it only to you
>>>>>> first time).
>>>>>>
>>>>> Never mind!
>>>>>
>>>>>> v3 (as well as v2) doesn't increase NUM_XLOGINSERT_LOCKS itself.
>>>>>> With only 8 in-progress inserters spin-lock is certainly better
>>>>>> than any
>>>>>> more complex solution.
>>>>>>
>>>>>> You need to compare "master" vs "master + NUM_XLOGINSERT_LOCKS=64" vs
>>>>>> "master + NUM_XLOGINSERT_LOCKS=64 + v3".
>>>>>>
>>>>>> And even this way I don't claim "Lock-free reservation" gives any
>>>>>> profit.
>>>>>>
>>>>>> That is why your benchmarking is very valuable! It could answer, does
>>>>>> we need such not-small patch, or there is no real problem at all?
>>>>>>
>>>>
>>>> Hi, Yura Sokolov
>>>>
>>>> Here is the test result compared with NUM_XLOGINSERT_LOCKS and the
>>>> v3 patch.
>>>>
>>>> | case                  | min          | avg          |
>>>> max          | rate% |
>>>> |-----------------------+--------------+--------------+--------------
>>>> +-------|
>>>> | master (4108440)      | 891,225.77   | 904,868.75   |
>>>> 913,708.17   |        |
>>>> | lock 64               | 1,007,716.95 | 1,012,013.22 |
>>>> 1,018,674.00 | 11.84 |
>>>> | lock 64 attempt 1     | 1,016,716.07 | 1,017,735.55 |
>>>> 1,019,328.36 | 12.47 |
>>>> | lock 64 attempt 2     | 1,015,328.31 | 1,018,147.74 |
>>>> 1,021,513.14 | 12.52 |
>>>> | lock 128              | 1,010,147.38 | 1,014,128.11 |
>>>> 1,018,672.01 | 12.07 |
>>>> | lock 128 attempt 1    | 1,018,154.79 | 1,023,348.35 |
>>>> 1,031,365.42 | 13.09 |
>>>> | lock 128 attempt 2    | 1,013,245.56 | 1,018,984.78 |
>>>> 1,023,696.00 | 12.61 |
>>>> | lock 64 v3            | 1,010,893.30 | 1,022,787.25 |
>>>> 1,029,200.26 | 13.03 |
>>>> | lock 64 attempt 1 v3  | 1,014,961.21 | 1,019,745.09 |
>>>> 1,025,511.62 | 12.70 |
>>>> | lock 64 attempt 2 v3  | 1,015,690.73 | 1,018,365.46 |
>>>> 1,020,200.57 | 12.54 |
>>>> | lock 128 v3           | 1,012,653.14 | 1,013,637.09 |
>>>> 1,014,358.69 | 12.02 |
>>>> | lock 128 attempt 1 v3 | 1,008,027.57 | 1,016,849.87 |
>>>> 1,024,597.15 | 12.38 |
>>>> | lock 128 attempt 2 v3 | 1,020,552.04 | 1,024,658.92 |
>>>> 1,027,855.90 | 13.24 |
>>
>> The data looks really interesting and I recognize the need for further
>> investigation. I'm not very familiar with BenchmarkSQL but we've done
>> similar tests with HammerDB/TPCC by solely increasing
>> NUM_XLOGINSERT_LOCKS from 8 to 128, and we observed a significant
>> performance drop of ~50% and the cycle ratio of spinlock acquisition
>> (s_lock) rose to over 60% of the total, which is basically consistent
>> with the previous findings in [1].
>>
>> Could you please share the details of your test environment, including
>> the device, configuration, and test approach, so we can collaborate on
>> understanding the differences?
>
> Sorry for the late reply. I'm on my vacation.
>
> I use Hygon C86 7490 64-core, it has 8 NUMA nodes with 1.5T memory, and
> I use 0-3 run the database, and 4-7 run the BenchmarkSQL.
>
> Here is my database settings:
>
> listen_addresses = '*'
> max_connections = '1050'
> shared_buffers = '100GB'
> work_mem = '64MB'
> maintenance_work_mem = '512MB'
> max_wal_size = '50GB'
> min_wal_size = '10GB'
> random_page_cost = '1.1'
> wal_buffers = '1GB'
> wal_level = 'minimal'
> max_wal_senders = '0'
> wal_sync_method = 'open_datasync'
> wal_compression = 'lz4'
> track_activities = 'off'
> checkpoint_timeout = '1d'
> checkpoint_completion_target = '0.95'
> effective_cache_size = '300GB'
> effective_io_concurrency = '32'
> update_process_title = 'off'
> password_encryption = 'md5'
> huge_pages = 'on'
>
>>> Sorry for pause, it was my birthday, so I was on short vacation.
>>> So, in total:
>>> - increasing NUM_XLOGINSERT_LOCKS to 64 certainly helps
>>> - additional lock attempts seems to help a bit in this benchmark,
>>>   but it helps more in other (rather synthetic) benchmark [1]
>>> - my version of lock-free reservation looks to help a bit when
>>>   applied alone, but look strange in conjunction with additional
>>>   lock attempts.
>>> I don't see small improvement from my version of Lock-Free
>>> reservation
>>> (1.1% = 1023/1012) pays for its complexity at the moment.
>>
>> Due to limited hardware resources, I only had the opportunity to
>> measure the performance impact of your v1 patch of the lock-free hash
>> table with 64 NUM_XLOGINSERT_LOCKS and the two lock attempt patch. I
>> observed an improvement of *76.4%* (RSD: 4.1%) when combining them
>> together on the SPR with 480 vCPUs. I understand that your test
>> devices may not have as many cores, which might be why this
>> optimization brings an unnoticeable impact. However, I don't think
>> this is an unreal problem. In fact, this issue was raised by our
>> customer who is trying to deploy Postgres on devices with hundreds of
>> cores, and I believe the resolution of this performance issue would
>> result in real impacts.
>>
>>> Probably, when other places will be optimized/improved, it will pay
>>> more.
>>> Or probably Zhiguo Zhou's version will perform better.
>>>
>>
>> Our primary difference lies in the approach to handling the prev-link,
>> either via the hash table or directly within the XLog buffer. During
>> my analysis, I didn't identify significant hotspots in the hash table
>> functions, leading me to believe that both implementations should
>> achieve comparable performance improvements.
>>
>> Following your advice, I revised my implementation to update the
>> prev-link atomically and resolved the known TAP tests. However, I
>> encountered the last failure in the recovery/t/027_stream_regress.pl
>> test. Addressing this issue might require a redesign of the underlying
>> writing convention of XLog, which I believe is not necessary,
>> especially since your implementation already achieves the desired
>> performance improvements without suffering from the test failures. I
>> think we may need to focus on your implementation in the next phase.
>>
>>> I think, we could measure theoretical benefit by completely ignoring
>>> fill of xl_prev. I've attached patch "Dumb-lock-free..." so you could
>>> measure. It passes almost all "recovery" tests, though fails two
>>> strictly dependent on xl_prev.
>>
>
>> I currently don't have access to the high-core-count device, but I
>> plan to measure the performance impact of your latest patch and the
>> "Dump-lock-free..." patch once I regain access.
>>> [1] https://postgr.es/m/3b11fdc2-9793-403d-
>>> b3d4-67ff9a00d447%40postgrespro.ru
>>> ------
>>> regards
>>> Yura
>>
>> Hi Yura and Japin,
>>
>> Thanks so much for your recent patch works and discussions which
>> inspired me a lot! I agree with you that we need to:
>> - Align the test approach and environment
>> - Address the motivation and necessity of this optimization
>> - Further identify the optimization opportunities after applying
>> Yura's patch
>>
>> WDYT?
>>
>> [1]
>> https://www.postgresql.org/message-id/6ykez6chr5wfiveuv2iby236mb7ab6fqwpxghppdi5ugb4kdyt%40lkrn4maox2wj
>>
>> Regards,
>> Zhiguo
>

Hi Japin,

Apologies for the delay in responding—I've just returned from vacation.
To move things forward, I'll be running the BenchmarkSQL workload on my
end shortly.

In the meantime, could you run the HammerDB/TPCC workload on your
device? We've observed significant performance improvements with this
test, and it might help clarify whether the discrepancies we're seeing
stem from the workload itself. Thanks!

Regards,
Zhiguo

On Mon, 10 Feb 2025 at 22:12, "Zhou, Zhiguo" <zhiguo(dot)zhou(at)intel(dot)com> wrote:
> On 2/5/2025 4:32 PM, Japin Li wrote:
>> On Mon, 27 Jan 2025 at 17:30, "Zhou, Zhiguo" <zhiguo(dot)zhou(at)intel(dot)com> wrote:
>>> On 1/26/2025 10:59 PM, Yura Sokolov wrote:
>>>> 24.01.2025 12:07, Japin Li пишет:
>>>>> On Thu, 23 Jan 2025 at 21:44, Japin Li <japinli(at)hotmail(dot)com> wrote:
>>>>>> On Thu, 23 Jan 2025 at 15:03, Yura Sokolov
>>>>>> <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>>>>>>> 23.01.2025 11:46, Japin Li пишет:
>>>>>>>> On Wed, 22 Jan 2025 at 22:44, Japin Li <japinli(at)hotmail(dot)com> wrote:
>>>>>>>>> On Wed, 22 Jan 2025 at 17:02, Yura Sokolov
>>>>>>>>> <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>>>>>>>>>> I believe, I know why it happens: I was in hurry making v2 by
>>>>>>>>>> cherry-picking internal version. I reverted some changes in
>>>>>>>>>> CalcCuckooPositions manually and forgot to add modulo
>>>>>>>>>> PREV_LINKS_HASH_CAPA.
>>>>>>>>>>
>>>>>>>>>> Here's the fix:
>>>>>>>>>>
>>>>>>>>>>           pos->pos[0] = hash % PREV_LINKS_HASH_CAPA;
>>>>>>>>>> -       pos->pos[1] = pos->pos[0] + 1;
>>>>>>>>>> +       pos->pos[1] = (pos->pos[0] + 1) % PREV_LINKS_HASH_CAPA;
>>>>>>>>>>           pos->pos[2] = (hash >> 16) % PREV_LINKS_HASH_CAPA;
>>>>>>>>>> -       pos->pos[3] = pos->pos[2] + 2;
>>>>>>>>>> +       pos->pos[3] = (pos->pos[2] + 2) % PREV_LINKS_HASH_CAPA;
>>>>>>>>>>
>>>>>>>>>> Any way, here's v3:
>>>>>>>>>> - excess file "v0-0001-Increase..." removed. I believe it was source
>>>>>>>>>>     of white-space apply warnings.
>>>>>>>>>> - this mistake fixed
>>>>>>>>>> - more clear slots strategies + "8 positions in two
>>>>>>>>>> cache-lines" strategy.
>>>>>>>>>>
>>>>>>>>>> You may play with switching PREV_LINKS_HASH_STRATEGY to 2 or 3
>>>>>>>>>> and see
>>>>>>>>>> if it affects measurably.
>>>>>>>>>
>>>>>>>>> Thanks for your quick fixing.  I will retest it tomorrow.
>>>>>>>> Hi, Yura Sokolov
>>>>>>>> Here is my test result of the v3 patch:
>>>>>>>> | case                          | min        | avg        | max
>>>>>>>> |
>>>>>>>> |-------------------------------+------------+------------
>>>>>>>> +------------|
>>>>>>>> | master (44b61efb79)           | 865,743.55 | 871,237.40 |
>>>>>>>> 874,492.59 |
>>>>>>>> | v3                            | 857,020.58 | 860,180.11 |
>>>>>>>> 864,355.00 |
>>>>>>>> | v3 PREV_LINKS_HASH_STRATEGY=2 | 853,187.41 | 855,796.36 |
>>>>>>>> 858,436.44 |
>>>>>>>> | v3 PREV_LINKS_HASH_STRATEGY=3 | 863,131.97 | 864,272.91 |
>>>>>>>> 865,396.42 |
>>>>>>>> It seems there are some performance decreases :( or something I
>>>>>>>> missed?
>>>>>>>>
>>>>>>>
>>>>>>> Hi, Japin.
>>>>>>> (Excuse me for duplicating message, I found I sent it only to you
>>>>>>> first time).
>>>>>>>
>>>>>> Never mind!
>>>>>>
>>>>>>> v3 (as well as v2) doesn't increase NUM_XLOGINSERT_LOCKS itself.
>>>>>>> With only 8 in-progress inserters spin-lock is certainly better
>>>>>>> than any
>>>>>>> more complex solution.
>>>>>>>
>>>>>>> You need to compare "master" vs "master + NUM_XLOGINSERT_LOCKS=64" vs
>>>>>>> "master + NUM_XLOGINSERT_LOCKS=64 + v3".
>>>>>>>
>>>>>>> And even this way I don't claim "Lock-free reservation" gives any
>>>>>>> profit.
>>>>>>>
>>>>>>> That is why your benchmarking is very valuable! It could answer, does
>>>>>>> we need such not-small patch, or there is no real problem at all?
>>>>>>>
>>>>>
>>>>> Hi, Yura Sokolov
>>>>>
>>>>> Here is the test result compared with NUM_XLOGINSERT_LOCKS and the
>>>>> v3 patch.
>>>>>
>>>>> | case                  | min          | avg          |
>>>>> max          | rate% |
>>>>> |-----------------------+--------------+--------------+--------------
>>>>> +-------|
>>>>> | master (4108440)      | 891,225.77   | 904,868.75   |
>>>>> 913,708.17   |        |
>>>>> | lock 64               | 1,007,716.95 | 1,012,013.22 |
>>>>> 1,018,674.00 | 11.84 |
>>>>> | lock 64 attempt 1     | 1,016,716.07 | 1,017,735.55 |
>>>>> 1,019,328.36 | 12.47 |
>>>>> | lock 64 attempt 2     | 1,015,328.31 | 1,018,147.74 |
>>>>> 1,021,513.14 | 12.52 |
>>>>> | lock 128              | 1,010,147.38 | 1,014,128.11 |
>>>>> 1,018,672.01 | 12.07 |
>>>>> | lock 128 attempt 1    | 1,018,154.79 | 1,023,348.35 |
>>>>> 1,031,365.42 | 13.09 |
>>>>> | lock 128 attempt 2    | 1,013,245.56 | 1,018,984.78 |
>>>>> 1,023,696.00 | 12.61 |
>>>>> | lock 64 v3            | 1,010,893.30 | 1,022,787.25 |
>>>>> 1,029,200.26 | 13.03 |
>>>>> | lock 64 attempt 1 v3  | 1,014,961.21 | 1,019,745.09 |
>>>>> 1,025,511.62 | 12.70 |
>>>>> | lock 64 attempt 2 v3  | 1,015,690.73 | 1,018,365.46 |
>>>>> 1,020,200.57 | 12.54 |
>>>>> | lock 128 v3           | 1,012,653.14 | 1,013,637.09 |
>>>>> 1,014,358.69 | 12.02 |
>>>>> | lock 128 attempt 1 v3 | 1,008,027.57 | 1,016,849.87 |
>>>>> 1,024,597.15 | 12.38 |
>>>>> | lock 128 attempt 2 v3 | 1,020,552.04 | 1,024,658.92 |
>>>>> 1,027,855.90 | 13.24 |
>>>
>>> The data looks really interesting and I recognize the need for further
>>> investigation. I'm not very familiar with BenchmarkSQL but we've done
>>> similar tests with HammerDB/TPCC by solely increasing
>>> NUM_XLOGINSERT_LOCKS from 8 to 128, and we observed a significant
>>> performance drop of ~50% and the cycle ratio of spinlock acquisition
>>> (s_lock) rose to over 60% of the total, which is basically consistent
>>> with the previous findings in [1].
>>>
>>> Could you please share the details of your test environment, including
>>> the device, configuration, and test approach, so we can collaborate on
>>> understanding the differences?
>> Sorry for the late reply. I'm on my vacation.
>> I use Hygon C86 7490 64-core, it has 8 NUMA nodes with 1.5T memory,
>> and
>> I use 0-3 run the database, and 4-7 run the BenchmarkSQL.
>> Here is my database settings:
>> listen_addresses = '*'
>> max_connections = '1050'
>> shared_buffers = '100GB'
>> work_mem = '64MB'
>> maintenance_work_mem = '512MB'
>> max_wal_size = '50GB'
>> min_wal_size = '10GB'
>> random_page_cost = '1.1'
>> wal_buffers = '1GB'
>> wal_level = 'minimal'
>> max_wal_senders = '0'
>> wal_sync_method = 'open_datasync'
>> wal_compression = 'lz4'
>> track_activities = 'off'
>> checkpoint_timeout = '1d'
>> checkpoint_completion_target = '0.95'
>> effective_cache_size = '300GB'
>> effective_io_concurrency = '32'
>> update_process_title = 'off'
>> password_encryption = 'md5'
>> huge_pages = 'on'
>>
>>>> Sorry for pause, it was my birthday, so I was on short vacation.
>>>> So, in total:
>>>> - increasing NUM_XLOGINSERT_LOCKS to 64 certainly helps
>>>> - additional lock attempts seems to help a bit in this benchmark,
>>>>   but it helps more in other (rather synthetic) benchmark [1]
>>>> - my version of lock-free reservation looks to help a bit when
>>>>   applied alone, but look strange in conjunction with additional
>>>>   lock attempts.
>>>> I don't see small improvement from my version of Lock-Free
>>>> reservation
>>>> (1.1% = 1023/1012) pays for its complexity at the moment.
>>>
>>> Due to limited hardware resources, I only had the opportunity to
>>> measure the performance impact of your v1 patch of the lock-free hash
>>> table with 64 NUM_XLOGINSERT_LOCKS and the two lock attempt patch. I
>>> observed an improvement of *76.4%* (RSD: 4.1%) when combining them
>>> together on the SPR with 480 vCPUs. I understand that your test
>>> devices may not have as many cores, which might be why this
>>> optimization brings an unnoticeable impact. However, I don't think
>>> this is an unreal problem. In fact, this issue was raised by our
>>> customer who is trying to deploy Postgres on devices with hundreds of
>>> cores, and I believe the resolution of this performance issue would
>>> result in real impacts.
>>>
>>>> Probably, when other places will be optimized/improved, it will pay
>>>> more.
>>>> Or probably Zhiguo Zhou's version will perform better.
>>>>
>>>
>>> Our primary difference lies in the approach to handling the prev-link,
>>> either via the hash table or directly within the XLog buffer. During
>>> my analysis, I didn't identify significant hotspots in the hash table
>>> functions, leading me to believe that both implementations should
>>> achieve comparable performance improvements.
>>>
>>> Following your advice, I revised my implementation to update the
>>> prev-link atomically and resolved the known TAP tests. However, I
>>> encountered the last failure in the recovery/t/027_stream_regress.pl
>>> test. Addressing this issue might require a redesign of the underlying
>>> writing convention of XLog, which I believe is not necessary,
>>> especially since your implementation already achieves the desired
>>> performance improvements without suffering from the test failures. I
>>> think we may need to focus on your implementation in the next phase.
>>>
>>>> I think, we could measure theoretical benefit by completely ignoring
>>>> fill of xl_prev. I've attached patch "Dumb-lock-free..." so you could
>>>> measure. It passes almost all "recovery" tests, though fails two
>>>> strictly dependent on xl_prev.
>>>
>>
>>> I currently don't have access to the high-core-count device, but I
>>> plan to measure the performance impact of your latest patch and the
>>> "Dump-lock-free..." patch once I regain access.
>>>> [1] https://postgr.es/m/3b11fdc2-9793-403d-
>>>> b3d4-67ff9a00d447%40postgrespro.ru
>>>> ------
>>>> regards
>>>> Yura
>>>
>>> Hi Yura and Japin,
>>>
>>> Thanks so much for your recent patch works and discussions which
>>> inspired me a lot! I agree with you that we need to:
>>> - Align the test approach and environment
>>> - Address the motivation and necessity of this optimization
>>> - Further identify the optimization opportunities after applying
>>> Yura's patch
>>>
>>> WDYT?
>>>
>>> [1]
>>> https://www.postgresql.org/message-id/6ykez6chr5wfiveuv2iby236mb7ab6fqwpxghppdi5ugb4kdyt%40lkrn4maox2wj
>>>
>>> Regards,
>>> Zhiguo
>>
>
> Hi Japin,
>
> Apologies for the delay in responding—I've just returned from
> vacation. To move things forward, I'll be running the BenchmarkSQL
> workload on my end shortly.
>
> In the meantime, could you run the HammerDB/TPCC workload on your
> device? We've observed significant performance improvements with this
> test, and it might help clarify whether the discrepancies we're seeing
> stem from the workload itself. Thanks!
>

Sorry, I currently don't have access to the test device, I will try to test
it if I can regain access.

--
Regrads,
Japin Li

On 2/11/2025 9:25 AM, Japin Li wrote:
> On Mon, 10 Feb 2025 at 22:12, "Zhou, Zhiguo" <zhiguo(dot)zhou(at)intel(dot)com> wrote:
>> On 2/5/2025 4:32 PM, Japin Li wrote:
>>> On Mon, 27 Jan 2025 at 17:30, "Zhou, Zhiguo" <zhiguo(dot)zhou(at)intel(dot)com> wrote:
>>>> On 1/26/2025 10:59 PM, Yura Sokolov wrote:
>>>>> 24.01.2025 12:07, Japin Li пишет:
>>>>>> On Thu, 23 Jan 2025 at 21:44, Japin Li <japinli(at)hotmail(dot)com> wrote:
>>>>>>> On Thu, 23 Jan 2025 at 15:03, Yura Sokolov
>>>>>>> <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>>>>>>>> 23.01.2025 11:46, Japin Li пишет:
>>>>>>>>> On Wed, 22 Jan 2025 at 22:44, Japin Li <japinli(at)hotmail(dot)com> wrote:
>>>>>>>>>> On Wed, 22 Jan 2025 at 17:02, Yura Sokolov
>>>>>>>>>> <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>>>>>>>>>>> I believe, I know why it happens: I was in hurry making v2 by
>>>>>>>>>>> cherry-picking internal version. I reverted some changes in
>>>>>>>>>>> CalcCuckooPositions manually and forgot to add modulo
>>>>>>>>>>> PREV_LINKS_HASH_CAPA.
>>>>>>>>>>>
>>>>>>>>>>> Here's the fix:
>>>>>>>>>>>
>>>>>>>>>>>           pos->pos[0] = hash % PREV_LINKS_HASH_CAPA;
>>>>>>>>>>> -       pos->pos[1] = pos->pos[0] + 1;
>>>>>>>>>>> +       pos->pos[1] = (pos->pos[0] + 1) % PREV_LINKS_HASH_CAPA;
>>>>>>>>>>>           pos->pos[2] = (hash >> 16) % PREV_LINKS_HASH_CAPA;
>>>>>>>>>>> -       pos->pos[3] = pos->pos[2] + 2;
>>>>>>>>>>> +       pos->pos[3] = (pos->pos[2] + 2) % PREV_LINKS_HASH_CAPA;
>>>>>>>>>>>
>>>>>>>>>>> Any way, here's v3:
>>>>>>>>>>> - excess file "v0-0001-Increase..." removed. I believe it was source
>>>>>>>>>>>     of white-space apply warnings.
>>>>>>>>>>> - this mistake fixed
>>>>>>>>>>> - more clear slots strategies + "8 positions in two
>>>>>>>>>>> cache-lines" strategy.
>>>>>>>>>>>
>>>>>>>>>>> You may play with switching PREV_LINKS_HASH_STRATEGY to 2 or 3
>>>>>>>>>>> and see
>>>>>>>>>>> if it affects measurably.
>>>>>>>>>>
>>>>>>>>>> Thanks for your quick fixing.  I will retest it tomorrow.
>>>>>>>>> Hi, Yura Sokolov
>>>>>>>>> Here is my test result of the v3 patch:
>>>>>>>>> | case                          | min        | avg        | max
>>>>>>>>> |
>>>>>>>>> |-------------------------------+------------+------------
>>>>>>>>> +------------|
>>>>>>>>> | master (44b61efb79)           | 865,743.55 | 871,237.40 |
>>>>>>>>> 874,492.59 |
>>>>>>>>> | v3                            | 857,020.58 | 860,180.11 |
>>>>>>>>> 864,355.00 |
>>>>>>>>> | v3 PREV_LINKS_HASH_STRATEGY=2 | 853,187.41 | 855,796.36 |
>>>>>>>>> 858,436.44 |
>>>>>>>>> | v3 PREV_LINKS_HASH_STRATEGY=3 | 863,131.97 | 864,272.91 |
>>>>>>>>> 865,396.42 |
>>>>>>>>> It seems there are some performance decreases :( or something I
>>>>>>>>> missed?
>>>>>>>>>
>>>>>>>>
>>>>>>>> Hi, Japin.
>>>>>>>> (Excuse me for duplicating message, I found I sent it only to you
>>>>>>>> first time).
>>>>>>>>
>>>>>>> Never mind!
>>>>>>>
>>>>>>>> v3 (as well as v2) doesn't increase NUM_XLOGINSERT_LOCKS itself.
>>>>>>>> With only 8 in-progress inserters spin-lock is certainly better
>>>>>>>> than any
>>>>>>>> more complex solution.
>>>>>>>>
>>>>>>>> You need to compare "master" vs "master + NUM_XLOGINSERT_LOCKS=64" vs
>>>>>>>> "master + NUM_XLOGINSERT_LOCKS=64 + v3".
>>>>>>>>
>>>>>>>> And even this way I don't claim "Lock-free reservation" gives any
>>>>>>>> profit.
>>>>>>>>
>>>>>>>> That is why your benchmarking is very valuable! It could answer, does
>>>>>>>> we need such not-small patch, or there is no real problem at all?
>>>>>>>>
>>>>>>
>>>>>> Hi, Yura Sokolov
>>>>>>
>>>>>> Here is the test result compared with NUM_XLOGINSERT_LOCKS and the
>>>>>> v3 patch.
>>>>>>
>>>>>> | case                  | min          | avg          |
>>>>>> max          | rate% |
>>>>>> |-----------------------+--------------+--------------+--------------
>>>>>> +-------|
>>>>>> | master (4108440)      | 891,225.77   | 904,868.75   |
>>>>>> 913,708.17   |        |
>>>>>> | lock 64               | 1,007,716.95 | 1,012,013.22 |
>>>>>> 1,018,674.00 | 11.84 |
>>>>>> | lock 64 attempt 1     | 1,016,716.07 | 1,017,735.55 |
>>>>>> 1,019,328.36 | 12.47 |
>>>>>> | lock 64 attempt 2     | 1,015,328.31 | 1,018,147.74 |
>>>>>> 1,021,513.14 | 12.52 |
>>>>>> | lock 128              | 1,010,147.38 | 1,014,128.11 |
>>>>>> 1,018,672.01 | 12.07 |
>>>>>> | lock 128 attempt 1    | 1,018,154.79 | 1,023,348.35 |
>>>>>> 1,031,365.42 | 13.09 |
>>>>>> | lock 128 attempt 2    | 1,013,245.56 | 1,018,984.78 |
>>>>>> 1,023,696.00 | 12.61 |
>>>>>> | lock 64 v3            | 1,010,893.30 | 1,022,787.25 |
>>>>>> 1,029,200.26 | 13.03 |
>>>>>> | lock 64 attempt 1 v3  | 1,014,961.21 | 1,019,745.09 |
>>>>>> 1,025,511.62 | 12.70 |
>>>>>> | lock 64 attempt 2 v3  | 1,015,690.73 | 1,018,365.46 |
>>>>>> 1,020,200.57 | 12.54 |
>>>>>> | lock 128 v3           | 1,012,653.14 | 1,013,637.09 |
>>>>>> 1,014,358.69 | 12.02 |
>>>>>> | lock 128 attempt 1 v3 | 1,008,027.57 | 1,016,849.87 |
>>>>>> 1,024,597.15 | 12.38 |
>>>>>> | lock 128 attempt 2 v3 | 1,020,552.04 | 1,024,658.92 |
>>>>>> 1,027,855.90 | 13.24 |
>>>>
>>>> The data looks really interesting and I recognize the need for further
>>>> investigation. I'm not very familiar with BenchmarkSQL but we've done
>>>> similar tests with HammerDB/TPCC by solely increasing
>>>> NUM_XLOGINSERT_LOCKS from 8 to 128, and we observed a significant
>>>> performance drop of ~50% and the cycle ratio of spinlock acquisition
>>>> (s_lock) rose to over 60% of the total, which is basically consistent
>>>> with the previous findings in [1].
>>>>
>>>> Could you please share the details of your test environment, including
>>>> the device, configuration, and test approach, so we can collaborate on
>>>> understanding the differences?
>>> Sorry for the late reply. I'm on my vacation.
>>> I use Hygon C86 7490 64-core, it has 8 NUMA nodes with 1.5T memory,
>>> and
>>> I use 0-3 run the database, and 4-7 run the BenchmarkSQL.
>>> Here is my database settings:
>>> listen_addresses = '*'
>>> max_connections = '1050'
>>> shared_buffers = '100GB'
>>> work_mem = '64MB'
>>> maintenance_work_mem = '512MB'
>>> max_wal_size = '50GB'
>>> min_wal_size = '10GB'
>>> random_page_cost = '1.1'
>>> wal_buffers = '1GB'
>>> wal_level = 'minimal'
>>> max_wal_senders = '0'
>>> wal_sync_method = 'open_datasync'
>>> wal_compression = 'lz4'
>>> track_activities = 'off'
>>> checkpoint_timeout = '1d'
>>> checkpoint_completion_target = '0.95'
>>> effective_cache_size = '300GB'
>>> effective_io_concurrency = '32'
>>> update_process_title = 'off'
>>> password_encryption = 'md5'
>>> huge_pages = 'on'
>>>
>>>>> Sorry for pause, it was my birthday, so I was on short vacation.
>>>>> So, in total:
>>>>> - increasing NUM_XLOGINSERT_LOCKS to 64 certainly helps
>>>>> - additional lock attempts seems to help a bit in this benchmark,
>>>>>   but it helps more in other (rather synthetic) benchmark [1]
>>>>> - my version of lock-free reservation looks to help a bit when
>>>>>   applied alone, but look strange in conjunction with additional
>>>>>   lock attempts.
>>>>> I don't see small improvement from my version of Lock-Free
>>>>> reservation
>>>>> (1.1% = 1023/1012) pays for its complexity at the moment.
>>>>
>>>> Due to limited hardware resources, I only had the opportunity to
>>>> measure the performance impact of your v1 patch of the lock-free hash
>>>> table with 64 NUM_XLOGINSERT_LOCKS and the two lock attempt patch. I
>>>> observed an improvement of *76.4%* (RSD: 4.1%) when combining them
>>>> together on the SPR with 480 vCPUs. I understand that your test
>>>> devices may not have as many cores, which might be why this
>>>> optimization brings an unnoticeable impact. However, I don't think
>>>> this is an unreal problem. In fact, this issue was raised by our
>>>> customer who is trying to deploy Postgres on devices with hundreds of
>>>> cores, and I believe the resolution of this performance issue would
>>>> result in real impacts.
>>>>
>>>>> Probably, when other places will be optimized/improved, it will pay
>>>>> more.
>>>>> Or probably Zhiguo Zhou's version will perform better.
>>>>>
>>>>
>>>> Our primary difference lies in the approach to handling the prev-link,
>>>> either via the hash table or directly within the XLog buffer. During
>>>> my analysis, I didn't identify significant hotspots in the hash table
>>>> functions, leading me to believe that both implementations should
>>>> achieve comparable performance improvements.
>>>>
>>>> Following your advice, I revised my implementation to update the
>>>> prev-link atomically and resolved the known TAP tests. However, I
>>>> encountered the last failure in the recovery/t/027_stream_regress.pl
>>>> test. Addressing this issue might require a redesign of the underlying
>>>> writing convention of XLog, which I believe is not necessary,
>>>> especially since your implementation already achieves the desired
>>>> performance improvements without suffering from the test failures. I
>>>> think we may need to focus on your implementation in the next phase.
>>>>
>>>>> I think, we could measure theoretical benefit by completely ignoring
>>>>> fill of xl_prev. I've attached patch "Dumb-lock-free..." so you could
>>>>> measure. It passes almost all "recovery" tests, though fails two
>>>>> strictly dependent on xl_prev.
>>>>
>>>
>>>> I currently don't have access to the high-core-count device, but I
>>>> plan to measure the performance impact of your latest patch and the
>>>> "Dump-lock-free..." patch once I regain access.
>>>>> [1] https://postgr.es/m/3b11fdc2-9793-403d-
>>>>> b3d4-67ff9a00d447%40postgrespro.ru
>>>>> ------
>>>>> regards
>>>>> Yura
>>>>
>>>> Hi Yura and Japin,
>>>>
>>>> Thanks so much for your recent patch works and discussions which
>>>> inspired me a lot! I agree with you that we need to:
>>>> - Align the test approach and environment
>>>> - Address the motivation and necessity of this optimization
>>>> - Further identify the optimization opportunities after applying
>>>> Yura's patch
>>>>
>>>> WDYT?
>>>>
>>>> [1]
>>>> https://www.postgresql.org/message-id/6ykez6chr5wfiveuv2iby236mb7ab6fqwpxghppdi5ugb4kdyt%40lkrn4maox2wj
>>>>
>>>> Regards,
>>>> Zhiguo
>>>
>>
>> Hi Japin,
>>
>> Apologies for the delay in responding—I've just returned from
>> vacation. To move things forward, I'll be running the BenchmarkSQL
>> workload on my end shortly.
>>
>> In the meantime, could you run the HammerDB/TPCC workload on your
>> device? We've observed significant performance improvements with this
>> test, and it might help clarify whether the discrepancies we're seeing
>> stem from the workload itself. Thanks!
>>
>
> Sorry, I currently don't have access to the test device, I will try to test
> it if I can regain access.
>

Good day, Yura and Japin!

I recently acquired the SUT device again and had the opportunity to
conduct performance experiments using the TPC-C benchmark (pg_count_ware
757, vu 256) with HammerDB on an Intel CPU with 480 vCPUs. Below are the
results and key observations:

+----------------+-------------+------------+-------------+------------+
| Version | NOPM | NOPM Gain% | TPM | TPM(Gain%) |
+----------------+-------------+------------+-------------+------------+
| master(b4a07f5)| 1,681,233 | 0.0% | 3,874,491 | 0.0% |
| 64-lock | 643,853 | -61.7% | 1,479,647 | -61.8% |
| 64-lock-v4 | 2,423,972 | 44.2% | 5,577,580 | 44.0% |
| 128-lock | 462,993 | -72.5% | 1,064,733 | -72.5% |
| 128-lock-v4 | 2,468,034 | 46.8% | 5,673,349 | 46.4% |
+----------------+-------------+------------+-------------+------------+

- Though the baseline (b4a07f5) has improved compared to when we created
this mailing list, we still achieve 44% improvement with this optimization.
- Increasing NUM_XLOGINSERT_LOCKS solely to 64/128 leads to severe
performance regression due to intensified lock contention.
- Increasing NUM_XLOGINSERT_LOCKS and applying the lock-free xlog
insertion optimization jointly improve overall performance.
- 64 locks seems the sweet spot for achieving the most performance
improvement.

I also executed the same benchmark, TPCC, with BenchmarkSQL (I'm not
sure if the difference of their implementation of TPCC would lead to
some performance gap). I observed that:

- The performance indicator (NOPM) shows differences of several
magnitudes compared to Japin's results.
- NOPM/TPM seems insensitive to code changes (lock count increase,
lock-free algorithm), which is quite strange.
- Possible reasons may include: 1) scaling parameters [1] are not
aligned, 2) test configuration did not reach the pain point of the XLog
insertions.

And I noticed a 64-core device (32 cores for the server) was used in
Japin's test. In our previous core-scaling test (attached), 32/64 cores
may not be enough to show the impact of the optimization, I think that
would be one of the reason why Japin observed minimal impact from the
lock-free optimization.

In summary, I think:
- The TPC-C benchmark (pg_count_ware 757, vu 256) with HammerDB
effectively reflects performance in XLog insertions.
- This test on a device with hundreds of cores reflects a real user
scenario, making it a significant consideration.
- The lock-free algorithm with the lock count increased to 64 can bring
significant performance improvements.

So I propose to continue the code review process for this optimization
patch. WDYT?

Regards,
Zhiguo

[1]
https://github.com/pgsql-io/benchmarksql/blob/master/docs/PROPERTIES.md#scaling-parameters

14.02.2025 11:41, Zhou, Zhiguo пишет:
>
>
> On 2/11/2025 9:25 AM, Japin Li wrote:
>> On Mon, 10 Feb 2025 at 22:12, "Zhou, Zhiguo" <zhiguo(dot)zhou(at)intel(dot)com> wrote:
>>> On 2/5/2025 4:32 PM, Japin Li wrote:
>>>> On Mon, 27 Jan 2025 at 17:30, "Zhou, Zhiguo" <zhiguo(dot)zhou(at)intel(dot)com> wrote:
>>>>> On 1/26/2025 10:59 PM, Yura Sokolov wrote:
>>>>>> 24.01.2025 12:07, Japin Li пишет:
>>>>>>> On Thu, 23 Jan 2025 at 21:44, Japin Li <japinli(at)hotmail(dot)com> wrote:
>>>>>>>> On Thu, 23 Jan 2025 at 15:03, Yura Sokolov
>>>>>>>> <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>>>>>>>>> 23.01.2025 11:46, Japin Li пишет:
>>>>>>>>>> On Wed, 22 Jan 2025 at 22:44, Japin Li <japinli(at)hotmail(dot)com> wrote:
>>>>>>>>>>> On Wed, 22 Jan 2025 at 17:02, Yura Sokolov
>>>>>>>>>>> <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>>>>>>>>>>>> I believe, I know why it happens: I was in hurry making v2 by
>>>>>>>>>>>> cherry-picking internal version. I reverted some changes in
>>>>>>>>>>>> CalcCuckooPositions manually and forgot to add modulo
>>>>>>>>>>>> PREV_LINKS_HASH_CAPA.
>>>>>>>>>>>>
>>>>>>>>>>>> Here's the fix:
>>>>>>>>>>>>
>>>>>>>>>>>>           pos->pos[0] = hash % PREV_LINKS_HASH_CAPA;
>>>>>>>>>>>> -       pos->pos[1] = pos->pos[0] + 1;
>>>>>>>>>>>> +       pos->pos[1] = (pos->pos[0] + 1) % PREV_LINKS_HASH_CAPA;
>>>>>>>>>>>>           pos->pos[2] = (hash >> 16) % PREV_LINKS_HASH_CAPA;
>>>>>>>>>>>> -       pos->pos[3] = pos->pos[2] + 2;
>>>>>>>>>>>> +       pos->pos[3] = (pos->pos[2] + 2) % PREV_LINKS_HASH_CAPA;
>>>>>>>>>>>>
>>>>>>>>>>>> Any way, here's v3:
>>>>>>>>>>>> - excess file "v0-0001-Increase..." removed. I believe it was source
>>>>>>>>>>>>     of white-space apply warnings.
>>>>>>>>>>>> - this mistake fixed
>>>>>>>>>>>> - more clear slots strategies + "8 positions in two
>>>>>>>>>>>> cache-lines" strategy.
>>>>>>>>>>>>
>>>>>>>>>>>> You may play with switching PREV_LINKS_HASH_STRATEGY to 2 or 3
>>>>>>>>>>>> and see
>>>>>>>>>>>> if it affects measurably.
>>>>>>>>>>>
>>>>>>>>>>> Thanks for your quick fixing.  I will retest it tomorrow.
>>>>>>>>>> Hi, Yura Sokolov
>>>>>>>>>> Here is my test result of the v3 patch:
>>>>>>>>>> | case                          | min        | avg        | max
>>>>>>>>>> |
>>>>>>>>>> |-------------------------------+------------+------------
>>>>>>>>>> +------------|
>>>>>>>>>> | master (44b61efb79)           | 865,743.55 | 871,237.40 |
>>>>>>>>>> 874,492.59 |
>>>>>>>>>> | v3                            | 857,020.58 | 860,180.11 |
>>>>>>>>>> 864,355.00 |
>>>>>>>>>> | v3 PREV_LINKS_HASH_STRATEGY=2 | 853,187.41 | 855,796.36 |
>>>>>>>>>> 858,436.44 |
>>>>>>>>>> | v3 PREV_LINKS_HASH_STRATEGY=3 | 863,131.97 | 864,272.91 |
>>>>>>>>>> 865,396.42 |
>>>>>>>>>> It seems there are some performance decreases :( or something I
>>>>>>>>>> missed?
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Hi, Japin.
>>>>>>>>> (Excuse me for duplicating message, I found I sent it only to you
>>>>>>>>> first time).
>>>>>>>>>
>>>>>>>> Never mind!
>>>>>>>>
>>>>>>>>> v3 (as well as v2) doesn't increase NUM_XLOGINSERT_LOCKS itself.
>>>>>>>>> With only 8 in-progress inserters spin-lock is certainly better
>>>>>>>>> than any
>>>>>>>>> more complex solution.
>>>>>>>>>
>>>>>>>>> You need to compare "master" vs "master + NUM_XLOGINSERT_LOCKS=64" vs
>>>>>>>>> "master + NUM_XLOGINSERT_LOCKS=64 + v3".
>>>>>>>>>
>>>>>>>>> And even this way I don't claim "Lock-free reservation" gives any
>>>>>>>>> profit.
>>>>>>>>>
>>>>>>>>> That is why your benchmarking is very valuable! It could answer, does
>>>>>>>>> we need such not-small patch, or there is no real problem at all?
>>>>>>>>>
>>>>>>>
>>>>>>> Hi, Yura Sokolov
>>>>>>>
>>>>>>> Here is the test result compared with NUM_XLOGINSERT_LOCKS and the
>>>>>>> v3 patch.
>>>>>>>
>>>>>>> | case                  | min          | avg          |
>>>>>>> max          | rate% |
>>>>>>> |-----------------------+--------------+--------------+--------------
>>>>>>> +-------|
>>>>>>> | master (4108440)      | 891,225.77   | 904,868.75   |
>>>>>>> 913,708.17   |        |
>>>>>>> | lock 64               | 1,007,716.95 | 1,012,013.22 |
>>>>>>> 1,018,674.00 | 11.84 |
>>>>>>> | lock 64 attempt 1     | 1,016,716.07 | 1,017,735.55 |
>>>>>>> 1,019,328.36 | 12.47 |
>>>>>>> | lock 64 attempt 2     | 1,015,328.31 | 1,018,147.74 |
>>>>>>> 1,021,513.14 | 12.52 |
>>>>>>> | lock 128              | 1,010,147.38 | 1,014,128.11 |
>>>>>>> 1,018,672.01 | 12.07 |
>>>>>>> | lock 128 attempt 1    | 1,018,154.79 | 1,023,348.35 |
>>>>>>> 1,031,365.42 | 13.09 |
>>>>>>> | lock 128 attempt 2    | 1,013,245.56 | 1,018,984.78 |
>>>>>>> 1,023,696.00 | 12.61 |
>>>>>>> | lock 64 v3            | 1,010,893.30 | 1,022,787.25 |
>>>>>>> 1,029,200.26 | 13.03 |
>>>>>>> | lock 64 attempt 1 v3  | 1,014,961.21 | 1,019,745.09 |
>>>>>>> 1,025,511.62 | 12.70 |
>>>>>>> | lock 64 attempt 2 v3  | 1,015,690.73 | 1,018,365.46 |
>>>>>>> 1,020,200.57 | 12.54 |
>>>>>>> | lock 128 v3           | 1,012,653.14 | 1,013,637.09 |
>>>>>>> 1,014,358.69 | 12.02 |
>>>>>>> | lock 128 attempt 1 v3 | 1,008,027.57 | 1,016,849.87 |
>>>>>>> 1,024,597.15 | 12.38 |
>>>>>>> | lock 128 attempt 2 v3 | 1,020,552.04 | 1,024,658.92 |
>>>>>>> 1,027,855.90 | 13.24 |
>>>>>
>>>>> The data looks really interesting and I recognize the need for further
>>>>> investigation. I'm not very familiar with BenchmarkSQL but we've done
>>>>> similar tests with HammerDB/TPCC by solely increasing
>>>>> NUM_XLOGINSERT_LOCKS from 8 to 128, and we observed a significant
>>>>> performance drop of ~50% and the cycle ratio of spinlock acquisition
>>>>> (s_lock) rose to over 60% of the total, which is basically consistent
>>>>> with the previous findings in [1].
>>>>>
>>>>> Could you please share the details of your test environment, including
>>>>> the device, configuration, and test approach, so we can collaborate on
>>>>> understanding the differences?
>>>> Sorry for the late reply. I'm on my vacation.
>>>> I use Hygon C86 7490 64-core, it has 8 NUMA nodes with 1.5T memory,
>>>> and
>>>> I use 0-3 run the database, and 4-7 run the BenchmarkSQL.
>>>> Here is my database settings:
>>>> listen_addresses = '*'
>>>> max_connections = '1050'
>>>> shared_buffers = '100GB'
>>>> work_mem = '64MB'
>>>> maintenance_work_mem = '512MB'
>>>> max_wal_size = '50GB'
>>>> min_wal_size = '10GB'
>>>> random_page_cost = '1.1'
>>>> wal_buffers = '1GB'
>>>> wal_level = 'minimal'
>>>> max_wal_senders = '0'
>>>> wal_sync_method = 'open_datasync'
>>>> wal_compression = 'lz4'
>>>> track_activities = 'off'
>>>> checkpoint_timeout = '1d'
>>>> checkpoint_completion_target = '0.95'
>>>> effective_cache_size = '300GB'
>>>> effective_io_concurrency = '32'
>>>> update_process_title = 'off'
>>>> password_encryption = 'md5'
>>>> huge_pages = 'on'
>>>>
>>>>>> Sorry for pause, it was my birthday, so I was on short vacation.
>>>>>> So, in total:
>>>>>> - increasing NUM_XLOGINSERT_LOCKS to 64 certainly helps
>>>>>> - additional lock attempts seems to help a bit in this benchmark,
>>>>>>   but it helps more in other (rather synthetic) benchmark [1]
>>>>>> - my version of lock-free reservation looks to help a bit when
>>>>>>   applied alone, but look strange in conjunction with additional
>>>>>>   lock attempts.
>>>>>> I don't see small improvement from my version of Lock-Free
>>>>>> reservation
>>>>>> (1.1% = 1023/1012) pays for its complexity at the moment.
>>>>>
>>>>> Due to limited hardware resources, I only had the opportunity to
>>>>> measure the performance impact of your v1 patch of the lock-free hash
>>>>> table with 64 NUM_XLOGINSERT_LOCKS and the two lock attempt patch. I
>>>>> observed an improvement of *76.4%* (RSD: 4.1%) when combining them
>>>>> together on the SPR with 480 vCPUs. I understand that your test
>>>>> devices may not have as many cores, which might be why this
>>>>> optimization brings an unnoticeable impact. However, I don't think
>>>>> this is an unreal problem. In fact, this issue was raised by our
>>>>> customer who is trying to deploy Postgres on devices with hundreds of
>>>>> cores, and I believe the resolution of this performance issue would
>>>>> result in real impacts.
>>>>>
>>>>>> Probably, when other places will be optimized/improved, it will pay
>>>>>> more.
>>>>>> Or probably Zhiguo Zhou's version will perform better.
>>>>>>
>>>>>
>>>>> Our primary difference lies in the approach to handling the prev-link,
>>>>> either via the hash table or directly within the XLog buffer. During
>>>>> my analysis, I didn't identify significant hotspots in the hash table
>>>>> functions, leading me to believe that both implementations should
>>>>> achieve comparable performance improvements.
>>>>>
>>>>> Following your advice, I revised my implementation to update the
>>>>> prev-link atomically and resolved the known TAP tests. However, I
>>>>> encountered the last failure in the recovery/t/027_stream_regress.pl
>>>>> test. Addressing this issue might require a redesign of the underlying
>>>>> writing convention of XLog, which I believe is not necessary,
>>>>> especially since your implementation already achieves the desired
>>>>> performance improvements without suffering from the test failures. I
>>>>> think we may need to focus on your implementation in the next phase.
>>>>>
>>>>>> I think, we could measure theoretical benefit by completely ignoring
>>>>>> fill of xl_prev. I've attached patch "Dumb-lock-free..." so you could
>>>>>> measure. It passes almost all "recovery" tests, though fails two
>>>>>> strictly dependent on xl_prev.
>>>>>
>>>>
>>>>> I currently don't have access to the high-core-count device, but I
>>>>> plan to measure the performance impact of your latest patch and the
>>>>> "Dump-lock-free..." patch once I regain access.
>>>>>> [1] https://postgr.es/m/3b11fdc2-9793-403d-
>>>>>> b3d4-67ff9a00d447%40postgrespro.ru
>>>>>> ------
>>>>>> regards
>>>>>> Yura
>>>>>
>>>>> Hi Yura and Japin,
>>>>>
>>>>> Thanks so much for your recent patch works and discussions which
>>>>> inspired me a lot! I agree with you that we need to:
>>>>> - Align the test approach and environment
>>>>> - Address the motivation and necessity of this optimization
>>>>> - Further identify the optimization opportunities after applying
>>>>> Yura's patch
>>>>>
>>>>> WDYT?
>>>>>
>>>>> [1]
>>>>> https://www.postgresql.org/message-id/6ykez6chr5wfiveuv2iby236mb7ab6fqwpxghppdi5ugb4kdyt%40lkrn4maox2wj
>>>>>
>>>>> Regards,
>>>>> Zhiguo
>>>>
>>>
>>> Hi Japin,
>>>
>>> Apologies for the delay in responding—I've just returned from
>>> vacation. To move things forward, I'll be running the BenchmarkSQL
>>> workload on my end shortly.
>>>
>>> In the meantime, could you run the HammerDB/TPCC workload on your
>>> device? We've observed significant performance improvements with this
>>> test, and it might help clarify whether the discrepancies we're seeing
>>> stem from the workload itself. Thanks!
>>>
>>
>> Sorry, I currently don't have access to the test device, I will try to test
>> it if I can regain access.
>>
>
> Good day, Yura and Japin!
>
> I recently acquired the SUT device again and had the opportunity to
> conduct performance experiments using the TPC-C benchmark (pg_count_ware
> 757, vu 256) with HammerDB on an Intel CPU with 480 vCPUs. Below are the
> results and key observations:
>
> +----------------+-------------+------------+-------------+------------+
> | Version | NOPM | NOPM Gain% | TPM | TPM(Gain%) |
> +----------------+-------------+------------+-------------+------------+
> | master(b4a07f5)| 1,681,233 | 0.0% | 3,874,491 | 0.0% |
> | 64-lock | 643,853 | -61.7% | 1,479,647 | -61.8% |
> | 64-lock-v4 | 2,423,972 | 44.2% | 5,577,580 | 44.0% |
> | 128-lock | 462,993 | -72.5% | 1,064,733 | -72.5% |
> | 128-lock-v4 | 2,468,034 | 46.8% | 5,673,349 | 46.4% |
> +----------------+-------------+------------+-------------+------------+
>
> - Though the baseline (b4a07f5) has improved compared to when we created
> this mailing list, we still achieve 44% improvement with this optimization.
> - Increasing NUM_XLOGINSERT_LOCKS solely to 64/128 leads to severe
> performance regression due to intensified lock contention.
> - Increasing NUM_XLOGINSERT_LOCKS and applying the lock-free xlog
> insertion optimization jointly improve overall performance.
> - 64 locks seems the sweet spot for achieving the most performance
> improvement.
>
> I also executed the same benchmark, TPCC, with BenchmarkSQL (I'm not
> sure if the difference of their implementation of TPCC would lead to
> some performance gap). I observed that:
>
> - The performance indicator (NOPM) shows differences of several
> magnitudes compared to Japin's results.
> - NOPM/TPM seems insensitive to code changes (lock count increase,
> lock-free algorithm), which is quite strange.
> - Possible reasons may include: 1) scaling parameters [1] are not
> aligned, 2) test configuration did not reach the pain point of the XLog
> insertions.
>
> And I noticed a 64-core device (32 cores for the server) was used in
> Japin's test. In our previous core-scaling test (attached), 32/64 cores
> may not be enough to show the impact of the optimization, I think that
> would be one of the reason why Japin observed minimal impact from the
> lock-free optimization.
>
> In summary, I think:
> - The TPC-C benchmark (pg_count_ware 757, vu 256) with HammerDB
> effectively reflects performance in XLog insertions.
> - This test on a device with hundreds of cores reflects a real user
> scenario, making it a significant consideration.
> - The lock-free algorithm with the lock count increased to 64 can bring
> significant performance improvements.
>
> So I propose to continue the code review process for this optimization
> patch. WDYT?
>
> [1]
> https://github.com/pgsql-io/benchmarksql/blob/master/docs/PROPERTIES.md#scaling-parameters

Good day.

I'll just repeat my answer from personal mail:

I'm impressed with results. I really didn't expect it is so important for
huge servers.

Main problem will be to prove this patch doesn't harm performance on
smaller servers. Or made things configurable so that smaller server still
uses simpler code.

-------
regards
Yura Sokolov aka funny-falcon

On 2/23/2025 8:03 PM, Yura Sokolov wrote:
> 14.02.2025 11:41, Zhou, Zhiguo пишет:
>>
>>
>> On 2/11/2025 9:25 AM, Japin Li wrote:
>>> On Mon, 10 Feb 2025 at 22:12, "Zhou, Zhiguo" <zhiguo(dot)zhou(at)intel(dot)com> wrote:
>>>> On 2/5/2025 4:32 PM, Japin Li wrote:
>>>>> On Mon, 27 Jan 2025 at 17:30, "Zhou, Zhiguo" <zhiguo(dot)zhou(at)intel(dot)com> wrote:
>>>>>> On 1/26/2025 10:59 PM, Yura Sokolov wrote:
>>>>>>> 24.01.2025 12:07, Japin Li пишет:
>>>>>>>> On Thu, 23 Jan 2025 at 21:44, Japin Li <japinli(at)hotmail(dot)com> wrote:
>>>>>>>>> On Thu, 23 Jan 2025 at 15:03, Yura Sokolov
>>>>>>>>> <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>>>>>>>>>> 23.01.2025 11:46, Japin Li пишет:
>>>>>>>>>>> On Wed, 22 Jan 2025 at 22:44, Japin Li <japinli(at)hotmail(dot)com> wrote:
>>>>>>>>>>>> On Wed, 22 Jan 2025 at 17:02, Yura Sokolov
>>>>>>>>>>>> <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>>>>>>>>>>>>> I believe, I know why it happens: I was in hurry making v2 by
>>>>>>>>>>>>> cherry-picking internal version. I reverted some changes in
>>>>>>>>>>>>> CalcCuckooPositions manually and forgot to add modulo
>>>>>>>>>>>>> PREV_LINKS_HASH_CAPA.
>>>>>>>>>>>>>
>>>>>>>>>>>>> Here's the fix:
>>>>>>>>>>>>>
>>>>>>>>>>>>>           pos->pos[0] = hash % PREV_LINKS_HASH_CAPA;
>>>>>>>>>>>>> -       pos->pos[1] = pos->pos[0] + 1;
>>>>>>>>>>>>> +       pos->pos[1] = (pos->pos[0] + 1) % PREV_LINKS_HASH_CAPA;
>>>>>>>>>>>>>           pos->pos[2] = (hash >> 16) % PREV_LINKS_HASH_CAPA;
>>>>>>>>>>>>> -       pos->pos[3] = pos->pos[2] + 2;
>>>>>>>>>>>>> +       pos->pos[3] = (pos->pos[2] + 2) % PREV_LINKS_HASH_CAPA;
>>>>>>>>>>>>>
>>>>>>>>>>>>> Any way, here's v3:
>>>>>>>>>>>>> - excess file "v0-0001-Increase..." removed. I believe it was source
>>>>>>>>>>>>>     of white-space apply warnings.
>>>>>>>>>>>>> - this mistake fixed
>>>>>>>>>>>>> - more clear slots strategies + "8 positions in two
>>>>>>>>>>>>> cache-lines" strategy.
>>>>>>>>>>>>>
>>>>>>>>>>>>> You may play with switching PREV_LINKS_HASH_STRATEGY to 2 or 3
>>>>>>>>>>>>> and see
>>>>>>>>>>>>> if it affects measurably.
>>>>>>>>>>>>
>>>>>>>>>>>> Thanks for your quick fixing.  I will retest it tomorrow.
>>>>>>>>>>> Hi, Yura Sokolov
>>>>>>>>>>> Here is my test result of the v3 patch:
>>>>>>>>>>> | case                          | min        | avg        | max
>>>>>>>>>>> |
>>>>>>>>>>> |-------------------------------+------------+------------
>>>>>>>>>>> +------------|
>>>>>>>>>>> | master (44b61efb79)           | 865,743.55 | 871,237.40 |
>>>>>>>>>>> 874,492.59 |
>>>>>>>>>>> | v3                            | 857,020.58 | 860,180.11 |
>>>>>>>>>>> 864,355.00 |
>>>>>>>>>>> | v3 PREV_LINKS_HASH_STRATEGY=2 | 853,187.41 | 855,796.36 |
>>>>>>>>>>> 858,436.44 |
>>>>>>>>>>> | v3 PREV_LINKS_HASH_STRATEGY=3 | 863,131.97 | 864,272.91 |
>>>>>>>>>>> 865,396.42 |
>>>>>>>>>>> It seems there are some performance decreases :( or something I
>>>>>>>>>>> missed?
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> Hi, Japin.
>>>>>>>>>> (Excuse me for duplicating message, I found I sent it only to you
>>>>>>>>>> first time).
>>>>>>>>>>
>>>>>>>>> Never mind!
>>>>>>>>>
>>>>>>>>>> v3 (as well as v2) doesn't increase NUM_XLOGINSERT_LOCKS itself.
>>>>>>>>>> With only 8 in-progress inserters spin-lock is certainly better
>>>>>>>>>> than any
>>>>>>>>>> more complex solution.
>>>>>>>>>>
>>>>>>>>>> You need to compare "master" vs "master + NUM_XLOGINSERT_LOCKS=64" vs
>>>>>>>>>> "master + NUM_XLOGINSERT_LOCKS=64 + v3".
>>>>>>>>>>
>>>>>>>>>> And even this way I don't claim "Lock-free reservation" gives any
>>>>>>>>>> profit.
>>>>>>>>>>
>>>>>>>>>> That is why your benchmarking is very valuable! It could answer, does
>>>>>>>>>> we need such not-small patch, or there is no real problem at all?
>>>>>>>>>>
>>>>>>>>
>>>>>>>> Hi, Yura Sokolov
>>>>>>>>
>>>>>>>> Here is the test result compared with NUM_XLOGINSERT_LOCKS and the
>>>>>>>> v3 patch.
>>>>>>>>
>>>>>>>> | case                  | min          | avg          |
>>>>>>>> max          | rate% |
>>>>>>>> |-----------------------+--------------+--------------+--------------
>>>>>>>> +-------|
>>>>>>>> | master (4108440)      | 891,225.77   | 904,868.75   |
>>>>>>>> 913,708.17   |        |
>>>>>>>> | lock 64               | 1,007,716.95 | 1,012,013.22 |
>>>>>>>> 1,018,674.00 | 11.84 |
>>>>>>>> | lock 64 attempt 1     | 1,016,716.07 | 1,017,735.55 |
>>>>>>>> 1,019,328.36 | 12.47 |
>>>>>>>> | lock 64 attempt 2     | 1,015,328.31 | 1,018,147.74 |
>>>>>>>> 1,021,513.14 | 12.52 |
>>>>>>>> | lock 128              | 1,010,147.38 | 1,014,128.11 |
>>>>>>>> 1,018,672.01 | 12.07 |
>>>>>>>> | lock 128 attempt 1    | 1,018,154.79 | 1,023,348.35 |
>>>>>>>> 1,031,365.42 | 13.09 |
>>>>>>>> | lock 128 attempt 2    | 1,013,245.56 | 1,018,984.78 |
>>>>>>>> 1,023,696.00 | 12.61 |
>>>>>>>> | lock 64 v3            | 1,010,893.30 | 1,022,787.25 |
>>>>>>>> 1,029,200.26 | 13.03 |
>>>>>>>> | lock 64 attempt 1 v3  | 1,014,961.21 | 1,019,745.09 |
>>>>>>>> 1,025,511.62 | 12.70 |
>>>>>>>> | lock 64 attempt 2 v3  | 1,015,690.73 | 1,018,365.46 |
>>>>>>>> 1,020,200.57 | 12.54 |
>>>>>>>> | lock 128 v3           | 1,012,653.14 | 1,013,637.09 |
>>>>>>>> 1,014,358.69 | 12.02 |
>>>>>>>> | lock 128 attempt 1 v3 | 1,008,027.57 | 1,016,849.87 |
>>>>>>>> 1,024,597.15 | 12.38 |
>>>>>>>> | lock 128 attempt 2 v3 | 1,020,552.04 | 1,024,658.92 |
>>>>>>>> 1,027,855.90 | 13.24 |
>>>>>>
>>>>>> The data looks really interesting and I recognize the need for further
>>>>>> investigation. I'm not very familiar with BenchmarkSQL but we've done
>>>>>> similar tests with HammerDB/TPCC by solely increasing
>>>>>> NUM_XLOGINSERT_LOCKS from 8 to 128, and we observed a significant
>>>>>> performance drop of ~50% and the cycle ratio of spinlock acquisition
>>>>>> (s_lock) rose to over 60% of the total, which is basically consistent
>>>>>> with the previous findings in [1].
>>>>>>
>>>>>> Could you please share the details of your test environment, including
>>>>>> the device, configuration, and test approach, so we can collaborate on
>>>>>> understanding the differences?
>>>>> Sorry for the late reply. I'm on my vacation.
>>>>> I use Hygon C86 7490 64-core, it has 8 NUMA nodes with 1.5T memory,
>>>>> and
>>>>> I use 0-3 run the database, and 4-7 run the BenchmarkSQL.
>>>>> Here is my database settings:
>>>>> listen_addresses = '*'
>>>>> max_connections = '1050'
>>>>> shared_buffers = '100GB'
>>>>> work_mem = '64MB'
>>>>> maintenance_work_mem = '512MB'
>>>>> max_wal_size = '50GB'
>>>>> min_wal_size = '10GB'
>>>>> random_page_cost = '1.1'
>>>>> wal_buffers = '1GB'
>>>>> wal_level = 'minimal'
>>>>> max_wal_senders = '0'
>>>>> wal_sync_method = 'open_datasync'
>>>>> wal_compression = 'lz4'
>>>>> track_activities = 'off'
>>>>> checkpoint_timeout = '1d'
>>>>> checkpoint_completion_target = '0.95'
>>>>> effective_cache_size = '300GB'
>>>>> effective_io_concurrency = '32'
>>>>> update_process_title = 'off'
>>>>> password_encryption = 'md5'
>>>>> huge_pages = 'on'
>>>>>
>>>>>>> Sorry for pause, it was my birthday, so I was on short vacation.
>>>>>>> So, in total:
>>>>>>> - increasing NUM_XLOGINSERT_LOCKS to 64 certainly helps
>>>>>>> - additional lock attempts seems to help a bit in this benchmark,
>>>>>>>   but it helps more in other (rather synthetic) benchmark [1]
>>>>>>> - my version of lock-free reservation looks to help a bit when
>>>>>>>   applied alone, but look strange in conjunction with additional
>>>>>>>   lock attempts.
>>>>>>> I don't see small improvement from my version of Lock-Free
>>>>>>> reservation
>>>>>>> (1.1% = 1023/1012) pays for its complexity at the moment.
>>>>>>
>>>>>> Due to limited hardware resources, I only had the opportunity to
>>>>>> measure the performance impact of your v1 patch of the lock-free hash
>>>>>> table with 64 NUM_XLOGINSERT_LOCKS and the two lock attempt patch. I
>>>>>> observed an improvement of *76.4%* (RSD: 4.1%) when combining them
>>>>>> together on the SPR with 480 vCPUs. I understand that your test
>>>>>> devices may not have as many cores, which might be why this
>>>>>> optimization brings an unnoticeable impact. However, I don't think
>>>>>> this is an unreal problem. In fact, this issue was raised by our
>>>>>> customer who is trying to deploy Postgres on devices with hundreds of
>>>>>> cores, and I believe the resolution of this performance issue would
>>>>>> result in real impacts.
>>>>>>
>>>>>>> Probably, when other places will be optimized/improved, it will pay
>>>>>>> more.
>>>>>>> Or probably Zhiguo Zhou's version will perform better.
>>>>>>>
>>>>>>
>>>>>> Our primary difference lies in the approach to handling the prev-link,
>>>>>> either via the hash table or directly within the XLog buffer. During
>>>>>> my analysis, I didn't identify significant hotspots in the hash table
>>>>>> functions, leading me to believe that both implementations should
>>>>>> achieve comparable performance improvements.
>>>>>>
>>>>>> Following your advice, I revised my implementation to update the
>>>>>> prev-link atomically and resolved the known TAP tests. However, I
>>>>>> encountered the last failure in the recovery/t/027_stream_regress.pl
>>>>>> test. Addressing this issue might require a redesign of the underlying
>>>>>> writing convention of XLog, which I believe is not necessary,
>>>>>> especially since your implementation already achieves the desired
>>>>>> performance improvements without suffering from the test failures. I
>>>>>> think we may need to focus on your implementation in the next phase.
>>>>>>
>>>>>>> I think, we could measure theoretical benefit by completely ignoring
>>>>>>> fill of xl_prev. I've attached patch "Dumb-lock-free..." so you could
>>>>>>> measure. It passes almost all "recovery" tests, though fails two
>>>>>>> strictly dependent on xl_prev.
>>>>>>
>>>>>
>>>>>> I currently don't have access to the high-core-count device, but I
>>>>>> plan to measure the performance impact of your latest patch and the
>>>>>> "Dump-lock-free..." patch once I regain access.
>>>>>>> [1] https://postgr.es/m/3b11fdc2-9793-403d-
>>>>>>> b3d4-67ff9a00d447%40postgrespro.ru
>>>>>>> ------
>>>>>>> regards
>>>>>>> Yura
>>>>>>
>>>>>> Hi Yura and Japin,
>>>>>>
>>>>>> Thanks so much for your recent patch works and discussions which
>>>>>> inspired me a lot! I agree with you that we need to:
>>>>>> - Align the test approach and environment
>>>>>> - Address the motivation and necessity of this optimization
>>>>>> - Further identify the optimization opportunities after applying
>>>>>> Yura's patch
>>>>>>
>>>>>> WDYT?
>>>>>>
>>>>>> [1]
>>>>>> https://www.postgresql.org/message-id/6ykez6chr5wfiveuv2iby236mb7ab6fqwpxghppdi5ugb4kdyt%40lkrn4maox2wj
>>>>>>
>>>>>> Regards,
>>>>>> Zhiguo
>>>>>
>>>>
>>>> Hi Japin,
>>>>
>>>> Apologies for the delay in responding—I've just returned from
>>>> vacation. To move things forward, I'll be running the BenchmarkSQL
>>>> workload on my end shortly.
>>>>
>>>> In the meantime, could you run the HammerDB/TPCC workload on your
>>>> device? We've observed significant performance improvements with this
>>>> test, and it might help clarify whether the discrepancies we're seeing
>>>> stem from the workload itself. Thanks!
>>>>
>>>
>>> Sorry, I currently don't have access to the test device, I will try to test
>>> it if I can regain access.
>>>
>>
>> Good day, Yura and Japin!
>>
>> I recently acquired the SUT device again and had the opportunity to
>> conduct performance experiments using the TPC-C benchmark (pg_count_ware
>> 757, vu 256) with HammerDB on an Intel CPU with 480 vCPUs. Below are the
>> results and key observations:
>>
>> +----------------+-------------+------------+-------------+------------+
>> | Version | NOPM | NOPM Gain% | TPM | TPM(Gain%) |
>> +----------------+-------------+------------+-------------+------------+
>> | master(b4a07f5)| 1,681,233 | 0.0% | 3,874,491 | 0.0% |
>> | 64-lock | 643,853 | -61.7% | 1,479,647 | -61.8% |
>> | 64-lock-v4 | 2,423,972 | 44.2% | 5,577,580 | 44.0% |
>> | 128-lock | 462,993 | -72.5% | 1,064,733 | -72.5% |
>> | 128-lock-v4 | 2,468,034 | 46.8% | 5,673,349 | 46.4% |
>> +----------------+-------------+------------+-------------+------------+
>>
>> - Though the baseline (b4a07f5) has improved compared to when we created
>> this mailing list, we still achieve 44% improvement with this optimization.
>> - Increasing NUM_XLOGINSERT_LOCKS solely to 64/128 leads to severe
>> performance regression due to intensified lock contention.
>> - Increasing NUM_XLOGINSERT_LOCKS and applying the lock-free xlog
>> insertion optimization jointly improve overall performance.
>> - 64 locks seems the sweet spot for achieving the most performance
>> improvement.
>>
>> I also executed the same benchmark, TPCC, with BenchmarkSQL (I'm not
>> sure if the difference of their implementation of TPCC would lead to
>> some performance gap). I observed that:
>>
>> - The performance indicator (NOPM) shows differences of several
>> magnitudes compared to Japin's results.
>> - NOPM/TPM seems insensitive to code changes (lock count increase,
>> lock-free algorithm), which is quite strange.
>> - Possible reasons may include: 1) scaling parameters [1] are not
>> aligned, 2) test configuration did not reach the pain point of the XLog
>> insertions.
>>
>> And I noticed a 64-core device (32 cores for the server) was used in
>> Japin's test. In our previous core-scaling test (attached), 32/64 cores
>> may not be enough to show the impact of the optimization, I think that
>> would be one of the reason why Japin observed minimal impact from the
>> lock-free optimization.
>>
>> In summary, I think:
>> - The TPC-C benchmark (pg_count_ware 757, vu 256) with HammerDB
>> effectively reflects performance in XLog insertions.
>> - This test on a device with hundreds of cores reflects a real user
>> scenario, making it a significant consideration.
>> - The lock-free algorithm with the lock count increased to 64 can bring
>> significant performance improvements.
>>
>> So I propose to continue the code review process for this optimization
>> patch. WDYT?
>>
>> [1]
>> https://github.com/pgsql-io/benchmarksql/blob/master/docs/PROPERTIES.md#scaling-parameters
>
> Good day.
>
> I'll just repeat my answer from personal mail:
>
> I'm impressed with results. I really didn't expect it is so important for
> huge servers.
>
> Main problem will be to prove this patch doesn't harm performance on
> smaller servers. Or made things configurable so that smaller server still
> uses simpler code.
>
> -------
> regards
> Yura Sokolov aka funny-falcon

Good day, Yura!

Firstly, I'd apologize for the delayed response due to internal
urgencies and the time required to set up the tests on a new device.

Regarding your concerns about the potential negative impact on
performance, I have conducted further evaluations. To assess the
performance impact of the patch on a smaller device, I located another
device with significantly fewer processors. Using the same database and
test configurations (TPC-C benchmark: pg_count_ware 757, vu 256) and
code bases (b4a07f5 as "base" and v4 patch with 64 locks as "opt"), I
performed core scaling tests ranging from 8 to 64 physical cores in
steps of 8. The results (attached) indicate that the optimization does
not lead to performance regression within this low core count range.

Please kindly let me know if more data is required to move the process
forward.

I look forward to your insights.

Regards,
Zhiguo

05.03.2025 08:39, Zhou, Zhiguo пишет:
>
>
> On 2/23/2025 8:03 PM, Yura Sokolov wrote:
>> 14.02.2025 11:41, Zhou, Zhiguo пишет:
>>>
>>>
>>> On 2/11/2025 9:25 AM, Japin Li wrote:
>>>> On Mon, 10 Feb 2025 at 22:12, "Zhou, Zhiguo" <zhiguo(dot)zhou(at)intel(dot)com> wrote:
>>>>> On 2/5/2025 4:32 PM, Japin Li wrote:
>>>>>> On Mon, 27 Jan 2025 at 17:30, "Zhou, Zhiguo" <zhiguo(dot)zhou(at)intel(dot)com> wrote:
>>>>>>> On 1/26/2025 10:59 PM, Yura Sokolov wrote:
>>>>>>>> 24.01.2025 12:07, Japin Li пишет:
>>>>>>>>> On Thu, 23 Jan 2025 at 21:44, Japin Li <japinli(at)hotmail(dot)com> wrote:
>>>>>>>>>> On Thu, 23 Jan 2025 at 15:03, Yura Sokolov
>>>>>>>>>> <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>>>>>>>>>>> 23.01.2025 11:46, Japin Li пишет:
>>>>>>>>>>>> On Wed, 22 Jan 2025 at 22:44, Japin Li <japinli(at)hotmail(dot)com> wrote:
>>>>>>>>>>>>> On Wed, 22 Jan 2025 at 17:02, Yura Sokolov
>>>>>>>>>>>>> <y(dot)sokolov(at)postgrespro(dot)ru> wrote:
>>>>>>>>>>>>>> I believe, I know why it happens: I was in hurry making v2 by
>>>>>>>>>>>>>> cherry-picking internal version. I reverted some changes in
>>>>>>>>>>>>>> CalcCuckooPositions manually and forgot to add modulo
>>>>>>>>>>>>>> PREV_LINKS_HASH_CAPA.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Here's the fix:
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>           pos->pos[0] = hash % PREV_LINKS_HASH_CAPA;
>>>>>>>>>>>>>> -       pos->pos[1] = pos->pos[0] + 1;
>>>>>>>>>>>>>> +       pos->pos[1] = (pos->pos[0] + 1) % PREV_LINKS_HASH_CAPA;
>>>>>>>>>>>>>>           pos->pos[2] = (hash >> 16) % PREV_LINKS_HASH_CAPA;
>>>>>>>>>>>>>> -       pos->pos[3] = pos->pos[2] + 2;
>>>>>>>>>>>>>> +       pos->pos[3] = (pos->pos[2] + 2) % PREV_LINKS_HASH_CAPA;
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Any way, here's v3:
>>>>>>>>>>>>>> - excess file "v0-0001-Increase..." removed. I believe it was source
>>>>>>>>>>>>>>     of white-space apply warnings.
>>>>>>>>>>>>>> - this mistake fixed
>>>>>>>>>>>>>> - more clear slots strategies + "8 positions in two
>>>>>>>>>>>>>> cache-lines" strategy.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> You may play with switching PREV_LINKS_HASH_STRATEGY to 2 or 3
>>>>>>>>>>>>>> and see
>>>>>>>>>>>>>> if it affects measurably.
>>>>>>>>>>>>>
>>>>>>>>>>>>> Thanks for your quick fixing.  I will retest it tomorrow.
>>>>>>>>>>>> Hi, Yura Sokolov
>>>>>>>>>>>> Here is my test result of the v3 patch:
>>>>>>>>>>>> | case                          | min        | avg        | max
>>>>>>>>>>>> |
>>>>>>>>>>>> |-------------------------------+------------+------------
>>>>>>>>>>>> +------------|
>>>>>>>>>>>> | master (44b61efb79)           | 865,743.55 | 871,237.40 |
>>>>>>>>>>>> 874,492.59 |
>>>>>>>>>>>> | v3                            | 857,020.58 | 860,180.11 |
>>>>>>>>>>>> 864,355.00 |
>>>>>>>>>>>> | v3 PREV_LINKS_HASH_STRATEGY=2 | 853,187.41 | 855,796.36 |
>>>>>>>>>>>> 858,436.44 |
>>>>>>>>>>>> | v3 PREV_LINKS_HASH_STRATEGY=3 | 863,131.97 | 864,272.91 |
>>>>>>>>>>>> 865,396.42 |
>>>>>>>>>>>> It seems there are some performance decreases :( or something I
>>>>>>>>>>>> missed?
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> Hi, Japin.
>>>>>>>>>>> (Excuse me for duplicating message, I found I sent it only to you
>>>>>>>>>>> first time).
>>>>>>>>>>>
>>>>>>>>>> Never mind!
>>>>>>>>>>
>>>>>>>>>>> v3 (as well as v2) doesn't increase NUM_XLOGINSERT_LOCKS itself.
>>>>>>>>>>> With only 8 in-progress inserters spin-lock is certainly better
>>>>>>>>>>> than any
>>>>>>>>>>> more complex solution.
>>>>>>>>>>>
>>>>>>>>>>> You need to compare "master" vs "master + NUM_XLOGINSERT_LOCKS=64" vs
>>>>>>>>>>> "master + NUM_XLOGINSERT_LOCKS=64 + v3".
>>>>>>>>>>>
>>>>>>>>>>> And even this way I don't claim "Lock-free reservation" gives any
>>>>>>>>>>> profit.
>>>>>>>>>>>
>>>>>>>>>>> That is why your benchmarking is very valuable! It could answer, does
>>>>>>>>>>> we need such not-small patch, or there is no real problem at all?
>>>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Hi, Yura Sokolov
>>>>>>>>>
>>>>>>>>> Here is the test result compared with NUM_XLOGINSERT_LOCKS and the
>>>>>>>>> v3 patch.
>>>>>>>>>
>>>>>>>>> | case                  | min          | avg          |
>>>>>>>>> max          | rate% |
>>>>>>>>> |-----------------------+--------------+--------------+--------------
>>>>>>>>> +-------|
>>>>>>>>> | master (4108440)      | 891,225.77   | 904,868.75   |
>>>>>>>>> 913,708.17   |        |
>>>>>>>>> | lock 64               | 1,007,716.95 | 1,012,013.22 |
>>>>>>>>> 1,018,674.00 | 11.84 |
>>>>>>>>> | lock 64 attempt 1     | 1,016,716.07 | 1,017,735.55 |
>>>>>>>>> 1,019,328.36 | 12.47 |
>>>>>>>>> | lock 64 attempt 2     | 1,015,328.31 | 1,018,147.74 |
>>>>>>>>> 1,021,513.14 | 12.52 |
>>>>>>>>> | lock 128              | 1,010,147.38 | 1,014,128.11 |
>>>>>>>>> 1,018,672.01 | 12.07 |
>>>>>>>>> | lock 128 attempt 1    | 1,018,154.79 | 1,023,348.35 |
>>>>>>>>> 1,031,365.42 | 13.09 |
>>>>>>>>> | lock 128 attempt 2    | 1,013,245.56 | 1,018,984.78 |
>>>>>>>>> 1,023,696.00 | 12.61 |
>>>>>>>>> | lock 64 v3            | 1,010,893.30 | 1,022,787.25 |
>>>>>>>>> 1,029,200.26 | 13.03 |
>>>>>>>>> | lock 64 attempt 1 v3  | 1,014,961.21 | 1,019,745.09 |
>>>>>>>>> 1,025,511.62 | 12.70 |
>>>>>>>>> | lock 64 attempt 2 v3  | 1,015,690.73 | 1,018,365.46 |
>>>>>>>>> 1,020,200.57 | 12.54 |
>>>>>>>>> | lock 128 v3           | 1,012,653.14 | 1,013,637.09 |
>>>>>>>>> 1,014,358.69 | 12.02 |
>>>>>>>>> | lock 128 attempt 1 v3 | 1,008,027.57 | 1,016,849.87 |
>>>>>>>>> 1,024,597.15 | 12.38 |
>>>>>>>>> | lock 128 attempt 2 v3 | 1,020,552.04 | 1,024,658.92 |
>>>>>>>>> 1,027,855.90 | 13.24 |
>>>>>>>
>>>>>>> The data looks really interesting and I recognize the need for further
>>>>>>> investigation. I'm not very familiar with BenchmarkSQL but we've done
>>>>>>> similar tests with HammerDB/TPCC by solely increasing
>>>>>>> NUM_XLOGINSERT_LOCKS from 8 to 128, and we observed a significant
>>>>>>> performance drop of ~50% and the cycle ratio of spinlock acquisition
>>>>>>> (s_lock) rose to over 60% of the total, which is basically consistent
>>>>>>> with the previous findings in [1].
>>>>>>>
>>>>>>> Could you please share the details of your test environment, including
>>>>>>> the device, configuration, and test approach, so we can collaborate on
>>>>>>> understanding the differences?
>>>>>> Sorry for the late reply. I'm on my vacation.
>>>>>> I use Hygon C86 7490 64-core, it has 8 NUMA nodes with 1.5T memory,
>>>>>> and
>>>>>> I use 0-3 run the database, and 4-7 run the BenchmarkSQL.
>>>>>> Here is my database settings:
>>>>>> listen_addresses = '*'
>>>>>> max_connections = '1050'
>>>>>> shared_buffers = '100GB'
>>>>>> work_mem = '64MB'
>>>>>> maintenance_work_mem = '512MB'
>>>>>> max_wal_size = '50GB'
>>>>>> min_wal_size = '10GB'
>>>>>> random_page_cost = '1.1'
>>>>>> wal_buffers = '1GB'
>>>>>> wal_level = 'minimal'
>>>>>> max_wal_senders = '0'
>>>>>> wal_sync_method = 'open_datasync'
>>>>>> wal_compression = 'lz4'
>>>>>> track_activities = 'off'
>>>>>> checkpoint_timeout = '1d'
>>>>>> checkpoint_completion_target = '0.95'
>>>>>> effective_cache_size = '300GB'
>>>>>> effective_io_concurrency = '32'
>>>>>> update_process_title = 'off'
>>>>>> password_encryption = 'md5'
>>>>>> huge_pages = 'on'
>>>>>>
>>>>>>>> Sorry for pause, it was my birthday, so I was on short vacation.
>>>>>>>> So, in total:
>>>>>>>> - increasing NUM_XLOGINSERT_LOCKS to 64 certainly helps
>>>>>>>> - additional lock attempts seems to help a bit in this benchmark,
>>>>>>>>   but it helps more in other (rather synthetic) benchmark [1]
>>>>>>>> - my version of lock-free reservation looks to help a bit when
>>>>>>>>   applied alone, but look strange in conjunction with additional
>>>>>>>>   lock attempts.
>>>>>>>> I don't see small improvement from my version of Lock-Free
>>>>>>>> reservation
>>>>>>>> (1.1% = 1023/1012) pays for its complexity at the moment.
>>>>>>>
>>>>>>> Due to limited hardware resources, I only had the opportunity to
>>>>>>> measure the performance impact of your v1 patch of the lock-free hash
>>>>>>> table with 64 NUM_XLOGINSERT_LOCKS and the two lock attempt patch. I
>>>>>>> observed an improvement of *76.4%* (RSD: 4.1%) when combining them
>>>>>>> together on the SPR with 480 vCPUs. I understand that your test
>>>>>>> devices may not have as many cores, which might be why this
>>>>>>> optimization brings an unnoticeable impact. However, I don't think
>>>>>>> this is an unreal problem. In fact, this issue was raised by our
>>>>>>> customer who is trying to deploy Postgres on devices with hundreds of
>>>>>>> cores, and I believe the resolution of this performance issue would
>>>>>>> result in real impacts.
>>>>>>>
>>>>>>>> Probably, when other places will be optimized/improved, it will pay
>>>>>>>> more.
>>>>>>>> Or probably Zhiguo Zhou's version will perform better.
>>>>>>>>
>>>>>>>
>>>>>>> Our primary difference lies in the approach to handling the prev-link,
>>>>>>> either via the hash table or directly within the XLog buffer. During
>>>>>>> my analysis, I didn't identify significant hotspots in the hash table
>>>>>>> functions, leading me to believe that both implementations should
>>>>>>> achieve comparable performance improvements.
>>>>>>>
>>>>>>> Following your advice, I revised my implementation to update the
>>>>>>> prev-link atomically and resolved the known TAP tests. However, I
>>>>>>> encountered the last failure in the recovery/t/027_stream_regress.pl
>>>>>>> test. Addressing this issue might require a redesign of the underlying
>>>>>>> writing convention of XLog, which I believe is not necessary,
>>>>>>> especially since your implementation already achieves the desired
>>>>>>> performance improvements without suffering from the test failures. I
>>>>>>> think we may need to focus on your implementation in the next phase.
>>>>>>>
>>>>>>>> I think, we could measure theoretical benefit by completely ignoring
>>>>>>>> fill of xl_prev. I've attached patch "Dumb-lock-free..." so you could
>>>>>>>> measure. It passes almost all "recovery" tests, though fails two
>>>>>>>> strictly dependent on xl_prev.
>>>>>>>
>>>>>>
>>>>>>> I currently don't have access to the high-core-count device, but I
>>>>>>> plan to measure the performance impact of your latest patch and the
>>>>>>> "Dump-lock-free..." patch once I regain access.
>>>>>>>> [1] https://postgr.es/m/3b11fdc2-9793-403d-
>>>>>>>> b3d4-67ff9a00d447%40postgrespro.ru
>>>>>>>> ------
>>>>>>>> regards
>>>>>>>> Yura
>>>>>>>
>>>>>>> Hi Yura and Japin,
>>>>>>>
>>>>>>> Thanks so much for your recent patch works and discussions which
>>>>>>> inspired me a lot! I agree with you that we need to:
>>>>>>> - Align the test approach and environment
>>>>>>> - Address the motivation and necessity of this optimization
>>>>>>> - Further identify the optimization opportunities after applying
>>>>>>> Yura's patch
>>>>>>>
>>>>>>> WDYT?
>>>>>>>
>>>>>>> [1]
>>>>>>> https://www.postgresql.org/message-id/6ykez6chr5wfiveuv2iby236mb7ab6fqwpxghppdi5ugb4kdyt%40lkrn4maox2wj
>>>>>>>
>>>>>>> Regards,
>>>>>>> Zhiguo
>>>>>>
>>>>>
>>>>> Hi Japin,
>>>>>
>>>>> Apologies for the delay in responding—I've just returned from
>>>>> vacation. To move things forward, I'll be running the BenchmarkSQL
>>>>> workload on my end shortly.
>>>>>
>>>>> In the meantime, could you run the HammerDB/TPCC workload on your
>>>>> device? We've observed significant performance improvements with this
>>>>> test, and it might help clarify whether the discrepancies we're seeing
>>>>> stem from the workload itself. Thanks!
>>>>>
>>>>
>>>> Sorry, I currently don't have access to the test device, I will try to test
>>>> it if I can regain access.
>>>>
>>>
>>> Good day, Yura and Japin!
>>>
>>> I recently acquired the SUT device again and had the opportunity to
>>> conduct performance experiments using the TPC-C benchmark (pg_count_ware
>>> 757, vu 256) with HammerDB on an Intel CPU with 480 vCPUs. Below are the
>>> results and key observations:
>>>
>>> +----------------+-------------+------------+-------------+------------+
>>> | Version | NOPM | NOPM Gain% | TPM | TPM(Gain%) |
>>> +----------------+-------------+------------+-------------+------------+
>>> | master(b4a07f5)| 1,681,233 | 0.0% | 3,874,491 | 0.0% |
>>> | 64-lock | 643,853 | -61.7% | 1,479,647 | -61.8% |
>>> | 64-lock-v4 | 2,423,972 | 44.2% | 5,577,580 | 44.0% |
>>> | 128-lock | 462,993 | -72.5% | 1,064,733 | -72.5% |
>>> | 128-lock-v4 | 2,468,034 | 46.8% | 5,673,349 | 46.4% |
>>> +----------------+-------------+------------+-------------+------------+
>>>
>>> - Though the baseline (b4a07f5) has improved compared to when we created
>>> this mailing list, we still achieve 44% improvement with this optimization.
>>> - Increasing NUM_XLOGINSERT_LOCKS solely to 64/128 leads to severe
>>> performance regression due to intensified lock contention.
>>> - Increasing NUM_XLOGINSERT_LOCKS and applying the lock-free xlog
>>> insertion optimization jointly improve overall performance.
>>> - 64 locks seems the sweet spot for achieving the most performance
>>> improvement.
>>>
>>> I also executed the same benchmark, TPCC, with BenchmarkSQL (I'm not
>>> sure if the difference of their implementation of TPCC would lead to
>>> some performance gap). I observed that:
>>>
>>> - The performance indicator (NOPM) shows differences of several
>>> magnitudes compared to Japin's results.
>>> - NOPM/TPM seems insensitive to code changes (lock count increase,
>>> lock-free algorithm), which is quite strange.
>>> - Possible reasons may include: 1) scaling parameters [1] are not
>>> aligned, 2) test configuration did not reach the pain point of the XLog
>>> insertions.
>>>
>>> And I noticed a 64-core device (32 cores for the server) was used in
>>> Japin's test. In our previous core-scaling test (attached), 32/64 cores
>>> may not be enough to show the impact of the optimization, I think that
>>> would be one of the reason why Japin observed minimal impact from the
>>> lock-free optimization.
>>>
>>> In summary, I think:
>>> - The TPC-C benchmark (pg_count_ware 757, vu 256) with HammerDB
>>> effectively reflects performance in XLog insertions.
>>> - This test on a device with hundreds of cores reflects a real user
>>> scenario, making it a significant consideration.
>>> - The lock-free algorithm with the lock count increased to 64 can bring
>>> significant performance improvements.
>>>
>>> So I propose to continue the code review process for this optimization
>>> patch. WDYT?
>>>
>>> [1]
>>> https://github.com/pgsql-io/benchmarksql/blob/master/docs/PROPERTIES.md#scaling-parameters
>>
>> Good day.
>>
>> I'll just repeat my answer from personal mail:
>>
>> I'm impressed with results. I really didn't expect it is so important for
>> huge servers.
>>
>> Main problem will be to prove this patch doesn't harm performance on
>> smaller servers. Or made things configurable so that smaller server still
>> uses simpler code.
>>
>> -------
>> regards
>> Yura Sokolov aka funny-falcon
>
> Good day, Yura!
>
> Firstly, I'd apologize for the delayed response due to internal
> urgencies and the time required to set up the tests on a new device.
>
> Regarding your concerns about the potential negative impact on
> performance, I have conducted further evaluations. To assess the
> performance impact of the patch on a smaller device, I located another
> device with significantly fewer processors. Using the same database and
> test configurations (TPC-C benchmark: pg_count_ware 757, vu 256) and
> code bases (b4a07f5 as "base" and v4 patch with 64 locks as "opt"), I
> performed core scaling tests ranging from 8 to 64 physical cores in
> steps of 8. The results (attached) indicate that the optimization does
> not lead to performance regression within this low core count range.
>
> Please kindly let me know if more data is required to move the process
> forward.
>
> I look forward to your insights.

Good day, Zhiguo.

Thank you a lot for testing!
I will validate on servers I have access too (and on notebook).

To be honestly, I didn't bench v4, and it fixes cache-line sharing issue i
mistakenly introduced in previous version. So probably it is really doesn't
affect performance as v3 did.

-------
regards
Yura Sokolov aka funny-falcon

Reliably fails tests on windows, due to what looks to be a null pointer dereference.

E.g. https://cirrus-ci.com/task/6178371937239040

That's likely related to EXEC_BACKEND.

The new status of this patch is: Waiting on Author

Good day, Andres.

18.03.2025 23:40, Andres Freund wrote:
> Reliably fails tests on windows, due to what looks to be a null pointer dereference.
>
> E.g. https://cirrus-ci.com/task/6178371937239040
>
> That's likely related to EXEC_BACKEND.
>
> The new status of this patch is: Waiting on Author

Thank you very much for pointing on!
Yes, I've missed copying from XLogCtl as it is done for WALInsertLocks.
Fixed.

--
regards
Yura Sokolov aka funny-falcon

Just rebase

--
regards
Yura Sokolov aka funny-falcon