Re: OOM on large SELECT

Hello,the output of show work_mem; is:

work_mem---------- 1MB(1 row)
Is maybe this the problem?
# - Memory -
shared_buffers = 32MB # min 128kB # (change requires restart)#temp_buffers = 8MB # min 800kB#max_prepared_transactions = 0 # zero disables the feature # (change requires restart)# Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory# per transaction slot, plus lock space (see max_locks_per_transaction).# It is not advisable to set max_prepared_transactions nonzero unless you# actively intend to use prepared transactions.#work_mem = 1MB # min 64kB#maintenance_work_mem = 16MB # min 1MB#max_stack_depth = 2MB # min 100kB
# - Kernel Resource Usage -
#max_files_per_process = 1000 # min 25 # (change requires restart)#shared_preload_libraries = '' # (change requires restart)
# - Cost-Based Vacuum Delay -
#vacuum_cost_delay = 0ms # 0-100 milliseconds#vacuum_cost_page_hit = 1 # 0-10000 credits#vacuum_cost_page_miss = 10 # 0-10000 credits#vacuum_cost_page_dirty = 20 # 0-10000 credits#vacuum_cost_limit = 200 # 1-10000 credits
# - Background Writer -
#bgwriter_delay = 200ms # 10-10000ms between rounds#bgwriter_lru_maxpages = 100 # 0-1000 max buffers written/round#bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on buffers scanned/round
# - Asynchronous Behavior -
#effective_io_concurrency = 1 # 1-1000. 0 disables prefetching
There is maybe some problem in the configuration?Thank you again for your help;Regards,Angelo.

> Subject: Re: [JDBC] OOM on large SELECT
> From: hannu(at)2ndQuadrant(dot)com
> To: amenuor(at)hotmail(dot)com
> CC: pgsql-jdbc(at)postgresql(dot)org
> Date: Sun, 20 Sep 2009 13:08:23 +0300
>
> On Thu, 2009-09-17 at 19:03 +0200, Angelo Nicolosi wrote:
> > Sorry for the delay of this answer but i was trying to figure out.
> > However I saw that the memory that the postgres is using is getting
> > larger step by step.
> > So it doesn't free it.
>
> If the memory is allocated using palloc() and not freed even after the
> query finishes, then you must be using a wrong memory context.
>
> > After the third query it is already full and one of the thread of the
> > postgres is killed from the OOM.
> > When the process is killed the program usually is going to call again
> > a stored function.
> > By the way the info that you required me are:
> >
> >
> > postgres (PostgreSQL) 8.4.0
> > Linux kernel 2.6.18 64bits
> >
> >
> > For the memory settings I have to contact the system admin because i
> > don't have the rights, on that machines, to read the configurations
> > file.
>
> do
>
> show work_mem;
>
> from psql;
>
> to see all conf params, do
>
> show all;
>
>
>
> > Thank you again to all for your help.
> > Cheers,
> > Angelo.
> >
> > > Date: Wed, 16 Sep 2009 09:30:59 -0700
> > > From: pierce(at)hogranch(dot)com
> > > To: amenuor(at)hotmail(dot)com
> > > CC: pgsql-jdbc(at)postgresql(dot)org
> > > Subject: Re: [JDBC] OOM on large SELECT
> > >
> > > Angelo Nicolosi wrote:
> > > > It's possible that the problem is in my C code but every time that
> > I'm
> > > > allocating memory, using always the palloc() function, I'm always
> > > > calling the pfree().
> > > > There is some way to analyze the code meanwhile is working inside
> > the
> > > > Postgre server (something like valgrind)?
> > > > However the command free -m on my machine outputs:
> > > >
> > > > total used free shared buffers cached
> > > > Mem: 2010 664 1345 0 157 383
> > > > -/+ buffers/cache: 123 1886
> > > > Swap: 16386 41 16345
> > > >
> > > > I think that the swap is enough.
> > > > Could you give me some tips about how can I see where is the
> > problem?
> > > > Thank you for your help!
> > >
> > > do you know what query you were making when you ran out of memory?
> > it
> > > -appears- it was a postgres server process that was OOM'd.
> > >
> > > what OS and version are you on (OOM seems to imply its likely
> > linux,
> > > since no other OS I'm familiar with would randomly kill processes
> > like
> > > that), what version postgres, etc ?
> > >
> > > also, what are the various memory settings in your postgresql.conf
> > > (shared_buffers, work_mem, etc)
> > >
> > >
> >
> >
> >
> > ______________________________________________________________________
> > Una risposta istantanea? Usa Messenger da Hotmail
> --
> Hannu Krosing http://www.2ndQuadrant.com
> PostgreSQL Scalability and Availability
> Services, Consulting and Training
>
>

_________________________________________________________________
Quante ne sai? Gioca con Crosswire e mettiti alla prova!
http://livesearch.games.msn.com/crosswire/default_it/