Innotop – A Monitoring tool for MySQL

Monitoring MySQL server has never been an easy task. Monitoring also needs to go through many Complex and difficult queries to get the details.

All these problems can be overcome by an excellent command line monitoring tool  called “Innotop”. Innotop comes with many features and different types of modes/options, which helps to monitor different aspects of MySQL  (InnoDB) performance and also helps database administrator to find out what’s wrong going with MySQL server. Innotop helps in monitoring user statistics, mysql replication status,query list, InnoDB I/O informations etc. Another important thing about innotop is it refreshes the data continuously , so we can view realtime statistics.

Innotop is one of the designed based on top utility for linux.

Installation:

In linux  distribution  like CentOS , RHEL , Scientific Linux and Fedora  it can be installed by enabling epel repository and installing it using yum command.

Source Code : https://github.com/innotop/innotop

OS Version – RHEL/CentOS 7 64 Bit

[root@mydbopslabs25 vagrant]# wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
[root@mydbopslabs25 vagrant]# yum install epel-release-latest-7.noarch.rpm
[root@mydbopslabs25 vagrant]# yum install innotop
Starting the Innotop -

[root@mydbopslabs25 vagrant]$ innotop -uroot --askpass

Enter password :

Provide MySQL server  username and password which you are about to monitor.

Once you are connected to MySQL server , you will see the below statistics on screen.

[RO] Dashboard (? for help)                                                                                                                      
Uptime MaxSQL ReplLag QPS Cxns Run Miss Lock Tbls Repl SQL  13m 7m    2 5 1 4.22 1 107 ALTER

TABLE sbtest1

To get various different  options and its usage press  “ ? ”.

Once you Press“?”you will be directed to all below options

Innotop options :

[RO] Dashboard (? 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:

d Change refresh interval            q Quit innotop 
k Kill a query's connection         r Reverse sort order
n Switch to the next connection      s Choose sort column
p Pause innotop                     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

Basic Usage :

Uppercase keys  - To switch between different modes.

Lowercase keys - To initiate some action within the current mode.

Other keys      - To change configuration etc.

Mentioned are the few most probable useful modes used in our  day to day usage .

Q – Query List :

[RO] Query List (? for help)           
localhost, 47m, 0.95 QPS, 5/3/3 con/run/cac thds, 5.7.25-log
When   Load Cxns  QPS Slow Se/In/Up/De%  QCacheHit KCacheHit

Now    0.13 5   0.95 0   16/ 0/ 0/ 0   0.00% 100.00%
Total   0.00 151  0.69 0   10/ 0/ 0/ 0   0.00% 50.00%


BpsIn   BpsOut
18.07    1.09M
122.46   351.44k


Cmd   ID State       User Host    DB Time   Query Query 4   Sending data  root localhost sbtest 00:04 select

* from sbtest1

This mode displays the output from SHOW FULL PROCESSLIST.

This is probably one of the most useful modes for general usage.

You can perform following actions on these queries by using Actions provided in innotop.

e - Can be used to  explain a thread's query.

f  - Can be used to show a thread's full query.

k - Can be used to kill a query's connection.

x - Can be used to  Kill a query.

p - Can pause innotop.

q - Used to Quit innotop

F – InnoDB FK Err :

[RO] InnoDB FK Err (? for help)                                                              localhost, 27d, InnoDB 1s :-), 596.05 QPS, 161/10/66 con/run/cac thds, 5.7.19-17-log
Reason: Foreign key constraint fails for table `mif-def`.`m_lat`:


User system user from , thread 2028 was executing:

insert into m_abc (amount, appuser_id, created_date, transaction_date, external_id, fee_charges_portion_derived, interest_portion_derived, loan_id, manually_adjusted_or_reversed, office_id, outstanding_loan_balance_derived, overpayment_portion_derived, payment_detail_id, penalty_charges_portion_derived, principal_portion_derived, is_reversed, submitted_on_date, transaction_type_enum, unrecognized_income_portion) values (940, 1, '2019-04-05 08:05:06.071', '2019-04-05', '2105470', 38, 27, 768283, 0, 1, 0, null, 2083987, null, 875, 0, '2019-04-05', 2, null)

Timestring   2019-04-05 08:05:06

Child DB     mifo

Child Table m_atk

Child Index                   

Parent DB   
mif-def

Parent Table m_lat      

Parent Column   id

