« Why large IN clauses are problematic
Come work with me! »

The innotop MySQL and InnoDB monitor

Published at July 2, 2006 in Innotop, SQL and Tools. Tags: No Tags.

Download innotop

MySQL and InnoDB expose lots of information about their internals, but it’s hard to gather it all into one place and make sense of it. I’ve written a tool to do that, and you are free to download and use it. This article introduces innotop, a powerful text-mode MySQL and InnoDB monitoring tool. It has lots of features, is fast and configurable, and it’s easy to use.

Note: I’m now making it a priority to make innotop very stable and robust. If innotop crashes, please help me fix it. Please read this article about how to submit a bug report for innotop. Thanks so much for helping me build a comprehensive test suite!

Why another text-mode monitoring tool?

Because the available ones aren’t good enough. I didn’t want to duplicate anyone else’s effort, but the other similar tools (mytop, mtop) haven’t been actively maintained in years, don’t work correctly with newer versions of MySQL, and frankly only offer a small fraction of the features I’ve built into innotop.

I said innotop has lots of features and is really flexible. Here’s just a small sampling to give an idea:

  1. 11 different modes to show lots of information in very useful ways
  2. completely configurable — for example, you can choose which columns to see in every tabular display, in what order, what column to sort by, what direction to sort, add perl regex filters to any column, and on and on.
  3. offers features no other tool gives you, period. What was the date, time, and query (plus lots of other info!) of each transaction involved in the last deadlock? No other tool can give you that. And that’s just one of its dozens of unique features.
  4. it parses and displays InnoDB information, which is packed full of information. No other tool even tries to do this. Parsing the output of the InnoDB monitor is not for the faint of heart!
  5. its interactive help and configuration make it very easy to learn and highly productive to use. What keys are mapped in the current mode? Press ‘?’ and find out. What configuration variables can you change? Press a key and find out. Every function has context-sensitive help to keep you productive.

Perhaps its most powerful and ambitious feature is the way it presents InnoDB internals. MySQL is sorely lacking in instrumentation and analysis compared to other major relational database systems (for example, Microsoft SQL Server), and it is just no fun searching through the output of the InnoDB monitors to glean bits of information from it. In my opinion, this feature alone is a major step forward to looking at what MySQL is doing internally. The information has always been there, but until now it’s been hard for DBAs to use.

innotop is designed to do whatever you need it to do, and if it doesn’t, you can let me know how to improve it. I am continually using and improving this tool, in response to my own needs and those of other people using it.

How to get and install innotop

You can download innotop from this link, and read the documentation here (generated from the embedded POD, which you can also read on the command-line). You can also press the ‘?’ key for online, context-sensitive help once innotop is running.

Please send improvements and suggestions to me. I have been using it for quite a while now, but I’m sure there are bugs lurking around somewhere. In particular, if you find any InnoDB monitor output it can’t parse, please send me that output so I can add it to my test suite.

Screenshots

These screenshots are a bit contrived, since I’m getting them from a server that’s basically idle, but I hope they give a sense of innotop’s features. If you run innotop on a server that’s doing anything, you’ll see a lot more information in some of the modes.

InnoDB Transaction mode:

InnoDB Transaction Mode

Query List mode, with and without the header:

Query List ModeQuery List Mode with header and lots of inactive connections

InnoDB Foreign Key Error mode:

InnoDB Foreign Key Error mode

InnoDB Deadlock mode:

InnoDB Deadlock Mode

InnoDB I/O mode:

InnoDB I/O Mode

InnoDB Buffer mode:

InnoDB Buffer mode

InnoDB Row Operations mode:

InnoDB Row Operations

Load Graph mode:

Load Graph mode

Load Statistics mode:

Load Statistics mode

Variables and Status mode:

Variables and Status mode

Editing configuration on the fly:

Editing configuration on the fly

Technorati Tags:No Tags

You might also like:

  1. Version 0.1.123 of innotop released
  2. A look at innotop’s new features
  3. Version 0.1.154 of innotop released
  4. How to monitor InnoDB lock waits
  5. What to do when innotop crashes

37 Responses to “The innotop MySQL and InnoDB monitor”

