Re: Logical replication very slow

I think it should. I set it to unlogged on target/slave server only. One
other table which is much smaller and already replicated receives changes
from master.

About settings copy_data to false, nice idea, I'll try that too and compare
speed.

On Mon, Feb 25, 2019 at 9:51 AM Achilleas Mantzios <
achill(at)matrix(dot)gatewaynet(dot)com> wrote:

> On 25/2/19 9:59 π.μ., Boris Sagadin wrote:
>
>
> Doing an initial replica.
>
> postgres 119454 93.5 25.9 34613692 32649656 ? Rs 07:16 32:45 \_
> postgres: 10/main: bgworker: logical replication worker for subscription
> 24783 sync 16500
>
> I've cancelled the sync, set the tables to unlogged type and started it
> again. I think it helped, still much slower than binary, but better, about
> 40MB/s. Will set them back to logged type after initial replica is done.
>
> Will this work at all? Doesn't unlogged mean no changes are written to the
> WAL? What if you just copy by dump and then just add and refresh without copy_data
> ?
>
>
> After the initial replica, there aren't that many updates, so it's OK
> then. But if a need for a new slave arises, waiting a few days for initial
> replica to finish, which in binary replication case is just hours, can be a
> big problem for us.
>
> Boris
>
>
> On Mon, Feb 25, 2019 at 8:08 AM Achilleas Mantzios <
> achill(at)matrix(dot)gatewaynet(dot)com> wrote:
>
>> On 25/2/19 8:52 π.μ., Boris Sagadin wrote:
>>
>> Doing an initial replica and trying to find a bottleneck, Ubuntu 16.04,
>> NVMe disks, PgSQL v10.7, AWS. With binary replication, DB is replicated at
>> good speed, around 500MB/s. Trying LR now for a big table (about 1.4TB with
>> 2 indexes) and the speed is only about 2MB/s.
>>
>> Is logical replication subscriber in "streaming" state or in initial
>> snapshot? What's the behavior after the initial snapshot, when it gets into
>> streaming state?
>>
>> Checked disk util with iostat and only about 20% utilized on master, 15%
>> on target, CPU load on master is low. On slave I can see the "logical
>> replication worker" process is taking about 70% CPU time on a single core,
>> machine has 16 cores.
>>
>> Is there a setting I am missing here? Any ideas appreciated.
>>
>> Boris
>>
>>
>>
>>
>>
>> --
>> Achilleas Mantzios
>> IT DEV Lead
>> IT DEPT
>> Dynacom Tankers Mgmt
>>
>>
>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>
>