Thread: out of memory for query result

out of memory for query result

From
Francisco Reyes
Date:
What resource do I need to increase to avoid the error above?

Trying to do a straight select against a table with 6 million records.
So far tried increasing SHMMAX to 512MB


Re: out of memory for query result

From
Tom Lane
Date:
Francisco Reyes <lists@stringsutils.com> writes:
> What resource do I need to increase to avoid the error above?

Process memory allowed to the client; this is not a server-side error.

            regards, tom lane

Re: out of memory for query result

From
Sven Willenberger
Date:
On Sat, 2006-04-22 at 15:08 -0400, Tom Lane wrote:
> Francisco Reyes <lists@stringsutils.com> writes:
> > What resource do I need to increase to avoid the error above?
>
> Process memory allowed to the client; this is not a server-side error.
>

I am experiencing an "out of memory" situation as well on large query
results, even with allowing 2G process memory to the client:

PostgreSQL 8.1.3, FreeBSD 6.1RC amd64, 8GB RAM.

Relevent configs:
# cat /boot/loader.conf
kern.maxdsiz="2147483648"
kern.dfldsiz="1073741824"

from the kernel config file:
options         SYSVSHM                 # SYSV-style shared memory
options         SYSVMSG                 # SYSV-style message queues
options         SYSVSEM                 # SYSV-style semaphores
options         SHMMAXPGS=131072
options         SEMMNI=128
options         SEMMNS=512
options         SEMUME=100
options         SEMMNU=256

work_mem = 64MB
maint_work_mem = 512MB

The query result contains about 7.5million rows and I am simply trying
to \o[utput] it to a file:

SELECT callstartdate, callenddate, callduration, calling_number,
called_number, dest_type, sessionrate, sessioncost,
quote_ident(callcenter) as callcenter from cdrs_local where callenddate
between '2006-04-01' and '2006-04-30 23:59:59' order by callstartdate;

When viewing the process in top, I see postgres and the psql client
using relatively little memory (I guess this is the disk read part).
Then I see the psql process eat up memory till it hits the 2G mark
(imposed by the loader.conf tuner) and then "out of memory".

Removing the order by clause doesn't help, nor does reducing work_mem to
8MB. I also tried disabling the bitmap scan and sequence scan to no
avail. I don't know if this is related to the pg_restore memory issues
discussed in another thread or not.

This same query running on FreeBSD i386 (P4 xeon) using PostgreSQL 8.0.x
did not experience this problem.

Any ideas? How can I view the memory allocation and heap management in
the logfiles? (what do I need to set in postgresql.conf).

Sven


Re: out of memory for query result

From
Douglas McNaught
Date:
Sven Willenberger <sven@dmv.com> writes:

> On Sat, 2006-04-22 at 15:08 -0400, Tom Lane wrote:
>> Francisco Reyes <lists@stringsutils.com> writes:
>> > What resource do I need to increase to avoid the error above?
>>
>> Process memory allowed to the client; this is not a server-side error.
>>
>
> I am experiencing an "out of memory" situation as well on large query
> results, even with allowing 2G process memory to the client:

