out of memory error
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
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.
Show quoted text
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.
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
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.
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?
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
!DSPAM:737,4e109dd612097665720452!
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
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
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
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
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
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
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
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/
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