Search Postgresql Archives

Re: out of memory for query result

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Sat, 2006-04-22 at 15:08 -0400, Tom Lane wrote:
> Francisco Reyes <lists@xxxxxxxxxxxxxxxx> 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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux