How to Monitor MySQL Performance Using Mytop on CentOS 7

In this article, we will learn how to install and configure the Mytop to monitor the MySQL performance. Mytop is an open-source monitoring tool for MySQL performance, this tool uses command line to monitor MySQL, it looks like the Linux System monitoring tool top which will connect to MySQL and runs the show process list and show global status commands and summarize the information in a meaning-full format where the humans can understand it. We can use mtop for monitor the MySQL real-time threads, uptime and queries and will also show the users running the queries and the database they are using, where this information can be used for performance tuning.

To complete this demo we needed CentOS 7 64-bit and a user with root privileges, MySQL installed.

Installing Mytop on Centos 7

As the Mytop is not available from the Centos repository we needed to install the Extra package for Enterprises Linux (EPEL) on the server. This EPEL repository is mainted by a group who maintains, creates and manages the high quality set of open source add on packages for Linux.

Below is the command to install and enable the EPEL repository on the server machine.

# yum install epel-release
output
Loaded plugins: fastestmirror
Dependencies Resolved
=======================================================================================================================
Package Arch Version Repository Size
=======================================================================================================================
Updating:
epel-release noarch 7-7 epel 14 k
Transaction Summary
=======================================================================================================================
Upgrade 1 Package
Total download size: 14 k
Is this ok [y/d/N]: y
Downloading packages:
Delta RPMs disabled because /usr/bin/applydeltarpm not installed.
warning: /var/cache/yum/x86_64/7/epel/packages/epel-release-7-7.noarch.rpm: Header V3 RSA/SHA256 Signature, key ID 352c64e5: NOKEY
Public key for epel-release-7-7.noarch.rpm is not installed
epel-release-7-7.noarch.rpm | 14 kB 00:00:02
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7
Importing GPG key 0x352C64E5:
Userid : "Fedora EPEL (7) <epel@fedoraproject.org>"
Fingerprint: 91e9 7d7c 4a5e 96f1 7f3e 888f 6a2f aea2 352c 64e5
Package : epel-release-7-6.noarch (@extras)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7
Is this ok [y/N]: y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Updating : epel-release-7-7.noarch 1/2
Cleanup : epel-release-7-6.noarch 2/2
Verifying : epel-release-7-7.noarch 1/2
Verifying : epel-release-7-6.noarch 2/2
Updated:
epel-release.noarch 0:7-7
Complete!

To verify the yum repository list updated or not we can use below command

# yum repolist
output
yumrepolist
Loaded plugins: fastestmirror
Determining fastest mirrors
epel/metalink | 12 kB 00:00
* base: linux.cc.lehigh.edu
* epel: ftp.osuosl.org
* extras: mirror.fusioncloud.co
* remi-safe: fr.mirror.babylon.network
* rpmforge: mirror.lug.udel.edu
* updates: centos.mirror.constant.com
base | 3.7 kB 00:00
base/primary_db | 4.7 MB 00:00
epel | 4.3 kB 00:00
epel/primary_db | 5.8 MB 00:00
extras | 3.4 kB 00:00
extras/primary_db | 36 kB 00:00
remi-safe | 2.9 kB 00:00
remi-safe/primary_db | 285 kB 00:00
rpmforge | 1.9 kB 00:00
updates | 3.4 kB 00:00
updates/primary_db | 726 kB 00:00
repo id repo name status
WandiscoSVNWandisco SVN Repo 73
base CentOS-6 - Base 6,696
epel Extra Packages for Enterprise Linux 6 - x86_64 12,156
extras CentOS-6 - Extras 60
remi-safe Safe Remi's RPM repository for Enterprise Linux 6 - x86_64 659
rpmforge RHEL 6 - RPMforge.net - dag 245
updates CentOS-6 - Updates 131
repolist: 20,000

As we have updated the EPEL repository on the server, Now we can install the mytop package using the EPEL repository with the below command.

