MIME-Version: 1.0
References: <CAJpy0uBxEJnabEp3JS=n9X19Vx2ZK3k5AR7N0h-cSMtOwYV3fA@mail.gmail.com>
In-Reply-To: <CAJpy0uBxEJnabEp3JS=n9X19Vx2ZK3k5AR7N0h-cSMtOwYV3fA@mail.gmail.com>
From: Nisha Moond <nisha.moond412@gmail.com>
Date: Mon, 9 Sep 2024 11:41:13 +0530
Message-ID: <CABdArM71Q6GYMBs19c_dVrwLub3CsaFNmR9Orf5c-agG6W1xFA@mail.gmail.com>
Subject: Re: Commit Timestamp and LSN Inversion issue
To: shveta malik <shveta.malik@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>, Amit Kapila <amit.kapila16@gmail.com>, 
	"Zhijie Hou (Fujitsu)" <houzj.fnst@fujitsu.com>, tomas@vondra.me
Content-Type: multipart/mixed; boundary="0000000000006937bc0621a9a05c"
Archived-At: <https://www.postgresql.org/message-id/CABdArM71Q6GYMBs19c_dVrwLub3CsaFNmR9Orf5c-agG6W1xFA%40mail.gmail.com>
Precedence: bulk

--0000000000006937bc0621a9a05c
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, Sep 4, 2024 at 12:23=E2=80=AFPM shveta malik <shveta.malik@gmail.co=
m> wrote:
>
> Hello hackers,
> (Cc people involved in the earlier discussion)
>
> I would like to discuss the $Subject.
>
> While discussing Logical Replication's Conflict Detection and
> Resolution (CDR) design in [1] , it came to  our notice that the
> commit LSN and timestamp may not correlate perfectly i.e. commits may
> happen with LSN1 < LSN2 but with Ts1 > Ts2. This issue may arise
> because, during the commit process, the timestamp (xactStopTimestamp)
> is captured slightly earlier than when space is reserved in the WAL.
>
>  ~~
>
>  Reproducibility of conflict-resolution problem due to the timestamp inve=
rsion
> ------------------------------------------------
> It was suggested that timestamp inversion *may* impact the time-based
> resolutions such as last_update_wins (targeted to be implemented in
> [1]) as we may end up making wrong decisions if timestamps and LSNs
> are not correctly ordered. And thus we tried some tests but failed to
> find any practical scenario where it could be a problem.
>
> Basically, the proposed conflict resolution is a row-level resolution,
> and to cause the row value to be inconsistent, we need to modify the
> same row in concurrent transactions and commit the changes
> concurrently. But this doesn't seem possible because concurrent
> updates on the same row are disallowed (e.g., the later update will be
> blocked due to the row lock).  See [2] for the details.
>
> We tried to give some thoughts on multi table cases as well e.g.,
> update table A with foreign key and update the table B that table A
> refers to. But update on table A will block the update on table B as
> well, so we could not reproduce data-divergence due to the
> LSN/timestamp mismatch issue there.
>
>  ~~
>
> Idea proposed to fix the timestamp inversion issue
> ------------------------------------------------
> There was a suggestion in [3] to acquire the timestamp while reserving
> the space (because that happens in LSN order). The clock would need to
> be monotonic (easy enough with CLOCK_MONOTONIC), but also cheap. The
> main problem why it's being done outside the critical section, because
> gettimeofday() may be quite expensive. There's a concept of hybrid
> clock, combining "time" and logical counter, which might be useful
> independently of CDR.
>
> On further analyzing this idea, we found that CLOCK_MONOTONIC can be
> accepted only by clock_gettime() which has more precision than
> gettimeofday() and thus is equally or more expensive theoretically (we
> plan to test it and post the results). It does not look like a good
> idea to call any of these when holding spinlock to reserve the wal
> position.  As for the suggested solution "hybrid clock", it might not
> help here because the logical counter is only used to order the
> transactions with the same timestamp. The problem here is how to get
> the timestamp along with wal position
> reservation(ReserveXLogInsertLocation).
>

Here are the tests done to compare clock_gettime() and gettimeofday()
performance.

Machine details :
Intel(R) Xeon(R) CPU E7-4890 v2 @ 2.80GHz
CPU(s): 120; 800GB RAM

Three functions were tested across three different call volumes (1
million, 100 million, and 1 billion):
1) clock_gettime() with CLOCK_REALTIME
2) clock_gettime() with CLOCK_MONOTONIC
3) gettimeofday()

