Need HELP: Server hang

[Kenneth Tiller]

Version: 5.0.51a (upgrading to 5.0.67 on monday)

Our production database server has started to hang daily, and we can't seem to find the reason. There are usually about 40-80 threads executing when the server stops responding. There are different queries that show up as the ones with longest executing times each time we are down, Usually there are 4-5 queries that show long "Time" in the process list, with one of them(not the same each time) being there multiple times. (Indicating that a user has hit the refresh button several times in our web application) The "Command" is either Execute, Query, Copying to tmp table or *** DEAD ***. (The final one caused by a bug in our version, that state isn't an offical state). It seems that the mysql server can't handle more queries for some reason. The machine itself has lots of free CPU and memory when the mysql server stops repsonding. How do we find out what the problem is and where to begin?

We can't pinpoint our problem to be one exact query, so we're looking to find out what the state of our system is when it hangs, so that we can locate the causes to these problems. We hope to solve these problems with a combination of tuning(my.ini), indexing and possibly rewriting some sqls.

We really appreciate help on this one, as our customers are starting to tire from too much downtime lately!



Edited 1 time(s). Last edit at 01/15/2009 06:30AM by Kenneth Tiller.