Re: Parallel Apply

Hi Hou,

Thanks for looking at this, and good to hear the sleep was already on
your list. Answers below; the script is attached.

> what you mean by "updating the hot row" or the hot-counter
> pattern (it sounds like a dependency, but I may be misreading it). I'd
> also be curious about whether the table has an index

The attached repro_parallel_apply.sh takes PGBIN (or PATH), starts a
publisher and a subscriber in a scratch directory, loads the schema and
runs the drains, printing txn/s per drain and a median/speedup summary.
Default run: ./repro_parallel_apply.sh 3

Schema is identical on both nodes; every table has a primary key and no
other index, and replica identity is the default (the PK). The tables
are counters(id int PK, version bigint) with 10,000 rows, rows(id bigint
PK, version bigint) with 4,000,000 rows, events(id bigserial PK, c int)
which grows about 15k rows per drain, and marker(id int PK).

"Hot counter" (hot<H>) is: each transaction either updates one of H rows
in counters (version = version + 1 WHERE id = random(1, H)) or inserts
one row into events, 50/50. You read it right: with small H those
UPDATEs are real dependencies. That is why I also ran "indep": the UPDATE
goes to a row of "rows" that no other transaction ever touches, plus the
same INSERTs. Nothing is shared there, and it regresses the same way: 4
workers 1,392 txn/s vs 1,432 for H=10, and the sweep over H = 1..10,000
was flat (1,506..1,396). So it is not driven by row dependencies but by
transaction size: the waits that pay the sleep are the commit-order ones,
which every transaction has.

> roughly how large the subscriber table is, since both can affect
> replay. Similarly, the configuration of the test PostgreSQL instance
> can make a real difference, so if you could share that too, it helps.

GUCs on both nodes: shared_buffers = 256MB, fsync = off, wal_level =
logical, max_logical_replication_workers = 12, max_worker_processes =
24, max_wal_size = 4GB; everything else default (the subscription keeps
its default synchronous_commit = off). Backlog from 8 pgbench clients,
VACUUM + CHECKPOINT on both nodes before each drain.

Machine: AMD Ryzen AI 9 HX 370 laptop (12 cores / 24 threads: 4 Zen 5
plus 8 Zen 5c), 30 GB RAM, -O2 build. Both nodes and pgbench on the same
host, pinned to the 8 hardware threads of the Zen 5 cores, data
directories on tmpfs, and I discarded runs where those CPUs were not
idle before and after.

> since our own earlier tests (without the pg_usleep()) didn't show a
> regression, we'd like to understand what differs in your environment.

I think my numbers agree with yours. I reran today with a build whose
only change is pg_usleep(1000L) -> pg_usleep(0L), without the logging I
used last time, and with 300k-transaction drains. Speedup with 4 workers
vs serial, first with v27's sleep and then with the sleep removed (K is
disjoint rows updated per transaction):

K=1: 0.0096x then 0.87x
K=10: 0.15x then 1.15x
K=100: 0.50x then 2.13x
K=300: 1.22x then 1.32x (noisy)
K=1000: 1.55x then 1.67x
H=10: 0.0059x then 0.74x

(K=10..300 with the sleep are from the earlier run.) Without the sleep
the crossover drops from 100-300 rows per transaction to between 1 and
10, so only single-row transactions stay below serial. Out of curiosity,
what transaction sizes did your tests use?

This also corrects my previous mail: I wrote that ~1.7x remained with no
sleep at all. That came from the instrumented build (one LOG line per
wait, which only the parallel side pays) and from 0.2 s drains, where a
start-up jitter of ~0.3 s shows up on both sides. The clean residual is
~1.15x (K=1) to ~1.35x (H=10), and I can't tell from this machine how
much of that is CPU contention: publisher, walsender, leader and four
spinning workers share 8 hardware threads.

> For performance test, we normally prefer to test on a more stable
> machine, which could be a more powerful one, so this is worth noting.

The sleep part should not depend on the machine: each wait pays at least
1 ms after its predecessor commits, and a 23.3 s drain had 22,405 of
them (22.4 s, 96% of the wall time), so a faster CPU only shortens the
other 4%. The crossover compares runs on the same machine. The part that
could change with more cores is the residual above; I can rerun the same
script on a larger instance if that would help.

I can also send the raw results and the instrumentation diff.

Regards,
Manu