Re: [GENERAL] Logical decoding CPU-bound w/ large number of tables

Mathieu Fenniak <mathieu.fenniak@replicon.com> writes:
> I'm attempting to use logical decoding with the streaming replication
> protocol to perform change-data-capture on PostgreSQL 9.5.4.  I'm seeing
> the replication stream "stall" for long periods of time where the walsender
> process will be pinned at 100% CPU utilization, but no data is being sent
> to my client.

> I've performed a CPU sampling with the OSX `sample` tool based upon
> reproduction approach #1:
> https://gist.github.com/mfenniak/366d7ed19b2d804f41180572dc1600d8
> It appears that most of the time is spent in the
> RelfilenodeMapInvalidateCallback and CatalogCacheIdInvalidate cache
> invalidation callbacks, both of which appear to be invalidating caches
> based upon the cache value.

Hmm ... as for RelfilenodeMapInvalidateCallback, the lack of calls to
hash_search() from it in your trace says that it usually isn't doing
anything useful.  All the time is being spent in hash_seq_search,
uselessly iterating over the hashtable.  I'm inclined to think that
we need a smarter data structure there, maybe an independent hashtable
tracking the reverse map from relation OID to filenode map entry.

As for CatalogCacheIdInvalidate, I wonder how many of those cycles
are doing something useful, and how many are being wasted in the outer
loop that just iterates over the cache list.  We could trivially get
rid of that outer search by using syscache.c's array, as in the
attached patch.  It'd be interesting to see if this patch helps your
scenario #1.  (Patch is against HEAD but seems to apply cleanly to 9.5)

Most likely, your scenario #2 is completely stuck on the
RelfilenodeMapInvalidateCallback issue, though it would be good
to get a trace to confirm that.

            regards, tom lane

