1 of 37

Information Schema InnoDB Tables

These system tables provide detailed metadata about InnoDB tables, including their structure, storage, and other crucial properties for analysis.

Information Schema INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables
Information Schema INNODB_BUFFER_PAGE Table
Information Schema INNODB_BUFFER_PAGE_LRU Table

Information Schema INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables

These tables provide status information on compression operations for XtraDB/InnoDB tables, grouped by individual indexes.

The INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET tables contain status information on compression operations related to compressed XtraDB/InnoDB tables, grouped by individual indexes. These tables are only populated if the innodb_cmp_per_index_enabled system variable is set to ON.

The PROCESS privilege is required to query this table.

These tables contains the following columns:

Column Name

Description

DATABASE_NAME

Database containing the index.

TABLE_NAME

Table containing the index.

INDEX_NAME

Other values are totals which refer to this index's compression.

COMPRESS_OPS

How many times a page of INDEX_NAME has been compressed. This happens when a new page is created because the compression log runs out of space. This value includes both successful operations and compression failures.

COMPRESS_OPS_OK

How many times a page of INDEX_NAME has been successfully compressed. This value should be as close as possible to COMPRESS_OPS. If it is notably lower, either avoid compressing some tables, or increase the KEY_BLOCK_SIZE for some compressed tables.

COMPRESS_TIME

Time (in seconds) spent to compress pages of the size PAGE_SIZE. This value includes time spent in compression failures.

UNCOMPRESS_OPS

How many times a page of INDEX_NAME has been uncompressed. This happens when an uncompressed version of a page is created in the buffer pool, or when a compression failure occurs.

UNCOMPRESS_TIME

Time (in seconds) spent to uncompress pages of INDEX_NAME.

These tables can be used to measure the effectiveness of XtraDB/InnoDB compression, per table or per index. The values in these tables show which tables perform better with index compression, and which tables cause too many compression failures or perform too many compression/uncompression operations. When compression performs badly for a table, this might mean that you should change its KEY_BLOCK_SIZE, or that the table should not be compressed.

INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET have the same columns and always contain the same values, but when INNODB_CMP_PER_INDEX_RESET is queried, both the tables are cleared. INNODB_CMP_PER_INDEX_RESET can be used, for example, if a script periodically logs the performances of compression in the last period of time. INNODB_CMP_PER_INDEX can be used to see the cumulated statistics.

Information Schema INNODB_BUFFER_PAGE_LRU Table

The Information Schema INNODB_BUFFER_PAGE_LRU table details pages in the buffer pool and their position in the LRU eviction list.

The INNODB_BUFFER_PAGE_LRU table contains information about pages in the and how they are ordered for eviction purposes.

The PROCESS is required to view the table.

It has the following columns:

Column

Description

Information Schema INNODB_BUFFER_POOL_PAGES Table

The Information Schema INNODB_BUFFER_POOL_PAGES table provides a record for each page in the buffer pool, specific to the XtraDB engine.

The Information Schema INNODB_BUFFER_POOL_PAGES table is a Percona enhancement, and is only available for XtraDB, not InnoDB (see XtraDB and InnoDB). It contains a record for each page in the buffer pool.

It has the following columns:

Column

Description

PAGE_TYPE

Type of page; one of index, undo_log, inode, ibuf_free_list, allocated, bitmap, sys, trx_sys, fsp_hdr, xdes, blob, zblob, zblob2 and unknown.

SPACE_ID

Tablespace ID.

PAGE_NO

Page offset within tablespace.

LRU_POSITION

Page position in the LRU (least-recently-used) list.

FIX_COUNT

Page reference count, incremented each time the page is accessed. 0 if the page is not currently being accessed.

FLUSH_TYPE

Flush type of the most recent flush.0 (LRU), 2 (flush_list)

_{This page is licensed: CC BY-SA / Gnu FDL}

Information Schema INNODB_BUFFER_POOL_PAGES_BLOB Table

The INNODB_BUFFER_POOL_PAGES_BLOB table contains information about blob pages in the buffer pool, only available for XtraDB.

The Information Schema INNODB_BUFFER_POOL_PAGES_BLOB table is a Percona enhancement, and is only available for XtraDB, not InnoDB (see XtraDB and InnoDB). It contains information about buffer pool blob pages.

It has the following columns:

Column

Description

SPACE_ID

Tablespace ID.

PAGE_NO

Page offset within tablespace.

COMPRESSED

1 if the blob contains compressed data, 0 if not.

PART_LEN

Page data length.

NEXT_PAGE_NO

Next page number.

LRU_POSITION

Page position in the LRU (least-recently-used) list.

FIX_COUNT

Page reference count, incremented each time the page is accessed. 0 if the page is not currently being accessed.

FLUSH_TYPE

Flush type of the most recent flush.0 (LRU), 2 (flush_list)

_{This page is licensed: CC BY-SA / Gnu FDL}

Information Schema INNODB_BUFFER_POOL_PAGES_INDEX Table

