public inbox for [email protected]  

help / color / mirror / Atom feed

From: Hayato Kuroda (Fujitsu) <[email protected]>
To: pgsql-hackers <[email protected]>
Cc: Amit Kapila <[email protected]>
Cc: Aleksander Alekseev <[email protected]>
Cc: shveta malik <[email protected]>
Cc: [email protected] <[email protected]>
Cc: Jan Wieck <[email protected]>
Cc: Zhijie Hou (Fujitsu) <[email protected]>
Cc: Andres Freund <[email protected]>
Subject: RE: Commit Timestamp and LSN Inversion issue
Date: Mon, 11 Nov 2024 09:49:19 +0000
Message-ID: <TYAPR01MB569222C1312E7A6C3C63539DF5582@TYAPR01MB5692.jpnprd01.prod.outlook.com> (raw)
In-Reply-To: <OS0PR01MB57162A227EC357482FEB4470945D2@OS0PR01MB5716.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>
	<[email protected]>
	<gkbrjxwyjbqckjbrkep3uy2mvrcq2mrfzprfmegbeiqxidtmbu@3cgs5iocjykk>
	<OS0PR01MB57162A227EC357482FEB4470945D2@OS0PR01MB5716.jpnprd01.prod.outlook.com>

Dear hackers,

> I understand your concern and appreciate the feedback. I've made some
> adjustments to the patch by directly placing the code to adjust the commit
> timestamp within the spinlock, aiming to keep it as efficient as possible. The
> changes have resulted in just a few extra lines. Would this approach be
> acceptable to you ?
> 
> Additionally, we're also doing performance tests for it and will share the
> results once they're available.

I've done performance tests compared with master vs. v2 patch.
It showed that for small transactions cases, the performance difference was 0-2%,
which was almost the same of the run-by-run variation.

We may completely change the approach based on the recent discussion,
but let me share it once.

## Executed workload

Very small transactions with many clients were executed and results between master
and patched were compared. Two workloads were executed and compared:

- Insert single tuple to the table which does not have indices:
```
BEGIN;
INSERT INTO foo VALUES (1);
COMMIT;
```

- Emit a transactional logical replication message:
```
BEGIN;
SELECT pg_logical_emit_message(true, 'pgbench', 'benchmarking', false);
COMMIT;
```

## Results

Here is a result.
Each run had 60s periods and median of 5 runs were chosen.

### Single-tuple insertion

# of clients  HEAD  V2  diff
1  5602.828793  5593.991167  0.158%
2  10547.04503  10615.55583  -0.650%
4  15967.80926  15651.12383  1.983%
8  31213.14796  30584.75382  2.013%
16  60321.34215  59998.0144  0.536%
32  111108.2883  110615.9216  0.443%
64  171860.0186  171359.8172  0.291%

### Transactional message

# of clients  HEAD  V2  diff
1  5714.611827  5648.9299  1.149%
2  10651.26476  10677.2745  -0.244%
4  16137.30248  15984.11671  0.949%
8  31220.16833  30772.53673  1.434%
16  60364.22808  59866.92579  0.824%
32  111887.1129  111406.4923  0.430%
64  172136.76  171347.5658  0.458%

Actually the standard deviation of each runs was almost the same (0-2%), so we could
conclude that there were no significant difference.

## Appendix - measurement environments

- Used machine has 755GB memory and 120 cores (Intel(R) Xeon(R) CPU E7-4890).
- RHEL 7.9 is running on the machine
- HEAD pointed the commit 41b98ddb7 when tested.
- Only `--prefix` was specified for when configured.
- Changed parameters from the default were:
```
autovacuum = false
checkpoint_timeout = 1h
shared_buffers = '30GB'
max_wal_size = 20GB
min_wal_size = 10GB
```
Best regards,
Hayato Kuroda
FUJITSU LIMITED

view thread (33+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: RE: Commit Timestamp and LSN Inversion issue
  In-Reply-To: <TYAPR01MB569222C1312E7A6C3C63539DF5582@TYAPR01MB5692.jpnprd01.prod.outlook.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox