MySQL: Find Out Which Table is Consuming Resources

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


🐧 Get the latest tutorials on Linux, Open Source & DevOps via RSS feed or Weekly email newsletter.

🐧 3 comments so far... add one


CategoryList of Unix and Linux commands
Disk space analyzersdf duf ncdu pydf
File Managementcat cp mkdir tree
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Modern utilitiesbat exa
Network UtilitiesNetHogs dig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg glances gtop jobs killall kill pidof pstree pwdx time vtop
Searchingag grep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04
3 comments… add one
  • Sachin Dec 24, 2008 @ 3:53

    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.

  • 🐧 nixCraft Dec 24, 2008 @ 4:42

    Sachin.

    Thanks for the heads up. The faq has been updated.

  • Vinod Dec 29, 2008 @ 4:30

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

Leave a Reply

Your email address will not be published.

Use HTML <pre>...</pre> for code samples. Still have questions? Post it on our forum