From: Marcos Ortiz <mlortiz@uci.cu>
To: Mark Stosberg <mark@summersault.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: debugging handle exhaustion and 15 min/ 5mil row delete
Date: Fri, 07 May 2010 09:44:09 +0200
Message-ID: <4BE3C4C9.60601@uci.cu> (raw)
In-Reply-To: <20100507093742.20c715b1@summersault.com>
References: <20100507093742.20c715b1@summersault.com>
El 07/05/2010 15:37, Mark Stosberg escribió:
> 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/ . . . . . . . .
>
>
>
>
You can use TRUNCATE instead DELETE. TRUNCATE is more efficient and
faster that DELETE.
Now, we need more information about your system to give you a certain
solution:
Are you using a RAID controller for you data?
Do you have separated the xlog directory from the data directory?
Which is your Operating System?
Which is you architecture?
Regards
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mlortiz@uci.cu, mark@summersault.com
Subject: Re: debugging handle exhaustion and 15 min/ 5mil row delete
In-Reply-To: <4BE3C4C9.60601@uci.cu>
* 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