Yo,
I had one of these aweful problems that you know you have an issue or issues but you have absolutely no idea what/how and why it is happening. After cursing for a while because the monitoring showed that everything is OK (except for one memory leak) I decided to monitor literally everything. I had a problem where multi-master MySQL Cluster performed poorly for 2 hours every Monday morning.
So, firstly, I checked all system parameters – RAM, CPU, hdd. All good.
Then I checked all crontabs. I walked through all scheduled scripts that are set to run Sunday evening or Monday morning. Again simple tasks that I ran manually and they took <1 sec.
I checked for deadlocks but I knew that’s not the problem. If there was a deadlock, the whole DB whould have been frozen. So .. yeah. The last thing that came up on my mind – processlist of queries. That’s where pt-stalk (Percona utility) stepped in. That’s how I discovered where my issue came from. I had so many locked queries .. But never midn, I wrote the whole thing just to provide you with the script I used.
wget http://bit.ly/1ltoZtk -O pt-stalk
chmod +x pt-stalk
mkdir -p /var/lib/pt-stalk/
/usr/bin/pt-stalk –password=PASS –daemonize –notify-by-email <EMAIL> #if you want, not neccessary# –log /var/log/pt-stalk.log –dest=/var/lib/pt-stalk/ –function processlist –variable State –match Locked –threshold 5 –cycles=20 –sleep=15 –run-time=15