Innotop: A real-time, advanced investigation tool for MySQL

GUI monitoring tools for MySQL are not always suitable for all our needs or situations. Most of them are designed to provide historical views into what happens to our database over time rather then real-time insight into current MySQL server status. Excellent free tools for this include Cacti, Zabbix, Ganglia, Nagios, etc. But each of them needs to be properly configured to provide details on what is going on in our MySQL instances. And setting up one of these monitoring solutions is neither quick nor trivial (well, maybe with the exception of Ganglia).

MySQL Workbench provides a substitute for real-time MySQL status views, though it is very limited. It also needs to be installed on a workstation with a graphical environment.

The fact is that you won’t find many text-mode tools of this kind for MySQL. The most widely known are Innotop and mytop. The latter unfortunately seems to be an abandon project as the last significant update happened in 2007. Additionally, mytop offers way less functionality then Innotop.


Innotop can be really handy when you need a quick and easy tool that can provide a lot of details on what MySQL is doing – without using difficult queries to get those details manually. For our Percona Support customers, using Innotop will often be much easier then running complex SHOW statements and filtering their output in order to get some simple facts like slave replication lag, number of busy threads or InnoDB history list length. Also for us, Support engineers, it’s sometimes quicker to get a fast system overview with Innotop before we do deeper investigation using SHOW statements, Information_schema or more complex ways.

But Innotop is even more then that! In this article I will show what are my favorite features of this little tool, that you probably were not aware of. It is not my intention to show all the functionality here, so to get quick idea of the base tool capabilities, check the sample screenshots.


Let’s start from quick configuration examples to demonstrate how you can get the tool up and running in 5 minutes. Btw. I think configuration examples can be really useful as documentation is bit unclear about some of it’s aspects.

After we are successful with installing latest Innotop, simply type ‘innotop’ command in our terminal and this is what most likely you will see:

There are actually at least three ways to establish first working connection to MySQL server(s) for Innotop.

The simplest way to just start working is to run it by using similar host/user/password parameters as you do for basic mysql client:

Another way is to run innotop and configure connection(s) from inside. Quick help screen (invoked with ‘?’ key) tells us: “@ Select/create server connections”, so let’s press ‘@’:

As we can see there is already ‘localhost’ one created automatically. We can now select this one, or create a new one. However, after you enter ‘localhost’ – it just continues to use current connection and gets you back to previous screen. I have no idea if you can actually edit this existing ‘localhost’ connection, so let’s just create a new one:

Next step:

so the only thing I put in this ‘connection wizard’s’ step was “DBI:mysql:;host=localhost”, but if you have some custom TCP port or socket, you must specify it here.
Next step:

And after that step, we are immediately back in our Dashboard view, but now it is actually working:

This way is for sure much more complicated and longer then just running with -h -u -p parameters, so why bother?
Also, if we quit the tool, and run again – the set up connection is lost!
OK, I’ll show you later why it is actually good to set up connection this way.
First, we make sure the set up connection will be saved! To make so, run it like this:

Then configure the connection again and quit the tool. Now Innotop has created configuration files for us:

We edit the .innotop/innotop.conf file and… it’s huge! All options inside… Check documentation for all of their meaning, I will only focus on my favourite ones.
For us the most important will be this section:

Actually, using the connection wizard was useful for me only to create initial first connection definition, which we can use as an example to create connections to more MySQL instances. It is just hard to create it manually just by reading the documentation.
We can see both the default ‘localhost’ connection which was there already, and our new ‘local’ where we did specify connection credentials. Now feel free to remove the line with ‘localhost’ one, we don’t need it.

Another important section is [active_connections] where each tool’s view can have assigned connection name. For example:
means that ‘local’ is default connection for Dashboard view. If we only have one connection configured in Innotop, after we enter another mode, it will get automatically assigned in configuration.

Monitoring Many Hosts

OK, since we now have a new configuration template ready, let’s manually configure more connections, which will be much faster then using the ‘wizard’.
For my tests, I used example replication sandbox (set up in 1 minute with mysqlsandbox), so connections will look like this:

Cool, now I can switch between each server for a given view mode using ‘n’ key, or choose a connection from the list after pressing ‘@’.
This is how an example [Q]uery view for slave256 looks like:

Server Groups

But that’s not enough! Maybe you have 100 servers, in 10 master->slave(s) groups – you can watch them all using single Innotop instance in a sensible way! All thanks to server groups functionality. From the defined connections, you can easily configure groups and for almost each view mode – you can either choose a single connection or a group. Then switching between groups is as easy as pressing TAB. There is information in the top right corner of the screen telling us what server group or what single server we are watching at.

So let’s create our first group:

Now we can see all the servers in the group in the same screen, which for some view modes makes more in other less sense. I am sure you will like the group view for replication mode.
Below is an example when one of the slaves is lagging. We can see 52s lag on slave1, I/O threads are keeping up on both slaves with master (pos 596814971), but what I am missing here is the Exec_Master_Log_Pos info for slaves. But the information is enough to see it’s the SQL thread on slave2 not keeping up.

One press key (I) and we are in the InnoDB IO threads view, where there we can see one important detail difference about slave1 – it’s doing much more fsyncs and write operations then two other servers.