1MYTOP(1) User Contributed Perl Documentation MYTOP(1)
2
6 mytop - display MySQL server performance info like `top'
7
9 mytop [options]
10
12 The latest version of mytop is available from
13 http://jeremy.zawodny.com/mysql/mytop/ it might also be on CPAN as
14 well.
15
17 In order for mytop to function properly, you must have the following:
18 * Perl 5.005 or newer
20 * Getopt::Long
21 * DBI and DBD::mysql
22 * Term::ReadKey from CPAN
23 Most systems are likely to have all of those installed--except for
25 Term::ReadKey. You will need to pick that up from the CPAN. You can
26 pick up Term::ReadKey here:
27 http://search.cpan.org/search?dist=TermReadKey
29 And you obviously need access to a MySQL server (version 3.22.x or
31 3.23.x) with the necessary security to run the SHOW PROCESSLIST and
32 SHOW GLOBAL STATUS commands.
33 If you are a Windows user, using ActiveState's Perl, you can use PPM
35 (the Perl Package Manager) to install the MySQL and Term::ReadKey
36 modules.
37 Optional Color Support
39 In additon, if you want a color mytop (recommended), install
40 Term::ANSIColor from the CPAN:
41 http://search.cpan.org/search?dist=ANSIColor
43 Once you do, mytop will automatically use it. However, color is not yet
45 working on Windows. Patches welcome. :-)
46 Optional Hi-Res Timing
48 If you want mytop to provide more accurate real-time queries-per-second
49 statistics, install the Time::HiRes module from CPAN. mytop will
50 automatically notice that you have it and use it rather than the
51 standard timing mechanism.
52 Platforms
54 mytop is known to work on:
55 * Linux (2.2.x, 2.4.x)
57 * FreeBSD (2.2, 3.x, 4.x)
58 * Mac OS X
59 * BSDI 4.x
60 * Solaris 2.x
61 * Windows NT 4.x (ActivePerl)
62 If you find that it works on another platform, please let me know.
64 Given that it is all Perl code, I expect it to be rather portable to
65 Unix and Unix-like systems. Heck, it might even work on Win32 systems.
66
68 Help is always welcome in improving this software. Feel free to contact
69 the author (see "AUTHOR" below) with bug reports, fixes, suggestions,
70 and comments. Additionally "BUGS" will provide a list of things this
71 software is not able to do yet.
72 Having said that, here are the details on how it works and what you can
74 do with it.
75 The Basics
77 mytop was inspired by the system monitoring tool top. I routinely use
78 top on Linux, FreeBSD, and Solaris. You are likely to notice features
79 from each of them here.
80 mytop will connect to a MySQL server and periodically run the SHOW
82 PROCESSLIST and SHOW GLOBAL STATUS commands and attempt to summarize
83 the information from them in a useful format.
84 The Display
86 The mytop display screen is really broken into two parts. The top 4
87 lines (header) contain summary information about your MySQL server. For
88 example, you might see something like:
89 MySQL on localhost (4.0.13-log) up 1+11:13:00
91 [23:29:11]
92 Queries: 19.3M qps: 160 Slow: 1.0 Se/In/Up/De(%):
93 00/80/03/17
94 qps now: 219 Slow qps: 0.0 Threads: 1 ( 1/ 16)
95 00/74/00/25
96 Key Efficiency: 99.3% Bps in/out: 30.5k/162.8 Now in/out: 32.7k/
97 3.3k
98 The first line identifies the hostname of the server (localhost) and
100 the version of MySQL it is running. The right had side shows the uptime
101 of the MySQL server process in days+hours:minutes:seconds format (much
102 like FreeBSD's top) as well as the current time.
103 The second line displays the total number of queries the server has
105 processed, the average number of queries per second, the number of slow
106 queries, and the percentage of Select, Insert, Update, and Delete
107 queries.
108 The third real-time values. First is the number of queries per second,
110 then the number of slow queries, followed by query precentages (like on
111 the previous line).
112 And the fourth line displays key buffer efficiency (how often keys are
114 read from the buffer rather than disk) and the number of bytes that
115 MySQL has sent and received, both over all and in the last cycle.
116 You can toggle the header by hitting h when running mytop.
118 The second part of the display lists as many threads as can fit on
120 screen. By default they are sorted according to their idle time (least
121 idle first). The display looks like:
122 Id User Host Dbase Time Cmd Query or State
124 -- ---- ---- ----- ---- --- --------------
125 61 jzawodn localhost music 0 Query show processlist
126 As you can see, the thread id, username, host from which the user is
128 connecting, database to which the user is connected, number of seconds
129 of idle time, the command the thread is executing, and the query info
130 are all displayed.
131 Often times the query info is what you are really interested in, so it
133 is good to run mytop in an xterm that is wider than the normal 80
134 columns if possible.
135 The thread display color-codes the threads if you have installed color
137 support. The current color scheme only works well in a window with a
138 dark (like black) background. The colors are selected according to the
139 "Command" column of the display:
140 Query - Yellow
142 Sleep - White
143 Connect - Green
144 Those are purely arbitrary and will be customizable in a future
146 release. If they annoy you just start mytop with the -nocolor flag or
147 adjust your config file appropriately.
148 Arguments
150 mytop handles long and short command-line arguments. Not all options
151 have both long and short formats, however. The long arguments can start
152 with one or two dashes `-' or `--'. They are shown here with just one.
153 -u or -user username
155 Username to use when logging in to the MySQL server. Default:
156 ``root''.
157 -p or -pass or -password password
159 Password to use when logging in to the MySQL server. Default: none.
160 -h or -host hostname[:port]
162 Hostname of the MySQL server. The hostname may be followed by an
163 option port number. Note that the port is specified separate from
164 the host when using a config file. Default: ``localhost''.
165 -port or -P port
167 If you're running MySQL on a non-standard port, use this to specify
168 the port number. Default: 3306.
169 -s or -delay seconds
171 How long between display refreshes. Default: 5
172 -d or -db or -database database
174 Use if you'd like mytop to connect to a specific database by
175 default. Default: ``test''.
176 -b or -batch or -batchmode
178 In batch mode, mytop runs only once, does not clear the screen, and
179 places no limit on the number of lines it will print. This is
180 suitable for running periodically (perhaps from cron) to capture
181 the information into a file for later viewing. You might use batch
182 mode in a CGI script to occasionally display your MySQL server
183 status on the web.
184 Default: unset.
186 -S or -socket /path/to/socket
188 If you're running mytop on the same host as MySQL, you may wish to
189 have it use the MySQL socket directly rather than a standard TCP/IP
190 connection. If you do,just specify one.
191 Note that specifying a socket will make mytop ignore any host
193 and/or port that you might have specified. If the socket does not
194 exist (or the file specified is not a socket), this option will be
195 ignored and mytop will use the hostname and port number instead.
196 Default: none.
198 -header or -noheader
200 Sepcify if you want the header to display or not. You can toggle
201 this with the h key while mytop is running.
202 Default: header.
204 -color or -nocolor
206 Specify if you want a color display. This has no effect if you
207 don't have color support available.
208 Default: If you have color support, mytop will try color unless you
210 tell it not to.
211 -i or -idle or -noidle
213 Specify if you want idle (sleeping) threads to appear in the list.
214 If sleeping threads are omitted, the default sorting order is
215 reversed so that the longest running queries appear at the top of
216 the list.
217 Default: idle.
219 -prompt or -noprompt
221 Specify if you want to be prompted to type in your database
222 password. This provides a little bit more security since it not
223 only prevents the password from viewable in a process list, but
224 also doesn't require the password to be stored in plain text in
225 your ~/.mytop config file. You will only be prompted if a password
226 has not been specified in your config file or through another
227 command line option.
228 Default: noprompt.
230 -resolve
232 If you have skip-resolve set on MySQL (to keep it from doing a
233 reverse DNS lookup on each inbound connection), mytop can replace
234 IP addresses with hostnames but toggling this option.
235 Default: noresolve
237 Command-line arguments will always take precedence over config file
239 options. That happens because the config file is read BEFORE the
240 command-line arguments are applied.
241 Config File
243 Instead of always using bulky command-line parameters, you can also use
244 a config file in your home directory ("~/.mytop"). If present, mytop
245 will read it automatically. It is read before any of your command-line
246 arguments are processed, so your command-line arguments will override
247 directives in the config file.
248 Here is a sample config file "~/.mytop" which implements the defaults
250 described above.
251 user=root
253 pass=
254 host=localhost
255 db=test
256 delay=5
257 port=3306
258 socket=
259 batchmode=0
260 header=1
261 color=1
262 idle=1
263 Using a config file will help to ensure that your database password
265 isn't visible to users on the command-line. Just make sure that the
266 permissions on "~/.mytop" are such that others cannot read it (unless
267 you want them to, of course).
268 You may have white space on either side of the "=" in lines of the
270 config file.
271 Shortcut Keys
273 The following keys perform various actions while mytop is running.
274 Those which have not been implemented are listed as such. They are
275 included to give the user idea of what is coming.
276 ? Display help.
278 c Show "command counters" based on the Com_* values in SHOW GLOBAL
280 STATUS. This is a new feature. Feedback welcome.
281 d Show only threads connected to a particular database.
283 f Given a thread id, display the entire query that thread was (and
285 still may be) running.
286 F Disable all filtering (host, user, and db).
288 h Only show queries from a particular host.
290 H Toggle the header display. You can also specify either "header=0"
292 or "header=1" in your config file to set the default behavior.
293 i Toggle the display of idle (sleeping) threads. If sleeping threads
295 are filtered, the default sorting order is reversed so that the
296 longest running queries appear at the top of the list.
297 I Switch to InnoDB Status mode. The output of "SHOW INNODB STATUS"
299 will be displayed every cycle. In a future version, this may
300 actually summarize that data rather than producing raw output.
301 k Kill a thread.
303 m Toggle modes. Currently this switches from `top' mode to `qps'
305 (Queries Per Second Mode). In this mode, mytop will write out one
306 integer per second. The number written reflects the number of
307 queries executed by the server in the previous one second interval.
308 More modes may be added in the future.
310 o Reverse the default sort order.
312 p Pause display.
314 q Quit mytop
316 r Reset the server's status counters via a FLUSH STATUS command.
318 s Change the sleep time (number of seconds between display
320 refreshes).
321 u Show only threads owned by a giver user.
323 The s key has a command-line counterpart: -s.
325 The h key has two command-line counterparts: -header and -noheader.
327
329 This is more of a BUGS + WishList.
330 Some performance information is not available when talking to a version
332 3.22.x MySQL server. Additional information (about threads mostly) was
333 added to the output of SHOW STATUS in MySQL 3.23.x and mytop makes use
334 of it. If the information is not available, you will simply see zeros
335 where the real numbers should be.
336 Simply running this program will increase your overall counters (such
338 as the number of queries run). But you may or may not view that as a
339 bug.
340 mytop consumes too much CPU time when running (verified on older
342 versions of Linux and FreeBSD). It's likely a problem related to
343 Term::ReadKey. I haven't had time to investigate yet, so mytop now
344 automatically lowers its priority when you run it. You may also think
345 about running mytop on another workstation instead of your database
346 server. However, "mytop" on Solaris does not have this problem. Newer
347 versions of Linux and FreeBSD seem to have fixed this.
348 You can't specify the maximum number of threads to list. If you have
350 many threads and a tall xterm, mytop will always try to display as many
351 as it can fit.
352 The size of most of the columns in the display has a small maximum
354 width. If you have fairly long database/user/host names the display may
355 appear odd. I have no good idea as to how best to deal with that yet.
356 Suggestions are welcome.
357 It'd be nice if you could just add mytop configuration directives in
359 your "my.cnf" file instead of having a separate config file.
360 You should be able to specify the columns you'd like to see in the
362 display and the order in which they appear. If you only have one
363 username that connects to your database, it's probably not worth having
364 the User column appear, for example.
365
367 mytop was developed and is maintained by Jeremy D. Zawodny
368 (Jeremy@Zawodny.com).
369 If you wish to e-mail me regarding this software, PLEASE subscribe to
371 the mytop mailing list. See the mytop homepage for details.
372
374 While I use this software in my job at Yahoo!, I am solely responsible
375 for it. Yahoo! does not necessarily support this software in any way.
376 It is merely a personal idea which happened to be very useful in my
377 job.
378
380 If you hack Perl and grok MySQL, come work at Yahoo! Contact me for
381 details. Or just send me your resume. Er, unless we just had layoffs,
382 in which case we're not hiring. :-(
383
385 Please check the MySQL manual if you're not sure where some of the
386 output of mytop is coming from.
387
389 Copyright (C) 2000-2001, Jeremy D. Zawodny.
390
392 Fix a bug. Add a feature. See your name here!
393 Many thanks go to these fine folks:
395 Sami Ahlroos (sami@avis-net.de)
397 Suggested the idle/noidle stuff.
398 Jan Willamowius (jan@janhh.shnet.org)
400 Mirnor bug report. Documentation fixes.
401 Alex Osipov (alex@acky.net)
403 Long command-line options, Unix socket support.
404 Stephane Enten (tuf@grolier.fr)
406 Suggested batch mode.
407 Richard Ellerbrock (richarde@eskom.co.za)
409 Bug reports and usability suggestions.
410 William R. Mattil (wrm@newton.irngtx.tel.gte.com)
412 Bug report about empty passwords not working.
413 Benjamin Pflugmann (philemon@spin.de)
415 Suggested -P command-line flag as well as other changes.
416 Justin Mecham <justin@aspect.net>
418 Suggested setting $0 to `mytop'.
419 Thorsten Kunz <thorsten.kunz@de.tiscali.com>
421 Provided a fix for cases when we try remove the domain name from
422 the display even if it is actually an IP address.
423 Sasha Pachev <sasha@mysql.com>
425 Provided the idea of real-time queries per second in the main
426 display.
427 Paul DuBois <paul@snake.net>
429 Pointed out some option-handling bugs.
430 Mike Wexler <mwexler@tias.com>
432 Suggested that we don't mangle (normalize) whitespace in query info
433 by default.
434 Mark Zweifel <markez@yahoo-inc.com>
436 Make the --idle command-line argument negatable.
437 Axel Schwenke <schwenke@jobpilot.de>
439 Noticed the inccorect formula for query cache hit percentages in
440 version 1.2.
441 Steven Roussey <sroussey@network54.com>
443 Supplied a patch to help filter binary junk in queries so that
444 terminals don't freak out.
445 jon r. luini <falcon@chime.com>
447 Supplied a patch that formed the basis for "-prompt" support. Sean
448 Leach <sleach@wiggum.com> submitted a similar patch.
449 Yogish Baliga <baliga@yahoo-inc.com>
451 Supplied a patch that formed the basis for "-resolve" support.
452 Per Andreas Buer <perbu@linpro.no>
454 Supplied an excellent patch to tidy up the top display. This
455 includes showing most values in short form, such as 10k rather than
456 10000.
457 See the Changes file on the mytop distribution page for more details on
459 what has changed.
460
462 mytop is licensed under the GNU General Public License version 2. For
463 the full license information, please visit
464 http://www.gnu.org/copyleft/gpl.html
465perl v5.30.1 2020-01-29 MYTOP(1)