Re: Unresolved repliaction hang and stop problem.

On Thu, Jun 17, 2021 at 7:28 AM Kyotaro Horiguchi
<horikyota(dot)ntt(at)gmail(dot)com> wrote:
>
> At Wed, 16 Jun 2021 18:28:28 -0400, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote in
> > On 2021-Jun-16, Ha Ka wrote:
> > # Children Self Command Shared Object Symbol
> > # ........ ........ ........ ............. ..................................
> > #
> > 100.00% 0.00% postgres postgres [.] exec_replication_command
> > |
> > ---exec_replication_command
> > WalSndLoop
> > XLogSendLogical
> > LogicalDecodingProcessRecord
> > |
> > --99.51%--ReorderBufferQueueChange
> > |
> > |--96.06%--hash_seq_search
> > |
> > |--1.78%--ReorderBufferSerializeTXN
> > | |
> > | --0.52%--errstart
> > |
> > --0.76%--deregister_seq_scan
> >
> > What this tells me is that ReorderBufferQueueChange is spending a lot of
> > time doing hash_seq_search, which probably is the one in
> > ReorderBufferTXNByXid.
>
> I don't see a call to hash_*seq*_search there. Instead, I see one in
> ReorderBufferCheckMemoryLimit().
>
> If added an elog line in hash_seq_search that is visited only when it
> is called under ReorderBufferQueueChange, then set
> logical_decoding_work_mem to 64kB.
>
> Running the following query calls hash_seq_search (relatively) frequently.
>
> pub=# create table t1 (a int primary key);
> pub=# create publication p1 for table t1;
> sub=# create table t1 (a int primary key);
> sub=# create subscription s1 connection 'host=/tmp port=5432' publication p1;
> pub=# insert into t1 (select a from generate_series(0, 9999) a);
>
> The insert above makes 20 calls to ReorderBufferLargestTXN() (via
> ReorderBufferCheckmemoryLimit()), which loops over hash_seq_search.
>

If there are large transactions then someone can probably set
logical_decoding_work_mem to a higher value.

--
With Regards,
Amit Kapila.