Install Innotop to Monitor MySQL Server Performance

Innotop is an excellent command line program, similar to ‘top command‘ to monitor local and remote MySQL servers running under InnoDB engine. Innotop comes with many features and different types of modes/options, which helps to monitor different aspects of MySQL performance and also helps database administrator to find out what’s wrong going with MySQL server.

For example, Innotop helps in monitoring mysql replication status, user statistics, query list, InnoDB buffers, InnoDB I/O information, open tables, lock tables, etc, it refreshes its data regularly, so you could see updated results.

Innotop MySQL Server Monitoring

Innotop comes with great features and flexibility and doesn’t needs any extra configuration and it can be executed by just running ‘innotop‘ command from the terminal.

Installing Innotop (MySQL Monitoring)

By default innotop package is not included in Linux distributions such as RHEL, CentOS, Fedora and Scientific Linux. You need to install it by enabling third party epel repository and using yum command as shown below.

# yum install innotop
Sample Output
Loaded plugins: fastestmirror
 Loading mirror speeds from cached hostfile
 * base: centos.mirror.net.in
 * epel: epel.mirror.net.in
 * epel-source: epel.mirror.net.in
 * extras: centos.mirror.net.in
 * updates: centos.mirror.net.in
 Setting up Install Process
 Resolving Dependencies
 --> Running transaction check
 ---> Package innotop.noarch 0:1.9.0-3.el6 will be installed
 --> Finished Dependency Resolution
 
 Dependencies Resolved
 
 ==========================================================================================================
 Package   Arch  Version   Repository  Size
 ==========================================================================================================
 Installing:
 innotop noarch 1.9.0-3.el6 epel 149 k
 
 Transaction Summary
 ==========================================================================================================
 Install 1 Package(s)
 
 Total download size: 149 k
 Installed size: 489 k
 Is this ok [y/N]: y
 Downloading Packages:
 innotop-1.9.0-3.el6.noarch.rpm | 149 kB 00:00 
 Running rpm_check_debug
 Running Transaction Test
 Transaction Test Succeeded
 Running Transaction
 Installing : innotop-1.9.0-3.el6.noarch       1/1 
 Verifying : innotop-1.9.0-3.el6.noarch 1/1 
 
 Installed:
 innotop.noarch 0:1.9.0-3.el6 
 
 Complete!

To start innotop, simply type “innotop” and specify options -u (username) and -p (password) respectively, from the command line and press Enter.

# innotop -u root -p 'tecm1nt'

Once you’ve connected to MySQL server, you should see something similar to the following screen.

[RO] Dashboard (? for help) localhost, 61d, 254.70 QPS, 5/2/200 con/run/cac thds, 5.1.61-log
 Uptime MaxSQL ReplLag Cxns Lock QPS QPS Run Run Tbls Repl SQL
 61d 4 0 254.70 _ _ 462 Off 1
Innotop Help

Press “?” to get the summary of command line options and usage.

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 p Pause innotop
 k Kill a query's connection q Quit innotop
 n Switch to the next connection 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

This section contains screen shots of innotop usage. Use Upper-case keys to switch between modes.

User Statistics

This mode displays user statistics and index statistics sorted by reads.

CXN When Load QPS Slow QCacheHit KCacheHit BpsIn BpsOut 
 localhost Total 0.00 1.07k 697 0.00% 98.17% 476.83k 242.83k
Query List

This mode displays the output from SHOW FULL PROCESSLIST, similar to mytop’s query list mode. This feature doesn’t display InnoDB information and it’s most useful for general usage.

When Load Cxns QPS Slow Se/In/Up/De% QCacheHit KCacheHit BpsIn BpsOut
 Now 0.05 1 0.20 0 0/200/450/100 0.00% 100.00% 882.54 803.24
 Total 0.00 151 0.00 0 31/231470/813290/188205 0.00% 99.97% 1.40k 0.22
 
 Cmd ID State User Host DB Time Query
 Connect 25 Has read all relay system u 05:26:04
InnoDB I/O Info

This mode displays InnoDB’s I/O statistics, pending I/O, I/O threads, file I/O and log statistics tables by default.

____________________ I/O Threads ____________________
 Thread Purpose Thread Status 
 0 insert buffer thread waiting for i/o request
 1 log thread waiting for i/o request
 2 read thread waiting for i/o request
 3 write thread waiting for i/o request
 
 ____________________________ Pending I/O _____________________________
 Async Rds Async Wrt IBuf Async Rds Sync I/Os Log Flushes Log I/Os
 0 0 0 0 0 0
 
 ________________________ File I/O Misc _________________________
 OS Reads OS Writes OS fsyncs Reads/Sec Writes/Sec Bytes/Sec
 26 3 3 0.00 0.00 0
 
 _____________________ Log Statistics _____________________
 Sequence No. Flushed To Last Checkpoint IO Done IO/Sec
 0 5543709 0 5543709 0 5543709 8 0.00
InnoDB Buffers

This section, you will see information about the InnoDB buffer pool, page statistics, insert buffer, and adaptive hash index. The data fetches from SHOW INNODB STATUS.

__________________________ Buffer Pool __________________________
 Size Free Bufs Pages Dirty Pages Hit Rate Memory Add'l Pool
 512 492 20 0 -- 16.51M 841.38k
 
 ____________________ Page Statistics _____________________
 Reads Writes Created Reads/Sec Writes/Sec Creates/Sec
 20 0 0 0.00 0.00 0.00
 
 ______________________ Insert Buffers ______________________
 Inserts Merged Recs Merges Size Free List Len Seg. Size
 0 0 0 1 0 2
 
 __________________ Adaptive Hash Index ___________________
 Size Cells Used Node Heap Bufs Hash/Sec Non-Hash/Sec
 33.87k 0 0.00 0.00
