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.

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

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


Then save the file and type


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 A felhasználók hasznosnak találták ezt
Hasznosnak találta ezt a választ?

Kapcsolódó cikkek

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

How do I SSH into my VPS? (OS X/Terminal)

If you are on a Mac, SSH-ing into your VPS is very simple. Simply launch "Terminal" by going to...

How do I SSH into my VPS? (Windows/Putty)

This article will teach you how to access your server via SSH using the PuTTY client on Windows....

Install Remi and EPEL yum repo Centos 7

Install Yum EPEL and Remi repositories on Centos 7 Adding additional useful repo's on Centos 6...

Install PHP/PHP-FPM 5.4 Centos 7

Install PHP/PHP-FPM 5.4 on Centos 7 PHP is a server-side scripting language designed for web...