List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:April 14 2010 2:22pm
Subject:Re: Make delete requests without impact on a database
View as plain text  
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

-- 
	Dan Nelson
	dnelson@stripped
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