Thread: Memory grows without bounds in aggregates!
Hello, I have downloaded the latest snapshot-version from 27th, compiled and installed it onto a Linux 2.1.131, libc6 I have the following table: CREATE TABLE "west0" ( "lfnr" int8, "kdnr" int8, "artnr" int8, "eknumsatz" float8, "ekumsatz"float8, "vkumsatz" float8, "lvkumsatz" float8, "menge" float8, "anz" int2, "datum"date); Doing the following is quite fast and memory usage of the postmaster is ok (abt 3MB). stamm=> select count(*) from west0; count -------- 12290703 (1 row) But doing the following aggregate on the same table will crash the backend: stamm=> select sum(ekumsatz), sum(vkumsatz),sum(lvkumsatz),count(*) from west0; pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. We have lost the connection to the backend, so further processing is impossible. Terminating. Take a look at the output of top after about 2 minutes: 6:43pm up 25 days, 10:19, 1 user, load average: 1.97, 0.71, 0.42 70 processes: 68 sleeping, 2 running, 0 zombie, 0 stopped CPU states: 25.6% user, 11.1% system, 1.5% nice, 63.4% idle Mem: 257244K av, 254048K used, 3196K free, 6116K shrd, 13100K buff Swap: 130748K av, 122264K used, 8484K free 18812K cached PID USER PRI NI SIZE RSS SHARE STAT LIB %CPU %MEM TIME COMMAND 12253 postgres 16 0 304M 205M 1148 R 0 33.8 81.7 2:00 postmaster ^^^^^^^^^^ Any idea? Kind regards Michael Contzen Dohle Handelsgruppe Systemberatung GmbH, Germany E-Mail mcontzen@dohle.com
Michael Contzen <mcontzen@dohle.com> writes:
> [ out of memory for ]
> stamm=> select sum(ekumsatz), sum(vkumsatz),sum(lvkumsatz),count(*) from
> west0;
Right, this is an instance of a known problem (palloc'd temporaries for
aggregate functions aren't freed until end of statement). I think
someone was looking into a quick-hack patch for aggregates, but there
are comparable problems in evaluation of WHERE expressions, COPY, etc.
We really need a general-purpose solution, and that probably won't
happen till 6.6.
In the meantime, I expect that doing only one float8 sum() per select
would take a third as much memory --- you might find that that's an
adequate workaround for the short run.
regards, tom lane
> Michael Contzen <mcontzen@dohle.com> writes: > > [ out of memory for ] > > stamm=> select sum(ekumsatz), sum(vkumsatz),sum(lvkumsatz),count(*) from > > west0; > > Right, this is an instance of a known problem (palloc'd temporaries for > aggregate functions aren't freed until end of statement). I think > someone was looking into a quick-hack patch for aggregates, but there > are comparable problems in evaluation of WHERE expressions, COPY, etc. > We really need a general-purpose solution, and that probably won't > happen till 6.6. > > In the meantime, I expect that doing only one float8 sum() per select > would take a third as much memory --- you might find that that's an > adequate workaround for the short run. > I thought we fixed this recently with that aggregate patch? -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <maillist@candle.pha.pa.us> writes:
>> Right, this is an instance of a known problem (palloc'd temporaries for
>> aggregate functions aren't freed until end of statement).
> I thought we fixed this recently with that aggregate patch?
No, we backed out said patch because it was busted (tried to free temp
even for pass-by-value types :-(). Anyone want to try again?
regards, tom lane