Thread: out of memory error

out of memory error

From
Geoffrey Myers
Date:
We have a process that we successfully ran on virtually identical
databases.  The process completed fine on a machine with 8 gig of
memory.  The process fails when run on another machine that has 16 gig
of memory with the following error:

out of memory for query result

How is this possible?

The databases are almost identical.  By that I mean, the database that
the process completed properly is a dump of the database from the
machine where it failed.  There is about a week's more data in the
database where the process failed.  The whole database only takes up
about 13 gig of disk space.

Any clues would be appreciated.

--
Until later, Geoffrey

"I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them."
- Thomas Jefferson

Re: out of memory error

From
Sim Zacks
Date:
On 07/03/2011 01:00 PM, Geoffrey Myers wrote:

> We have a process that we successfully ran on virtually identical
> databases.  The process completed fine on a machine with 8 gig of
> memory.  The process fails when run on another machine that has 16 gig
> of memory with the following error:
>
> out of memory for query result
>
> How is this possible?

Look at the diff on the postgresql.conf from the two machines.

>
> The databases are almost identical.  By that I mean, the database that
> the process completed properly is a dump of the database from the
> machine where it failed.  There is about a week's more data in the
> database where the process failed.  The whole database only takes up
> about 13 gig of disk space.
>
> Any clues would be appreciated.
>


Re: out of memory error

From
Craig Ringer
Date:
On 3/07/2011 6:00 PM, Geoffrey Myers wrote:

> out of memory for query result
>
> How is this possible?

Resource limits?

Do you have a ulimit in place that applies to postgresql? You can check
by examining the resource limits of a running postgresql backend as
shown in /proc/$PG_PID where $PG_PID is the process ID of the backend of
interest.

Check your work_mem in postgresql.conf, too.

--
Craig Ringer

Re: out of memory error

From
Alban Hertroys
Date:
On 3 Jul 2011, at 12:00, Geoffrey Myers wrote:

> We have a process that we successfully ran on virtually identical databases.  The process completed fine on a machine
with8 gig of memory.  The process fails when run on another machine that has 16 gig of memory with the following error: 
>
> out of memory for query result

You didn't mention what client you're using, but could it possibly be the client that's running out of memory? The fact
thatit's happening in the query result seems to point to the client. 

Another thing you might want to check: Does the second server have at least as much shared memory configured in the
kernelas the first has? 

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4e109dd612097665720452!



Re: out of memory error

From
Geoffrey Myers
Date:
Alban Hertroys wrote:
> On 3 Jul 2011, at 12:00, Geoffrey Myers wrote:
>
>> We have a process that we successfully ran on virtually identical
>> databases.  The process completed fine on a machine with 8 gig of
>> memory.  The process fails when run on another machine that has 16
>> gig of memory with the following error:
>>
>> out of memory for query result
>
> You didn't mention what client you're using, but could it possibly be
> the client that's running out of memory? The fact that it's happening
> in the query result seems to point to the client.

Perl.

> Another thing you might want to check: Does the second server have at
> least as much shared memory configured in the kernel as the first
> has?

I was thinking that might be the issue.  They have the same amount of
share memory configured, but the server that had the error, has 8
postmasters running, whereas the other server only has one.

>
> Alban Hertroys
>
> -- Screwing up is an excellent way to attach something to the
> ceiling.
>
>
> !DSPAM:1272,4e109ddd12091486111017!
>
>
>


--
Until later, Geoffrey

"I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them."
- Thomas Jefferson

Re: out of memory error

From
Geoffrey Myers
Date:
Craig Ringer wrote:
> On 3/07/2011 6:00 PM, Geoffrey Myers wrote:
>
>> out of memory for query result
>>
>> How is this possible?
>
> Resource limits?


Could this message be generated because of shared memory issues?

The odd thing is the error was generated by a user process, but there is
no reference to the error in the database log file.

>
> Do you have a ulimit in place that applies to postgresql? You can check
> by examining the resource limits of a running postgresql backend as
> shown in /proc/$PG_PID where $PG_PID is the process ID of the backend of
> interest.
>
> Check your work_mem in postgresql.conf, too.

work_mem is commented out on both machines, so I suspect that it is then
using the default value?  What would be the default value?

>
> --
> Craig Ringer
>


--
Until later, Geoffrey

"I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them."
- Thomas Jefferson

Re: out of memory error

From
Geoffrey Myers
Date:
One other note, there is no error in the postgres log for this database.
  I would have expected to find an error there.

--
Until later, Geoffrey

"I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them."
- Thomas Jefferson

Re: out of memory error

