Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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 ?

share|improve this question
    
Do you have any log entries? Error messages? –  SvW Mar 7 '11 at 20:25
    
Could you provide more information? –  Ency Mar 7 '11 at 20:48
    
would you like to consider asking me to move this over to our programming-related sister site stackoverflow.com? I won't do it unless you ask but perhaps you could take a look and let me know, I'm just not 100% sure this question is right for this site and wanted you to decide. –  Chopper3 Mar 7 '11 at 20:56
add comment

1 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.
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.