The INNODB_BUFFER_POOL_PAGES_INDEX table provides information about index pages in the buffer pool, specific to the XtraDB engine.

The Information Schema INNODB_BUFFER_POOL_PAGES table is a Percona enhancement, and is only available for XtraDB, not InnoDB (see XtraDB and InnoDB). It contains information about buffer pool index pages.

It has the following columns:

Column

Description

INDEX_ID

Index name

SPACE_ID

Tablespace ID

PAGE_NO

Page offset within tablespace.

N_RECS

Number of user records on the page.

DATA_SIZE

Total data size in bytes of records in the page.

HASHED

1 if the block is in the adaptive hash index, 0 if not.

ACCESS_TIME

Page's last access time.

MODIFIED

1 if the page has been modified since being loaded, 0 if not.

DIRTY

1 if the page has been modified since it was last flushed, 0 if not

OLD

1 if the page in the in the old blocks of the LRU (least-recently-used) list, 0 if not.

LRU_POSITION

Position in the LRU (least-recently-used) list.

FIX_COUNT

Page reference count, incremented each time the page is accessed. 0 if the page is not currently being accessed.

FLUSH_TYPE

Flush type of the most recent flush.0 (LRU), 2 (flush_list)

_{This page is licensed: CC BY-SA / Gnu FDL}

Information Schema INNODB_CHANGED_PAGES Table

The Information Schema INNODB_CHANGED_PAGES table lists pages that have been modified since a specific checkpoint.

The Information Schema INNODB_CHANGED_PAGES Table contains data about modified pages from the bitmap file. It is updated at checkpoints by the log tracking thread parsing the log, so does not contain real-time data.

The number of records is limited by the value of the innodb_max_changed_pages system variable.

The PROCESS privilege is required to view the table.

It has the following columns:

Column

Description

SPACE_ID

Modified page space id

PAGE_ID

Modified page id

START_LSN

Interval start after which page was changed (equal to checkpoint LSN)

END_LSN

Interval end before which page was changed (equal to checkpoint LSN)

_{This page is licensed: CC BY-SA / Gnu FDL}

Information Schema INNODB_CMP and INNODB_CMP_RESET Tables

These tables contain status information on compression operations for compressed XtraDB/InnoDB tables, detailing compress and uncompress ops.

The INNODB_CMP and INNODB_CMP_RESET tables contain status information on compression operations related to compressed XtraDB/InnoDB tables.

The PROCESS privilege is required to query this table.

These tables contain the following columns:

Column Name

Description

PAGE_SIZE

Compressed page size, in bytes. This value is unique in the table; other values are totals which refer to pages of this size.

COMPRESS_OPS

How many times a page of the size PAGE_SIZE has been compressed. This happens when a new page is created because the compression log runs out of space. This value includes both successful operations and compression failures.

COMPRESS_OPS_OK

How many times a page of the size PAGE_SIZE has been successfully compressed. This value should be as close as possible to COMPRESS_OPS. If it is notably lower, either avoid compressing some tables, or increase the KEY_BLOCK_SIZE for some compressed tables.

COMPRESS_TIME

Time (in seconds) spent to compress pages of the size PAGE_SIZE. This value includes time spent in compression failures.

UNCOMPRESS_OPS

These tables can be used to measure the effectiveness of XtraDB/InnoDB table compression. When you have to decide a value for KEY_BLOCK_SIZE, you can create more than one version of the table (one for each candidate value) and run a realistic workload on them. Then, these tables can be used to see how the operations performed with different page sizes.

INNODB_CMP and INNODB_CMP_RESET have the same columns and always contain the same values, but when INNODB_CMP_RESET is queried, both the tables are cleared. INNODB_CMP_RESET can be used, for example, if a script periodically logs the performances of compression in the last period of time. INNODB_CMP can be used to see the cumulated statistics.

Examples

SELECT * FROM information_schema.INNODB_CMP\G
**************************** 1. row *****************************
      page_size: 1024
   compress_ops: 0
compress_ops_ok: 0
  compress_time: 0
 uncompress_ops: 0
uncompress_time: 0
...

Information Schema INNODB_CMPMEM and INNODB_CMPMEM_RESET Tables

These tables provide status statistics on compressed pages within the buffer pool, useful for analyzing compression efficiency.

The INNODB_CMPMEM and INNODB_CMPMEM_RESET tables contain status information on compressed pages in the buffer pool (see InnoDB COMPRESSED format).

The PROCESS privilege is required to query this table.

These tables contain the following columns:

Column Name

Description

PAGE_SIZE

Compressed page size, in bytes. This value is unique in the table; other values are totals which refer to pages of this size.

BUFFER_POOL_INSTANCE

Buffer Pool identifier. From returns a value of 0, since multiple InnoDB buffer pool instances has been removed.

PAGES_USED

Number of pages of the size PAGE_SIZE which are currently in the buffer pool.

PAGES_FREE

Number of pages of the size PAGE_SIZE which are currently free, and thus are available for allocation. This value represents the buffer pool's fragmentation. A totally unfragmented buffer pool has at most 1 free page.

