Q. How do I find out which table is consuming resources under MySQL Database server from UNIX shell prompt?
A. You can use mytop command a console-based (non-gui) tool for monitoring the threads and overall performance of a MySQL.
Install mytop
First install required perl modules:
cpan -i Term::ReadKey
cpan -i Term::ANSIColor
cpan -i Time::HiRes
Next, install mytop, enter:
wget http://jeremy.zawodny.com/mysql/mytop/mytop-1.6.tar.gz
tar -zxvf mytop-1.6.tar.gz
cd mytop-1.6
perl Makefile.PL
make
make test
make install
To view information about database called foo, enter:
$ mytop -u dba -p 'password' -h 10.2.4.5 -d foo
Where,
- -u : Database username.
- -p : Database password.
- -h : Database server IP address.
- -d : Database name.
Read mytop man page for further information:
perldoc mytop
You should follow me on twitter here or grab rss feed to keep track of new changes.
Featured Articles:
- 30 Handy Bash Shell Aliases For Linux / Unix / Mac OS X
- Top 30 Nmap Command Examples For Sys/Network Admins
- 25 PHP Security Best Practices For Sys Admins
- 20 Linux System Monitoring Tools Every SysAdmin Should Know
- 20 Linux Server Hardening Security Tips
- Linux: 20 Iptables Examples For New SysAdmins
- Top 20 OpenSSH Server Best Security Practices
- Top 20 Nginx WebServer Best Security Practices
- 20 Examples: Make Sure Unix / Linux Configuration Files Are Free From Syntax Errors
- 15 Greatest Open Source Terminal Applications Of 2012
- My 10 UNIX Command Line Mistakes
- Top 10 Open Source Web-Based Project Management Software
- Top 5 Email Client For Linux, Mac OS X, and Windows Users
- The Novice Guide To Buying A Linux Laptop
![[ERROR] /usr/local/libexec/mysqld: unknown variable ‘thread_concurrency=8′](http://s13.cyberciti.org/images/shared/rp/3/8.jpg)
{ 3 comments… read them below or add one }
Very good way to install mytop.
However there is one small step missing. After tar one needs to go into mytop directory to run perl Makefile.PL. So the steps would be as follows:
wget http://jeremy.zawodny.com/mysql/mytop/mytop-1.6.tar.gz
tar -zxvf mytop-1.6.tar.gz
cd mytop-1.6
perl Makefile.PL
make
make test
make install
Thank you.
Sachin.
Thanks for the heads up. The faq has been updated.
hi vivek,
my suggestion is that when you describe such tips like installing a package etc, i guess you should tell how to do this in other os also. for eg, in freebsd mytop can be installed in from /usr/ports/databases/mytop.
A drawback which I have seen which this tool is that it will create a database called ‘test’ and many control panels like cPanel will not allow this db.
So, a much reliable tool is the command ” mysqladmin -v processlist ” which provides a comprehensive data about the queries and using the mysql command ” explain $query ” to dee what exactly the particular query is doing and why it is resourse consuming.
Vinod :)