Monday, January 11, 2016

An alternative to 'top' for Mysql

I think that one of my most used tools at work is 'top'.   It helps me figure out what is going on with a server very quickly.  While it's not perfect, it is a good all purpose tool. Unfortunately, there is no viable alternative that I've found yet that is prepackaged so I resort to using the following commands run from the shell:

for i in `seq 1 1000`;do clear; uptime;free -m ;mysql -uxxx -pxxx information_schema  -B -e "select * from PROCESSLIST where command != 'Sleep';" 2>/dev/null | grep -v 'from PROCESSLIST where ' ;sleep 4;done

Modify these commands to suit your needs -- I decided that updating every 4 seconds was reasonable enough for my needs and that displaying the memory info in MB works for me.

This allows me to monitor what a particular MySQL server is doing, hopefully helping me spot any long running queries at a glance with out relying any application debugging support or MySQL's general_log.

It may not be as pretty or polished as iotop or top, but it can give an insight into what is going with a MySQL server

The problem with relying on any particular applications debugging logs is that any access done directly by other MySQL users will not be logged.  MySQL's general log does capture everything and definitely has it's purpose both during application testing as well as during fault resolution in the field but it only reports on completed queries.

This solution does have some short comings, it doesn't capture every single query, nor does it show if a user is hammering the database with thousands of tiny queries every second but as an equivalent tool to top for peering into MySQL, this can get the job done.

No comments: