innotop

innotop - A MySQL and InnoDB monitor program.

innotop connects to one or many MySQL database servers and retrieves data, then displays it. It can run interactively as a monitor, or serve as a source for \s-1UNIX\s0 pipe-and-filter style programming. innotop uses the data from \s-1SHOW\s0 \s-1VARIABLES\s0, \s-1SHOW\s0 \s-1GLOBAL\s0 \s-1STATUS\s0, \s-1SHOW\s0 \s-1FULL\s0 \s-1PROCESSLIST\s0, and \s-1SHOW\s0 \s-1ENGINE\s0 \s-1INNODB\s0 \s-1STATUS\s0, among other things. It refreshes the data at regular intervals, so you get a sense of what's happening inside your MySQL servers. You can control how fast it refreshes.

I originally wrote innotop to parse \s-1SHOW\s0 \s-1INNODB\s0 \s-1STATUS\s0 and show a list of current transactions in `top' style, hence the name. It now has much more functionality.

When innotop is running interactively, you control it with key presses. You can find a complete list of all available keys at any time by pressing '?' for help. Keys change innotop from one mode to another, let you change configuration, and send commands to MySQL servers.

Within each of innotop's modes, innotop displays 'tables' of the current data. For example, in T (InnoDB Transactions) mode, it shows the transactions in a table. In some modes there are many tables on screen at once.

You can choose which tables to display, in what order, which columns and in what order, how to sort the rows, colorize and filter the rows, and more. Think of the tables as spreadsheets; you have quite a bit of control over what goes into the cells. You can even define your own formulas and apply formatting. For example, you can choose whether a cell should be right or left justified, specify minimum and maximum widths, shorten large numbers to familiar units like \s-1MB\s0 and \s-1GB\s0, and turn an integer number of seconds into hours:minutes:seconds display.

Some modes allow you to see the incremental changes since last refresh. This can be useful to see how many new queries have been issued during that time, for example. You can toggle this on and off.

You can define many connections to servers, group the servers together, and switch between them easily to manage many MySQL instances conveniently. See \s-1SERVER\s0 \s-1GROUPS\s0 for more.

Remember, press '?' to see what commands are available to you at any time.

innotop is completely configurable. The default configuration is built into the program, but everything is written out to a configuration file when you exit innotop. You can edit this file by hand as you wish, or just use the built-in configuration commands while innotop is running.

You can specify certain options on the command-line. Run `innotop --help' for details.

innotop has many modes. The following is a brief description of each in alphabetical order. Remember, you can always get the authoritative help by pressing '?'.

"B: This mode displays the InnoDB buffer pool, page statistics, insert buffer, and adaptive hash index.

"D: This mode shows the transactions involved in the last InnoDB deadlock. A second table shows the locks each transaction held and waited for (recall that a deadlock is caused by a cycle in the waits-for graph). InnoDB puts deadlock information before some other information in the \s-1SHOW\s0 \s-1INNODB\s0 \s-1STATUS\s0 output. If there are a lot of locks, the deadlock information can grow very large indeed, and there is a limit on the size of the \s-1SHOW\s0 \s-1INNODB\s0 \s-1STATUS\s0 output. A large deadlock can fill the entire output, or even be truncated, and prevent you from seeing other information at all. If you are running innotop in another mode, for example T mode, and suddenly you don't see anything, you might want to check and see if a deadlock has wiped out the data you need. If it has, you can create a small deadlock to replace it. Use the 'w' key to 'wipe' the large deadlock with a small one. This will not work unless you have defined a deadlock table for the connection look in your configuration file. You can also set innotop to automatically detect when a large deadlock needs to be replaced with a small one. This feature is turned off by default.

"F: This mode shows the last InnoDB foreign key error information, such as the table where it happened, when and who and what query caused it, and so on. InnoDB has a huge variety of foreign key error messages, and many of them are just hard to parse. innotop doesn't always do the best job here, but there's so much code devoted to parsing this messy, unparseable output that innotop is likely never to be perfect in this regard. If innotop doesn't show you what you need to see, just look at the status text directly.

"G: This mode calculates per-second statistics, such as queries per second, scales them against a maximum, and prints them out as a “bar graph.” It's similar to the Load Statistics mode, except it's a graph instead of numbers. Headers are abbreviated to fit on the screen if necessary. This only happens in interactive operation, not while running unattended.

"I: This mode shows InnoDB's I/O statistics, including the I/O threads, pending I/O, file I/O miscellaneous, and log statistics.

"M: This mode shows the output of \s-1SHOW\s0 \s-1SLAVE\s0 \s-1STATUS\s0 and \s-1SHOW\s0 \s-1MASTER\s0 \s-1STATUS\s0 in three tables. The first two divide the slave's status into \s-1SQL\s0 and I/O thread status, and the last shows master status. Filters are applied to eliminate non-slave servers from the slave tables and vice versa.

"O: This section comes from MySQL's \s-1SHOW\s0 \s-1OPEN\s0 \s-1TABLES\s0 command. By default it is filtered to show tables which are in use by one or more queries, so you can get a quick look at which tables are 'hot'. You can use this to guess which tables might be locked implicitly.

"Q: This mode displays the output from \s-1SHOW\s0 \s-1FULL\s0 \s-1PROCESSLIST\s0, much like mytop's query list mode. This mode does not show InnoDB-related information. This is probably one of the most useful modes for general usage. You can toggle an informative header that shows general status information about your server. There are default sorting, filtering, and colorization rules. You can \s-1EXPLAIN\s0 a query from this mode. This will allow you to see the query's full text, the results of \s-1EXPLAIN\s0, and in newer MySQL versions, even see the optimized query resulting from \s-1EXPLAIN\s0 \s-1EXTENDED\s0.

"R: This mode shows InnoDB row operations, row operation miscellaneous, semaphores, and information from the wait array.

"S: This mode calculates statistics, such as queries per second, and prints them out in the style of <vmstat>. It's similar to the Load Graph mode, except it's a numbers instead of a graph. You can show absolute values or incremental values since the last refresh. Like G mode, headers may be abbreviated to fit on the screen in interactive operation. You choose which variables to display with the 'c' key, which selects from predefined sets. You can choose your own sets.

"T: This mode shows every transaction in the InnoDB monitor's output, in `top' format. This mode is the reason I wrote innotop. By default, two filters are applied to the table to hide inactive transactions and hide innotop's own transaction. You can toggle this on and off. There are also default sort and colorization rules in this view. You can customize these. If you are only viewing one server's transactions, innotop can display an informational header. This will show you things like how many entries there are in the InnoDB history list, how much of the buffer pool is used, and so forth.

"V: This mode displays any variables you please from \s-1SHOW\s0 \s-1GLOBAL\s0 \s-1STATUS\s0 and \s-1SHOW\s0 \s-1VARIABLES\s0, as well as the values parsed from \s-1SHOW\s0 \s-1INNODB\s0 \s-1STATUS\s0. It displays not only the current values, but previous values too; you choose how many sets to keep on screen.

"W: This mode shows information about current InnoDB lock waits. This information comes from the \s-1TRANSACTIONS\s0 section of the InnoDB status text. If you have a very busy server, you may have frequent lock waits; it helps to be able to see which tables and indexes are the “hot spot” for locks. If your server is running pretty well, this mode should show nothing. A second table shows any waits in the \s-1OS\s0 wait array. This comes from a separate section of the status text. If you see frequent waits, your server is probably running under a high concurrency workload. This is the same table displayed in R mode.

If you have a lot of MySQL instances, or even if you only have a few, you will probably find this functionality helpful.

To begin with, when you start innotop it will prompt you to define a connection to a server. After that is done, you can tell it to monitor another server with the @ key. This key actually brings up a list of connections you've defined. If you name one that doesn't exist, innotop will guide you through the process of defining it as a new connection, and it will be available from then on.

