X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 883D09F9CAA
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 15 Jun 2006 09:15:21 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 90514-07 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 15 Jun 2006 09:15:11 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from q1exch01.Q1LABS.INC (Q1EXCH01.Q1Labs.com [205.174.165.4])
	by postgresql.org (Postfix) with ESMTP id 317AA9FA5BA
	for <pgsql-performance@postgresql.org>;
	Thu, 15 Jun 2006 09:15:09 -0300 (ADT)
Received: from q1exch02.Q1LABS.INC ([10.100.50.8]) by q1exch01.Q1LABS.INC with
	Microsoft SMTPSVC(6.0.3790.1830); Thu, 15 Jun 2006 09:15:08 -0300
Thread-Index: AcaQdVfH7ygW5TSCT4evw5j6qhV5Uw==
X-PMWin-Version: 2.5.0e, Antispam-Engine: 2.3.0.1, Antivirus-Engine: 2.34.3
Received: from remedy ([10.100.100.123]) by q1exch02.Q1LABS.INC with Microsoft
	SMTPSVC(6.0.3790.1830); Thu, 15 Jun 2006 09:15:08 -0300
From: "jody brownell" <jody.brownell@q1labs.com>
To: <pgsql-performance@postgresql.org>
Subject: Re: Postgres consuming way too much memory???
Date: Thu, 15 Jun 2006 09:15:10 -0300
User-Agent: KMail/1.8.3
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>
References: <200606141618.37849.jody.brownell@q1labs.com>
	<3106.1150315380@sss.pgh.pa.us>
	<200606150901.19201.jody.brownell@q1labs.com>
Content-class: urn:content-classes:message
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2663
Importance: normal
In-Reply-To: <200606150901.19201.jody.brownell@q1labs.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-ID: <200606150915.10306.jody.brownell@q1labs.com>
X-OriginalArrivalTime: 15 Jun 2006 12:15:08.0474 (UTC)
	FILETIME=[57C501A0:01C69075]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200606/299
X-Sequence-Number: 19656

The last version of postgres we had in production was 8.1.1 actually, not 8.1.3. 

So far, on my stability box and older production stability boxes I dont see the same behavior. 

I will install 8.1.1 on these boxes and see what I see.

On Thursday 15 June 2006 09:01, jody brownell wrote:
> Sorry about that, I was in a slight panic :) 
> 
> I am using postgresql 8.1.4. I will install 8.1.3 and see if the same behavior exists.. we 
> may have started seeing this in 8.1.3, but I dont think before. I will check some stability 
> machines for similar bloating.
> 
> The query (calling a store proc) which is always running when the spiral begins is below. It simply performs 
> bulk linking of two objects.  Depending on what the application is detecting, it could be called to insert
> 40 - 50k records, 500 at a time. When the box is healthy, this is a 200 - 500 ms op, but this starts to become 
> a 20000+ ms op. I guess this makes sense considering the paging.....
> 
> Jun 14 12:50:18 xxx postgres[5649]: [3-1] LOG:  duration: 20117.984 ms  statement: EXECUTE <unnamed>  [PREPARE:  select * from link_attacker_targets($1, $2, $3)  as
> 
> CREATE OR REPLACE FUNCTION link_attacker_targets (p_attacker bigint, p_targets varchar, p_targets_size integer) 
> 	returns bigint[] as
> $body$
> DECLARE
>     v_targets      bigint[];
>     v_target       bigint;
>     v_returns      bigint[];
>     v_returns_size integer := 0;
> BEGIN
>     v_targets := convert_string2bigint_array (p_targets, p_targets_size);
> 
>     FOR i IN 1..p_targets_size LOOP
>       	v_target := v_targets[i];
> 
> 	BEGIN
>           INSERT into attacker_target_link (attacker_id, target_id) values (p_attacker, v_target);
>           v_returns_size := v_returns_size + 1;
>           v_returns[v_returns_size] := v_target;
>           
> 	EXCEPTION WHEN unique_violation THEN
> 		-- do nothing... app cache may be out of date.
> 	END;
>     END LOOP;
>     RETURN v_returns;
> END;
> $body$
> LANGUAGE plpgsql VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
> 
> On Wednesday 14 June 2006 17:03, you wrote:
> > "jody brownell" <jody.brownell@q1labs.com> writes:
> > > 27116 postgres  15   0 1515m 901m  91m S  0.0 22.9  18:33.96 postgres: qradar qradar ::ffff:x.x.x.x(51149) idle
> > 
> > This looks like a memory leak, but you haven't provided enough info to
> > let someone else reproduce it.  Can you log what your application is
> > doing and extract a test case?  What PG version is this, anyway?
> > 
> > 			regards, tom lane
> > 
> > 
> > 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>