Horton
Menu

Thread

  1. Re: debugging handle exhaustion and 15 min/ 5mil row delete

    Ing. Marcos Ortiz Valmaseda <mlortiz@uci.cu> — 2010-05-07T07:44:09Z

    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

  2. Re: debugging handle exhaustion and 15 min/ 5mil row delete

    Ing. Marcos Ortiz Valmaseda <mlortiz@uci.cu> — 2010-05-07T13:22:58Z

    El 07/05/2010 16:10, Mark Stosberg escribió:
>    
>> You can use TRUNCATE instead DELETE. TRUNCATE is more efficient and
>> faster that DELETE.
>>      
> Thanks for the suggestion. However, TRUNCATE is not compatible with
> Slony, and we also have some rows which remain in table.
>
>    
>> Now, we need more information about your system to give you a certain
>> solution:
>> Are you using a RAID controller for you data?
>>      
> Yes.
>
>    
>> Do you have separated the xlog directory from the data directory?
>>      
> No.
>
>    
>> Which is your Operating System?
>>      
> FreeBSD.
>
>    
>> Which is you architecture?
>>      
> i386.
>
> Thanks for the feedback. I'm going to try batching the deletes for now,
> which is approach was worked well for some of our other long-running
> deletes.
>
>      Mark
>
>    
Have you valorated to use a 64 bits version of FreeBSD for that?
The 64 bits OS can help you very much on large databases because yo can 
use actually all available RAM that you have on the server.

Many experts on this list recommende to separate the xlog directory on a 
RAID 1 configuration and the data directory on RAID 10 to obtain a 
better performance.
The filesystems are very diverse, but I ´ve seen that ZFS is very useful 
on these cases.

Which version of Slony-I are you using?

Regards

  3. debugging handle exhaustion and 15 min/ 5mil row delete

    Mark Stosberg <mark@summersault.com> — 2010-05-07T13:37:42Z

    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/ . . . . . . . .

  4. Re: debugging handle exhaustion and 15 min/ 5mil row delete

    Kenneth Marshall <ktm@rice.edu> — 2010-05-07T13:42:15Z

    On Fri, May 07, 2010 at 09:37:42AM -0400, Mark Stosberg wrote:
> 
> 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?
Yes.
> 
> - 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:
Yes, that is the method we use in several cases to avoid this behavior.
Deletion is a more intensive process in PostgreSQL, so batching it will
keep from dragging down other queries which results in your out-of-handles
error.

Regards,
Ken

  5. Re: debugging handle exhaustion and 15 min/ 5mil row delete

    Mark Stosberg <mark@summersault.com> — 2010-05-07T14:10:31Z

    > You can use TRUNCATE instead DELETE. TRUNCATE is more efficient and 
> faster that DELETE.

Thanks for the suggestion. However, TRUNCATE is not compatible with
Slony, and we also have some rows which remain in table. 

> Now, we need more information about your system to give you a certain 
> solution:
> Are you using a RAID controller for you data? 

Yes.

> Do you have separated the xlog directory from the data directory?

No.

> Which is your Operating System?

FreeBSD.

> Which is you architecture?

i386.

Thanks for the feedback. I'm going to try batching the deletes for now,
which is approach was worked well for some of our other long-running
deletes.

    Mark