Approximately how long should it take to delete 10m records from an MySQL InnoDB table with 30m records?

Question

I am deleting approximately 1/3 of the records in a table using the query:

DELETE FROM `abc` LIMIT 10680000;

The query appears in the processlist with the state "updating". There are 30m records in total. The table has 5 columns and two indexes, and when dumped to SQL the file about 9GB.

This is the only database and table in MySQL.

This is running on a machine with 2GB of memory, a 3 GHz quad-core processor and a fast SAS disk. MySQL is not performing any reads or writes other than this DELETE operation. No other "heavy" processes are running on the machine.

This query has been running for more than 2 hours -- how long can I expect it to take?

Thanks for the help! I'm pretty new to MySQL, so any tidbits about what's happening "under the hood" while running this query are definitely appreciated.

Let me know if I can provide any other information that would be pertinent.

Update: I just ran a COUNT(*), and in 2 hours, it's only deleted 200k records. I think I'm going to take Joe Enos' advice and see how well inserting the data into a new table and dropping the previous table performs.

Update 2: Sorry, I actually misread the number. In 2 hours, it's not deleted anything. I'm confused. Any suggestions?

Update 3: I ended up using mysqldump with --where "true LIMIT 10680000,31622302" and then importing the data into a new table. I then deleted the old table and renamed the new one. This took just over half an hour.

Answer 1

Don't know if this would be any better, but it might be worth thinking about doing the following: Create a new table and insert 2/3 of the original table into the new one. Drop the original table. Rename the new table to the original table's name.

This would prevent the log file from having all the deletes, but I don't know if inserting 20m records is faster than deleting 10m.

Answer 2

You should post the table definition. Also, to know why is it taking to much time, try to enable the profile mode on the delete request via :

SET profiling=1; 
DELETE FROM abc LIMIT 10680000;
SET profiling=0;
SHOW PROFILES;
SHOW PROFILE ALL FOR QUERY X; (X is the ID of your query shown in SHOW PROFILES)

and post what it returns (But I think the query must end to return the profiling data)

http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html

Also, I think you'll get more responses on ServerFault ;)