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