There is well known Postgres problem that logical replication subscriber
can not caught-up with publisher just because LR changes are applied by
single worker and at publisher changes are made by
multiple concurrent backends. The problem is not logical replication
specific: physical replication stream is also handled by single
walreceiver. But for physical replication Postgres now implements
prefetch: looking at WAL record blocks it is quite easy to predict which
pages will be required for redo and prefetch them. With logical
replication situation is much more complicated.
My first idea was to implement parallel apply of transactions. But to do
it we need to track dependencies between transactions. Right now
Postgres can apply transactions in parallel, but only if they are
streamed (which is done only for large transactions) and serialize them
by commits. It is possible to enforce parallel apply of short
transactions using `debug_logical_replication_streaming` but then
performance is ~2x times slower than in case of sequential apply by
single worker. By removing serialization by commits, it is possible to
speedup apply 3x times and make subscriber apply changes faster then
producer can produce them even with multiple clients. But it is possible
only if transactions are independent and it can be enforced only by
tracking dependencies which seems to be very non-trivial and invasive.
I still do not completely give up with tracking dependencies approach,
but decided first to try more simple solution - prefetching. It is
already used for physical replication. Certainly in case of physical
replication it is much simpler, because each WAL record contains list of
accessed blocks.
In case of logical replication prefetching can be done either by
prefetching access to replica identity index (usually primary key),
either by executing replication command by some background worker
Certainly first case is much more easy. We just perform index lookup in
prefetch worker and it loads accessed index and heap pages in shared
buffer, so main apply worker does not need to read something from disk.
But it works well only for DELETE and HOT UPDATE operations.
In the second case we normally execute the LR command in background
worker and then abort transaction. Certainly in this case we are doing
the same work twice. But assumption is the same: parallel prefetch
workers should load affected pages, speeding up work of the main apply
worker.
I have implemented some PoC (see attached patch). And get first results
of efficiency of such prefetching.
*** First scenario (update-only).
Publisher:
```
create table t(pk integer primary key, counter integer, filler text
default repeat('x', 1000)) with (fillfactor=10);
insert into t values (generate_series(1,100000), 0);
create publication pub1 for table t;
```
Subscriber:
```
create table t(pk integer primary key, counter integer, filler text
default repeat('x', 1000)) with (fillfactor=10);
create subscription sub1 connection 'port=54321 dbname=postgres'
publication pub1;
```
Then I wait until replication is synced, stop subscriber and do random
dot updates in 10 sessions at publisher:
```
pgbench -T 100 -c 10 -M prepared -n -f update.sql -p 54321 -d postgres
```
where update.sql is:
```
\set pk random(1, 100000)
update t set counter=counter+1 where pk=:pk;
```
Then I start subscriber and measure how much time is needed for it to
caught up.
Results:
no prefetch: 2:00 min
prefetch (replica identity only): 0:55 min
prefetch (all): 1:10 min
This is definitely the best case for replica-identity index only
prefetch (update-only and no other indexes).
How to interpret this results?
Without prefetch applying updates takes about two times more at
subscriber than performing this updates at publisher.
It means that under huge workload subscriber has no chances to caught up.
With prefetching replica identity index, apply time is even smaller than
time needed to perform updates at publisher.
Performing the whole operation and transaction abort certainly adds more
overhead. But still improvement is quite significant.
Please also notice that this results were obtains at the system with
larger amount of RAM (64Gb) and fast SSD.
With data set not fitting in RAM and much slower disks, the difference
is expected to be more significant.
I have tried to simulate it be adding 0.1msec delay to pg_preadv.
When I add artificial 0.1msec `preadv` delay, I got the following results:
no prefetch: 7:40
prefetch (replica identity only): 3:10 min
prefetch (all): 3:09
In this case apply takes much more time than 100 seconds during which
updates are performed at publisher. Prefetch can improve speed about two
times,
but it doesn't allow subcriber to caught-up.
*** Second scenario: inserts with secondary random key.
Publisher:
```
create table t(pk serial primary key, sk integer, counter integer default 0)
insert into t (sk) select random()*10000000 from generate_series(1,10000000)
create index on t(sk)
create publication pub1 for table t
```
Subscriber:
```
create table t(pk integer primary key, sk integer, counter integer)
create index on t(sk)
create subscription sub1 connection 'port=54321 dbname=postgres'
publication pub1
```
workload:
```
pgbench -T 100 -c 10 -M prepared -n -f insert.sql -p 54321 -d postgres
```
where insert.sql:
```
INSERT INTO t (sk) VALUES (random()*10000000);
```
Results (with 0.1msec delay) are the followingL
no prefetch: 10:10 min
prefetch (identity): 8:25 min
prefetch (full): 5:50min
Here as expected prefetching only primary key doesn't provide some big
improvement. But replaying insert command in prefetch worker allows to
speedup apply almost twice.
Please notice that this approach requires minimal changes in Postgres,
because all infrastructure of parallel apply workers is already present
and we can reuse the same apply code (with minimal changes) for
performing prefetch. I only have to introduce extra tuple lock types
(no-lock and try-lock) to minimize overhead and lock conflicts between
prefetch and main apply workers. Still it can not completely prevent
locks conflicts and deadlocks in prefetch workers. Looks like more work
is needed here. Also I set `wal_level=minimal` in prefetch workers to
avoid WAL-logging overhead.
Number of prefetch workers is specified by
`max_parallel_prefetch_workers_per_subscription` GUC. If it is zero
(default) then no prefetching is performed.
Prefetch mode is controlled by `prefetch_replica_identity_only` GUC . By
default it is true which makes prefetch efficient for hot updates,
deletes or inserts in table with just one index (primary key).
Attached please find patch and two shell scripts used to produce this
test results.
Also it may be more convenient to inspect this patch as PR:
https://github.com/knizhnik/postgres/pull/3
I wonder if such LR prefetching approach is considered to be useful?
Or it is better to investigate other ways to improve LR apply speed
(parallel apply)?