RELOCATION_OPS

These tables can be used to measure the effectiveness of InnoDB table compression. When you have to decide a value for KEY_BLOCK_SIZE, you can create more than one version of the table (one for each candidate value) and run a realistic workload on them. Then, these tables can be used to see how the operations performed with different page sizes.

INNODB_CMPMEM and INNODB_CMPMEM_RESET have the same columns and always contain the same values, but when INNODB_CMPMEM_RESET is queried, the RELOCATION_TIME column from both the tables are cleared. INNODB_CMPMEM_RESET can be used, for example, if a script periodically logs the performances of compression in the last period of time. INNODB_CMPMEM can be used to see the cumulated statistics.

Example

SELECT * FROM information_schema.INNODB_CMPMEM\G
********************** 1. row **********************
            page_size: 1024
 buffer_pool_instance: 0
           pages_used: 0
           pages_free: 0
      reloacation_ops: 0
      relocation_time: 0

Information Schema INNODB_FT_BEING_DELETED Table

This table is used only during an OPTIMIZE TABLE operation to store the document IDs of rows currently being deleted from an InnoDB FULLTEXT index.

The Information Schema INNODB_FT_BEING_DELETED table is only used while document ID's in the related INNODB_FT_DELETED are being removed from an InnoDB fulltext index while an OPTIMIZE TABLE is underway. At all other times the table will be empty.

The SUPER privilege is required to view the table, and it also requires the innodb_ft_aux_table system variable to be set.

It has the following column:

Column

Description

DOC_ID

Document ID of the row being deleted. Either an underlying ID value, or a sequence value generated by InnoDB if no usable option exists.

_{This page is licensed: CC BY-SA / Gnu FDL}

Information Schema INNODB_FT_CONFIG Table

The Information Schema INNODB_FT_CONFIG table displays metadata about the configuration of an InnoDB FULLTEXT index, such as the internal cache size.

The Information Schema INNODB_FT_CONFIG table contains InnoDB fulltext index metadata.

The SUPER privilege is required to view the table, and it also requires the innodb_ft_aux_table system variable to be set.

It has the following columns:

Column

Description

KEY

Metadata item name.

VALUE

Associated value.

Example

SELECT * FROM INNODB_FT_CONFIG;
+---------------------------+-------+
| KEY                       | VALUE |
+---------------------------+-------+
| optimize_checkpoint_limit | 180   |
| synced_doc_id             | 6     |
| last_optimized_word       |       |
| deleted_doc_count         | 0     |
| total_word_count          |       |
| optimize_start_time       |       |
| optimize_end_time         |       |
| stopword_table_name       |       |
| use_stopword              | 1     |
| table_state               | 0     |
+---------------------------+-------+

_{This page is licensed: CC BY-SA / Gnu FDL}

Information Schema INNODB_FT_DEFAULT_STOPWORD Table

The INNODB_FT_DEFAULT_STOPWORD table lists the default stop words used by InnoDB FULLTEXT indexes when no custom stopword list is defined.

The Information Schema INNODB_FT_DEFAULT_STOPWORD table contains a list of default stopwords used when creating an InnoDB fulltext index.

The PROCESS privilege is required to view the table.

It has the following column:

Column

Description

VALUE

Default stopword for an InnoDB fulltext index. Setting either the innodb_ft_server_stopword_table or the innodb_ft_user_stopword_table system variable will override this.

Example

SELECT * FROM information_schema.INNODB_FT_DEFAULT_STOPWORD\G
*************************** 1. row ***************************
value: a
*************************** 2. row ***************************
value: about
*************************** 3. row ***************************
value: an
*************************** 4. row ***************************
value: are
...
*************************** 36. row ***************************
value: www

_{This page is licensed: CC BY-SA / Gnu FDL}

Information Schema INNODB_FT_DELETED Table

The INNODB_FT_DELETED table lists document IDs of rows that have been deleted from an InnoDB FULLTEXT index but not yet removed from the index files.

The Information Schema INNODB_FT_DELETED table contains rows that have been deleted from an InnoDB fulltext index. This information is then used to filter results on subsequent searches, removing the need to expensively reorganise the index each time a row is deleted.

The fulltext index is then only reorganized when an OPTIMIZE TABLE statement is underway. The related INNODB_FT_BEING_DELETED table contains rows being deleted while an OPTIMIZE TABLE is in the process of running.

The SUPER privilege is required to view the table, and it also requires the innodb_ft_aux_table system variable to be set.

It has the following column:

Column

Description

DOC_ID

Document ID of the deleted row deleted. Either an underlying ID value, or a sequence value generated by InnoDB if no usable option exists.

Example

SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
+--------+
| DOC_ID |
+--------+
|      2 |
+--------+

DELETE FROM test.ft_innodb LIMIT 1;

SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
+--------+
| DOC_ID |
+--------+
|      2 |
|      3 |
+--------+

_{This page is licensed: CC BY-SA / Gnu FDL}

Information Schema INNODB_FT_INDEX_CACHE Table

The INNODB_FT_INDEX_CACHE table displays token information from newly inserted rows in a FULLTEXT index before the data is flushed to disk.

The INNODB_FT_INDEX_CACHE table contains information about rows that have recently been inserted into an InnoDB . To avoid re-organizing the fulltext index each time a change is made, which would be very expensive, new changes are stored separately and only integrated when an is run.

The SUPER is required to view the table, and it also requires the system variable to be set.

It has the following columns:

Column

Information Schema INNODB_FT_INDEX_TABLE Table

The INNODB_FT_INDEX_TABLE table provides information about the inverted index (tokens and positions) for an InnoDB FULLTEXT index currently in use.

The INNODB_FT_INDEX_TABLE table contains information about InnoDB . To avoid re-organizing the fulltext index each time a change is made, which would be very expensive, new changes are stored separately and only integrated when an is run. See the table.

The SUPER is required to view the table, and it also requires the system variable to be set.

It has the following columns:

Column

Information Schema INNODB_LOCK_WAITS Table

The Information Schema INNODB_LOCK_WAITS table maps blocked transactions to the transactions that are blocking them, aiding in deadlock analysis.

The Information Schema INNODB_LOCK_WAITS table contains information about blocked InnoDB transactions. The PROCESS privilege is required to view the table.

It contains the following columns:

Column

Description

REQUESTING_TRX_ID

Requesting transaction ID from the table.

REQUESTED_LOCK_ID

Lock ID from the table for the waiting transaction.

BLOCKING_TRX_ID

Blocking transaction ID from the table.

BLOCKING_LOCK_ID

Lock ID from the table of a lock held by a transaction that is blocking another transaction.

The table is often used in conjunction with the INNODB_LOCKS and INNODB_TRX tables to diagnose problematic locks and transactions.

_{This page is licensed: CC BY-SA / Gnu FDL}

Information Schema INNODB_LOCKS Table

The INNODB_LOCKS table provides information about locks that a transaction has requested but not yet obtained, or locks that are blocking another transaction.

The Information Schema INNODB_LOCKS table stores information about locks that InnoDB transactions have requested but not yet acquired, or that are blocking another transaction.

It has the following columns:

Column

Description

LOCK_ID

The table is often used in conjunction with the and tables to diagnose problematic locks and transactions

_{This page is licensed: CC BY-SA / Gnu FDL}

Information Schema INNODB_MUTEXES Table

The Information Schema INNODB_MUTEXES table displays statistics about InnoDB mutex and read-write lock waits, helping to identify internal contention.

The INNODB_MUTEXES table monitors mutex and rw locks waits. It has the following columns:

Column

Description

NAME

Name of the lock, as it appears in the source code.

CREATE_FILE

File name of the mutex implementation.

CREATE_LINE

Line number of the mutex implementation.

OS_WAITS

How many times the mutex occurred.

The CREATE_FILE and CREATE_LINE columns depend on the InnoDB/XtraDB version.

The table provides information about all columns listed in the previous table.

The table provides information about rw_lock_t, not about any mutexes.

The SHOW ENGINE INNODB STATUS statement provides similar information.

Examples

SELECT * FROM INNODB_MUTEXES;
+------------------------------+---------------------+-------------+----------+
| NAME                         | CREATE_FILE         | CREATE_LINE | OS_WAITS |
+------------------------------+---------------------+-------------+----------+
| &dict_sys->mutex             | dict0dict.cc        |         989 |        2 |
| &buf_pool->flush_state_mutex | buf0buf.cc          |        1388 |        1 |
| &log_sys->checkpoint_lock    | log0log.cc          |        1014 |        2 |
| &block->lock                 | combined buf0buf.cc |        1120 |        1 |
+------------------------------+---------------------+-------------+----------+

_{This page is licensed: CC BY-SA / Gnu FDL}

Information Schema INNODB_SYS_COLUMNS Table

The INNODB_SYS_COLUMNS table contains metadata about InnoDB table columns, derived directly from the internal InnoDB data dictionary.

The Information Schema INNODB_SYS_COLUMNS table contains information about InnoDB fields.

The PROCESS privilege is required to view the table.

It has the following columns:

Column

Description

The column MTYPE uses a numeric column type identifier, which has the following values:

Column Type Identifier

Description

_{This page is licensed: CC BY-SA / Gnu FDL}

Information Schema INNODB_SYS_DATAFILES Table

The INNODB_SYS_DATAFILES table, now deprecated and removed, was intended to provide metadata for InnoDB tablespace paths.

This table is deprecated and was removed in MariaDB 10.6.0.

The Information Schema INNODB_SYS_DATAFILES table contains information about InnoDB datafile paths. It was intended to provide metadata for tablespaces inside InnoDB tables, which was never implemented in MariaDB and was removed in . The PROCESS privilege was required to view the table.

It contains the following columns:

Column

Description

SPACE

Numeric tablespace. Matches the value.

PATH

Tablespace datafile path.

Example

SELECT * FROM INNODB_SYS_DATAFILES;
+-------+--------------------------------+
| SPACE | PATH                           |
+-------+--------------------------------+
|    19 | ./test/t2.ibd                  |
|    20 | ./test/t3.ibd                  |
...
|    68 | ./test/animals.ibd             |
|    69 | ./test/animal_count.ibd        |
|    70 | ./test/t.ibd                   |
+-------+--------------------------------+

_{This page is licensed: CC BY-SA / Gnu FDL}

Information Schema INNODB_SYS_FIELDS Table

The INNODB_SYS_FIELDS table lists the fields that comprise InnoDB indexes, detailing their position within the index structure.

The Information Schema INNODB_SYS_FIELDS table contains information about fields that are part of an InnoDB index.

The PROCESS privilege is required to view the table.

It has the following columns:

Column

Description

INDEX_ID

Index identifier, matching the value from .

NAME

Field name, matching the value from .

POS

Ordinal position of the field within the index, starting from 0. This is adjusted as columns are removed.

Example

SELECT * FROM information_schema.INNODB_SYS_FIELDS LIMIT 3\G
*************************** 1. row ***************************
INDEX_ID: 11
    NAME: ID
     POS: 0
*************************** 2. row ***************************
INDEX_ID: 12
    NAME: FOR_NAME 
     POS: 0
*************************** 3. row ***************************
INDEX_ID: 13
    NAME: REF_NAME 
     POS: 0
3 rows in set (0.00 sec)

_{This page is licensed: CC BY-SA / Gnu FDL}

Information Schema INNODB_SYS_FOREIGN Table

The INNODB_SYS_FOREIGN table provides metadata about foreign key constraints defined on InnoDB tables, including reference details.

The Information Schema INNODB_SYS_FOREIGN table contains information about InnoDB foreign keys.

The PROCESS privilege is required to view the table.

It has the following columns:

Column

Description

The TYPE column provides a bit flag with information about the foreign key. This information is OR'ed together to read:

Bit Flag

Description

Prior to :

From MariaDB 12.1:

_{This page is licensed: CC BY-SA / Gnu FDL}

Information Schema INNODB_SYS_FOREIGN_COLS Table

The INNODB_SYS_FOREIGN_COLS table maps columns to their respective foreign key constraints within the InnoDB storage engine.

The Information Schema INNODB_SYS_FOREIGN_COLS table contains information about InnoDB foreign key columns.

The PROCESS privilege is required to view the table.

It has the following columns:

Column

Description

Prior to MariaDB 12.1:

From MariaDB 12.1:

_{This page is licensed: CC BY-SA / Gnu FDL}

Information Schema INNODB_SYS_INDEXES Table

The INNODB_SYS_INDEXES table provides metadata about InnoDB indexes, including the table they belong to, unique IDs, and type.

The INNODB_SYS_INDEXES table contains information about InnoDB indexes.

The PROCESS is required to view the table.

It has the following columns:

Field

Type

Null

Information Schema INNODB_SYS_SEMAPHORE_WAITS Table

The INNODB_SYS_SEMAPHORE_WAITS table displays threads currently waiting for semaphores, which is useful for debugging high-concurrency contention.

The INNODB_SYS_SEMAPHORE_WAITS table is meant to contain information about current semaphore waits. At present it is not correctly populated. See .

The is required to view the table.

It contains the following columns:

Column

Description

Information Schema INNODB_SYS_TABLESPACES Table

The INNODB_SYS_TABLESPACES table provides metadata about the tablespaces used by InnoDB, including space ID, page size, and filename.

The INNODB_SYS_TABLESPACES table contains information about InnoDB tablespaces. Until it was based on the internal SYS_TABLESPACES table. This internal table was removed in , so this Information Schema table has been repurposed to directly reflect the filesystem (fil_system.space_list).

The PROCESS is required to view the table.

It has the following columns:

Information Schema INNODB_SYS_TABLESTATS Table

The INNODB_SYS_TABLESTATS table offers low-level performance statistics for InnoDB tables, such as row counts and modification counters.

The INNODB_SYS_TABLESTATS table contains InnoDB status information. It can be used for developing new performance-related extensions, or high-level performance monitoring.

The PROCESS is required to view the table.

Note that the MySQL InnoDB and Percona XtraDB versions of the tables differ (see ).

It contains the following columns:

Information Schema INNODB_SYS_VIRTUAL Table

The INNODB_SYS_VIRTUAL table contains metadata about the base columns of virtual generated columns in InnoDB tables.

The Information Schema INNODB_SYS_VIRTUAL table contains information about base columns of virtual columns. The PROCESS privilege is required to view the table.

It contains the following columns:

Field

Type

Null

Default

_{This page is licensed: CC BY-SA / Gnu FDL}

Information Schema INNODB_TABLESPACES_ENCRYPTION Table

The INNODB_TABLESPACES_ENCRYPTION table provides metadata about encrypted InnoDB tablespaces, including key versions and rotation status.

