MySQL: Find Out Which Table is Consuming Resources

last updated in Categories , , , , , , , , , , ,

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:
tar -zxvf mytop-1.6.tar.gz
cd mytop-1.6
perl Makefile.PL
make test
make install

To view information about database called foo, enter:
$ mytop -u dba -p 'password' -h -d foo

  • -u : Database username.
  • -p : Database password.
  • -h : Database server IP address.
  • -d : Database name.

Read mytop man page for further information:
perldoc mytop

Posted by: Vivek Gite

The author is the creator of nixCraft and a seasoned sysadmin, DevOps engineer, and a trainer for the Linux operating system/Unix shell scripting. Get the latest tutorials on SysAdmin, Linux/Unix and open source topics via RSS/XML feed or weekly email newsletter.

3 comment

  1. 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 :)

    Still, have a question? Get help on our forum!