MySQL is one of the most important programs on a server, unfortunatly it is also pretty resource intensive. One a server it is not uncommon for a single user or even a query to take up the bulk of the servers cpu cycles. Mytop is a very useful program to see what queries a server is currently processing as well as which user is executing them. Think of mytop as top for mysql. If you see a lot from a user that means they are probably the hog. Mytop can also be useful for figuring out exactly which queries are causing the problem in the case of a self-designed website. The following is how to install mytop on the server and run it. Thanks to carlos for the actual commands
We are going to install 2 perl modules in addition to mytop to ensure that it will work.
Install TermReadKey:
cd /usr/local/src
wget http://search.cpan.org/CPAN/authors/id/J/JS/JSTOWE/TermReadKey-2.30.tar.gz
tar -zxf TermReadKey-2.30.tar.gz
cd TermRead*
perl Makefile.PL
make test
make
make install
cd ..
Now install DBI:
wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.48.tar.gz
tar -zxf DBI-1.48.tar.gz
cd DBI*
perl Makefile.PL
make test
make
make install
cd ..
Finally install mytop:
wget http://jeremy.zawodny.com/mysql/mytop/mytop-1.4.tar.gz
tar -zxf mytop-1.4.tar.gz
cd mytop*
perl Makefile.PL
make test
make
make install
Now simply run "mytop" and your done!
forgot tar xvzf
Just a note for newbies,
Dont forget to Extract the tar.gz files after downloading them:
tar xvzf TermReadKey-2.30.tar.gz
Then you can change directory (cd) to the files.
Do this with all three archives you download.
Regards,
Craig craig@shadeaux.net
tar -- oops
Oops thanks I did not even notice I had missed a pretty important step. I have gone back and fixed the guide so the tar commands are included =-)
errors with DBI
I had errors with 1.4.8 so I downloaded 1.5.0 but still get errors after make:
Failed 2/49 test scripts, 95.92% okay. 86/2445 subtests failed, 96.48% okay.
make: *** [test_dynamic] Error 29
anyone else getting errors? Running on RHEL3
~/.mytop config file
Hello,
Quick addition / question
The mytop site ssuggests using a config file as shown below this text- however I was wondering if anyone has gotten my top configured correctly- I am unsure of how to have it display activity on all of the servers databases.
Any idea would be great.
EDIT: I specified a random DB and it seems to work- but I'm not sure.- also I turned off color support because it doesnt work out to be terribly readable.
Here is a sample config file ~/.mytop which implements the defaults described above.
user=root
pass=
host=localhost
db=test
delay=5
port=3306
socket=
batchmode=0
header=1
color=1
idle=1
mytop config
I have never used the mytop configuration file BUT if you specify a random db it still has always showed all of the db for me. The default "test" also works fine to get it to show all of the traffic.
I have changed the
I have changed the usr/passwd data in the mytop-file but after starting mytop I get the information:
Cannot connect to MySQL server. Please check the:
* database you specified "test" (default is "test")
* username you specified "root" (default is "root")
* password you specified "" (default is "")
Why does mytop uses still the old data and not data I entered and saved in the mytop-config section?
Thanks,
Micyaro