To: pgsql-performance@postgresql.org
connect(): No such file or directory
From: Mark Stosberg <mark@summersault.com>
Subject: debugging handle exhaustion and 15 min/ 5mil row delete
Date: Fri, 7 May 2010 09:37:42 -0400
Lines: 46
Message-ID: <20100507093742.20c715b1@summersault.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
X-Complaints-To: usenet@dough.gmane.org
X-Gmane-NNTP-Posting-Host: frigga.summersault.com
X-Newsreader: Claws Mail 3.7.5 (GTK+ 2.12.9; i486-pc-linux-gnu)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201005/19
X-Sequence-Number: 38603


Hello,

We've been a satified user of PostgreSQL for several years, and use it
to power a national pet adoption website: http://www.adoptapet.com/

Recently we've had a regularly-timed middle-of-the-night problem where
database handles are exhausted for a very brief period.

In tracking it down, I have found that the event seems to correspond to
a time when a cron script is deleting from a large logging table, but
I'm not certain if this is the cause or a correlation.

We are deleting about 5 million rows from a time-based logging table
that is replicated by Slony. We are currently using a single delete
statement, which takes about 15 minutes to run. There is no RI on the
table, but the use of Slony means that a trigger call and action is made
for every row deleted, which causes a corresponding insertion in another
table so the deletion can be replicated to the slave.

My questions:

- Could this kind of activity lead to an upward spiral in database
  handle usage?

- Would it be advisable to use several small DELETE statements instead,
  to delete rows in batches of 1,000. We could use the recipe for this
  that was posted earlier to this list:

  delete from table where pk in
    (select pk from table where delete_condition limit X);

Partitions seems attractive here, but aren't easy to use Slony. Perhaps
once we migrate to PostgreSQL 9.0 and the hot standby feature we can
consider that.

Thanks for your help!

    Mark

 . . . . . . . . . . . . . . . . . . . . . . . . . . . 
   Mark Stosberg            Principal Developer  
   mark@summersault.com     Summersault, LLC     
   765-939-9301 ext 202     database driven websites
 . . . . . http://www.summersault.com/ . . . . . . . .