Thread: [BUG] postgresql replaying WAL slowly when master drop a large number of relations in one transaction
[BUG] postgresql replaying WAL slowly when master drop a large number of relations in one transaction
From
"158306855"
Date:
HI pg team:
I found a point that could be improved that is postgresql replaying WAL slowly when master drop a large number of tables in one transaction.
1. When postgresql drop some relation, replication replay it in function xact_redo_commit
2. We can see that every relation run function smgrdounlink one by one, this looks ok, code like this:
for (i = 0; i < parsed->nrels; i++)
{
SMgrRelation srel = smgropen(parsed->xnodes[i], InvalidBackendId);
ForkNumber fork;
for (fork = 0; fork <= MAX_FORKNUM; fork++)
XLogDropRelation(parsed->xnodes[i], fork);
smgrdounlink(srel, true);
smgrclose(srel);
}
3 But, the problem drop relation buffers by function DropRelFileNodesAllBuffers, that call by smgrdounlink. Each relation search the entire shared_buffer. This is unnecessary and can be optimized.
DropRelFileNodesAllBuffers code:
for (i = 0; i < NBuffers; i++)
{
....
}
4 When master drop a large number of relations in one transaction, replaying WAL slowly.
Replay of this record on the replication node consumes CPU and execution time much longer than the master node.
5 The function xact_redo_abort also has this problem.
Re: [BUG] postgresql replaying WAL slowly when master drop a largenumber of relations in one transaction
From
Michael Paquier
Date:
On Wed, May 30, 2018 at 10:38:01AM +0800, 158306855 wrote: > 3 But, the problem drop relation buffers by function > DropRelFileNodesAllBuffers, that call by smgrdounlink. Each relation > search the entire shared_buffer. This is unnecessary and can be > optimized. Yes, this is a known problem. Fujii Masao has written a patch which will likely get committed in v12. Please see this thread: https://www.postgresql.org/message-id/CAHGQGwHVQkdfDqtvGVkty+19cQakAydXn1etGND3X0PHbZ3+6w@mail.gmail.com -- Michael
Attachment
Re: [BUG] postgresql replaying WAL slowly when master drop a largenumber of relations in one transaction
From
"158306855"
Date:
oh, nice patch. It solves the problem and is very grateful.
------------------ Original ------------------
From: "Michael Paquier"<michael@paquier.xyz>;
Date: Thu, May 31, 2018 01:20 AM
To: "158306855"<anderson2013@qq.com>;
Cc: "pgsql-bugs"<pgsql-bugs@postgresql.org>;
Subject: Re: [BUG] postgresql replaying WAL slowly when master drop a largenumber of relations in one transaction
> 3 But, the problem drop relation buffers by function
> DropRelFileNodesAllBuffers, that call by smgrdounlink. Each relation
> search the entire shared_buffer. This is unnecessary and can be
> optimized.
Yes, this is a known problem. Fujii Masao has written a patch which
will likely get committed in v12. Please see this thread:
https://www.postgresql.org/message-id/CAHGQGwHVQkdfDqtvGVkty+19cQakAydXn1etGND3X0PHbZ3+6w@mail.gmail.com
--
Michael