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(at)gmail(dot)com
> <mailto: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
> <mailto: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 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.