Re: Slow synchronous logical replication

On 11.10.2017 10:07, Craig Ringer wrote:
> On 9 October 2017 at 15:37, Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>> Thank you for explanations.
>>
>> On 08.10.2017 16:00, Craig Ringer wrote:
>>> I think it'd be helpful if you provided reproduction instructions,
>>> test programs, etc, making it very clear when things are / aren't
>>> related to your changes.
>>
>> It will be not so easy to provide some reproducing scenario, because
>> actually it involves many components (postgres_fdw, pg_pasthman,
>> pg_shardman, LR,...)
> So simplify it to a test case that doesn't.
The simplest reproducing scenario is the following:
1. Start two Posgtgres instances: synchronous_commit=on, fsync=off
2. Initialize pgbench database at both instances: pgbench -i
3. Create publication for pgbench_accounts table at one node
4. Create correspondent subscription at another node with
copy_data=false parameter
5. Add subscription to synchronous_standby_names at first node.
6. Start pgbench -c 8 -N -T 100 -P 1 at first node. At my systems
results are the following:
standalone postgres: 8600 TPS
asynchronous replication: 6600 TPS
synchronous replication: 5600 TPS
Quite good results.
7. Create some dummy table and perform bulk insert in it:
create table dummy(x integer primary key);
insert into dummy values (generate_series(1,10000000));

pgbench almost stuck: until end of insert performance drops almost
to zero.

The reason of such behavior is obvious: wal sender has to decode huge
transaction generate by insert although it has no relation to this
publication.
Filtering of insert records of this transaction is done only inside
output plug-in.
Unfortunately it is not quite clear how to make wal-sender smarter and
let him skip transaction not affecting its publication.
Once of the possible solutions is to let backend inform wal-sender about
smallest LSN it should wait for (backend knows which table is affected
by current operation,
so which publications are interested in this operation and so can point
wal -sender to the proper LSN without decoding huge part of WAL.
But it seems to be not so easy to implement.

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company