I have a problem I have never met before. I was clearing yesterday client's db and table with stats of visits that had 15GB of data. I decided to delete all rows older than 1 Feb, because we have all that data stored in daily stats and there's no need to keep those old single stats.
I tried to run DELETE FROM sv WHERE datetime < ?
(index on datetime) and DELETE FROM sv WHERE id < ?
(primary index on id), but both queries where too slow. They took more than 15 mins. So I have just aborted those queries by Ctrl+C.
Then I thought a better idea would be copying the table structure and February rows and remove the old table. But today I found out that mysql process on server is running up to 50% cpu usage with normal traffic on our site.
After moving the sv table out of the database folder (MyISAM table) usage is still up to 50%.
First I thought that table must have been corrupted, but phpmyadmin does not show it marked as crashed.
How can I debug this one? What have happened there?
SHOW FULL PROCESSLIST
and mysql's slow query log shed any light?