.\" Automatically generated by Pod::Man 4.14 (Pod::Simple 3.43) .\" .\" Standard preamble: .\" ======================================================================== .de Sp \" Vertical space (when we can't use .PP) .if t .sp .5v .if n .sp .. .de Vb \" Begin verbatim text .ft CW .nf .ne \\$1 .. .de Ve \" End verbatim text .ft R .fi .. .\" Set up some character translations and predefined strings. \*(-- will .\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left .\" double quote, and \*(R" will give a right double quote. \*(C+ will .\" give a nicer C++. Capital omega is used to do unbreakable dashes and .\" therefore won't be available. \*(C` and \*(C' expand to `' in nroff, .\" nothing in troff, for use with C<>. .tr \(*W- .ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p' .ie n \{\ . ds -- \(*W- . ds PI pi . if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch . if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\" diablo 12 pitch . ds L" "" . ds R" "" . ds C` "" . ds C' "" 'br\} .el\{\ . ds -- \|\(em\| . ds PI \(*p . ds L" `` . ds R" '' . ds C` . ds C' 'br\} .\" .\" Escape single quotes in literal strings from groff's Unicode transform. .ie \n(.g .ds Aq \(aq .el .ds Aq ' .\" .\" If the F register is >0, we'll generate index entries on stderr for .\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index .\" entries marked with X<> in POD. Of course, you'll have to process the .\" output yourself in some meaningful fashion. .\" .\" Avoid warning from groff about undefined register 'F'. .de IX .. .nr rF 0 .if \n(.g .if rF .nr rF 1 .if (\n(rF:(\n(.g==0)) \{\ . if \nF \{\ . de IX . tm Index:\\$1\t\\n%\t"\\$2" .. . if !\nF==2 \{\ . nr % 0 . nr F 2 . \} . \} .\} .rr rF .\" ======================================================================== .\" .IX Title "MYTOP 1p" .TH MYTOP 1p "2022-11-12" "perl v5.36.0" "User Contributed Perl Documentation" .\" For nroff, turn off justification. Always turn off hyphenation; it makes .\" way too many mistakes in technical documents. .if n .ad l .nh .SH "NAME" mytop \- display MySQL server performance info like `top' .SH "SYNOPSIS" .IX Header "SYNOPSIS" \&\fBmytop\fR [options] .SH "AVAILABILITY" .IX Header "AVAILABILITY" The latest version of \fBmytop\fR is available from http://www.mysqlfanboy.com/mytop\-3/ it \fBmight\fR also be on \s-1CPAN\s0 as well. .SH "REQUIREMENTS" .IX Header "REQUIREMENTS" In order for \fBmytop\fR to function properly, you must have the following: .PP .Vb 5 \& * Perl 5.005 or newer \& * Config::IniFiles; \& * Getopt::Long \& * DBI and DBD::mysql \& * Term::ReadKey from CPAN .Ve .PP Most systems are likely to have all of those installed\*(--except for Term::ReadKey. You will need to pick that up from the \s-1CPAN.\s0 You can pick up Term::ReadKey here: .PP .Vb 1 \& http://search.cpan.org/search?dist=TermReadKey .Ve .PP And you obviously need access to a MySQL server (version 3.22.x or 3.23.x) with the necessary security to run the \fI\s-1SHOW PROCESSLIST\s0\fR and \&\fI\s-1SHOW STATUS\s0\fR commands. .PP If you are a Windows user, using ActiveState's Perl, you can use \s-1PPM\s0 (the Perl Package Manager) to install the MySQL and Term::ReadKey modules. .SS "Optional Color Support" .IX Subsection "Optional Color Support" In additon, if you want a color \fBmytop\fR (recommended), install Term::ANSIColor from the \s-1CPAN:\s0 .PP .Vb 1 \& http://search.cpan.org/search?dist=ANSIColor .Ve .PP Once you do, \fBmytop\fR will automatically use it. However, color is not yet working on Windows. Patches welcome. :\-) .SS "Optional Hi-Res Timing" .IX Subsection "Optional Hi-Res Timing" If you want \fBmytop\fR to provide more accurate real-time queries-per-second statistics, install the Time::HiRes module from \&\s-1CPAN.\s0 \fBmytop\fR will automatically notice that you have it and use it rather than the standard timing mechanism. .SS "Platforms" .IX Subsection "Platforms" \&\fBmytop\fR is known to work on: .PP .Vb 6 \& * 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) .Ve .PP 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 \fImight\fR even work on Win32 systems. .SH "DESCRIPTION" .IX Header "DESCRIPTION" Help is always welcome in improving this software. Feel free to contact the author (see \*(L"\s-1AUTHOR\*(R"\s0 below) with bug reports, fixes, suggestions, and comments. Additionally \*(L"\s-1BUGS\*(R"\s0 will provide a list of things this software is not able to do yet. .PP Having said that, here are the details on how it works and what you can do with it. .SS "The Basics" .IX Subsection "The Basics" \&\fBmytop\fR was inspired by the system monitoring tool \fBtop\fR. I routinely use \fBtop\fR on Linux, FreeBSD, and Solaris. You are likely to notice features from each of them here. .PP \&\fBmytop\fR will connect to a MySQL server and periodically run the \&\fI\s-1SHOW PROCESSLIST\s0\fR and \fI\s-1SHOW STATUS\s0\fR commands and attempt to summarize the information from them in a useful format. .SS "The Display" .IX Subsection "The Display" The \fBmytop\fR 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: .PP 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 .PP 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. .PP 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. .PP 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). .PP 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. .PP You can toggle the header by hitting \fBh\fR when running \fBmytop\fR. .PP 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: .PP .Vb 3 \& Id User Host Dbase Time Cmd Query or State \& \-\- \-\-\-\- \-\-\-\- \-\-\-\-\- \-\-\-\- \-\-\- \-\-\-\-\-\-\-\-\-\-\-\-\-\- \& 61 jzawodn localhost music 0 Query show processlist .Ve .PP 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. .PP Often times the query info is what you are really interested in, so it is good to run \fBmytop\fR in an xterm that is wider than the normal 80 columns if possible. .PP 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 \&\f(CW\*(C`Command\*(C'\fR column of the display: .PP .Vb 5 \& Query \- Yellow \& Sleep \- White \& Connect \- Green \& Slow \- Bright \& Long \- Magenta .Ve .PP Those are purely arbitrary and will be customizable in a future release. If they annoy you just start \fBmytop\fR with the \fB\-\-nocolor\fR flag or adjust your config file appropriately. .SS "Arguments" .IX Subsection "Arguments" \&\fBmytop\fR 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 '\-'. .IP "\fB\-u\fR or \fB\-\-user\fR \fIusername\fR" 4 .IX Item "-u or --user username" Username to use when logging in to the MySQL server. Default: ``\fBroot\fR''. .IP "\fB\-p\fR or \fB\-\-pass\fR or \fB\-\-password\fR \fIpassword\fR" 4 .IX Item "-p or --pass or --password password" Password to use when logging in to the MySQL server. Default: none. .Sp \&\s-1WARNING:\s0 This is insecure as the password is visible for anyone. See \fB\-\-prompt\fR instead! .IP "\fB\-h\fR or \fB\-\-host\fR \fIhostname\fR[\fB:\fR\fIport\fR]" 4 .IX Item "-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: ``\fBlocalhost\fR''. .IP "\fB\-\-port\fR or \fB\-P\fR \fIport\fR" 4 .IX Item "--port or -P port" If you're running MySQL on a non-standard port, use this to specify the port number. Default: \fB3306\fR. .IP "\fB\-s\fR or \fB\-\-delay\fR \fIseconds\fR" 4 .IX Item "-s or --delay seconds" How long between display refreshes. Default: \fB5\fR .IP "\fB\-d\fR or \fB\-\-db\fR or \fB\-\-database\fR \fIdatabase\fR" 4 .IX Item "-d or --db or --database database" Use if you'd like \fBmytop\fR to connect to a specific database by default. Default: none. .IP "\fB\-b\fR or \fB\-\-batch\fR or \fB\-\-batchmode\fR" 4 .IX Item "-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 \fBcron\fR) to capture the information into a file for later viewing. You might use batch mode in a \s-1CGI\s0 script to occasionally display your MySQL server status on the web. .Sp Default: unset. .IP "\fB\-S\fR or \fB\-\-socket\fR \fI/path/to/socket\fR" 4 .IX Item "-S or --socket /path/to/socket" If you're running \fBmytop\fR on the same host as MySQL, you may wish to have it use the MySQL socket directly rather than a standard \s-1TCP/IP\s0 connection. If you do,just specify one. .Sp Note that specifying a socket will make \fBmytop\fR 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 \fBmytop\fR will use the hostname and port number instead. .Sp Default: none. .IP "\fB\-\-header\fR or \fB\-\-noheader\fR" 4 .IX Item "--header or --noheader" Specify if you want the header to display or not. You can toggle this with the \fBh\fR key while \fBmytop\fR is running. .Sp Default: header. .IP "\fB\-\-color\fR or \fB\-\-nocolor\fR" 4 .IX Item "--color or --nocolor" Specify if you want a color display. This has no effect if you don't have color support available. .Sp Default: If you have color support, \fBmytop\fR will try color unless you tell it not to. .IP "\fB\-i\fR or \fB\-\-idle\fR or \fB\-\-noi\fR or \fB\-\-noidle\fR" 4 .IX Item "-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. .Sp Default: idle. .IP "\fB\-\-prompt\fR or \fB\-\-noprompt\fR" 4 .IX Item "--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 \f(CW\*(C`~/.mytop\*(C'\fR config file. You will \fBonly\fR be prompted if a password has not been specified in your config file or through another command line option. .Sp Default: noprompt. .IP "\fB\-\-resolve\fR" 4 .IX Item "--resolve" If you have skip-resolve set on MySQL (to keep it from doing a reverse \&\s-1DNS\s0 lookup on each inbound connection), mytop can replace \s-1IP\s0 addresses with hostnames but toggling this option. .Sp Default: noresolve .IP "\fB\-\-long\fR or \fB\-\-nolong\fR" 4 .IX Item "--long or --nolong" For large numbers print all digits (e.g. 10.000) instead of using a more compact approximation (e.g. 10.0k). .Sp Default: nolong. .IP "\fB\-m\fR or \fB\-\-mode\fR \fImode\fR" 4 .IX Item "-m or --mode mode" Specify initial mode \fBqps\fR(queries/second), \fBtop\fR(overview), \&\fBcmd\fR(command summary), \fBinnodb\fR(InnoDB status) or \fBstatus\fR(). .Sp Default: \fBtop\fR .IP "\fB\-\-sort\fR or \fB\-\-nosort\fR" 4 .IX Item "--sort or --nosort" Reverse sort order from ascending to descending using Idle time. .Sp Default: nosort. .PP Command-line arguments will always take precedence over config file options. That happens because the config file is read \fI\s-1BEFORE\s0\fR the command-line arguments are applied. .SS "Config File" .IX Subsection "Config File" Instead of always using bulky command-line parameters, you can also use a config file in your home directory (\f(CW\*(C`~/.mytop\*(C'\fR). If present, \&\fBmytop\fR will read it automatically. It is read \fIbefore\fR any of your command-line arguments are processed, so your command-line arguments will override directives in the config file. .PP Here is a sample config file \f(CW\*(C`~/.mytop\*(C'\fR which implements the defaults described above. .PP .Vb 10 \& user=root \& pass= \& host=localhost \& db=test \& delay=5 \& port=3306 \& slow=10 \& socket= \& batchmode=0 \& header=1 \& color=1 \& idle=1 \& long=120 .Ve .PP 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 \f(CW\*(C`~/.mytop\*(C'\fR are such that others cannot read it (unless you want them to, of course). .PP You may have white space on either side of the \f(CW\*(C`=\*(C'\fR in lines of the config file. .PP If present, mytop will also read mysql's default config file \f(CW\*(C`~/.my.cnf\*(C'\fR. Values from both the [client] and [mytop] section are read, in this order. The long options \fIdatabase\fR and \fIpassword\fR are preferred over the short versions \fIdb\fR and \fIpass\fR to match mysql's syntax. These settings take lowest precedence and will be overwritten by either values in \f(CW\*(C`~/.mytop\*(C'\fR or command-line options. .SS "Shortcut Keys" .IX Subsection "Shortcut Keys" The following keys perform various actions while \fBmytop\fR is running. Those which have not been implemented are listed as such. They are included to give the user idea of what is coming. .IP "\fB?\fR" 4 .IX Item "?" Display help. .IP "\fBc\fR" 4 .IX Item "c" Show \*(L"command counters\*(R" based on the Com_* values in \s-1SHOW STATUS.\s0 This is a new feature. Feedback welcome. .IP "\fBC\fR" 4 .IX Item "C" Turn display color on and off. Default is on. .IP "\fBd\fR" 4 .IX Item "d" Show only threads connected to a particular database. .IP "\fBf\fR" 4 .IX Item "f" Given a thread id, display the entire query that thread was (and still may be) running. .IP "\fBF\fR" 4 .IX Item "F" Disable all filtering (host, user, and db). .IP "\fBh\fR" 4 .IX Item "h" Only show queries from a particular host. .IP "\fBH\fR" 4 .IX Item "H" Toggle the header display. You can also specify either \f(CW\*(C`header=0\*(C'\fR or \&\f(CW\*(C`header=1\*(C'\fR in your config file to set the default behavior. .IP "\fBi\fR" 4 .IX Item "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. .IP "\fBI\fR" 4 .IX Item "I" Switch to InnoDB Status mode. The output of \*(L"\s-1SHOW ENGINE INNODB STATUS\*(R"\s0 will be displayed every cycle. In a future version, this may actually summarize that data rather than producing raw output. .IP "\fBk\fR" 4 .IX Item "k" Kill a thread. .IP "\fBm\fR" 4 .IX Item "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. .Sp More modes may be added in the future. .IP "\fBo\fR" 4 .IX Item "o" Reverse the default sort order. .IP "\fBp\fR" 4 .IX Item "p" Pause display. .IP "\fBq\fR" 4 .IX Item "q" Quit \fBmytop\fR .IP "\fBr\fR" 4 .IX Item "r" Reset the server's status counters via a \fI\s-1FLUSH STATUS\s0\fR command. .IP "\fBR\fR" 4 .IX Item "R" Togle \s-1IP\s0 reverse lookup. Default is on. .IP "\fBs\fR" 4 .IX Item "s" Change the sleep time (number of seconds between display refreshes). .IP "\fBS\fR" 4 .IX Item "S" Set the number of seconds a query will need to run before it is considered old and will be highlighted. .IP "\fBu\fR" 4 .IX Item "u" Show only threads owned by a giver user. .PP The \fBs\fR key has a command-line counterpart: \fB\-s\fR. .PP The \fBh\fR key has two command-line counterparts: \fB\-\-header\fR and \&\fB\-\-noheader\fR. .SH "BUGS" .IX Header "BUGS" This is more of a \s-1BUGS +\s0 WishList. .PP 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 \fI\s-1SHOW STATUS\s0\fR in MySQL 3.23.x and \&\fBmytop\fR makes use of it. If the information is not available, you will simply see zeros where the real numbers should be. .PP 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. .PP \&\fBmytop\fR consumes too much \s-1CPU\s0 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 \fBmytop\fR now automatically lowers its priority when you run it. You may also think about running \fBmytop\fR on another workstation instead of your database server. However, \f(CW\*(C`mytop\*(C'\fR on Solaris does \fBnot\fR have this problem. Newer versions of Linux and FreeBSD seem to have fixed this. .PP You can't specify the maximum number of threads to list. If you have many threads and a tall xterm, \fBmytop\fR will always try to display as many as it can fit. .PP 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. .PP 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. .SH "AUTHOR" .IX Header "AUTHOR" mytop was developed and is maintained by Jeremy D. Zawodny (Jeremy@Zawodny.com). .PP If you wish to e\-mail me regarding this software, \fB\s-1PLEASE\s0\fR subscribe to the \fBmytop\fR mailing list. See the \fBmytop\fR homepage for details. .SH "DISCLAIMER" .IX Header "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. .SH "RECRUITING" .IX Header "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. :\-( .SH "SEE ALSO" .IX Header "SEE ALSO" Please check the MySQL manual if you're not sure where some of the output of \fBmytop\fR is coming from. .SH "COPYRIGHT" .IX Header "COPYRIGHT" Copyright (C) 2000\-2010, Jeremy D. Zawodny. .SH "CREDITS" .IX Header "CREDITS" Fix a bug. Add a feature. See your name here! .PP Many thanks go to these fine folks: .IP "Mark Grennan (mark@grennan.com) www.linuxfangoy.com" 4 .IX Item "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. .IP "Sami Ahlroos (sami@avis\-net.de)" 4 .IX Item "Sami Ahlroos (sami@avis-net.de)" Suggested the idle/noidle stuff. .IP "Jan Willamowius (jan@janhh.shnet.org)" 4 .IX Item "Jan Willamowius (jan@janhh.shnet.org)" Mirnor bug report. Documentation fixes. .IP "Alex Osipov (alex@acky.net)" 4 .IX Item "Alex Osipov (alex@acky.net)" Long command-line options, Unix socket support. .IP "Stephane Enten (tuf@grolier.fr)" 4 .IX Item "Stephane Enten (tuf@grolier.fr)" Suggested batch mode. .IP "Richard Ellerbrock (richarde@eskom.co.za)" 4 .IX Item "Richard Ellerbrock (richarde@eskom.co.za)" Bug reports and usability suggestions. .IP "William R. Mattil (wrm@newton.irngtx.tel.gte.com)" 4 .IX Item "William R. Mattil (wrm@newton.irngtx.tel.gte.com)" Bug report about empty passwords not working. .IP "Benjamin Pflugmann (philemon@spin.de)" 4 .IX Item "Benjamin Pflugmann (philemon@spin.de)" Suggested \-P command-line flag as well as other changes. .IP "Justin Mecham " 4 .IX Item "Justin Mecham " Suggested setting \f(CW$0\fR to `mytop'. .IP "Thorsten Kunz " 4 .IX Item "Thorsten Kunz " Provided a fix for cases when we try remove the domain name from the display even if it is actually an \s-1IP\s0 address. .IP "Sasha Pachev " 4 .IX Item "Sasha Pachev " Provided the idea of real-time queries per second in the main display. .IP "Paul DuBois " 4 .IX Item "Paul DuBois " Pointed out some option-handling bugs. .IP "Mike Wexler " 4 .IX Item "Mike Wexler " Suggested that we don't mangle (normalize) whitespace in query info by default. .IP "Mark Zweifel " 4 .IX Item "Mark Zweifel " Make the \-\-idle command-line argument negatable. .IP "Axel Schwenke " 4 .IX Item "Axel Schwenke " Noticed the inccorect formula for query cache hit percentages in version 1.2. .IP "Steven Roussey " 4 .IX Item "Steven Roussey " Supplied a patch to help filter binary junk in queries so that terminals don't freak out. .IP "jon r. luini " 4 .IX Item "jon r. luini " Supplied a patch that formed the basis for \f(CW\*(C`\-\-prompt\*(C'\fR support. Sean Leach submitted a similar patch. .IP "Yogish Baliga " 4 .IX Item "Yogish Baliga " Supplied a patch that formed the basis for \f(CW\*(C`\-\-resolve\*(C'\fR support. .IP "Per Andreas Buer " 4 .IX Item "Per Andreas Buer " Supplied an excellent patch to tidy up the top display. This includes showing most values in short form, such as 10k rather than 10000. .PP See the Changes file on the \fBmytop\fR distribution page for more details on what has changed. .SH "LICENSE" .IX Header "LICENSE" \&\fBmytop\fR is licensed under the \s-1GNU\s0 General Public License version 2. For the full license information, please visit http://www.gnu.org/copyleft/gpl.html