Difference between revisions of "MySQL - Check Which Query is Consuming Resources"

From Brian Nelson Ramblings
Jump to: navigation, search
(→‎1 )Installing and Configuring Mytop)
(→‎2) Show full process list)
 
(14 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
==MySQL - Checking Which Query is Consuming Resources==
 
==MySQL - Checking Which Query is Consuming Resources==
−
 
+
[[File:Mysqllogo.gif|thumbnail|right]]
 
Have you ever wondered which mysql query is consuming the most resources?  We can check this by running doing a few things.   
 
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===
 
=== 1 )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.  
+
mytop is a console-based (non-gui) tool for monitoring the threads and overall performance of a MySQL servers.  
  
−
First lest install the required perl modules:
+
===Install mytop via Yum===
  
−
cpan -i Term::ReadKey && cpan -i Term::ANSIColor && cpan -i Time::HiRes
+
If you can install with yum if you have the epel repo
  
−
'''Install mytop'''
+
yum install mytop
  
−
If you can install with yum or apt-get
+
To install the EPEL repo
  
−
  yum install mytop
+
  wget http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
−
or
+
wget http://rpms.famillecollet.com/enterprise/remi-release-6.rpm
−
  apt-get install mytop
+
  rpm -Uvh remi-release-6*.rpm epel-release-6*.rpm
  
 +
===Manual Mytop Install===
 
But if those do not work you can download from their [http://jeremy.zawodnycom./mysql/mytop/ website] and install
 
But if those do not work you can download from their [http://jeremy.zawodnycom./mysql/mytop/ 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
 
  wget http://jeremy.zawodny.com/mysql/mytop/mytop-1.6.tar.gz
 
  tar -zxvf mytop-1.6.tar.gz
 
  tar -zxvf mytop-1.6.tar.gz
Line 28: Line 30:
 
  make test
 
  make test
 
  make install
 
  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
 
Now you can configure mytop by either using the ~/.mytop configuration file or by passing the information when starting up mytop
Line 38: Line 46:
  
 
  vim ~/.mytop
 
  vim ~/.mytop
−
 
+
[[File:Mytop.png|framed|right]]
 
Insert your configuration information
 
Insert your configuration information
 
         user=root
 
         user=root
Line 57: Line 65:
 
If all is good, you are now able to run 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''
+
'''To kill a process hit k, to see the entire query hit e'''
  
 
=== 2) Show full process list===
 
=== 2) Show full process list===

Latest revision as of 22:26, 19 December 2013

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>
Retrieved from "https://briansnelson.com/index.php?title=MySQL_-_Check_Which_Query_is_Consuming_Resources&oldid=302"