Installing mytop
From brokenpoet.org wiki
Contents |
Description
mytop is basically a 'top' clone for MySQL
It is, more specifically, a console-based tool for monitoring the threads and overall performance of MySQL 3.22.x, 3.23.x, and 4.x servers. It runs on most Unix systems which have Perl, DBI, and Term::ReadKey installed. And with Term::ANSIColor installed you even get color.
Installation
There are some requirements to get this to function:
Meeting Requirements
- Perl 5.005 or newer
perl -v
- Getopt::Long
cpan Getopt::Long
- DBI and DBD::mysql
cpan DBI cpan DBD::mysql
- Term::ReadKey from CPAN
cpan Term::ReadKey
- Term::ANSIColor
cpan Term::ANSIColor
Getting, Building and Installing Program
wget http://jeremy.zawodny.com/mysql/mytop/mytop-1.6.tar.gz
tar -zxvf mytop-<version>.tar.gz cd mytop-<version> perl Makefile.PL make make test make install
Usage
The Display
The mytop display screen is really broken into two parts. The top 4 lines (header) contain summary information about your MySQL server. For example, you might see something like:
MySQL on localhost (3.22.32) up 3+23:14:20 [23:54:52] Queries Total: 617 Avg/Sec: 0.00 Now/Sec: 0.05 Slow: 0 Threads Total: 1 Active: 1 Cached: 0 Key Efficiency: 88.38% Bytes in: 0 Bytes out: 0
The first line identified the hostname of the server (localhost) and the version of MySQL it is running. The right had side shows the uptime of the MySQL server process in days+hours:minutes:seconds format (much like FreeBSD's top) as well as the current time.
The second line displays the total number of queries the server has processed, the average number of queries per second, the real-time number of queries per second, and the number of slow queries.
The third line deals with threads. Versions of MySQL before 3.23.x didn't give out this information, so you'll see all zeros.
And the fourth line displays key buffer efficiency (how often keys are read from the buffer rather than disk) and the number of bytes that MySQL has sent and received.
You can toggle the header by hitting h when running mytop.
The second part of the display lists as many threads as can fit on screen. By default they are sorted according to their idle time (least idle first). The display looks like:
Id User Host Dbase Time Cmd Query or State -- ---- ---- ----- ---- --- -------------- 61 jzawodn localhost music 0 Query show processlist
As you can see, the thread id, username, host from which the user is connecting, database to which the user is connected, number of seconds of idle time, the command the thread is executing, and the query info are all displayed.
Often times the query info is what you are really interested in, so it is good to run mytop in an xterm that is wider than the normal 80 columns if possible.
The thread display color-codes the threads if you have installed color support. The current color scheme only works well in a window with a dark (like black) background. The colors are selected according to the Command column of the display:
Query - Yellow Sleep - White Connect - Green
Those are purely arbitrary and will be customizable in a future release. If they annoy you just start mytop with the -nocolor flag or adjust your config file appropriately.
Arguments
mytop handles long and short command-line arguments. Not all options have both long and short formats, however. The long arguments can start with one or two dashes `-' or `--'. They are shown here with just one.
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
-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 Specify 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.
Command-line arguments will always take precedence over config file options. That happens because the config file is read BEFORE the command-line arguments are applied.
Config File
Instead of always using bulky command-line parameters, you can also use a config file in your home directory (~/.mytop). If present, mytop will read it automatically. It is read before any of your command-line arguments are processed, so your command-line arguments will override directives in the config file.
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
Using a config file will help to ensure that your database password isn't visible to users on the command-line. Just make sure that the permissions on ~/.mytop are such that others cannot read it (unless you want them to, of course).
You may have white space on either side of the = in lines of the config file.
Shortcut Keys
The following keys perform various actions while mytop is running. Those which have not been implemented are listed as such. They are included to give the user idea of what is coming.
? : 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.
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.