The INNODB_TABLESPACES_ENCRYPTION table contains metadata about . When you , an entry for the tablespace is added to this table. If you later , then the row still remains in this table, but the ENCRYPTION_SCHEME and CURRENT_KEY_VERSION columns will be set to 0.

Viewing this table requires the privilege. It contains the following columns:

Information Schema INNODB_TABLESPACES_SCRUBBING Table

The INNODB_TABLESPACES_SCRUBBING table contains statistics about background data scrubbing processes for InnoDB tablespaces.

The INNODB_TABLESPACES_SCRUBBING table contains information.

The PROCESS is required to view the table.

It has the following columns:

Column

Description

Information Schema TEMP_TABLES_INFO Table

The TEMP_TABLES_INFO table lists active InnoDB temporary tables, including their column counts and tablespace identifiers.

The TEMP_TABLES_INFO table contains information about active InnoDB temporary tables. All user and system-created temporary tables are reported when querying this table, with the exception of optimized internal temporary tables. The data is stored in memory.

Previously, InnoDB temp table metadata was rather stored in InnoDB system tables.

It has the following columns:

Column

Description

Information Schema INNODB_UNDO_LOGS Table

The INNODB_UNDO_LOGS table, an XtraDB enhancement, contains information about undo log segments, including transaction IDs and states.

The INNODB_UNDO_LOGS are a Percona enhancement, introduced in version and removed in 10.0.

The Information Schema INNODB_UNDO_LOGS table is a Percona enhancement, and is only available for XtraDB, not InnoDB (see XtraDB and InnoDB). It contains information about the InnoDB undo log, with each record being an undo log segment. It was removed in .

It has the following columns:

Column

Description

_{This page is licensed: CC BY-SA / Gnu FDL}

SELECT * FROM INNODB_FT_INDEX_CACHE; +------------+--------------+-------------+-----------+--------+----------+ | WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION | +------------+--------------+-------------+-----------+--------+----------+ | and | 4 | 4 | 1 | 4 | 0 | | arrived | 4 | 4 | 1 | 4 | 20 | | ate | 1 | 1 | 1 | 1 | 4 | | everybody | 1 | 1 | 1 | 1 | 8 | | goldilocks | 4 | 4 | 1 | 4 | 9 | | hungry | 3 | 3 | 1 | 3 | 8 | | then | 4 | 4 | 1 | 4 | 4 | | wicked | 2 | 2 | 1 | 2 | 4 | | witch | 2 | 2 | 1 | 2 | 11 | +------------+--------------+-------------+-----------+--------+----------+ 9 rows in set (0.00 sec) INSERT INTO test.ft_innodb VALUES(3,'And she ate a pear'); SELECT * FROM INNODB_FT_INDEX_CACHE; +------------+--------------+-------------+-----------+--------+----------+ | WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION | +------------+--------------+-------------+-----------+--------+----------+ | and | 4 | 5 | 2 | 4 | 0 | | and | 4 | 5 | 2 | 5 | 0 | | arrived | 4 | 4 | 1 | 4 | 20 | | ate | 1 | 5 | 2 | 1 | 4 | | ate | 1 | 5 | 2 | 5 | 8 | | everybody | 1 | 1 | 1 | 1 | 8 | | goldilocks | 4 | 4 | 1 | 4 | 9 | | hungry | 3 | 3 | 1 | 3 | 8 | | pear | 5 | 5 | 1 | 5 | 14 | | she | 5 | 5 | 1 | 5 | 4 | | then | 4 | 4 | 1 | 4 | 4 | | wicked | 2 | 2 | 1 | 2 | 4 | | witch | 2 | 2 | 1 | 2 | 11 | +------------+--------------+-------------+-----------+--------+----------+

SELECT * FROM information_schema.INNODB_TABLESPACES_ENCRYPTION WHERE NAME LIKE 'db_encrypt%'; +-------+----------------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+ | SPACE | NAME | ENCRYPTION_SCHEME | KEYSERVER_REQUESTS | MIN_KEY_VERSION | CURRENT_KEY_VERSION | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER | +-------+----------------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+ | 18 | db_encrypt/t_encrypted_existing_key | 1 | 1 | 1 | 1 | NULL | NULL | | 19 | db_encrypt/t_not_encrypted_existing_key | 1 | 0 | 1 | 1 | NULL | NULL | | 20 | db_encrypt/t_not_encrypted_non_existing_key | 1 | 0 | 4294967295 | 4294967295 | NULL | NULL | | 21 | db_encrypt/t_default_encryption_existing_key | 1 | 1 | 1 | 1 | NULL | NULL | | 22 | db_encrypt/t_encrypted_default_key | 1 | 1 | 1 | 1 | NULL | NULL | | 23 | db_encrypt/t_not_encrypted_default_key | 1 | 0 | 1 | 1 | NULL | NULL | | 24 | db_encrypt/t_defaults | 1 | 1 | 1 | 1 | NULL | NULL | +-------+----------------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+ 7 rows in set (0.00 sec)