From
Geoffrey Myers
Date:
Geoffrey Myers wrote:
> We have a process that we successfully ran on virtually identical
> databases.  The process completed fine on a machine with 8 gig of
> memory.  The process fails when run on another machine that has 16 gig
> of memory with the following error:
>
> out of memory for query result
>
> How is this possible?
>
> The databases are almost identical.  By that I mean, the database that
> the process completed properly is a dump of the database from the
> machine where it failed.  There is about a week's more data in the
> database where the process failed.  The whole database only takes up
> about 13 gig of disk space.
>
> Any clues would be appreciated.


One other note that is bothering me.  There is no reference in the log
regarding the out of memory error.  Should that not also show up in the
associated database log?


--
Geoffrey Myers
Myers Consulting Inc.
770.592.1651

Re: out of memory error

From
Geoffrey Myers
Date:
Craig Ringer wrote:
> On 3/07/2011 6:00 PM, Geoffrey Myers wrote:
>
>> out of memory for query result
>>
>> How is this possible?
>
> Resource limits?

Could this message be generated because of shared memory issues?

The odd thing is the error was generated by a user process, but there is
no reference to the error in the database log file.

> Do you have a ulimit in place that applies to postgresql? You can check
> by examining the resource limits of a running postgresql backend as
> shown in /proc/$PG_PID where $PG_PID is the process ID of the backend of
> interest.
>
> Check your work_mem in postgresql.conf, too.

work_mem is commented out on both machines, so I suspect that it is then
using the default value?  What would be the default value?

>
> --
> Craig Ringer
>


--
Geoffrey Myers
Myers Consulting Inc.
770.592.1651

Re: out of memory error

From
Tom Lane
Date:
Geoffrey Myers <geof@serioustechnology.com> writes:
> Geoffrey Myers wrote:
>> out of memory for query result

> One other note that is bothering me.  There is no reference in the log
> regarding the out of memory error.  Should that not also show up in the
> associated database log?

Not if it's a client-side error.

(Which a quick grep through the PG source code says it is ...)

            regards, tom lane

Re: out of memory error

From
Geoffrey Myers
Date:
Tom Lane wrote:
> Geoffrey Myers <geof@serioustechnology.com> writes:
>> Geoffrey Myers wrote:
>>> out of memory for query result
>
>> One other note that is bothering me.  There is no reference in the log
>> regarding the out of memory error.  Should that not also show up in the
>> associated database log?
>
> Not if it's a client-side error.
>
> (Which a quick grep through the PG source code says it is ...)
>
>             regards, tom lane

Wanted to add more specifics. Here is the actual code that generated the
error:

my $result = $conn->exec($select);

if ($result->resultStatus != PGRES_TUPLES_OK)
{
    $error = $conn->errorMessage;
         die "Error: <$error> Failed: <$select>";
}

So you're saying this select request failing would not be logged to the
postgres database log?


--
Until later, Geoffrey

"I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them."
- Thomas Jefferson

Re: out of memory error

From
Craig Ringer
Date:
On 5/07/2011 11:12 PM, Geoffrey Myers wrote:

> my $result = $conn->exec($select);
>
> if ($result->resultStatus != PGRES_TUPLES_OK)
> {
> $error = $conn->errorMessage;
> die "Error: <$error> Failed: <$select>";
> }
>
> So you're saying this select request failing would not be logged to the
> postgres database log?

If that request failed due to a server-side error, then that error would
appear in the server logs.

If it failed for a client-side reason like the client running out of
memory, then at most the server would report an unexpected disconnect or
connection timeout when the client vanishes. The server has no way to
know a client process crashed out.

--
Craig Ringer

POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088     Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/

Re: out of memory error

From
Toby Corkindale
Date:
On 06/07/11 01:12, Geoffrey Myers wrote:
> Wanted to add more specifics. Here is the actual code that generated the
> error:
>
> my $result = $conn->exec($select);
>
> if ($result->resultStatus != PGRES_TUPLES_OK)
> {
> $error = $conn->errorMessage;
> die "Error: <$error> Failed: <$select>";
> }


That looks like Perl code.
Which CPAN module are you using?

Judging by the PGRES_TUPLES_OK bit, I'm guessing it's either the
very-experimental Pg::PQ, or more likey - the ancient Pg module.

I just ask, because I don't think that module has been maintained since
the 20th Century! I mean, it's seriously out of date. It was built
against Postgresql version 6!

I believe everyone using Perl with PostgreSQL uses the DBD::Pg module
now - if you're having client errors, I really think you should look at
moving to it as well.


I hope this helps,
Toby