mytop

Content

 

Data

license:
Version:
Developer / owner:

Short description:

Manual page and help for the mytop linux command. Mytop is a tool for displaying information about MySQL server performance, such as "top".

 

 

Man page output

man mytop
MYTOP(1p)             User Contributed Perl Documentation            MYTOP(1p)

NAME
       mytop - display MySQL server performance info like `top'

SYNOPSIS
       mytop [options]

AVAILABILITY
       The latest version of mytop is available from
       http://www.mysqlfanboy.com/mytop-3/ it might also be on CPAN as well.

REQUIREMENTS
       In order for mytop to function properly, you must have the following:

         * Perl 5.005 or newer
         * Config::IniFiles;
         * Getopt::Long
         * DBI and DBD::mysql
         * Term::ReadKey from CPAN

       Most systems are likely to have all of those installed--except for
       Term::ReadKey. You will need to pick that up from the CPAN. You can
       pick up Term::ReadKey here:

           http://search.cpan.org/search?dist=TermReadKey

       And you obviously need access to a MySQL server (version 3.22.x or
       3.23.x) with the necessary security to run the SHOW PROCESSLIST and
       SHOW STATUS commands.

       If you are a Windows user, using ActiveState's Perl, you can use PPM
       (the Perl Package Manager) to install the MySQL and Term::ReadKey
       modules.

   Optional Color Support
       In additon, if you want a color mytop (recommended), install
       Term::ANSIColor from the CPAN:

           http://search.cpan.org/search?dist=ANSIColor

       Once you do, mytop will automatically use it. However, color is not yet
       working on Windows. Patches welcome. :-)

   Optional Hi-Res Timing
       If you want mytop to provide more accurate real-time queries-per-second
       statistics, install the Time::HiRes module from CPAN.  mytop will
       automatically notice that you have it and use it rather than the
       standard timing mechanism.

   Platforms
       mytop is known to work on:

         * Linux (2.2.x, 2.4.x)
         * FreeBSD (2.2, 3.x, 4.x)
         * Mac OS X
         * BSDI 4.x
         * Solaris 2.x
         * Windows NT 4.x (ActivePerl)

       If you find that it works on another platform, please let me know.
       Given that it is all Perl code, I expect it to be rather portable to
       Unix and Unix-like systems. Heck, it might even work on Win32 systems.

DESCRIPTION
       Help is always welcome in improving this software. Feel free to contact
       the author (see "AUTHOR" below) with bug reports, fixes, suggestions,
       and comments. Additionally "BUGS" will provide a list of things this
       software is not able to do yet.

       Having said that, here are the details on how it works and what you can
       do with it.

   The Basics
       mytop was inspired by the system monitoring tool top. I routinely use
       top on Linux, FreeBSD, and Solaris. You are likely to notice features
       from each of them here.

       mytop will connect to a MySQL server and periodically run the SHOW
       PROCESSLIST and SHOW STATUS commands and attempt to summarize the
       information from them in a useful format.

   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 (4.0.13-log)                        up 1+11:13:00
       [23:29:11]
        Queries: 19.3M  qps:  160 Slow:     1.0         Se/In/Up/De(%):
       00/80/03/17
                    qps now:  219 Slow qps: 0.0  Threads:    1 (   1/  16)
       00/74/00/25
        Key Efficiency: 99.3%  Bps in/out: 30.5k/162.8   Now in/out: 32.7k/
       3.3k

       The first line identifies 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 number of slow
       queries, and the percentage of Select, Insert, Update, and Delete
       queries.

       The third real-time values. First is the number of queries per second,
       then the number of slow queries, followed by query precentages (like on
       the previous line).

       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, both over all and in the last cycle.

       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
           Slow    -  Bright
           Long    -  Magenta

       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 have two
       dashes `--'. Short arguments only have 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.

           WARNING: This is insecure as the password is visible for anyone.
           See --prompt instead!

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

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

       --prompt or --noprompt
           Specify if you want to be prompted to type in your database
           password.  This provides a little bit more security since it not
           only prevents the password from viewable in a process list, but
           also doesn't require the password to be stored in plain text in
           your "~/.mytop" config file.  You will only be prompted if a
           password has not been specified in your config file or through
           another command line option.

           Default: noprompt.

       --resolve
           If you have skip-resolve set on MySQL (to keep it from doing a
           reverse DNS lookup on each inbound connection), mytop can replace
           IP addresses with hostnames but toggling this option.

           Default: noresolve

       --long or --nolong
           For large numbers print all digits (e.g. 10.000) instead of using a
           more compact approximation (e.g. 10.0k).

           Default: nolong.

       -m or --mode mode
           Specify initial mode qps(queries/second), top(overview),
           cmd(command summary), innodb(InnoDB status) or status().

           Default: top

       --sort or --nosort
           Reverse sort order from ascending to descending using Idle time.

           Default: nosort.

       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
         slow=10
         socket=
         batchmode=0
         header=1
         color=1
         idle=1
         long=120

       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.

       If present, mytop will also read mysql's default config file
       "~/.my.cnf".  Values from both the [client] and [mytop] section are
       read, in this order.  The long options database and password are
       preferred over the short versions db and pass to match mysql's syntax.
       These settings take lowest precedence and will be overwritten by either
       values in "~/.mytop" or command-line options.

   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.

       C   Turn display color on and off. Default is on.

       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.

       I   Switch to InnoDB Status mode. The output of "SHOW ENGINE INNODB
           STATUS" will be displayed every cycle. In a future version, this
           may actually summarize that data rather than producing raw output.

       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.

       R   Togle IP reverse lookup. Default is on.

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

       S   Set the number of seconds a query will need to run before it is
           considered old and will be highlighted.

       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.

