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?
Best Answer
Since
sv
is a MyISAM table, I would expect a backlog of DB Connections attempting to write to the table in a high-traffic environment.In fact, any DML (INSERT, UPDATE, DELETE) against the
sv
table will result in a full table lock onsv
.You should still run a
CHECK TABLE sv;
as a precaution.Please keep in mind that data for MyISAM tables are never cached. It is possible that the OS is just swapping used memory as a result of the heavy activity against the MyISAM.
All you can really do in that instance is reboot the OS