From: "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>
To: 'Andres Freund' <andres@anarazel.de>
CC: pgsql-hackers <pgsql-hackers@postgresql.org>, Amit Kapila
	<amit.kapila16@gmail.com>, Aleksander Alekseev <aleksander@timescale.com>,
	shveta malik <shveta.malik@gmail.com>, "tomas@vondra.me" <tomas@vondra.me>,
	Jan Wieck <jan@wi3ck.info>, "Zhijie Hou (Fujitsu)" <houzj.fnst@fujitsu.com>
Subject: RE: Commit Timestamp and LSN Inversion issue
Thread-Topic: Commit Timestamp and LSN Inversion issue
Thread-Index:
 AQHa/pcuaMY0um2heUGX6w6EchTH+bJHTW+AgAAyFICAABlbAIABFeMAgGBprgCAA22dAIAA+HKAgATBV0CAAdsYgIAA1OVg
Date: Wed, 13 Nov 2024 03:00:03 +0000
Message-ID:
 <TYAPR01MB5692056065E77BBE94522DDAF55A2@TYAPR01MB5692.jpnprd01.prod.outlook.com>
References:
 <CAJpy0uBxEJnabEp3JS=n9X19Vx2ZK3k5AR7N0h-cSMtOwYV3fA@mail.gmail.com>
 <CAJ7c6TNZY3idBDoGOaoS_fF6vJuBMjnDhZC4i5V0J58s2SJ3AA@mail.gmail.com>
 <CAA4eK1LOmtMpmJAxsmN0yXJb-Q9vi6KThDYXdpVGL_WZW5ZgvQ@mail.gmail.com>
 <CAJ7c6TOcQPcydC7HW=xPEDN6_Ec7iSTP9j=1VTAeG=haguOQHw@mail.gmail.com>
 <CAA4eK1KhAxe6=VTxa6xEbc22_VVBLsVuvRH8Wxws8i50gH=eqw@mail.gmail.com>
 <9f7f4def-7ea8-4ad3-83e7-a8cc9d18c58a@wi3ck.info>
 <gkbrjxwyjbqckjbrkep3uy2mvrcq2mrfzprfmegbeiqxidtmbu@3cgs5iocjykk>
 <OS0PR01MB57162A227EC357482FEB4470945D2@OS0PR01MB5716.jpnprd01.prod.outlook.com>
 <TYAPR01MB569222C1312E7A6C3C63539DF5582@TYAPR01MB5692.jpnprd01.prod.outlook.com>
 <43vrkfqyv72ofuxhltur35twwcxiyujqrcfu3b26yzy4o5eyg3@g4fxomgmnqgk>
In-Reply-To: <43vrkfqyv72ofuxhltur35twwcxiyujqrcfu3b26yzy4o5eyg3@g4fxomgmnqgk>
Accept-Language: ja-JP, en-US
Content-Language: ja-JP
msip_labels:
 MSIP_Label_a7295cc1-d279-42ac-ab4d-3b0f4fece050_ActionId=cf922fea-c5f1-4872-aada-710d254f050a;MSIP_Label_a7295cc1-d279-42ac-ab4d-3b0f4fece050_ContentBits=0;MSIP_Label_a7295cc1-d279-42ac-ab4d-3b0f4fece050_Enabled=true;MSIP_Label_a7295cc1-d279-42ac-ab4d-3b0f4fece050_Method=Standard;MSIP_Label_a7295cc1-d279-42ac-ab4d-3b0f4fece050_Name=FUJITSU-RESTRICTED?;MSIP_Label_a7295cc1-d279-42ac-ab4d-3b0f4fece050_SetDate=2024-11-13T02:48:21Z;MSIP_Label_a7295cc1-d279-42ac-ab4d-3b0f4fece050_SiteId=a19f121d-81e1-4858-a9d8-736e267fd4c7;
authentication-results: dkim=none (message not signed)
 header.d=none;dmarc=none action=none header.from=fujitsu.com;
x-ms-publictraffictype: Email
x-ms-traffictypediagnostic: TYAPR01MB5692:EE_|OS0PR01MB5955:EE_
x-ms-office365-filtering-correlation-id: 9daba7ee-d982-4562-ee05-08dd038f4544
x-ms-exchange-senderadcheck: 1
x-ms-exchange-antispam-relay: 0
x-microsoft-antispam:
 BCL:0;ARA:13230040|366016|376014|1800799024|38070700018|1580799027;
