On Thu, Jan 18, 2018 at 12:13 PM, Davlet Panech <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> 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
"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.