InnoDB Row Ops

Here, you will see the output of InnoDB row operations, row operation misc, semaphores, and wait array tables by default.

________________ InnoDB Row Operations _________________
 Ins Upd Read Del Ins/Sec Upd/Sec Read/Sec Del/Sec
 0 0 0 0 0.00 0.00 0.00 0.00
 
 ________________________ Row Operation Misc _________________________
 Queries Queued Queries Inside Rd Views Main Thread State 
 0 0 1 waiting for server activity
 
 _____________________________ InnoDB Semaphores _____________________________
 Waits Spins Rounds RW Waits RW Spins Sh Waits Sh Spins Signals ResCnt
 2 0 41 1 1 2 4 5 5
 
 ____________________________ InnoDB Wait Array _____________________________
 Thread Time File Line Type Readers Lck Var Waiters Waiting? Ending?
Command Summary

The command summary mode displays all the cmd_summary table, which looks similar to the below.

_____________________ Command Summary _____________________
 Name Value Pct Last Incr Pct 
 Com_update 11980303 65.95% 2 33.33%
 Com_insert 3409849 18.77% 1 16.67%
 Com_delete 2772489 15.26% 0 0.00%
 Com_select 507 0.00% 0 0.00%
 Com_admin_commands 411 0.00% 1 16.67%
 Com_show_table_status 392 0.00% 0 0.00%
 Com_show_status 339 0.00% 2 33.33%
 Com_show_engine_status 164 0.00% 0 0.00%
 Com_set_option 162 0.00% 0 0.00%
 Com_show_tables 92 0.00% 0 0.00%
 Com_show_variables 84 0.00% 0 0.00%
 Com_show_slave_status 72 0.00% 0 0.00%
 Com_show_master_status 47 0.00% 0 0.00%
 Com_show_processlist 43 0.00% 0 0.00%
 Com_change_db 27 0.00% 0 0.00%
 Com_show_databases 26 0.00% 0 0.00%
 Com_show_charsets 24 0.00% 0 0.00%
 Com_show_collations 24 0.00% 0 0.00%
 Com_alter_table 12 0.00% 0 0.00%
 Com_show_fields 12 0.00% 0 0.00%
 Com_show_grants 10 0.00% 0 0.00%
Variables & Status

This section calculates statistics, like queries per second, and displays them out in number of different modes.

QPS Commit_PS Rlbck_Cmt Write_Commit R_W_Ratio Opens_PS Tbl_Cch_Usd Threads_PS Thrd_Cch_Usd CXN_Used_Ever CXN_Used_Now
 0 0 0 18163174 0 0 0 0 0 1.99 1.32
 0 0 0 18163180 0 0 0 0 0 1.99 1.32
 0 0 0 18163188 0 0 0 0 0 1.99 1.32
 0 0 0 18163192 0 0 0 0 0 1.99 1.32
 0 0 0 18163217 0 0 0 0 0 1.99 1.32
 0 0 0 18163265 0 0 0 0 0 1.99 1.32
 0 0 0 18163300 0 0 0 0 0 1.99 1.32
 0 0 0 18163309 0 0 0 0 0 1.99 1.32
 0 0 0 18163321 0 0 0 0 0 1.99 1.32
 0 0 0 18163331 0 0 0 0 0 1.99 1.32
Replication Status

In this mode, you will see the output of Slave SQL Status, Slave I/O Status and Master Status. The first two section shows the slave status and slave I/O thread status and the last section shows Master status.

_______________________ Slave SQL Status _______________________
 Master On? TimeLag Catchup Temp Relay Pos Last Error
 172.16.25.125 Yes 00:00 0.00 0 41295853 
 
 ____________________________________ Slave I/O Status _____________________________________
 Master On? File Relay Size Pos State 
 172.16.25.125 Yes mysql-bin.000025 39.38M 41295708 Waiting for master to send event
 
 ____________ Master Status _____________
 File Position Binlog Cache
 mysql-bin.000010 10887846 0.00%
Non-Interactively

You can run “innotop” in non-interactively.

# innotop --count 5 -d 1 -n
uptime max_query_time time_behind_master connections locked_count qps spark_qps run spark_run open slave_running longest_sql
 61d   2 0 0.000363908088893752    64 Yes  
 61d   2 0 4.96871146980749 _  _ 64 Yes  
 61d   2 0 3.9633543857494 ^_  __ 64 Yes  
 61d   2 0 3.96701862656428 ^__  ___ 64 Yes  
 61d   2 0 3.96574802684297 ^___  ____ 64 Yes
Monitor Remote Database

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

# innotop -u username -p password -h hostname

For more information about ‘innotop‘ usage and options, see the man pages by hitting “man innotop” on a terminal.

Reference Links

Innotop Homepage

Read Also :

  1. Mtop (MySQL Database Monitoring) in RHEL/CentOS/Fedora
If you liked this article, then do subscribe to email alerts for Linux tutorials. If you have any questions or doubts? do ask for help in the comments section.

If You Appreciate What We Do Here On TecMint, You Should Consider:

TecMint is the fastest growing and most trusted community site for any kind of Linux Articles, Guides and Books on the web. Millions of people visit TecMint! to search or browse the thousands of published articles available FREELY to all.

If you like what you are reading, please consider buying us a coffee ( or 2 ) as a token of appreciation.

We are thankful for your never ending support.

Got something to say? Join the discussion. Cancel reply

Scroll back to top