2

I have a innodb table that counts circa 1 billion entries. I started a delete operation (with a small where condition on an indexed column) that should delete a lot entries, leaving circa 100 million. The delete operation runs for 2 days now and I got an error "Lost connetcion to mysql server during query" a few hours ago. The mysql processlist command shows that delete operation with status "Updating", so it seems like the query is still running though the command line has shown me this error. How can I trace that down? I basically don't know if it is still deleting or got stuck. I am afraid killing the process because I do not want to trigger a rollback.

  • Show us the query, the indexes defined on it and the execution plan. – a_horse_with_no_name Mar 31 '14 at 18:02
  • Good point. Explain shows me that the query is done without using an index. Urgh. That explains why the query is so slow. The question is: Kill it or don't kill it. How can I decide? The query looks like "DELETE FROM table WHERE field='one' or field='two' or field='three'... – Starty Mar 31 '14 at 18:07
  • No, it's slow because it's trying to delete about 900M rows, and in a single transaction. That takes time. It has to delete the 900M rows and also update all the table's indexes. – ypercubeᵀᴹ Mar 31 '14 at 18:09
  • 1
    And don't kill the query. – ypercubeᵀᴹ Mar 31 '14 at 18:11
  • Yeah. The point is that the error "Lost connection to mysql server during query" upsets me, you know? – Starty Mar 31 '14 at 18:11
5

If it's showing in your processlist then it's still running.

In the future, for situations like this we're you're pruning 90%+ of the table consider this approach:

create table new_table like old_table;
insert into new_table (select * from old_table where <what you want to keep>)
rename table old_table to old_table_drop_prep, new_table to old_table;
drop table old_table_drop_prep;
  • This is indeed one of the best approaches. – ddaniel Apr 1 '14 at 5:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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