x-microsoft-antispam-message-info:
 =?iso-2022-jp?B?NmFuelpaa3RPbUF5SXFpMWE5Wm1LRy9SaUI1dUZacThZS2d5aGE4TUk1?=
 =?iso-2022-jp?B?RUc2R0o0RkYzbFQ3b0RxL0pCQXBLUEhZNmNPVjdJQllkTzhrbURJajFK?=
 =?iso-2022-jp?B?bkFVOG8zUG1NMENaTE4wUjFpc1UxajZJa2Y5Ylk4R2JKNitTVHhmbHRi?=
 =?iso-2022-jp?B?NE96MVRBRVZJWENITlRhbVlQQVc5UkVBbGJ1eGJOdXl2WEdTa3Raa1Bx?=
 =?iso-2022-jp?B?R3BpejI4VE01OGtZMm1ZTnVRY2M1c1lvMWNQZG04MnViUkRHbmQvdnpH?=
 =?iso-2022-jp?B?anRxKzRZa3EyN1QvcGRaME9IVllFQXZ3MDVLVStQRmJnK29sQ1pPTlZx?=
 =?iso-2022-jp?B?UHN4NFVJbThzVjR1bUhuUksyRnlrN00wdDJiNHhqNXR5ZUwzWk13QmRK?=
 =?iso-2022-jp?B?cVYrc084VHFTWVZzM0ZMeGs4a3RtWHVkeG02S3RBcWlCQ1hiZ2dYaXdk?=
 =?iso-2022-jp?B?WGNPdVN1Nnlhb09qMEJKRVFvRlVaRXdid2JCTjl6MGNZTG1BcXgwbEx2?=
 =?iso-2022-jp?B?eitld3EvNmVyQkZqNVN1S2ZhWStlMTZmNEZWRjdiWWxxc0djazlLM29v?=
 =?iso-2022-jp?B?b1VkckluRFprNERRQWFjLzAyRHJsNmdNd3FPdXNmc0NQVXcxRjJ5OUNo?=
 =?iso-2022-jp?B?YzNjK01mbk8yLzRUKzdZemFXaWNKNmQrN2VZblRJcjY2ZlV6V3B4RUJw?=
 =?iso-2022-jp?B?dU1NdmdxRFZucUNjU0t1Rzl2ZjlJMDgrdldvYXM3MjJnTU9zQ1Q4dHg3?=
 =?iso-2022-jp?B?TStSaWVPU2pQM2UweStnSWZVcm1aZk9JcjMzUkp1VW1QYjllMy9sYkIy?=
 =?iso-2022-jp?B?Mmp0c1hTRGFmTUN1UkdUb1ZYOXFqR2c1SWgwbHZhQTNZdlF2QXpzbVVQ?=
 =?iso-2022-jp?B?YUo5bVZYekJwTDFXcUI2TmFTcnlndDRBd3l1QVdPaEc1enl3TDRCU1Rl?=
 =?iso-2022-jp?B?Z2ZPQ1YxV2RFVHJEV2hZcWxNN2xueWx2aUs1cCtTNkY3Um8zMWNiVFQ4?=
 =?iso-2022-jp?B?RW1XVGVTZi95em1SQW5ET2dTNlJZWVRFNWh3YzJ2UmRpWEpYTEtsQkUr?=
 =?iso-2022-jp?B?cUlQdnpLZ0l6UXNnaU5VU2xtVVVCTDFMRjZQZU9QTEFGaXM2dm5YV2Fx?=
 =?iso-2022-jp?B?Y2d2dTR6YzJJMkxPSi9CUzZFQlZycHZSZTFHRzBCWmpIQStXWWtDT2ox?=
 =?iso-2022-jp?B?UHljNklLeGVSUmRPVm5JbXBVaU9qRlFUa01EL3JBNHYvRXMxV1d6Y3Vh?=
 =?iso-2022-jp?B?dlBpZHNWa2V5OXNHVGZPVXlyZXNwbW93ajF3Qm5jV3dFL0xhWE1MaTVR?=
 =?iso-2022-jp?B?bTBETWtYL3FqdlR6TWFzUnVwUzNMeDFLTzh0L0Zkck1nRHB6Tit4SW5q?=
 =?iso-2022-jp?B?eHRLSTNnWFo3NDdWS3E2NXU3VzBwWDMzVjdqWVBxdkNuM2wwOHVZdXIy?=
 =?iso-2022-jp?B?ZzB0dXVjTVlmcGRNSHQ2c0l3dEVKc0dpUWprTXZwdGgycDhRNjhLcnVq?=
 =?iso-2022-jp?B?RkFmZ1dDMUJJOVdTeHByanpOR2pXZHdpOUo1U1psbGRrSlZRN3IvTTkw?=
 =?iso-2022-jp?B?a3VaOFlVblFWWkxoOCtxdGgyR1ordnhySnBpeFJaS2ppT0Y2VzJBZDBM?=
 =?iso-2022-jp?B?amNtMUpUTXhnTHJkV3BWang5a0dLaTdCWFFid3VlOGF4WjJnZGl4S1FM?=
 =?iso-2022-jp?B?d1J3Rko5MnY2U2lubytBZjRjaW5sQ2NuaXNzZktMaFVvblpHalRJQjdK?=
 =?iso-2022-jp?B?K1VXTmNZSmxRNm1kZmxBMGQ5S2VDNDRTUC9VNlJ5bzdobHpucUVyNklh?=
 =?iso-2022-jp?B?YjhoNDI4MkVudURXSzdwSVJHb1pQL2RLMm5YcjQzTTZ2cDExK2E1RG1U?=
 =?iso-2022-jp?B?Rm04OHlFYi9ZaGszNEtnQzZ6em5weW4wd3ZPNTA1VWJocmEyS3ZTTTJS?=
 =?iso-2022-jp?B?YnFsblR2VmI1VXJaT0FWMHo0YTBwcDU3eEZqV2dtajNxRWQ3MEh1WXUw?=
 =?iso-2022-jp?B?SjBPSWgzdkFqaGw4eDFvUXdRNVp5M2ZKUkhxc0J1QkttazZ1ZU4yYWpD?=
 =?iso-2022-jp?B?Ync9PQ==?=
x-forefront-antispam-report:
 CIP:255.255.255.255;CTRY:;LANG:ja;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:TYAPR01MB5692.jpnprd01.prod.outlook.com;PTR:;CAT:NONE;SFS:(13230040)(366016)(376014)(1800799024)(38070700018)(1580799027);DIR:OUT;SFP:1101;
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0:
 =?iso-2022-jp?B?SVY3RzBaRXpON0NWUWloWUlZSkQ4K0dXTDV5Z1doMUhIVFBZYjVKdkFC?=
 =?iso-2022-jp?B?Nnp0NHJ1d1hPZWd1dS9lT2hKbFBNdmN2UEUxNC93Nm55d2FBYkxqaksy?=
 =?iso-2022-jp?B?cG11ZjVSNmFaVG5XeC9DWEhrckVPRXZDaUV1MDZXa2dMK0lRVUFKMVF6?=
 =?iso-2022-jp?B?QzNnQktEbmFTc3YwcExkU1AxeGZrUVZURTRWcmtvQkQvQ29GYVBObGZB?=
 =?iso-2022-jp?B?MWl3SmlDN1JQYUFIOG9WNHJjdUhSdE01emtGL3p2ZEM2dWU1dzd4d1p1?=
 =?iso-2022-jp?B?dHBXUE51NDgwVlNkVWVwV3pMRVFxYXVraFo0bWFtVmR6RjA1R1FnYi9R?=
 =?iso-2022-jp?B?bG9CdlR6TGR1L1d6RWlFSXNMY3JwaS9WekpzTm1tRGQrTTB0NHVqQjV0?=
 =?iso-2022-jp?B?bWFvdys1cjBLZzVXQXhkWXkwK3FISkU2NmtCTnZST1htZG1ReTA4NkZx?=
 =?iso-2022-jp?B?QmhmQ1dCcGdid1c5YlJZZndSYzdoZjNpQldIaDdndjNldWZDQW9KYmFX?=
 =?iso-2022-jp?B?TmhrSkdnOUZucGVPRVlOc2hIYitUTWUzU3lrYnJ3SGdhM0VGMXY0d1lR?=
 =?iso-2022-jp?B?V3hpU2dqZWFSRkVBak5JaWFJZWQ1VWNoQm4zVDZFZ1J4TERTZDk3UGdn?=
 =?iso-2022-jp?B?eTdlbkNrcjBjajA1VDVITWJXM1lRRWVyVCt1UTBTMTFHRE80T3NSMlRQ?=
 =?iso-2022-jp?B?enFNd2R0dUI3aVhZN2c5RkVaTnhJekhJV1hZVDJzd2kyQTg3Q05zRXh5?=
 =?iso-2022-jp?B?U2dIVmtYcmFBY1FxZ1BMeXZFdVJ1bnVTUUZGNy9LTDVBbEY3Zm1PQksy?=
 =?iso-2022-jp?B?WHVTaHNlMHlObE9iTFRxVFJBNE11NGw0QkVoZHpBZ0hLb3UxMUp3ZFBL?=
 =?iso-2022-jp?B?M2FCV1g0ZFJ2MHdVZ012dmd1RHNSdXZxYldBZWpOQkVhWlVmVGdsRlFN?=
 =?iso-2022-jp?B?UnNaZ3A1VHJoZFpwSE1yQ3RseFprM3ZGTmg1a3R5R3pwS29BdHlnSEh2?=
 =?iso-2022-jp?B?cUhtcThaZG92cXd2Z1ZjUnZtT1ZvbnN3dkw2TGozL1dDRVZFSU9HNHJw?=
 =?iso-2022-jp?B?c1N2U2NCUFZDcHRsWnBhTkxqM3d3SXRkVkNURW1JVkgvNVAremEzV2lp?=
 =?iso-2022-jp?B?ZDd0aTNDMGRvZm5YdUwza25xZE13L0VqNkx3dlVBMVcyMm8yQVlSR0xW?=
 =?iso-2022-jp?B?THRtMmV0MCszNnM1L2ltSFpKR2F6cGpreXRiaWNwTGt6VnNOTlBDK1lN?=
 =?iso-2022-jp?B?Wko5bFRxeU4xQmpWVWpjcHo0VTFQb1d3OTZxSit6YVZtWTJZSVFhdEpD?=
 =?iso-2022-jp?B?TG5WNTR3N0w2VW9qU0tsd2Nad3RRcXBqQTJ6NHkyaXh5KzVlOEVQYVlD?=
 =?iso-2022-jp?B?bXdXQWpMc2JWNFMvVnhSWVhuNEQvdzFuS05GZjBneng3Q0F6WjJpckFy?=
 =?iso-2022-jp?B?Y1dhNlhUckh3NVp2cHRkd1NFMytMa3hSL2Jjb28zRTYrM2p2eVQ1dklI?=
 =?iso-2022-jp?B?ZnZjZUp4VGlIT1k4WHlzV2xpQ3Yzam9icm9BZGVONmdtd0Zua0ppWlo2?=
 =?iso-2022-jp?B?OFNFL1o5ZVF6Zkt5Z0U3bVg5Nks5eFZ4Nmpkd09yZ0dOVytSYzMvRExi?=
 =?iso-2022-jp?B?Mm1pSEdnUyt6YjZYanpaS0h2Vnd0S1U3eVVIMGt5N0sxNmN4UUpFK1Jw?=
 =?iso-2022-jp?B?NTVxeXdqTUxVdjJXTGlmcVF6WHBZNjE3VUg1dU11NkN1d0JpTXR6T0hC?=
 =?iso-2022-jp?B?SmNsSlRZbWdNVWVndVNveU85cTFweHA4NTg3eStKaGtMUFN5czg0MXVw?=
 =?iso-2022-jp?B?Q3F0dzY5L08zbHNQL1BIMkc0MThHbTRLTHJlbERoWEtvUGVzb0ZvWGgz?=
 =?iso-2022-jp?B?cDBkbTlIVXk4VkFrS1hpZitKazMxOXhPZmZ0N053MlRzdkRkU0pSZHJR?=
 =?iso-2022-jp?B?eWd1RHhZQWErL3htS2tMMzdVWTZ4OGZLU1FqaVpsVGc5eTFac29Ubkxu?=
 =?iso-2022-jp?B?S3JEdXdyUGhVb2t0QlNwWEhST0ZkRUVZQnV3NTRuL05POGtNU0RlaDJS?=
 =?iso-2022-jp?B?aUNNUmZOUTBoV25TODJaZmRFUlYzcWloMXlCSVdtN2thQmJVakFLckk5?=
 =?iso-2022-jp?B?MkVSTEk0TW15SHg1L0FOZGdOWTFPNTZBODBpK09vSlJkZmpvcUoyNUhr?=
 =?iso-2022-jp?B?bUtQVlhoNnYwdGt0cUJNZll1NWZCYUdNY2psZmFqb2dlTkdwaXlTdTJB?=
 =?iso-2022-jp?B?UERyd2xQRm1FbUpRdVAzNHFBelI0TG1jTGdyc2FrUkRXYmVudlZsNHhM?=
 =?iso-2022-jp?B?Sk1IT2hBUGRKOGlFbkZJT0VNcnV4NW5GVlE9PQ==?=
