From: Kyotaro HORIGUCHI [mailto:horiguchi.kyotaro@lab.ntt.co.jp]
> - If you find the process too much "bloat"s and you (intuirively)
> suspect the cause is system cache, set it to certain shorter
> value, say 1 minutes, and set the catalog_cache_memory_target
> to allowable amount of memory for each process. The memory
> usage will be stable at (un)certain amount above the target.
Could you guide me how to tune these parameters in an example scenario? Let me take the original problematic case
referencedat the beginning of this thread. That is:
* A PL/pgSQL function that creates a temp table, accesses it, (accesses other non-temp tables), and drop the temp
table.
* An application repeatedly begins a transaction, calls the stored function, and commits the transaction.
With v16 patch applied, and leaving the catalog_cache_xxx parameters set to their defaults, CacheMemoryContext
continuedto increase as follows:
CacheMemoryContext: 1065016 total in 9 blocks; 104168 free (17 chunks); 960848 used
CacheMemoryContext: 8519736 total in 12 blocks; 3765504 free (19 chunks); 4754232 used
CacheMemoryContext: 25690168 total in 14 blocks; 8372096 free (21 chunks); 17318072 used
CacheMemoryContext: 42991672 total in 16 blocks; 11741024 free (21761 chunks); 31250648 used
How can I make sure that this context won't exceed, say, 10 MB to avoid OOM?
I'm afraid that once the catcache hash table becomes large in a short period, the eviction would happen less
frequently,leading to memory bloat.
Regards
Takayuki Tsunakawa