I’ve got a mySQL server based in a Debian distro with updates etc, in a virtual (vmware) server.
Over time the slow queries log starts to get entries and it seems that the more it gets the slower mySQL is responding to all queries.
Shutdown and restart the server and it’s now 5 days without a single slow query entry. (of course, I could have buggered up the slow query logging, but probably not)
I have started trawling to find a possible solution, the now unmaintained blog Hack mySQL has this reporting process that I might want to come back to.
Yes, I came back to this and you may need to fiddle with the mysqld.sock location but otherwise this script works fine. Also use the prompt for your username and password if it is not root. The script appears to fall over with the following error:
ERROR 1317 (70100) at line 1: Query execution was interrupted
but in fact it is just thinking. Wait 5 minutes or so (I didnot time it) and it will complete.
The information about your config and resources may just point you at some setting to improve the performance.
However for me I have not used the tuning script to makes changes as yet.
Earlier in the week I was checking the DNS settings as a result of a thread (that I have lost the link to) which led me to read both the basic notes at MySQL on DNS and this entry at HackMySQL
Now I am not copying the information from those sites verbatim, but suffice to say that your MySQL server and the application server(s) making requests must all see each other correctly by hostname and ip address. If your hosts file (local) or DNS server (local or remote) have incorrect details it may result in regular misdirection of the responses from the database.
In my case I found a small mistake in the hosts file on the mySQL server that was misdirecting responses to the wrong server. i.e the app server was requesting but being found with a different location on reverse lookup. I made that change last weekend and this week we have 0 entries in the slow log and no user issues. (At least none we were told about).
On that basis I think it might be case closed.
So if your MySQL is slowing for no apparent reason check and double-check the name resolution forward and backward.