mytop เป็นเครื่องมือสำหรับเอาไว้วิเคราะห์ performance MySQL ช่วยให้เราสามารถดู Query ของ Database ที่มีปัญหาได้

http://jeremy.zawodny.com/mysql/mytop/

ก่อนติดตั้ง mytop ต้องลง perl modules ตามนี้ก่อน

1. Installing TermReadKey
Download ไฟล์มา compile ตามนี้ครับ
# 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

หากเกิด error ในขั้นตอนนี้ให้ดู error ที่แสดงขึ้นมา อาจเกิดจากสาเหตุบาง module ที่ระบบต้องการเราอาจจะไม่ได้ลงไว้ อย่างของผมไม่ได้ลง gcc ไว้จึงต้องทำการลงก่อน เมื่อลงครบหมดแล้ว ก็ให้ทดลอง run คำสั่งเดิมอีกรอบ
# make
# make install

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

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

4.ทำการ create mysql user

# mysql  -u root -p
# grant super, reload, process on *.* to mytop@localhost;

# flush privileges;

5.ทำการสร้างไฟล์ config ของ mytop

# cd
# vim .mytop

**********************************

prompt=1
user=root
db=mysql
host=localhost
delay=3
batchmode=0
header=1
color=1
idle=1
sort=1

**********************************

มี Argument ต่างๆ ที่เอาไว้ใช้ดังต่อไปนี้

-u or -user username

Username to use when logging in to the MySQL server. Default: “root”.

-p or -pass or -password password

Password to use when logging in to the MySQL server. Default: none.

-h or -host hostname[:port]

Hostname of the MySQL server. The hostname may be followed by an option port number. Note that the port is specified separate from the host when using a config file. Default: “localhost”.

-port or -P port

If you’re running MySQL on a non-standard port, use this to specify the port number. Default: 3306.

-s or -delay seconds

How long between display refreshes. Default: 5 (หรือจะไปกำหนดเอาไว้ .mytop ก็ได้)

-d or -db or -database database

Use if you’d like mytop to connect to a specific database by default. Default: “test”.

-b or -batch or -batchmode

In batch mode, mytop runs only once, does not clear the screen, and places no limit on the number of lines it will print. This is suitable for running periodically (perhaps from cron) to capture the information into a file for later viewing. You might use batch mode in a CGI script to occasionally display your MySQL server status on the web.Default: unset.

-S or -socket /path/to/socket

If you’re running mytop on the same host as MySQL, you may wish to have it use the MySQL socket directly rather than a standard TCP/IP connection. If you do,just specify one.Note that specifying a socket will make mytop ignore any host and/or port that you might have specified. If the socket does not exist (or the file specified is not a socket), this option will be ignored and mytop will use the hostname and port number instead.

Default: none.

-header or -noheader

Sepcify if you want the header to display or not. You can toggle this with the h key while mytop is running.Default: header.

-color or -nocolor

Specify if you want a color display. This has no effect if you don’t have color support available.Default: If you have color support, mytop will try color unless you tell it not to.

-i or -idle or -noidle

Specify if you want idle (sleeping) threads to appear in the list. If sleeping threads are omitted, the default sorting order is reversed so that the longest running queries appear at the top of the list.Default: idle.

นอกนั้นก็จะมี Shortcut Key ต่างๆ เอาไว้เรียกใช้งาน

? Display help

c Show “command counters” based on the Com_* values in SHOW STATUS. This is a new feature. Feedback welcome.

d Show only threads connected to a particular database.

f Given a thread id, display the entire query that thread was (and still may be) running.

F Disable all filtering (host, user, and db).

h Only show queries from a particular host.

H Toggle the header display. You can also specify either header=0 or header=1 in your config file to set the default behavior.

i Toggle the display of idle (sleeping) threads. If sleeping threads are filtered, the default sorting order is reversed so that the longest running queries appear at the top of the list.

k Kill a thread

m Toggle modes. Currently this switches from `top’ mode to `qps’ (Queries Per Second Mode). In this mode, mytop will write out one integer per second. The number written reflects the number of queries executed by the server in the previous one second interval.

More modes may be added in the future.

o Reverse the default sort order.

p Pause display.

q Quit mytop

r Reset the server’s status counters via a FLUSH STATUS command.

s Change the sleep time (number of seconds between display refreshes).

u Show only threads owned by a giver user.

The s key has a command-line counterpart: -s.

The h key has two command-line counterparts: -header and -noheader.

---