Nothing showing up in the logs for the time period where the reversion was believed to have happened?
No one ran an operation that deleted records after a certain date?
Are you looking at raw data from the database or from an application that may be filtering the output so you don't see things after a certain date?
Any data restore from backup run, anyone playing with filesystem snapshots?
Any scripts running to make backups that could have hiccuped?
Who else has access to that directory with rights to copy/modify programs? You said it wasn't a production database, so are developers playing with the server, with access to version control that could have done something to the file?
Filesystem errors showing up? Was it just the database files that were affected, no other system or user data? Logfiles?
You could play some games with the timeout values in MySQL.
For example, the default value for 'wait_timeout' and 'interactive_timeout' is 28800 (that's 8 hours)
You can see what they are set to by running this:
SHOW VARIABLES LIKE 'interactive_timeout';<BR>
SHOW VARIABLES LIKE 'wait_timeout';
If you want to lower these to, say, 1 minute, a MySQL restart is not required.
Run these as the root user:
SET GLOBAL interactive_timeout=60;<BR>
SET GLOBAL wait_timeout=60;
This will assure that any new MySQL connections will timeout in 60 seconds.
then add these lines to /etc/my.cnf under the [mysqld] section
interactive_timeout=60
wait_timeout=60
Of course, it is easier to restart mysql to remove the remaining sleeping connections. All connections, going forward from there, will timeout in 60 seconds.
Give it a try and let us know !!!
Best Answer
There are a lot of factors that could be the cause of this.
Slow resolver shouldn't have an impact if you can measure queries by themselves. However, a slow resolver can definitely have an effect when connecting to the server - if users get access based on the hostname they connect from. MySQL also have an option for logging the hostname of connections.
The mysql-report doesn't say much about indexes. What I usually do when I see this happening, is that I take one of the queries that takes a long time, and I run
EXPLAINon it. If it doesn't use any indexes, and needs to do a full table scan - I'd look to see if I'm able to add a index that would make it faster. I've seen indexes disappear by accident before (someone deleted it by accident, an upgrade script deleted it and didn't put it back afterwards, or similar).The server could be overloaded in lots of ways:
It could also be a series of queries which locks the table - preventing the lookups from occurring in ~10 seconds at a time. You can get information about that if you log slow queries to a file.
It could be a bug in MySQL. There is a quite heavy burden on proving that, but it happens.
You need much more data to figure out the cause of this. You need metrics of CPU, memory, MySQL metrics and such. May I suggest a monitoring tool such as Munin? There is a very good MySQL plugin that will give you interesting data as it happens.