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 (1000);
create table pgbench_accounts_copy_a1 partition of pgbench_accounts_copy for
values from (1000) to (2000);
create table pgbench_accounts_copy_a2 partition of pgbench_accounts_copy for
values from (2000) to (3000);
create table pgbench_accounts_copy_a3 partition of pgbench_accounts_copy for
values from (3000) to (4000);
create table pgbench_accounts_copy_a4 partition of pgbench_accounts_copy for
values from (4000) to (5000);
create table pgbench_accounts_copy_a5 partition of pgbench_accounts_copy for
values from (5000) to (6000);
create table pgbench_accounts_copy_a6 partition of pgbench_accounts_copy for
values from (6000) to (7000);
create table pgbench_accounts_copy_a7 partition of pgbench_accounts_copy for
values from (7000) to (8000);
create table pgbench_accounts_copy_a8 partition of pgbench_accounts_copy for
values from (8000) to (9000);
create table pgbench_accounts_copy_a9 partition of pgbench_accounts_copy for
values from (9000) to (1);
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 (1000);
create table pgbench_accounts_copy_a1 partition of pgbench_accounts_copy for
values from (1000) to (2000);
create table pgbench_accounts_copy_a2 partition of pgbench_accounts_copy for
values from (2000) to (3000);
create table pgbench_accounts_copy_a3 partition of pgbench_accounts_copy for
values from (3000) to (4000);
create table pgbench_accounts_copy_a4 partition of pgbench_accounts_copy for
values from (4000) to (5000);
create table pgbench_accounts_copy_a5 partition of pgbench_accounts_copy for
values from (5000) to (6000);
create table pgbench_accounts_copy_a6 partition of pgbench_accounts_copy for
values from (6000) to (7000);
create table pgbench_accounts_copy_a7 partition of pgbench_accounts_copy for
values from (7000) to (8000);
create table pgbench_accounts_copy_a8 partition of pgbench_accounts_copy for
values from (8000) to (9000);
create table pgbench_accounts_copy_a9 partition of pgbench_accounts_copy for
values from (9000) to (1);
create subscription sub1 CONNECTION 'host= port= user=postgres
dbname=postgres' publication my_publication;
On pg1
insert into pgbench_accounts_copy select * from pgbench_accounts where aid0
and aid<1;
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=_ejmoo...@mail.gmail.com
And I believe patch #83fd453 introduce this problem.
Thanks,
Mark Zhao
0001-add-missed-RelationClose-after-RelationIdGetRelation.patch
Description: Binary data
