1MYTOP(1)              User Contributed Perl Documentation             MYTOP(1)
2
3
4

NAME

6       mytop - display MySQL server performance info like `top'
7

SYNOPSIS

9       mytop [options]
10

AVAILABILITY

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

REQUIREMENTS

17       In order for mytop to function properly, you must have the following:
18
19         * Perl 5.005 or newer
20         * Getopt::Long
21         * DBI and DBD::mysql
22         * Term::ReadKey from CPAN
23
24       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
28           http://search.cpan.org/search?dist=TermReadKey
29
30       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
34       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
38   Optional Color Support
39       In additon, if you want a color mytop (recommended), install
40       Term::ANSIColor from the CPAN:
41
42           http://search.cpan.org/search?dist=ANSIColor
43
44       Once you do, mytop will automatically use it. However, color is not yet
45       working on Windows. Patches welcome. :-)
46
47   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
53   Platforms
54       mytop is known to work on:
55
56         * 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
63       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

DESCRIPTION

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
73       Having said that, here are the details on how it works and what you can
74       do with it.
75
76   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
81       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
85   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
90       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
99       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
104       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
109       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
113       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
117       You can toggle the header by hitting h when running mytop.
118
119       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
123           Id     User       Host      Dbase   Time      Cmd Query or State
124           --     ----       ----      -----   ----      --- --------------
125           61  jzawodn  localhost      music      0    Query show processlist
126
127       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
132       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
136       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
141           Query   -  Yellow
142           Sleep   -  White
143           Connect -  Green
144
145       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
149   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
154       -u or -user username
155           Username to use when logging in to the MySQL server. Default:
156           ``root''.
157
158       -p or -pass or -password password
159           Password to use when logging in to the MySQL server. Default: none.
160
161       -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
166       -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
170       -s or -delay seconds
171           How long between display refreshes. Default: 5
172
173       -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
177       -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
185           Default: unset.
186
187       -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
192           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
197           Default: none.
198
199       -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
203           Default: header.
204
205       -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
209           Default: If you have color support, mytop will try color unless you
210           tell it not to.
211
212       -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
218           Default: idle.
219
220       -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
229           Default: noprompt.
230
231       -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
236           Default: noresolve
237
238       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
242   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
249       Here is a sample config file "~/.mytop" which implements the defaults
250       described above.
251
252         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
264       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
269       You may have white space on either side of the "=" in lines of the
270       config file.
271
272   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
277       ?   Display help.
278
279       c   Show "command counters" based on the Com_* values in SHOW GLOBAL
280           STATUS.  This is a new feature.  Feedback welcome.
281
282       d   Show only threads connected to a particular database.
283
284       f   Given a thread id, display the entire query that thread was (and
285           still may be) running.
286
287       F   Disable all filtering (host, user, and db).
288
289       h   Only show queries from a particular host.
290
291       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
294       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
298       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
302       k   Kill a thread.
303
304       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
309           More modes may be added in the future.
310
311       o   Reverse the default sort order.
312
313       p   Pause display.
314
315       q   Quit mytop
316
317       r   Reset the server's status counters via a FLUSH STATUS command.
318
319       s   Change the sleep time (number of seconds between display
320           refreshes).
321
322       u   Show only threads owned by a giver user.
323
324       The s key has a command-line counterpart: -s.
325
326       The h key has two command-line counterparts: -header and -noheader.
327

BUGS

329       This is more of a BUGS + WishList.
330
331       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
337       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
341       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
349       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
353       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
358       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
361       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

AUTHOR

367       mytop was developed and is maintained by Jeremy D. Zawodny
368       (Jeremy@Zawodny.com).
369
370       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

DISCLAIMER

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

RECRUITING

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

SEE ALSO

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

CREDITS

392       Fix a bug. Add a feature. See your name here!
393
394       Many thanks go to these fine folks:
395
396       Sami Ahlroos (sami@avis-net.de)
397           Suggested the idle/noidle stuff.
398
399       Jan Willamowius (jan@janhh.shnet.org)
400           Mirnor bug report. Documentation fixes.
401
402       Alex Osipov (alex@acky.net)
403           Long command-line options, Unix socket support.
404
405       Stephane Enten (tuf@grolier.fr)
406           Suggested batch mode.
407
408       Richard Ellerbrock (richarde@eskom.co.za)
409           Bug reports and usability suggestions.
410
411       William R. Mattil (wrm@newton.irngtx.tel.gte.com)
412           Bug report about empty passwords not working.
413
414       Benjamin Pflugmann (philemon@spin.de)
415           Suggested -P command-line flag as well as other changes.
416
417       Justin Mecham <justin@aspect.net>
418           Suggested setting $0 to `mytop'.
419
420       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
424       Sasha Pachev <sasha@mysql.com>
425           Provided the idea of real-time queries per second in the main
426           display.
427
428       Paul DuBois <paul@snake.net>
429           Pointed out some option-handling bugs.
430
431       Mike Wexler <mwexler@tias.com>
432           Suggested that we don't mangle (normalize) whitespace in query info
433           by default.
434
435       Mark Zweifel <markez@yahoo-inc.com>
436           Make the --idle command-line argument negatable.
437
438       Axel Schwenke <schwenke@jobpilot.de>
439           Noticed the inccorect formula for query cache hit percentages in
440           version 1.2.
441
442       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
446       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
450       Yogish Baliga <baliga@yahoo-inc.com>
451           Supplied a patch that formed the basis for "-resolve" support.
452
453       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
458       See the Changes file on the mytop distribution page for more details on
459       what has changed.
460

LICENSE

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
465
466
467
468perl v5.30.1                      2020-01-29                          MYTOP(1)
Impressum