Installing and using innotop

innotop is a utility for monitoring the status of MySQL server.

I will give an example of installing innotop in Ubuntu. Since innotop is present in mysql-client, we install it and the necessary components:

sudo apt install mysql-client libterm-readkey-perl libclass-dbi-perl libclass-dbi-mysql-perl

Installation command on CentOS:

yum install innotop

After installation, you can see which version was installed (for example, at the time of writing this article, version 1.7.1 was installed in my Ubuntu Server 16.04):

innotop --version

But as it turned out, 1.7.1 is not compatible with new versions of MySQL, so I installed the newer version from git (after installing from under root, version 1.12.0 will be launched, and under other users 1.7.1):

sudo apt update
sudo apt install git make
cd /usr/src/
sudo git clone https://github.com/innotop/innotop.git
cd innotop/
sudo perl Makefile.PL 
sudo make install
sudo innotop --version

I will give examples of starting:

sudo innotop
sudo innotop --user root --password password --host 127.0.0.1 --port 3306
sudo innotop --count 5 -d 1 -n
sudo innotop /var/log/mysql/mysqld.err
sudo innotop --help

I will describe the columns displayed:
Uptime – MySQL server time
MaxSQL – the longest query execution time
ReplLag – slave replication lag behind master
QPS – the number of requests per second
Cxns – total number of connections to MySQL server
Run – the number of current requests to the server
Lock – the number of blocked requests
Tbls – the number of open tables
Repl – slave replication status
SQL is the longest query

I will describe the function keys and modes:
d – Change the frequency of updating information (in seconds)
p – Pause
q – Output
A – Main panel
B – Information about InnoDB buffers
C – Statistics on requests
D – InnoDB Deadlocks
F – InnoDB Foreign Key Errors
I – I/O Statistics
K – InnoDB Lock Waits
L – Locks
M – Master/Slave replication status
O – Open Tables
U – User Statistics
Q – List of requests (from SHOW FULL PROCESSLIST)
R – InnoDB Row Operations
S – Variables & Status
T – InnoDB operations

Did my article help you? How about buying me a cup of coffee as an encouragement? Buy me a coffe.

Leave a comment

Leave a Reply