diff --git a/src/backend/utils/cache/catcache.c b/src/backend/utils/cache/catcache.c
index c27186f..b19044c 100644
*** a/src/backend/utils/cache/catcache.c
--- b/src/backend/utils/cache/catcache.c
*************** CatCacheRemoveCList(CatCache *cache, Cat
*** 422,428 ****


  /*
!  *    CatalogCacheIdInvalidate
   *
   *    Invalidate entries in the specified cache, given a hash value.
   *
--- 422,428 ----


  /*
!  *    CatCacheInvalidate
   *
   *    Invalidate entries in the specified cache, given a hash value.
   *
*************** CatCacheRemoveCList(CatCache *cache, Cat
*** 440,510 ****
   *    This routine is only quasi-public: it should only be used by inval.c.
   */
  void
! CatalogCacheIdInvalidate(int cacheId, uint32 hashValue)
  {
!     slist_iter    cache_iter;

!     CACHE1_elog(DEBUG2, "CatalogCacheIdInvalidate: called");

      /*
!      * inspect caches to find the proper cache
       */
-     slist_foreach(cache_iter, &CacheHdr->ch_caches)
-     {
-         CatCache   *ccp = slist_container(CatCache, cc_next, cache_iter.cur);
-         Index        hashIndex;
-         dlist_mutable_iter iter;

!         if (cacheId != ccp->id)
!             continue;
!
!         /*
!          * We don't bother to check whether the cache has finished
!          * initialization yet; if not, there will be no entries in it so no
!          * problem.
!          */

!         /*
!          * Invalidate *all* CatCLists in this cache; it's too hard to tell
!          * which searches might still be correct, so just zap 'em all.
!          */
!         dlist_foreach_modify(iter, &ccp->cc_lists)
!         {
!             CatCList   *cl = dlist_container(CatCList, cache_elem, iter.cur);

!             if (cl->refcount > 0)
!                 cl->dead = true;
!             else
!                 CatCacheRemoveCList(ccp, cl);
!         }

!         /*
!          * inspect the proper hash bucket for tuple matches
!          */
!         hashIndex = HASH_INDEX(hashValue, ccp->cc_nbuckets);
!         dlist_foreach_modify(iter, &ccp->cc_bucket[hashIndex])
          {
!             CatCTup    *ct = dlist_container(CatCTup, cache_elem, iter.cur);
!
!             if (hashValue == ct->hash_value)
              {
!                 if (ct->refcount > 0 ||
!                     (ct->c_list && ct->c_list->refcount > 0))
!                 {
!                     ct->dead = true;
!                     /* list, if any, was marked dead above */
!                     Assert(ct->c_list == NULL || ct->c_list->dead);
!                 }
!                 else
!                     CatCacheRemoveCTup(ccp, ct);
!                 CACHE1_elog(DEBUG2, "CatalogCacheIdInvalidate: invalidated");
  #ifdef CATCACHE_STATS
!                 ccp->cc_invals++;
  #endif
!                 /* could be multiple matches, so keep looking! */
!             }
          }
-         break;                    /* need only search this one cache */
      }
  }

--- 440,496 ----
   *    This routine is only quasi-public: it should only be used by inval.c.
   */
  void
! CatCacheInvalidate(CatCache *cache, uint32 hashValue)
  {
!     Index        hashIndex;
!     dlist_mutable_iter iter;

!     CACHE1_elog(DEBUG2, "CatCacheInvalidate: called");

      /*
!      * We don't bother to check whether the cache has finished initialization
!      * yet; if not, there will be no entries in it so no problem.
       */

!     /*
!      * Invalidate *all* CatCLists in this cache; it's too hard to tell which
!      * searches might still be correct, so just zap 'em all.
!      */
!     dlist_foreach_modify(iter, &cache->cc_lists)
!     {
!         CatCList   *cl = dlist_container(CatCList, cache_elem, iter.cur);

!         if (cl->refcount > 0)
!             cl->dead = true;
!         else
!             CatCacheRemoveCList(cache, cl);
!     }

!     /*
!      * inspect the proper hash bucket for tuple matches
!      */
!     hashIndex = HASH_INDEX(hashValue, cache->cc_nbuckets);
!     dlist_foreach_modify(iter, &cache->cc_bucket[hashIndex])
!     {
!         CatCTup    *ct = dlist_container(CatCTup, cache_elem, iter.cur);

!         if (hashValue == ct->hash_value)
          {
!             if (ct->refcount > 0 ||
!                 (ct->c_list && ct->c_list->refcount > 0))
              {
!                 ct->dead = true;
!                 /* list, if any, was marked dead above */
!                 Assert(ct->c_list == NULL || ct->c_list->dead);
!             }
!             else
!                 CatCacheRemoveCTup(cache, ct);
!             CACHE1_elog(DEBUG2, "CatCacheInvalidate: invalidated");
  #ifdef CATCACHE_STATS
!             cache->cc_invals++;
  #endif
!             /* could be multiple matches, so keep looking! */
          }
      }
  }

*************** build_dummy_tuple(CatCache *cache, int n
*** 1823,1829 ****
   *    the specified relation, find all catcaches it could be in, compute the
   *    correct hash value for each such catcache, and call the specified
   *    function to record the cache id and hash value in inval.c's lists.
!  *    CatalogCacheIdInvalidate will be called later, if appropriate,
   *    using the recorded information.
   *
   *    For an insert or delete, tuple is the target tuple and newtuple is NULL.
--- 1809,1815 ----
   *    the specified relation, find all catcaches it could be in, compute the
   *    correct hash value for each such catcache, and call the specified
   *    function to record the cache id and hash value in inval.c's lists.
!  *    SysCacheInvalidate will be called later, if appropriate,
   *    using the recorded information.
   *
   *    For an insert or delete, tuple is the target tuple and newtuple is NULL.
diff --git a/src/backend/utils/cache/inval.c b/src/backend/utils/cache/inval.c
index 55e5c8c..347fdde 100644
*** a/src/backend/utils/cache/inval.c
--- b/src/backend/utils/cache/inval.c
*************** LocalExecuteInvalidationMessage(SharedIn
*** 552,558 ****
          {
              InvalidateCatalogSnapshot();

!             CatalogCacheIdInvalidate(msg->cc.id, msg->cc.hashValue);

              CallSyscacheCallbacks(msg->cc.id, msg->cc.hashValue);
          }
--- 552,558 ----
          {
              InvalidateCatalogSnapshot();

!             SysCacheInvalidate(msg->cc.id, msg->cc.hashValue);

              CallSyscacheCallbacks(msg->cc.id, msg->cc.hashValue);
          }
diff --git a/src/backend/utils/cache/syscache.c b/src/backend/utils/cache/syscache.c
index edbc151..066ce72 100644
*** a/src/backend/utils/cache/syscache.c
--- b/src/backend/utils/cache/syscache.c
*************** SearchSysCacheList(int cacheId, int nkey
*** 1339,1344 ****
--- 1339,1365 ----
  }

  /*
+  * SysCacheInvalidate
+  *
+  *    Invalidate entries in the specified cache, given a hash value.
+  *    See CatCacheInvalidate() for more info.
+  *
+  *    This routine is only quasi-public: it should only be used by inval.c.
+  */
+ void
+ SysCacheInvalidate(int cacheId, uint32 hashValue)
+ {
+     if (cacheId < 0 || cacheId >= SysCacheSize)
+         elog(ERROR, "invalid cache ID: %d", cacheId);
+
+     /* if this cache isn't initialized yet, no need to do anything */
+     if (!PointerIsValid(SysCache[cacheId]))
+         return;
+
+     CatCacheInvalidate(SysCache[cacheId], hashValue);
+ }
+
+ /*
   * Certain relations that do not have system caches send snapshot invalidation
   * messages in lieu of catcache messages.  This is for the benefit of
   * GetCatalogSnapshot(), which can then reuse its existing MVCC snapshot
diff --git a/src/include/utils/catcache.h b/src/include/utils/catcache.h
index 299d246..5add424 100644
*** a/src/include/utils/catcache.h
--- b/src/include/utils/catcache.h
*************** extern void ReleaseCatCacheList(CatCList
*** 190,196 ****

  extern void ResetCatalogCaches(void);
  extern void CatalogCacheFlushCatalog(Oid catId);
! extern void CatalogCacheIdInvalidate(int cacheId, uint32 hashValue);
  extern void PrepareToInvalidateCacheTuple(Relation relation,
                                HeapTuple tuple,
                                HeapTuple newtuple,
--- 190,196 ----

  extern void ResetCatalogCaches(void);
  extern void CatalogCacheFlushCatalog(Oid catId);
! extern void CatCacheInvalidate(CatCache *cache, uint32 hashValue);
  extern void PrepareToInvalidateCacheTuple(Relation relation,
                                HeapTuple tuple,
                                HeapTuple newtuple,
diff --git a/src/include/utils/syscache.h b/src/include/utils/syscache.h
index 36805eb..73991dd 100644
*** a/src/include/utils/syscache.h
--- b/src/include/utils/syscache.h
*************** struct catclist;
*** 140,145 ****
--- 140,147 ----
  extern struct catclist *SearchSysCacheList(int cacheId, int nkeys,
                     Datum key1, Datum key2, Datum key3, Datum key4);

+ extern void SysCacheInvalidate(int cacheId, uint32 hashValue);
+
  extern bool RelationInvalidatesSnapshotsOnly(Oid relid);
  extern bool RelationHasSysCache(Oid relid);
  extern bool RelationSupportsSysCache(Oid relid);

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general