Content-Type: text/plain; charset="iso-2022-jp"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-MS-Exchange-AntiSpam-ExternalHop-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-ExternalHop-MessageData-0:
	fDBLZTi0DGLkk96ZZfOUGjGB5XO5dEQ+9Y5nDiV8I4qlxuEtyapVJuTOWVjhiAvB2OThSVoqfL+m8Q9gm5wKTeUK6URQV12OYgJT6Qn9mV3S7ZjP16lh8DQMP8cYdbTH6f0yAqtdnpWc1Bb4Otuy6FM2yMK9iKOgyRpZEH2K60H/stY4TGwLVRzmCiUu0TJdbBcMhDKAJ5zsUSkVTsPjkNZQSmcnSwt8I40utorbEkKdIF/tg8ReP4m4lcdoPOJlgd1YWylJyg31fQ0XNCPuHNIcTi+D4HClkgbElUNnmOOVjlyQB7qDXunTu94HdlDJSwkraaUvcwCnbBiZgpuwDSguJyIRYy/DgibdZVe7PQaMFvIgRfo9AOU4wowTgPazL9/Rb8w+ppv8hzfFfJq/v05MSPf39JY4nJ8xd1y+yOY4mHEfG5ym7cXnOorlnkTumd+7EUFAW9RtBzrxh0qzKbkhsLNNoW4xWsr9CawZLuwDTAQgMLXJvuv40BjisBnUNDtCeroKF8cVpfvIVPqZo41jxu2e7t6gETto27FJbS5z8Ks0Kii+6GxquiCf1s+Kg9lQThrNr5XGjUB+LvdfZ3zfkJWb+CXZKLhd10KdMo+cwJ9mrKpbYCFO03bVgyyj
