Thread: Why would this use 600Meg of VM?
Can one of you knowledgeable people tell me why current CVS as of
a week ago would have the backend running this query grow to
600 meg+?
INSERT into traffic_summary
SELECT asn,protocol,
cast(sum(pkts_src) as float) as pkts_src,
cast(sum(pkts_dst) as float) as pkts_dst,
cast(sum(bytes_src) as float) as bytes_src,
cast(sum(bytes_dst) as float) as bytes_dst,
cast(sum(secs_src) as float) as secs_src,
cast(sum(secs_dst) as float) as secs_dst,
min(early) as early,
max(late) as late
FROM traffic
WHERE early between '2001-06-01 00:00:00'::timestamp and '2001-06-18 23:59:59'::timestamp
GROUP BY asn,protocol,date_part('epoch',early)/60/60;
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Larry Rosenman <ler@lerctr.org> writes:
> Can one of you knowledgeable people tell me why current CVS as of
> a week ago would have the backend running this query grow to
> 600 meg+?
Sounds like there's still a memory leak in there somewhere, but the
query looks fairly harmless. Could we see enough info to reproduce
this? (Table declarations, explain output, etc) Another useful
attack would be to let the query run awhile, then set a breakpoint
at sbrk(). Stack traces from the first few hits of the breakpoint
would give a pretty good indication of where the leak is, probably.
regards, tom lane
* Tom Lane <tgl@sss.pgh.pa.us> [010622 11:55]:
> Larry Rosenman <ler@lerctr.org> writes:
> > Can one of you knowledgeable people tell me why current CVS as of
> > a week ago would have the backend running this query grow to
> > 600 meg+?
>
> Sounds like there's still a memory leak in there somewhere, but the
> query looks fairly harmless. Could we see enough info to reproduce
> this? (Table declarations, explain output, etc) Another useful
> attack would be to let the query run awhile, then set a breakpoint
> at sbrk(). Stack traces from the first few hits of the breakpoint
> would give a pretty good indication of where the leak is, probably.
>
> regards, tom lane
neteng@tide.iadfw.net$ psql traffic_analysis
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands
\g or terminate with semicolon to execute query \q to quit
traffic_analysis=# analyze traffic;
ANALYZE
traffic_analysis=# \i traffic_sum.sql
psql:traffic_sum.sql:15: NOTICE: QUERY PLAN:
Subquery Scan *SELECT* (cost=8471740.01..8994414.10 rows=1900633
width=72) -> Aggregate (cost=8471740.01..8994414.10 rows=1900633 width=72) -> Group
(cost=8471740.01..8614287.49rows=19006331
width=72) -> Sort (cost=8471740.01..8471740.01 rows=19006331
width=72) -> Seq Scan on traffic (cost=0.00..615601.86
rows=19006331 width=72)
EXPLAIN
traffic_analysis=#
neteng@tide.iadfw.net$ cat traffic_sum.sql
EXPLAIN
INSERT into traffic_summary
SELECT asn,protocol,
cast(sum(pkts_src) as float) as pkts_src,
cast(sum(pkts_dst) as float) as pkts_dst,
cast(sum(bytes_src) as float) as bytes_src,
cast(sum(bytes_dst) as float) as bytes_dst,
cast(sum(secs_src) as float) as secs_src,
cast(sum(secs_dst) as float) as secs_dst,
min(early) as early,
max(late) as late
FROM traffic
WHERE early between '2001-06-01 00:00:00'::timestamp and '2001-06-18 23:59:59'::timestamp
GROUP BY asn,protocol,date_part('epoch',early)/60/60;
neteng@tide.iadfw.net$
What else?
Failing a way to actually get this query to run, how would you suggest
aggregating the data down to 1 hour summaries?
neteng@tide.iadfw.net$ psql traffic_analysis
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands
\g or terminate with semicolon to execute query \q to quit
traffic_analysis=# \d traffic Table "traffic"Attribute | Type | Modifier
-----------+--------------------------+----------asn | integer | protocol | integer
| pkts_src | bigint | pkts_dst | bigint | bytes_src | bigint
| bytes_dst | bigint | secs_src | bigint | secs_dst | bigint |
early | timestamp with time zone | late | timestamp with time zone |
Index: traffic_early
traffic_analysis=# \d traffic_summary Table "traffic_summary"Attribute | Type | Modifier
-----------+--------------------------+----------asn | integer | protocol | integer
| pkts_src | double precision | pkts_dst | double precision | bytes_src | double precision
| bytes_dst | double precision | secs_src | double precision | secs_dst | double precision |
early | timestamp with time zone | late | timestamp with time zone |
traffic_analysis=#
traffic_analysis=# \d traffic_early Index "traffic_early"Attribute | Type
-----------+--------------------------early | timestamp with time zone
btree
traffic_analysis=#
LER
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
* Tom Lane <tgl@sss.pgh.pa.us> [010622 12:31]: > Larry Rosenman <ler@lerctr.org> writes: > > What else? > > If you don't want to do the debugger work yourself, could you send me > enough of the data to let me reproduce the problem? how much data do you need? It's multi hundred megs. I can probably get permission to give you a login on tide if that would be easier? LER > > regards, tom lane -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Larry Rosenman <ler@lerctr.org> writes:
> What else?
If you don't want to do the debugger work yourself, could you send me
enough of the data to let me reproduce the problem?
regards, tom lane
Larry Rosenman <ler@lerctr.org> writes:
> Can one of you knowledgeable people tell me why current CVS as of
> a week ago would have the backend running this query grow to
> 600 meg+?
The answer: the query has nothing to do with it. However, the
deferred triggers you have on the target relation have a lot to do
with it. It's all deferred-trigger-event storage.
regards, tom lane
At 01:06 24/06/01 -0400, Tom Lane wrote: > >The answer: the query has nothing to do with it. However, the >deferred triggers you have on the target relation have a lot to do >with it. It's all deferred-trigger-event storage. Would it be worth using a local (system) temporary table for this sort of thing? ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
* Philip Warner <pjw@rhyme.com.au> [010624 00:46]: > At 01:06 24/06/01 -0400, Tom Lane wrote: > > > >The answer: the query has nothing to do with it. However, the > >deferred triggers you have on the target relation have a lot to do > >with it. It's all deferred-trigger-event storage. > > Would it be worth using a local (system) temporary table for this sort of > thing? I this is an FK check, that I can probably turn off. I wonder if there is a better way? > > > ---------------------------------------------------------------- > Philip Warner | __---_____ > Albatross Consulting Pty. Ltd. |----/ - \ > (A.B.N. 75 008 659 498) | /(@) ______---_ > Tel: (+61) 0500 83 82 81 | _________ \ > Fax: (+61) 0500 83 82 82 | ___________ | > Http://www.rhyme.com.au | / \| > | --________-- > PGP key available upon request, | / > and from pgp5.ai.mit.edu:11371 |/ -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
> At 01:06 24/06/01 -0400, Tom Lane wrote: > > > >The answer: the query has nothing to do with it. However, the > >deferred triggers you have on the target relation have a lot to do > >with it. It's all deferred-trigger-event storage. > > Would it be worth using a local (system) temporary table for this sort of > thing? Jan intially wanted to store large FK events in a file when they got too big but never completed it. The TODO list has: * Add deferred trigger queue file (Jan) -- Bruce Momjian | http://candle.pha.pa.us pgman@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