You can name multiple connections in any mode. For example, suppose you are in T mode, monitoring transactions on server1; if you press @, you can type 'server1 server2' and see data from both.

This becomes unwieldy after a bit though. To address this, you can press the '#' key to create and select server groups. Groups work just the same as connections: if you name one that doesn't exist, you can create it.

As an example, you might have groups named 'all', 'masters', 'slaves', 'oltp' and 'olap'. Many of the servers could belong to several of these groups. It's just a quick way to toggle between various servers.

Once you have defined groups, you can press the \s-1TAB\s0 key to cycle between them.

As of this writing innotop does \s-1NOT\s0 fetch data in parallel from different servers, so if your groups get large you may notice increased delay time when innotop refreshes. You can address this by creating more small groups. At some point I plan to make the data-fetching multi-threaded and this problem will not be so severe.

You must connect to the \s-1DB\s0 server as a user who has the \s-1SUPER\s0 privilege for many of the functions. If you don't have the \s-1SUPER\s0 privilege, you may still be able to run some functions.

I think everything you need to run innotop is distributed either with Perl, or with innotop itself. You need \s-1DBI\s0 and DBD::mysql. You also need the InnoDBParser module, and Term::ReadKey. If you have Time::HiRes, innotop will use it. If you have Term::ANSIColor, innotop will use it to format headers more readably and compactly. (Under Microsoft Windows, you also need Win32::Console::ANSI for terminal formatting codes to be honored). If you install Term::ReadLine, preferably Term::ReadLine::Gnu, you'll get nice auto-completion support.

I run innotop on Gentoo GNU/Linux, Debian and Ubuntu, and I've had feedback from people successfully running it on Red Hat, CentOS, Solaris, and Mac \s-1OSX\s0. I don't see any reason why it won't work on other UNIX-ish operating systems, but I don't know for sure. It also runs on Windows under ActivePerl without problem.

I have perl v5.8.8 installed, and I've had reports of it working on 5.8.5 but I don't know about other versions.

I use innotop on MySQL version 4.1 and 5.0, and have heard of others using it on these same versions and 5.1.

$HOMEDIR/.innotop is used to store configuration information.

This program is copyright (c) 2006 Baron Schwartz, baron at xaprb dot com. Feedback and improvements are welcome.

\s-1THIS\s0 \s-1PROGRAM\s0 \s-1IS\s0 \s-1PROVIDED\s0 “\s-1AS\s0 \s-1IS\s0” \s-1AND\s0 \s-1WITHOUT\s0 \s-1ANY\s0 \s-1EXPRESS\s0 \s-1OR\s0 \s-1IMPLIED\s0 \s-1WARRANTIES\s0, \s-1INCLUDING\s0, \s-1WITHOUT\s0 \s-1LIMITATION\s0, \s-1THE\s0 \s-1IMPLIED\s0 \s-1WARRANTIES\s0 \s-1OF\s0 \s-1MERCHANTIBILITY\s0 \s-1AND\s0 \s-1FITNESS\s0 \s-1FOR\s0 A \s-1PARTICULAR\s0 \s-1PURPOSE\s0.

This program is free software; you can redistribute it and/or modify it under the terms of the \s-1GNU\s0 General Public License as published by the Free Software Foundation, version 2; \s-1OR\s0 the Perl Artistic License. On \s-1UNIX\s0 and similar systems, you can issue `man perlgpl' or `man perlartistic' to read these licenses.

You should have received a copy of the \s-1GNU\s0 General Public License along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, \s-1MA\s0 02111-1307 \s-1USA\s0.

Execute innotop and press '!' to see this information at any time.

Baron Schwartz, baron at xaprb dot com.

If you find any problems with innotop, please contact me. Specifically, if you find any problems with parsing the InnoDB monitor output, I would greatly appreciate you sending me the full text of the monitor output that caused the problem.