DESC information_schema.innodb_buffer_pool_stats; +----------------------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------------------+---------------------+------+-----+---------+-------+ | POOL_ID | bigint(21) unsigned | NO | | 0 | | | POOL_SIZE | bigint(21) unsigned | NO | | 0 | | | FREE_BUFFERS | bigint(21) unsigned | NO | | 0 | | | DATABASE_PAGES | bigint(21) unsigned | NO | | 0 | | | OLD_DATABASE_PAGES | bigint(21) unsigned | NO | | 0 | | | MODIFIED_DATABASE_PAGES | bigint(21) unsigned | NO | | 0 | | | PENDING_DECOMPRESS | bigint(21) unsigned | NO | | 0 | | | PENDING_READS | bigint(21) unsigned | NO | | 0 | | | PENDING_FLUSH_LRU | bigint(21) unsigned | NO | | 0 | | | PENDING_FLUSH_LIST | bigint(21) unsigned | NO | | 0 | | | PAGES_MADE_YOUNG | bigint(21) unsigned | NO | | 0 | | | PAGES_NOT_MADE_YOUNG | bigint(21) unsigned | NO | | 0 | | | PAGES_MADE_YOUNG_RATE | double | NO | | 0 | | | PAGES_MADE_NOT_YOUNG_RATE | double | NO | | 0 | | | NUMBER_PAGES_READ | bigint(21) unsigned | NO | | 0 | | | NUMBER_PAGES_CREATED | bigint(21) unsigned | NO | | 0 | | | NUMBER_PAGES_WRITTEN | bigint(21) unsigned | NO | | 0 | | | PAGES_READ_RATE | double | NO | | 0 | | | PAGES_CREATE_RATE | double | NO | | 0 | | | PAGES_WRITTEN_RATE | double | NO | | 0 | | | NUMBER_PAGES_GET | bigint(21) unsigned | NO | | 0 | | | HIT_RATE | bigint(21) unsigned | NO | | 0 | | | YOUNG_MAKE_PER_THOUSAND_GETS | bigint(21) unsigned | NO | | 0 | | | NOT_YOUNG_MAKE_PER_THOUSAND_GETS | bigint(21) unsigned | NO | | 0 | | | NUMBER_PAGES_READ_AHEAD | bigint(21) unsigned | NO | | 0 | | | NUMBER_READ_AHEAD_EVICTED | bigint(21) unsigned | NO | | 0 | | | READ_AHEAD_RATE | double | NO | | 0 | | | READ_AHEAD_EVICTED_RATE | double | NO | | 0 | | | LRU_IO_TOTAL | bigint(21) unsigned | NO | | 0 | | | LRU_IO_CURRENT | bigint(21) unsigned | NO | | 0 | | | UNCOMPRESS_TOTAL | bigint(21) unsigned | NO | | 0 | | | UNCOMPRESS_CURRENT | bigint(21) unsigned | NO | | 0 | | +----------------------------------+---------------------+------+-----+---------+-------+

Information Schema INNODB_METRICS Table

The Information Schema INNODB_METRICS table contains a wide range of low-level performance metrics and counters for the InnoDB storage engine.

The Information Schema INNODB_METRICS table contains a list of useful InnoDB performance metrics. Each row in the table represents an instrumented counter that can be stopped, started and reset, and which can be grouped together by module.

The PROCESS privilege is required to view the table.

It has the following columns:

Column

Description

Note: In MariaDB 10.4 and earlier the ENABLED column was called STATUS.

Most of the counters are disabled by default. To enable them, use the system variable. You can either enable a variable by its name, for example:

or enable a number of counters grouped by module. The SUBSYSTEM field indicates which counters are grouped together, but the following module names need to be used:

Module Name

Subsystem Field

There are four counters in the icp subsystem:

To enable them all, use the associated module name from the table above, module_icp.

The % wildcard, used to represent any number of characters, can also be used when naming counters, for example:

To disable counters, use the system variable, using the same naming rules as described above for enabling.

Counter status is not persistent, and will be reset when the server restarts. It is possible to use the options on the command line, or the innodb_monitor_enable option only in a configuration file.

Counters can also be reset. Resetting sets all the *_COUNT_RESET values to zero, while leaving the *_COUNT values, which perform counts since the counter was enabled, untouched. Resetting is performed with the (for individual counters) and (for all counters) system variables.

From MariaDB 10.6, the interface was simplified by removing the following variables:

buffer_LRU_batches_flush
buffer_LRU_batch_flush_pages
buffer_LRU_batches_evict
buffer_LRU_batch_evict_pages

And by making the following reflect the status variables:

buffer_LRU_batch_flush_total_pages:
buffer_LRU_batch_evict_total_pages:

The intention is to eventually remove the interface entirely (see ).

_{This page is licensed: CC BY-SA / Gnu FDL}

