MySQL server hangs after approximately 1 month (CentOS)

Hey guys, I've got a web server running Apache/PHP and MySQL (CentOS), and MySQL seems to hang once every month or so. As far as I can tell, there are a few slow queries that are being resolved, but other than that I can't really see any reason why MySQL would hang. I'm having problems determining the problem - nothing is showing up in /var/log/mysqld.log, and again there are a few slow queries, but nothing out of the ordinary. Load is average at the time of the crashes...

Can I please get some hints on how to work out the issue? I can't reproduce on our staging environment, so I'm a little stuck.

From serverfault $pageUsers[$entry.posts[0].author].name

• Can you define "stuck" ? Ie, is it dead and not responding to any queries what so ever? Can you not even get a process list of what queries it is running?

  Or is it chewing huge amounts of CPU/memory and just grinding the server to a halt or continually crashing or what?

  What state is the process in ? And can you strace the daemon process ?

  Probably need a bit more insight as to what it is doing to give any real good advice.

  Keiran Holloway : Sure, I can appreciate the doing diagnosis on a production machine is challenge at times. What you really need to communicate to the client, however, is that without doing sufficient diagnosis when the problem is actually occurring and simply restarting the server then the exact same problem is likely to occur again in the future. Given MySQL is chewing 100% CPU time, it is likely that there is a suspect query triggering the event. Remember, slow queries only get logged ONCE they are completed. You're not letting it complete.

  Kevin M : One thing you might be able to do is to have another mysql server in a slave configuration. Reboot that one daily. Then when the problem happens, swap in the slave so that it's now the primary. Then debug to your heart's content on the (formerly) production server.
  From Keiran Holloway

• You can try using strace or truss against the mysqld process when it happens.

  Another solution may be to schedule a weekly bounce of the database, in case this is not a bad query but a slow leak inside MySQL. Is your client 24x7?

  From Tom Kyle

• This isn't really an answer but a suggestion: Install a monitoring tool like Munin with the MySQL plugin and watch usage and memory, CPU, TCP connections, etc. leading up to and at the time of the crash. This has been invaluable to me in similar situations.

  Installing Munin on CentOS

  From Dave Forgac