There are few easy ticks to see what is stuck inside MySQL instance. All these techniques will not give you whole picture, but might help to find queries that block performance. Let’s start from what exactly doing your MySQL server right now.
Which queries are running now?
This will give you an idea what’s running right now so you can find long running queries which slowing down MySQL and/or causing replication lag:
mysql -e "SHOW PROCESSLIST" | grep -v -i "sleep"
It is more convenient than just run “SHOW PROCESSLIST” as it’s hiding all connected threads in “Sleep” state so you’ll get a clean output. Also you can get same output but updating each second:
watch -n1 'mysql -e "SHOW FULL PROCESSLIST" | grep -v -i "Sleep"'
What to look for? This is complex output but you can start with Time and State columns. When you see a query running for more …[Read more]