Roughly, how long will it take to remove 10 million records from a MySQL InnoDB table with 30 mm records?

Question

I delete about 1/3 of the entries in the table using the query:

DELETE FROM `abc` LIMIT 10680000; 

The request appears in the list of processes with a status update. A total of 30 million records. The table has 5 columns and two indexes, and when flushing to SQL, the file is about 9 GB.

This is the only database and table in MySQL.

It runs on a machine with 2 GB of memory, a quad-core 3 GHz processor and a fast SAS drive. MySQL does not perform any read or write operations except this DELETE operation. Other “heavy” processes do not work on the machine.

This query has been running for more than two hours - how much time can I expect from it?

Thanks for the help! I am new to MySQL, so any tidbits about what happens “under the hood” during the execution of this query are certainly appreciated.

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

Update: I just ran COUNT(*) , and after 2 hours, it deleted only 200 thousand records. I think I'm going to take the advice of Joe Enos and see how good it is to insert data into a new table and discard the previous table.

Update 2: Sorry, I'm actually reading the number incorrectly. After 2 hours, he did not delete anything . I'm confused. Any suggestions?

Update 3: I ended up using mysqldump with --where "true LIMIT 10680000,31622302" and then imported the data into a new table. Then I deleted the old table and renamed the new one. It took a little more than half an hour.

Answer 1

I don’t know if this will be better, but perhaps it’s worth considering the following: Create a new table and insert 2/3 of the original table into the new one. Drop the source table. Rename the new table to the original table name.

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

Answer 2

You must publish a table definition. Also, to find out why this is taking so long, try turning on profile mode in the delete request with:

 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 request should complete in order to return the profiling data)

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

Also, I think you will get more answers on ServerFault;)

Answer 3

When you run this query, the InnoDB database log file is used to record all the details of the rows to be deleted. If this log file is not large enough from the very beginning, it will automatically expand as and when necessary (if it is configured for this) - I am not familiar with the specifics, but I expect that this automatic extension will not be dazzlingly fast. 2 hours seems long - but it doesn't surprise me if the log file grows as the request completes.

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

Answer 4

I hope your request has ended so far ... :) but from what I saw, LIMIT with large numbers (and I never tried such numbers) is very slow. I would try something based on pk like

 DELETE FROM abc WHERE abc_pk < 10680000;