pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dawn Hollingsworth <dmh@airdefense.net>
Cc: pgsql-performance@postgresql.org, Ben Scherrey <scherrey@proteus-tech.com>
Subject: Re: Postgres Connections Requiring Large Amounts of Memory
Date: Tue, 17 Jun 2003 18:03:45 -0400
Message-ID: <15851.1055887425@sss.pgh.pa.us> (raw)
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>

Dawn Hollingsworth <dmh@airdefense.net> 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



view thread (11+ messages)  latest in thread

Message-ID: <15851.1055887425@sss.pgh.pa.us>
Permalink:  ../15851.1055887425@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/15851.1055887425@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, dmh@airdefense.net, scherrey@proteus-tech.com
  Subject: Re: Postgres Connections Requiring Large Amounts of Memory
  In-Reply-To: <15851.1055887425@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> <1055842927.2182.227.camel@kaos> <15851.1055887425@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox