Thread: out of memory for query result
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
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/ > From each according to his ability. To each according to his ability to litigate.