Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Using mySQL 5.0.51 on Solaris .

At intermittent times it looks like MySQL is getting 'stuck' . The disk usage on the server spikes to 98% busy from reads. I used dtrace (specifically DTrace toolkit - iosnoop) to track down what processes was using all the reads. Mysql was calling tablename.TDM hundreds of times per second. There was no more than average load on the webserver that could account for this. There were no cronjobs running, and no other utilities like mysqldump or anything. It is a master / slave replication setup.

As a jury-rigged fix, I altered the mysql table from 'tablename' to 'tablename2' and then back to 'tablename'

This fixed the problem temporarily, and "unsticks" mysql. The disk usage goes back down and dtrace is no longer showing hundreds of reads to 'tablename.TDM' / second.

A couple ideas I had are:

1. MySQL version bug 
2. Infinite loop somewhere in my application (which i'm not sure how likely this is) 
3. ?? 

Has anybody seen this before or have any insight?

Thanks

share|improve this question
2  
Have you checked your slow query log? Is it enabled? –  Zoredache Mar 10 '10 at 17:44
    
Didn't have it enabled, will enable it tonight during slow hours –  bonez05 Mar 10 '10 at 19:58

1 Answer 1

Uh, renaming tables randomly is a horrible idea.

Look at the MySQL process list to determine what processes are loading the system. Use:

show full processlist;

There's also utilities like mytop and mtop that are handy.

Edit 1

Isolating is going to depend on your environment. Where are the connections from?

If localhost can it only be a system script or do you have an application being served from your DB?

If remote host, what function does that remote host serve? Is it different from others? Does it have scripts or is it only an application?

It should be fairly quick and simple to isolate the source but not necessarily the cause.

share|improve this answer
    
def. agree with not renaming tables, but had to do something. will get mytop. Thanks –  bonez05 Mar 10 '10 at 16:58
    
Connecting from DB server so essential localhost, i also have to webservers attached to the db server, which are running mysql connections and apache. but i'm doing my debugging right from the db server itself. as a side note the table in question has 600,000 records, which i think mysql should be able to handle, but maybe this is an issue –  bonez05 Mar 10 '10 at 18:32
    
That makes it more difficult to isolate from a connection perspective. If you're running unoptimized queries against an improperly indexed table, it's definitely plausible that is the source of the problem. Zoredache provided a good recommendation as well. –  Warner Mar 10 '10 at 18:47

Your Answer

 
discard

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