Thread: [Bug Fix] Logical replication on partition table is very slow and CPU is 99%

Logical replication on partition table is very slow and CPU is 99%.

To reproduce this problem,

pg1 for publish

pgbench -i -s 1000

create table pgbench_accounts_copy(aid integer, bid integer, abalance integer, filler character(84)) partition by range (aid);

create table pgbench_accounts_copy_a0 partition of pgbench_accounts_copy for values from (0) to (10000000);

create table pgbench_accounts_copy_a1 partition of pgbench_accounts_copy for values from (10000000) to (20000000);

create table pgbench_accounts_copy_a2 partition of pgbench_accounts_copy for values from (20000000) to (30000000);

create table pgbench_accounts_copy_a3 partition of pgbench_accounts_copy for values from (30000000) to (40000000);

create table pgbench_accounts_copy_a4 partition of pgbench_accounts_copy for values from (40000000) to (50000000);

create table pgbench_accounts_copy_a5 partition of pgbench_accounts_copy for values from (50000000) to (60000000);

create table pgbench_accounts_copy_a6 partition of pgbench_accounts_copy for values from (60000000) to (70000000);

create table pgbench_accounts_copy_a7 partition of pgbench_accounts_copy for values from (70000000) to (80000000);

create table pgbench_accounts_copy_a8 partition of pgbench_accounts_copy for values from (80000000) to (90000000);

create table pgbench_accounts_copy_a9 partition of pgbench_accounts_copy for values from (90000000) to (100000000);

create publication my_publication for table pgbench_accounts_copy with (publish_via_partition_root = true);

pg2 for subscribe

create table pgbench_accounts_copy(aid integer, bid integer, abalance integer, filler character(84)) partition by range (aid);

create table pgbench_accounts_copy_a0 partition of pgbench_accounts_copy for values from (0) to (10000000);

create table pgbench_accounts_copy_a1 partition of pgbench_accounts_copy for values from (10000000) to (20000000);

create table pgbench_accounts_copy_a2 partition of pgbench_accounts_copy for values from (20000000) to (30000000);

create table pgbench_accounts_copy_a3 partition of pgbench_accounts_copy for values from (30000000) to (40000000);

create table pgbench_accounts_copy_a4 partition of pgbench_accounts_copy for values from (40000000) to (50000000);

create table pgbench_accounts_copy_a5 partition of pgbench_accounts_copy for values from (50000000) to (60000000);

create table pgbench_accounts_copy_a6 partition of pgbench_accounts_copy for values from (60000000) to (70000000);

create table pgbench_accounts_copy_a7 partition of pgbench_accounts_copy for values from (70000000) to (80000000);

create table pgbench_accounts_copy_a8 partition of pgbench_accounts_copy for values from (80000000) to (90000000);

create table pgbench_accounts_copy_a9 partition of pgbench_accounts_copy for values from (90000000) to (100000000);

create subscription sub1 CONNECTION 'host=xxxx port=xxxx user=postgres dbname=postgres' publication my_publication;

On pg1

insert into pgbench_accounts_copy select * from pgbench_accounts where aid>0 and aid<100000000;

The replication will be very slow, it lasts for 5 days in my environment and will continue. What's more, the CPU is 99% but actually do nothing. 

In fact, there is a hidden bug when replication on partition table. When we publish partition tables via root table, the reference of root table on current owner is added, but the decrement of reference is missed. When the reference is large enough, It takes so much time and CPU to re-hash and resolve hash collision. 

This patch adds the missed decrement to resolve the problem.

Previous discussion is here: https://www.postgresql.org/message-id/flat/CA+HiwqH=Y85vRK3mOdjEkqFK+E=ST=eQiHdpj43L=_eJMOOznQ@mail.gmail.com
And I believe patch #83fd453 introduce this problem.

Thanks,

Mark Zhao

On Mon, Jan 4, 2021 at 7:07 PM 赵锐 <875941708@qq.com> wrote:

>
>
> This patch adds the missed decrement to resolve the problem.
>

>
> Previous discussion is here:
https://www.postgresql.org/message-id/flat/CA+HiwqH=Y85vRK3mOdjEkqFK+E=ST=eQiHdpj43L=_eJMOOznQ@mail.gmail.com
> And I believe patch #83fd453 introduce this problem.
>


Thanks for the report and fix. The fix looks straight forward and
correct to me. Please add the patch to the next CF so that it's not
forgotten. But since this is a bug fix it need not wait for CF.

Also please report the results of your experiment with the patch
applied so as to know this bug's contribution to the slow down.

--
Best Wishes,
Ashutosh Bapat

On Mon, Jan 11, 2021 at 6:31 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
>
> On Mon, Jan 4, 2021 at 7:07 PM 赵锐 <875941708@qq.com> wrote:
>
> >
> >
> > This patch adds the missed decrement to resolve the problem.
> >
>
> >
> > Previous discussion is here:
https://www.postgresql.org/message-id/flat/CA+HiwqH=Y85vRK3mOdjEkqFK+E=ST=eQiHdpj43L=_eJMOOznQ@mail.gmail.com
> > And I believe patch #83fd453 introduce this problem.
> >
>
>
> Thanks for the report and fix. The fix looks straight forward and
> correct to me. Please add the patch to the next CF so that it's not
> forgotten. But since this is a bug fix it need not wait for CF.
>

I have pushed the fix for this. This was also being discussed in the
nearby thread [1].

[1] - https://www.postgresql.org/message-id/tencent_41FEA657C206F19AB4F406BE9252A0F69C06%40qq.com

--
With Regards,
Amit Kapila.