Thank you Daniel. You are shedding more light on this. I can see that the tweaking hasn't really done much since I had to reboot over the weekend and today. Well, over the weekend so I could get back to painting I simply rebooted but normally I can issue "FLUSH TABLES" and the problem will go away.
I'm sure 100% key hit rate is a good thing, however, what I notice is that during the problem the hit rate is continuously at 100%, it never falls down. Whereas in normal operations it jumps up and down as queries come in.
Here is the image I had on page one, this demonstrates in a short space how before "FLUSH TABLES" the key hit rate was 100% and then afterwards it hops up and down in normal operation.
http://www.fromtheduke.com/mysql100.jpg
Perhaps it's simply showing what is going on and not anything that can help fix it, that a query is stuck somehow and so it's stuck at 100% hit rate until that query is released.
Here is how i know the server is having problems: I have a script I saw an example of in the user notes of the "Kill" command. Basically I run a script that looks at queries that have been running over 600 seconds and it kills them. I will get, for example today, about e-mails reporting on the queries that were killed. Every 5 minutes so I know that every five minutes queries are stuck and I need to go in and flush tables.
Now, perhaps, instead of me going in and flushing tables, perhaps I should retool the script simply to flush tables for me. That way the server may just continue running, eh? If that's the work-around I need then I will do it, I simply was hoping that I wouldn't need to do that and actually find out what is wrong.
I know enough to be dangerous. Thanks for being patient!
