3

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?

2
  • Hi Somal, welcome to the site! Does the SHOW FULL PROCESSLIST and mysql's slow query log shed any light? Feb 7, 2012 at 16:41
  • Not really. Nothing unusual there. However this got fixed by my customers admins. I will try to get to know what really they fixed. I am only PHP Programmer, not mySql expert. Feb 7, 2012 at 17:53

1 Answer 1

2

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

1
  • I know about full table lock. Would you recommend to use innodb instead? In sv we store info about each visit on site (15k - 25k per day) and then we recount this in cron and store values for each day in another table. Most important thing is that summing and counting queries will be quick. Feb 7, 2012 at 17:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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