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