--> clock_gettime() with CLOCK_MONOTONIC sometimes shows slightly
better performance, but not consistently. The difference in time taken
by all three functions is minimal, with averages varying by no more
than ~2.5%. Overall, the performance between CLOCK_MONOTONIC and
gettimeofday() is essentially the same.

Below are the test results -
(each test was run twice for consistency)

1) For 1 million calls:
 1a) clock_gettime() with CLOCK_REALTIME:
    - Run 1: 0.01770 seconds, Run 2: 0.01772 seconds, Average: 0.01771 seco=
nds.
 1b) clock_gettime() with CLOCK_MONOTONIC:
    - Run 1: 0.01753 seconds, Run 2: 0.01748 seconds, Average: 0.01750 seco=
nds.
 1c) gettimeofday():
    - Run 1: 0.01742 seconds, Run 2: 0.01777 seconds, Average: 0.01760 seco=
nds.

2) For 100 million calls:
 2a) clock_gettime() with CLOCK_REALTIME:
    - Run 1: 1.76649 seconds, Run 2: 1.76602 seconds, Average: 1.76625 seco=
nds.
 2b) clock_gettime() with CLOCK_MONOTONIC:
    - Run 1: 1.72768 seconds, Run 2: 1.72988 seconds, Average: 1.72878 seco=
nds.
 2c) gettimeofday():
    - Run 1: 1.72436 seconds, Run 2: 1.72174 seconds, Average: 1.72305 seco=
nds.

3) For 1 billion calls:
 3a) clock_gettime() with CLOCK_REALTIME:
    - Run 1: 17.63859 seconds, Run 2: 17.65529 seconds, Average:
17.64694 seconds.
 3b) clock_gettime() with CLOCK_MONOTONIC:
    - Run 1: 17.15109 seconds, Run 2: 17.27406 seconds, Average:
17.21257 seconds.
 3c) gettimeofday():
    - Run 1: 17.21368 seconds, Run 2: 17.22983 seconds, Average:
17.22175 seconds.
~~~~
Attached the scripts used for tests.

--
Thanks,
Nisha

--0000000000006937bc0621a9a05c
Content-Type: application/x-zip-compressed; name="clock_gettime_test.zip"
Content-Disposition: attachment; filename="clock_gettime_test.zip"
Content-Transfer-Encoding: base64
Content-ID: <f_m0ulp4i50>
X-Attachment-Id: f_m0ulp4i50

