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

The innotop MySQL and InnoDB monitor

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:

spacer

Query List mode, with and without the header:

spacer spacer

InnoDB Foreign Key Error mode:

spacer

InnoDB Deadlock mode:

spacer

InnoDB I/O mode:

spacer

InnoDB Buffer mode:

spacer

InnoDB Row Operations mode:

spacer

Load Graph mode:

spacer

Load Statistics mode:

spacer

Variables and Status mode:

spacer

Editing configuration on the fly:

spacer

Posted Sunday, July 2nd, 2006 under Tools, SQL, Innotop.

28 Responses to “The innotop MySQL and InnoDB monitor”

  1. Xaprb Says:
    July 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. gesammelte Werke » mytop mit innodb-Fokus Says:
    July 10th, 2006 at 3:06 am

    […] begegnet. Innotop arbeitet ähnlich wie mytop, legt aber den Fokus klar auf die Storage-Engine. permalink | trackback uri| […]

  3. C Says:
    July 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

  4. Xaprb Says:
    July 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.

  5. Xaprb Says:
    July 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.

  6. jim Says:
    July 20th, 2006 at 7:11 am

    How can I install the related perl modules?

  7. Xaprb Says:
    July 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.

  8. Adam Says:
    July 24th, 2006 at 1:49 pm

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

  9. Xaprb Says:
    July 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.

  10. Adam Says:
    July 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.

  11. Xaprb Says:
    July 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.

  12. Xaprb Says:
    July 24th, 2006 at 3:13 pm

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

  13. Adam Says:
    July 24th, 2006 at 3:33 pm

    Many thanks :)

  14. Adam Says:
    July 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…

  15. Xaprb Says:
    July 24th, 2006 at 4:28 pm

    Good point. I changed that.

  16. dilip Says:
    July 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.

  17. Xaprb Says:
    July 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.

  18. Allen Smith Says:
    August 1st, 2006 at 7:43 pm

    On RHEL4 I get:

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

    -Allen

  19. Xaprb Says:
    August 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.

  20. jpk Says:
    August 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.

  21. Xaprb Says:
    August 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!

  22. jpk Says:
    August 2nd, 2006 at 9:28 am

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

  23. Erik Says:
    September 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.
  24. Xaprb Says:
    September 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";
  25. kelvin Says:
    September 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.

  26. Xaprb Says:
    September 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.

  27. Jari Aalto Says:
    November 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.

  28. Xaprb Says:
    November 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!

Leave a Reply

XHTML: You can use these tags: <a class="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <br> <code> <del datetime=""> <dd> <dl> <dt> <em> <i> <ins datetime="" cite=""> <li> <ol> <p> <pre> <q> <strike> <strong> <sub> <sup> <u> <ul>

I really appreciate your comments, feeback, and additions. You add a lot to this site. All comments are moderated. If you don't see your comment in the moderation queue after you submit it, you may have entered a forbidden word such as 'casino' or 'poker.'

Note: If you're asking a question and you would like a reply, make sure you include enough information for me to make an intelligent response. If it's a question about SQL, that means I need to know table structures and other relevant details. I delete comments that I just can't answer.

Bicycles have:



gipoco.com is neither affiliated with the authors of this page nor responsible for its contents. This is a safe-cache copy of the original web site.