innotop

Version

innotop --version

Install

For Red Hat derivatives (RHEL, OEL, CentOS etc) ensure EPEL repository is enabled.

yum install innotop

innotop has dependencies on perl-TermReadKey and perl-Time-HiRes

Usage

To connect as 'root', prompt for the password, using the socket file in /mysql/data/mysql.sock with a 1 second refresh rate... 

innotop -u root --askpass --socket /mysql/data/mysql.sock --delay 1

You can change this when innotop is running by pressing "d"

When innotop is running hit "?" for help

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

innotop for MySQL 8.0 and later

If you see this...

localhost PROCESSLIST_NO_IS: DBI connect(';mysql_socket=/mysql/mysql.sock;mysql_read_default_group=client','root',...) failed: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at /bin/innotop line 7775.

You are using a version of innotop that does not support the caching_sha2_password plugin. 

As a workaround, you can set the user to use the old mysql_native_password plugin (but this is not recommended).

The innotop version that includes MySQL 8.0 support is 1.12.0 (released in November 2018). However this still depends on you having perl-DBD-MySQL that has been linked with libmysqlclient.so.21 or later, or libmariadb.so.3 or later.

Check using...

ldd /usr/lib64/perl5/vendor_perl/auto/DBD/mysql/mysql.so | grep  'mysql\|maria'

Bibliography