From: | "Peter" <peter(at)greatnowhere(dot)com> |
---|---|
To: | "Merlin Moncure" <mmoncure(at)gmail(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Transaction eating up all RAM |
Date: | 2006-03-14 09:49:46 |
Message-ID: | 025201c6474c$a5ece450$a502a8c0@komtek.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
> >> I have stored proc that retrieves a bunch of data, stores it in temp =
> >> table, computes all sorts of totals/averages/whatnots from the temp =
> >> table, and inserts results in another table. It works fine (except I =
> >> don't like wrapping all SQL statements in 'execute'), but multiple
> >> calls
> >> =
> >> to that proc from another procedure causes excessive memory usage =
> >> (upwards of 400M), and server eventually runs out of swap space. I =
> >> believe this is because PG caches transactions in RAM, and this =
> >> particular one is a bit too big.=20
> is that multiple simultaneous calls? maybe you are over committing
> your sort memory. If you can reproduce the out of memory behavior
> from a single backend that argues for a memory leak.
Single backend.
> p.s. you can create one function temp_tables_init(), called after
> connection to backend (and not in a transaction) which creates all
> temp tables for the process. If you do that and remember to truncate
> the tables (not drop), you can use non-dynamic pl/pgsql calls.
That's an interesting option... would make our PHP frontend a bit more
complex thou. Obviously performance would be better in this case as query
plans will be pre-compiled.
Peter
From | Date | Subject | |
---|---|---|---|
Next Message | Mary Adel | 2006-03-14 11:00:34 | stored procedure |
Previous Message | Richard Huxton | 2006-03-14 09:42:40 | Re: Urgent !!! Please Help Me |
From | Date | Subject | |
---|---|---|---|
Next Message | Qingqing Zhou | 2006-03-14 10:22:23 | Re: log_duration and log_statement |
Previous Message | Simon Riggs | 2006-03-14 08:55:40 | Re: [PATCHES] Automatic free space map filling |