Innotop is a very useful tool to monitor innodb information in real time. This tool is written by Baron Schwartz who is also an author of “High Performance MySQL, Second edition” book. [Side note: I highly recommend getting this book when it comes out (in June, 08?). Other authors include: Peter Zaitsev, Jeremy Zawodny, Arjen Lentz, Vadim Tkachenko and Derek J. Balling.] Quick summary of what innotop can monitor (from: http://innotop.sourceforge.net/): InnoDB transactions and internals, queries and processes, deadlocks, foreign key errors, replication status, system variables and status and much more.
Following are the instructions on how to install innotop on CentOS x64/Fedora/RHEL (Redhat enterprise). Most probably same instructions can be used on all flavors of Linux. If not, leave me a comment and I will research a solution for you. Let us start with downloading innotop. I used version 1.6.0 which is the latest at the time of writing.
Now let us go ahead and unzip and create the MakeFile to get it ready for install
tar zxpf innotop-1.6.0.tar.gz
At this point if you get the following output, you are good to continue:
Checking if your kit is complete...
Writing Makefile for innotop
If you get something similar to following, you will need to take care of the prerequisites:
Warning: prerequisite DBD::mysql 1 not found.
Warning: prerequisite DBI 1.13 not found.
Warning: prerequisite Term::ReadKey 2.1 not found.
Writing Makefile for innotop
Just because they are warnings does not mean you ignore them. So let us install those prerequisites. We will use perl’s cpan shell to get this installed (visit my post on how to install perl modules for more details). If it is your first time starting this up, you will have to answer some questions. Defaults will work fine in all cases.
perl -MCPAN -eshell
Note: you must install DBI before you can install DBD::mysql.
If you get an error like following when you are installing DBD::mysql:
Error: Can't load '/root/.cpan/build/DBD-mysql-4.007/blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: libmysqlclient.so.15: cannot open shared object file: No such file or directory at /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/DynaLoader.pm line 230.
You will have to create a symlink to the object file in your lib64 (or lib if you are not using x64 version) folder:
ln -s /usr/local/mysql/lib/mysql/libmysqlclient.so.15 /lib64/
Once all prerequisites are done, type perl Makefile.PL and you should have no warnings. Continue the install:
At this point you should have innotop installed on your system. Let us do some quick set up so you can start using innotop. We start with configuring your .my.cnf to include connection directives.
Add the following (edit to reflect your install) and save/exit
port = 3306
socket = /tmp/mysql.sock
Start up innotop by typing innotop at your shell prompt. First prompt will ask you to “Enter a name:”. I just put localhost since this will be used to connect locally. Next prompt asks you about DSN entry. I use: DBI:mysql:;mysql_read_default_group=mysql
This tells innotop to read .my.cnf file and use group [mysql] directives. Next prompt is optional (I just press enter). Next two prompts you enter information if you need to.
At this point your innotop installation / testing is complete. You can read man innotop to get more details on how to use innotop.
DISCLAIMER: Please be smart and use code found on internet carefully. Make backups often. And yeah.. last but not least.. I am not responsible for any damage caused by this posting. Use at your own risk.