Tutorial Install Innotop to Monitor MySQL on CentOS

Innotop is an advanced command line-based research application for monitoring local servers and MySQL output under the InnoDB engine. Innotop includes many features and different types of modes/options. These features help users avoid various aspects of MySQL output performance to find out what is wrong with the MySQL server. In this article, we try to introduce you to the Tutorial Install Innotop to Monitor MySQL on CentOS. You can visit the packages available in Eldernode to purchase a CentOS VPS server.

How to Install Innotop to Monitor MySQL on CentOS 7 | 8

Install Innotop to Monitor MySQL on CentOS

As the Innotop package is not included with CentOS by default now, you will need to install it by activating the Epel third-party repository. To do this you can use the following command:

yum install innotop

In the next step to start Innotop, you have to type “innotop” as in the following command. In the following command line, the -u and -p options are the username and password, respectively.

innotop -u root -p 'eldernode'

When you can successfully connect to the MySQL server, you will see the following information:

[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

It should be note that if you want to know the features available in Innotop, you can easily run “?“. By doing this, you will see information as below.

The next section is User Statistics. This section shows user statistics and indexes that are sorte by read.

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

The next mode is Query List. Note that this mode shows the output of the SHOW FULL PROCESSLIST.

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

The next section is about InnoDB I/O Info. It should be note that this mode displays InnoDB’s I/O statistics, pending I/O, I/O threads, file I/O and log statistics tables by default.

The next part that needs to be said is InnoDB Buffers. In this section, you can view information about the InnoDB buffer pool, page statistics, buffer import, and adaptive hash index.

In the next section, called InnoDB Row Ops, you will see the output of InnoDB row operations, row malfunctions, semaphores, and 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?

As you can see in the image below, the command summary mode shows the entire cmd_summary table.

The next section is callingg Variables and Status. In this section, like queries per second, statistics are calculating and displaying in a number of different modes.

Learn how to Setup Innotop to Monitor MySQL on CentOS

Note that another important part is the Replication Status. In this case, you can see the output of Slave SQL Status, Slave I/O Status and Master Status. The first two sections show slave status and I/O slave status, and the last section shows Master status.

_______________________ Slave SQL Status _______________________  Master On? TimeLag Catchup Temp Relay Pos Last Error  178.16.24.115 Yes 00:00 0.00 0 41295853    _______________________ Slave I/O Status _______________________  Master On? File Relay Size Pos State   178.16.24.115 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%

The next section is calling Non-Interactively. You can run “Innotop” non-interactively using the following command.

innotop --count 5 -d 1 -n

The final part is  Monitor Remote Database, which you can do using the following command and a username, password and hostname.

innotop -u username -p password -h hostname

Conclusion

Innotop is a very easy and useful tool that can provide many details of MySQL work. In this article, we tried to learn you how to Install Innotop to Monitor MySQL on CentOS. We have also described the various features and features of Innotop for you.