Re: Logical replication very slow

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.

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
>
>