Re: ERROR: out of memory DETAIL: Failed on request of size ???

On 19 Listopad 2013, 5:30, Brian Wong wrote:
> I've tried any work_mem value from 1gb all the way up to 40gb, with no
> effect on the error. I'd like to think of this problem as a server
> process memory (not the server's buffers) or client process memory issue,
> primarily because when we tested the error there was no other load
> whatsoever. Unfortunately, the error doesn't say what kinda memory ran
> out.

Hi Brian,

first of all, please don't top-post, especially if the previous response
used bottom-post. Thank you ;-)

Regarding the issue you're seeing:

* Increasing work_mem in hope that it will make the issue go away is
pointless. In case work_mem is too low, PostgreSQL will automatically
spill the data to disk (e.g. it won't do a sort in memory, but will do a
on-disk merge sort). It will never fail, and messages "failed on request
of size" is actually coming from malloc, when requesting another chunk of
memory from the OS. So you're hitting a OS-level memory limit.

Note: AFAIK the only operation that does not spill to disk, and may fail
with OOM-like errors is hash aggregate. But by increasing the work_mem
you're actually encouraging PostgreSQL to do this planning error.

I see the query you're running is doing MAX() so it might be hitting this
issue. How much data are you dealing with? How many groups are in the
result?

* Setting shared buffers to 18GB is almost certainly a bad choice. I'm yet
to see a real-world database where shared_buffers over 8GB actually make a
measurable difference. More is not always better, and you're actually
reserving memory that can't be used for work_mem (so you're making the
issue worse). Please, decrease shared_buffers to e.g. 4GB, then try to
increase it and measure the performance difference.

* So how much memory does the query allocate? Can you watch it over
top/free to get an idea if it e.g. allocates all available memory, or if
it allocates only 1GB and then fail, or something?

* I believe you're hitting some sort of limit, imposed by the operating
system. Please check ulimit and overcommit settings.

* BTW the SO post you mentioned as a perfect match was talking about query
executed over dblink - are you doing the same? If so, try to move the
aggregation into the query (so that the aggregation happens on the other
end).

regards
Tomas

>
> --- Original Message ---
>
> From: "bricklen" <bricklen(at)gmail(dot)com>
> Sent: November 18, 2013 7:25 PM
> To: "Brian Wong" <bwong64(at)hotmail(dot)com>
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] ERROR: out of memory DETAIL: Failed on request of
> size ???
>
> On Mon, Nov 18, 2013 at 12:40 PM, Brian Wong <bwong64(at)hotmail(dot)com> wrote:
>
>> We'd like to seek out your expertise on postgresql regarding this error
>> that we're getting in an analytical database.
>>
>> Some specs:
>> proc: Intel Xeon X5650 @ 2.67Ghz dual procs 6-core, hyperthreading on.
>> memory: 48GB
>> OS: Oracle Enterprise Linux 6.3
>> postgresql version: 9.1.9
>> shared_buffers: 18GB
>>
>> After doing a lot of googling, I've tried setting FETCH_COUNT on psql
>> AND/OR setting work_mem. I'm just not able to work around this issue,
>> unless if I take most of the MAX() functions out but just one.
>>
>
> What is your work_mem set to?
> Did testing show that shared_buffers set to 18GB was effective? That seems
> about 2 to 3 times beyond what you probably want.
>