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
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
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]