From: Davlet Panech <dpanech@gmail.com>
To: Keith <keith@keithf4.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Abnormally high memory usage/OOM triggered
Date: Thu, 18 Jan 2018 14:16:32 -0500
Message-ID: <8c6d9f34-2a2c-4317-ebbb-ba3ce2cd6a26@gmail.com> (raw)
In-Reply-To: <CAHw75vsT8-RnJ-Hv8iYp=H513jP==CMsJCtagOLibYvhXe8n8w@mail.gmail.com>
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>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: dpanech@gmail.com, keith@keithf4.com, pgsql-admin@lists.postgresql.org
Subject: Re: Abnormally high memory usage/OOM triggered
In-Reply-To: <8c6d9f34-2a2c-4317-ebbb-ba3ce2cd6a26@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox