out of memory for query result

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

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

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

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

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

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/