>From: Robert Haas [mailto:robertmhaas@gmail.com]
>
>On Mon, Feb 25, 2019 at 3:50 AM Tsunakawa, Takayuki
><tsunakawa.takay@jp.fujitsu.com> wrote:
>> How can I make sure that this context won't exceed, say, 10 MB to avoid OOM?
>
>As Tom has said before and will probably say again, I don't think you actually want that.
>We know that PostgreSQL gets roughly 100x slower with the system caches disabled
>- try running with CLOBBER_CACHE_ALWAYS. If you are accessing the same system
>cache entries repeatedly in a loop - which is not at all an unlikely scenario, just run the
>same query or sequence of queries in a loop - and if the number of entries exceeds
>10MB even, perhaps especially, by just a tiny bit, you are going to see a massive
>performance hit.
>Maybe it won't be 100x because some more-commonly-used entries will always stay
>cached, but it's going to be really big, I think.
>
>Now you could say - well it's still better than running out of memory.
>However, memory usage is quite unpredictable. It depends on how many backends
>are active and how many copies of work_mem and/or maintenance_work_mem are in
>use, among other things. I don't think we can say that just imposing a limit on the
>size of the system caches is going to be enough to reliably prevent an out of memory
>condition unless the other use of memory on the machine happens to be extremely
>stable.
>So I think what's going to happen if you try to impose a hard-limit on the size of the
>system cache is that you will cause some workloads to slow down by 3x or more
>without actually preventing out of memory conditions. What you need to do is accept
>that system caches need to grow as big as they need to grow, and if that causes you
>to run out of memory, either buy more memory or reduce the number of concurrent
>sessions you allow. It would be fine to instead limit the cache memory if those cache
>entries only had a mild effect on performance, but I don't think that's the case.
I'm afraid I may be quibbling about it.
What about users who understand performance drops but don't want to
add memory or decrease concurrency?
I think that PostgreSQL has a parameter
which most of users don't mind and use is as default
but a few of users want to change it.
In this case as you said, introducing hard limit parameter causes
performance decrease significantly so how about adding detailed caution
to the document like planner cost parameter?
Regards,
Takeshi Ideriha