Subject: Re: Abnormally high memory usage/OOM triggered
To: Keith <keith@keithf4.com>
Cc: pgsql-admin@lists.postgresql.org
References: <4d52fee7-73ba-16a9-9c27-8ae5107ee6ae@gmail.com>
 <5E0AB8DB-F13B-41BF-843A-FE64FBE9C097@elevated-dev.com>
 <15a90b55-5151-ce22-16ee-7ff883e5eb97@gmail.com>
 <CAHw75vsT8-RnJ-Hv8iYp=H513jP==CMsJCtagOLibYvhXe8n8w@mail.gmail.com>
From: Davlet Panech <dpanech@gmail.com>
Message-ID: <8c6d9f34-2a2c-4317-ebbb-ba3ce2cd6a26@gmail.com>
Date: Thu, 18 Jan 2018 14:16:32 -0500
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101
 Thunderbird/52.5.2
MIME-Version: 1.0
In-Reply-To: 
 <CAHw75vsT8-RnJ-Hv8iYp=H513jP==CMsJCtagOLibYvhXe8n8w@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

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.