BUG #14365: drop big schema will hang the standby's apply

From: digoal(at)126(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14365: drop big schema will hang the standby's apply
Date: 2016-10-11 11:30:26
Message-ID: 20161011113026.1412.57124@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14365
Logged by: Zhou Digoal
Email address: digoal(at)126(dot)com
PostgreSQL version: 9.4.9
Operating system: CentOS 6.x x64
Description:

When i drop a big schema in master, the standby apply will hang (very very
slow).

master :

connect to template1
```
create table test(
c0 int check(c0>0),
c1 int check(c1>0),
c2 int check(c2>0),
c3 int check(c3>0),
c4 int check(c4>0),
c5 int check(c5>0),
c6 int check(c6>0),
c7 int check(c7>0),
c8 int check(c8>0),
c9 int check(c9>0),
c10 int check(c10>0),
c11 int check(c11>0),
c12 int check(c12>0),
c13 int check(c13>0),
c14 int check(c14>0),
c15 int check(c15>0),
c16 int check(c16>0),
c17 int check(c17>0),
c18 int check(c18>0),
c19 int check(c19>0),
c20 int check(c20>0),
c21 int check(c21>0),
c22 int check(c22>0),
c23 int check(c23>0),
c24 int check(c24>0),
c25 int check(c25>0),
c26 int check(c26>0),
c27 int check(c27>0),
c28 int check(c28>0),
c29 int check(c29>0),
c30 int check(c30>0),
c31 int check(c31>0),
c32 int check(c32>0),
c33 int check(c33>0),
c34 int check(c34>0),
c35 int check(c35>0),
c36 int check(c36>0),
c37 int check(c37>0),
c38 int check(c38>0),
c39 int check(c39>0),
c40 int check(c40>0),
c41 int check(c41>0),
c42 int check(c42>0),
c43 int check(c43>0),
c44 int check(c44>0),
c45 int check(c45>0),
c46 int check(c46>0),
c47 int check(c47>0),
c48 int check(c48>0),
c49 int check(c49>0),
c50 int check(c50>0),
c51 int check(c51>0),
c52 int check(c52>0),
c53 int check(c53>0),
c54 int check(c54>0),
c55 int check(c55>0),
c56 int check(c56>0),
c57 int check(c57>0),
c58 int check(c58>0),
c59 int check(c59>0),
c60 int check(c60>0),
c61 int check(c61>0),
c62 int check(c62>0),
c63 int check(c63>0),
c64 int check(c64>0),
c65 int check(c65>0),
c66 int check(c66>0),
c67 int check(c67>0),
c68 int check(c68>0),
c69 int check(c69>0),
c70 int check(c70>0),
c71 int check(c71>0),
c72 int check(c72>0),
c73 int check(c73>0),
c74 int check(c74>0),
c75 int check(c75>0),
c76 int check(c76>0),
c77 int check(c77>0),
c78 int check(c78>0),
c79 int check(c79>0),
c80 int check(c80>0),
c81 int check(c81>0),
c82 int check(c82>0),
c83 int check(c83>0),
c84 int check(c84>0),
c85 int check(c85>0),
c86 int check(c86>0),
c87 int check(c87>0),
c88 int check(c88>0),
c89 int check(c89>0),
c90 int check(c90>0),
c91 int check(c91>0),
c92 int check(c92>0),
c93 int check(c93>0),
c94 int check(c94>0),
c95 int check(c95>0),
c96 int check(c96>0),
c97 int check(c97>0),
c98 int check(c98>0),
c99 int check(c99>0)
);

do language plpgsql $$
declare
i int ;
begin
for i in 1..5000 loop
execute 'create table test'||i||' (like test including all)';
end loop;
end;
$$;
```

```
create database test with template1;
\c test
drop schema public cascade;
```

and see the delay with this query:
```
select
pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(),sent_location)),
pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(),write_location)),
pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(),flush_location)),
pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(),replay_location)),

* from pg_stat_replication ;
```

apply delay will big (few GB);

and i see the standby's recovery process stack, some very slow comman push
out
```
close(x)
close(xx)
close(xxx)
close(xxxx)
```
x is fd number.

why?

it's a bug? or performance problem?

best regards.

Browse pgsql-bugs by date

  From Date Subject
Next Message Greg Stark 2016-10-11 11:41:44 Re: parallel plan in insert query
Previous Message Grigory Smolkin 2016-10-11 10:01:15 parallel plan in insert query