1INNOTOP(1) User Contributed Perl Documentation INNOTOP(1)
2
3
4
6 innotop - MySQL and InnoDB transaction/status monitor.
7
9 To monitor servers normally:
10
11 innotop
12
13 To monitor InnoDB status information from a file:
14
15 innotop /var/log/mysql/mysqld.err
16
17 To run innotop non-interactively in a pipe-and-filter configuration:
18
19 innotop --count 5 -d 1 -n
20
22 innotop monitors MySQL servers. Each of its modes shows you a differ‐
23 ent aspect of what's happening in the server. For example, there's a
24 mode for monitoring replication, one for queries, and one for transac‐
25 tions. innotop refreshes its data periodically, so you see an updating
26 view.
27
28 innotop has lots of features for power users, but you can start and run
29 it with virtually no configuration. If you're just getting started,
30 see "QUICK-START". Press '?' at any time while running innotop for
31 context-sensitive help.
32
34 To start innotop, open a terminal or command prompt. If you have
35 installed innotop on your system, you should be able to just type
36 "innotop" and press Enter; otherwise, you will need to change to inno‐
37 top's directory and type "perl innotop".
38
39 The first thing innotop needs to know is how to connect to a MySQL
40 server. You can just enter the hostname of the server, for example
41 "localhost" or "127.0.0.1" if the server is on the same machine as
42 innotop. After this innotop will prompt you for a DSN (data source
43 name). You should be able to just accept the defaults by pressing
44 Enter.
45
46 When innotop asks you about a table to use when resetting InnoDB dead‐
47 lock information, just accept the default for now. This is an advanced
48 feature you can configure later (see "D: InnoDB Deadlocks" for more).
49
50 If you have a .my.cnf file with your MySQL connection defaults, innotop
51 can read it, and you won't need to specify a username and password if
52 it's in that file. Otherwise, you should answer 'y' to the next couple
53 of prompts.
54
55 After this, you should be connected, and innotop should show you some‐
56 thing like the following:
57
58 InnoDB Txns (? for help) localhost, 01:11:19, InnoDB 10s :-), 50 QPS,
59
60 CXN History Versions Undo Dirty Buf Used Bufs Txns MaxTxn
61 localhost 7 2035 0 0 0.00% 92.19% 1 07:34
62
63 CXN ID User Host Txn Status Time Undo Query Tex
64 localhost 98379 user1 webserver ACTIVE 07:34 0 SELECT `c
65 localhost 98450 user1 webserver ACTIVE 01:06 0 INSERT IN
66 localhost 97750 user1 webserver not starte 00:00 0
67 localhost 98375 user1 appserver not starte 00:00 0
68
69 (This sample is truncated at the right so it will fit on a terminal
70 when running 'man innotop')
71
72 This sample comes from a quiet server with few transactions active. If
73 your server is busy, you'll see more output. Notice the first line on
74 the screen, which tells you what mode you're in and what server you're
75 connected to. You can change to other modes with keystrokes; press 'Q'
76 to switch to a list of currently running queries.
77
78 Press the '?' key to see what keys are active in the current mode. You
79 can press any of these keys and innotop will either take the requested
80 action or prompt you for more input. If your system has Term::ReadLine
81 support, you can use TAB and other keys to auto-complete and edit
82 input.
83
84 To quit innotop, press the 'q' key.
85
87 innotop is mostly configured via its configuration file, but some of
88 the configuration options can come from the command line. You can also
89 specify a file to monitor for InnoDB status output; see "MONITORING A
90 FILE" for more details.
91
92 You can negate some options by prefixing the option name with --no.
93 For example, --noinc (or --no-inc) negates "--inc".
94
95 --help
96 Print a summary of command-line usage and exit.
97
98 --color
99 Enable or disable terminal coloring. Corresponds to the "color"
100 config file setting.
101
102 --config
103 Specifies a configuration file to read. This option is non-sticky,
104 that is to say it does not persist to the configuration file
105 itself.
106
107 --nonint
108 Enable non-interactive operation. See "NON-INTERACTIVE OPERATION"
109 for more.
110
111 --count
112 Refresh only the specified number of times (ticks) before exiting.
113 Each refresh is a pause for "interval" seconds, followed by
114 requesting data from MySQL connections and printing it to the ter‐
115 minal.
116
117 --delay
118 Specifies the amount of time to pause between ticks (refreshes).
119 Corresponds to the configuration option "interval".
120
121 --mode
122 Specifies the mode in which innotop should start. Corresponds to
123 the configuration option "mode".
124
125 --inc
126 Specifies whether innotop should display absolute numbers or rela‐
127 tive numbers (offsets from their previous values). Corresponds to
128 the configuration option "status_inc".
129
130 --version
131 Output version information and exit.
132
134 innotop is interactive, and you control it with key-presses.
135
136 · Uppercase keys switch between modes.
137
138 · Lowercase keys initiate some action within the current mode.
139
140 · Other keys do something special like change configuration or show
141 the innotop license.
142
143 Press '?' at any time to see the currently active keys and what they
144 do.
145
147 Each of innotop's modes retrieves and displays a particular type of
148 data from the servers you're monitoring. You switch between modes with
149 uppercase keys. The following is a brief description of each mode, in
150 alphabetical order. To switch to the mode, press the key listed in
151 front of its heading in the following list:
152
153 B: InnoDB Buffers
154 This mode displays information about the InnoDB buffer pool, page
155 statistics, insert buffer, and adaptive hash index. The data comes
156 from SHOW INNODB STATUS.
157
158 This mode contains the "buffer_pool", "page_statistics",
159 "insert_buffers", and "adaptive_hash_index" tables by default.
160
161 C: Command Summary
162 This mode is similar to mytop's Command Summary mode. It shows the
163 "cmd_summary" table, which looks something like the following:
164
165 Command Summary (? for help) localhost, 25+07:16:43, 2.45 QPS, 3 thd, 5.0.40
166 _____________________ Command Summary _____________________
167 Name Value Pct Last Incr Pct
168 Select_scan 3244858 69.89% 2 100.00%
169 Select_range 1354177 29.17% 0 0.00%
170 Select_full_join 39479 0.85% 0 0.00%
171 Select_full_range_join 4097 0.09% 0 0.00%
172 Select_range_check 0 0.00% 0 0.00%
173
174 The command summary table is built by extracting variables from
175 "STATUS_VARIABLES". The variables must be numeric and must match
176 the prefix given by the "cmd_filter" configuration variable. The
177 variables are then sorted by value descending and compared to the
178 last variable, as shown above. The percentage columns are percent‐
179 age of the total of all variables in the table, so you can see the
180 relative weight of the variables.
181
182 The example shows what you see if the prefix is "Select_". The
183 default prefix is "Com_". You can choose a prefix with the 's'
184 key.
185
186 It's rather like running SHOW VARIABLES LIKE "prefix%" with memory
187 and nice formatting.
188
189 Values are aggregated across all servers. The Pct columns are not
190 correctly aggregated across multiple servers. This is a known lim‐
191 itation of the grouping algorithm that may be fixed in the future.
192
193 D: InnoDB Deadlocks
194 This mode shows the transactions involved in the last InnoDB dead‐
195 lock. A second table shows the locks each transaction held and
196 waited for. A deadlock is caused by a cycle in the waits-for
197 graph, so there should be two locks held and one waited for unless
198 the deadlock information is truncated.
199
200 InnoDB puts deadlock information before some other information in
201 the SHOW INNODB STATUS output. If there are a lot of locks, the
202 deadlock information can grow very large, and there is a limit on
203 the size of the SHOW INNODB STATUS output. A large deadlock can
204 fill the entire output, or even be truncated, and prevent you from
205 seeing other information at all. If you are running innotop in
206 another mode, for example T mode, and suddenly you don't see any‐
207 thing, you might want to check and see if a deadlock has wiped out
208 the data you need.
209
210 If it has, you can create a small deadlock to replace the large
211 one. Use the 'w' key to 'wipe' the large deadlock with a small
212 one. This will not work unless you have defined a deadlock table
213 for the connection (see "SERVER CONNECTIONS").
214
215 You can also configure innotop to automatically detect when a large
216 deadlock needs to be replaced with a small one (see
217 "auto_wipe_dl").
218
219 This mode displays the "deadlock_transactions" and "deadlock_locks"
220 tables by default.
221
222 F: InnoDB Foreign Key Errors
223 This mode shows the last InnoDB foreign key error information, such
224 as the table where it happened, when and who and what query caused
225 it, and so on.
226
227 InnoDB has a huge variety of foreign key error messages, and many
228 of them are just hard to parse. innotop doesn't always do the best
229 job here, but there's so much code devoted to parsing this messy,
230 unparseable output that innotop is likely never to be perfect in
231 this regard. If innotop doesn't show you what you need to see,
232 just look at the status text directly.
233
234 This mode displays the "fk_error" table by default.
235
236 I: InnoDB I/O Info
237 This mode shows InnoDB's I/O statistics, including the I/O threads,
238 pending I/O, file I/O miscellaneous, and log statistics. It dis‐
239 plays the "io_threads", "pending_io", "file_io_misc", and "log_sta‐
240 tistics" tables by default.
241
242 L: Locks
243 This mode shows information about current locks. At the moment
244 only InnoDB locks are supported, and by default you'll only see
245 locks for which transactions are waiting. This information comes
246 from the TRANSACTIONS section of the InnoDB status text. If you
247 have a very busy server, you may have frequent lock waits; it helps
248 to be able to see which tables and indexes are the "hot spot" for
249 locks. If your server is running pretty well, this mode should
250 show nothing.
251
252 You can configure MySQL and innotop to monitor not only locks for
253 which a transaction is waiting, but those currently held, too. You
254 can do this with the InnoDB Lock Monitor
255 (<http://dev.mysql.com/doc/en/innodb-monitor.html>). It's not doc‐
256 umented in the MySQL manual, but creating the lock monitor with the
257 following statement also affects the output of SHOW INNODB STATUS,
258 which innotop uses:
259
260 CREATE TABLE innodb_lock_monitor(a int) ENGINE=INNODB;
261
262 This causes InnoDB to print its output to the MySQL file every 16
263 seconds or so, as stated in the manual, but it also makes the nor‐
264 mal SHOW INNODB STATUS output include lock information, which inno‐
265 top can parse and display (that's the undocumented feature).
266
267 This means you can do what may have seemed impossible: to a limited
268 extent (InnoDB truncates some information in the output), you can
269 see which transaction holds the locks something else is waiting
270 for. You can also enable and disable the InnoDB Lock Monitor with
271 the key mappings in this mode.
272
273 This mode displays the "innodb_locks" table by default. Here's a
274 sample of the screen when one connection is waiting for locks
275 another connection holds:
276
277 _________________________________ InnoDB Locks __________________________
278 CXN ID Type Waiting Wait Active Mode DB Table Index
279 localhost 12 RECORD 1 00:10 00:10 X test t1 PRIMARY
280 localhost 12 TABLE 0 00:10 00:10 IX test t1
281 localhost 12 RECORD 1 00:10 00:10 X test t1 PRIMARY
282 localhost 11 TABLE 0 00:00 00:25 IX test t1
283 localhost 11 RECORD 0 00:00 00:25 X test t1 PRIMARY
284
285 You can see the first connection, ID 12, is waiting for a lock on
286 the PRIMARY key on test.t1, and has been waiting for 10 seconds.
287 The second connection isn't waiting, because the Waiting column is
288 0, but it holds locks on the same index. That tells you connection
289 11 is blocking connection 12.
290
291 M: Master/Slave Replication Status
292 This mode shows the output of SHOW SLAVE STATUS and SHOW MASTER
293 STATUS in three tables. The first two divide the slave's status
294 into SQL and I/O thread status, and the last shows master status.
295 Filters are applied to eliminate non-slave servers from the slave
296 tables, and non-master servers from the master table.
297
298 This mode displays the "slave_sql_status", "slave_io_status", and
299 "master_status" tables by default.
300
301 O: Open Tables
302 This section comes from MySQL's SHOW OPEN TABLES command. By
303 default it is filtered to show tables which are in use by one or
304 more queries, so you can get a quick look at which tables are
305 'hot'. You can use this to guess which tables might be locked
306 implicitly.
307
308 This mode displays the "open_tables" mode by default.
309
310 Q: Query List
311 This mode displays the output from SHOW FULL PROCESSLIST, much like
312 mytop's query list mode. This mode does not show InnoDB-related
313 information. This is probably one of the most useful modes for
314 general usage.
315
316 There is an informative header that shows general status informa‐
317 tion about your server. You can toggle it on and off with the 'h'
318 key. By default, innotop hides inactive processes and its own
319 process. You can toggle these on and off with the 'i' and 'a'
320 keys.
321
322 You can EXPLAIN a query from this mode with the 'e' key. This dis‐
323 plays the query's full text, the results of EXPLAIN, and in newer
324 MySQL versions, even the optimized query resulting from EXPLAIN
325 EXTENDED. innotop also tries to rewrite certain queries to make
326 them EXPLAIN-able. For example, INSERT/SELECT statements are
327 rewritable.
328
329 This mode displays the "q_header" and "processlist" tables by
330 default.
331
332 R: InnoDB Row Operations and Semaphores
333 This mode shows InnoDB row operations, row operation miscellaneous,
334 semaphores, and information from the wait array. It displays the
335 "row_operations", "row_operation_misc", "semaphores", and
336 "wait_array" tables by default.
337
338 S: Variables & Status
339 This mode calculates statistics, such as queries per second, and
340 prints them out in several different styles. You can show absolute
341 values, or incremental values between ticks.
342
343 You can switch between the views by pressing a key. The 's' key
344 prints a single line each time the screen updates, in the style of
345 vmstat. The 'g' key changes the view to a graph of the same num‐
346 bers, sort of like tload. The 'v' key changes the view to a piv‐
347 oted table of variable names on the left, with successive updates
348 scrolling across the screen from left to right. You can choose how
349 many updates to put on the screen with the "num_status_sets" con‐
350 figuration variable.
351
352 Headers may be abbreviated to fit on the screen in interactive
353 operation. You choose which variables to display with the 'c' key,
354 which selects from predefined sets, or lets you create your own
355 sets. You can edit the current set with the 'e' key.
356
357 This mode doesn't really display any tables like other modes.
358 Instead, it uses a table definition to extract and format the data,
359 but it then transforms the result in special ways before outputting
360 it. It uses the "var_status" table definition for this.
361
362 T: InnoDB Transactions
363 This mode shows transactions from the InnoDB monitor's output, in
364 top-like format. This mode is the reason I wrote innotop.
365
366 You can kill queries or processes with the 'k' and 'x' keys, and
367 EXPLAIN a query with the 'e' or 'f' keys. InnoDB doesn't print the
368 full query in transactions, so explaining may not work right if the
369 query is truncated.
370
371 The informational header can be toggled on and off with the 'h'
372 key. By default, innotop hides inactive transactions and its own
373 transaction. You can toggle this on and off with the 'i' and 'a'
374 keys.
375
376 This mode displays the "t_header" and "innodb_transactions" tables
377 by default.
378
380 The first line innotop displays is a "status bar" of sorts. What it
381 contains depends on the mode you're in, and what servers you're moni‐
382 toring. The first few words are always the innotop mode, such as "Inn‐
383 oDB Txns" for T mode, followed by a reminder to press '?' for help at
384 any time.
385
386 ONE SERVER
387
388 The simplest case is when you're monitoring a single server. In this
389 case, the name of the connection is next on the status line. This is
390 the name you gave when you created the connection -- most likely the
391 MySQL server's hostname. This is followed by the server's uptime.
392
393 If you're in an InnoDB mode, such as T or B, the next word is "InnoDB"
394 followed by some information about the SHOW INNODB STATUS output used
395 to render the screen. The first word is the number of seconds since
396 the last SHOW INNODB STATUS, which InnoDB uses to calculate some per-
397 second statistics. The next is a smiley face indicating whether the
398 InnoDB output is truncated. If the smiley face is a :-), all is well;
399 there is no truncation. A :^⎪ means the transaction list is so long,
400 InnoDB has only printed out some of the transactions. Finally, a frown
401 :-( means the output is incomplete, which is probably due to a deadlock
402 printing too much lock information (see "D: InnoDB Deadlocks").
403
404 The next two words indicate the server's queries per second (QPS) and
405 how many threads (connections) exist. Finally, the server's version
406 number is the last thing on the line.
407
408 MULTIPLE SERVERS
409
410 If you are monitoring multiple servers (see "SERVER CONNECTIONS"), the
411 status line does not show any details about individual servers.
412 Instead, it shows the names of the connections that are active. Again,
413 these are connection names you specified, which are likely to be the
414 server's hostname. A connection that has an error is prefixed with an
415 exclamation point.
416
417 If you are monitoring a group of servers (see "SERVER GROUPS"), the
418 status line shows the name of the group. If any connection in the
419 group has an error, the group's name is followed by the fraction of the
420 connections that don't have errors.
421
422 See "ERROR HANDLING" for more details about innotop's error handling.
423
424 MONITORING A FILE
425
426 If you give a filename on the command line, innotop will not connect to
427 ANY servers at all. It will watch the specified file for InnoDB status
428 output and use that as its data source. It will always show a single
429 connection called 'file'. And since it can't connect to a server, it
430 can't determine how long the server it's monitoring has been up; so it
431 calculates the server's uptime as time since innotop started running.
432
434 While innotop is primarily a monitor that lets you watch and analyze
435 your servers, it can also send commands to servers. The most fre‐
436 quently useful commands are killing queries and stopping or starting
437 slaves.
438
439 You can kill a connection, or in newer versions of MySQL kill a query
440 but not a connection, from "Q: Query List" and "T: InnoDB Transactions"
441 modes. Press 'k' to issue a KILL command, or 'x' to issue a KILL QUERY
442 command. innotop will prompt you for the server and/or connection ID
443 to kill (innotop does not prompt you if there is only one possible
444 choice for any input). innotop pre-selects the longest-running query,
445 or the oldest connection. Confirm the command with 'y'.
446
447 In "M: Master/Slave Replication Status" mode, you can start and stop
448 slaves with the 'a' and 'o' keys, respectively. You can send these
449 commands to many slaves at once. innotop fills in a default command of
450 START SLAVE or STOP SLAVE for you, but you can actually edit the com‐
451 mand and send anything you wish, such as SET GLOBAL
452 SQL_SLAVE_SKIP_COUNTER=1 to make the slave skip one binlog event when
453 it starts.
454
455 You can also ask innotop to calculate the earliest binlog in use by any
456 slave and issue a PURGE MASTER LOGS on the master. Use the 'b' key for
457 this. innotop will prompt you for a master to run the command on, then
458 prompt you for the connection names of that master's slaves (there is
459 no way for innotop to determine this reliably itself). innotop will
460 find the minimum binlog in use by these slave connections and suggest
461 it as the argument to PURGE MASTER LOGS.
462
464 When you create a server connection, innotop asks you for a series of
465 inputs, as follows:
466
467 DSN A DSN is a Data Source Name, which is the initial argument passed
468 to the DBI module for connecting to a server. It is usually of the
469 form
470
471 DBI:mysql:;mysql_read_default_group=mysql;host=HOSTNAME
472
473 Since this DSN is passed to the DBD::mysql driver, you should read
474 the driver's documentation at
475 "http://search.cpan.org/dist/DBD-mysql/lib/DBD/mysql.pm" for the
476 exact details on all the options you can pass the driver in the
477 DSN. You can read more about DBI at <http://dbi.perl.org/docs/>,
478 and especially at <http://search.cpan.org/~timb/DBI/DBI.pm>.
479
480 The mysql_read_default_group=mysql option lets the DBD driver read
481 your MySQL options files, such as ~/.my.cnf on UNIX-ish systems.
482 You can use this to avoid specifying a username or password for the
483 connection.
484
485 InnoDB Deadlock Table
486 This optional item tells innotop a table name it can use to delib‐
487 erately create a small deadlock (see "D: InnoDB Deadlocks"). If
488 you specify this option, you just need to be sure the table doesn't
489 exist, and that innotop can create and drop the table with the Inn‐
490 oDB storage engine. You can safely omit or just accept the default
491 if you don't intend to use this.
492
493 Username
494 innotop will ask you if you want to specify a username. If you say
495 'y', it will then prompt you for a user name. If you have a MySQL
496 option file that specifies your username, you don't have to specify
497 a username.
498
499 The username defaults to your login name on the system you're run‐
500 ning innotop on.
501
502 Password
503 innotop will ask you if you want to specify a password. Like the
504 username, the password is optional, but there's an additional
505 prompt that asks if you want to save the password in the innotop
506 configuration file. If you don't save it in the configuration
507 file, innotop will prompt you for a password each time it starts.
508 Passwords in the innotop configuration file are saved in plain
509 text, not encrypted in any way.
510
511 Once you finish answering these questions, you should be connected to a
512 server. But innotop isn't limited to monitoring a single server; you
513 can define many server connections and switch between them by pressing
514 the '@' key. See "SWITCHING BETWEEN CONNECTIONS".
515
516 To create a new connection, press the '@' key and type the name of the
517 new connection, then follow the steps given above.
518
520 If you have multiple MySQL instances, you can put them into named
521 groups, such as 'all', 'masters', and 'slaves', which innotop can moni‐
522 tor all together.
523
524 You can choose which group to monitor with the '#' key, and you can
525 press the TAB key to switch to the next group. If you're not currently
526 monitoring a group, pressing TAB selects the first group.
527
528 To create a group, press the '#' key and type the name of your new
529 group, then type the names of the connections you want the group to
530 contain.
531
533 innotop lets you quickly switch which servers you're monitoring. The
534 most basic way is by pressing the '@' key and typing the name(s) of the
535 connection(s) you want to use. This setting is per-mode, so you can
536 monitor different connections in each mode, and innotop remembers which
537 connections you choose.
538
539 You can quickly switch to the 'next' connection in alphabetical order
540 with the 'n' key. If you're monitoring a server group (see "SERVER
541 GROUPS") this will switch to the first connection.
542
543 You can also type many connection names, and innotop will fetch and
544 display data from them all. Just separate the connection names with
545 spaces, for example "server1 server2." Again, if you type the name of
546 a connection that doesn't exist, innotop will prompt you for connection
547 information and create the connection.
548
549 Another way to monitor multiple connections at once is with server
550 groups. You can use the TAB key to switch to the 'next' group in
551 alphabetical order, or if you're not monitoring any groups, TAB will
552 switch to the first group.
553
554 innotop does not fetch data in parallel from connections, so if you are
555 monitoring a large group or many connections, you may notice increased
556 delay between ticks.
557
558 When you monitor more than one connection, innotop's status bar
559 changes. See "INNOTOP STATUS".
560
562 Error handling is not that important when monitoring a single connec‐
563 tion, but is crucial when you have many active connections. A crashed
564 server or lost connection should not crash innotop. As a result, inno‐
565 top will continue to run even when there is an error; it just won't
566 display any information from the connection that had an error. Because
567 of this, innotop's behavior might confuse you. It's a feature, not a
568 bug!
569
570 innotop does not continue to query connections that have errors,
571 because they may slow innotop and make it hard to use, especially if
572 the error is a problem connecting and causes a long time-out. Instead,
573 innotop retries the connection occasionally to see if the error still
574 exists. If so, it will wait until some point in the future. The wait
575 time increases in ticks as the Fibonacci series, so it tries less fre‐
576 quently as time passes.
577
578 Since errors might only happen in certain modes because of the SQL com‐
579 mands issued in those modes, innotop keeps track of which mode caused
580 the error. If you switch to a different mode, innotop will retry the
581 connection instead of waiting.
582
583 By default innotop will display the problem in red text at the bottom
584 of the first table on the screen. You can disable this behavior with
585 the "show_cxn_errors_in_tbl" configuration option, which is enabled by
586 default. If the "debug" option is enabled, innotop will display the
587 error at the bottom of every table, not just the first. And if
588 "show_cxn_errors" is enabled, innotop will print the error text to STD‐
589 OUT as well. Error messages might only display in the mode that caused
590 the error, depending on the mode and whether innotop is avoiding query‐
591 ing that connection.
592
594 You can run innotop in non-interactive mode, in which case it is
595 entirely controlled from the configuration file and command-line
596 options. To start innotop in non-interactive mode, give the L"<--non‐
597 int"> command-line option. This changes innotop's behavior in the fol‐
598 lowing ways:
599
600 · Certain Perl modules are not loaded. Term::Readline is not loaded,
601 since innotop doesn't prompt interactively. Term::ANSIColor and
602 Win32::Console::ANSI modules are not loaded. Term::ReadKey is
603 still used, since innotop may have to prompt for connection pass‐
604 words when starting up.
605
606 · innotop does not clear the screen after each tick.
607
608 · innotop does not persist any changes to the configuration file.
609
610 · If "--count" is given and innotop is in incremental mode (see "sta‐
611 tus_inc" and "--inc"), innotop actually refreshes one more time
612 than specified so it can print incremental statistics. This sup‐
613 presses output during the first tick, so innotop may appear to
614 hang.
615
616 · innotop only displays the first table in each mode. This is so the
617 output can be easily processed with other command-line utilities
618 such as awk and sed. To change which tables display in each mode,
619 see "TABLES". Since "Q: Query List" mode is so important, innotop
620 automatically disables the "q_header" table. This ensures you'll
621 see the "processlist" table, even if you have innotop configured to
622 show the q_header table during interactive operation. Similarly,
623 in "T: InnoDB Transactions" mode, the "t_header" table is sup‐
624 pressed so you see only the "innodb_transactions" table.
625
626 · All output is tab-separated instead of being column-aligned with
627 whitespace, and innotop prints the full contents of each table
628 instead of only printing one screenful at a time.
629
630 · innotop only prints column headers once instead of every tick (see
631 "hide_hdr"). innotop does not print table captions (see "dis‐
632 play_table_captions"). innotop ensures there are no empty lines in
633 the output.
634
635 · innotop does not honor the "shorten" transformation, which normally
636 shortens some numbers to human-readable formats.
637
638 · innotop does not print a status line (see "INNOTOP STATUS").
639
641 Nearly everything about innotop is configurable. Most things are pos‐
642 sible to change with built-in commands, but you can also edit the con‐
643 figuration file.
644
645 While running innotop, press the '$' key to bring up the configuration
646 editing dialog. Press another key to select the type of data you want
647 to edit:
648
649 S: Statement Sleep Times
650 Edits SQL statement sleep delays, which make innotop pause for the
651 specified amount of time after executing a statement. See "SQL
652 STATEMENTS" for a definition of each statement and what it does.
653 By default innotop does not delay after any statements.
654
655 This feature is included so you can customize the side-effects
656 caused by monitoring your server. You may not see any effects, but
657 some innotop users have noticed that certain MySQL versions under
658 very high load with InnoDB enabled take longer than usual to exe‐
659 cute SHOW GLOBAL STATUS. If innotop calls SHOW FULL PROCESSLIST
660 immediately afterward, the processlist contains more queries than
661 the machine actually averages at any given moment. Configuring
662 innotop to pause briefly after calling SHOW GLOBAL STATUS allevi‐
663 ates this effect.
664
665 Sleep times are stored in the "stmt_sleep_times" section of the
666 configuration file. Fractional-second sleeps are supported, sub‐
667 ject to your hardware's limitations.
668
669 c: Edit Columns
670 Starts the table editor on one of the displayed tables. See "TABLE
671 EDITOR". An alternative way to start the table editor without
672 entering the configuration dialog is with the '^' key.
673
674 g: General Configuration
675 Starts the configuration editor to edit global and mode-specific
676 configuration variables (see "MODES"). innotop prompts you to
677 choose a variable from among the global and mode-specific ones
678 depending on the current mode.
679
680 k: Row-Coloring Rules
681 Starts the row-coloring rules editor on one of the displayed ta‐
682 ble(s). See "COLORS" for details.
683
684 p: Manage Plugins
685 Starts the plugin configuration editor. See "PLUGINS" for details.
686
687 s: Server Groups
688 Lets you create and edit server groups. See "SERVER GROUPS".
689
690 t: Choose Displayed Tables
691 Lets you choose which tables to display in this mode. See "MODES"
692 and "TABLES".
693
695 innotop's default configuration file location is in $HOME/.innotop, but
696 can be overridden with the "--config" command-line option. You can
697 edit it by hand safely. innotop reads the configuration file when it
698 starts, and writes it out again when it exits, so any changes you make
699 while innotop is running will be lost.
700
701 innotop doesn't store its entire configuration in the configuration
702 file. It has a huge set of default configuration that it holds only in
703 memory, and the configuration file only overrides these defaults. When
704 you customize a default setting, innotop notices, and then stores the
705 customizations into the file. This keeps the file size down, makes it
706 easier to edit, and makes upgrades easier.
707
708 A configuration file can be made read-only. See "readonly".
709
710 The configuration file is arranged into sections like an INI file.
711 Each section begins with [section-name] and ends with [/section-name].
712 Each section's entries have a different syntax depending on the data
713 they need to store. You can put comments in the file; any line that
714 begins with a # character is a comment. innotop will not read the com‐
715 ments, so it won't write them back out to the file when it exits. Com‐
716 ments in read-only configuration files are still useful, though.
717
718 The first line in the file is innotop's version number. This lets
719 innotop notice when the file format is not backwards-compatible, and
720 upgrade smoothly without destroying your customized configuration.
721
722 The following list describes each section of the configuration file and
723 the data it contains:
724
725 general
726 The 'general' section contains global configuration variables and
727 variables that may be mode-specific, but don't belong in any other
728 section. The syntax is a simple key=value list. innotop writes a
729 comment above each value to help you edit the file by hand.
730
731 S_func
732 Controls S mode presentation (see "S: Variables & Status"). If
733 g, values are graphed; if s, values are like vmstat; if p, val‐
734 ues are in a pivoted table.
735
736 S_set
737 Specifies which set of variables to display in "S: Variables &
738 Status" mode. See "VARIABLE SETS".
739
740 auto_wipe_dl
741 Instructs innotop to automatically wipe large deadlocks when it
742 notices them. When this happens you may notice a slight delay.
743 At the next tick, you will usually see the information that was
744 being truncated by the large deadlock.
745
746 charset
747 Specifies what kind of characters to allow through the
748 "no_ctrl_char" transformation. This keeps non-printable char‐
749 acters from confusing a terminal when you monitor queries that
750 contain binary data, such as images.
751
752 The default is 'ascii', which considers anything outside normal
753 ASCII to be a control character. The other allowable values
754 are 'unicode' and 'none'. 'none' considers every character a
755 control character, which can be useful for collapsing ALL text
756 fields in queries.
757
758 cmd_filter
759 This is the prefix that filters variables in "C: Command Sum‐
760 mary" mode.
761
762 color
763 Whether terminal coloring is permitted.
764
765 cxn_timeout
766 On MySQL versions 4.0.3 and newer, this variable is used to set
767 the connection's timeout, so MySQL doesn't close the connection
768 if it is not used for a while. This might happen because a
769 connection isn't monitored in a particular mode, for example.
770
771 debug
772 This option enables more verbose errors and makes innotop more
773 strict in some places. It can help in debugging filters and
774 other user-defined code. It also makes innotop write a lot of
775 information to "debugfile" when there is a crash.
776
777 debugfile
778 A file to which innotop will write information when there is a
779 crash. See "FILES".
780
781 display_table_captions
782 innotop displays a table caption above most tables. This vari‐
783 able suppresses or shows captions on all tables globally. Some
784 tables are configured with the hide_caption property, which
785 overrides this.
786
787 global
788 Whether to show GLOBAL variables and status. innotop only
789 tries to do this on servers which support the GLOBAL option to
790 SHOW VARIABLES and SHOW STATUS. In some MySQL versions, you
791 need certain privileges to do this; if you don't have them,
792 innotop will not be able to fetch any variable and status data.
793 This configuration variable lets you run innotop and fetch what
794 data you can even without the elevated privileges.
795
796 I can no longer find or reproduce the situation where GLOBAL
797 wasn't allowed, but I know there was one.
798
799 graph_char
800 Defines the character to use when drawing graphs in "S: Vari‐
801 ables & Status" mode.
802
803 header_highlight
804 Defines how to highlight column headers. This only works if
805 Term::ANSIColor is available. Valid values are 'bold' and
806 'underline'.
807
808 hide_hdr
809 Hides column headers globally.
810
811 interval
812 The interval at which innotop will refresh its data (ticks).
813 The interval is implemented as a sleep time between ticks, so
814 the true interval will vary depending on how long it takes
815 innotop to fetch and render data.
816
817 This variable accepts fractions of a second.
818
819 mode
820 The mode in which innotop should start. Allowable arguments
821 are the same as the key presses that select a mode interac‐
822 tively. See "MODES".
823
824 num_digits
825 How many digits to show in fractional numbers and percents.
826 This variable's range is between 0 and 9 and can be set
827 directly from "S: Variables & Status" mode with the '+' and '-'
828 keys. It is used in the "set_precision", "shorten", and "per‐
829 cent" transformations.
830
831 num_status_sets
832 Controls how many sets of status variables to display in piv‐
833 oted "S: Variables & Status" mode. It also controls the number
834 of old sets of variables innotop keeps in its memory, so the
835 larger this variable is, the more memory innotop uses.
836
837 plugin_dir
838 Specifies where plugins can be found. By default, innotop
839 stores plugins in the 'plugins' subdirectory of your innotop
840 configuration directory.
841
842 readonly
843 Whether the configuration file is readonly. This cannot be set
844 interactively, because it would prevent itself from being writ‐
845 ten to the configuration file.
846
847 show_cxn_errors
848 Makes innotop print connection errors to STDOUT. See "ERROR
849 HANDLING".
850
851 show_cxn_errors_in_tbl
852 Makes innotop display connection errors as rows in the first
853 table on screen. See "ERROR HANDLING".
854
855 show_percent
856 Adds a '%' character after the value returned by the "percent"
857 transformation.
858
859 show_statusbar
860 Controls whether to show the status bar in the display. See
861 "INNOTOP STATUS".
862
863 skip_innodb
864 Disables fetching SHOW INNODB STATUS, in case your server(s) do
865 not have InnoDB enabled and you don't want innotop to try to
866 fetch it. This can also be useful when you don't have the
867 SUPER privilege, required to run SHOW INNODB STATUS.
868
869 status_inc
870 Whether to show absolute or incremental values for status vari‐
871 ables. Incremental values are calculated as an offset from the
872 last value innotop saw for that variable. This is a global
873 setting, but will probably become mode-specific at some point.
874 Right now it is honored a bit inconsistently; some modes don't
875 pay attention to it.
876
877 plugins
878 This section holds a list of package names of active plugins. If
879 the plugin exists, innotop will activate it. See "PLUGINS" for
880 more information.
881
882 filters
883 This section holds user-defined filters (see "FILTERS"). Each line
884 is in the format filter_name=text='filter text' tbls='table list'.
885
886 The filter text is the text of the subroutine's code. The table
887 list is a list of tables to which the filter can apply. By
888 default, user-defined filters apply to the table for which they
889 were created, but you can manually override that by editing the
890 definition in the configuration file.
891
892 active_filters
893 This section stores which filters are active on each table. Each
894 line is in the format table_name=filter_list.
895
896 tbl_meta
897 This section stores user-defined or user-customized columns (see
898 "COLUMNS"). Each line is in the format col_name=properties, where
899 the properties are a name=quoted-value list.
900
901 connections
902 This section holds the server connections you have defined. Each
903 line is in the format name=properties, where the properties are a
904 name=value list. The properties are self-explanatory, and the only
905 one that is treated specially is 'pass' which is only present if
906 'savepass' is set. See "SERVER CONNECTIONS".
907
908 active_connections
909 This section holds a list of which connections are active in each
910 mode. Each line is in the format mode_name=connection_list.
911
912 server_groups
913 This section holds server groups. Each line is in the format
914 name=connection_list. See "SERVER GROUPS".
915
916 active_server_groups
917 This section holds a list of which server group is active in each
918 mode. Each line is in the format mode_name=server_group.
919
920 max_values_seen
921 This section holds the maximum values seen for variables. This is
922 used to scale the graphs in "S: Variables & Status" mode. Each
923 line is in the format name=value.
924
925 active_columns
926 This section holds table column lists. Each line is in the format
927 tbl_name=column_list. See "COLUMNS".
928
929 sort_cols
930 This section holds the sort definition. Each line is in the format
931 tbl_name=column_list. If a column is prefixed with '-', that col‐
932 umn sorts descending. See "SORTING".
933
934 visible_tables
935 This section defines which tables are visible in each mode. Each
936 line is in the format mode_name=table_list. See "TABLES".
937
938 varsets
939 This section defines variable sets for use in "S: Status & Vari‐
940 ables" mode. Each line is in the format name=variable_list. See
941 "VARIABLE SETS".
942
943 colors
944 This section defines colorization rules. Each line is in the for‐
945 mat tbl_name=property_list. See "COLORS".
946
947 stmt_sleep_times
948 This section contains statement sleep times. Each line is in the
949 format statement_name=sleep_time. See "S: Statement Sleep Times".
950
951 group_by
952 This section contains column lists for table group_by expressions.
953 Each line is in the format tbl_name=column_list. See "GROUPING".
954
956 You can customize innotop a great deal. For example, you can:
957
958 · Choose which tables to display, and in what order.
959
960 · Choose which columns are in those tables, and create new columns.
961
962 · Filter which rows display with built-in filters, user-defined fil‐
963 ters, and quick-filters.
964
965 · Sort the rows to put important data first or group together related
966 rows.
967
968 · Highlight rows with color.
969
970 · Customize the alignment, width, and formatting of columns, and
971 apply transformations to columns to extract parts of their values
972 or format the values as you wish (for example, shortening large
973 numbers to familiar units).
974
975 · Design your own expressions to extract and combine data as you
976 need. This gives you unlimited flexibility.
977
978 All these and more are explained in the following sections.
979
980 TABLES
981
982 A table is what you'd expect: a collection of columns. It also has
983 some other properties, such as a caption. Filters, sorting rules, and
984 colorization rules belong to tables and are covered in later sections.
985
986 Internally, table meta-data is defined in a data structure called
987 %tbl_meta. This hash holds all built-in table definitions, which con‐
988 tain a lot of default instructions to innotop. The meta-data includes
989 the caption, a list of columns the user has customized, a list of col‐
990 umns, a list of visible columns, a list of filters, color rules, a
991 sort-column list, sort direction, and some information about the ta‐
992 ble's data sources. Most of this is customizable via the table editor
993 (see "TABLE EDITOR").
994
995 You can choose which tables to show by pressing the '$' key. See
996 "MODES" and "TABLES".
997
998 The table life-cycle is as follows:
999
1000 · Each table begins with a data source, which is an array of hashes.
1001 See below for details on data sources.
1002
1003 · Each element of the data source becomes a row in the final table.
1004
1005 · For each element in the data source, innotop extracts values from
1006 the source and creates a row. This row is another hash, which
1007 later steps will refer to as $set. The values innotop extracts are
1008 determined by the table's columns. Each column has an extraction
1009 subroutine, compiled from an expression (see "EXPRESSIONS"). The
1010 resulting row is a hash whose keys are named the same as the column
1011 name.
1012
1013 · innotop filters the rows, removing those that don't need to be dis‐
1014 played. See "FILTERS".
1015
1016 · innotop sorts the rows. See "SORTING".
1017
1018 · innotop groups the rows together, if specified. See "GROUPING".
1019
1020 · innotop colorizes the rows. See "COLORS".
1021
1022 · innotop transforms the column values in each row. See "TRANSFORMA‐
1023 TIONS".
1024
1025 · innotop optionally pivots the rows (see "PIVOTING"), then filters
1026 and sorts them.
1027
1028 · innotop formats and justifies the rows as a table. During this
1029 step, innotop applies further formatting to the column values,
1030 including alignment, maximum and minimum widths. innotop also does
1031 final error checking to ensure there are no crashes due to unde‐
1032 fined values. innotop then adds a caption if specified, and the
1033 table is ready to print.
1034
1035 The lifecycle is slightly different if the table is pivoted, as noted
1036 above. To clarify, if the table is pivoted, the process is extract,
1037 group, transform, pivot, filter, sort, create. If it's not pivoted,
1038 the process is extract, filter, sort, group, color, transform, create.
1039 This slightly convoluted process doesn't map all that well to SQL, but
1040 pivoting complicates things pretty thoroughly. Roughly speaking, fil‐
1041 tering and sorting happen as late as needed to effect the final result
1042 as you might expect, but as early as possible for efficiency.
1043
1044 Each built-in table is described below:
1045
1046 adaptive_hash_index
1047 Displays data about InnoDB's adaptive hash index. Data source:
1048 "STATUS_VARIABLES".
1049
1050 buffer_pool
1051 Displays data about InnoDB's buffer pool. Data source: "STA‐
1052 TUS_VARIABLES".
1053
1054 cmd_summary
1055 Displays weighted status variables. Data source: "STATUS_VARI‐
1056 ABLES".
1057
1058 deadlock_locks
1059 Shows which locks were held and waited for by the last detected
1060 deadlock. Data source: "DEADLOCK_LOCKS".
1061
1062 deadlock_transactions
1063 Shows transactions involved in the last detected deadlock. Data
1064 source: "DEADLOCK_TRANSACTIONS".
1065
1066 explain
1067 Shows the output of EXPLAIN. Data source: "EXPLAIN".
1068
1069 file_io_misc
1070 Displays data about InnoDB's file and I/O operations. Data source:
1071 "STATUS_VARIABLES".
1072
1073 fk_error
1074 Displays various data about InnoDB's last foreign key error. Data
1075 source: "STATUS_VARIABLES".
1076
1077 innodb_locks
1078 Displays InnoDB locks. Data source: "INNODB_LOCKS".
1079
1080 innodb_transactions
1081 Displays data about InnoDB's current transactions. Data source:
1082 "INNODB_TRANSACTIONS".
1083
1084 insert_buffers
1085 Displays data about InnoDB's insert buffer. Data source: "STA‐
1086 TUS_VARIABLES".
1087
1088 io_threads
1089 Displays data about InnoDB's I/O threads. Data source:
1090 "IO_THREADS".
1091
1092 log_statistics
1093 Displays data about InnoDB's logging system. Data source: "STA‐
1094 TUS_VARIABLES".
1095
1096 master_status
1097 Displays replication master status. Data source: "STATUS_VARI‐
1098 ABLES".
1099
1100 open_tables
1101 Displays open tables. Data source: "OPEN_TABLES".
1102
1103 page_statistics
1104 Displays InnoDB page statistics. Data source: "STATUS_VARIABLES".
1105
1106 pending_io
1107 Displays InnoDB pending I/O operations. Data source: "STATUS_VARI‐
1108 ABLES".
1109
1110 processlist
1111 Displays current MySQL processes (threads/connections). Data
1112 source: "PROCESSLIST".
1113
1114 q_header
1115 Displays various status values. Data source: "STATUS_VARIABLES".
1116
1117 row_operation_misc
1118 Displays data about InnoDB's row operations. Data source: "STA‐
1119 TUS_VARIABLES".
1120
1121 row_operations
1122 Displays data about InnoDB's row operations. Data source: "STA‐
1123 TUS_VARIABLES".
1124
1125 semaphores
1126 Displays data about InnoDB's semaphores and mutexes. Data source:
1127 "STATUS_VARIABLES".
1128
1129 slave_io_status
1130 Displays data about the slave I/O thread. Data source: "STA‐
1131 TUS_VARIABLES".
1132
1133 slave_sql_status
1134 Displays data about the slave SQL thread. Data source: "STA‐
1135 TUS_VARIABLES".
1136
1137 t_header
1138 Displays various InnoDB status values. Data source: "STATUS_VARI‐
1139 ABLES".
1140
1141 var_status
1142 Displays user-configurable data. Data source: "STATUS_VARIABLES".
1143
1144 wait_array
1145 Displays data about InnoDB's OS wait array. Data source:
1146 "OS_WAIT_ARRAY".
1147
1148 COLUMNS
1149
1150 Columns belong to tables. You can choose a table's columns by pressing
1151 the '^' key, which starts the "TABLE EDITOR" and lets you choose and
1152 edit columns. Pressing 'e' from within the table editor lets you edit
1153 the column's properties:
1154
1155 · hdr: a column header. This appears in the first row of the table.
1156
1157 · just: justification. '-' means left-justified and '' means
1158 right-justified, just as with printf formatting codes (not a coin‐
1159 cidence).
1160
1161 · dec: whether to further align the column on the decimal point.
1162
1163 · num: whether the column is numeric. This affects how values are
1164 sorted (lexically or numerically).
1165
1166 · label: a small note about the column, which appears in dialogs that
1167 help the user choose columns.
1168
1169 · src: an expression that innotop uses to extract the column's data
1170 from its source (see "DATA SOURCES"). See "EXPRESSIONS" for more
1171 on expressions.
1172
1173 · minw: specifies a minimum display width. This helps stabilize the
1174 display, which makes it easier to read if the data is changing fre‐
1175 quently.
1176
1177 · maxw: similar to minw.
1178
1179 · trans: a list of column transformations. See "TRANSFORMATIONS".
1180
1181 · agg: an aggregate function. See "GROUPING". The default is
1182 "first".
1183
1184 · aggonly: controls whether the column only shows when grouping is
1185 enabled on the table (see "GROUPING"). By default, this is dis‐
1186 abled. This means columns will always be shown by default, whether
1187 grouping is enabled or not. If a column's aggonly is set true, the
1188 column will appear when you toggle grouping on the table. Several
1189 columns are set this way, such as the count column on "processlist"
1190 and "innodb_transactions", so you don't see a count when the group‐
1191 ing isn't enabled, but you do when it is.
1192
1193 FILTERS
1194
1195 Filters remove rows from the display. They behave much like a WHERE
1196 clause in SQL. innotop has several built-in filters, which remove
1197 irrelevant information like inactive queries, but you can define your
1198 own as well. innotop also lets you create quick-filters, which do not
1199 get saved to the configuration file, and are just an easy way to
1200 quickly view only some rows.
1201
1202 You can enable or disable a filter on any table. Press the '%' key
1203 (mnemonic: % looks kind of like a line being filtered between two cir‐
1204 cles) and choose which table you want to filter, if asked. You'll then
1205 see a list of possible filters and a list of filters currently enabled
1206 for that table. Type the names of filters you want to apply and press
1207 Enter.
1208
1209 USER-DEFINED FILTERS
1210
1211 If you type a name that doesn't exist, innotop will prompt you to cre‐
1212 ate the filter. Filters are easy to create if you know Perl, and not
1213 hard if you don't. What you're doing is creating a subroutine that
1214 returns true if the row should be displayed. The row is a hash refer‐
1215 ence passed to your subroutine as $set.
1216
1217 For example, imagine you want to filter the processlist table so you
1218 only see queries that have been running more than five minutes. Type a
1219 new name for your filter, and when prompted for the subroutine body,
1220 press TAB to initiate your terminal's auto-completion. You'll see the
1221 names of the columns in the "processlist" table (innotop generally
1222 tries to help you with auto-completion lists). You want to filter on
1223 the 'time' column. Type the text "$set->{time} > 300" to return true
1224 when the query is more than five minutes old. That's all you need to
1225 do.
1226
1227 In other words, the code you're typing is surrounded by an implicit
1228 context, which looks like this:
1229
1230 sub filter {
1231 my ( $set ) = @_;
1232 # YOUR CODE HERE
1233 }
1234
1235 If your filter doesn't work, or if something else suddenly behaves dif‐
1236 ferently, you might have made an error in your filter, and innotop is
1237 silently catching the error. Try enabling "debug" to make innotop
1238 throw an error instead.
1239
1240 QUICK-FILTERS
1241
1242 innotop's quick-filters are a shortcut to create a temporary filter
1243 that doesn't persist when you restart innotop. To create a quick-fil‐
1244 ter, press the '/' key. innotop will prompt you for the column name
1245 and filter text. Again, you can use auto-completion on column names.
1246 The filter text can be just the text you want to "search for." For
1247 example, to filter the "processlist" table on queries that refer to the
1248 products table, type '/' and then 'info product'.
1249
1250 The filter text can actually be any Perl regular expression, but of
1251 course a literal string like 'product' works fine as a regular expres‐
1252 sion.
1253
1254 Behind the scenes innotop compiles the quick-filter into a specially
1255 tagged filter that is otherwise like any other filter. It just isn't
1256 saved to the configuration file.
1257
1258 To clear quick-filters, press the '\' key and innotop will clear them
1259 all at once.
1260
1261 SORTING
1262
1263 innotop has sensible built-in defaults to sort the most important rows
1264 to the top of the table. Like anything else in innotop, you can cus‐
1265 tomize how any table is sorted.
1266
1267 To start the sort dialog, start the "TABLE EDITOR" with the '^' key,
1268 choose a table if necessary, and press the 's' key. You'll see a list
1269 of columns you can use in the sort expression and the current sort
1270 expression, if any. Enter a list of columns by which you want to sort
1271 and press Enter. If you want to reverse sort, prefix the column name
1272 with a minus sign. For example, if you want to sort by column a
1273 ascending, then column b descending, type 'a -b'. You can also explic‐
1274 itly add a + in front of columns you want to sort ascending, but it's
1275 not required.
1276
1277 Some modes have keys mapped to open this dialog directly, and to
1278 quickly reverse sort direction. Press '?' as usual to see which keys
1279 are mapped in any mode.
1280
1281 GROUPING
1282
1283 innotop can group, or aggregate, rows together (I use the terms inter‐
1284 changeably). This is quite similar to an SQL GROUP BY clause. You can
1285 specify to group on certain columns, or if you don't specify any, the
1286 entire set of rows is treated as one group. This is quite like SQL so
1287 far, but unlike SQL, you can also select un-grouped columns. innotop
1288 actually aggregates every column. If you don't explicitly specify a
1289 grouping function, the default is 'first'. This is basically a conve‐
1290 nience so you don't have to specify an aggregate function for every
1291 column you want in the result.
1292
1293 You can quickly toggle grouping on a table with the '=' key, which tog‐
1294 gles its aggregate property. This property doesn't persist to the con‐
1295 fig file.
1296
1297 The columns by which the table is grouped are specified in its group_by
1298 property. When you turn grouping on, innotop places the group_by col‐
1299 umns at the far left of the table, even if they're not supposed to be
1300 visible. The rest of the visible columns appear in order after them.
1301
1302 Two tables have default group_by lists and a count column built in:
1303 "processlist" and "innodb_transactions". The grouping is by connection
1304 and status, so you can quickly see how many queries or transactions are
1305 in a given status on each server you're monitoring. The time columns
1306 are aggregated as a sum; other columns are left at the default 'first'
1307 aggregation.
1308
1309 By default, the table shown in "S: Variables & Status" mode also uses
1310 grouping so you can monitor variables and status across many servers.
1311 The default aggregation function in this mode is 'avg'.
1312
1313 Valid grouping functions are defined in the %agg_funcs hash. They
1314 include
1315
1316 first
1317 Returns the first element in the group.
1318
1319 count
1320 Returns the number of elements in the group, including undefined
1321 elements, much like SQL's COUNT(*).
1322
1323 avg Returns the average of defined elements in the group.
1324
1325 sum Returns the sum of elements in the group.
1326
1327 Here's an example of grouping at work. Suppose you have a very busy
1328 server with hundreds of open connections, and you want to see how many
1329 connections are in what status. Using the built-in grouping rules, you
1330 can press 'Q' to enter "Q: Query List" mode. Press '=' to toggle
1331 grouping (if necessary, select the "processlist" table when prompted).
1332
1333 Your display might now look like the following:
1334
1335 Query List (? for help) localhost, 32:33, 0.11 QPS, 1 thd, 5.0.38-log
1336
1337 CXN Cmd Cnt ID User Host Time Query
1338 localhost Query 49 12933 webusr localhost 19:38 SELECT * FROM
1339 localhost Sending Da 23 2383 webusr localhost 12:43 SELECT col1,
1340 localhost Sleep 120 140 webusr localhost 5:18:12
1341 localhost Statistics 12 19213 webusr localhost 01:19 SELECT * FROM
1342
1343 That's actually quite a worrisome picture. You've got a lot of idle
1344 connections (Sleep), and some connections executing queries (Query and
1345 Sending Data). That's okay, but you also have a lot in Statistics sta‐
1346 tus, collectively spending over a minute. That means the query opti‐
1347 mizer is having a really hard time optimizing your statements. Some‐
1348 thing is wrong; it should normally take milliseconds to optimize
1349 queries. You might not have seen this pattern if you didn't look at
1350 your connections in aggregate. (This is a made-up example, but it can
1351 happen in real life).
1352
1353 PIVOTING
1354
1355 innotop can pivot a table for more compact display, similar to a Pivot
1356 Table in a spreadsheet (also known as a crosstab). Pivoting a table
1357 makes columns into rows. Assume you start with this table:
1358
1359 foo bar
1360 === ===
1361 1 3
1362 2 4
1363
1364 After pivoting, the table will look like this:
1365
1366 name set0 set1
1367 ==== ==== ====
1368 foo 1 2
1369 bar 3 4
1370
1371 To get reasonable results, you might need to group as well as pivoting.
1372 innotop currently does this for "S: Variables & Status" mode.
1373
1374 COLORS
1375
1376 By default, innotop highlights rows with color so you can see at a
1377 glance which rows are more important. You can customize the coloriza‐
1378 tion rules and add your own to any table. Open the table editor with
1379 the '^' key, choose a table if needed, and press 'o' to open the color
1380 editor dialog.
1381
1382 The color editor dialog displays the rules applied to the table, in the
1383 order they are evaluated. Each row is evaluated against each rule to
1384 see if the rule matches the row; if it does, the row gets the specified
1385 color, and no further rules are evaluated. The rules look like the
1386 following:
1387
1388 state eq Locked black on_red
1389 cmd eq Sleep white
1390 user eq system user white
1391 cmd eq Connect white
1392 cmd eq Binlog Dump white
1393 time > 600 red
1394 time > 120 yellow
1395 time > 60 green
1396 time > 30 cyan
1397
1398 This is the default rule set for the "processlist" table. In order of
1399 priority, these rules make locked queries black on a red background,
1400 "gray out" connections from replication and sleeping queries, and make
1401 queries turn from cyan to red as they run longer.
1402
1403 (For some reason, the ANSI color code "white" is actually a light gray.
1404 Your terminal's display may vary; experiment to find colors you like).
1405
1406 You can use keystrokes to move the rules up and down, which re-orders
1407 their priority. You can also delete rules and add new ones. If you
1408 add a new rule, innotop prompts you for the column, an operator for the
1409 comparison, a value against which to compare the column, and a color to
1410 assign if the rule matches. There is auto-completion and prompting at
1411 each step.
1412
1413 The value in the third step needs to be correctly quoted. innotop does
1414 not try to quote the value because it doesn't know whether it should
1415 treat the value as a string or a number. If you want to compare the
1416 column against a string, as for example in the first rule above, you
1417 should enter 'Locked' surrounded by quotes. If you get an error mes‐
1418 sage about a bareword, you probably should have quoted something.
1419
1420 EXPRESSIONS
1421
1422 Expressions are at the core of how innotop works, and are what enables
1423 you to extend innotop as you wish. Recall the table lifecycle
1424 explained in "TABLES". Expressions are used in the earliest step,
1425 where it extracts values from a data source to form rows.
1426
1427 It does this by calling a subroutine for each column, passing it the
1428 source data set, a set of current values, and a set of previous values.
1429 These are all needed so the subroutine can calculate things like the
1430 difference between this tick and the previous tick.
1431
1432 The subroutines that extract the data from the set are compiled from
1433 expressions. This gives significantly more power than just naming the
1434 values to fill the columns, because it allows the column's value to be
1435 calculated from whatever data is necessary, but avoids the need to
1436 write complicated and lengthy Perl code.
1437
1438 innotop begins with a string of text that can look as simple as a
1439 value's name or as complicated as a full-fledged Perl expression. It
1440 looks at each 'bareword' token in the string and decides whether it's
1441 supposed to be a key into the $set hash. A bareword is an unquoted
1442 value that isn't already surrounded by code-ish things like dollar
1443 signs or curly brackets. If innotop decides that the bareword isn't a
1444 function or other valid Perl code, it converts it into a hash access.
1445 After the whole string is processed, innotop compiles a subroutine,
1446 like this:
1447
1448 sub compute_column_value {
1449 my ( $set, $cur, $pre ) = @_;
1450 my $val = # EXPANDED STRING GOES HERE
1451 return $val;
1452 }
1453
1454 Here's a concrete example, taken from the header table "q_header" in
1455 "Q: Query List" mode. This expression calculates the qps, or Queries
1456 Per Second, column's values, from the values returned by SHOW STATUS:
1457
1458 Questions/Uptime_hires
1459
1460 innotop decides both words are barewords, and transforms this expres‐
1461 sion into the following Perl code:
1462
1463 $set->{Questions}/$set->{Uptime_hires}
1464
1465 When surrounded by the rest of the subroutine's code, this is exe‐
1466 cutable Perl that calculates a high-resolution queries-per-second
1467 value.
1468
1469 The arguments to the subroutine are named $set, $cur, and $pre. In
1470 most cases, $set and $cur will be the same values. However, if "sta‐
1471 tus_inc" is set, $cur will not be the same as $set, because $set will
1472 already contain values that are the incremental difference between $cur
1473 and $pre.
1474
1475 Every column in innotop is computed by subroutines compiled in the same
1476 fashion. There is no difference between innotop's built-in columns and
1477 user-defined columns. This keeps things consistent and predictable.
1478
1479 TRANSFORMATIONS
1480
1481 Transformations change how a value is rendered. For example, they can
1482 take a number of seconds and display it in H:M:S format. The following
1483 transformations are defined:
1484
1485 commify
1486 Adds commas to large numbers every three decimal places.
1487
1488 dulint_to_int
1489 Accepts two unsigned integers and converts them into a single long‐
1490 long. This is useful for certain operations with InnoDB, which
1491 uses two integers as transaction identifiers, for example.
1492
1493 no_ctrl_char
1494 Removes quoted control characters from the value. This is affected
1495 by the "charset" configuration variable.
1496
1497 This transformation only operates within quoted strings, for exam‐
1498 ple, values to a SET clause in an UPDATE statement. It will not
1499 alter the UPDATE statement, but will collapse the quoted string to
1500 [BINARY] or [TEXT], depending on the charset.
1501
1502 percent
1503 Converts a number to a percentage by multiplying it by two, format‐
1504 ting it with "num_digits" digits after the decimal point, and
1505 optionally adding a percent sign (see "show_percent").
1506
1507 secs_to_time
1508 Formats a number of seconds as time in days+hours:minutes:seconds
1509 format.
1510
1511 set_precision
1512 Formats numbers with "num_digits" number of digits after the deci‐
1513 mal point.
1514
1515 shorten
1516 Formats a number as a unit of 1024 (k/M/G/T) and with "num_digits"
1517 number of digits after the decimal point.
1518
1519 TABLE EDITOR
1520
1521 The innotop table editor lets you customize tables with keystrokes.
1522 You start the table editor with the '^' key. If there's more than one
1523 table on the screen, it will prompt you to choose one of them. Once
1524 you do, innotop will show you something like this:
1525
1526 Editing table definition for Buffer Pool. Press ? for help, q to quit.
1527
1528 name hdr label src
1529 cxn CXN Connection from which cxn
1530 buf_pool_size Size Buffer pool size IB_bp_buf_poo
1531 buf_free Free Bufs Buffers free in the b IB_bp_buf_fre
1532 pages_total Pages Pages total IB_bp_pages_t
1533 pages_modified Dirty Pages Pages modified (dirty IB_bp_pages_m
1534 buf_pool_hit_rate Hit Rate Buffer pool hit rate IB_bp_buf_poo
1535 total_mem_alloc Memory Total memory allocate IB_bp_total_m
1536 add_pool_alloc Add'l Pool Additonal pool alloca IB_bp_add_poo
1537
1538 The first line shows which table you're editing, and reminds you again
1539 to press '?' for a list of key mappings. The rest is a tabular repre‐
1540 sentation of the table's columns, because that's likely what you're
1541 trying to edit. However, you can edit more than just the table's col‐
1542 umns; this screen can start the filter editor, color rule editor, and
1543 more.
1544
1545 Each row in the display shows a single column in the table you're edit‐
1546 ing, along with a couple of its properties such as its header and
1547 source expression (see "EXPRESSIONS").
1548
1549 The key mappings are Vim-style, as in many other places. Pressing 'j'
1550 and 'k' moves the highlight up or down. You can then (d)elete or
1551 (e)dit the highlighted column. You can also (a)dd a column to the ta‐
1552 ble. This actually just activates one of the columns already defined
1553 for the table; it prompts you to choose from among the columns avail‐
1554 able but not currently displayed. Finally, you can re-order the col‐
1555 umns with the '+' and '-' keys.
1556
1557 You can do more than just edit the columns with the table editor, you
1558 can also edit other properties, such as the table's sort expression and
1559 group-by expression. Press '?' to see the full list, of course.
1560
1561 If you want to really customize and create your own column, as opposed
1562 to just activating a built-in one that's not currently displayed, press
1563 the (n)ew key, and innotop will prompt you for the information it
1564 needs:
1565
1566 · The column name: this needs to be a word without any funny charac‐
1567 ters, e.g. just letters, numbers and underscores.
1568
1569 · The column header: this is the label that appears at the top of the
1570 column, in the table header. This can have spaces and funny char‐
1571 acters, but be careful not to make it too wide and waste space
1572 on-screen.
1573
1574 · The column's data source: this is an expression that determines
1575 what data from the source (see "TABLES") innotop will put into the
1576 column. This can just be the name of an item in the source, or it
1577 can be a more complex expression, as described in "EXPRESSIONS".
1578
1579 Once you've entered the required data, your table has a new column.
1580 There is no difference between this column and the built-in ones; it
1581 can have all the same properties and behaviors. innotop will write the
1582 column's definition to the configuration file, so it will persist
1583 across sessions.
1584
1585 Here's an example: suppose you want to track how many times your slaves
1586 have retried transactions. According to the MySQL manual, the
1587 Slave_retried_transactions status variable gives you that data: "The
1588 total number of times since startup that the replication slave SQL
1589 thread has retried transactions. This variable was added in version
1590 5.0.4." This is appropriate to add to the "slave_sql_status" table.
1591
1592 To add the column, switch to the replication-monitoring mode with the
1593 'M' key, and press the '^' key to start the table editor. When
1594 prompted, choose slave_sql_status as the table, then press 'n' to cre‐
1595 ate the column. Type 'retries' as the column name, 'Retries' as the
1596 column header, and 'Slave_retried_transactions' as the source. Now the
1597 column is created, and you see the table editor screen again. Press
1598 'q' to exit the table editor, and you'll see your column at the end of
1599 the table.
1600
1602 Variable sets are used in "S: Variables & Status" mode to define more
1603 easily what variables you want to monitor. Behind the scenes they are
1604 compiled to a list of expressions, and then into a column list so they
1605 can be treated just like columns in any other table, in terms of data
1606 extraction and transformations. However, you're protected from the
1607 tedious details by a syntax that ought to feel very natural to you: a
1608 SQL SELECT list.
1609
1610 The data source for variable sets, and indeed the entire S mode, is the
1611 combination of SHOW STATUS, SHOW VARIABLES, and SHOW INNODB STATUS.
1612 Imagine that you had a huge table with one column per variable returned
1613 from those statements. That's the data source for variable sets. You
1614 can now query this data source just like you'd expect. For example:
1615
1616 Questions, Uptime, Questions/Uptime as QPS
1617
1618 Behind the scenes innotop will split that variable set into three
1619 expressions, compile them and turn them into a table definition, then
1620 extract as usual. This becomes a "variable set," or a "list of vari‐
1621 ables you want to monitor."
1622
1623 innotop lets you name and save your variable sets, and writes them to
1624 the configuration file. You can choose which variable set you want to
1625 see with the 'c' key, or activate the next and previous sets with the
1626 '>' and '<' keys. There are many built-in variable sets as well, which
1627 should give you a good start for creating your own. Press 'e' to edit
1628 the current variable set, or just to see how it's defined. To create a
1629 new one, just press 'c' and type its name.
1630
1631 You may want to use some of the functions listed in "TRANSFORMATIONS"
1632 to help format the results. In particular, "set_precision" is often
1633 useful to limit the number of digits you see. Extending the above
1634 example, here's how:
1635
1636 Questions, Uptime, set_precision(Questions/Uptime) as QPS
1637
1638 Actually, this still needs a little more work. If your "interval" is
1639 less than one second, you might be dividing by zero because Uptime is
1640 incremental in this mode by default. Instead, use Uptime_hires:
1641
1642 Questions, Uptime, set_precision(Questions/Uptime_hires) as QPS
1643
1644 This example is simple, but it shows how easy it is to choose which
1645 variables you want to monitor.
1646
1648 innotop has a simple but powerful plugin mechanism by which you can
1649 extend or modify its existing functionality, and add new functionality.
1650 innotop's plugin functionality is event-based: plugins register them‐
1651 selves to be called when events happen. They then have a chance to
1652 influence the event.
1653
1654 An innotop plugin is a Perl module placed in innotop's "plugin_dir"
1655 directory. On UNIX systems, you can place a symbolic link to the mod‐
1656 ule instead of putting the actual file there. innotop automatically
1657 discovers the file. If there is a corresponding entry in the "plugins"
1658 configuration file section, innotop loads and activates the plugin.
1659
1660 The module must conform to innotop's plugin interface. Additionally,
1661 the source code of the module must be written in such a way that inno‐
1662 top can inspect the file and determine the package name and descrip‐
1663 tion.
1664
1665 Package Source Convention
1666
1667 innotop inspects the plugin module's source to determine the Perl pack‐
1668 age name. It looks for a line of the form "package Foo;" and if found,
1669 considers the plugin's package name to be Foo. Of course the package
1670 name can be a valid Perl package name, with double semicolons and so
1671 on.
1672
1673 It also looks for a description in the source code, to make the plugin
1674 editor more human-friendly. The description is a comment line of the
1675 form "# description: Foo", where "Foo" is the text innotop will con‐
1676 sider to be the plugin's description.
1677
1678 Plugin Interface
1679
1680 The innotop plugin interface is quite simple: innotop expects the plug‐
1681 in to be an object-oriented module it can call certain methods on. The
1682 methods are
1683
1684 new(%variables)
1685 This is the plugin's constructor. It is passed a hash of innotop's
1686 variables, which it can manipulate (see "Plugin Variables"). It
1687 must return a reference to the newly created plugin object.
1688
1689 At construction time, innotop has only loaded the general configu‐
1690 ration and created the default built-in variables with their
1691 default contents (which is quite a lot). Therefore, the state of
1692 the program is exactly as in the innotop source code, plus the con‐
1693 figuration variables from the "general" section in the config file.
1694
1695 If your plugin manipulates the variables, it is changing global
1696 data, which is shared by innotop and all plugins. Plugins are
1697 loaded in the order they're listed in the config file. Your plugin
1698 may load before or after another plugin, so there is a potential
1699 for conflict or interaction between plugins if they modify data
1700 other plugins use or modify.
1701
1702 register_for_events()
1703 This method must return a list of events in which the plugin is
1704 interested, if any. See "Plugin Events" for the defined events.
1705 If the plugin returns an event that's not defined, the event is
1706 ignored.
1707
1708 event handlers
1709 The plugin must implement a method named the same as each event for
1710 which it has registered. In other words, if the plugin returns
1711 qw(foo bar) from register_for_events(), it must have foo() and
1712 bar() methods. These methods are callbacks for the events. See
1713 "Plugin Events" for more details about each event.
1714
1715 Plugin Variables
1716
1717 The plugin's constructor is passed a hash of innotop's variables, which
1718 it can manipulate. It is probably a good idea if the plugin object
1719 saves a copy of it for later use. The variables are defined in the
1720 innotop variable %pluggable_vars, and are as follows:
1721
1722 action_for
1723 A hashref of key mappings. These are innotop's global hot-keys.
1724
1725 agg_funcs
1726 A hashref of functions that can be used for grouping. See "GROUP‐
1727 ING".
1728
1729 config
1730 The global configuration hash.
1731
1732 connections
1733 A hashref of connection specifications. These are just specifica‐
1734 tions of how to connect to a server.
1735
1736 dbhs
1737 A hashref of innotop's database connections. These are actual DBI
1738 connection objects.
1739
1740 filters
1741 A hashref of filters applied to table rows. See "FILTERS" for
1742 more.
1743
1744 modes
1745 A hashref of modes. See "MODES" for more.
1746
1747 server_groups
1748 A hashref of server groups. See "SERVER GROUPS".
1749
1750 tbl_meta
1751 A hashref of innotop's table meta-data, with one entry per table
1752 (see "TABLES" for more information).
1753
1754 trans_funcs
1755 A hashref of transformation functions. See "TRANSFORMATIONS".
1756
1757 var_sets
1758 A hashref of variable sets. See "VARIABLE SETS".
1759
1760 Plugin Events
1761
1762 Each event is defined somewhere in the innotop source code. When inno‐
1763 top runs that code, it executes the callback function for each plugin
1764 that expressed its interest in the event. innotop passes some data for
1765 each event. The events are defined in the %event_listener_for vari‐
1766 able, and are as follows:
1767
1768 extract_values($set, $cur, $pre, $tbl)
1769 This event occurs inside the function that extracts values from a
1770 data source. The arguments are the set of values, the current val‐
1771 ues, the previous values, and the table name.
1772
1773 set_to_tbl
1774 Events are defined at many places in this subroutine, which is
1775 responsible for turning an arrayref of hashrefs into an arrayref of
1776 lines that can be printed to the screen. The events all pass the
1777 same data: an arrayref of rows and the name of the table being cre‐
1778 ated. The events are set_to_tbl_pre_filter,
1779 set_to_tbl_pre_sort,set_to_tbl_pre_group, set_to_tbl_pre_colorize,
1780 set_to_tbl_pre_transform, set_to_tbl_pre_pivot, set_to_tbl_pre_cre‐
1781 ate, set_to_tbl_post_create.
1782
1783 draw_screen($lines)
1784 This event occurs inside the subroutine that prints the lines to
1785 the screen. $lines is an arrayref of strings.
1786
1787 Simple Plugin Example
1788
1789 The easiest way to explain the plugin functionality is probably with a
1790 simple example. The following module adds a column to the beginning of
1791 every table and sets its value to 1.
1792
1793 use strict;
1794 use warnings FATAL => 'all';
1795
1796 package Innotop::Plugin::Example;
1797 # description: Adds an 'example' column to every table
1798
1799 sub new {
1800 my ( $class, %vars ) = @_;
1801 # Store reference to innotop's variables in $self
1802 my $self = bless { %vars }, $class;
1803
1804 # Design the example column
1805 my $col = {
1806 hdr => 'Example',
1807 just => '',
1808 dec => 0,
1809 num => 1,
1810 label => 'Example',
1811 src => 'example', # Get data from this column in the data source
1812 tbl => '',
1813 trans => [],
1814 };
1815
1816 # Add the column to every table.
1817 my $tbl_meta = $vars{tbl_meta};
1818 foreach my $tbl ( values %$tbl_meta ) {
1819 # Add the column to the list of defined columns
1820 $tbl->{cols}->{example} = $col;
1821 # Add the column to the list of visible columns
1822 unshift @{$tbl->{visible}}, 'example';
1823 }
1824
1825 # Be sure to return a reference to the object.
1826 return $self;
1827 }
1828
1829 # I'd like to be called when a data set is being rendered into a table, please.
1830 sub register_for_events {
1831 my ( $self ) = @_;
1832 return qw(set_to_tbl_pre_filter);
1833 }
1834
1835 # This method will be called when the event fires.
1836 sub set_to_tbl_pre_filter {
1837 my ( $self, $rows, $tbl ) = @_;
1838 # Set the example column's data source to the value 1.
1839 foreach my $row ( @$rows ) {
1840 $row->{example} = 1;
1841 }
1842 }
1843
1844 1;
1845
1846 Plugin Editor
1847
1848 The plugin editor lets you view the plugins innotop discovered and
1849 activate or deactivate them. Start the editor by pressing $ to start
1850 the configuration editor from any mode. Press the 'p' key to start the
1851 plugin editor. You'll see a list of plugins innotop discovered. You
1852 can use the 'j' and 'k' keys to move the highlight to the desired one,
1853 then press the * key to toggle it active or inactive. Exit the editor
1854 and restart innotop for the changes to take effect.
1855
1857 innotop uses a limited set of SQL statements to retrieve data from
1858 MySQL for display. The statements are customized depending on the
1859 server version against which they are executed; for example, on MySQL 5
1860 and newer, INNODB_STATUS executes "SHOW ENGINE INNODB STATUS", while on
1861 earlier versions it executes "SHOW INNODB STATUS". The statements are
1862 as follows:
1863
1864 Statement SQL executed
1865 =================== ===============================
1866 INNODB_STATUS SHOW [ENGINE] INNODB STATUS
1867 KILL_CONNECTION KILL
1868 KILL_QUERY KILL QUERY
1869 OPEN_TABLES SHOW OPEN TABLES
1870 PROCESSLIST SHOW FULL PROCESSLIST
1871 SHOW_MASTER_LOGS SHOW MASTER LOGS
1872 SHOW_MASTER_STATUS SHOW MASTER STATUS
1873 SHOW_SLAVE_STATUS SHOW SLAVE STATUS
1874 SHOW_STATUS SHOW [GLOBAL] STATUS
1875 SHOW_VARIABLES SHOW [GLOBAL] VARIABLES
1876
1878 Each time innotop extracts values to create a table (see "EXPRESSIONS"
1879 and "TABLES"), it does so from a particular data source. Largely
1880 because of the complex data extracted from SHOW INNODB STATUS, this is
1881 slightly messy. SHOW INNODB STATUS contains a mixture of single values
1882 and repeated values that form nested data sets.
1883
1884 Whenever innotop fetches data from MySQL, it adds two extra bits to
1885 each set: cxn and Uptime_hires. cxn is the name of the connection from
1886 which the data came. Uptime_hires is a high-resolution version of the
1887 server's Uptime status variable, which is important if your "interval"
1888 setting is sub-second.
1889
1890 Here are the kinds of data sources from which data is extracted:
1891
1892 STATUS_VARIABLES
1893 This is the broadest category, into which the most kinds of data
1894 fall. It begins with the combination of SHOW STATUS and SHOW VARI‐
1895 ABLES, but other sources may be included as needed, for example,
1896 SHOW MASTER STATUS and SHOW SLAVE STATUS, as well as many of the
1897 non-repeated values from SHOW INNODB STATUS.
1898
1899 DEADLOCK_LOCKS
1900 This data is extracted from the transaction list in the LATEST
1901 DETECTED DEADLOCK section of SHOW INNODB STATUS. It is nested two
1902 levels deep: transactions, then locks.
1903
1904 DEADLOCK_TRANSACTIONS
1905 This data is from the transaction list in the LATEST DETECTED DEAD‐
1906 LOCK section of SHOW INNODB STATUS. It is nested one level deep.
1907
1908 EXPLAIN
1909 This data is from the result set returned by EXPLAIN.
1910
1911 INNODB_TRANSACTIONS
1912 This data is from the TRANSACTIONS section of SHOW INNODB STATUS.
1913
1914 IO_THREADS
1915 This data is from the list of threads in the the FILE I/O section
1916 of SHOW INNODB STATUS.
1917
1918 INNODB_LOCKS
1919 This data is from the TRANSACTIONS section of SHOW INNODB STATUS
1920 and is nested two levels deep.
1921
1922 OPEN_TABLES
1923 This data is from SHOW OPEN TABLES.
1924
1925 PROCESSLIST
1926 This data is from SHOW FULL PROCESSLIST.
1927
1928 OS_WAIT_ARRAY
1929 This data is from the SEMAPHORES section of SHOW INNODB STATUS and
1930 is nested one level deep. It comes from the lines that look like
1931 this:
1932
1933 --Thread 1568861104 has waited at btr0cur.c line 424 ....
1934
1936 · You must connect to MySQL as a user who has the SUPER privilege for
1937 many of the functions.
1938
1939 · If you don't have the SUPER privilege, you can still run some func‐
1940 tions, but you won't necessarily see all the same data.
1941
1942 · You need the PROCESS privilege to see the list of currently running
1943 queries in Q mode.
1944
1945 · You need special privileges to start and stop slave servers.
1946
1947 · You need appropriate privileges to create and drop the deadlock
1948 tables if needed (see "SERVER CONNECTIONS").
1949
1951 You need Perl to run innotop, of course. You also need a few Perl mod‐
1952 ules: DBI, DBD::mysql, Term::ReadKey, and Time::HiRes. These should
1953 be included with most Perl distributions, but in case they are not, I
1954 recommend using versions distributed with your operating system or Perl
1955 distribution, not from CPAN. Term::ReadKey in particular has been
1956 known to cause problems if installed from CPAN.
1957
1958 If you have Term::ANSIColor, innotop will use it to format headers more
1959 readably and compactly. (Under Microsoft Windows, you also need
1960 Win32::Console::ANSI for terminal formatting codes to be honored). If
1961 you install Term::ReadLine, preferably Term::ReadLine::Gnu, you'll get
1962 nice auto-completion support.
1963
1964 I run innotop on Gentoo GNU/Linux, Debian and Ubuntu, and I've had
1965 feedback from people successfully running it on Red Hat, CentOS,
1966 Solaris, and Mac OSX. I don't see any reason why it won't work on
1967 other UNIX-ish operating systems, but I don't know for sure. It also
1968 runs on Windows under ActivePerl without problem.
1969
1970 I use innotop on MySQL versions 3.23.58, 4.0.27, 4.1.0, 4.1.22, 5.0.26,
1971 5.1.15, and 5.2.3. If it doesn't run correctly for you, that is a bug
1972 and I hope you report it.
1973
1975 $HOMEDIR/.innotop is used to store configuration information. Files
1976 include the configuration file innotop.ini, the core_dump file which
1977 contains verbose error messages if "debug" is enabled, and the plugins/
1978 subdirectory.
1979
1981 tick
1982 A tick is a refresh event, when innotop re-fetches data from con‐
1983 nections and displays it.
1984
1986 I'm grateful to the following people for various reasons, and hope I
1987 haven't forgotten to include anyone:
1988
1989 Allen K. Smith, Aurimas Mikalauskas, Bartosz Fenski, Brian Miezejewski,
1990 Christian Hammers, Cyril Scetbon, Dane Miller, David Multer, Dr. Frank
1991 Ullrich, Giuseppe Maxia, Google.com Site Reliability Engineers, Jan
1992 Pieter Kunst, Jari Aalto, Jay Pipes, Jeremy Zawodny, Johan Idren, Kris‐
1993 tian Kohntopp, Lenz Grimmer, Maciej Dobrzanski, Michiel Betel, MySQL
1994 AB, Paul McCullagh, Sebastien Estienne, Sourceforge.net, Steven
1995 Kreuzer, The Gentoo MySQL Team, Trevor Price, Yaar Schnitman, and prob‐
1996 ably more people I've neglected to include.
1997
1998 (If I misspelled your name, it's probably because I'm afraid of putting
1999 international characters into this documentation; earlier versions of
2000 Perl might not be able to compile it then).
2001
2003 This program is copyright (c) 2006 Baron Schwartz. Feedback and
2004 improvements are welcome.
2005
2006 THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
2007 WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
2008 MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.
2009
2010 This program is free software; you can redistribute it and/or modify it
2011 under the terms of the GNU General Public License as published by the
2012 Free Software Foundation, version 2; OR the Perl Artistic License. On
2013 UNIX and similar systems, you can issue `man perlgpl' or `man per‐
2014 lartistic' to read these licenses.
2015
2016 You should have received a copy of the GNU General Public License along
2017 with this program; if not, write to the Free Software Foundation, Inc.,
2018 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
2019
2020 Execute innotop and press '!' to see this information at any time.
2021
2023 Baron Schwartz.
2024
2026 You can report bugs, ask for improvements, and get other help and sup‐
2027 port at <http://sourceforge.net/projects/innotop>. There are mailing
2028 lists, forums, a bug tracker, etc. Please use these instead of con‐
2029 tacting me directly, as it makes my job easier and benefits others if
2030 the discussions are permanent and public. Of course, if you need to
2031 contact me in private, please do.
2032
2033
2034
2035perl v5.8.8 2007-11-09 INNOTOP(1)