Monitor Threads and Performance of MySQL Database using “MyTop”

By: Published: Jan 7th, 2013 Category: Cpanel, DirectAdmin, Linux Tips, MySQL

Database plays a very important part in any CMS or website as it stores the complete data but this database sometime becomes headache. Your website start running slow and when you contact your hosting company you get reply “You need to optimize your database” OR sometime for hosting company also its very hard to explain high resource usage by MySQL Database as usually System Admins are depended on “top” command which doesn’t provide accurate information.

MyTop is a console based utility which helps user to monitor the threads and performance of MySQL Database Server. This utility is easy to install and useful for Database Administrators, Server Administrators. In this article I will explain how to install and use MyTop.

1) Download: Download latest version of myTop from http://jeremy.zawodny.com/mysql/mytop/ using command wget

root@server [~]# wget http://jeremy.zawodny.com/mysql/mytop/mytop-1.6.tar.gz

mtop1 300x44 Monitor Threads and Performance of MySQL Database using MyTop

2) Extract and go to mytop directory

root@server [~]# tar -zxvf mytop-1.6.tar.gz
root@server [~]# cd mytop-1.6
root@server [~/mytop-1.6]

mtop2 300x239 Monitor Threads and Performance of MySQL Database using MyTop

3) Installation of MyTop: Copy and Paste following commands to install MyTOP on your Server

root@server [~/mytop-1.6]# perl Makefile.PL
root@server [~/mytop-1.6]# make
root@server [~/mytop-1.6]# make test
root@server [~/mytop-1.6]# make install

mtop3 300x113 Monitor Threads and Performance of MySQL Database using MyTop

4) Configure MyTop: To get results from MyTop you have user various command-line arguments like username, password, hostname (you can check all options using command “man mytop”)etc. everytime OR you can setup a config file and mention everything in that.
Create and open file “~/.mytop” (copy and paste name as written) using your favraite editor and then copy paste following conent

user=[database user]
pass=[password of the user]
host=[IP or hostname of the host]
db=[database name]
delay=5
port=[Port Number]
socket=
batchmode=0
header=1
color=1
idle=1

Make sure it should look like:

mtop4 300x161 Monitor Threads and Performance of MySQL Database using MyTop

5) Checking Mysql Usage: You can check the MySQL usage using command “mytop

root@server [~]# mytop

Which will show output like below

mtop5 300x56 Monitor Threads and Performance of MySQL Database using MyTop

*** Problem: When you execute mytop you will get error “Error in option spec: “long|!”

root@server [~/mytop-1.6]# mytop
Error in option spec: “long|!”
root@server [~/mytop-1.6]#

mtop6 300x42 Monitor Threads and Performance of MySQL Database using MyTop

To fix this error you have to edit mytop script. To find the exact path of mytop script use command “which

root@server [~]# which mytop
/usr/local/bin/mytop
root@server [~]#

Now edit “/usr/local/bin/mytop” using any editor like vi, nano etc. and change

“long|!” => $config{long_nums},

TO:

“long|long_nums|l!” => $config{long_nums},

Save and Exit and you will able to run mytop without any problem.

 Monitor Threads and Performance of MySQL Database using MyTop

About 

Milind Koyande is a Project Manager and his job is to work with new technologies, specially Cloud Computing / Virtualization Technology. His past projects include Government Sector initiatives, Backup and Disaster Recovery Solutions.

Tags: , , , , , , ,

2 Responses to “Monitor Threads and Performance of MySQL Database using “MyTop””


  1. Monitor threads and performance of MySQL Database Server « .:][ Gr8 Admin Team ][:.
    on Jun 7th, 2011
    @ 4:07 pm

    […] Nice tool to monitor the threads and performance of MySQL Database Server Amplify’d from eitwebguru.com […]


  2. How to change & set Data and time on Citrix Xen VM « .:][ Gr8 Admin Team ][:.
    on Jun 29th, 2011
    @ 8:18 am

    […] Read more at eitwebguru.com   See this Amp at http://amplify.com/u/a168xr […]

Leave a Reply

Subscribe to eITWebguru

© 2012 eITWebguru. All Rights Reserved.