MySQL 优化工具 mtop

最近在做mysql优化，发现[url=http://mtop.sourceforge.net/]mtop[/url]比较简单好用,可以实时的监控MySQL

安装

    perl Makefile.PL
    make
    make install

使用：

mtop -dbu mysql_username -p mysql_password -se 1

对慢语句使用了紫，黄，红颜色高亮，使用起来非常简单方便。

[b]OPTIONS[/b]

All options can be abbreviated by their shortest unique abbreviation.

-?, --help
Show the help screen and exit.

-v, --version
Show the version number and exit.

-h {mysql_host}, --host={mysql_host}
By default, the mysqld on localhost is monitored. Specify an alternate host with this option.

-dbu {mysql_user}, --dbuser={mysql_user}
By default, the user 'mysqltop' is used to connect to the database. Specify an alternate user with this option.

-p {mysqluser_pw}, --password={mysqluser_pw}
By default, there is no password associated with the mysqltop user, specify a password with this option.

-se {refresh}, --seconds={refresh}
The default screen refresh is 5 seconds.

-sl {seconds}, --slow={seconds}
The number of seconds before a slow query is highlighted. The default is the server's long_query configuration variable.

-vs {seconds}, --veryslow={seconds}
The number of seconds before a very slow query is highlighted. The default is the the --slow option * 2.

-vvs {seconds}, --veryveryslow={seconds}
The number of seconds before a very very slow query is highlighted. The default is the the --slow option * 4.

-i, --[no]idle
By default, processes in the Sleep command state are not shown. This option turns on display of idle threads.

-u {user}, --user={user}
Show only threads owned by this user.

-fu {regex_pattern}, --filter-user={regex_pattern}
-fh {regex_pattern}, --filter-host={regex_pattern}
-fd {regex_pattern}, --filter-db={regex_pattern}
-fs {regex_pattern}, --filter-state={regex_pattern}
-fc {regex_pattern}, --filter-command={regex_pattern}
-fi {regex_pattern}, --filter-info={regex_pattern}
Filter the display based on the regex_pattern provided. The regex_pattern is a perl regular expression. The regular expression match is done with case insensitivity.

For example, to only show select statements on the user table, use the following:

--filter-info='select from user'

or, to be more forgiving for mutil-table joins and extra spaces, use:

--filter-info='select\s+from\s+.*\buser\b.*where'

These same regular expression filters can be used with the interactive d command. Be careful to escape any special shell characters in the regex.

-m, --manualrefresh
In this mode, the screen only refreshes when the user hits a key on the keyboard. The screen will refresh automatically until a query is seen and then wait for further input. An uppercase M will appear in the top right hand corner of the screen to indicate that you are in this mode.

All options can be stored in initialization files. Command line options override options stored in the initialization file(s). The following files are checked for arguments: current direcotry .mtoprc, home directory .mtoprc, /usr/local/etc/mtoprc, /etc/mtoprc. Options in the former files override options in the later files.

The format of the initialization file is one option per line. Options are specified just as they would be on the command line. They can be abbreviated and use the one or two hyphen syntax. Comments and blank lines are ignored. The following is an exmple .mtoprc file which sets a user filter to user1 and sets the refresh rate to one second:

# Only look at 'user1'
-fu user1
--seconds=1 # refresh every one seconds