Requires the following perl modules:
?
# perl -e 'use Curses'
# perl -e 'use DBI'
# perl -e 'use DBD::mysql'
# perl -e 'use Getopt::Long'
# perl -e 'use Net::Domain'
?
沒有輸出就是模塊已經安裝了，有錯誤就是相應的模塊沒有安裝。
可以在上面給出的地址下載相應模塊的最新版本。
安裝所需的 Perl 模塊：
# tar zxvf Curses-x.xx.tar.gz
# cd Curses*
# perl Makefile.PL
# make
# make install
?
yum 倉庫里有 perl-DBI ，所以直接 yum 安裝，
# yum -y install perl-DBI
?
因為是編譯安裝的 MySQL ，所以需要把 lib 的路徑加入 ld.so.conf ，
# vi /etc/ld.so.conf
/usr/local/mysql/lib/mysql
# ldconfig
?
雖然 yum 倉庫里有 perl-DBD-MySQL ，但其依賴 mysql ，所以還是編譯安裝，
# tar zxvf DBD-mysql-x.xxxx.tar.gz
# cd DBD-mysql*
# perl Makefile.PL --mysql_config=/usr/local/mysql/bin/mysql_config
# make
# make install
?
安裝 mtop ：
# tar zxvf mtop-0.6.6.tar.gz
# cd mtop-0.6.6
# perl Makefile.PL
# make
# make install
?
添加相應的 MySQL 數據庫用戶：
# mysql -uroot -pPASSWORD
mysql> GRANT RELOAD,PROCESS,SUPER ON *.* TO 'mtop'@'localhost' IDENTIFIED BY 'mtop';
mysql> FLUSH PRIVILEGES;
mysql> exit
?
運行：
因為自定義了 mysql.sock 的位置，所以需要做個鏈接 mtop 才能運行，
# ln -s /var/run/mysqld/mysql.sock /tmp/mysql.sock
?
# mtop --dbuser=mtop --password=mtop
?
參考資料：
http://mtop.sourceforge.net/MTOP(1p)???????????????????????????? User Contributed Perl Documentation??????????????????????????? MTOP(1p)
NAME
?????? mtop - Shows top mysql threads
SYNOPSIS
?????????? mtop [--host={mysql_host}] [--dbuser={mysql_user}]
?????????????? [--password={mysqluser_pw}] [--seconds={refresh}] [--[no]idle]
?????????????? [--filter-user={regex}] [--filter-host={regex}] [--filter-db={regex}]
?????????????? [--filter-command={regex}] [--filter-state={regex}] [--filter-info={{regex}}]
?????????????? [--fold-select-columns]
?????????????? [--user={user}] [--manualrefresh] [--slow={seconds}] [--vs|veryslow={seconds}]
?????????????? [--vvs|veryveryslow={seconds}]
?????????? mtop --help
?????????? mtop --version
DESCRIPTION
?????? Shows the MySQL commands consuming the greatest time.? By default, only non-sleeping threads are
?????? shown, the --idle option shows idle threads.? While running several keys will affect the operation of
?????? mtop.? Hitting h or ? will show the available options.
?????? Normally, run as a console program this will allow you to see errant or badly optimized queries as
?????? they will stay on the screen for a while.? However, if you are hunting for short lived queries, run-
?????? ning in the manualrefresh mode with a short refresh time will allow you to catch short lived queries
?????? as well.
?????? The following keys are active while mtop is running:
?????????? q - quit
?????????? ? - help
?????????? Filtering/display
?????????? s - change the number of seconds to delay between updates
?????????? m - toggle manual refresh mode on/off
?????????? d - filter display with regular expression (user/host/db/command/state/info)
?????????? F - fold/unfold column names in select statement display
?????????? h - display process for only one host
?????????? u - display process for only one user
?????????? i - toggle all/non-Sleeping process display
?????????? o - reverse the sort order
?????????? Control/Detail
?????????? k - kill processes; send a kill to a list of ids
?????????? e - explain a process; show query optimizer info
?????????? z - zoom in on a process; show sql statement detail
?????????? f - flush stats (reset show status variables)
?????????? t - show mysqld stats (show status/mysqladmin ext)
?????????? T - show short/important status
?????????? v - show mysqld variables (show variables/mysqladmin vars)
?????????? r - show replication status for master/slaves
?????? Main Screen
?????? The main query screen shows the following information as well as the currently active queries (expla-
?????? nations are from the MySQL online manual and references refer to the section in the manual where the
?????? explanation came from):
?????? n Threads: running, cached
?????????? The n Threads represents how many threads the mysqld has allocated.? One thread is allocated for
?????????? each user connection.? Additional threads are allocated for replication.
?????? Queries/slow: Total queries / Total SLOW QUERIES
?????????? The first number is the total number of queries sent to the server since the last ’flush status’
?????????? or since server start.? The second number is the number of queries that have taken more than
?????????? long_query_time. See section 4.9.5 The Slow Query Log.
?????? Cache Hit: Cache hit ratio
?????????? This is the percentage of times a key read is handled from the key buffer cache.? See section
?????????? 4.5.7.4 SHOW VARIABLES of the MySQL manual for more information.
?????? Opened tables: tables opened
?????????? MySQL has a cache for open tables.? If ’opened tables’ is high, your cache may be too small.
?????????? Look at the MySQL manual section: 5.4.7 How MySQL Opens and Closes Tables for further informa-
?????????? tion.
?????? RRN: Handler_read_rnd_next
?????????? Number of requests to read the next row in the datafile. This will be high if you are doing a lot
?????????? of table scans. Generally this suggests that your tables are not properly indexed or that your
?????????? queries are not written to take advantage of the indexes you have. (4.5.7.3)
?????? TLW: Table_locks_waited
?????????? Number of times a table lock could not be acquired immediately and a wait was needed. If this is
?????????? high, and you have performance problems, you should first optimise your queries, and then either
?????????? split your table(s) or use replication. Available after 3.23.33.? (4.5.7.3)
?????? SFJ: Select_full_join
?????????? Number of joins without keys (If this is not 0, you should carefully check the indexes of your
?????????? tables). (4.5.7.3)
?????? SMP: Sort_merge_passes
?????????? Number of merges passes the sort algoritm have had to do. If this value is large you should con-
?????????? sider increasing sort_buffer. (4.5.7.3)
?????? QPS: Questions per second
?????????? The total number of sql commands handled by the MySQL server since startup or the last flush sta-
?????????? tus command.
?????? Statistics/Variables
?????? When viewing the stats screen (t), the screen will refresh until a key is pressed at which point you
?????? will return to the main screen.? The bottom of the stats screen is denoted with a line containing
?????? ---.? If you do not see that line, resize your screen until you do.
?????? The statistics screen has the following format:
?????????? Stat:????? total [avg per sec / instant per sec ]
?????? For example:
?????????? Questions:???? 720,672 [30/12]
?????? The short/important status screen is a list of recommendations from the MySQL manual.
?????? The first number is the total since startup or the last ’flush status’.? The second number is the
?????? number per second since startup or flush.? The last is the number per second since the last screen
?????? refresh.
?????? The variables screen only shows the information once and returns to the main screen as the variables
?????? do not change after server startup.
????? Replication
?????? The replication monitor screen looks for a master or slave server running on the currently monitored
?????? mysqld.? If a master server is found, it then tries to connect to each slave connected to the master.
?????? Replication is shown for all masters and slaves found.? Offsets from the master for each of the
?????? slaves is shown.? Note: the offset may be less than zero because the slave position is checked after
?????? the master position.? The offset shown is the number of queries in the binlog that the slave has to
?????? process before being caught up with the master.
OPTIONS
?????? 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 .mto-
?????? prc, home directory .mtoprc, /usr/local/etc/mtoprc, /etc/mtoprc.? Options in the former files over-
?????? ride 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.? Com-
?????? ments 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
SETUP
?????? The most convenient way to setup your system to use mtop is to create a database user called mysqltop
?????? which has no password.? For security purposes, this user should have all privileges set to N except
?????? Process_priv which must be set to Y.
?????? To grant these privileges, execute the following from the MySQL command prompt
?????? For mysql 4.0.2 and greater:
?????????? mysql> grant super, reload, process on *.* to mysqltop;
?????????? mysql> grant super, reload, process on *.* to
mysqltop@localhost;
?????????? mysql> flush privileges;
?????? For mysql 3.x and 4.0.1:
?????????? mysql> grant reload, process on *.* to mysqltop;
?????????? mysql> grant reload, process on *.* to
mysqltop@localhost;
?????????? mysql> flush privileges;
?????? Notes:
?????? ·?? GRANT only works in MySQL 3.22.11 or later, for earlier versions add the user manually and fix
?????????? the permissions as noted above.
?????? ·?? The GRANT to mysqltop and
mysqltop@localhost may be modified depending upon which hosts you want
?????????? to grant access from.? In general, you probably want to limit it to the hosts in your domain.
?????? ·?? The reload privilege is not required for mtop to do basic monitoring.? It is only needed if you
?????????? wish to issue flush commands from mtop.? The super privilege is needed if you wish to kill
?????????? queries in mysql 4.0.2 versions and above.? In 3.x, the process privilege allows a user to termi-
?????????? nate a query.
?????? Initially, mtop does not connect to a specific database.? Most commands this program issues are non-
?????? database specific (SHOW FULL PROCESSLIST, SHOW VARIABLES, KILL id).? However, when database-specific
?????? commands are needed, mtop will try to connect to the the required database and prompt for a user-
?????? name/password if the default one fails.
?????? To install mtop, run the following shell commands:
?????????? perl Makefile.PL
?????????? make
?????????? make install
?????? The default {install_prefix} is /usr/local which means that mtop is installed in /usr/local/bin/.? To
?????? change this, run:
?????????? perl Makefile.PL --prefix={install_prefix}
?????? or modify the PREFIX line in Makefile.PL.
?????? Requires the following perl modules:
?????? Copyright (C) 2002 Marc Prewitt/Chelsea Networks, under the GNU GPL.? mtop comes with ABSOLUTELY NO
?????? WARRANTY. This is free software, and you are welcome to redistribute it under certain conditions; see
?????? the COPYING file for details.
KNOWN BUGS
?????? The cache hit ratio may look very low if your server receives more than 4 billion key_read_requests.
?????? This is due to a roll-over condition.? If that number exceeds 4 billion and change, it rolls over to
?????? 1.? The cache hit ratio is calculated as: 100 - (key_reads/key_read_requests) * 100.
?????? If you are really trying to focus on the cache hit ratio, flush the status variables with the mtop f
?????? command to reset both the key_reads and key_read_requests.
?????? Win2K telnet.exe - If you are using the Windows 2000 telnet program, it defaults to ansi mode which
?????? doesn’t work well with curses (in my testing on Solaris 8).? To work around this, set the terminal
?????? type to vt100.? To do this, issue the following command from the telnet prompt before connecting to a
?????? host:
?????????? set term vt100
?????? Alternatively, you can manually set your TERM environment variable to vt100 after you are logged in.
TODO
?????? Offer sorts by other columns
?????? For the ’More:’ paging, it would be nice to support ’Less’ behaviour.
?????? Add ’n’ command and properly calculate number of lines on screen.
?????????? $Id: mtop.PL,v 1.61 2004/09/12 22:22:03 mdprewitt Exp $