MySQL - Check Which Query is Consuming Resources

MySQL - Checking Which Query is Consuming Resources

Mysqllogo.gif
 

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

1 )Installing and Configuring Mytop

mytop is a console-based (non-gui) tool for monitoring the threads and overall performance of a MySQL servers.

Install mytop via Yum

If you can install with yum if you have the epel repo

yum install mytop

To install the EPEL repo

wget http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
wget http://rpms.famillecollet.com/enterprise/remi-release-6.rpm
rpm -Uvh remi-release-6*.rpm epel-release-6*.rpm

Manual Mytop Install

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

yum install perl-DBD-MySQL perl-TermReadKey perl-DBIx-Simple perl-ExtUtils-MakeMaker perl-Time-HiRes -y
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

This version has a broken line, that must be commented out before you can use mytop

sed -i 's/"long|/#"long|/g' $(which mytop)

Configure and Use Mytop

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
Mytop.png
 

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

2) 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>
  • 0 Users Found This Useful
Was this answer helpful?

Related Articles

What do I do if my VPS is offline or down?

Your VPS may be down or offline for a variety of reasons. In this article, we'll show you how to...

What kind of IPv6 support do you have?

Since OBHost operates out of multiple locations and datacenters, our IPv6 support varies per...

Setup vsftp with SELinux

Howto Setup vsftp with SELinux Vsftpd is a fast and secure FTP server. Installing an FTP server...

Setup Nginx PHP FPM Percona Mysql

Setup Nginx + php-fpm + Percona Mysql LEMP stack is a group of open source software to get web...

OpenVPN Tutorial For Debian/Ubuntu on OpenVZ

What is OpenVPN?OpenVPN is a reliable and well tested VPN solution that is able to run over a...