Horton

Re: Add 64-bit XIDs into PostgreSQL 15

Aleksander Alekseev <aleksander@timescale.com>

View thread
From: Aleksander Alekseev <aleksander@timescale.com>
To: pgsql-hackers@lists.postgresql.org
Cc: Maxim Orlov <orlovmg@gmail.com>, Dilip Kumar <dilipbalaut@gmail.com>, Thom Brown <thom@linux.com>, Zhang Mingli <zmlpostgres@gmail.com>, Michael Paquier <michael@paquier.xyz>, Justin Pryzby <pryzby@telsasoft.com>, Pavel Borisov <pashkin.elfe@gmail.com>, Stephen Frost <sfrost@snowman.net>, Alexander Korotkov <aekorotkov@gmail.com>, Andres Freund <andres@anarazel.de>, Ilya Anfimov <ilan@tzirechnoy.com>
Date: 2022-11-16T09:57:27Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add SLRU tests for 64-bit page case

  2. Make use FullTransactionId in 2PC filenames

  3. Use larger segment file names for pg_notify

  4. Index SLRUs by 64-bit integers rather than by 32-bit integers

Hi hackers,

> Dilip Kumar asked a good question in the thread about the 0001..0003
> subset [1]. I would like to duplicate it here to make sure it was not
> missed by mistake:
>
> """
> Have we measured the WAL overhead because of this patch set? maybe
> these particular patches will not impact but IIUC this is ground work
> for making xid 64 bit. So each XLOG record size will increase at
> least by 4 bytes because the XLogRecord contains the xid.
> """
>
> Do we have an estimate on this?

I decided to simulate one completely synthetic and non-representative
scenario when we write multiple small WAL records.

Here is what I did:

$ psql -c 'CREATE TABLE phonebook(
  "id" SERIAL PRIMARY KEY NOT NULL,
  "name" TEXT NOT NULL,
  "phone" INT NOT NULL);'

$ echo 'INSERT INTO phonebook (name, phone) VALUES (random(),
random());' > t.sql

$ pgbench -j 8 -c 8 -f t.sql -T 60 eax

== 32-bit XIDs ==

Branch: https://github.com/afiskon/postgres/tree/64bit_xids_v50_14Nov_without_patch

pgbench output:

```
number of transactions actually processed: 68650
number of failed transactions: 0 (0.000%)
latency average = 6.993 ms
initial connection time = 5.415 ms
tps = 1144.074340 (without initial connection time)
```

$ ls -lah /home/eax/projects/pginstall/data-master/pg_wal
...
-rw-------  1 eax eax  16M Nov 16 12:48 000000010000000000000002
-rw-------  1 eax eax  16M Nov 16 12:47 000000010000000000000003

$ pg_waldump -p ~/projects/pginstall/data-master/pg_wal
000000010000000000000002 000000010000000000000003 | perl -e 'while(<>)
{ $_ =~ m#len \(rec/tot\):\s*(\d+)/\s*(\d+),#; $rec += $1; $tot += $2;
$count++; } $rec /= $count; $tot /= $count; print "rec: $rec, tot:
$tot\n";'

pg_waldump: error: error in WAL record at 0/28A4118: invalid record
length at 0/28A4190: wanted 24, got 0
rec: 65.8201835569952, tot: 67.3479022057689

== 64-bit XIDs ==

Branch: https://github.com/afiskon/postgres/tree/64bit_xids_v50_14Nov

pgbench output:

```
number of transactions actually processed: 68744
number of failed transactions: 0 (0.000%)
latency average = 6.983 ms
initial connection time = 5.334 ms
tps = 1145.664765 (without initial connection time)
```

$ ls -lah /home/eax/projects/pginstall/data-master/pg_wal
...
-rw-------  1 eax eax  16M Nov 16 12:32 000000010000000000000002
-rw-------  1 eax eax  16M Nov 16 12:31 000000010000000000000003

$ pg_waldump -p ~/projects/pginstall/data-master/pg_wal
000000010000000000000002 000000010000000000000003 | perl -e 'while(<>)
{ $_ =~ m#len \(rec/tot\):\s*(\d+)/\s*(\d+),#; $rec += $1; $tot += $2;
$count++; } $rec /= $count; $tot /= $count; print "rec: $rec, tot:
$tot\n";'

pg_waldump: error: error in WAL record at 0/29F4778: invalid record
length at 0/29F4810: wanted 26, got 0
rec: 69.1783950928736, tot: 70.6413934278527

So under this load with 64-bit XIDs we see ~5% penalty in terms of the
WAL size. This seems to be expected considering the fact that
sizeof(XLogRecord) became 4 bytes larger and the average record size
was about 66 bytes.

-- 
Best regards,
Aleksander Alekseev