pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to avoid "Out of memory" using aggregate functions?
5+ messages / 3 participants
[nested] [flat]
* How to avoid "Out of memory" using aggregate functions?
@ 1999-12-02 19:30 Mark Dalphin <mdalphin@amgen.com>
1999-12-02 23:32 ` Re: [SQL] How to avoid "Out of memory" using aggregate functions? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Mark Dalphin @ 1999-12-02 19:30 UTC (permalink / raw)
To: pgsql-sql
Hi,
I am working with a simple table similar to the following:
CREATE TABLE Seq (
SeqID int8 PRIMARY KEY,
FileID int2 NOT NULL,
FileOffset int8 NOT NULL,
SeqLength int4,
FOREIGN KEY (FileID) REFERENCES File (FileID)
);
CREATE INDEX Seq_SeqLength ON Seq (SeqLength);
The table contains facts about a DNA sequence (eg its length) and how to access
that sequence from an ordinary Unix file (FileID and FileOffset) where it is
concatonated with all the other DNA sequences.
Currently the TABLE Seq contains 4,586,379 (times 2) rows and the overall size
of the Unix file is 3,660,117,293 bytes (ie I expect sum(SeqLength) to be in the
region of 3.6 Gbytes).
I want to use the PostgreSQL aggregate functions to learn more about the
sequences.
The following query works well and quickly (I need to cast as int8 to avoid
overflow of the sum() and perhaps for avg()):
SELECT count(seqlength::int8),
min(seqlength::int8),
max(seqlength::int8),
avg(seqlength::int8),
sum(seqlength::int8)
FROM Seq
WHERE FileID=4
AND SeqLength>1000;
An EXPLAIN of the above query returns the following:
NOTICE: QUERY PLAN:
Aggregate (cost=202000.62 rows=918779 width=4)
-> Index Scan using seq_seqlength on seq (cost=202000.62 rows=918779
width=4)
EXPLAIN
If I remove the SeqLength constraint, the query takes forever to execute and
then crashes with an out-of-memory (on my SGI, I have 256 Mbyte RAM and equal
sized swap space; I run out of swap space during the query which makes a very
unpleasent noise).
EXPLAIN
SELECT count(seqlength::int8),
min(seqlength::int8),
max(seqlength::int8),
avg(seqlength::int8),
sum(seqlength::int8)
FROM Seq
WHERE FileID=4;
NOTICE: QUERY PLAN:
Aggregate (cost=397916.69 rows=2756337 width=4)
-> Seq Scan on seq (cost=397916.69 rows=2756337 width=4)
EXPLAIN
I do not understand how to interpret this Query Plan, except that I am doing a
sequential scan of the DB (hence slow), but, since all the elements requested
are aggregates, why do I run out of memory? I am not trying to load the whole
table into memory at once, am I? Suggestions for how to make this query run
would be welcome; my database is rapidly growing larger!
Thanks,
Mark
--
Mark Dalphin email: mdalphin@amgen.com
Mail Stop: 29-2-A phone: +1-805-447-4951 (work)
One Amgen Center Drive +1-805-375-0680 (home)
Thousand Oaks, CA 91320 fax: +1-805-499-9955 (work)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] How to avoid "Out of memory" using aggregate functions?
1999-12-02 19:30 How to avoid "Out of memory" using aggregate functions? Mark Dalphin <mdalphin@amgen.com>
@ 1999-12-02 23:32 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 1999-12-02 23:32 UTC (permalink / raw)
To: Mark Dalphin <mdalphin@amgen.com>; +Cc: pgsql-sql
Mark Dalphin <mdalphin@amgen.com> writes:
> ... since all the elements requested
> are aggregates, why do I run out of memory?
The basic problem here is that Postgres' expression evaluator leaks
memory when dealing with expressions that use pass-by-reference data
types (which means practically any type other than bool, int2, int4).
The memory is reclaimed at the end of the query ... but that doesn't
help you if your allowable swap space fills with int8 values before
you get to the end :-(.
Fixing this is on the TODO list. I have made a start on it in current
sources: aggregate functions applied to simple field references do not
leak memory anymore. That won't help your query as written:
SELECT min(seqlength::int8) ...
since cast-to-int8 is a runtime type conversion expression and the
result of that expression evaluation will be leaked. But perhaps you
can store seqlength as an int8 field to begin with, and skip the
conversion. If so, and if you're handy with C, you might want to look
at src/backend/executor/nodeAgg.c in a current snapshot and see whether
it's practical to back-patch the memory-releasing logic into 6.5 ...
or just run the current snapshot if you're feeling adventurous ...
Of course the whole problem only comes up because avg() and sum() use
accumulators of the same width as the source data type, so they are
uncomfortably prone to overflow on large tables. They really should
be modified to use more reasonable choices of accumulation data type;
probably float8 for all datatypes except int8 and numeric, and numeric
for those two would work OK. This wasn't practical in prior releases
because a float8 accumulator would leak memory, but I think it would
be a good change to make for 7.0.
Anyway, to get back to your immediate problem, what you probably want to
do while using 6.5 is tweak the query to minimize the problem as much as
you can. First off, I see no reason to cast the inputs of count(),
min(), or max() to int8; leaving those inputs as the int4 field value
should work just as well and not leak memory. You don't have much
choice for either avg() or sum(), but perhaps not trying to evaluate
both in one query will bring the memory usage down to where you can
live with it. If not, the only answer I can see is to do sums() over
subsets of the table and save the results to be added and averaged
later (hack hack ...)
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] How to avoid "Out of memory" using aggregate functions?
@ 1999-12-06 12:56 Frank Joerdens <frank@x9media.com>
1999-12-06 16:23 ` Re: [SQL] How to avoid "Out of memory" using aggregate functions? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Frank Joerdens @ 1999-12-06 12:56 UTC (permalink / raw)
To: pgsql-sql
Presumably I am suffering from the same problem running 6.5.1:
The table has the following structure:
create table stunde_test (
source_numeric int8 not null,
destination_numeric int8 not null,
source inet not null,
destination inet not null,
bytes int8 not null,
date datetime
);
It stores IP traffic data and currently contains a little over
2 million rows.
The query is
SELECT
sum(stunde_test.bytes),
date_trunc('day', stunde_test.date),
stunde_test.destination
FROM
stunde_test,
domains
WHERE
date_part('hour', stunde_test.date) > 6
AND
date_part('hour', stunde_test.date) < 23
AND
domains.internet = stunde_test.destination
and date_trunc('day', stunde_test.date) = 'yesterday'
GROUP BY
date_trunc('day', stunde_test.date),
stunde_test.destination;
which stopped working after the table grew over the 1 million mark
(approximately) - I get the error
FATAL 1: Memory exhausted in AllocSetAlloc()
This sounds to me like the problem described below . . . my question is:
Would the changes you already made to the current sources fix it for me?
Otherwise I'd have to shring the table by removing older data or use
the brutal method of just putting more memory into the box . . .
thanks, frank
On Thu, Dec 02, 1999 at 06:32:59PM -0500, Tom Lane wrote:
> Mark Dalphin <mdalphin@amgen.com> writes:
> > ... since all the elements requested
> > are aggregates, why do I run out of memory?
>
> The basic problem here is that Postgres' expression evaluator leaks
> memory when dealing with expressions that use pass-by-reference data
> types (which means practically any type other than bool, int2, int4).
> The memory is reclaimed at the end of the query ... but that doesn't
> help you if your allowable swap space fills with int8 values before
> you get to the end :-(.
>
> Fixing this is on the TODO list. I have made a start on it in current
> sources: aggregate functions applied to simple field references do not
> leak memory anymore. That won't help your query as written:
>
> SELECT min(seqlength::int8) ...
>
> since cast-to-int8 is a runtime type conversion expression and the
> result of that expression evaluation will be leaked. But perhaps you
> can store seqlength as an int8 field to begin with, and skip the
> conversion. If so, and if you're handy with C, you might want to look
> at src/backend/executor/nodeAgg.c in a current snapshot and see whether
> it's practical to back-patch the memory-releasing logic into 6.5 ...
> or just run the current snapshot if you're feeling adventurous ...
>
> Of course the whole problem only comes up because avg() and sum() use
> accumulators of the same width as the source data type, so they are
> uncomfortably prone to overflow on large tables. They really should
> be modified to use more reasonable choices of accumulation data type;
> probably float8 for all datatypes except int8 and numeric, and numeric
> for those two would work OK. This wasn't practical in prior releases
> because a float8 accumulator would leak memory, but I think it would
> be a good change to make for 7.0.
>
> Anyway, to get back to your immediate problem, what you probably want to
> do while using 6.5 is tweak the query to minimize the problem as much as
> you can. First off, I see no reason to cast the inputs of count(),
> min(), or max() to int8; leaving those inputs as the int4 field value
> should work just as well and not leak memory. You don't have much
> choice for either avg() or sum(), but perhaps not trying to evaluate
> both in one query will bring the memory usage down to where you can
> live with it. If not, the only answer I can see is to do sums() over
> subsets of the table and save the results to be added and averaged
> later (hack hack ...)
>
> regards, tom lane
>
> ************
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] How to avoid "Out of memory" using aggregate functions?
1999-12-06 12:56 Re: [SQL] How to avoid "Out of memory" using aggregate functions? Frank Joerdens <frank@x9media.com>
@ 1999-12-06 16:23 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-06 18:20 ` Re: [SQL] How to avoid "Out of memory" using aggregate functions? Frank Joerdens <frank@x9media.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 1999-12-06 16:23 UTC (permalink / raw)
To: Frank Joerdens <frank@x9media.com>; +Cc: pgsql-sql
Frank Joerdens <frank@x9media.com> writes:
> [ complex query ]
> which stopped working after the table grew over the 1 million mark
> (approximately) - I get the error
> FATAL 1: Memory exhausted in AllocSetAlloc()
> This sounds to me like the problem described below . . . my question is:
> Would the changes you already made to the current sources fix it for me?
Afraid not. I think what's killing you is all those date_part() and
date_trunc() operations --- the resultant datetime or float8 value from
each one occupies memory that won't get reclaimed till end of statement
:-(.
Silly as it sounds, you might be able to put off the problem by
rearranging the order of the WHERE clauses, remembering that AND stops
evaluating its subclauses as soon as it finds a FALSE. Presumably the
day check eliminates many more rows than the time-of-day checks, so
WHERE
domains.internet = stunde_test.destination
AND
date_trunc('day', stunde_test.date) = 'yesterday'
AND
date_part('hour', stunde_test.date) > 6
AND
date_part('hour', stunde_test.date) < 23
would probably about halve the number of date_part+date_trunc
calculations done.
Of course the real fix is to recycle temporary memory for all
expressions intra-query, but I do not know if that will get done for
7.0. It will get done eventually.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] How to avoid "Out of memory" using aggregate functions?
1999-12-06 12:56 Re: [SQL] How to avoid "Out of memory" using aggregate functions? Frank Joerdens <frank@x9media.com>
1999-12-06 16:23 ` Re: [SQL] How to avoid "Out of memory" using aggregate functions? Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-12-06 18:20 ` Frank Joerdens <frank@x9media.com>
0 siblings, 0 replies; 5+ messages in thread
From: Frank Joerdens @ 1999-12-06 18:20 UTC (permalink / raw)
To: pgsql-sql
Well, not silly at all . . . regrouping
the order of the WHERE clause does the trick!
Cheers,
Frank
On Mon, Dec 06, 1999 at 11:23:27AM -0500, Tom Lane wrote:
> Frank Joerdens <frank@x9media.com> writes:
> > [ complex query ]
> > which stopped working after the table grew over the 1 million mark
> > (approximately) - I get the error
>
> > FATAL 1: Memory exhausted in AllocSetAlloc()
>
> > This sounds to me like the problem described below . . . my question is:
> > Would the changes you already made to the current sources fix it for me?
>
> Afraid not. I think what's killing you is all those date_part() and
> date_trunc() operations --- the resultant datetime or float8 value from
> each one occupies memory that won't get reclaimed till end of statement
> :-(.
>
> Silly as it sounds, you might be able to put off the problem by
> rearranging the order of the WHERE clauses, remembering that AND stops
> evaluating its subclauses as soon as it finds a FALSE. Presumably the
> day check eliminates many more rows than the time-of-day checks, so
>
> WHERE
> domains.internet = stunde_test.destination
> AND
> date_trunc('day', stunde_test.date) = 'yesterday'
> AND
> date_part('hour', stunde_test.date) > 6
> AND
> date_part('hour', stunde_test.date) < 23
>
> would probably about halve the number of date_part+date_trunc
> calculations done.
>
> Of course the real fix is to recycle temporary memory for all
> expressions intra-query, but I do not know if that will get done for
> 7.0. It will get done eventually.
>
> regards, tom lane
>
> ************
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~1999-12-06 18:20 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-12-02 19:30 How to avoid "Out of memory" using aggregate functions? Mark Dalphin <mdalphin@amgen.com>
1999-12-02 23:32 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-06 12:56 Re: [SQL] How to avoid "Out of memory" using aggregate functions? Frank Joerdens <frank@x9media.com>
1999-12-06 16:23 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-06 18:20 ` Frank Joerdens <frank@x9media.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox