Use Innotop Monitoring MySQL

install

[root@mytest ~]# tar -zxvf innotop-1.9.0.tar.gz 
innotop-1.9.0/
innotop-1.9.0/t/
innotop-1.9.0/t/InnoDBParser.t
innotop-1.9.0/t/innodb-status-001
innotop-1.9.0/t/innodb-status-002
innotop-1.9.0/t/innodb-status-003
innotop-1.9.0/t/innodb-status-004
innotop-1.9.0/t/innodb-status-005
innotop-1.9.0/t/innodb-status-006
innotop-1.9.0/t/innodb-status-007
innotop-1.9.0/innotop.spec
innotop-1.9.0/Makefile.PL
innotop-1.9.0/INSTALL
innotop-1.9.0/Changelog
innotop-1.9.0/innotop
innotop-1.9.0/COPYING
innotop-1.9.0/MANIFEST
[root@mytest ~]# cd innotop-1.9.0
[root@mytest innotop-1.9.0]# 
[root@mytest innotop-1.9.0]# perl Makefile.PL
Warning: prerequisite Term::ReadKey 2.1 not found.
Writing Makefile for innotop
[root@mytest innotop-1.9.0]# 
[root@mytest innotop-1.9.0]# 
[root@mytest innotop-1.9.0]# perl  -MCPAN -e shell
Terminal does not support AddHistory.

cpan shell -- CPAN exploration and modules installation (v1.7602)
ReadLine support available (try 'install Bundle::CPAN')

cpan> install Term::ReadKey
CPAN: Storable loaded ok
CPAN: LWP::UserAgent loaded ok
Fetching with LWP:
  ftp://ftp.perl.org/pub/CPAN/authors/01mailrc.txt.gz
Going to read /root/.cpan/sources/authors/01mailrc.txt.gz
CPAN: Compress::Zlib loaded ok
Fetching with LWP:
  ftp://ftp.perl.org/pub/CPAN/modules/02packages.details.txt.gz
Going to read /root/.cpan/sources/modules/02packages.details.txt.gz
  Database was generated on Sat, 03 Aug 2013 22:29:03 GMT

  There's a new CPAN.pm version (v2.00) available!
  [Current version is v1.7602]
  You might want to try
    install Bundle::CPAN
    reload cpan
  without quitting the current session. It should be a seamless upgrade
  while we are running...

Fetching with LWP:
  ftp://ftp.perl.org/pub/CPAN/modules/03modlist.data.gz
Going to read /root/.cpan/sources/modules/03modlist.data.gz
Going to write /root/.cpan/Metadata
Running install for module Term::ReadKey
Running make for J/JS/JSTOWE/TermReadKey-2.30.tar.gz
Fetching with LWP:
  ftp://ftp.perl.org/pub/CPAN/authors/id/J/JS/JSTOWE/TermReadKey-2.30.tar.gz
CPAN: Digest::MD5 loaded ok
Fetching with LWP:
  ftp://ftp.perl.org/pub/CPAN/authors/id/J/JS/JSTOWE/CHECKSUMS
Checksum for /root/.cpan/sources/authors/id/J/JS/JSTOWE/TermReadKey-2.30.tar.gz ok
Scanning cache /root/.cpan/build for sizes
TermReadKey-2.30/
TermReadKey-2.30/genchars.pl
TermReadKey-2.30/Makefile.PL
TermReadKey-2.30/Configure.pm
TermReadKey-2.30/test.pl
TermReadKey-2.30/ReadKey.pm
TermReadKey-2.30/META.yml
TermReadKey-2.30/ReadKey.xs
TermReadKey-2.30/ppport.h
TermReadKey-2.30/MANIFEST
TermReadKey-2.30/README

  CPAN.pm: Going to build J/JS/JSTOWE/TermReadKey-2.30.tar.gz

Checking if your kit is complete...
Looks good
Writing Makefile for Term::ReadKey
cp ReadKey.pm blib/lib/Term/ReadKey.pm
AutoSplitting blib/lib/Term/ReadKey.pm (blib/lib/auto/Term/ReadKey)
/usr/bin/perl -I/usr/lib/perl5/5.8.8 genchars.pl

Writing termio/termios section of cchars.h... Done.
Checking for sgtty...
        Sgtty NOT found.
Writing sgtty section of cchars.h... Done.
/usr/bin/perl /usr/lib/perl5/5.8.8/ExtUtils/xsubpp -noprototypes -typemap /usr/lib/perl5/5.8.8/ExtUtils/typemap  ReadKey.xs > ReadKey.xsc && mv ReadKey.xsc ReadKey.c
gcc -c   -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -pipe -Wdeclaration-after-statement -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic   -DVERSION=\"2.30\" -DXS_VERSION=\"2.30\" -fPIC "-I/usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/CORE"   ReadKey.c
In file included from ReadKey.xs:6:
ppport.h:230:1: warning: "PERL_UNUSED_DECL" redefined
In file included from ReadKey.xs:4:
/usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/CORE/perl.h:163:1: warning: this is the location of the previous definition
In file included from ReadKey.xs:375:
cchars.h: In function 鈥榅S_Term__ReadKey_GetControlChars鈥

                                                            cchars.h:244: warning: unused variable 鈥榠鈥
ReadKey.xs: In function 鈥楻eadMode鈥

                                                                                                                                                ReadKey.xs:770: warning: unused variable 鈥楶erl___notused鈥
