thousands of CachedPlan entry per backend

Поиск
Список
Период
Сортировка
От James Pang (chaolpan)
Тема thousands of CachedPlan entry per backend
Дата
Msg-id PH0PR11MB5191F9F3C8CA35737E6F0A99D6499@PH0PR11MB5191.namprd11.prod.outlook.com
обсуждение исходный текст
Ответы Re: thousands of CachedPlan entry per backend  (Laurenz Albe <laurenz.albe@cybertec.at>)
Список pgsql-performance

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.

 

 

 

 

Вложения

В списке pgsql-performance по дате отправления:

Предыдущее
От: Ranier Vilela
Дата:
Сообщение: Re: How to reduce latency with fast short queries in Postgresql 15.3 on a NUMA server
Следующее
От: Laurenz Albe
Дата:
Сообщение: Re: thousands of CachedPlan entry per backend