Feed for this Entry
  1. 1 Xaprb
    Jul 6th, 2006 at 8:54 am

    If you’re interested in researching what OS resources your MySQL server is using, the Data Charmer has written an excellent article on that subject, and includes a command-line tool to output statistics too. Go take a look at mysqlresources.

  2. 2 C
    Jul 18th, 2006 at 7:56 pm

    Hi,

    I am interesting in using this app. However, I’m having a bit of a problem getting it running. I have installed the innotop package and all the related perl modules that are needed as well (DBI, Term::ReadKey, etc) on my MacBook, with the intention of playing with a tool a bit by pointing it to our master and slave dbs. The following are the prompts I am getting when I attempt to start innotop:

    Enter a new value for 'db' (Which DB to connect to).
Enter a value:  

Enter a new value for 'password' (The password of a user with the SUPER privilege).
Enter a value: 

Enter a new value for 'user' (The DB user (must have SUPER privilege)).
Enter a value: 

Enter a new value for 'savepass' (Whether to save your DB password in the config file).
Current value: 0
Enter a value:

    On this last one I’m not sure exactly what to enter here. Everything I do put in simple just tells me:

    Invalid response.
Try again:

    Could you possibly give me any insight on what I need to do to get this started? I would really like see it what it has to offer.

    Thanks

  3. 3 Xaprb
    Jul 19th, 2006 at 9:07 pm

    C,

    Thanks for writing in. It is looking for a 1 or 0 response, which is totally unclear, I agree. Thanks for pointing this out. If you enter 1, it will save your password in the config file; if 0, it won’t, and will prompt you every time.

  4. 4 Xaprb
    Jul 19th, 2006 at 9:37 pm

    Just a quick note, I’m making a ton of improvements to innotop, based on reading a lot of the MySQL source code among other things… it really is kind of hard to parse the InnoDB monitor’s output, alas. Anyway, I hope to have a new version done this weekend, if all goes well.

  5. 5 jim
    Jul 20th, 2006 at 7:11 am

    How can I install the related perl modules?

  6. 6 Xaprb
    Jul 20th, 2006 at 7:15 am

    Generally via CPAN, depending on your Perl distribution. On a UNIX system, use the cpan command-line tool; on Windows with ActiveState’s ActivePerl, there’s a specific tool included with the distribution, which you should prefer to CPAN, though I forget exactly what it’s called.

  7. 7 Adam
    Jul 24th, 2006 at 1:49 pm

    What exactly is the InnoDBParser module and where can it be located?

  8. 8 Xaprb
    Jul 24th, 2006 at 2:29 pm

    The innotop program uses InnoDBParser.pm to parse the text output from SHOW INNODB STATUS into Perl data structures. Everything is included in the zip file: innotop-program, InnoDBParser.pm, and the two combined for convenience: innotop. You can just ignore everything but the innotop file if you want. I should clarify that somehow.

  9. 9 Adam
    Jul 24th, 2006 at 2:44 pm

    Ok, I see InnoDBParser.pm but how do I ge the program to use it? At the moment, it doesn’t seem like it sees the file.

  10. 10 Xaprb
    Jul 24th, 2006 at 2:53 pm

    You shouldn’t need to worry about it. It shouldn’t be looking for anything — the innotop program should be entirely self-contained, and the other files are just there because they’re the separate components that I assemble to make innotop (I wanted to distribute the entire source for the program).

    Last night I uploaded a zip file and then realized it wasn’t correctly assembled. It is possible that you downloaded that zip file before I replaced it with one that works correctly (about a 20-minute interval). The file that’s there now doesn’t have that problem. Please try re-downloading, unzip, and throw away everything but the file named ‘innotop.’

    If you still have problems, please post the error messages you are getting, and I’ll try to figure out what’s going on.

  11. 11 Xaprb
    Jul 24th, 2006 at 3:13 pm

    I repackaged the zip file to try to lessen the confusion. I hope that helps.

  12. 12 Adam
    Jul 24th, 2006 at 3:33 pm

    Many thanks :)

  13. 13 Adam
    Jul 24th, 2006 at 4:08 pm

    Just wanted to add that the archive should extract its contents to a directory rather than the current working directory. In other words, the current archive is a tar bomb.

    Thanks again…

  14. 14 Xaprb
    Jul 24th, 2006 at 4:28 pm

    Good point. I changed that.

  15. 15 dilip
    Jul 28th, 2006 at 12:26 am

    Please let me know how to install innotop in linux (Fedore Core 2).I am using latest perl and also mysql 4.x.

  16. 16 Xaprb
    Jul 28th, 2006 at 9:08 am

    Instead of replying in a comment, I just wrote an article explaining how to install innotop — please see the article.

  17. 17 Allen Smith
    Aug 1st, 2006 at 7:43 pm

    On RHEL4 I get:

    Undefined subroutine &InnoDBParser::time called at ./innotop line 1987

    -Allen

  18. 18 Xaprb
    Aug 1st, 2006 at 9:46 pm

    Allen,

    I’ll bet that’s because I didn’t explicitly say package main; at the end of the InnoDBParser module when I concatenated the files to make one program for version 0.1.12. You are probably running a slightly older version of Perl than I am and mine is probably guessing at what I meant.

    Fortunately, your message is just in time for version 0.1.123, which has lots of other enhancements too :-) Try re-downloading the new package and see if that works for you. If not, I’ll have to think harder about what could be wrong.

    Hopefully I’ve packaged it up right this time. Let me know if anything doesn’t work.

  19. 19 jpk
    Aug 2nd, 2006 at 4:36 am

    Mac OS X 10.4.7, Perl 5.8.6, trying to set mode “R”:


    Use of uninitialized value in length at /usr/local/bin/innotop line 3347.

    And innotop quits.

  20. 20 Xaprb
    Aug 2nd, 2006 at 7:21 am

    Hi jpk, I’ve written a post on what information I need to debug crashes. Can you please follow the instructions there to turn on debugging information and send me the results? Thanks very much!

  21. 21 jpk
    Aug 2nd, 2006 at 9:28 am

    Relevant info mailed to xaprb at xaprb dot com. Thanks!

  22. 22 Erik
    Sep 19th, 2006 at 4:39 am

    When “firstrun” configuring innotop I bump into this:

    I’m using the latest version of innotop (0.1.149), perl v5.8.0, MySQL 3.23.58 (yeah, the ancient one)

    Enter a new value for 'host' (Which server to connect to).