ReadKey.c: In function 鈥榅S_Term__ReadKey_SetTerminalSize鈥

                                      ReadKey.c:1999: warning: unused variable 鈥榯arg鈥
Running Mkbootstrap for Term::ReadKey ()
chmod 644 ReadKey.bs
rm -f blib/arch/auto/Term/ReadKey/ReadKey.so
gcc  -shared -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic ReadKey.o  -o blib/arch/auto/Term/ReadKey/ReadKey.so       \
                \
          
chmod 755 blib/arch/auto/Term/ReadKey/ReadKey.so
cp ReadKey.bs blib/arch/auto/Term/ReadKey/ReadKey.bs
chmod 644 blib/arch/auto/Term/ReadKey/ReadKey.bs
Manifying blib/man3/Term::ReadKey.3pm
  /usr/bin/make  -- OK
Running make test
PERL_DL_NONLAZY=1 /usr/bin/perl "-Iblib/lib" "-Iblib/arch" -w test.pl
1 .. 8
ok 1
ok 2
ok 3
ok 4
ok 5
ok 6
ok 7
ok 8
  /usr/bin/make test -- OK
Running make install
Manifying blib/man3/Term::ReadKey.3pm
Installing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/Term/ReadKey/ReadKey.so
Installing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/Term/ReadKey/ReadKey.bs
Files found in blib/arch: installing files in blib/lib into architecture dependent library tree
Installing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/Term/ReadKey/autosplit.ix
Installing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/Term/ReadKey.pm
Installing /usr/share/man/man3/Term::ReadKey.3pm
Writing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/Term/ReadKey/.packlist
Appending installation info to /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/perllocal.pod
  /usr/bin/make install  -- OK

cpan> Terminal does not support GetHistory.
Lockfile removed.
[root@mytest innotop-1.9.0]# 
[root@mytest innotop-1.9.0]# 
[root@mytest innotop-1.9.0]# perl Makefile.PL
Writing Makefile for innotop
[root@mytest innotop-1.9.0]# make install
cp innotop blib/script/innotop
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/innotop
Manifying blib/man1/innotop.1
Installing /usr/share/man/man1/innotop.1
Installing /usr/bin/innotop
Writing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/innotop/.packlist
Appending installation info to /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/perllocal.pod

help

[root@mytest ~]# innotop --help
Usage: innotop <options> <innodb-status-file>

  --[no]color   -C   Use terminal coloring (default)
  --config      -c   Config file to read
  --count            Number of updates before exiting
  --delay       -d   Delay between updates in seconds
  --help             Show this help message
  --host        -h   Connect to host
  --[no]inc     -i   Measure incremental differences
  --mode        -m   Operating mode to start in
  --nonint      -n   Non-interactive, output tab-separated fields
  --password    -p   Password to use for connection
  --port        -P   Port number to use for connection
  --skipcentral -s   Skip reading the central configuration file
  --socket      -S   MySQL socket to use for connection
  --spark            Length of status sparkline (default 10)
  --timestamp   -t   Print timestamp in -n mode (1: per iter; 2: per line)
  --user        -u   User for login if not current user
  --version          Output version information and exit
  --write       -w   Write running configuration into home directory if no config files were loaded

innotop is a MySQL and InnoDB transaction/status monitor, like 'top' for
MySQL.  It displays queries, InnoDB transactions, lock waits, deadlocks,
foreign key errors, open tables, replication status, buffer information,
row operations, logs, I/O operations, load graph, and more.  You can
monitor many servers at once with innotop. 

use

[root@mytest ~]# innotop -uroot -pmysql -S /data/mysql/mysql.sock 
[RO] Query List (? for help)                                                                                                                                             localhost, 11h, 0.20 QPS, 2/2/0 con/run/cac thds, 5.5.25-log

When   Load  Cxns   QPS   Slow  Se/In/Up/De%  QCacheHit  KCacheHit  BpsIn  BpsOut
Now    0.05  1      0.20     0   0/ 0/ 0/ 0       0.00%    100.00%   8.80  808.23
Total  0.00  1.00k  0.00     0   3/ 0/ 0/ 0       0.00%    100.00%   0.09    4.32

Cmd     ID      State               User      Host           DB      Time      Query
Daemon       1  Waiting on empty q  event_sc  localhost              11:27:41       
[RO] Query List (? for help)                                                                                                                                             localhost, 11h, 0.20 QPS, 2/2/0 con/run/cac thds, 5.5.25-log

Switch to a different mode:
   A  Dashboard         I  InnoDB I/O Info     Q  Query List
   B  InnoDB Buffers    K  InnoDB Lock Waits   R  InnoDB Row Ops
   C  Command Summary   L  Locks               S  Variables & Status
   D  InnoDB Deadlocks  M  Replication Status  T  InnoDB Txns
   F  InnoDB FK Err     O  Open Tables         U  User Statistics

Actions:
   a  Toggle the innotop process    k  Kill a query's connection
   c  Choose visible columns        n  Switch to the next connection
   d  Change refresh interval       p  Pause innotop
   e  Explain a thread's query      q  Quit innotop
   f  Show a thread's full query    r  Reverse sort order
   h  Toggle the header on and off  s  Change the display's sort column
   i  Toggle idle processes         x  Kill a query

Other:
 TAB  Switch to the next server group   /  Quickly filter what you see
   !  Show license and warranty         =  Toggle aggregation
   #  Select/create server groups       @  Select/create server connections
   $  Edit configuration settings       \  Clear quick-filters
Press any key to continue