Re: Abnormally high memory usage/OOM triggered

On Thu, Jan 18, 2018 at 12:13 PM, Davlet Panech <dpanech(at)gmail(dot)com> wrote:

> On 1/17/2018 5:57 PM, scott ribe wrote:
>
>> On Jan 17, 2018, at 2:57 PM, Davlet Panech <dpanech(at)gmail(dot)com> wrote:
>>
>>>
>>> Does my configuration look reasonable? I just don't understand how it
>>> could possibly use up 19 GB of memory based on the configuration below. Is
>>> there a memory leak in there somewhere?
>>>
>>
>> It does seem awfully high, but... An update can involve a join across
>> multiple tables. Or an update can run a trigger which can cascade. Either
>> of those could result in an "accidental cross product" join, which can
>> always blow up memory.
>>
> There must be a way to put an upper limit on memory even for such cases. I
> was under the impression that parameters such as "work_mem" serve that
> purpose, is that not the case? So an "accidental cross product" join's
> memory usage is unbounded? It can't be... could somebody confirm this
> please?
>
> Thanks,
> D.
>
>
work_mem isn't really an upper limit on overall memory usage. It's just an
upper limit on how much is used in certain processes before spilling to
disk. A query or group of queries can easily use up all of system memory if
it's complex enough by using multiple instances of work_mem. This is why
work_mem shouldn't be set any higher than necessary. The wiki explains this
better

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

"This size is applied to each and every sort done by each user, and complex
queries can use multiple working memory sort buffers. Set it to 50MB, and
have 30 users submitting queries, and you are soon using 1.5GB of real
memory. "

I would go with Tom's suggestion in this case, though, since that bug seems
to fit the situation described by the patch he found. It's always important
to be running the latest patch release to rule out a bug being the cause of
an issue.

Keith