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
Import Notes
Reply to msg id not found: YourmessageofSun28Mar1999174658+020036FE4EF2.A3761C23@dohle.com | Resolved by subject fallback
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, Pennsylvania 19026Bruce 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
Import Notes
Reply to msg id not found: YourmessageofMon29Mar1999110308-0500199903291604.LAA10369@candle.pha.pa.us | Resolved by subject fallback