The server settings don't have anything to do with this problem.  The
client is trying to read the entire result set into memory before it
writes it out (that's just the way it works).  You can do it in
smaller chunks by using a cursor with DECLARE and FETCH.

-Doug

Re: out of memory for query result

From
Sven Willenberger
Date:
On Wed, 2006-05-03 at 13:16 -0400, Douglas McNaught wrote:
> Sven Willenberger <sven@dmv.com> writes:
>
> > On Sat, 2006-04-22 at 15:08 -0400, Tom Lane wrote:
> >> Francisco Reyes <lists@stringsutils.com> writes:
> >> > What resource do I need to increase to avoid the error above?
> >>
> >> Process memory allowed to the client; this is not a server-side error.
> >>
> >
> > I am experiencing an "out of memory" situation as well on large query
> > results, even with allowing 2G process memory to the client:
>
> The server settings don't have anything to do with this problem.  The
> client is trying to read the entire result set into memory before it
> writes it out (that's just the way it works).  You can do it in
> smaller chunks by using a cursor with DECLARE and FETCH.
>
> -Doug

OK, that I do see; I guess I never noticed it on the other (i386)
machine as the memory never exceeded the max amount allowed by the
tunables. That raises a question though:

Using identical data and identical queries, why would the amd64 system
using postgresql 8.1.3 be using some 2/3s more memory to store the query
results before output than the i386 system using postgresql 8.0.4?

amd64 system:
  PID USERNAME  THR PRI NICE   SIZE    RES STATE  C   TIME   WCPU CMD
 8193 svenw       1   5    0  1516M  1475M ttyin  1   0:15  0.00% psql

i386 system:
  PID USERNAME  THR PRI NICE   SIZE    RES STATE  C   TIME   WCPU CMD
61263 svenw       1   5    0   972M   956M ttyin  2   0:27  0.00% psql

These are the final stages after the file has flushed (this was run on
the prior month's data which had fewer results). This would either point
to an issue with a) amd64 memory allocation or b) palloc interacting
with 64bit memory or c) some other change.

For now I will try the cursor technique; I assume that if this is run
from within a function returning setof xxxx that I will run into the
same issue?

Sven


Re: out of memory for query result

From
"Larry Rosenman"
Date:
Sven Willenberger wrote:

> OK, that I do see; I guess I never noticed it on the other (i386)
> machine as the memory never exceeded the max amount allowed by the
> tunables. That raises a question though:
>
> Using identical data and identical queries, why would the amd64 system
> using postgresql 8.1.3 be using some 2/3s more memory to store the
> query results before output than the i386 system using postgresql
> 8.0.4?
>
> amd64 system:
>   PID USERNAME  THR PRI NICE   SIZE    RES STATE  C   TIME   WCPU CMD
>  8193 svenw       1   5    0  1516M  1475M ttyin  1   0:15  0.00% psql
>
> i386 system:
>   PID USERNAME  THR PRI NICE   SIZE    RES STATE  C   TIME   WCPU CMD
> 61263 svenw       1   5    0   972M   956M ttyin  2   0:27  0.00% psql
>
> These are the final stages after the file has flushed (this was run on
> the prior month's data which had fewer results). This would either
> point to an issue with a) amd64 memory allocation or b) palloc
> interacting with 64bit memory or c) some other change.

My suspicion is that it has to do with pointers being 8 bytes vs 4 bytes
in amd64.


--
Larry Rosenman
Database Support Engineer

PERVASIVE SOFTWARE. INC.
12365B RIATA TRACE PKWY
3015
AUSTIN TX  78727-6531

Tel: 512.231.6173
Fax: 512.231.6597
Email: Larry.Rosenman@pervasive.com
Web: www.pervasive.com

Re: out of memory for query result

From
Martijn van Oosterhout
Date:
On Wed, May 03, 2006 at 02:52:57PM -0400, Sven Willenberger wrote:
> Using identical data and identical queries, why would the amd64 system
> using postgresql 8.1.3 be using some 2/3s more memory to store the query
> results before output than the i386 system using postgresql 8.0.4?

Is the amd64 system running 64-bit code? If so, the all the pointers
would double in size. That together with additional alignment
requirements could explain it.

> These are the final stages after the file has flushed (this was run on
> the prior month's data which had fewer results). This would either point
> to an issue with a) amd64 memory allocation or b) palloc interacting
> with 64bit memory or c) some other change.

psql doesn't use palloc. palloc exists purely in the backend.

> For now I will try the cursor technique; I assume that if this is run
> from within a function returning setof xxxx that I will run into the
> same issue?

The memory usage is purely the client trying to store the entire
dataset in memory. If your function is using the output but not
storing, there is ofcourse no problem.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Attachment