X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 1EA6630F252 for ; Tue, 17 Jun 2003 21:44:05 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 92944-02 for ; Tue, 17 Jun 2003 18:43:53 -0300 (ADT) Received: from smaug.securitymatrix.com (unknown [64.25.5.177]) by svr1.postgresql.org (Postfix) with ESMTP id C371330F241 for ; Tue, 17 Jun 2003 18:43:52 -0300 (ADT) Received: from kaos.securitymatrix.com ([172.16.0.101]) by smaug.securitymatrix.com (8.11.6/8.11.6) with ESMTP id h5HLhEV22868; Tue, 17 Jun 2003 17:43:14 -0400 Subject: Re: Postgres Connections Requiring Large Amounts of Memory From: Dawn Hollingsworth To: Tom Lane Cc: pgsql-performance@postgresql.org, Ben Scherrey In-Reply-To: <14875.1055878682@sss.pgh.pa.us> References: <1055752337.2182.49.camel@kaos> <6634.1055796302@sss.pgh.pa.us> <1055753856.5467.57.camel@kaos> <6892.1055799269@sss.pgh.pa.us> <1055832411.5467.100.camel@kaos> <14875.1055878682@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="=-Lq5FzIe2W5aN0kK/fEWI" X-Mailer: Ximian Evolution 1.0.8 Date: 17 Jun 2003 09:42:07 +0000 Message-Id: <1055842927.2182.227.camel@kaos> Mime-Version: 1.0 X-Archive-Number: 200306/115 X-Sequence-Number: 2199 --=-Lq5FzIe2W5aN0kK/fEWI Content-Type: text/plain Content-Transfer-Encoding: 7bit The database is used to store information for a network management application. Almost all the Primary Keys are MACADDR or MACADDR,TIMSTAMPTZ and the Foreign Keys are almost always on one MACADDR column with "ON UPDATE CASCADE ON DELETE CASCADE". It's not very complicated. I have not written any triggers of my own. The connection I was looking at only does inserts and updates, no deletes. All database access is made through stored procedures using plpgsql. The stored procedures all work like: table1( id MACADDR, ... Primary Key(id) ) table2( id MACADDR, mytime TIMESTAMPTZ, .... Primary Key(id, mytime), FOREIGN KEY(id) REFERENCES table1 ON UPDATE CASCADE ON DELETE CASCADE) Update table1 if update row count = 0 then insert into table1 end if insert into table 2 I'm not starting any of my own transactions and I'm not calling stored procedures from withing stored procedures. The stored procedures do have large parameters lists, up to 100. The tables are from 300 to 500 columns. 90% of the columns are either INT4 or INT8. Some of these tables are inherited. Could that be causing problems? - Dawn > Hmm. This only seems to account for about 5 meg of space, which means > either that lots of space is being used and released, or that the leak > is coming from direct malloc calls rather than palloc. I doubt the > latter though; we don't use too many direct malloc calls. > > On the former theory, could it be something like updating a large > number of tuples in one transaction in a table with foreign keys? > The pending-triggers list could have swelled up and then gone away > again. > > The large number of SPI Plan contexts seems a tad fishy, and even more > so the fact that some of them are rather large. They still only account > for a couple of meg, so they aren't directly the problem, but perhaps > they are related to the problem. I presume these came from either > foreign-key triggers or something you've written in PL functions. Can > you tell us more about what you use in that line? > > regards, tom lane --=-Lq5FzIe2W5aN0kK/fEWI Content-Type: text/html; charset=utf-8
The database is used to store information for a network management application. Almost all the Primary Keys are MACADDR or MACADDR,TIMSTAMPTZ and the Foreign Keys are almost always on one MACADDR column with "ON UPDATE CASCADE ON DELETE CASCADE".   It's not very complicated. I have not written any triggers of my own.

The connection I was looking at only does inserts and updates, no deletes. All database access is made through stored procedures using plpgsql.  The stored procedures all work like:
table1( id MACADDR, ... Primary Key(id) )
table2( id MACADDR, mytime TIMESTAMPTZ, .... Primary Key(id, mytime), FOREIGN KEY(id) REFERENCES table1 ON UPDATE CASCADE ON DELETE CASCADE)

Update table1
if update row count = 0 then
   insert into table1
end if

insert into table 2

I'm not starting any of my own transactions and I'm not calling stored procedures from withing stored procedures. The stored procedures do have large parameters lists, up to 100. The tables are from 300 to 500 columns. 90% of the columns are either INT4 or INT8.  Some of these tables are inherited. Could that be causing problems?


- Dawn
> Hmm.  This only seems to account for about 5 meg of space, which means
> either that lots of space is being used and released, or that the leak
> is coming from direct malloc calls rather than palloc.  I doubt the
> latter though; we don't use too many direct malloc calls.
> 
> On the former theory, could it be something like updating a large
> number of tuples in one transaction in a table with foreign keys?
> The pending-triggers list could have swelled up and then gone away
> again.
> 
> The large number of SPI Plan contexts seems a tad fishy, and even more
> so the fact that some of them are rather large.  They still only account
> for a couple of meg, so they aren't directly the problem, but perhaps
> they are related to the problem.  I presume these came from either
> foreign-key triggers or something you've written in PL functions.  Can
> you tell us more about what you use in that line?
> 
> 			regards, tom lane

--=-Lq5FzIe2W5aN0kK/fEWI--