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