On Sun, Apr 14, 2019 at 3:51 PM Gunther <raj@gusw.net> wrote:
>
> For weeks now, I am banging my head at an "out of memory" situation. There is only one query I am running on an 8 GB
system,whatever I try, I get knocked out on this out of memory. It is extremely impenetrable to understand and fix this
error.I guess I could add a swap file, and then I would have to take the penalty of swapping. But how can I actually
addressan out of memory condition if the system doesn't tell me where it is happening?
> We can't really see anything too worrisome. There is always lots of memory used by cache, which could have been
mobilized.The only possible explanation I can think of is that in that moment of the crash the memory utilization
suddenlyskyrocketed in less than a second, so that the 2 second vmstat interval wouldn't show it??? Nah.
>
> I have already much reduced work_mem, which has helped in some other cases before. Now I am going to reduce the
shared_buffersnow, but that seems counter-intuitive because we are sitting on all that cache memory unused!
>
> Might this be a bug? It feels like a bug. It feels like those out of memory issues should be handled more gracefully
(garbagecollection attempt?) and that somehow there should be more information so the person can do anything about it.
I kind of agree that nothing according to vmstat suggests you have a
problem. One thing you left out is the precise mechanics of the
failure; is the database getting nuked by the oom killer? Do you have
the logs?
*) what are values of shared_buffers and work_mem and maintenance_work_mem?
*) Is this a 32 bit build? (I'm guessing no, but worth asking)
*) I see that you've disabled swap. Maybe it should be enabled?
*) Can you get the query to run through? an 'explain analyze' might
point to gross misses in plan; say, sort memory overuse
*) If you're still getting failures, maybe we need to look at sampling
frequency of memory usage.
*) iowait is super high.
*) I see optimization potential in this query; explain analyze would
help here too.
merlin
