Re: Improve WALRead() to suck data directly from WAL buffers when possible

On Fri, Jan 27, 2023 at 12:16 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> I'd like to confirm whether there is any performance regression caused
> by this patch in some cases, especially when not using DIO.

Thanks. I ran some insert tests with primary and 1 async standby.
Please see the numbers below and attached graphs. I've not noticed a
regression as such, in fact, with patch, there's a slight improvement.
Note that there's no WAL DIO involved here.

test-case 1:
clients    HEAD    PATCHED
1    139    156
2    624    599
4    3113    3410
8    6194    6433
16    11255    11722
32    22455    21658
64    46072    47103
128    80255    85970
256    110067    111488
512    114043    118094
768    109588    111892
1024    106144    109361
2048    85808    90745
4096    55911    53755

test-case 2:
clients    HEAD    PATCHED
1    177    128
2    186    425
4    2114    2946
8    5835    5840
16    10654    11199
32    14071    13959
64    18092    17519
128    27298    28274
256    24600    24843
512    17139    19450
768    16778    20473
1024    18294    20209
2048    12898    13920
4096    6399    6815

test-case 3:
clients    HEAD    PATCHED
1    148    191
2    302    317
4    3415    3243
8    5864    6193
16    9573    10267
32    14069    15819
64    17424    18453
128    24493    29192
256    33180    38250
512    35568    36551
768    29731    30317
1024    32291    32124
2048    27964    28933
4096    13702    15034

[1]
cat << EOF >> data/postgresql.conf
shared_buffers = '8GB'
wal_buffers = '1GB'
max_wal_size = '16GB'
max_connections = '5000'
archive_mode = 'on'
archive_command='cp %p /home/ubuntu/archived_wal/%f'
EOF

test-case 1:
./pgbench -i -s 300 -d postgres
./psql -d postgres -c "ALTER TABLE pgbench_accounts DROP CONSTRAINT
pgbench_accounts_pkey;"
cat << EOF >> insert.sql
\set aid random(1, 10 * :scale)
\set delta random(1, 100000 * :scale)
INSERT INTO pgbench_accounts (aid, bid, abalance) VALUES (:aid, :aid, :delta);
EOF
for c in 1 2 4 8 16 32 64 128 256 512 768 1024 2048 4096; do echo -n
"$c ";./pgbench -n -M prepared -U ubuntu postgres -f insert.sql -c$c
-j$c -T5 2>&1|grep '^tps'|awk '{print $3}';done

test-case 2:
./pgbench --initialize --scale=300 postgres
for c in 1 2 4 8 16 32 64 128 256 512 768 1024 2048 4096; do echo -n
"$c ";./pgbench -n -M prepared -U ubuntu postgres -b tpcb-like -c$c
-j$c -T5 2>&1|grep '^tps'|awk '{print $3}';done

test-case 3:
./pgbench --initialize --scale=300 postgres
for c in 1 2 4 8 16 32 64 128 256 512 768 1024 2048 4096; do echo -n
"$c ";./pgbench -n -M prepared -U ubuntu postgres -b simple-update
-c$c -j$c -T5 2>&1|grep '^tps'|awk '{print $3}';done

--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com