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}}]
    [--user={user}] [--manualrefresh]

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, running 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)
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)
v - show mysqld variables (show variables/mysqladmin vars)
r - show replication status for master/slaves

Statistics/Variables

When viewing the stats screen, 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 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.

-s {refresh}, --seconds={refresh}

The default screen refresh is 5 seconds.

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

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

mysql> grant select on test.* to mysqltop;
mysql> grant select on test.* to mysqltop@localhost;
mysql> update user set process_priv='y' where user='mysqltop';
mysql> flush privileges;

Note: GRANT only works in MySQL 3.22.11 or later, for earlier versions add the user manually and fix the permissions as noted above. Note 2: 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.

In addition, the mysqltop user must have Select_priv to the test database. This requirement is only needed because the DBI driver requires a database to connect to even though no database commands are issued. Most commands this program issues are non-database specific (SHOW FULL PROCESSLIST, SHOW VARIABLES, KILL id). When database-specific commands are needed, mtop will prompt for a username/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 DBD::mysql, Curses, and Net::Domain.

AUTHOR

Marc Prewitt, Chelsea Networks <mprewitt@chelsea.net>

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.

BUGS

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

Feature to allow regex filters on other display columns. Show current filters.

Offer sorts by other columns

For the 'More:' paging, it would be nice to support 'Less' behaviour.

Add support for saving commandline opts in ~/.mtop

Add 'n' command and properly calculate number of lines on screen.

$Id: mtop.PL,v 1.49 2002/06/30 03:18:34 mdprewitt Exp $

cpanm mtop

perl -MCPAN -e shell
install mtop