Monitoring MySQL server has never been an easy task. Monitoring also needs to go through many Complex and difficult queries to get the details. Master MySQL performance with Innotop!
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
Don't settle for average MySQL performance. Unleash the power of Innotop with Mydbops! Contact us today for a free consultation and unlock a new level of MySQL monitoring and optimization.
{{cta}}
.png)
.png)
.svg)
