Re: Big insert/delete memory problems - Mailing list pgsql-general

Just to follow up on this thread, I found the problem(s).  JDBC2
connection pooling AND that pre-compiled version on RedHat.  After
switching to my compiled version, I was still seeing memory leaking from
the postgresql processes.  BUT- it wasn't as bad as it was with the
precompiled version.  It was still leaking too much for me to be
comfortable with though, so I stopped doing connection pooling.  Its a
bit slower, especially with DB connection intensive operations, but now
postgresql takes up less memory than my apache processes!  WOOOO HOOOOO!
   So the problem appears to be that Jdbc2 connection pooling makes
postgresql leak, at least on RedHat 7.3.   Thanks Tom!


/kurt


Kurt Overberg wrote:
> Hi all,  I have a kinda weird postgres sql question for everyone.  When
> running the following SQL commands:
>
>
> delete from xrefmembergroup where membergroupid = 2 ; select 1 as true;
>
> insert into xrefmembergroup (membergroupid, memberid) select 2 as
> membergroupid, member.id as memberid from member where  ((extract(year
> from age(birthdate))) >= '17' ); select 1 as true;
>
> ...my memory usage goes nuts- allocates 20-30 Mb per time I run this.
> After it finishes these commands, the memory usage does not go back
> down, so after awhile, memory usage becomes a problem.   I would
> estimate that the xrefmembergroup table has about 20,000-30,000 rows in
> it, and I'm deleting/inserting about 5000 rows at a time with these
> commands, which run VERY fast.
>
> Here's the table definition:
>
>     Column     |           Type           | Modifiers
> ---------------+--------------------------+------------------------------------------------------------
>
>  id            | integer                  | not null default
> nextval('"xrefmembergroup_id_seq"'::text)
>  membergroupid | integer                  | not null default 0
>  memberid      | integer                  | not null default 0
>  timestamp     | timestamp with time zone | default
> "timestamp"('now'::text)
> Indexes: xrefmembergroup_pkey primary key btree (id),
>          membergroupid_xrefmembergroup_key btree (membergroupid),
>          memberid_xrefmembergroup_key btree (memberid)
>
>
> Is this an excessive delete/insert?  Am I breaking 'good form' here?
> Thanks for any help!
>
> /kurt
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>