UEsDBBQAAAAIAAdVJlmQWhEA4QEAAIAEAAAtAAAAY2xvY2tfZ2V0dGltZV90ZXN0L3Rlc3RfY2xv
Y2tfZ2V0dGltZV9tb25vLnNolVNNb9swDL3rV3ANstpNYmfHtUkvxgYMS2tg6HGAoch0IlSWDFnu
DkP/+/Rhp7G3AJ0OgiQ+ko+P4uxDuucy3dP2SMgMMo3UIFDIoNHqoGkNRkGNtO00gjkiNKgrpWsq
GYKqgAnFnosDGsNrjGLCqIHNBq6/5F+v4X5sLpxvYbA1CSMzLpnoSoRNa0qukuP92ZNDuxfCpYGa
chnF8JuAXa3RHTPgAG2DzN6pNktAWd55u1DyEDbnioI2LZaFpFK1AVCqbi/wZGmRKVn2NucilGq2
n9bDejPwcExTeBjksCTAigGMCsFtyokYUHfC8EYEZEu8/xiT7fLse/GQP+ZP+eO3bAkffUFxyOVi
Rxy2sL4DDhvPzZ4Wi0GN9wS00vThXt9FIOCHWjMqWCfcn+gVC1VzCV7ToJ4Hj7S2nCMbKDEvTmFY
hT711xhu4E3h3Q4WJ7CcoN39nI2SL6jNeW73P/9FYzBvJ8TSs9TJug/daNvhKrp6crUZ+ozSaz8v
fWt9jmlvf3FzhIl6tzAXohyxi+bJ52ogGP+UV0vfxeWY1XJKu++YRtNpadtPXomdKD+hqm64CLN4
GlJyYFY2dWncLo8hrIQ2LuyPTv413g5CkvSCsycjkEroGqJrWFX/n56QP1BLAwQUAAAACADyVCZZ
NsxhSfoBAAClBAAALQAAAGNsb2NrX2dldHRpbWVfdGVzdC90ZXN0X2Nsb2NrX2dldHRpbWVfcmVh
bC5zaJVUYWvbMBD9rl9xa8iaLImdfdySFIbJoCypoeRjISjyORGVJSOfO8bof58kx2mcNbAZbCTd
07undzr3PsQ7qeMdrw6M9SCxyAmBQwKlNXvLCyADBfKqtgh0QCjR5sYWXAsEk4NQRjxv90gkCxwM
4aekg6kJqhKFzH9JvYdklSY/tuv0Id2kD/cJE5xgPofbZfr9Fu66DFtPvyWsKBKsJ7VQdYYwryiT
JjrcnS15tF9hUhMUXGqX/DcD91Rka0HgAV6Fm3NLY0CdzUJcGScqfPxWVLysMNtqrk3VADJT7xSe
IhUKo7NjzG9RxpSLz9P2eQvIGQvjOIZ1a5lTAc4wEFwp78alYUWtSJaqQVbN/i7mwr8xfAwnGjZ5
PfdAwgKmM5AwD+LcaDRq7bhG+Lj8ttrcr5eOz1lzZHv9p/wNvj1qwpWolb82R8eaQ0sNwdPGvQDu
eO0kDxxRRC/eYZg0dTpOh/AJ3hxerWB0AusLtJ+fqzH6BS2d5/ZX+D0ZbXhxISw+Sx1Nj9SldRXO
Bzcbfzbiz6iD9f0sVDbkeK8XoGv2V+grlXXEDfrRl7zVN3zSN+NQw3FX1PhSdVMw/1qk2mpXf/bK
XE+FNjZFKVXTsKdOZnvhjDPXGu56I8JEWfK0j7X+6x/gISyKr2wOYhRyDXXJbAGT/P/TM/YHUEsD
BBQAAAAIABJVJlnvCbcQDwIAAPYEAAAnAAAAY2xvY2tfZ2V0dGltZV90ZXN0L3Rlc3RfZ2V0dGlt
ZW9mZGF5LnNolVTRbtsgFH3nK+4apXWW1E4etyR9sTZpWppIVfc2KSL4OkHFYAGOVE399wG2k9hL
p40HBNzDvYdzgMGHZMdlsqPmQMgAUo3UIlBIodRqr2kBVkGB1FQawR4QStS50gWVDEHlsEdreYEq
z+hrNAIqM1CVLSsbwBpNJSxwCZJKZZApmZkAasaEUQuLBdx92Xy9g4dOuq0vtbVobMzIgEsmqgxh
YWzGVXx4uFx6NYnf1l1tVwiXFgrKpeP3i4BrxuqKOYYOcKTCTam2swmgzGbzPsCUyGpEANRxoeS+
7nxuFLQ0mG0LzrQyf0MEFWpApqqdwFOk0aOO+S1CqXI5m7btHOBzEsZJAo+tL44nOFeAUSG4q9lz
pXAm8FLUQBN2h44JxV62DThKV5v0+/Zxs948b9bf0gnchmOP6tI+fcRhCdM5cFgEfm40Hrei+tap
e9vquv6xWjVZ3v6prtN5dD5lSgWrhL+WjVj1ebuXKoA7MjuqkUsU26MXF+5rE5vpCD7CWdzVCsYn
sOyh/fySjZJH1LZzod0TuUajDS97xJKL0vG0SV1qZ24e3Tz7s1n6gjJIPsyCqaFGz9Ur7TMMhcg6
3KJh/Clv6Y1+yptJsG7S5TTpk2780mgrLZ3n5I24Jxq+CFWUXNSfwemXIHvmRFPvvN/3n7XL91TJ
Pz4WHyVxcn1bICGQSqhKogu4z/+77G9QSwECFAAUAAAACAAHVSZZkFoRAOEBAACABAAALQAAAAAA
AAABACAAAAAAAAAAY2xvY2tfZ2V0dGltZV90ZXN0L3Rlc3RfY2xvY2tfZ2V0dGltZV9tb25vLnNo
UEsBAhQAFAAAAAgA8lQmWTbMYUn6AQAApQQAAC0AAAAAAAAAAQAgAAAALAIAAGNsb2NrX2dldHRp
bWVfdGVzdC90ZXN0X2Nsb2NrX2dldHRpbWVfcmVhbC5zaFBLAQIUABQAAAAIABJVJlnvCbcQDwIA
APYEAAAnAAAAAAAAAAEAIAAAAHEEAABjbG9ja19nZXR0aW1lX3Rlc3QvdGVzdF9nZXR0aW1lb2Zk
YXkuc2hQSwUGAAAAAAMAAwALAQAAxQYAAAAA
--0000000000006937bc0621a9a05c--