Current value: localhost
Enter a value: localhost
DBD::mysql::db selectcol_arrayref failed: Unknown error at ./innotop line 4049,  line 6.
  23. 23 Xaprb
    Sep 19th, 2006 at 8:59 am

    My first thought is an issue getting Perl and MySQL talking to one another. Can you get the following script to output anything? It should print your version number. You will need to save it as mysql-test.pl, fill in values for username and password, and execute it with perl mysql-test.pl.

    #!/usr/bin/perl

use strict;
use warnings FATAL => 'all';

use DBI;

my $user = 'user';
my $pass = 'password';
my $host = 'localhost';
my $db   = 'test';

my $dbh =
   DBI->connect( "DBI:mysql:$db;host=$host", $user, $pass,
      { RaiseError => 1, PrintError => 1, AutoCommit => 1 },
   )
   or die $DBI::errstr;

my $version = $dbh->selectcol_arrayref("select VERSION()");

print @$version, "n";
  24. 24 kelvin
    Sep 29th, 2006 at 12:32 pm

    When I use innotop, it show me nothing but just “Nothing to display message”? When I install inntop I get “InnoDBParser module” error message.

  25. 25 Xaprb
    Sep 29th, 2006 at 12:59 pm

    kelvin, I apologize… I must have botched up something in my build script. I’m not at my home right now so I don’t know what I did! But, I’ve manually fixed up the executable in the zip file (linked from the top of this article) so it runs without error. Very strange…

    If there’s nothing to display, your InnoDB monitor’s output has been truncated. You need to read How to deliberately cause a deadlock in MySQL and follow the instructions there to clear out the data that’s causing your output to be truncated.

  26. 26 Jari Aalto
    Nov 4th, 2006 at 10:22 am

    Kindly release new versions using de facto notation: package-version.tar.gz

    An example (date based versions are always good); innotop-20062204.tar.gz. Unpacking should also happen to the appropriate directory: innotop-20062204/.

    This would make upgrades easy and make it possible to convert the utility into *.rpm and *.deb formats.

    Also consider if you could change zip, which is not installed by default in many Linux systems. The gzip or bzip2 are.

  27. 27 Xaprb
    Nov 4th, 2006 at 10:56 am

    The zip file is the most current version always (mostly for Windows users, where anything but .zip can be considered exotic by certain people), but there are versioned .tar.gz files available. My fault is that I didn’t link to them correctly, as this article became outdated over time. I’m changing the link in this article, and will change other articles too, to point to the directory where you may download all past versions.

    I’d love it if someone would create .deb and .rpm packages!

  28. 28 Hosam
    Nov 27th, 2006 at 5:56 pm

    Hi,

    I successfully downloaded and ran the tool on windows xp using activeperl 5.8.8 817

    Here is what I am trying to do: I have an application that uses the db for configuration. I need to know the sequence of statements that the application issues say to add a user. What tables are updated, what is the sql statement that is issued, etc.

    I ran the tool in T mode and Q mode and started adding and deleting users but nothing showed on the monitor. Am I missing something?

    Thanks for such a great tool. It is rare that such tools work from the first shot, yours did.

    Hosam

  29. 29 Xaprb
    Nov 27th, 2006 at 6:23 pm

    Hi Hosam, it’s likely that the queries were so fast you couldn’t see them go by. innotop only catches queries and transactions that are still running when it checks, which means fast queries never show up. If you do need to capture every query no matter how short, you can either turn on the general query log (not recommended for production systems) or use tcpdump to catch the queries as they go over the network. There’s a “Poor Man’s Query Profiler” on MySQL Forge that will help with that.

  30. 30 Hosam
    Nov 27th, 2006 at 7:03 pm

    Wow,

    That was definitely a quick turn around!

    Would it be possible for you to tell me how to turn that on? I would like to do that in the my.ini file as MySql is running as a service and I do not have the ability to control startup paramters.

    Thanks again!

    Hosam

  31. 31 Xaprb
    Nov 27th, 2006 at 7:17 pm

    Hi Hosam, here are links to the things I mentioned: how to turn on the MySQL general query log. You can enable this in the my.ini file by adding the same parameter there I believe, something like log=general-log.sql.

    And here is a link to the tcpdump sniffer for profiling MySQL queries.

  32. 32 Hosam
    Nov 28th, 2006 at 8:22 am

    Many thanks! I was able to get what I need. I added log=SOMEFILE.LOG to my.ini under [mysqld] and the queries started showing up. I used Tail for windows to monitor it as transactions were taking place.

    Many thanks again!

    Hosam

  33. 33 Fredrik
    Dec 4th, 2006 at 9:45 am

    Hi and thanks, I tried the tool, and it gives nice info.
    One thing I’m missing tho. Is to output the monitoring to file. I.e
    ./innotop 10 5 -monitor1 -monitor2 -F file.txt . Where #1 is times and #2 is interval and monitor would be some switches I’m interested of.
    Or just a plain dumpout to console for redirection.
    This is because id like another monitor/agent to read values/evaluate-threshold/send event/
    It would make it much more useful for a distributed monitoring.
    Also provide where/which configuration file to read,
    ./innotop -config /path/to/my/innotop.cfg

    Br Fredrik

  34. 34 Xaprb
    Dec 4th, 2006 at 10:05 am

    Hi Fredrik, thanks for writing in. I am working on these features right now, and many others too (I got a lot of feedback and ideas at the MySQL Camp). Please feel free to write me at if you have other requests!

  35. 35 Bartosz Fenski
    Jan 23rd, 2007 at 2:57 am

    As far as I can see innotop can’t connect to server via socket file, and I think that would be useful feature.

  36. 36 Xaprb
    Jan 24th, 2007 at 11:45 am

    Hi Bartosz, I’ll respond to you via email… this might be something I will add to the 1.0 branch if you need, or maybe it can wait till 1.4.

  1. 1 gesammelte Werke » mytop mit innodb-Fokus
    Pingback on Jul 10th, 2006 at 3:06 am
Comments are currently closed.