Re: Postgres consuming way too much memory???

Some more information...

When postgresql starts to go into this bloating state, I can only make it happen from my java app.
If I simultaneously perform insert of 10million rows into another table, it behaves as expected, but
the postgresql process handling the java connection slows down and bloats.

This leads me to think it has something to do with either the long lived connection. I am using
dbcp jdbc pool from jakarta OR I am trigger this behavior with something I am doing in the link
routine I sent earlier.

I am going to try closing the connection after each TX to see if this resolves it for now. If not, I will write
a java app, stored procedure (table etc) reproduce it without our application.

Oh yeah, it is when I use about have of my swap, dstat starts reporting heavy paging, memory climbs very quickly.

On Thursday 15 June 2006 09:15, jody brownell wrote:
> 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(dot)brownell(at)q1labs(dot)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
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>