DELETE query failure on mysql

Question

We just now ran a delete query on one of our mysql tables having 54 MM rows. The query was supposed to delete around 40 MM records. It ran for about 2 hours and after that the table state has not changed. Doing show table status is still showing the same number of rows as before running the delete query. The table engine is InnoDb.

The table is pretty much unusable now. Simple select queries are hanging on that table. Any idea what might have gone wrong ?

Answer 1

A few ideas and references based on the little information in the question:

• You say it ran for 2 hours. Did it actually finish the delete operation with no error messages or is the job still running? If it is still running it may explain why SELECT queries are hanging. If it is still running I would just let run for the next day or so barring any need to immediately use the database (consider the time it would take to completely restore it).
• Check your MySQL error log for any relevant messages.
• You have a backup from before the DELETE operation right? If not, consider it a lesson to always have a backup before you start fiddling with things (a lesson that I had to learn the hard way).
• See the MySQL page on delete speed and the delete syntax for numerous tips on getting faster delete operations.
• When deleting, try disabling all indices on the table first, delete, then rebuild.
• Try deleting fewer rows at a time.
• Instead of deleting 44M rows copy the 14M rows you want into a new table, drop the old table, rename new table.