List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:April 14 2010 11:15pm
Subject:RE: Make delete requests without impact on a database
View as plain text  
> -----Original Message-----
> From: Dan Nelson [mailto:dnelson@stripped] 
> Sent: Wednesday, April 14, 2010 7:23 AM
> To: David Florella
> Cc: mysql@stripped
> Subject: Re: Make delete requests without impact on a database
> 
> In the last episode (Apr 14), David Florella said:
> > I am using MySQL version 4.1.12-log. All the databases on 
> it are using
> > MyISAM database engine.
> > 
> > Every day, I delete almost 90000 rows on a table of 3 153 916 rows.
> > 
> > To delete the rows, I use a request like this : "DELETE 
> QUICK FROM [table]
> > WHERE [column] < '2010-04-13 00:00:00' LIMIT 7500".  I execute this
> > request until all the rows are delete.
> > 
> > This works but when I run the request, I can't access to 
> the database
> > (make INSERT and SELECT requests) during I do the DELETE.
> > 
> > How can I do a "DELETE" without impact on INSERT and SELECT 
> requests done
> > on the same time?
> 
> Switch to InnoDB :)  The MyISAM engine has to lock the entire 
> table during
> write queries, so all queries have to wait for slow 
> UPDATE/INSERT/DELETE
> calls to complete.  An alternative would be to lower your 
> LIMIT even more;
> say to 1000.  Then you'll do 90 very small deletes instead of 
> 12 smallish
> ones.  Hopefully you're doing this loop in a program somewhere and not
> manually running the deletes from a mysql CLI prompt...  You 
> may also want
> to add the LOW_PRIORITY keyword to your DELETE statement; 
> that will keep the
> DELETE from moving to the front of the queue if there are other SELECT
> statements pending.
> 
> http://dev.mysql.com/doc/refman/5.1/en/delete.html

Another option to try is make a new column called "purge", then instead of
DELETE, use UPDATE to set the flag to 1 or something. Then at night or when
you have a slow time, run your DELETE WHERE `purge` = 1;

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