Spec-Zone .ru
спецификации, руководства, описания, API

14.2.4.4. SHOW ENGINE INNODBSTATUS and the InnoDB Monitors

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. Also, output for the standard InnoDB Monitor is available on demand through the SHOW ENGINE INNODB STATUS SQL statement.

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.)

The PROCESS privilege is required to start or stop the InnoDB Monitor tables.

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.2, "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: 

================================================090407 12:06:19 INNODB TABLESPACE 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.4.4.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: 

mysql> SHOW ENGINE INNODB
            STATUS\G*************************** 1. row ***************************Status:=====================================030709 13:00:59 INNODB MONITOR OUTPUT=====================================Per second averages calculated from the last 18 seconds----------BACKGROUND THREAD----------srv_master_thread loops: 53 1_second, 44 sleeps, 5 10_second, 7 background,  7 flushsrv_master_thread log flush and writes: 48----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 413452, signal count 378357--Thread 32782 has waited at btr0sea.c line 1477 for 0.00 seconds thesemaphore: X-lock on RW-latch at 41a28668 created in file btr0sea.c line 135a writer (thread id 32782) has reserved it in mode wait exclusivenumber of readers 1, waiters flag 1Last time read locked in file btr0sea.c line 731Last time write locked in file btr0sea.c line 1347Mutex spin waits 0, rounds 0, OS waits 0RW-shared spins 2, rounds 60, OS waits 2RW-excl spins 0, rounds 0, OS waits 0Spin rounds per wait: 0.00 mutex, 20.00 RW-shared, 0.00 RW-excl------------------------LATEST FOREIGN KEY ERROR------------------------030709 13:00:59 Transaction:TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195inserting15 lock struct(s), heap size 2496, undo log entries 9MySQL thread id 25, query id 4668733 localhost heikki updateinsert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk')Foreign key constraint fails for table test/ibtest11a:,  CONSTRAINT `0_219242` FOREIGN KEY (`A`, `D`) REFERENCES `ibtest11b` (`A`,  `D`) ON DELETE CASCADE ON UPDATE CASCADETrying to add in child table, in index PRIMARY tuple: 0: len 4; hex 80000101; asc ....;; 1: len 4; hex 80000005; asc ....;; 2: len 4; hex 6b68446b; asc khDk;; 3: len 6; hex 0000114e0edc; asc ...N..;; 4: len 7; hex 00000000c3e0a7; asc .......;; 5: len 4; hex 6b68446b; asc khDk;;But in parent table test/ibtest11b, in index PRIMARY,the closest match we can find is record:RECORD: info bits 0 0: len 4; hex 8000015b; asc ...[;; 1: len 4; hex80000005; asc ....;; 2: len 3; hex 6b6864; asc khd;; 3: len 6; hex0000111ef3eb; asc ......;; 4: len 7; hex 800001001e0084; asc .......;; 5:len 3; hex 6b6864; asc khd;;------------------------LATEST DETECTED DEADLOCK------------------------030709 12:59:58*** (1) TRANSACTION:TRANSACTION 0 290252780, ACTIVE 1 sec, process no 3185insertingLOCK WAIT 3 lock struct(s), heap size 320, undo log entries 146MySQL thread id 21, query id 4553379 localhost heikki updateINSERT INTO alex1 VALUES(86, 86, 794,'aA35818','bb','c79166','d4766t','e187358f','g84586','h794',date_format('2001-04-03 12:54:22','%Y-%m-%d%H:%i'),7*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 indexsymbole trx id 0 290252780 lock mode S waitingRecord lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138;asc aa35818;; 1:*** (2) TRANSACTION:TRANSACTION 0 290251546, ACTIVE 2 sec, process no 3190inserting130 lock struct(s), heap size 11584, undo log entries 437MySQL thread id 23, query id 4554396 localhost heikki updateREPLACE INTO alex1 VALUES(NULL, 32, NULL,'aa3572','','c3572','d6012t','',NULL,'h396', NULL, NULL, 7.31,7.31,7.31,200)*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 indexsymbole trx id 0 290251546 lock_mode X locks rec but not gapRecord lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138;asc aa35818;; 1:*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 indexsymbole trx id 0 290251546 lock_mode X locks gap before rec insert intentionwaitingRecord lock, heap no 82 RECORD: info bits 0 0: len 7; hex 61613335373230;asc aa35720;; 1:*** WE ROLL BACK TRANSACTION (1)------------TRANSACTIONS------------Trx id counter 0 290328385Purge done for trx's n:o < 0 290315608 undo n:o < 0 17History list length 20Total number of lock structs in row lock hash table 70LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 0 0, not started, process no 3491MySQL thread id 32, query id 4668737 localhost heikkishow innodb status---TRANSACTION 0 290328384, ACTIVE 0 sec, process no 320538929 inserting1 lock struct(s), heap size 320MySQL thread id 29, query id 4668736 localhost heikki updateinsert into speedc values (1519229,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgjgjlhhgghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjfh---TRANSACTION 0 290328383, ACTIVE 0 sec, process no 318028684 committing1 lock struct(s), heap size 320, undo log entries 1MySQL thread id 19, query id 4668734 localhost heikki updateinsert into speedcm values (1603393,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgjgjlhhgghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjf---TRANSACTION 0 290328327, ACTIVE 0 sec, process no 320036880 starting index readLOCK WAIT 2 lock struct(s), heap size 320MySQL thread id 27, query id 4668644 localhost heikki Searching rows forupdateupdate ibtest11a set B = 'kHdkkkk' where A = 89572------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 0 page no 65556 n bits 232 table test/ibtest11a indexPRIMARY trx id 0 290328327 lock_mode X waitingRecord lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00;asc supremum.;;---------------------TRANSACTION 0 290328284, ACTIVE 0 sec, process no 319534831 rollback of SQL statementROLLING BACK 14 lock struct(s), heap size 2496, undo log entries 9MySQL thread id 25, query id 4668733 localhost heikki updateinsert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk')---TRANSACTION 0 290327208, ACTIVE 1 sec, process no 31903278258 lock struct(s), heap size 5504, undo log entries 159MySQL thread id 23, query id 4668732 localhost heikki updateREPLACE INTO alex1 VALUES(86, 46, 538,'aa95666','bb','c95666','d9486t','e200498f','g86814','h538',date_format('2001-04-03 12:54:22','%Y-%m-%d%H:%i'),---TRANSACTION 0 290323325, ACTIVE 3 sec, process no 318530733 inserting4 lock struct(s), heap size 1024, undo log entries 165MySQL thread id 21, query id 4668735 localhost heikki updateINSERT INTO alex1 VALUES(NULL, 49, NULL,'aa42837','','c56319','d1719t','',NULL,'h321', NULL, NULL, 7.31,7.31,7.31,200)--------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: 0Pending flushes (fsync) log: 0; buffer pool: 0151671 OS file reads, 94747 OS file writes, 8750 OS fsyncs25.44 reads/s, 18494 avg bytes/read, 17.55 writes/s, 2.33 fsyncs/s-------------------------------------INSERT BUFFER AND ADAPTIVE HASH INDEX-------------------------------------Ibuf for space 0: size 1, free list len 19, seg size 21,85004 inserts, 85004 merged recs, 26669 mergesHash table size 207619, used cells 14461, node heap has 16 buffer(s)1877.67 hash searches/s, 5121.10 non-hash searches/s---LOG---Log sequence number 18 1212842764Log flushed up to   18 1212665295Last checkpoint at  18 11358772900 pending log writes, 0 pending chkp writes4341 log i/o's done, 1.22 log i/o's/second----------------------BUFFER POOL AND MEMORY----------------------Total memory allocated 84966343; in additional pool allocated 1402624Buffer pool size   3200Free buffers       110Database pages     3074Modified db pages  2674Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages read 171380, created 51968, written 19468828.72 reads/s, 20.72 creates/s, 47.55 writes/sBuffer pool hit rate 999 / 1000--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in queueMain thread process no. 3004, id 7176, state: purgingNumber of rows inserted 3738558, updated 127415, deleted 33707, read 7557791586.13 inserts/s, 50.89 updates/s, 28.44 deletes/s, 107.88 reads/s----------------------------END OF INNODB MONITOR OUTPUT============================

InnoDB Monitor output is limited to 1MB 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.

BACKGROUND THREAD

The srv_master_thread lines shows work done by the main background thread.

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. The Spin rounds per wait line shows the number of spinlock rounds per OS wait for a mutex.

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.3.2, "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.

The number of these threads are controlled by the innodb_read_io_threads and innodb_write_io_threads parameters. See Section 14.2.6, "InnoDB Startup Options and System Variables".

INSERT BUFFER AND ADAPTIVE HASH INDEX

This section shows the status of the InnoDB insert buffer and adaptive hash index. (See Section 14.2.3.13.5, "Insert Buffering", and Section 14.2.3.13.6, "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 5.3.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.9.1, "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.

In MySQL 5.7, output from the standard Monitor includes additional sections compared to the output for previous versions. For details, see Diagnostic and Monitoring Capabilities.

14.2.4.4.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 0size 13440, free limit 3136, free extents 28not full frag extents 2: used pages 78, full frag extents 3first seg id not used 0 23845SEGMENT id 0 1 space 0; page 2; res 96 used 46; full ext 0fragm pages 32; free extents 0; not full extents 1: pages 14SEGMENT id 0 2 space 0; page 2; res 1 used 1; full ext 0fragm pages 1; free extents 0; not full extents 0: pages 0SEGMENT id 0 3 space 0; page 2; res 1 used 1; full ext 0fragm 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 2fragm pages 32; free extents 0; not full extents 0: pages 0SEGMENT id 0 488 space 0; page 2; res 1 used 1; full ext 0fragm pages 1; free extents 0; not full extents 0: pages 0SEGMENT id 0 17 space 0; page 2; res 1 used 1; full ext 0fragm 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 7fragm pages 16; free extents 0; not full extents 2: pages 17SEGMENT id 0 172 space 0; page 2; res 1 used 1; full ext 0fragm pages 1; free extents 0; not full extents 0: pages 0SEGMENT id 0 173 space 0; page 2; res 96 used 44; full ext 0fragm 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 0fragm pages 1; free extents 0; not full extents 0: pages 0NUMBER of file segments: 73Validating tablespaceValidation 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.

In this example using the default page size, the tablespace consists of database pages that are 16KB each. 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 0size 13440, free limit 3136, free extents 28not full frag extents 2: used pages 78, full frag extents 3first 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 2fragm 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 one at a time, up to 32 pages (this is the fragm pages value). After that, InnoDB allocates complete 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 0fragm 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.4.4.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:09:32 INNODB TABLE MONITOR OUTPUT===========================================--------------------------------------TABLE: name SYS_FOREIGN, id 0 11, columns 7, indexes 3, appr.rows 1  COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0;           FOR_NAME: DATA_VARCHAR DATA_ENGLISH len 0;           REF_NAME: DATA_VARCHAR DATA_ENGLISH len 0;           N_COLS: DATA_INT len 4;           DB_ROW_ID: DATA_SYS prtype 256 len 6;           DB_TRX_ID: DATA_SYS prtype 257 len 6;  INDEX: name ID_IND, id 0 11, fields 1/6, uniq 1, 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, uniq 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, uniq 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 7, indexes 1, appr.rows 1  COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0;           POS: DATA_INT len 4;           FOR_COL_NAME: DATA_VARCHAR DATA_ENGLISH len 0;           REF_COL_NAME: DATA_VARCHAR DATA_ENGLISH len 0;           DB_ROW_ID: DATA_SYS prtype 256 len 6;           DB_TRX_ID: DATA_SYS prtype 257 len 6;  INDEX: name ID_IND, id 0 14, fields 2/6, uniq 2, 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 10, indexes 2, appr.rows 201  COLUMNS: par_id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4;           child_id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4;           name: DATA_VARCHAR prtype 524303 len 40;           birth: DATA_INT DATA_BINARY_TYPE len 3;           weight: DATA_FIXBINARY DATA_BINARY_TYPE len 5;           misc_info: DATA_VARCHAR prtype 524303 len 255;           last_update: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 4;           DB_ROW_ID: DATA_SYS prtype 256 len 6;           DB_TRX_ID: DATA_SYS prtype 257 len 6;  INDEX: name PRIMARY, id 0 17, fields 2/9, uniq 2, type 3   root page 52, appr.key vals 201, leaf pages 5, size pages 6   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, uniq 3, type 0   root page 53, appr.key vals 210, 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 4, indexes 1, appr.rows 0  COLUMNS: i: DATA_INT DATA_BINARY_TYPE len 4;           DB_ROW_ID: DATA_SYS prtype 256 len 6;           DB_TRX_ID: DATA_SYS prtype 257 len 6;  INDEX: name GEN_CLUST_INDEX, id 0 19, fields 0/4, uniq 1, type 1   root page 193, 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 6, indexes 2, appr.rows 299  COLUMNS: par_id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4;           fname: DATA_CHAR prtype 524542 len 20;           lname: DATA_CHAR prtype 524542 len 20;           DB_ROW_ID: DATA_SYS prtype 256 len 6;           DB_TRX_ID: DATA_SYS prtype 257 len 6;  INDEX: name PRIMARY, id 0 15, fields 1/5, uniq 1, 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, uniq 2, 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.

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.3.13.2, "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.

  • uniq: The number of leading fields that are enough to determine index values uniquely.

  • 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.

Copyright © 1997, 2013, Oracle and/or its affiliates. All rights reserved.Legal Notices
Home