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

When you run this query, the InnoDB log file for the database is used to record all the details of the rows that are deleted - and if this log file isn't large enough from the outset it'll be auto-extended as and when necessary (if configured to do so) - I'm not familiar with the specifics but I expect this auto-extension is not blindingly fast. 2 hours does seem like a long time - but doesn't surprise me if the log file is growing as the query is running.

Is the table from which the records are being deleted on the end of a foreign key (i.e. does another table reference it through a FK constraint)?

Answer 2

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 3

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 ;)

Answer 4

I hope your query ended by now ... :) but from what I've seen, LIMIT with large numbers (and I never tried this kind of numbers) is very slow. I would try something based on the pk like

DELETE FROM abc WHERE abc_pk < 10680000;