Re: Abnormally high memory usage/OOM triggered

On 1/18/2018 12:45 PM, Keith wrote:
> 
> 
> On Thu, Jan 18, 2018 at 12:13 PM, Davlet Panech <dpanech@gmail.com 
> <mailto:dpanech@gmail.com>> wrote:
> 
>     On 1/17/2018 5:57 PM, scott ribe wrote:
> 
>         On Jan 17, 2018, at 2:57 PM, Davlet Panech <dpanech@gmail.com
>         <mailto:dpanech@gmail.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 understand, but in my case a single server-side postgres process used 
19GB, which (excluding shared memory etc) is something like a 100 times 
what I would expect, even for "complex" queries.

> 
> 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.

OK, so it is likely a memory leak; I just wanted to rule out other 
explanations.

Thanks to all who replied.