SELECT name,subsystem,type,comment FROM INFORMATION_SCHEMA.INNODB_METRICS\G *************************** 1. row *************************** name: metadata_table_handles_opened subsystem: metadata type: counter comment: Number of table handles opened *************************** 2. row *************************** name: lock_deadlocks subsystem: lock type: value comment: Number of deadlocks *************************** 3. row *************************** name: lock_timeouts subsystem: lock type: value comment: Number of lock timeouts *************************** 4. row *************************** name: lock_rec_lock_waits subsystem: lock type: counter comment: Number of times enqueued into record lock wait queue *************************** 5. row *************************** name: lock_table_lock_waits subsystem: lock type: counter comment: Number of times enqueued into table lock wait queue *************************** 6. row *************************** name: lock_rec_lock_requests subsystem: lock type: counter comment: Number of record locks requested *************************** 7. row *************************** name: lock_rec_lock_created subsystem: lock type: counter comment: Number of record locks created *************************** 8. row *************************** name: lock_rec_lock_removed subsystem: lock type: counter comment: Number of record locks removed from the lock queue *************************** 9. row *************************** name: lock_rec_locks subsystem: lock type: counter comment: Current number of record locks on tables *************************** 10. row *************************** name: lock_table_lock_created subsystem: lock type: counter comment: Number of table locks created ... *************************** 207. row *************************** name: icp_attempts subsystem: icp type: counter comment: Number of attempts for index push-down condition checks *************************** 208. row *************************** name: icp_no_match subsystem: icp type: counter comment: Index push-down condition does not match *************************** 209. row *************************** name: icp_out_of_range subsystem: icp type: counter comment: Index push-down condition out of range *************************** 210. row *************************** name: icp_match subsystem: icp type: counter comment: Index push-down condition matches

Information Schema InnoDB Tables

Information Schema INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables

See Also

Information Schema INNODB_BUFFER_PAGE_LRU Table

Information Schema INNODB_BUFFER_POOL_PAGES Table

Information Schema INNODB_BUFFER_POOL_PAGES_BLOB Table

Information Schema INNODB_BUFFER_POOL_PAGES_INDEX Table

Information Schema INNODB_CHANGED_PAGES Table

Information Schema INNODB_CMP and INNODB_CMP_RESET Tables

Examples

See Also

Information Schema INNODB_CMPMEM and INNODB_CMPMEM_RESET Tables

Example

See Also

Information Schema INNODB_FT_BEING_DELETED Table

Information Schema INNODB_FT_CONFIG Table

Example

Information Schema INNODB_FT_DEFAULT_STOPWORD Table

Example

Information Schema INNODB_FT_DELETED Table

Example

Information Schema INNODB_FT_INDEX_CACHE Table

Information Schema INNODB_FT_INDEX_TABLE Table

Information Schema INNODB_LOCK_WAITS Table

Information Schema INNODB_LOCKS Table

Information Schema INNODB_MUTEXES Table

Examples

Information Schema INNODB_SYS_COLUMNS Table

Information Schema INNODB_SYS_DATAFILES Table

Example

Information Schema INNODB_SYS_FIELDS Table

Example

Information Schema INNODB_SYS_FOREIGN Table

Information Schema INNODB_SYS_FOREIGN_COLS Table

Information Schema INNODB_SYS_INDEXES Table

Information Schema INNODB_SYS_SEMAPHORE_WAITS Table

Information Schema INNODB_SYS_TABLESPACES Table

Information Schema INNODB_SYS_TABLESTATS Table

Information Schema INNODB_SYS_VIRTUAL Table

Information Schema INNODB_TABLESPACES_ENCRYPTION Table

Information Schema INNODB_TABLESPACES_SCRUBBING Table

Information Schema TEMP_TABLES_INFO Table

Information Schema INNODB_UNDO_LOGS Table

Information Schema InnoDB Tables

Information Schema INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables

See Also

Information Schema INNODB_BUFFER_POOL_PAGES Table

Information Schema INNODB_BUFFER_POOL_PAGES_BLOB Table

Information Schema INNODB_BUFFER_POOL_PAGES_INDEX Table

Information Schema INNODB_CHANGED_PAGES Table

Information Schema INNODB_CMP and INNODB_CMP_RESET Tables

Examples

See Also

Information Schema INNODB_CMPMEM and INNODB_CMPMEM_RESET Tables

Example

See Also

Information Schema INNODB_FT_BEING_DELETED Table

Information Schema INNODB_FT_CONFIG Table

Example

Information Schema INNODB_FT_DEFAULT_STOPWORD Table

Example

Information Schema INNODB_FT_DELETED Table

Example

Information Schema INNODB_FT_INDEX_TABLE Table

Information Schema INNODB_LOCK_WAITS Table

Information Schema INNODB_MUTEXES Table

Examples

Information Schema INNODB_SYS_DATAFILES Table

Example

Information Schema INNODB_SYS_FIELDS Table

Example

Information Schema INNODB_SYS_FOREIGN_COLS Table

Information Schema INNODB_SYS_TABLESTATS Table

Information Schema INNODB_SYS_VIRTUAL Table

Information Schema INNODB_TABLESPACES_SCRUBBING Table

Information Schema TEMP_TABLES_INFO Table

Information Schema INNODB_UNDO_LOGS Table

Examples

Examples

Example