14.2.12.2 SHOW ENGINE INNODB STATUS and the InnoDB Monitors

14.2.12.2.1 InnoDB Standard Monitor and Lock Monitor Output
14.2.12.2.2 InnoDB Tablespace Monitor Output
14.2.12.2.3 InnoDB Table Monitor Output

InnoDB Monitors provide information about the InnoDB internal state. This information is useful for performance tuning. Each Monitor can be enabled by creating a table with a special name, which causes InnoDB to write Monitor output periodically. Output for the standard InnoDB Monitor is also available on demand through the SHOW ENGINE INNODB STATUS SQL statement. Additionally, to assist with troubleshooting, InnoDB temporarily enables standard InnoDB Monitor output under certain conditions. For more information, see Section 14.2.12.3, “InnoDB General Troubleshooting”.

There are several types of InnoDB Monitors:

To enable an InnoDB Monitor for periodic output, use a CREATE TABLE statement to create the table associated with the Monitor. For example, to enable the standard InnoDB Monitor, create the innodb_monitor table:

CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;

To stop the Monitor, drop the table:

DROP TABLE innodb_monitor;

The CREATE TABLE syntax is just a way to pass a command to the InnoDB engine through MySQL's SQL parser: The only things that matter are the table name innodb_monitor and that it be an InnoDB table. The structure of the table is not relevant at all for the InnoDB Monitor. If you shut down the server, the Monitor does not restart automatically when you restart the server. Drop the Monitor table and issue a new CREATE TABLE statement to start the Monitor. (This syntax may change in a future release.)

When you enable InnoDB Monitors for periodic output, InnoDB writes their output to the mysqld server standard error output (stderr). In this case, no output is sent to clients. When switched on, InnoDB Monitors print data about every 15 seconds. Server output usually is directed to the error log (see Section 5.2.1, “The Error Log”). This data is useful in performance tuning. On Windows, start the server from a command prompt in a console window with the --console option if you want to direct the output to the window rather than to the error log.

InnoDB sends diagnostic output to stderr or to files rather than to stdout or fixed-size memory buffers, to avoid potential buffer overflows. As a side effect, the output of SHOW ENGINE INNODB STATUS is written to a status file in the MySQL data directory every fifteen seconds. The name of the file is innodb_status.pid, where pid is the server process ID. InnoDB removes the file for a normal shutdown. If abnormal shutdowns have occurred, instances of these status files may be present and must be removed manually. Before removing them, you might want to examine them to see whether they contain useful information about the cause of abnormal shutdowns. The innodb_status.pid file is created only if the configuration option innodb-status-file=1 is set.

InnoDB Monitors should be enabled only when you actually want to see Monitor information because output generation does result in some performance decrement. Also, if you enable monitor output by creating the associated table, your error log may become quite large if you forget to remove the table later.

For additional information about InnoDB monitors, see:

Each monitor begins with a header containing a timestamp and the monitor name. For example:

=====================================
141017  8:11:59 INNODB MONITOR OUTPUT
=====================================

The header for the standard Monitor (INNODB MONITOR OUTPUT) is also used for the Lock Monitor because the latter produces the same output with the addition of extra lock information.

The following sections describe the output for each Monitor.

14.2.12.2.1 InnoDB Standard Monitor and Lock Monitor Output

The Lock Monitor is the same as the standard Monitor except that it includes additional lock information. Enabling either monitor for periodic output by creating the associated InnoDB table turns on the same output stream, but the stream includes the extra information if the Lock Monitor is enabled. For example, if you create the innodb_monitor and innodb_lock_monitor tables, that turns on a single output stream. The stream includes extra lock information until you disable the Lock Monitor by removing the innodb_lock_monitor table.

Example InnoDB Monitor output (as of MySQL 5.0.96):

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
Status: 
=====================================
141017  8:11:59 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 4 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 96, signal count 96
Mutex spin waits 0, rounds 1620, OS waits 46
RW-shared spins 92, OS waits 46; RW-excl spins 4, OS waits 4
------------------------
LATEST FOREIGN KEY ERROR
------------------------
141017  8:02:40 Transaction:
TRANSACTION 0 1799, ACTIVE 0 sec, process no 3857, OS thread id 140048508237568 
inserting, thread declared inside InnoDB 497
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1216, undo log entries 3
MySQL thread id 1, query id 33 localhost msandbox update
INSERT INTO child VALUES
    (NULL, 1)
    , (NULL, 2)
    , (NULL, 3)
    , (NULL, 4)
    , (NULL, 5)
    , (NULL, 6)