# yum install mytop –y
output
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: centos.webwerks.com
* epel: mirror.rise.ph
* extras: centos.webwerks.com
* updates: centos.webwerks.com
Resolving Dependencies
--> Running transaction check
….
….
….
Dependency Installed:
perl.x86_64 4:5.16.3-286.el7 perl-Carp.noarch 0:1.26-244.el7
perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7 perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7
perl-DBD-MySQL.x86_64 0:4.023-5.el7 perl-DBI.x86_64 0:1.627-4.el7
perl-Data-Dumper.x86_64 0:2.145-3.el7 perl-Encode.x86_64 0:2.51-7.el7
perl-Exporter.noarch 0:5.68-3.el7 perl-File-Path.noarch 0:2.09-2.el7
perl-File-Temp.noarch 0:0.23.01-3.el7 perl-Filter.x86_64 0:1.49-3.el7
perl-Getopt-Long.noarch 0:2.40-2.el7 perl-HTTP-Tiny.noarch 0:0.033-3.el7
perl-IO-Compress.noarch 0:2.061-2.el7 perl-Net-Daemon.noarch 0:0.48-5.el7
perl-PathTools.x86_64 0:3.40-5.el7 perl-PlRPC.noarch 0:0.2020-14.el7
perl-Pod-Escapes.noarch 1:1.04-286.el7 perl-Pod-Perldoc.noarch 0:3.20-4.el7
perl-Pod-Simple.noarch 1:3.28-4.el7 perl-Pod-Usage.noarch 0:1.63-3.el7
perl-Scalar-List-Utils.x86_64 0:1.27-248.el7 perl-Socket.x86_64 0:2.010-3.el7
perl-Storable.x86_64 0:2.45-3.el7 perl-TermReadKey.x86_64 0:2.30-20.el7
perl-Text-ParseWords.noarch 0:3.29-4.el7 perl-Time-HiRes.x86_64 4:1.9725-3.el7
perl-Time-Local.noarch 0:1.2300-2.el7 perl-constant.noarch 0:1.27-2.el7
perl-libs.x86_64 4:5.16.3-286.el7 perl-macros.x86_64 4:5.16.3-286.el7
perl-parent.noarch 1:0.225-244.el7 perl-podlators.noarch 0:2.5.1-3.el7
perl-threads.x86_64 0:1.87-4.el7 perl-threads-shared.x86_64 0:1.43-6.el7
Complete!

Configuring the Mytop Package on CentOS 7

We have to configure the mytop using the customized file .mytop to create the configuration file, run the below command, add the configuration given below –

# vi ~/.mytop

host=localhost
db=mysql
delay=10
port=3306
socket=
batchmode=0
color=1
idle=2

This configuration file will be used when we run the mytop command from the root user login.

Using and Connecting to my Top

Below is the command used to check the performance of the mysql where –prompt is used to prompt the mytop for the mysql database password. We needed to enter the mysql root user password and simply press enter.

# mytop –prompt
output
MySQL on localhost (5.6.31) up 0+00:01:04 [06:25:39]
Queries: 5.0 qps: 0 Slow: 0.0 Se/In/Up/De(%): 00/00/00/00
qps now: 1 Slow qps: 0.0 Threads: 1 ( 1/ 0) 00/00/00/00
Key Efficiency: 100.0% Bps in/out: 9.3/381.8 Now in/out: 19.5/ 3.7k
Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
3 root localhost mysql 0 Query show full processlist

Now we will use the specific user to run the mytop command

# mytop -u root –p
outputMySQL on localhost (5.6.31) up 0+00:11:40 [06:36:15] Queries: 131.0 qps: 0 Slow: 0.0 Se/In/Up/De(%): 01/00/00/00 Key Efficiency: 100.0% Bps in/out: 6.2/870.8 Id User Host/IP DB Time Cmd Query or State -- ---- ------- -- ---- --- ---------- 8 root localhost mysql 0 Query show full processlist

In the above example we have used the below options

-u -> This option is used for specifying the mytop to use specific SQL use for loging in to the MySQL
-p -> will use the user login password

Other options can be used

-h -> to specify the ports or hostname for the MySQL database.
-s -> to specify the delay in seconds the default time is 5 seconds

To see the full options available with my top we can use the below command

# man mytop

Shortcut Keys for mytop Command

The following keys performs various actions while mytop is running. Those which have not been implemented are listed as such. They are included to give the user an idea of what is coming.

? Display help.
c Show "command counters" based on the Com_* values in SHOW GLOBAL STATUS. This is a new feature.
Feedback welcome.
d Show only threads connected to a particular database.
f Given a thread id, display the entire query that thread was (and still may be) running.
F Disable all filtering (host, user, and db).
h Only show queries from a particular host.
H Toggle the header display. You can also specify either "header=0" or "header=1" in your config file to
set the default behavior.
i Toggle the display of idle (sleeping) threads. If sleeping threads are filtered, the default sorting
order is reversed so that the longest running queries appear at the top of the list.
I Switch to InnoDB Status mode. The output of "SHOW INNODB STATUS" will be displayed every cycle. In a
future version, this may actually summarize that data rather than producing raw output.
k Kill a thread.
m Toggle modes. Currently this switches from `top' mode to `qps' (Queries Per Second Mode). In this mode,
mytop will write out one integer per second. The number written reflects the number of queries executed
by the server in the previous one second interval.
More modes may be added in the future.
o Reverse the default sort order.
p Pause display.
q Quit mytop
r Reset the server's status counters via a FLUSH STATUS command.
s Change the sleep time (number of seconds between display refreshes).
u Show only threads owned by a giver user.

By using the above article we will learn how to use the mytop command and some options used to monitor the mysql performance so that we can tune the database accordingly. We can explore more command and usage we can also explore the other options using man mytop command.