Parent Index                  

Constraint   _FKCFCEA42640BE0710

Action                

This mode detects last innoDB foreign key error and provides the information regarding the accused table , query causing error ,parent table ,child table etc

I – InnoDB I/O Info :


[RO] InnoDB I/O Info (? for help)
localhost, 13m, InnoDB 11s :-), 0.48 QPS, 5/3/1 con/run/cac thds, 5.7.25-log


_______________________I/O Threads ________________________
Thread Purpose Thread Status
0 insert buffer thread waiting for completed aio requests
1 log thread waiting for completed aio requests
2 read thread waiting for completed aio requests
3 read thread waiting for completed aio requests
4 read thread waiting for completed aio requests
5 read thread waiting for completed aio requests
6 write thread waiting for completed aio requests
7 write thread waiting for completed aio requests
8 write thread waiting for completed aio requests
9 write thread waiting for completed aio requests

______________________Pending I/O __________________________
Async Rds Async Wrt IBuf Async Rds Sync I/Os Log Flushes Log I/Os
0

_____________________File I/O Misc _________________________
OS Reads OS Writes OS fsyncs Reads/Sec Writes/Sec Bytes/Sec
46776 25128 1335 0.00 0.00 0

_____________________ Log Statistics _____________________
Sequence No. Flushed To Last Checkpoint IO Done IO/Sec
1350554446 1350554446 1350554437 483 0.00


K – InnoDB Lock Waits :

[RO] InnoDB Lock Waits (? for help)            
localhost, 26m, 0 QPS, 5/0/0 con/run/cac thds, 5.7.25-log

WThread  Waiting Query    WWait BThread BRowsMod  BAge BWait

5 UPDATE sbtest1 40s 4 1 144s

BStatus Blocking Query              Sleep 147

This mode shows waiting and blocking transaction along with its Query . You can use it to find when a transaction is waiting for another, and kill the blocking transaction.

M – Replication Status :

RO] Replication Status (? for help)                                                                                                                  
localhost, 2h3m, 1.00 QPS, 2/1/0 con/run/cac thds, 5.7.25-log

_________________________ Slave SQL Status _________________
Channel Master Master UUID
192.168.33.25
899ddb7e-4934-11e9-b3a9-080027827325
On? TimeLag Catchup Temp Relay Pos Last Error
No 00:00 0.00 192.168.33.25 328 Error 'Table 'sbtest.sbtest1' doesn't exist' on query. Default database: 'sbtest'. Query: 'alter table sbtest

_________________________ Slave I/O Status _________________


Channel Master Master UUID
192.168.33.25
O899ddb7e-4934-11e9-b3a9-080027827325n?
On? File Relay Size Pos State
Yes mydbopslabs25-bin.000003 5.82k 235 Waiting
for master to send event

_______________________ Master Status________________________
Channel File Position Binlog Cache
mydbopslabs26-bin.000001 306 0.00%
Executed GTID Set Server UUID
N/A fc9155a1-65ae-11e9-93b2-080027827325

This mode shows the output of SHOW SLAVE STATUS and SHOW MASTER STATUS in three tables. The first two divide the slave’s status into SQL and I/O thread status, and the last shows master status.

T  InnoDB Txns :


[RO] InnoDB Txns (? for help)
localhost, 40m, InnoDB 5s :-), 0.92 QPS, 5/3/3 con/run/cac thds, 5.7.25-log


History Versions Undo Dirty Buf Used Bufs Txns MaxTxnTime
113 0.00% 93.93% 3 00:51

LStrcts


ID User Host Txn Status Time Undo Query Text
4 root localhost ACTIVE 00:51 0 select * from

sbtest1 FOR UPDATE

Transactions mode shows the transaction state of all the threads  running inside of MySQL. You can see who owns the transaction, which host they are connected from, the transaction status, running time, and the query that’s currently being run inside the transaction.

Monitor Remote Database

To monitor a remote database on a remote system, use the following command using a particular username, password and hostname (-h).

Remote connection :

[root@mydbopslabs25 vagrant]# innotop -u root --askpass -h 192.168.33.25

Enter password :

Thus , in the above blog i have tried giving out basic idea and usage of Innotop which plays a vital role in hassle free monitoring of day to day activities related to MySQL server and helps out find any abnormal activities going  inside MySQL server.

Image Credits: Photo by Stephen Dawson on Unsplash

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s