Re: Unresolved repliaction hang and stop problem.

At Wed, 16 Jun 2021 18:28:28 -0400, Alvaro Herrera <alvherre@alvh.no-ip.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.

/*
 * Find the largest transaction (toplevel or subxact) to evict (spill to disk).
 *
 * XXX With many subtransactions this might be quite slow, because we'll have
 * to walk through all of them. There are some options how we could improve
 * that: (a) maintain some secondary structure with transactions sorted by
 * amount of changes, (b) not looking for the entirely largest transaction,
 * but e.g. for transaction using at least some fraction of the memory limit,
 * and (c) evicting multiple transactions at once, e.g. to free a given portion
 * of the memory limit (e.g. 50%).
 */
static ReorderBufferTXN *
ReorderBufferLargestTXN(ReorderBuffer *rb)

This looks like a candidate of the culprit. The perf line for
"ReorderBufferSerializeTXN supports this hypothesis.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center