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 D835C30EF3E for ; Tue, 17 Jun 2003 22:03:59 +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 87352-09 for ; Tue, 17 Jun 2003 19:03:49 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 2A17030EEDB for ; Tue, 17 Jun 2003 19:03:48 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h5HM3kU6015852; Tue, 17 Jun 2003 18:03:46 -0400 (EDT) To: Dawn Hollingsworth Cc: pgsql-performance@postgresql.org, Ben Scherrey Subject: Re: Postgres Connections Requiring Large Amounts of Memory In-reply-to: <1055842927.2182.227.camel@kaos> 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> <1055842927.2182.227.camel@kaos> Comments: In-reply-to Dawn Hollingsworth message dated "17 Jun 2003 09:42:07 -0000" Date: Tue, 17 Jun 2003 18:03:45 -0400 Message-ID: <15851.1055887425@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200306/116 X-Sequence-Number: 2200 Dawn Hollingsworth writes: > 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? The only theory I can come up with is that the deferred trigger list is getting out of hand. Since you have foreign keys in all the tables, each insert or update is going to add a trigger event to the list of stuff to check at commit. The event entries aren't real large but they could add up if you insert or update a lot of stuff in a single transaction. How many rows do you process per transaction? regards, tom lane