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

NAME

6       innotop - MySQL and InnoDB transaction/status monitor.
7

SYNOPSIS

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
21       To monitor a database on another system using a particular username and
22       password:
23
24        innotop -u <username> -p <password> -h <hostname>
25

DESCRIPTION

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
33       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

QUICK-START

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
44       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
50       After you've connected, innotop should show you something like the
51       following:
52
53        [RO] Query List (? for help) localhost, 01:11:19, 449.44 QPS, 14/7/163 con/run
54
55        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
58        CXN        Cmd    ID         User  Host      DB   Time   Query
59        localhost  Query  766446598  test  10.0.0.1  foo  00:02  INSERT INTO table (
60
61       (This sample is truncated at the right so it will fit on a terminal
62       when running 'man innotop')
63
64       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
70       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
76       To quit innotop, press the 'q' key.
77

OPTIONS

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
84       You can negate some options by prefixing the option name with --no.
85       For example, --noinc (or --no-inc) negates "--inc".
86
87       --color
88           Enable or disable terminal coloring.  Corresponds to the "color"
89           config file setting.
90
91       --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
96       --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
102       --delay
103           Specifies the amount of time to pause between ticks (refreshes).
104           Corresponds to the configuration option "interval".
105
106       --help
107           Print a summary of command-line usage and exit.
108
109       --host
110           Host to connect to.
111
112       --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
117       --mode
118           Specifies the mode in which innotop should start.  Corresponds to
119           the configuration option "mode".
120
121       --nonint
122           Enable non-interactive operation.  See "NON-INTERACTIVE OPERATION"
123           for more.
124
125       --password
126           Password to use for connection.
127
128       --port
129           Port to use for connection.
130
131       --skipcentral
132           Don't read the central configuration file.
133
134       --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
139       --user
140           User to use for connection.
141
142       --version
143           Output version information and exit.
144
145       --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

HOTKEYS

151       innotop is interactive, and you control it with key-presses.
152
153       ·   Uppercase keys switch between modes.
154
155       ·   Lowercase keys initiate some action within the current mode.
156
157       ·   Other keys do something special like change configuration or show
158           the innotop license.
159
160       Press '?' at any time to see the currently active keys and what they
161       do.
162

MODES

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
170       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
177       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
182           This mode contains the "buffer_pool", "page_statistics",
183           "insert_buffers", and "adaptive_hash_index" tables by default.
184
185       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
189            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
198           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
206           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
210           It's rather like running SHOW VARIABLES LIKE "prefix%" with memory
211           and nice formatting.
212
213           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
218       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
225           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
235           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
240           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
244           This mode displays the "deadlock_transactions" and "deadlock_locks"
245           tables by default.
246
247       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
252           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
259           This mode displays the "fk_error" table by default.
260
261       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
267       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
273       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
283           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
291             CREATE TABLE innodb_lock_monitor(a int) ENGINE=INNODB;
292
293           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
298           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
304           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
308            _________________________________ 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
316           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
322       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
329           This mode displays the "slave_sql_status", "slave_io_status", and
330           "master_status" tables by default.
331
332       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
339           This mode displays the "open_tables" mode by default.
340
341       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
350           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
355           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
359       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
365           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
371           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
378           This mode displays the "q_header" and "processlist" tables by
379           default.
380
381       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
387       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
392           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
401           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
406           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
411       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
415           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
420           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
425           This mode displays the "t_header" and "innodb_transactions" tables
426           by default.
427

INNOTOP STATUS

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
435   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
441       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
452       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
456   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
464       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
469       See "ERROR HANDLING" for more details about innotop's error handling.
470
471   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

SERVER ADMINISTRATION

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
485       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
493       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
501       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
509       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

SERVER CONNECTIONS

513       When you create a server connection using '@', innotop asks you for a
514       series of inputs, as follows:
515
516       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
520            DBI:mysql:;mysql_read_default_group=mysql;host=HOSTNAME
521
522           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
529           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
534       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
542       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
548           The username defaults to your login name on the system you're
549           running innotop on.
550
551       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
560       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

SERVER GROUPS

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
570       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
574       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

SWITCHING BETWEEN CONNECTIONS

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
585       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
589       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
595       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
600       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
604       When you monitor more than one connection, innotop's status bar
605       changes.  See "INNOTOP STATUS".
606

ERROR HANDLING

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
616       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
624       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
629       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

NON-INTERACTIVE OPERATION

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
646       ·   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
652       ·   innotop does not clear the screen after each tick.
653
654       ·   innotop does not persist any changes to the configuration file.
655
656       ·   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
662       ·   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
672       ·   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
676       ·   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
681       ·   innotop does not honor the "shorten" transformation, which normally
682           shortens some numbers to human-readable formats.
683
684       ·   innotop does not print a status line (see "INNOTOP STATUS").
685

CONFIGURING

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
691       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
695       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
701           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
711           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
715       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
720       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
726       k: Row-Coloring Rules
727           Starts the row-coloring rules editor on one of the displayed
728           table(s).  See "COLORS" for details.
729
730       p: Manage Plugins
731           Starts the plugin configuration editor.  See "PLUGINS" for details.
732
733       s: Server Groups
734           Lets you create and edit server groups.  See "SERVER GROUPS".
735
736       t: Choose Displayed Tables
737           Lets you choose which tables to display in this mode.  See "MODES"
738           and "TABLES".
739

CONFIGURATION FILE

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
750       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
757       A configuration file is read-only be default.  You can override that
758       with "--write".  See "readonly".
759
760       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
768       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
772       The following list describes each section of the configuration file and
773       the data it contains:
774
775       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
781           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
786           S_set
787               Specifies which set of variables to display in "S: Variables &
788               Status" mode.  See "VARIABLE SETS".
789
790           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
796           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
802               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
808           cmd_filter
809               This is the prefix that filters variables in "C: Command
810               Summary" mode.
811
812           color
813               Whether terminal coloring is permitted.
814
815           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
821           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
827           debugfile
828               A file to which innotop will write information when there is a
829               crash.  See "FILES".
830
831           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
837           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
846               I can no longer find or reproduce the situation where GLOBAL
847               wasn't allowed, but I know there was one.
848
849           graph_char
850               Defines the character to use when drawing graphs in "S:
851               Variables & Status" mode.
852
853           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
858           hide_hdr
859               Hides column headers globally.
860
861           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
867               This variable accepts fractions of a second.
868
869           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
874           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
881           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
887           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
892           readonly
893               Whether the configuration file is readonly.  This cannot be set
894               interactively.
895
896           show_cxn_errors
897               Makes innotop print connection errors to STDOUT.  See "ERROR
898               HANDLING".
899
900           show_cxn_errors_in_tbl
901               Makes innotop display connection errors as rows in the first
902               table on screen.  See "ERROR HANDLING".
903
904           show_percent
905               Adds a '%' character after the value returned by the "percent"
906               transformation.
907
908           show_statusbar
909               Controls whether to show the status bar in the display.  See
910               "INNOTOP STATUS".
911
912           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
918           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
922           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
930           timeformat
931               The C-style strftime()-compatible format for the timestamp line
932               to be printed in -n mode when -t is set.
933
934       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
939       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
943           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
949       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
953       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
958       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
967       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
971       server_groups
972           This section holds server groups.  Each line is in the format
973           name=connection_list.  See "SERVER GROUPS".
974
975       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
979       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
984       active_columns
985           This section holds table column lists.  Each line is in the format
986           tbl_name=column_list.  See "COLUMNS".
987
988       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
993       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
997       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
1002       colors
1003           This section defines colorization rules.  Each line is in the
1004           format tbl_name=property_list.  See "COLORS".
1005
1006       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
1010       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

CUSTOMIZING

1015       You can customize innotop a great deal.  For example, you can:
1016
1017       ·   Choose which tables to display, and in what order.
1018
1019       ·   Choose which columns are in those tables, and create new columns.
1020
1021       ·   Filter which rows display with built-in filters, user-defined
1022           filters, and quick-filters.
1023
1024       ·   Sort the rows to put important data first or group together related
1025           rows.
1026
1027       ·   Highlight rows with color.
1028
1029       ·   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
1034       ·   Design your own expressions to extract and combine data as you
1035           need.  This gives you unlimited flexibility.
1036
1037       All these and more are explained in the following sections.
1038
1039   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
1044       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
1053       You can choose which tables to show by pressing the '$' key.  See
1054       "MODES" and "TABLES".
1055
1056       The table life-cycle is as follows:
1057
1058       ·   Each table begins with a data source, which is an array of hashes.
1059           See below for details on data sources.
1060
1061       ·   Each element of the data source becomes a row in the final table.
1062
1063       ·   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
1071       ·   innotop filters the rows, removing those that don't need to be
1072           displayed.  See "FILTERS".
1073
1074       ·   innotop sorts the rows.  See "SORTING".
1075
1076       ·   innotop groups the rows together, if specified.  See "GROUPING".
1077
1078       ·   innotop colorizes the rows.  See "COLORS".
1079
1080       ·   innotop transforms the column values in each row.  See
1081           "TRANSFORMATIONS".
1082
1083       ·   innotop optionally pivots the rows (see "PIVOTING"), then filters
1084           and sorts them.
1085
1086       ·   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
1093       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
1102       Each built-in table is described below:
1103
1104       adaptive_hash_index
1105           Displays data about InnoDB's adaptive hash index.  Data source:
1106           "STATUS_VARIABLES".
1107
1108       buffer_pool
1109           Displays data about InnoDB's buffer pool.  Data source:
1110           "STATUS_VARIABLES".
1111
1112       cmd_summary
1113           Displays weighted status variables.  Data source:
1114           "STATUS_VARIABLES".
1115
1116       deadlock_locks
1117           Shows which locks were held and waited for by the last detected
1118           deadlock.  Data source: "DEADLOCK_LOCKS".
1119
1120       deadlock_transactions
1121           Shows transactions involved in the last detected deadlock.  Data
1122           source: "DEADLOCK_TRANSACTIONS".
1123
1124       explain
1125           Shows the output of EXPLAIN.  Data source: "EXPLAIN".
1126
1127       file_io_misc
1128           Displays data about InnoDB's file and I/O operations.  Data source:
1129           "STATUS_VARIABLES".
1130
1131       fk_error
1132           Displays various data about InnoDB's last foreign key error.  Data
1133           source: "STATUS_VARIABLES".
1134
1135       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
1140       index_statistics
1141           Displays data from the INDEX_STATISTICS table in Percona-enhanced
1142           servers.
1143
1144       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
1152       innodb_blocked_blocker
1153           Displays InnoDB locks and lock waits. Data source:
1154           "INNODB_BLOCKED_BLOCKER".
1155
1156       innodb_locks
1157           Displays InnoDB locks.  Data source: "INNODB_LOCKS".
1158
1159       innodb_transactions
1160           Displays data about InnoDB's current transactions.  Data source:
1161           "INNODB_TRANSACTIONS".
1162
1163       insert_buffers
1164           Displays data about InnoDB's insert buffer.  Data source:
1165           "STATUS_VARIABLES".
1166
1167       io_threads
1168           Displays data about InnoDB's I/O threads.  Data source:
1169           "IO_THREADS".
1170
1171       log_statistics
1172           Displays data about InnoDB's logging system.  Data source:
1173           "STATUS_VARIABLES".
1174
1175       master_status
1176           Displays replication master status.  Data source:
1177           "STATUS_VARIABLES".
1178
1179       open_tables
1180           Displays open tables.  Data source: "OPEN_TABLES".
1181
1182       page_statistics
1183           Displays InnoDB page statistics.  Data source: "STATUS_VARIABLES".
1184
1185       pending_io
1186           Displays InnoDB pending I/O operations.  Data source:
1187           "STATUS_VARIABLES".
1188
1189       processlist
1190           Displays current MySQL processes (threads/connections).  Data
1191           source: "PROCESSLIST".
1192
1193       q_header
1194           Displays various status values.  Data source: "STATUS_VARIABLES".
1195
1196       row_operation_misc
1197           Displays data about InnoDB's row operations.  Data source:
1198           "STATUS_VARIABLES".
1199
1200       row_operations
1201           Displays data about InnoDB's row operations.  Data source:
1202           "STATUS_VARIABLES".
1203
1204       semaphores
1205           Displays data about InnoDB's semaphores and mutexes.  Data source:
1206           "STATUS_VARIABLES".
1207
1208       slave_io_status
1209           Displays data about the slave I/O thread.  Data source:
1210           "STATUS_VARIABLES".
1211
1212       slave_sql_status
1213           Displays data about the slave SQL thread.  Data source:
1214           "STATUS_VARIABLES".
1215
1216       table_statistics
1217           Displays data from the TABLE_STATISTICS table in Percona-enhanced
1218           servers.
1219
1220       t_header
1221           Displays various InnoDB status values.  Data source:
1222           "STATUS_VARIABLES".
1223
1224       var_status
1225           Displays user-configurable data.  Data source: "STATUS_VARIABLES".
1226
1227       wait_array
1228           Displays data about InnoDB's OS wait array.  Data source:
1229           "OS_WAIT_ARRAY".
1230
1231   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
1237       ·   hdr: a column header.  This appears in the first row of the table.
1238
1239       ·   just: justification.  '-' means left-justified and '' means right-
1240           justified, just as with printf formatting codes (not a
1241           coincidence).
1242
1243       ·   dec: whether to further align the column on the decimal point.
1244
1245       ·   num: whether the column is numeric.  This affects how values are
1246           sorted (lexically or numerically).
1247
1248       ·   label: a small note about the column, which appears in dialogs that
1249           help the user choose columns.
1250
1251       ·   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
1255       ·   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
1259       ·   maxw: similar to minw.
1260
1261       ·   trans: a list of column transformations.  See "TRANSFORMATIONS".
1262
1263       ·   agg: an aggregate function.  See "GROUPING".  The default is
1264           "first".
1265
1266       ·   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
1275       ·   agghide: the reverse of aggonly.  The column is hidden when
1276           grouping is enabled.
1277
1278   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
1286       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
1293       USER-DEFINED FILTERS
1294
1295       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
1301       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
1311       In other words, the code you're typing is surrounded by an implicit
1312       context, which looks like this:
1313
1314        sub filter {
1315           my ( $set ) = @_;
1316           # YOUR CODE HERE
1317        }
1318
1319       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
1324       QUICK-FILTERS
1325
1326       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
1335        sub filter {
1336           my ( $set ) = @_;
1337           $set->{info} =~ m/product/;
1338        }
1339
1340       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
1344       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
1351        1. /
1352        2. cmd ^(?!Gandalf)
1353
1354       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
1358       To clear quick-filters, press the '\' key and innotop will clear them
1359       all at once.
1360
1361   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
1366       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
1376       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
1380   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
1391       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
1395       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
1401       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
1408       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
1412       Valid grouping functions are defined in the %agg_funcs hash.  They
1413       include
1414
1415       first
1416           Returns the first element in the group.
1417
1418       count
1419           Returns the number of elements in the group, including undefined
1420           elements, much like SQL's COUNT(*).
1421
1422       avg Returns the average of defined elements in the group.
1423
1424       sum Returns the sum of elements in the group.
1425
1426       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
1432       Your display might now look like the following:
1433
1434        Query List (? for help) localhost, 32:33, 0.11 QPS, 1 thd, 5.0.38-log
1435
1436        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
1442       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
1452   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
1457        foo bar
1458        === ===
1459        1   3
1460        2   4
1461
1462       After pivoting, the table will look like this:
1463
1464        name set0 set1
1465        ==== ==== ====
1466        foo  1    2
1467        bar  3    4
1468
1469       To get reasonable results, you might need to group as well as pivoting.
1470       innotop currently does this for "S: Variables & Status" mode.
1471
1472   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
1479       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
1485        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
1495       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
1500       (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
1503       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
1510       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
1517   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
1523       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
1528       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
1534       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
1544        sub compute_column_value {
1545           my ( $set, $cur, $pre ) = @_;
1546           my $val = # EXPANDED STRING GOES HERE
1547           return $val;
1548        }
1549
1550       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
1554        Queries/Uptime_hires
1555
1556       innotop decides both words are barewords, and transforms this
1557       expression into the following Perl code:
1558
1559        $set->{Queries}/$set->{Uptime_hires}
1560
1561       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
1565       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
1571       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
1575   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
1580       commify
1581           Adds commas to large numbers every three decimal places.
1582
1583       distill
1584           Distills SQL into verb-noun-noun format for quick comprehension.
1585
1586       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
1591       fuzzy_time
1592           Converts a number of seconds into a friendly, readable value like
1593           "1h35m".
1594
1595       no_ctrl_char
1596           Removes quoted control characters from the value.  This is affected
1597           by the "charset" configuration variable.
1598
1599           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
1604       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
1609       secs_to_time
1610           Formats a number of seconds as time in days+hours:minutes:seconds
1611           format.
1612
1613       set_precision
1614           Formats numbers with "num_digits" number of digits after the
1615           decimal point.
1616
1617       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
1621   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
1627        Editing table definition for Buffer Pool.  Press ? for help, q to quit.
1628
1629        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
1639       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
1646       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
1650       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
1658       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
1662       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
1667       ·   The column name: this needs to be a word without any funny
1668           characters, e.g. just letters, numbers and underscores.
1669
1670       ·   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
1675       ·   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
1680       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
1686       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
1693       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

VARIABLE SETS

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
1711       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
1717        Queries, Uptime, Queries/Uptime as QPS
1718
1719       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
1724       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
1732       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
1737        Queries, Uptime, set_precision(Queries/Uptime) as QPS
1738
1739       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
1743        Queries, Uptime, set_precision(Queries/Uptime_hires) as QPS
1744
1745       This example is simple, but it shows how easy it is to choose which
1746       variables you want to monitor.
1747

PLUGINS

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
1755       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
1762       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
1767   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
1774       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
1779   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
1784       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
1789           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
1796           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
1803       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
1809       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
1816   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
1822       action_for
1823           A hashref of key mappings.  These are innotop's global hot-keys.
1824
1825       agg_funcs
1826           A hashref of functions that can be used for grouping.  See
1827           "GROUPING".
1828
1829       config
1830           The global configuration hash.
1831
1832       connections
1833           A hashref of connection specifications.  These are just
1834           specifications of how to connect to a server.
1835
1836       dbhs
1837           A hashref of innotop's database connections.  These are actual DBI
1838           connection objects.
1839
1840       filters
1841           A hashref of filters applied to table rows.  See "FILTERS" for
1842           more.
1843
1844       modes
1845           A hashref of modes.  See "MODES" for more.
1846
1847       server_groups
1848           A hashref of server groups.  See "SERVER GROUPS".
1849
1850       tbl_meta
1851           A hashref of innotop's table meta-data, with one entry per table
1852           (see "TABLES" for more information).
1853
1854       trans_funcs
1855           A hashref of transformation functions.  See "TRANSFORMATIONS".
1856
1857       var_sets
1858           A hashref of variable sets.  See "VARIABLE SETS".
1859
1860   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
1867       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
1872       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
1882       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
1886   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
1893        use strict;
1894        use warnings FATAL => 'all';
1895
1896        package Innotop::Plugin::Example;
1897        # description: Adds an 'example' column to every table
1898
1899        sub new {
1900           my ( $class, %vars ) = @_;
1901           # Store reference to innotop's variables in $self
1902           my $self = bless { %vars }, $class;
1903
1904           # 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
1916           # 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
1925           # Be sure to return a reference to the object.
1926           return $self;
1927        }
1928
1929        # 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
1935        # 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
1944        1;
1945
1946   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

SQL STATEMENTS

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
1963        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

DATA SOURCES

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
1985       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
1991       Here are the kinds of data sources from which data is extracted:
1992
1993       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
2000       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
2005       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
2010       EXPLAIN
2011           This data is from the result set returned by EXPLAIN.
2012
2013       INNODB_BLOCKED_BLOCKER
2014           This data is from the INFORMATION_SCHEMA tables related to InnoDB
2015           locks and the processlist.
2016
2017       INNODB_TRANSACTIONS
2018           This data is from the TRANSACTIONS section of SHOW INNODB STATUS.
2019
2020       IO_THREADS
2021           This data is from the list of threads in the the FILE I/O section
2022           of SHOW INNODB STATUS.
2023
2024       INNODB_LOCKS
2025           This data is from the TRANSACTIONS section of SHOW INNODB STATUS
2026           and is nested two levels deep.
2027
2028       MASTER_SLAVE
2029           This data is from the combination of SHOW MASTER STATUS and SHOW
2030           SLAVE STATUS.
2031
2032       OPEN_TABLES
2033           This data is from SHOW OPEN TABLES.
2034
2035       PROCESSLIST
2036           This data is from SHOW FULL PROCESSLIST.
2037
2038       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
2043       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
2048            --Thread 1568861104 has waited at btr0cur.c line 424 ....
2049

MYSQL PRIVILEGES

2051       ·   You must connect to MySQL as a user who has the SUPER privilege for
2052           many of the functions.
2053
2054       ·   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
2057       ·   You need the PROCESS privilege to see the list of currently running
2058           queries in Q mode.
2059
2060       ·   You need special privileges to start and stop slave servers.
2061
2062       ·   You need appropriate privileges to create and drop the deadlock
2063           tables if needed (see "SERVER CONNECTIONS").
2064

SYSTEM REQUIREMENTS

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
2073       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
2079       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
2085       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

FILES

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

GLOSSARY OF TERMS

2096       tick
2097           A tick is a refresh event, when innotop re-fetches data from
2098           connections and displays it.
2099

ACKNOWLEDGEMENTS

2101       The following people and organizations are acknowledged for various
2102       reasons.  Hopefully no one has been forgotten.
2103
2104       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
2114       (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

COPYRIGHT, LICENSE AND WARRANTY

2119       This program is copyright (c) 2006 Baron Schwartz.  Feedback and
2120       improvements are welcome.
2121
2122       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
2126       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
2132       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
2136       Execute innotop and press '!' to see this information at any time.
2137

AUTHOR

2139       Originally written by Baron Schwartz; currently maintained by Aaron
2140       Racine.
2141

BUGS

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.
2149
2150
2151
2152perl v5.28.1                      2018-11-08                        INNOTOP(1)
Impressum