Difference between revisions of "MySQL - Check Which Query is Consuming Resources"
(Created page with "==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 th...") |
(→2) Show full process list) |
||
| (16 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. | ||
| − | ===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 | + | 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 [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''' |
| − | ===Show full process list=== | + | === 2) Show full process list=== |
You can see what mysql processes are taking the longest run by checking the run time. | You can see what mysql processes are taking the longest run by checking the run time. | ||
Latest revision as of 22:26, 19 December 2013
Contents
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
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
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>
