Vladimir, Thanks for the details. I think you are undoubtedly running into
expression evaluation memory leaks. Basically, any expression that
yields a non-pass-by-value data type consumes memory that is not
reclaimed until end of statement --- so when you process a few million
rows, that memory starts to add up. (Yes, I realize this is a horrible
misfeature. It's on our TO-DO list to fix it, but it probably won't
happen until 7.1 or 7.2.) In the meantime the best I can offer you
is workarounds.
I think the major problems here are coming from the
"date_trunc('day',start)" calculation (because its datetime result is
pass-by-reference) and to a lesser extent from the sum(bytes)
calculation (because int8 is pass-by-reference). You could easily
replace "date_trunc('day',start)" with "date(start)"; since date is
a pass-by-value type, that won't leak memory, and it should give
equivalent results. The int8 sum is not quite so easy to fix.
I assume you can't get away with switching to int4 --- probably
your sum would overflow an int4? It may be that just fixing the
inefficient date_trunc calc will reduce your memory requirements
enough to get by. If not, the only good news I have is that release
7.0 does fix the memory-leak problem for internal calculations of
aggregate functions like sum(). You can get the first beta release
for 7.0 now.
regards, tom lane
"Vladimír Beneš" <Vladimir.Benes@pvt.cz> writes:
> -----P�vodn� zpr�va-----
> Od: Tom Lane <tgl@sss.pgh.pa.us>
> Komu: Oliver Elphick <olly@lfix.co.uk>
> Kopie: pgsql-hackers@postgreSQL.org <pgsql-hackers@postgreSQL.org>;
> Vladimir.Benes@pvt.cz <Vladimir.Benes@pvt.cz>
> Datum: 22. �nora 2000 18:06
> P�edm�t: Re: [HACKERS] Out of memory problem (forwarded bug report)
>> "Oliver Elphick" <olly@lfix.co.uk> writes:
>>> Can someone advise, please, how to deal with this problem in 6.5.3?
>>
>> My guess is that the cause is memory leaks during expression evaluation;
>> but without seeing the complete view definitions and underlying table
>> definitions, it's impossible to know what processing is being invoked
>> by this query...
>>
>> regards, tom lane
> Well, I will append views and underlying table definition:
> 1) Once again - failure query:
> select comm_type,name,tot_bytes,tot_packets
> from flow_sums_days_send_200002_view
> where day='2000-02-21' and name not like '@%'
> union all
> select comm_type,name,tot_bytes,tot_packets
> from flow_sums_days_receive_200002_view
> where day='2000-02-21' and name not like '@%'
> 2) views definition:
> create view flow_sums_days_send_200002_view as
> select
> 'send'::varchar as comm_type, date_trunc('day',start) as day,
> src_name as name, sum(bytes) as tot_bytes, sum(packets) as tot_packets
> from flow_sums_200002
> group by day, src_name
> create view flow_sums_days_receive_200002_view as
> select
> 'receive'::varchar as comm_type, date_trunc('day',start) as day,
> dst_name as name, sum(bytes) as tot_bytes, sum(packets) as tot_packets
> from flow_sums_200002
> group by day, dst_name
> I wanted create only one usefull view:
> create view flow_sums_days_200002_view as
> select
> 'send'::varchar as comm_type, date_trunc('day',start) as day,
> src_name as name, sum(bytes) as tot_bytes, sum(packets) as tot_packets
> from flow_sums_200002
> group by day, src_name
> UNION ALL
> select
> 'receive'::varchar as comm_type, date_trunc('day',start) as day,
> dst_name as name, sum(bytes) as tot_bytes, sum(packets) as tot_packets
> from flow_sums_200002
> group by day, dst_name
> ...but Postgres cann't use clause UNION ALL at view definition. So I created
> two views mentioned above and I wanted use this ones with UNION ALL clause
> only.
> 3) underlaying table definition:
> create table flow_sums_200002 (
> primary_collector varchar(50) not null,
> start datetime not null,
> end_period datetime not null,
> dead_time_rel float4 not null,
> src_name varchar(50) not null,
> dst_name varchar(50) not null,
> bytes int8 not null,
> packets int4 not null
> )
> Today this table has about 3 000 000 rows and the select command
> mentioned above returns 190 + 255 rows.
> Now I don't use clause "UNION ALL" and the program executes two queryes
> and then adds both result to new result. I reduced time increment of number
> rows to flow_sums_200002 table (three times less). This table contains data
> of February 2000 and the program will create table flow_sums_200003 with
> relevant views next month.
> Well, now this solution solve my problem but always depends on number of
> rows - I only moved limit of rows count.
> Thank You, V. Benes
> P.S.: I append part of top on my system while the query is running:
> CPU states: 98.6% user, 1.3% system, 0.0% nice, 0.0% idle
> Mem: 127256K av, 124316K used, 2940K free, 29812K shrd, 2620K buff
> Swap: 128516K av, 51036K used, 77480K free 7560K cached
> PID USER PRI NI SIZE RSS SHARE STAT LIB %CPU %MEM TIME COMMAND
> 2942 postgres 20 0 141M 99M 17348 R 0 99.0 80.4 1:22 postmaster
> => postmaster later took 80 - 95% of memory, free memory decressed to 2 MB,
> CPU was overloaded (0% idle and 99% by user process of postmaster). Have You
> ever seen something similar :-) ?