Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Hi Somal, welcome to the site! Does the SHOW FULL PROCESSLIST and mysql's slow query log shed any light? –  Derek Downey Feb 7 '12 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. –  Somal Somalski Feb 7 '12 at 17:53

1 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

share|improve this answer
    
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. –  Somal Somalski Feb 7 '12 at 17:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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