Innotop is an excellent command-line program, similar to the top command, designed to monitor local and remote MySQL servers running under the InnoDB engine.

It provides a comprehensive set of features and options that help database administrators (DBAs) track various aspects of MySQL performance, troubleshoot issues, and optimize server configurations.

Innotop allows you to monitor critical MySQL metrics such as:

  • MySQL replication status
  • User statistics
  • Query lists
  • InnoDB buffers
  • InnoDB I/O statistics
  • Open tables
  • Locked tables
  • And more…

The tool refreshes its data regularly, providing real-time insights into the server’s state.

Install Innotop (MySQL Monitoring) Tool in Linux

Innotop is available in most Linux distributions‘ package managers and you can install it using the following commands based on your distribution:

sudo apt install innotop         [On Debian, Ubuntu and Mint]
sudo yum install innotop         [On RHEL/CentOS/Fedora and Rocky/AlmaLinux]
sudo emerge -a sys-apps/innotop  [On Gentoo Linux]
sudo apk add innotop             [On Alpine Linux]
sudo pacman -S innotop           [On Arch Linux]
sudo zypper install innotop      [On OpenSUSE]    
sudo pkg install innotop         [On FreeBSD]

If Innotop is not available in your distribution’s package repository, you can clone the GitHub repository and install it manually:

git clone https://github.com/innotop/innotop.git
cd innotop

Next, you need to install some Perl dependencies using 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 yum install cpanminus         [On RHEL/CentOS/Fedora and Rocky/AlmaLinux]
sudo emerge -a sys-apps/cpanminus  [On Gentoo Linux]
sudo apk add cpanminus             [On Alpine Linux]
sudo pacman -S cpanminus           [On Arch Linux]
sudo zypper install cpanminus      [On OpenSUSE]    
sudo pkg install cpanminus         [On FreeBSD]

Then, install the required Perl modules using cpanminus.

cpanm Term::ReadKey DBI DBD::mysql

After installing the dependencies, you can run Innotop by executing the following command.

perl innotop

Alternatively, if you’ve installed Innotop via cpanminus, you can start it directly.

innotop

Using Innotop to Monitor MySQL Performance

Once Innotop is running, you can connect to your MySQL server by specifying the username and password with the following command:

innotop -u root -p 'your_password'

Upon connecting to the MySQL server, you will see a dashboard similar to this:

Innotop: MySQL Monitoring Tool
Innotop: MySQL Monitoring Tool

Press ? to view a summary of command-line options and usage.

Innotop MySQL Monitoring Tool
Innotop MySQL Monitoring Tool

Modes in Innotop

You can switch between different modes by pressing the corresponding keys:

Query List

Displays the output from SHOW FULL PROCESSLIST, similar to the query list mode in mytop. It does not display InnoDB-specific information but is useful for general query monitoring.

Analyze MySQL Query Performance
Analyze MySQL Query Performance

InnoDB I/O Info

Displays InnoDB’s I/O statistics, including pending I/O, I/O threads, file I/O, and log statistics.

Monitor InnoDB I/O Performance
Monitor InnoDB I/O Performance

InnoDB Buffers

Displays information about the InnoDB buffer pool, page statistics, insert buffer, and adaptive hash index.

InnoDB Performance Metrics
InnoDB Performance Metrics

InnoDB Row Operations

Shows InnoDB row operations, including inserts, updates, reads, and deletes.

InnoDB Row Operations
InnoDB Row Operations

Command Summary

Displays the summary of commands executed on the MySQL server, including the number of times each command has been executed.

MySQL Command Summary
MySQL Command Summary

Variables & Status

Displays various server variables and status information, such as queries per second, connections, and cache usage.

Server Status and Metrics
Server Status and Metrics
Conclusion

Innotop is a powerful tool for MySQL monitoring that provides real-time insights into various aspects of MySQL performance. With its easy-to-use command-line interface, it helps DBAs identify issues, optimize queries, and monitor the health of MySQL servers.

Whether you’re managing a single server or multiple MySQL instances, Innotop offers an efficient way to keep track of performance metrics and ensure smooth database operations.

Similar Posts