Re: Add 64-bit XIDs into PostgreSQL 15 - Mailing list pgsql-hackers

From Aleksander Alekseev
Subject Re: Add 64-bit XIDs into PostgreSQL 15
Date
Msg-id CAJ7c6TMU+PGwfaWkz-ddw2zL=kQ9GqWLk0B6tKoN1Rwr=hvhDQ@mail.gmail.com
Whole thread Raw
In response to Re: Add 64-bit XIDs into PostgreSQL 15  (Aleksander Alekseev <aleksander@timescale.com>)
Responses Re: Add 64-bit XIDs into PostgreSQL 15
List pgsql-hackers
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



pgsql-hackers by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: meson oddities
Next
From: vignesh C
Date:
Subject: Re: Skipping schema changes in publication