Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
558 Views
To Continue Learning Please Login