Foreign key constraint fails for table `mysql/child`:
,
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
 ON DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index `par_ind` tuple:
DATA TUPLE: 2 fields;
 0: len 4; hex 80000003; asc     ;; 1: len 4; hex 80000003; asc     ;;

But in parent table `mysql/parent`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;; 1: len 6; hex 000000000703; asc       ;; 2: 
len 7; hex 80000000320134; asc     2 4;;

------------------------
LATEST DETECTED DEADLOCK
------------------------
141017  8:04:35
*** (1) TRANSACTION:
TRANSACTION 0 1803, ACTIVE 11 sec, process no 3857, OS thread id 140048507971328
 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 368
MySQL thread id 2, query id 89 localhost msandbox updating
DELETE FROM t WHERE i = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 4555 n bits 72 index `GEN_CLUST_INDEX` of table 
`mysql/t` trx id 0 1803 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000200; asc       ;; 1: len 6; hex 000000000709; asc       
;; 2: len 7; hex 80000000320110; asc     2  ;; 3: len 4; hex 80000001; asc     ;
;

*** (2) TRANSACTION:
TRANSACTION 0 1802, ACTIVE 33 sec, process no 3857, OS thread id 140048508237568
 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1216
MySQL thread id 1, query id 90 localhost msandbox updating
DELETE FROM t WHERE i = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 4555 n bits 72 index `GEN_CLUST_INDEX` of table 
`mysql/t` trx id 0 1802 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000200; asc       ;; 1: len 6; hex 000000000709; asc       
;; 2: len 7; hex 80000000320110; asc     2  ;; 3: len 4; hex 80000001; asc     ;
;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 4555 n bits 72 index `GEN_CLUST_INDEX` of table 
`mysql/t` trx id 0 1802 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000200; asc       ;; 1: len 6; hex 000000000709; asc       
;; 2: len 7; hex 80000000320110; asc     2  ;; 3: len 4; hex 80000001; asc     ;
;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 0 2119
Purge done for trx's n:o < 0 2048 undo n:o < 0 0
History list length 6
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 2118, not started, process no 3857, OS thread id 14004850770508
8
MySQL thread id 4, query id 430 localhost msandbox end
INSERT INTO `salaries` VALUES (53000,67817,'1998-12-05','1999-12-05'),(53000,689
97,'1999-12-05','2000-12-04'),(53000,71070,'2000-12-04','2001-12-04'),(53000,752
14,'2001-12-04','9999-01-01'),(53001,49513,'1985-03-18','1986-03-18'),(53001,493
81,'1986-03-18','1987-03-18'),(53001,49006,'1987-03-18','1988-03-17'),(53001,523
22,'1988-03-17','1989-03-17'),(53001,54980,'1989-03-17','1990-03-17'),(53001,553
51,'1990-03-17','1991-03-17'),(53001,55221,'1991-03-17','1991-08-06'),(53002,400
00,'1998-03-26','1999-03-26'),(53002,41830,'1999-03-26','2000-03-25'),(53002,445
10,'2000-03-25','2001-03-25'),(53002,480
---TRANSACTION 0 1803, not started, process no 3857, OS thread id 14004850797132
8
MySQL thread id 2, query id 336 localhost msandbox
---TRANSACTION 0 1802, not started, process no 3857, OS thread id 14004850823756
8
MySQL thread id 1, query id 431 localhost msandbox
SHOW ENGINE INNODB STATUS
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
2454 OS file reads, 5144 OS file writes, 1437 OS fsyncs
42.74 reads/s, 26444 avg bytes/read, 86.73 writes/s, 25.24 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 17393, used cells 8885, node heap has 20 buffer(s)
329491.13 hash searches/s, 2940.76 non-hash searches/s
---
LOG
---
Log sequence number 0 927897901
Log flushed up to   0 927897901
Last checkpoint at  0 923527492
0 pending log writes, 0 pending chkp writes
1837 log i/o's done, 33.49 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 20641866; in additional pool allocated 951040
Buffer pool size   512
Free buffers       1
Database pages     491
Modified db pages  161
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 6690, created 17907, written 21574
68.98 reads/s, 325.42 creates/s, 394.65 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 3857, id 140048477996800, state: sleeping
Number of rows inserted 5427964, updated 0, deleted 3, read 4
110594.85 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

InnoDB Monitor output is limited to 64,000 bytes when produced using the SHOW ENGINE INNODB STATUS statement. This limit does not apply to output written to the server's error output.

Some notes on the output sections:

Status

This section shows the timestamp, the monitor name, and the number of seconds that per-second averages are based on. The number of seconds is the elapsed time between the current time and the last time InnoDB Monitor output was printed.

SEMAPHORES

This section reports threads waiting for a semaphore and statistics on how many times threads have needed a spin or a wait on a mutex or a rw-lock semaphore. A large number of threads waiting for semaphores may be a result of disk I/O, or contention problems inside InnoDB. Contention can be due to heavy parallelism of queries or problems in operating system thread scheduling. Setting the innodb_thread_concurrency system variable smaller than the default value might help in such situations.

LATEST FOREIGN KEY ERROR

This section provides information about the most recent foreign key constraint error. It is not present if no such error has occurred. The contents include the statement that failed as well as information about the constraint that failed and the referenced and referencing tables.

LATEST DETECTED DEADLOCK

This section provides information about the most recent deadlock. It is not present if no deadlock has occurred. The contents show which transactions are involved, the statement each was attempting to execute, the locks they have and need, and which transaction InnoDB decided to roll back to break the deadlock. The lock modes reported in this section are explained in Section 14.2.7.1, “InnoDB Lock Modes”.

TRANSACTIONS

If this section reports lock waits, your applications might have lock contention. The output can also help to trace the reasons for transaction deadlocks.

FILE I/O

This section provides information about threads that InnoDB uses to perform various types of I/O. The first few of these are dedicated to general InnoDB processing. The contents also display information for pending I/O operations and statistics for I/O performance.

On Unix, the number of threads is always 4. On Windows, the number depends on the setting of the innodb_file_io_threads system variable.

INSERT BUFFER AND ADAPTIVE HASH INDEX

This section shows the status of the InnoDB insert buffer and adaptive hash index. (See Section 14.2.9.3, “Insert Buffering”, and Section 14.2.9.4, “Adaptive Hash Indexes”.) The contents include the number of operations performed for each, plus statistics for hash index performance.

LOG

This section displays information about the InnoDB log. The contents include the current log sequence number, how far the log has been flushed to disk, and the position at which InnoDB last took a checkpoint. (See Section 14.2.5.3, “InnoDB Checkpoints”.) The section also displays information about pending writes and write performance statistics.

BUFFER POOL AND MEMORY

This section gives you statistics on pages read and written. You can calculate from these numbers how many data file I/O operations your queries currently are doing.

For additional information about the operation of the buffer pool, see Section 8.6.2, “The InnoDB Buffer Pool”.

ROW OPERATIONS

This section shows what the main thread is doing, including the number and performance rate for each type of row operation.

14.2.12.2.2 InnoDB Tablespace Monitor Output

The InnoDB Tablespace Monitor prints information about the file segments in the shared tablespace and validates the tablespace allocation data structures. If you use individual tablespaces by enabling innodb_file_per_table, the Tablespace Monitor does not describe those tablespaces.

Example InnoDB Tablespace Monitor output:

================================================
090408 21:28:09 INNODB TABLESPACE MONITOR OUTPUT
================================================
FILE SPACE INFO: id 0
size 13440, free limit 3136, free extents 28
not full frag extents 2: used pages 78, full frag extents 3
first seg id not used 0 23845
SEGMENT id 0 1 space 0; page 2; res 96 used 46; full ext 0
fragm pages 32; free extents 0; not full extents 1: pages 14
SEGMENT id 0 2 space 0; page 2; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
SEGMENT id 0 3 space 0; page 2; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
...
SEGMENT id 0 15 space 0; page 2; res 160 used 160; full ext 2
fragm pages 32; free extents 0; not full extents 0: pages 0
SEGMENT id 0 488 space 0; page 2; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
SEGMENT id 0 17 space 0; page 2; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
...
SEGMENT id 0 171 space 0; page 2; res 592 used 481; full ext 7
fragm pages 16; free extents 0; not full extents 2: pages 17
SEGMENT id 0 172 space 0; page 2; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
SEGMENT id 0 173 space 0; page 2; res 96 used 44; full ext 0
fragm pages 32; free extents 0; not full extents 1: pages 12
...
SEGMENT id 0 601 space 0; page 2; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
NUMBER of file segments: 73
Validating tablespace
Validation ok
---------------------------------------
END OF INNODB TABLESPACE MONITOR OUTPUT
=======================================

The Tablespace Monitor output includes information about the shared tablespace as a whole, followed by a list containing a breakdown for each segment within the tablespace.

The tablespace consists of database pages with a default size of 16KB. The pages are grouped into extents of size 1MB (64 consecutive pages).

The initial part of the output that displays overall tablespace information has this format:

FILE SPACE INFO: id 0
size 13440, free limit 3136, free extents 28
not full frag extents 2: used pages 78, full frag extents 3
first seg id not used 0 23845

Overall tablespace information includes these values:

  • id: The tablespace ID. A value of 0 refers to the shared tablespace.

  • size: The current tablespace size in pages.

  • free limit: The minimum page number for which the free list has not been initialized. Pages at or above this limit are free.

  • free extents: The number of free extents.

  • not full frag extents, used pages: The number of fragment extents that are not completely filled, and the number of pages in those extents that have been allocated.

  • full frag extents: The number of completely full fragment extents.

  • first seg id not used: The first unused segment ID.

Individual segment information has this format:

SEGMENT id 0 15 space 0; page 2; res 160 used 160; full ext 2
fragm pages 32; free extents 0; not full extents 0: pages 0

Segment information includes these values:

id: The segment ID.

space, page: The tablespace number and page within the tablespace where the segment inode is located. A tablespace number of 0 indicates the shared tablespace. InnoDB uses inodes to keep track of segments in the tablespace. The other fields displayed for a segment (id, res, and so forth) are derived from information in the inode.

res: The number of pages allocated (reserved) for the segment.

used: The number of allocated pages in use by the segment.

full ext: The number of extents allocated for the segment that are completely used.

fragm pages: The number of initial pages that have been allocated to the segment.

free extents: The number of extents allocated for the segment that are completely unused.

not full extents: The number of extents allocated for the segment that are partially used.

pages: The number of pages used within the not-full extents.

When a segment grows, it starts as a single page, and InnoDB allocates the first pages for it individually, up to 32 pages (this is the fragm pages value). After that, InnoDB allocates complete 64-page extents. InnoDB can add up to 4 extents at a time to a large segment to ensure good sequentiality of data.

For the example segment shown earlier, it has 32 fragment pages, plus 2 full extents (64 pages each), for a total of 160 pages used out of 160 pages allocated. The following segment has 32 fragment pages and one partially full extent using 14 pages for a total of 46 pages used out of 96 pages allocated:

SEGMENT id 0 1 space 0; page 2; res 96 used 46; full ext 0
fragm pages 32; free extents 0; not full extents 1: pages 14

It is possible for a segment that has extents allocated to it to have a fragm pages value less than 32 if some of the individual pages have been deallocated subsequent to extent allocation.

14.2.12.2.3 InnoDB Table Monitor Output

The InnoDB Table Monitor prints the contents of the InnoDB internal data dictionary.

The output contains one section per table. The SYS_FOREIGN and SYS_FOREIGN_COLS sections are for internal data dictionary tables that maintain information about foreign keys. There are also sections for the Table Monitor table and each user-created InnoDB table. Suppose that the following two tables have been created in the test database:

CREATE TABLE parent
(
  par_id    INT NOT NULL,
  fname      CHAR(20),
  lname      CHAR(20),
  PRIMARY KEY (par_id),
  UNIQUE INDEX (lname, fname)
) ENGINE = INNODB;

CREATE TABLE child
(
  par_id      INT NOT NULL,
  child_id    INT NOT NULL,
  name        VARCHAR(40),
  birth       DATE,
  weight      DECIMAL(10,2),
  misc_info   VARCHAR(255),
  last_update TIMESTAMP,
  PRIMARY KEY (par_id, child_id),
  INDEX (name),
  FOREIGN KEY (par_id) REFERENCES parent (par_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE = INNODB;

Then the Table Monitor output will look something like this (reformatted slightly):

===========================================
090420 12:05:26 INNODB TABLE MONITOR OUTPUT
===========================================
--------------------------------------
TABLE: name SYS_FOREIGN, id 0 11, columns 8, indexes 3, appr.rows 1
  COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0 prec 0;
           FOR_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0;
           REF_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0;
           N_COLS: DATA_INT len 4 prec 0;
           DB_ROW_ID: DATA_SYS prtype 256 len 6 prec 0;
           DB_TRX_ID: DATA_SYS prtype 257 len 6 prec 0;
           DB_ROLL_PTR: DATA_SYS prtype 258 len 7 prec 0;
  INDEX: name ID_IND, id 0 11, fields 1/6, type 3
   root page 46, appr.key vals 1, leaf pages 1, size pages 1
   FIELDS:  ID DB_TRX_ID DB_ROLL_PTR FOR_NAME REF_NAME N_COLS
  INDEX: name FOR_IND, id 0 12, fields 1/2, type 0
   root page 47, appr.key vals 1, leaf pages 1, size pages 1
   FIELDS:  FOR_NAME ID
  INDEX: name REF_IND, id 0 13, fields 1/2, type 0
   root page 48, appr.key vals 1, leaf pages 1, size pages 1
   FIELDS:  REF_NAME ID
--------------------------------------
TABLE: name SYS_FOREIGN_COLS, id 0 12, columns 8, indexes 1, appr.rows 1
  COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0 prec 0;
           POS: DATA_INT len 4 prec 0;
           FOR_COL_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0;
           REF_COL_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0;
           DB_ROW_ID: DATA_SYS prtype 256 len 6 prec 0;
           DB_TRX_ID: DATA_SYS prtype 257 len 6 prec 0;
           DB_ROLL_PTR: DATA_SYS prtype 258 len 7 prec 0;
  INDEX: name ID_IND, id 0 14, fields 2/6, type 3
   root page 49, appr.key vals 1, leaf pages 1, size pages 1
   FIELDS:  ID POS DB_TRX_ID DB_ROLL_PTR FOR_COL_NAME REF_COL_NAME
--------------------------------------
TABLE: name test/child, id 0 14, columns 11, indexes 2, appr.rows 210
  COLUMNS: par_id: DATA_INT len 4 prec 0;
           child_id: DATA_INT len 4 prec 0;
           name: DATA_VARCHAR prtype 524303 len 40 prec 0;
           birth: DATA_INT len 3 prec 0;
           weight: type 3 len 5 prec 0;
           misc_info: DATA_VARCHAR prtype 524303 len 255 prec 0;
           last_update: DATA_INT len 4 prec 0;
           DB_ROW_ID: DATA_SYS prtype 256 len 6 prec 0;
           DB_TRX_ID: DATA_SYS prtype 257 len 6 prec 0;
           DB_ROLL_PTR: DATA_SYS prtype 258 len 7 prec 0;
  INDEX: name PRIMARY, id 0 17, fields 2/9, type 3
   root page 52, appr.key vals 210, leaf pages 1, size pages 1
   FIELDS:  par_id child_id DB_TRX_ID DB_ROLL_PTR name birth weight misc_info last_update
  INDEX: name name, id 0 18, fields 1/3, type 0
   root page 53, appr.key vals 1, leaf pages 1, size pages 1
   FIELDS:  name par_id child_id
  FOREIGN KEY CONSTRAINT test/child_ibfk_1: test/child ( par_id )
             REFERENCES test/parent ( par_id )
--------------------------------------
TABLE: name test/innodb_table_monitor, id 0 15, columns 5, indexes 1, appr.rows 0
  COLUMNS: i: DATA_INT len 4 prec 0;
           DB_ROW_ID: DATA_SYS prtype 256 len 6 prec 0;
           DB_TRX_ID: DATA_SYS prtype 257 len 6 prec 0;
           DB_ROLL_PTR: DATA_SYS prtype 258 len 7 prec 0;
  INDEX: name GEN_CLUST_INDEX, id 0 19, fields 0/4, type 1
   root page 54, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  DB_ROW_ID DB_TRX_ID DB_ROLL_PTR i
--------------------------------------
TABLE: name test/parent, id 0 13, columns 7, indexes 2, appr.rows 299
  COLUMNS: par_id: DATA_INT len 4 prec 0;
           fname: DATA_CHAR prtype 524542 len 20 prec 0;
           lname: DATA_CHAR prtype 524542 len 20 prec 0;
           DB_ROW_ID: DATA_SYS prtype 256 len 6 prec 0;
           DB_TRX_ID: DATA_SYS prtype 257 len 6 prec 0;
           DB_ROLL_PTR: DATA_SYS prtype 258 len 7 prec 0;
  INDEX: name PRIMARY, id 0 15, fields 1/5, type 3
   root page 50, appr.key vals 299, leaf pages 2, size pages 3
   FIELDS:  par_id DB_TRX_ID DB_ROLL_PTR fname lname
  INDEX: name lname, id 0 16, fields 2/3, type 2
   root page 51, appr.key vals 300, leaf pages 1, size pages 1
   FIELDS:  lname fname par_id
  FOREIGN KEY CONSTRAINT test/child_ibfk_1: test/child ( par_id )
             REFERENCES test/parent ( par_id )
-----------------------------------
END OF INNODB TABLE MONITOR OUTPUT
==================================

For each table, Table Monitor output contains a section that displays general information about the table and specific information about its columns, indexes, and foreign keys.

The general information for each table includes the table name (in db_name/tbl_name format except for internal tables), its ID, the number of columns and indexes, and an approximate row count.

The COLUMNS part of a table section lists each column in the table. Information for each column indicates its name and data type characteristics. Some internal columns are added by InnoDB, such as DB_ROW_ID (row ID), DB_TRX_ID (transaction ID), and DB_ROLL_PTR (a pointer to the rollback/undo data).

  • DATA_xxx: These symbols indicate the data type. There may be multiple DATA_xxx symbols for a given column.

  • prtype: The column's precise type. This field includes information such as the column data type, character set code, nullability, signedness, and whether it is a binary string. This field is described in the innobase/include/data0type.h source file.

  • len: The column length in bytes.

  • prec: The precision of the type.

Each INDEX part of the table section provides the name and characteristics of one table index:

  • name: The index name. If the name is PRIMARY, the index is a primary key. If the name is GEN_CLUST_INDEX, the index is the clustered index that is created automatically if the table definition doesn't include a primary key or non-NULL unique index. See Section 14.2.9.1, “Clustered and Secondary Indexes”.

  • id: The index ID.

  • fields: The number of fields in the index, as a value in m/n format:

    • m is the number of user-defined columns; that is, the number of columns you would see in the index definition in a CREATE TABLE statement.

    • n is the total number of index columns, including those added internally. For the clustered index, the total includes the other columns in the table definition, plus any columns added internally. For a secondary index, the total includes the columns from the primary key that are not part of the secondary index.

  • type: The index type. This is a bit field. For example, 1 indicates a clustered index and 2 indicates a unique index, so a clustered index (which always contains unique values), will have a type value of 3. An index with a type value of 0 is neither clustered nor unique. The flag values are defined in the innobase/include/dict0mem.h source file.

  • root page: The index root page number.

  • appr. key vals: The approximate index cardinality.

  • leaf pages: The approximate number of leaf pages in the index.

  • size pages: The approximate total number of pages in the index.

  • FIELDS: The names of the fields in the index. For a clustered index that was generated automatically, the field list begins with the internal DB_ROW_ID (row ID) field. DB_TRX_ID and DB_ROLL_PTR are always added internally to the clustered index, following the fields that comprise the primary key. For a secondary index, the final fields are those from the primary key that are not part of the secondary index.

The end of the table section lists the FOREIGN KEY definitions that apply to the table. This information appears whether the table is a referencing or referenced table.