MySQL - Check Which Query is Consuming Resources

MySQL - Checking Which Query is Consuming Resources

Have you ever wondered which mysql query is consuming the most resources? We can check this by running doing a few things.

Installing and Configuring Mytop

mytop is a console-based (non-gui) tool for monitoring the threads and overall performance of a MySQL 3.22.x, 3.23.x, and 4.x server. It runs on most Unix systems (including Mac OS X) which have Perl, DBI, and Term::ReadKey installed.

First lest install the required perl modules:

cpan -i Term::ReadKey && cpan -i Term::ANSIColor && cpan -i Time::HiRes 

Install mytop

If you can install with yum or apt-get

yum install mytop

or

apt-get install mytop

But if those do not work you can download from their website and install

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

Now you can configure mytop by either using the ~/.mytop configuration file or by passing the information when starting up mytop

First lets pass the variables on running mytop

mytop -u root -p 'password' -h 127.0.0.1 -d test

Now by creating the configuration file, so you do not have your password in your history file.

vim ~/.mytop

Insert your configuration information

        user=root
        pass=password
        host=127.0.0.1
        db=
        delay=1
        port=3306
        socket=
        batchmode=0
        header=1
        color=1
        idle=1

Then save the file and type

mytop

If all is good, you are now able to run mytop.

To kill a process hit k, to see the entire query hit e

Show full process list

You can see what mysql processes are taking the longest run by checking the run time.

mysql> show full processlist \G;

This will show the process that are taking the longest to run. The longer the process runs, the more resources the query is using.

If this this a SELECT statement chances are you can kill this process and give the resources back to the system.

kill <process id>