X-OriginatorOrg: fujitsu.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: TYAPR01MB5692.jpnprd01.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 9daba7ee-d982-4562-ee05-08dd038f4544
X-MS-Exchange-CrossTenant-originalarrivaltime: 13 Nov 2024 03:00:03.3803
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: a19f121d-81e1-4858-a9d8-736e267fd4c7
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: XYDLtOsGvJ1Ly/4gGf61Hn0B1BO/GYFAVCt6svkUMiwqMgU9DKvu9FkRdddKNp6RhczQYBs95BM9gltPxrt0eCoWshBgO5O92GU97pMnfwE=
X-MS-Exchange-Transport-CrossTenantHeadersStamped: OS0PR01MB5955
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: <https://www.postgresql.org/message-id/TYAPR01MB5692056065E77BBE94522DDAF55A2%40TYAPR01MB5692.jpnprd01.prod.outlook.com>
Precedence: bulk

Dear Andres,

Thanks for giving comments for my test!

> This is not a useful measurement for overhead introduced in
> ReserveXLogInsertLocation(). What you're measuring here is the number of
> commits/second, not the WAL insertion rate. The number of commits/second =
is
> largely determined by your disk's write latency, the batching of WAL flus=
hes
> and things like the SLRU code.

You meant that committing transactions is huge work so that it may hide per=
formance
regression of ReserveXLogInsertLocation(), right? But...

> To measure the effect of changes to ReserveXLogInsertLocation() use somet=
hing
> like this as a pgbench script:
> SELECT pg_logical_emit_message(false, \:client_id::text, '1'), generate_s=
eries(1,
> 10000) OFFSET 10000;

I don't think the suggested workload is useful here. pg_logical_emit_messag=
e(transactional =3D false)
does insert the WAL without the commit, i.e., xlcommitrecis always NULL.
This means backends won't go through added codes in ReserveXLogInsertLocati=
on().


## Appendix - gdb results

1. connected a postgres instance
2. attached the backend process via gdb
3. added a breakpoint to XLogInsert
4. executed a command: SELECT pg_logical_emit_message(false, '1', '1'), gen=
erate_series(1, 10000) OFFSET 10000;
5. the process stopped at the break point. Backtrace was:
```
(gdb) bt
#0  XLogInsert (rmid=3D21 '\025', info=3D0 '\000') at xloginsert.c:479
#1  0x000000000094a640 in LogLogicalMessage (prefix=3D0x23adfd0 "1", messag=
e=3D0x23a34c4 "1~\177\177\060", size=3D1,=20
    transactional=3Dfalse, flush=3Dfalse) at message.c:72
#2  0x000000000094a545 in pg_logical_emit_message_bytea (fcinfo=3D0x23a65d0=
) at logicalfuncs.c:376
#3  0x000000000094a56f in pg_logical_emit_message_text (fcinfo=3D0x23a65d0)=
 at logicalfuncs.c:385
```
6. confirmed xlcommitrec was NULL
```
(gdb) p xlcommitrec
$1 =3D (xl_xact_commit *) 0x0
```

Best regards,
Hayato Kuroda
FUJITSU LIMITED