Обсуждение: 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. > > 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 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!
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 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
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