Обсуждение: thousands of CachedPlan entry per backend

PG V14.8-1 , client using Postgresql JDBC driver we found 40MB process memory per backend, from Operating system and memorycontext dump “Grand total:”, both mached. But from details, we found almost of entry belong to  “CacheMemoryContext”,  from this line  CacheMemoryContext: 8737352 total in 42 blocks; 1021944 free (215 chunks); 7715408 used,  but there are thousands of lines of it’s child, the sum of blocks much more than “8737352” total in 42 blocks

    CacheMemoryContext: 8737352 total in 42 blocks; 1021944 free (215 chunks); 7715408 used

        CachedPlan: 4096 total in 3 blocks; 888 free (0 chunks); 3208 used: xxxxxxx

        CachedPlanSource: 2048 total in 2 blocks; 440 free (0 chunks); 1608 used: xxxxxxx

             unnamed prepared statement: 8192 total in 1 blocks; 464 free (0 chunks); 7728 used

       CachedPlan: 66560 total in 7 blocks; 15336 free (0 chunks); 51224 used: xxxxxxx

       CachedPlan: 8192 total in 4 blocks; 2456 free (0 chunks); 5736 used: xxxxxxx

      CachedPlan: 33792 total in 6 blocks; 14344 free (1 chunks); 19448 used: xxxxxxx

      …

      SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used

       CachedPlanSource: 4096 total in 3 blocks; 1152 free (0 chunks); 2944 used: xxxxxxx

         CachedPlanQuery: 4096 total in 3 blocks; 848 free (0 chunks); 3248 used

    SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used

    CachedPlanSource: 4096 total in 3 blocks; 1472 free (0 chunks); 2624 used: xxxxxxx

        CachedPlanQuery: 4096 total in 3 blocks; 1464 free (0 chunks); 2632 used

    SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used

    index info: 2048 total in 2 blocks; 824 free (0 chunks); 1224 used: xxxxxxx

    index info: 2048 total in 2 blocks; 824 free (0 chunks); 1224 used: xxxxxxx

    index info: 2048 total in 2 blocks; 528 free (1 chunks); 1520 used: xxxxxxx

    index info: 2048 total in 2 blocks; 528 free (1 chunks); 1520 used: xxxxxxx

    index info: 2048 total in 2 blocks; 824 free (0 chunks); 1224 used: xxxxxxx

    index info: 2048 total in 2 blocks; 448 free (1 chunks); 1600 used: xxxxxxx

    index info: 3072 total in 2 blocks; 696 free (1 chunks); 2376 used: xxxxxxx

    index info: 2048 total in 2 blocks; 824 free (0 chunks); 1224 used: xxxxxxx

    index info: 2048 total in 2 blocks; 656 free (2 chunks); 1392 used: xxxxxxx

    index info: 3072 total in 2 blocks; 1160 free (2 chunks); 1912 used: xxxxxxx

    index info: 2048 total in 2 blocks; 904 free (1 chunks); 1144 used: xxxxxxx

    index info: 2048 total in 2 blocks; 904 free (1 chunks); 1144 used: xxxxxxx

    index info: 2048 total in 2 blocks; 904 free (1 chunks); 1144 used: xxxxxxx

  WAL record construction: 49768 total in 2 blocks; 6360 free (0 chunks); 43408 used

  PrivateRefCount: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used

  MdSmgr: 32768 total in 3 blocks; 10104 free (7 chunks); 22664 used

  LOCALLOCK hash: 65536 total in 4 blocks; 18704 free (13 chunks); 46832 used

  Timezones: 104120 total in 2 blocks; 2616 free (0 chunks); 101504 used

  ErrorContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used

Grand total: 34558032 bytes in 8798 blocks; 9206536 free (2484 chunks); 25351496 used

Our application use Postgresql JDBC driver with default parameters(maxprepared statement 256), there are many triggers, functions in this database, and a few functions run sql by an extension pg_background.  We have thousands of connections and have big concern why have thousands of entrys of cached SQL ?  that will consume huge memory ,  anyway to limit the cached plan entry to save memory consumption?  Or it looks like an abnormal behavior or bug to see so many cached plan lines.

  Attached please see details, the detail of SQL got masked sensitive information, this backend has huge lines so using MemoryContextStatsDetail(TopMemoryContext) instead to dump all lines.

    Yes, too many cached metadata and we are thinking of a workaround to disconnect the sessions timely.

In addition, based on the dumped memory context, I have questions

   1) we found thousands of cached plan , since JDBC driver only allow max 256 cached prepared statements, how backend cache so many sql plans. If we have one function,  when application call that function will make backend to cache  every SQL statement plan in that function too?   and for table triggers, have similar caching behavior ?

  2) from  this line, we saw total 42 blocks ,215 chunks      CacheMemoryContext: 8737352 total in 42 blocks; 1021944 free (215 chunks); 7715408 used,

      But from sum of it’s child level entrys,  total sum(child lines) block ,trunks show much more than “CacheMemoryContext,  is expected to see that?

Thanks,

James

these lines about "SPI Plan" are these PL/PGSQL functions related through SPI_prepare plan entry, right?

SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used

    CachedPlan: 2048 total in 2 blocks; 304 free (1 chunks); 1744 used: xxxxxxx

    CachedPlanSource: 2048 total in 2 blocks; 200 free (0 chunks); 1848 used: xxxxxxx

      CachedPlanQuery: 2048 total in 2 blocks; 704 free (0 chunks); 1344 used