Mysql takes up to 50% cpu after aborting DELETE query

Question

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?

Answer 1

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 on sv.

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