Posted this on the official forums a week ago and didn’t get a response, hopefully I’ll have more luck here, or if my question doesn’t provide enough information please let me know and I’ll give whatever I can.
MySQL Version: 5.0.45-7.el5 (current latest for RHEL5)
Every so often (it can be anything from a few hours to a few weeks), my MySQL process on a dedicated DB machine jumps to 100% CPU usage and stays there until restarted.
At first I thought it was my application recursing queries to the DB server but I shut all webservers down and left the DB running for an hour and the CPU usage did not let up. This leads me to believe that its a single (or small number) of bad queries causing problems within MySQL.
My problem is that I don’t know where to start to diagnose this.
I’ve enabled log-slow-queries of course but nothing shows up at all. If the application doesn’t run or has low usage, the problem doesn’t occur so I’m reasonably sure its some queries by the application… but its a big big app with many queries during normal usage, can’t really log all the queries at an app level and expect to be able to sift through them to find a problem (especially since it looks like this is a rarely called query given that it doesnt happen too often).
Anyway, any ideas how I can go about solving this?