List:General Discussion« Previous MessageNext Message »
From:Carsten Pedersen Date:April 14 2010 9:02pm
Subject:Re: Make delete requests without impact on a database
View as plain text  
mos skrev:
> At 01:20 PM 4/14/2010, Carsten Pedersen wrote:
>> Been there, done that. It's a maintenance nightmare.
> 
> Why is it a maintenance nightmare? I've been using this technique for a 
> couple of years to store large amounts of data and it has been working 
> just fine. 

In a previous reply, you mentioned splitting the tables on a daily 
basis, not yearly. Enormous difference. It's one thing to fiddle with a 
set of merge table once a year to create a new instance. Quite another 
when it's to be done every day. If you want to change the table 
structure, you'll have to do that on every single one of the underlying 
tables. That might be fine for 5 year-tables, but not fun if you need to 
do it for hundreds of tables.

If your merge table consists of 30 underlying tables*, a search in the 
table will result in 30 separate searches, one per table. Also, MySQL 
will need one file descriptor per underlying table *per client accessing 
that table*. Plus one shared file descriptor per index file. So if 30 
clients are accessing a merge table that consists of 30 days worth of 
data, that's 930 file descriptors for the OS to keep track of. Clearly, 
this doesn't scale well.

*Approx 1 month in your suggested solution, which also fits with OP 
saying that ~90k of about ~3.2 mio get deleted every day.

/ Carsten

Thread
Make delete requests without impact on a databaseDavid Florella14 Apr
  • Re: Make delete requests without impact on a databaseDan Nelson14 Apr
    • RE: Make delete requests without impact on a databaseDaevid Vincent15 Apr
    • Re: Make delete requests without impact on a databasePerrin Harkins15 Apr
      • RE: Make delete requests without impact on a databaseDavid Florella15 Apr
  • Re: Make delete requests without impact on a databasemos14 Apr
    • Re: Make delete requests without impact on a databaseCarsten Pedersen14 Apr
      • Re: Make delete requests without impact on a databasemos14 Apr
        • Re: Make delete requests without impact on a databaseCarsten Pedersen14 Apr