BUGS
       This is more of a BUGS + WishList.

       Some performance information is not available when talking to a version
       3.22.x MySQL server. Additional information (about threads mostly) was
       added to the output of SHOW STATUS in MySQL 3.23.x and mytop makes use
       of it. If the information is not available, you will simply see zeros
       where the real numbers should be.

       Simply running this program will increase your overall counters (such
       as the number of queries run). But you may or may not view that as a
       bug.

       mytop consumes too much CPU time when running (verified on older
       versions of Linux and FreeBSD). It's likely a problem related to
       Term::ReadKey. I haven't had time to investigate yet, so mytop now
       automatically lowers its priority when you run it. You may also think
       about running mytop on another workstation instead of your database
       server. However, "mytop" on Solaris does not have this problem.  Newer
       versions of Linux and FreeBSD seem to have fixed this.

       You can't specify the maximum number of threads to list. If you have
       many threads and a tall xterm, mytop will always try to display as many
       as it can fit.

       The size of most of the columns in the display has a small maximum
       width. If you have fairly long database/user/host names the display may
       appear odd. I have no good idea as to how best to deal with that yet.
       Suggestions are welcome.

       You should be able to specify the columns you'd like to see in the
       display and the order in which they appear. If you only have one
       username that connects to your database, it's probably not worth having
       the User column appear, for example.

AUTHOR
       mytop was developed and is maintained by Jeremy D. Zawodny
       (Jeremy@Zawodny.com).

       If you wish to e-mail me regarding this software, PLEASE subscribe to
       the mytop mailing list.  See the mytop homepage for details.

DISCLAIMER
       While I use this software in my job at Yahoo!, I am solely responsible
       for it. Yahoo! does not necessarily support this software in any way.
       It is merely a personal idea which happened to be very useful in my
       job.

RECRUITING
       If you hack Perl and grok MySQL, come work at Yahoo! Contact me for
       details. Or just send me your resume. Er, unless we just had layoffs,
       in which case we're not hiring. :-(

SEE ALSO
       Please check the MySQL manual if you're not sure where some of the
       output of mytop is coming from.

COPYRIGHT
       Copyright (C) 2000-2010, Jeremy D. Zawodny.

CREDITS
       Fix a bug. Add a feature. See your name here!

       Many thanks go to these fine folks:

       Mark Grennan (mark@grennan.com) www.linuxfangoy.com
           Added updates for MySQL 5.x. Added 'S' (slow) highlighting.  Added
           'C' to turn on and off Color. Added 'l' command to change color for
           long running queries. Fixed a few documentation issues.  Monitors
           Slave status. Added color to Queue hit ratio.  Added number of rows
           sorted per second.  Created release 1.7.

       Sami Ahlroos (sami@avis-net.de)
           Suggested the idle/noidle stuff.

       Jan Willamowius (jan@janhh.shnet.org)
           Mirnor bug report. Documentation fixes.

       Alex Osipov (alex@acky.net)
           Long command-line options, Unix socket support.

       Stephane Enten (tuf@grolier.fr)
           Suggested batch mode.

       Richard Ellerbrock (richarde@eskom.co.za)
           Bug reports and usability suggestions.

       William R. Mattil (wrm@newton.irngtx.tel.gte.com)
           Bug report about empty passwords not working.

       Benjamin Pflugmann (philemon@spin.de)
           Suggested -P command-line flag as well as other changes.

       Justin Mecham <justin@aspect.net>
           Suggested setting $0 to `mytop'.

       Thorsten Kunz <thorsten.kunz@de.tiscali.com>
           Provided a fix for cases when we try remove the domain name from
           the display even if it is actually an IP address.

       Sasha Pachev <sasha@mysql.com>
           Provided the idea of real-time queries per second in the main
           display.

       Paul DuBois <paul@snake.net>
           Pointed out some option-handling bugs.

       Mike Wexler <mwexler@tias.com>
           Suggested that we don't mangle (normalize) whitespace in query info
           by default.

       Mark Zweifel <markez@yahoo-inc.com>
           Make the --idle command-line argument negatable.

       Axel Schwenke <schwenke@jobpilot.de>
           Noticed the inccorect formula for query cache hit percentages in
           version 1.2.

       Steven Roussey <sroussey@network54.com>
           Supplied a patch to help filter binary junk in queries so that
           terminals don't freak out.

       jon r. luini <falcon@chime.com>
           Supplied a patch that formed the basis for "--prompt" support.
           Sean Leach <sleach@wiggum.com> submitted a similar patch.

       Yogish Baliga <baliga@yahoo-inc.com>
           Supplied a patch that formed the basis for "--resolve" support.

       Per Andreas Buer <perbu@linpro.no>
           Supplied an excellent patch to tidy up the top display.  This
           includes showing most values in short form, such as 10k rather than
           10000.

       See the Changes file on the mytop distribution page for more details on
       what has changed.

LICENSE
       mytop is licensed under the GNU General Public License version 2. For
       the full license information, please visit
       http://www.gnu.org/copyleft/gpl.html

perl v5.24.1                      2017-01-09                         MYTOP(1p)

 

 

Related Content