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