Innotop is an excellent command-line program, similar to the top command, to monitor local and remote MySQL servers running under the InnoDB engine.
Innotop comes with many features and different types of modes/options, which help monitor various aspects of MySQL performance and also assist database administrators in identifying issues with the MySQL server.
For example, Innotop helps in monitoring MySQL replication status, user statistics, query lists, InnoDB buffers, InnoDB I/O information, open tables, lock tables, etc. It refreshes its data regularly, allowing you to see updated results.
Innotop comes with great features and flexibility and doesn’t need any extra configuration. It can be executed by simply running the ‘innotop‘ command from the terminal.
Install Innotop (MySQL Monitoring) Tool in Linux
Innotop is available in your distribution’s package manager, you can install it using your package manager as shown.
sudo apt install innotop [On Debian, Ubuntu and Mint] sudo yum install innotop [On RHEL/CentOS/Fedora and Rocky/AlmaLinux]
If innotop is not available in your distribution’s package repository, you can clone the Git repository and install it as shown.
git clone https://github.com/innotop/innotop.git cd innotop
Next, you need to install some Perl dependencies uisng CPAN or cpanminus. If you don’t have cpanminus installed, you can install it using your package manager:
sudo apt install cpanminus [On Debian, Ubuntu and Mint] sudo dnf install cpanminus [On RHEL/CentOS/Fedora and Rocky/AlmaLinux]
Finally, install the required Perl modules using cpanminus as shown.
cpanm Term::ReadKey DBI DBD::mysql
After installing the dependencies, you can run Innotop using the following command:
perl innotop
First, you need to inssall cpanminus, a lightweight and user-friendly client for CPAN, which is a repository of Perl modules and software that simplified that installation of Perl modules and their dependencies.
sudo ap install cpanminus # For Debian/Ubuntu sudo yum install cpanminus # For CentOS/RHEL
Next, install following Perl module dependencies.
cpanm Term::ReadKey DBI DBD::mysql
Then, install Innotop:
sudo cpanm innotop
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
Read Also :
