mtop (MySQL top) is an open source real time MYSQL Server monitoring program written in Perl language that shows queries which are taking longer time to process and kills those longer queries after certain number of specified time. Mtop program enable us to monitor and identify performance and related issues of MySQL Server from the command line interface similar to Linux Top Command.
Install Mtop MySQL Monitoring
Mtop includes zooming feature that display query optimizer information of a running queries and killing queries, it also shows statistics of server, configuration information and some useful tuning tips to optimize and improve MySQL performance.
Please check some of the following features offered by Mtop program.
- Display real time MySQL server queries.
- Provides MySQL configuration information.
- Zooming feature to display process query.
- Provides query Optimizer information for a query and ‘killing’ queries.
- Provides MySQL tuning tips.
- Ability to save output in a .mtoprc configuration file.
- Provides Sysadmin recommendation page (‘T‘).
- Added queries/second to main header.
- Added per second info to stats screen.
In this article we’re going to show how to install Mtop (MySQL Top) program under RHEL 7/6.3/6.2/6.1/6/5.8/5.6/4.0, CentOS 7/6.3/6.2/6.1/6/5.8/5.6/4.0 and Fedora 17,16,15,14,13,12 using RPMForgerepository via YUM Command.
Enable RPMForge Repository in RHEL/CentOS 6/5/4 and Fedora 17-12
First, you need to enable RPMForge repository under your Linux machine to download and install latest version of MTOP program.
Install RPMForge on RHEL/CentOS 6
Select the following links based on your Linux architecture to enable RPMforge repository under your Linux box. (Note : Fedora user’s don’t need to enable any repository under Fedora box).
For RHEL/CentOS 6 32-Bit OS
# wget http://packages.sw.be/rpmforge-release/rpmforge-release-0.5.2-2.el6.rf.i686.rpm # rpm -Uvh rpmforge-release-0.5.2-2.el6.rf.i686.rpm
For RHEL/CentOS 6 64-Bit OS
# wget http://packages.sw.be/rpmforge-release/rpmforge-release-0.5.2-2.el6.rf.x86_64.rpm # rpm -Uvh rpmforge-release-0.5.2-2.el6.rf.x86_64.rpm
Install RPMForge on RHEL/CentOS 5
For RHEL/CentOS 5 32-Bit OS
# wget http://packages.sw.be/rpmforge-release/rpmforge-release-0.5.2-2.el5.rf.i386.rpm # rpm -Uvh rpmforge-release-0.5.2-2.el5.rf.i386.rpm
For RHEL/CentOS 5 64-Bit OS
# wget http://packages.sw.be/rpmforge-release/rpmforge-release-0.5.2-2.el5.rf.x86_64.rpm # rpm -Uvh rpmforge-release-0.5.2-2.el5.rf.x86_64.rpm
Install RPMForge on RHEL/CentOS 4
For RHEL/CentOS 4 32-Bit OS
# wget http://packages.sw.be/rpmforge-release/rpmforge-release-0.5.2-2.el4.rf.i386.rpm # rpm -Uvh rpmforge-release-0.5.2-2.el4.rf.i386.rpm
For RHEL/CentOS 4 64-Bit OS
# wget http://packages.sw.be/rpmforge-release/rpmforge-release-0.5.2-2.el4.rf.x86_64.rpm # rpm -Uvh rpmforge-release-0.5.2-2.el4.rf.x86_64.rpm
Import RPMForge Repository Key in RHEL/CentOS 6/5/4
# wget http://dag.wieers.com/rpm/packages/RPM-GPG-KEY.dag.txt # rpm --import RPM-GPG-KEY.dag.txt
Install Mtop in RHEL/CentOS 6/5/4 and Fedora 17-12
Once you’ve installed and enabled RPMForge repository, let’s install MTOP using following YUM command.
# yum install mtop
Sample Output :
Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile rpmforge | 1.9 kB 00:00 rpmforge/primary_db 2.6 MB 00:19 Setting up Install Process Dependencies Resolved ================================================================================================================ Package Arch Version Repository Size ================================================================================================================ Installing: mtop noarch 0.6.6-1.2.el6.rf rpmforge 52 k Installing for dependencies: perl-Curses i686 1.28-1.el6.rf rpmforge 156 k Transaction Summary ================================================================================================================ Install 2 Package(s) Total download size: 208 k Installed size: 674 k Is this ok [y/N]: y Downloading Packages: (1/2): mtop-0.6.6-1.2.el6.rf.noarch.rpm | 52 kB 00:00 (2/2): perl-Curses-1.28-1.el6.rf.i686.rpm | 156 kB 00:01 ----------------------------------------------------------------------------------------------------------------- Total 46 kB/s | 208 kB 00:04 Running rpm_check_debug Running Transaction Test Transaction Test Succeeded Running Transaction Warning: RPMDB altered outside of yum. Installing : perl-Curses-1.28-1.el6.rf.i686 1/2 Installing : mtop-0.6.6-1.2.el6.rf.noarch 2/2 Verifying : perl-Curses-1.28-1.el6.rf.i686 1/2 Verifying : mtop-0.6.6-1.2.el6.rf.noarch 2/2 Installed: mtop.noarch 0:0.6.6-1.2.el6.rf Dependency Installed: perl-Curses.i686 0:1.28-1.el6.rf Complete!
Starting Mtop in RHEL/CentOS 6/5/4
To start Mtop program, you need to connect to your MySQL Server, using following command.
# mysql -u root -p
Then you need to create separate user called mysqltop and grant privileges to him under your MySQL server. To do, this just run the following commands in mysql shell.
mysql> grant super, reload, process on *.* to mysqltop; Query OK, 0 rows affected (0.00 sec) mysql> grant super, reload, process on *.* to mysqltop@localhost; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> quit; Bye
Running Mtop in RHEL/CentOS 6/5/4
Let’s start the Mtop program by executing below command. You will see sample output similar to below.
# mtop
Sample Outpit :
load average: 0.01, 0.00, 0.00 mysqld 5.1.61 up 5 day(s), 19:21 hrs 2 threads: 1 running, 0 cached. Queries/slow: 5/0 Cache Hit: 71.43% Opened tables: 0 RRN: 277 TLW: 0 SFJ: 0 SMP: 0 QPS: 0 ID USER HOST DB TIME COMMAND STATE INFO 322081 mysqltop localhost Query show full processlist
Monitor Remote MySQL Server using Mtop
Simply, type the following command to monitor any remote MySQL Server.
# mtop –host=remotehost –dbuser=username –password=password –seconds=1
Mtop Usage and Functions
Please use the following keys while mtop is running.
Filtering/display
- s – change the number of seconds to delay between updates
- m – toggle manual refresh mode on/off
- d – filter display with regular expression (user/host/db/command/state/info)
- F – fold/unfold column names in select statement display
- h – display process for only one host
- u – display process for only one user
- i – toggle all/non-Sleeping process display
- o – reverse the sort order
- q – quit
- ? – help
For more options and usage please see the man pages of mtop command by running “man mtop” on terminal.
Read Also :