Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
These system tables provide detailed metadata about InnoDB tables, including their structure, storage, and other crucial properties for analysis.
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:
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.
Other tables that can be used to monitor XtraDB/InnoDB compressed tables:
This page is licensed: CC BY-SA / Gnu FDL
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.
The Information Schema INNODB_BUFFER_PAGE table contains detailed information about each page currently in the InnoDB buffer pool.
The Information Schema INNODB_BUFFER_PAGE table contains information about pages in the buffer pool.
Querying this table can have a noticeable performance impact on a production server.
The PROCESS privilege is required to view the table.
It has the following columns:
POOL_ID
The related table contains the same information, but with an LRU (least recently used) position rather than block id.
This page is licensed: CC BY-SA / Gnu FDL
Buffer Pool identifier. From returns a value of 0, since multiple InnoDB buffer pool instances has been removed.
BLOCK_ID
Buffer Pool Block identifier.
SPACE
Tablespace identifier. Matches the SPACE value in the INNODB_SYS_TABLES table.
PAGE_NUMBER
Buffer pool page number.
PAGE_TYPE
Page type; one of allocated (newly-allocated page), index (B-tree node), undo_log (undo log page), inode (index node), ibuf_free_list (insert buffer free list), ibuf_bitmap (insert buffer bitmap), system (system page), trx_system (transaction system data), file_space_header (file space header), extent_descriptor (extent descriptor page), blob (uncompressed blob page), compressed_blob (first compressed blob page), compressed_blob2 (subsequent compressed blob page) or unknown.
FLUSH_TYPE
Flush type.
FIX_COUNT
Count of the threads using this block in the buffer pool. When it is zero, the block can be evicted from the buffer pool.
IS_HASHED
Whether or not a hash index has been built on this page.
NEWEST_MODIFICATION
Most recent modification's Log Sequence Number.
OLDEST_MODIFICATION
Oldest modification's Log Sequence Number.
ACCESS_TIME
Abstract number representing the time the page was first accessed.
TABLE_NAME
Table that the page belongs to.
INDEX_NAME
Index that the page belongs to, either a clustered index or a secondary index.
NUMBER_RECORDS
Number of records the page contains.
DATA_SIZE
Size in bytes of all the records contained in the page.
COMPRESSED_SIZE
Compressed size in bytes of the page, or NULL for pages that aren't compressed.
PAGE_STATE
Page state; one of FILE_PAGE (page from a file) or MEMORY (page from an in-memory object) for valid data, or one of NULL, READY_FOR_USE, NOT_USED, REMOVE_HASH.
IO_FIX
Whether there is I/O pending for the page; one of IO_NONE (no pending I/O), IO_READ (read pending), IO_WRITE (write pending).
IS_OLD
Whether the page is old or not.
FREE_PAGE_CLOCK
Freed_page_clock counter, which tracks the number of blocks removed from the end of the least recently used (LRU) list, at the time the block was last placed at the head of the list.
The Information Schema INNODB_BUFFER_PAGE_LRU table details pages in the buffer pool and their position in the LRU eviction list.
The Information Schema INNODB_BUFFER_PAGE_LRU table contains information about pages in the buffer pool and how they are ordered for eviction purposes.
The PROCESS privilege is required to view the table.
It has the following columns:
POOL_ID
Buffer Pool identifier. From returns a value of 0, since multiple InnoDB buffer pool instances has been removed.
The related table contains the same information, but with a block id rather than LRU position.
This page is licensed: CC BY-SA / Gnu FDL
The INNODB_SYS_FIELDS table lists the fields that comprise InnoDB indexes, detailing their position within the index structure.
The INNODB_SYS_FIELDS table contains information about fields that are part of an InnoDB index.
The PROCESS is required to view the table.
It has the following columns:
The INNODB_SYS_FOREIGN_COLS table maps columns to their respective foreign key constraints within the InnoDB storage engine.
The INNODB_SYS_FOREIGN_COLS table contains information about InnoDB columns.
The PROCESS is required to view the table.
It has the following columns:
The INNODB_SYS_DATAFILES table, now deprecated and removed, was intended to provide metadata for InnoDB tablespace paths.
The 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 was required to view the table.
It contains the following columns:
DESC information_schema.innodb_buffer_page;
+---------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------------------+------+-----+---------+-------+
| POOL_ID | bigint(21) unsigned | NO | | 0 | |
| BLOCK_ID | bigint(21) unsigned | NO | | 0 | |
| SPACE | bigint(21) unsigned | NO | | 0 | |
| PAGE_NUMBER | bigint(21) unsigned | NO | | 0 | |
| PAGE_TYPE | varchar(64) | YES | | NULL | |
| FLUSH_TYPE | bigint(21) unsigned | NO | | 0 | |
| FIX_COUNT | bigint(21) unsigned | NO | | 0 | |
| IS_HASHED | varchar(3) | YES | | NULL | |
| NEWEST_MODIFICATION | bigint(21) unsigned | NO | | 0 | |
| OLDEST_MODIFICATION | bigint(21) unsigned | NO | | 0 | |
| ACCESS_TIME | bigint(21) unsigned | NO | | 0 | |
| TABLE_NAME | varchar(1024) | YES | | NULL | |
| INDEX_NAME | varchar(1024) | YES | | NULL | |
| NUMBER_RECORDS | bigint(21) unsigned | NO | | 0 | |
| DATA_SIZE | bigint(21) unsigned | NO | | 0 | |
| COMPRESSED_SIZE | bigint(21) unsigned | NO | | 0 | |
| PAGE_STATE | varchar(64) | YES | | NULL | |
| IO_FIX | varchar(64) | YES | | NULL | |
| IS_OLD | varchar(3) | YES | | NULL | |
| FREE_PAGE_CLOCK | bigint(21) unsigned | NO | | 0 | |
+---------------------+---------------------+------+-----+---------+-------+SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE\G
...
*************************** 6. row ***************************
POOL_ID: 0
BLOCK_ID: 5
SPACE: 0
PAGE_NUMBER: 11
PAGE_TYPE: INDEX
FLUSH_TYPE: 1
FIX_COUNT: 0
IS_HASHED: NO
NEWEST_MODIFICATION: 2046835
OLDEST_MODIFICATION: 0
ACCESS_TIME: 2585566280
TABLE_NAME: `SYS_INDEXES`
INDEX_NAME: CLUST_IND
NUMBER_RECORDS: 57
DATA_SIZE: 4016
COMPRESSED_SIZE: 0
PAGE_STATE: FILE_PAGE
IO_FIX: IO_NONE
IS_OLD: NO
FREE_PAGE_CLOCK: 0
...Child column name.
REF_COL_NAME
Parent column name.
POS
Ordinal position of the column in the table, starting from 0.
Prior to MariaDB 12.1:
From MariaDB 12.1:
This page is licensed: CC BY-SA / Gnu FDL
ID
Foreign key index associated with this column, matching the INNODB_SYS_FOREIGN.ID field.
Prior to MariaDB 12.1, this is preceded by the database name. From MariaDB 12.1, foreign key names are only required to be unique per table, not per database, so the redundant database name is not shown.
FOR_COL_NAME
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS\G
*************************** 1. row ***************************
ID: test/fk_book_author
FOR_COL_NAME: author_id
REF_COL_NAME: id
POS: 0SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS\G
*************************** 1. row ***************************
ID: fk_book_author
FOR_COL_NAME: author_id
REF_COL_NAME: id
POS: 0POS
Ordinal position of the field within the index, starting from 0. This is adjusted as columns are removed.
This page is licensed: CC BY-SA / Gnu FDL
INDEX_ID
Index identifier, matching the value from INNODB_SYS_INDEXES.INDEX_ID.
NAME
Field name, matching the value from .
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)SPACE
Numeric tablespace. Matches the value.
PATH
Tablespace datafile path.
This page is licensed: CC BY-SA / Gnu FDL
LRU_POSITION
LRU (Least recently-used), for determining eviction order from the buffer pool.
SPACE
Tablespace identifier. Matches the SPACE value on the INNODB_SYS_TABLES table.
PAGE_NUMBER
Buffer pool page number.
PAGE_TYPE
Page type; one of allocated (newly-allocated page), index (B-tree node), undo_log (undo log page), inode (index node), ibuf_free_list (insert buffer free list), ibuf_bitmap (insert buffer bitmap), system (system page), trx_system (transaction system data), file_space_header (file space header), extent_descriptor (extent descriptor page), blob (uncompressed blob page), compressed_blob (first compressed blob page), compressed_blob2 (subsequent compressed blob page) or unknown.
FLUSH_TYPE
Flush type. 0= FLUSH_KEEP, 1 =FLUSH_RELEASE, 2 = FLUSH_IGNORE_CHANGED, 3= FLUSH_FORCE_WRITE
FIX_COUNT
Count of the threads using this block in the buffer pool. When it is zero, the block can be evicted from the buffer pool.
IS_HASHED
Whether or not a hash index has been built on this page.
NEWEST_MODIFICATION
Most recent modification's Log Sequence Number.
OLDEST_MODIFICATION
Oldest modification's Log Sequence Number.
ACCESS_TIME
Abstract number representing the time the page was first accessed.
TABLE_NAME
Table that the page belongs to.
INDEX_NAME
Index that the page belongs to, either a clustered index or a secondary index.
NUMBER_RECORDS
Number of records the page contains.
DATA_SIZE
Size in bytes of all the records contained in the page.
COMPRESSED_SIZE
Compressed size in bytes of the page, or NULL for pages that aren't compressed.
PAGE_STATE
Page state; one of FILE_PAGE (page from a file) or MEMORY (page from an in-memory object) for valid data, or one of NULL, READY_FOR_USE, NOT_USED, REMOVE_HASH.
IO_FIX
Whether there is I/O pending for the page; one of IO_NONE (no pending I/O), IO_READ (read pending), IO_WRITE (write pending).
IS_OLD
Whether the page is old or not.
FREE_PAGE_CLOCK
Freed_page_clock counter, which tracks the number of blocks removed from the end of the LRU list, at the time the block was last placed at the head of the list.
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:
KEY
Metadata item name.
This page is licensed: CC BY-SA / Gnu FDL
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:
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.
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.
Other tables that can be used to monitor InnoDB compressed tables:
This page is licensed: CC BY-SA / Gnu FDL
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:
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.
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.
Other tables that can be used to monitor XtraDB/InnoDB compressed tables:
This page is licensed: CC BY-SA / Gnu FDL
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:
SPACE_ID
This page is licensed: CC BY-SA / Gnu FDL
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:
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.
This page is licensed: CC BY-SA / Gnu FDL
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:
VALUE
Default for an InnoDB . Setting either the or the system variable will override this.
This page is licensed: CC BY-SA / Gnu FDL
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 enchancement, 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:
SPACE_ID
Tablespace ID.
PAGE_NO
Page offset within tablespace.
This page is licensed: CC BY-SA / Gnu FDL
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:
This page is licensed: CC BY-SA / Gnu FDL
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:
REQUESTING_TRX_ID
Requesting transaction ID from the table.
REQUESTED_LOCK_ID
Lock ID from the table for the waiting transaction.
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
The INNODB_BUFFER_POOL_PAGES_INDEX table provides information about index pages in the buffer pool, specific to the XtraDB engine.
The INNODB_BUFFER_POOL_PAGES table is a Percona enhancement, and is only available for XtraDB, not InnoDB (see ). It contains information about index pages.
It has the following columns:
The INNODB_SYS_VIRTUAL table contains metadata about the base columns of virtual generated columns in InnoDB tables.
The INNODB_SYS_VIRTUAL table contains information about base columns of . The PROCESS is required to view the table.
It contains the following columns:
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:
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 INNODB_FT_BEING_DELETED table is only used while document ID's in the related are being removed from an InnoDB while an is underway. At all other times the table will be empty.
The SUPER is required to view the table, and it also requires the system variable to be set.
It has the following column:
The INNODB_UNDO_LOGS table, an XtraDB enhancement, contains information about undo log segments, including transaction IDs and states.
The INNODB_UNDO_LOGS table is a Percona enchancement, and is only available for XtraDB, not InnoDB (see ). It contains information about the InnoDB , with each record being an undo log segment. It was removed in .
It has the following columns:
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:
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 |
+-------+--------------------------------+DESC information_schema.innodb_buffer_page_lru;
+---------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------------------+------+-----+---------+-------+
| POOL_ID | bigint(21) unsigned | NO | | 0 | |
| LRU_POSITION | bigint(21) unsigned | NO | | 0 | |
| SPACE | bigint(21) unsigned | NO | | 0 | |
| PAGE_NUMBER | bigint(21) unsigned | NO | | 0 | |
| PAGE_TYPE | varchar(64) | YES | | NULL | |
| FLUSH_TYPE | bigint(21) unsigned | NO | | 0 | |
| FIX_COUNT | bigint(21) unsigned | NO | | 0 | |
| IS_HASHED | varchar(3) | YES | | NULL | |
| NEWEST_MODIFICATION | bigint(21) unsigned | NO | | 0 | |
| OLDEST_MODIFICATION | bigint(21) unsigned | NO | | 0 | |
| ACCESS_TIME | bigint(21) unsigned | NO | | 0 | |
| TABLE_NAME | varchar(1024) | YES | | NULL | |
| INDEX_NAME | varchar(1024) | YES | | NULL | |
| NUMBER_RECORDS | bigint(21) unsigned | NO | | 0 | |
| DATA_SIZE | bigint(21) unsigned | NO | | 0 | |
| COMPRESSED_SIZE | bigint(21) unsigned | NO | | 0 | |
| COMPRESSED | varchar(3) | YES | | NULL | |
| IO_FIX | varchar(64) | YES | | NULL | |
| IS_OLD | varchar(3) | YES | | NULL | |
| FREE_PAGE_CLOCK | bigint(21) unsigned | NO | | 0 | |
+---------------------+---------------------+------+-----+---------+-------+SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU\G
...
*************************** 6. row ***************************
POOL_ID: 0
LRU_POSITION: 5
SPACE: 0
PAGE_NUMBER: 11
PAGE_TYPE: INDEX
FLUSH_TYPE: 1
FIX_COUNT: 0
IS_HASHED: NO
NEWEST_MODIFICATION: 2046835
OLDEST_MODIFICATION: 0
ACCESS_TIME: 2585566280
TABLE_NAME: `SYS_INDEXES`
INDEX_NAME: CLUST_IND
NUMBER_RECORDS: 57
DATA_SIZE: 4016
COMPRESSED_SIZE: 0
COMPRESSED: NO
IO_FIX: IO_NONE
IS_OLD: NO
FREE_PAGE_CLOCK: 0
...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)
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)
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)
BLOCKING_TRX_ID
Blocking transaction ID from the INNODB_TRX table.
BLOCKING_LOCK_ID
Lock ID from the INNODB.LOCKS table of a lock held by a transaction that is blocking another transaction.
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
INDEX_ID
Index name
SPACE_ID
Tablespace ID
PAGE_NO
0
POS
int(11) unsigned
NO
0
BASE_POS
int(11) unsigned
NO
0
This page is licensed: CC BY-SA / Gnu FDL
TABLE_ID
bigint(21) unsigned
NO
This page is licensed: CC BY-SA / Gnu FDL
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.
VALUE
Associated value.
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 |
+---------------------------+-------+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
How many times a page of the size PAGE_SIZE 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 the size PAGE_SIZE.
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.
TABLE_ID
Table ID.
NAME
Table name.
N_COLS
Number of columns in the temporary table, including three hidden columns that InnoDB creates (DB_ROW_ID, DB_TRX_ID, and DB_ROLL_PTR).
SPACE
Numerical identifier for the tablespace identifier holding the temporary table. Compressed temporary tables are stored by default in separate per-table tablespaces in the temporary file directory. For non-compressed tables, the shared temporary table is named ibtmp1, found in the data directory. Always a non-zero value, and regenerated on server restart.
PER_TABLE_TABLESPACE
If TRUE, the temporary table resides in a separate per-table tablespace. If FALSE, it resides in the shared temporary tablespace.
IS_COMPRESSED
TRUE if the table is compressed.
The PROCESS privilege is required to view the table.
Adding a compressed table:
This page is licensed: CC BY-SA / Gnu FDL
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.
This page is licensed: CC BY-SA / Gnu FDL
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
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
...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: wwwSELECT * 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 |
+--------+CREATE TEMPORARY TABLE t (i INT) ENGINE=INNODB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO;
+----------+--------------+--------+-------+----------------------+---------------+
| TABLE_ID | NAME | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED |
+----------+--------------+--------+-------+----------------------+---------------+
| 39 | #sql1c93_3_1 | 4 | 64 | FALSE | FALSE |
+----------+--------------+--------+-------+----------------------+---------------+SET GLOBAL innodb_file_format="Barracuda";
CREATE TEMPORARY TABLE t2 (i INT) ROW_FORMAT=COMPRESSED ENGINE=INNODB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO;
+----------+--------------+--------+-------+----------------------+---------------+
| TABLE_ID | NAME | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED |
+----------+--------------+--------+-------+----------------------+---------------+
| 40 | #sql1c93_3_3 | 4 | 65 | TRUE | TRUE |
| 39 | #sql1c93_3_1 | 4 | 64 | FALSE | FALSE |
+----------+--------------+--------+-------+----------------------+---------------+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 |
+------------------------------+---------------------+-------------+----------+TRX_ID
Unique transaction ID number, matching the value from the table.
RSEG_ID
Rollback segment ID, matching the value from the information_schema.INNODB_RSEG table.
USEG_ID
Undo segment ID.
SEGMENT_TYPE
Indicates the operation type, for example INSERT or UPDATE.
STATE
Segment state; one of ACTIVE (contains active transaction undo log), CACHED, TO_FREE (insert undo segment can be freed), TO_PURGE (update undo segment won't be re-used and can be purged when all undo data is removed) or PREPARED (segment of a prepared transaction).
SIZE
Size in pages of the segment.
This page is licensed: CC BY-SA / Gnu FDL
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
How many times a page of the size PAGE_SIZE has been relocated. This happens when data exceeds a page (because a row must be copied into a new page) and when two pages are merged (because their data shrunk and can now be contained in one page).
RELOCATION_TIME
Time (in seconds) spent in relocation operations for pages of the size PAGE_SIZE. This column is reset when the INNODB_CMPMEM_RESET table is queried.
The INNODB_FT_INDEX_TABLE table provides information about the inverted index (tokens and positions) for an InnoDB FULLTEXT index currently in use.
The Information Schema INNODB_FT_INDEX_TABLE table contains information about InnoDB fulltext indexes. 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 OPTIMIZE TABLE is run. See the INNODB_FT_INDEX_CACHE table.
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:
WORD
Note that for OPTIMIZE TABLE to process InnoDB fulltext index data, the system variable needs to be set to 1. When this is done, and an OPTIMIZE TABLE statement run, the table will be emptied, and the INNODB_FT_INDEX_TABLE table will be updated.
This page is licensed: CC BY-SA / Gnu FDL
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 Information Schema INNODB_FT_INDEX_CACHE table contains information about rows that have recently been inserted into an InnoDB fulltext index. 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 OPTIMIZE TABLE is run.
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:
WORD
Note that for OPTIMIZE TABLE to process InnoDB fulltext index data, the system variable needs to be set to 1. When this is done, and an OPTIMIZE TABLE statement run, the INNODB_FT_INDEX_CACHE table will be emptied, and the table will be updated.
The OPTIMIZE TABLE statement has no effect, because the variable wasn't set:
This page is licensed: CC BY-SA / Gnu FDL
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:
ID
Foreign key name. Prior to , this is preceded by the database name. From MariaDB 12.1, foreign key names are only required to be unique per table, not per database, so the redundant database name is not shown.
The TYPE column provides a bit flag with information about the foreign key. This information is OR'ed together to read:
Prior to :
From MariaDB 12.1:
This page is licensed: CC BY-SA / Gnu FDL
The INNODB_TABLESPACES_SCRUBBING table contains statistics about background data scrubbing processes for InnoDB tablespaces.
The Information Schema INNODB_TABLESPACES_SCRUBBING table contains data scrubbing information.
The PROCESS privilege is required to view the table.
It has the following columns:
This page is licensed: CC BY-SA / Gnu FDL
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:
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:
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: 0Word from the text of a column with a fulltext index. Words can appear multiple times in the table, once per DOC_ID and POSITION combination.
FIRST_DOC_ID
First document ID where this word appears in the index.
LAST_DOC_ID
Last document ID where this word appears in the index.
DOC_COUNT
Number of rows containing this word in the index.
DOC_ID
Document ID of the newly added row, either an appropriate ID column or an internal InnoDB value.
POSITION
Position of this word instance within the DOC_ID, as an offset added to the previous POSITION instance.
Word from the text of a newly added row. Words can appear multiple times in the table, once per DOC_ID and POSITION combination.
FIRST_DOC_ID
First document ID where this word appears in the index.
LAST_DOC_ID
Last document ID where this word appears in the index.
DOC_COUNT
Number of rows containing this word in the index.
DOC_ID
Document ID of the newly added row, either an appropriate ID column or an internal InnoDB value.
POSITION
Position of this word instance within the DOC_ID, as an offset added to the previous POSITION instance.
FOR_NAME
Database and table name of the foreign key child.
REF_NAME
Database and table name of the foreign key parent.
N_COLS
Number of foreign key index columns.
TYPE
Bit flag providing information about the foreign key.
1
ON DELETE CASCADE
2
ON UPDATE SET NULL
4
ON UPDATE CASCADE
8
ON UPDATE SET NULL
16
ON DELETE NO ACTION
32
ON UPDATE NO ACTION
SELECT * FROM INNODB_FT_INDEX_TABLE;
Empty set (0.00 sec)
SET GLOBAL innodb_optimize_fulltext_only =1;
OPTIMIZE TABLE test.ft_innodb;
+----------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+----------+----------+----------+
| test.ft_innodb | optimize | status | OK |
+----------------+----------+----------+----------+
SELECT * FROM INNODB_FT_INDEX_TABLE;
+------------+--------------+-------------+-----------+--------+----------+
| 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 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 |
+------------+--------------+-------------+-----------+--------+----------+OPTIMIZE TABLE test.ft_innodb\G
*************************** 1. row ***************************
Table: test.ft_innodb
Op: optimize
Msg_type: note
Msg_text: Table does not support optimize, doing recreate + analyze instead
*************************** 2. row ***************************
Table: test.ft_innodb
Op: optimize
Msg_type: status
Msg_text: OK
2 rows in set (2.24 sec)
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 |
+------------+--------------+-------------+-----------+--------+----------+
13 rows in set (0.00 sec)SHOW VARIABLES LIKE 'innodb_optimize_fulltext_only';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| innodb_optimize_fulltext_only | OFF |
+-------------------------------+-------+
SET GLOBAL innodb_optimize_fulltext_only =1;
OPTIMIZE TABLE test.ft_innodb;
+----------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+----------+----------+----------+
| test.ft_innodb | optimize | status | OK |
+----------------+----------+----------+----------+
SELECT * FROM INNODB_FT_INDEX_CACHE;
Empty set (0.00 sec)ELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN\G
*************************** 1. row ***************************
ID: test/fk_book_author
FOR_NAME: test/book
REF_NAME: test/author
N_COLS: 1
TYPE: 1
...SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN\G
*************************** 1. row ***************************
ID: fk_book_author
FOR_NAME: test/book
REF_NAME: test/author
N_COLS: 1
TYPE: 1ON_SSD
The field contains 1 when MariaDB detects that the table space is on a SSD based storage. 0 if not SSD or it could not be determined
SPACE
InnoDB table space id number.
NAME
Path to the table space file, without the extension.
COMPRESSED
The compressed page size, or zero if uncompressed.
LAST_SCRUB_COMPLETED
Date and time when the last scrub was completed, or NULL if never been performed.
CURRENT_SCRUB_STARTED
Date and time when the current scrub started, or NULL if never been performed.
CURRENT_SCRUB_ACTIVE_THREADS
Number of threads currently scrubbing the tablespace.
CURRENT_SCRUB_PAGE_NUMBER
Page that the scrubbing thread is currently scrubbing, or NULL if not enabled.
CURRENT_SCRUB_MAX_PAGE_NUMBER
When a scrubbing starts rotating a table space, the field contains its current size. NULL if not enabled.
bigint(21) unsigned
NO
0
A unique index identifier.
NAME
varchar(64)
NO
Index name, lowercase for all user-created indexes, or uppercase for implicitly-created indexes; PRIMARY (primary key), GEN_CLUST_INDEX (index representing primary key where there isn't one), ID_IND, FOR_IND (validating foreign key constraint) , REF_IND.
TABLE_ID
bigint(21) unsigned
NO
0
Table identifier, matching the value from .
TYPE
int(11)
NO
0
Numeric type identifier; one of 0 (secondary index), 1 (clustered index), 2 (unique index), 3 (primary index), 32 ().
N_FIELDS
int(11)
NO
0
Number of columns in the index. GEN_CLUST_INDEX's have a value of 0 as the index is not based on an actual column in the table.
PAGE_NO
int(11)
NO
0
Index B-tree's root page number. -1 (unused) for full-text indexes, as they are laid out over several auxiliary tables.
SPACE
int(11)
NO
0
Tablespace identifier where the index resides. 0 represents the InnoDB system tablespace, while any other value represents a table created in file-per-table mode (see the system variable). Remains unchanged after a statement, and not necessarily unique.
MERGE_THRESHOLD
int(11)
NO
0
This page is licensed: CC BY-SA / Gnu FDL
INDEX_ID
Table ID, matching the value.
SCHEMA
Database name (XtraDB only).
NAME
Table name, matching the value.
STATS_INITIALIZED
Initialized if statistics have already been collected, otherwise Uninitialized.
NUM_ROWS
Estimated number of rows currently in the table. Updated after each statement modifying the data, but uncommited transactions mean it may not be accurate.
CLUST_INDEX_SIZE
Number of pages on disk storing the clustered index, holding InnoDB table data in primary key order, or NULL if not statistics yet collected.
OTHER_INDEX_SIZE
Number of pages on disk storing secondary indexes for the table, or NULL if not statistics yet collected.
MODIFIED_COUNTER
Number of rows modified by statements modifying data.
AUTOINC
value.
REF_COUNT
Countdown to zero, when table metadata can be removed from the table cache. (InnoDB only)
MYSQL_HANDLES_OPENED
(XtraDB only).
This page is licensed: CC BY-SA / Gnu FDL
TABLE_ID
The INNODB_SYS_TABLES table exposes the internal InnoDB data dictionary metadata for tables, including row formats and space IDs.
The Information Schema INNODB_SYS_TABLES table contains information about InnoDB tables.
The PROCESS privilege is required to view the table.
It has the following columns:
The flag field returns the dict_table_t::flags that correspond to the data dictionary record.
Note that the table flags returned here are not the same as tablespace flags (FSP_SPACE_FLAGS).
This page is licensed: CC BY-SA / Gnu FDL
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:
TABLE_ID
Table identifier, matching the value from .
NAME
The column MTYPE uses a numeric column type identifier, which has the following values:
This page is licensed: CC BY-SA / Gnu FDL
The INNODB_TABLESPACES_ENCRYPTION table provides metadata about encrypted InnoDB tablespaces, including key versions and rotation status.
The Information Schema INNODB_TABLESPACES_ENCRYPTION table contains metadata about encrypted InnoDB tablespaces. When you enable encryption for an InnoDB tablespace, an entry for the tablespace is added to this table. If you later disable encryption for the InnoDB tablespace, 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 PROCESS privilege. It contains the following columns:
When the is encrypted, it is represented in this table with the special name: innodb_system.
This page is licensed: CC BY-SA / Gnu FDL
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:
LOCK_ID
Lock ID number - the format is not fixed, so do not rely upon the number for information.
LOCK_TRX_ID
Lock's transaction ID. Matches the column.
LOCK_MODE
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
The Information Schema INNODB_BUFFER_POOL_STATS table displays high-level statistics about the InnoDB buffer pool's activity.
The INNODB_BUFFER_POOL_STATS table contains information about pages in the , similar to what is returned with the statement.
The PROCESS is required to view the table.
It has the following columns:
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:
SELECT * FROM information_schema.INNODB_TABLESPACES_SCRUBBING LIMIT 1\G
*************************** 1. row ***************************
SPACE: 1
NAME: mysql/innodb_table_stats
COMPRESSED: 0
LAST_SCRUB_COMPLETED: NULL
CURRENT_SCRUB_STARTED: NULL
CURRENT_SCRUB_PAGE_NUMBER: NULL
CURRENT_SCRUB_MAX_PAGE_NUMBER: 0
ROTATING_OR_FLUSHING: 0
1 rows in set (0.00 sec)SELECT * FROM information_schema.INNODB_SYS_INDEXES LIMIT 3\G
*************************** 1. row ***************************
INDEX_ID: 11
NAME: ID_IND
TABLE_ID: 11
TYPE: 3
N_FIELDS: 1
PAGE_NO: 302
SPACE: 0
MERGE_THRESHOLD: 50
*************************** 2. row ***************************
INDEX_ID: 12
NAME: FOR_IND
TABLE_ID: 11
TYPE: 0
N_FIELDS: 1
PAGE_NO: 303
SPACE: 0
MERGE_THRESHOLD: 50
*************************** 3. row ***************************
INDEX_ID: 13
NAME: REF_IND
TABLE_ID: 11
TYPE: 3
N_FIELDS: 1
PAGE_NO: 304
SPACE: 0
MERGE_THRESHOLD: 50
3 rows in set (0.00 sec)File name where semaphore was requested
LINE
Line number on above file
WAIT_TIME
Wait time
WAIT_OBJECT
WAIT_TYPE
Object type (mutex, rw-lock)
HOLDER_THREAD_ID
Holder thread id
HOLDER_FILE
File name where semaphore was acquired
HOLDER_LINE
Line number for above
CREATED_FILE
Creation file name
CREATED_LINE
Line number for above
WRITER_THREAD
Last write request thread id
RESERVATION_MODE
Reservation mode (shared, exclusive)
READERS
Number of readers if only shared mode
WAITERS_FLAG
Flags
LOCK_WORD
Lock word (for developers)
LAST_READER_FILE
Removed
LAST_READER_LINE
Removed
LAST_WRITER_FILE
Last writer file name
LAST_WRITER_LINE
Above line number
OS_WAIT_COUNT
Wait count
This page is licensed: CC BY-SA / Gnu FDL
THREAD_ID
Thread id waiting for semaphore
OBJECT_NAME
Semaphore name
FILE
Normally 00, but 11 for "no-rollback tables".
TABLE_ID
bigint(21) unsigned
NO
0
Unique InnoDB table identifier.
NAME
varchar(655)
NO
Database and table name, or the uppercase InnoDB system table name.
FLAG
int(11)
NO
0
See Flag below
N_COLS
int(11) unsigned (>= MariaDB 10.5) int(11) (<= MariaDB 10.4)
NO
0
Number of columns in the table. The count includes two or three hidden InnoDB system columns, appended to the end of the column list: DB_ROW_ID (if there is no primary key or unique index on NOT NULL columns), DB_TRX_ID, DB_ROLL_PTR.
SPACE
int(11) unsigned (>= MariaDB 10.5) int(11) (<= MariaDB 10.4)
NO
0
Tablespace identifier where the index resides. 0 represents the InnoDB system tablespace, while any other value represents a table created in file-per-table mode (see the innodb_file_per_table system variable). Remains unchanged after a TRUNCATE TABLE statement.
FILE_FORMAT
varchar(10)
YES
NULL
InnoDB file format (Antelope or Barracuda).
ROW_FORMAT
enum('Redundant', 'Compact', 'Compressed', 'Dynamic') (>= MariaDB 10.5)varchar(12) (<= MariaDB 10.4)
YES
NULL
InnoDB storage format (Compact, Redundant, Dynamic, or Compressed).
ZIP_PAGE_SIZE
int(11) unsigned
NO
0
For Compressed tables, the zipped page size.
SPACE_TYPE
enum('Single','System') (>= MariaDB 10.5)varchar(10) (<= MariaDB 10.4)
YES
NULL
0
Set if ROW_FORMAT is not REDUNDANT.
1 to 4
0, except for ROW_FORMAT=COMPRESSED, where they will determine the KEY_BLOCK_SIZE (the compressed page size).
5
Set for ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED.
6
Set if the DATA DIRECTORY attribute was present when the table was originally created.
7
Set if the page_compressed attribute is present.
8 to 11
Determine the page_compression_level.
12 13
SPACE
InnoDB tablespace ID.
NAME
Path to the InnoDB tablespace file, without the extension.
ENCRYPTION_SCHEME
Key derivation algorithm. Only 1 is currently used to represent an algorithm. If this value is 0, then the tablespace is unencrypted.
KEYSERVER_REQUESTS
Number of times InnoDB has had to request a key from the encryption key management plugin. The three most recent keys are cached internally.
MIN_KEY_VERSION
Minimum key version used to encrypt a page in the tablespace. Different pages may be encrypted with different key versions.
CURRENT_KEY_VERSION
Key version that will be used to encrypt pages. If this value is 0, then the tablespace is unencrypted.
KEY_ROTATION_PAGE_NUMBER
Page that a background encryption thread is currently rotating. If key rotation is not enabled, then the value will be NULL.
KEY_ROTATION_MAX_PAGE_NUMBER
When a background encryption thread starts rotating a tablespace, the field contains its current size. If key rotation is not enabled, then the value will be NULL.
CURRENT_KEY_ID
Key ID for the encryption key currently in use.
ROTATING_OR_FLUSHING
Current key rotation status. If this value is 1, then the background encryption threads are working on the tablespace. See MDEV-11738.
Lock mode. One of S (shared), X (exclusive), IS (intention shared), IX (intention exclusive row lock), S_GAP (shared gap lock), X_GAP (exclusive gap lock), IS_GAP (intention shared gap lock), IX_GAP (intention exclusive gap lock) or AUTO_INC (auto-increment table level lock).
LOCK_TYPE
Whether the lock is RECORD (row level) or TABLE level.
LOCK_TABLE
Name of the locked table,or table containing locked rows.
LOCK_INDEX
Index name if a RECORD LOCK_TYPE, or NULL if not.
LOCK_SPACE
Tablespace ID if a RECORD LOCK_TYPE, or NULL if not.
LOCK_PAGE
Locked record page number if a RECORD LOCK_TYPE, or NULL if not.
LOCK_REC
Locked record heap number if a RECORD LOCK_TYPE, or NULL if not.
LOCK_DATA
Locked record primary key as an SQL string if a RECORD LOCK_TYPE, or NULL if not. If no primary key exists, the internal InnoDB row_id number is instead used. To avoid unnecessary IO, also NULL if the locked record page is not in the buffer pool
SELECT * FROM information_schema.INNODB_SYS_TABLES LIMIT 2\G
*************************** 1. row ***************************
TABLE_ID: 14
NAME: SYS_DATAFILES
FLAG: 0
N_COLS: 5
SPACE: 0
ROW_FORMAT: Redundant
ZIP_PAGE_SIZE: 0
SPACE_TYPE: System
*************************** 2. row ***************************
TABLE_ID: 11
NAME: SYS_FOREIGN
FLAG: 0
N_COLS: 7
SPACE: 0
ROW_FORMAT: Redundant
ZIP_PAGE_SIZE: 0
SPACE_TYPE: System
2 rows in set (0.00 sec)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)-- session 1
START TRANSACTION;
UPDATE t SET id = 15 WHERE id = 10;
-- session 2
DELETE FROM t WHERE id = 10;
-- session 1
USE information_schema;
SELECT l.*, t.*
FROM information_schema.INNODB_LOCKS l
JOIN information_schema.INNODB_TRX t
ON l.lock_trx_id = t.trx_id
WHERE trx_state = 'LOCK WAIT' \G
*************************** 1. row ***************************
lock_id: 840:40:3:2
lock_trx_id: 840
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t`
lock_index: PRIMARY
lock_space: 40
lock_page: 3
lock_rec: 2
lock_data: 10
trx_id: 840
trx_state: LOCK WAIT
trx_started: 2019-12-23 18:43:46
trx_requested_lock_id: 840:40:3:2
trx_wait_started: 2019-12-23 18:43:46
trx_weight: 2
trx_mysql_thread_id: 46
trx_query: DELETE FROM t WHERE id = 10
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_is_read_only: 0
trx_autocommit_non_locking: 07
SYS_CHILD
8
SYS
9
10
11
12
VARMYSQL
13
MYSQL
Column name.
POS
Ordinal position of the column in the table, starting from 0. This value is adjusted when columns are added or removed.
MTYPE
Numeric column type identifier, (see the table below for an explanation of its values).
PRTYPE
Binary value of the InnoDB precise type, representing the data type, character set code and nullability.
LEN
Column length. For multi-byte character sets, represents the length in bytes.
1
2
3
FIXBINARY
4
5
6
Size in pages of the buffer pool.
FREE_BUFFERS
Number of free pages in the buffer pool.
DATABASE_PAGES
Total number of pages in the buffer pool.
OLD_DATABASE_PAGES
Number of pages in the old sublist.
MODIFIED_DATABASE_PAGES
Number of dirty pages.
PENDING_DECOMPRESS
Number of pages pending decompression.
PENDING_READS
Pending buffer pool level reads.
PENDING_FLUSH_LRU
Number of pages in the LRU pending flush.
PENDING_FLUSH_LIST
Number of pages in the flush list pending flush.
PAGES_MADE_YOUNG
Pages moved from the old sublist to the new sublist.
PAGES_NOT_MADE_YOUNG
Pages that have remained in the old sublist without moving to the new sublist.
PAGES_MADE_YOUNG_RATE
Hits that cause blocks to move to the top of the new sublist.
PAGES_MADE_NOT_YOUNG_RATE
Hits that do not cause blocks to move to the top of the new sublist due to the delay not being met.
NUMBER_PAGES_READ
Number of pages read.
NUMBER_PAGES_CREATED
Number of pages created.
NUMBER_PAGES_WRITTEN
Number of pages written.
PAGES_READ_RATE
Number of pages read since the last printout divided by the time elapsed, giving pages read per second.
PAGES_CREATE_RATE
Number of pages created since the last printout divided by the time elapsed, giving pages created per second.
PAGES_WRITTEN_RATE
Number of pages written since the last printout divided by the time elapsed, giving pages written per second.
NUMBER_PAGES_GET
Number of logical read requests.
HIT_RATE
Buffer pool hit rate.
YOUNG_MAKE_PER_THOUSAND_GETS
For every 1000 gets, the number of pages made young.
NOT_YOUNG_MAKE_PER_THOUSAND_GETS
For every 1000 gets, the number of pages not made young.
NUMBER_PAGES_READ_AHEAD
Number of pages read ahead.
NUMBER_READ_AHEAD_EVICTED
Number of pages read ahead by the read-ahead thread that were later evicted without being accessed by any queries.
READ_AHEAD_RATE
Pages read ahead since the last printout divided by the time elapsed, giving read-ahead rate per second.
READ_AHEAD_EVICTED_RATE
Read-ahead pages not accessed since the last printout divided by time elapsed, giving the number of read-ahead pages evicted without access per second.
LRU_IO_TOTAL
Total least-recently used I/O.
LRU_IO_CURRENT
Least-recently used I/O for the current interval.
UNCOMPRESS_TOTAL
Total number of pages decompressed.
UNCOMPRESS_CURRENT
Number of pages decompressed in the current interval
This page is licensed: CC BY-SA / Gnu FDL
POOL_ID
Buffer Pool identifier. From returns a value of 0, since multiple InnoDB buffer pool instances has been removed.
POOL_SIZE
SELECT * FROM information_schema.INNODB_SYS_COLUMNS LIMIT 3\G
*************************** 1. row ***************************
TABLE_ID: 11
NAME: ID
POS: 0
MTYPE: 1
PRTYPE: 524292
LEN: 0
*************************** 2. row ***************************
TABLE_ID: 11
NAME: FOR_NAME
POS: 0
MTYPE: 1
PRTYPE: 524292
LEN: 0
*************************** 3. row ***************************
TABLE_ID: 11
NAME: REF_NAME
POS: 0
MTYPE: 1
PRTYPE: 524292
LEN: 0
3 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 | |
+----------------------------------+---------------------+------+-----+---------+-------+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:
NAME
Unique counter name.
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:
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
module_purge
purge
module_compress
compression
module_file
file_system
module_index
index
module_adaptive_hash
adaptive_hash_index From , if is disabled (the default), adaptive_hash_index will not be updated.
module_ibuf_system
change_buffer
module_srv
server
module_ddl
ddl
module_dml
dml
module_log
recovery
module_icp
icp
SUBSYSTEM
InnoDB subsystem. See below for the matching module to use to enable/disable monitoring this subsytem with the innodb_monitor_enable and innodb_monitor_disable system variables.
COUNT
Count since being enabled.
MAX_COUNT
Maximum value since being enabled.
MIN_COUNT
Minimum value since being enabled.
AVG_COUNT
Average value since being enabled.
COUNT_RESET
Count since last being reset.
MAX_COUNT_RESET
Maximum value since last being reset.
MIN_COUNT_RESET
Minimum value since last being reset.
AVG_COUNT_RESET
Average value since last being reset.
TIME_ENABLED
Time last enabled.
TIME_DISABLED
Time last disabled
TIME_ELAPSED
Time since enabled
TIME_RESET
Time last reset.
ENABLED
1 if enabled, 0 otherwise
TYPE
Item type; one of counter, value, status_counter, set_owner, set_member.
COMMENT
Counter description.
module_metadata
metadata
module_lock
lock
module_buffer
buffer
module_buf_page
buffer_page_io
module_os
os
module_trx
transaction
SET GLOBAL innodb_monitor_enable = icp_match;SELECT NAME, SUBSYSTEM FROM INNODB_METRICS WHERE SUBSYSTEM='icp';
+------------------+-----------+
| NAME | SUBSYSTEM |
+------------------+-----------+
| icp_attempts | icp |
| icp_no_match | icp |
| icp_out_of_range | icp |
| icp_match | icp |
+------------------+-----------+SET GLOBAL innodb_monitor_enable = module_icp;SET GLOBAL innodb_monitor_enable = 'buffer%'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 matchesThe INNODB_SYS_TABLESPACES table provides metadata about the tablespaces used by InnoDB, including space ID, page size, and filename.
The Information Schema 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 MariaDB 10.6.0, so this Information Schema table has been repurposed
to directly reflect the filesystem (fil_system.space_list).
The PROCESS privilege is required to view the table.
It has the following columns:
This page is licensed: CC BY-SA / Gnu FDL
The INNODB_TRX table stores information about currently executing InnoDB transactions, including transaction state, start time, and locks.
The INNODB_TRX table stores information about all currently executing InnoDB transactions.
It has the following columns:
TRX_REQUESTED_LOCK_ID
If TRX_STATE is LOCK_WAIT, the value of the lock being waited on. NULL if any other state.
TRX_WAIT_STARTED
If TRX_STATE is LOCK_WAIT, the time the transaction started waiting for the lock, otherwise NULL.
TRX_WEIGHT
Transaction weight, based on the number of locked rows and the number of altered rows. To resolve deadlocks, lower weighted transactions are rolled back first. Transactions that have affected non-transactional tables are always treated as having a heavier weight.
TRX_MYSQL_THREAD_ID
Thread ID from the table (note that the locking and transaction information schema tables use a different snapshot from the processlist, so records may appear in one but not the other).
TRX_QUERY
SQL that the transaction is currently running.
TRX_OPERATION_STATE
Transaction's current state, or NULL.
TRX_TABLES_IN_USE
Number of InnoDB tables currently being used for processing the current SQL statement.
TRX_TABLES_LOCKED
Number of InnoDB tables that have row locks held by the current SQL statement.
TRX_LOCK_STRUCTS
Number of locks reserved by the transaction.
TRX_LOCK_MEMORY_BYTES
Total size in bytes of the memory used to hold the lock structures for the current transaction in memory.
TRX_ROWS_LOCKED
Number of rows the current transaction has locked. locked by this transaction. An approximation, and may include rows not visible to the current transaction that are delete-marked but physically present.
TRX_ROWS_MODIFIED
Number of rows added or changed in the current transaction.
TRX_CONCURRENCY_TICKETS
Indicates how much work the current transaction can do before being swapped out, see the system variable.
TRX_ISOLATION_LEVEL
of the current transaction.
TRX_UNIQUE_CHECKS
Whether unique checks are on or off for the current transaction. Bulk data are a case where unique checks would be off.
TRX_FOREIGN_KEY_CHECKS
Whether foreign key checks are on or off for the current transaction. Bulk data are a case where foreign keys checks would be off.
TRX_LAST_FOREIGN_KEY_ERROR
Error message for the most recent foreign key error, or NULL if none.
TRX_ADAPTIVE_HASH_LATCHED
Whether the adaptive hash index is locked by the current transaction or not. One transaction at a time can change the adaptive hash index.
TRX_ADAPTIVE_HASH_TIMEOUT
Whether the adaptive hash index search latch shoild be relinquished immediately or reserved across all MariaDB calls. 0 if there is no contention on the adaptive hash index, in which case the latch is reserved until completion, otherwise counts down to zero and the latch is released after each row lookup.
TRX_IS_READ_ONLY
1 if a read-only transaction, otherwise 0.
TRX_AUTOCOMMIT_NON_LOCKING
1 if the transaction only contains this one statement, that is, a statement not using FOR UPDATE or LOCK IN SHARED MODE, and with autocommit on. If this and TRX_IS_READ_ONLY are both 1, the transaction can be optimized by the storrage engine to reduce some overheads
The table is often used in conjunction with the INNODB_LOCKS and INNODB_LOCK_WAITS tables to diagnose problematic locks and transactions.
XA transactions are not stored in this table. To see them, XA RECOVER can be used.
This page is licensed: CC BY-SA / Gnu FDL
TRX_ID
Unique transaction ID number.
TRX_STATE
Transaction execution state; one of RUNNING, LOCK WAIT, ROLLING BACK or COMMITTING.
TRX_STARTED
Time that the transaction started.
SPACE
Unique InnoDB tablespace identifier.
NAME
Database and table name separated by a backslash, or the uppercase InnoDB system table name.
FLAG
1 if a DATA DIRECTORY option has been specified in CREATE TABLE, otherwise 0.
FILE_FORMAT
ROW_FORMAT
InnoDB storage format used for this tablespace. If the Antelope file format is used, this value is always Compact or Redundant. When a table's checksum algorithm is full_crc32 (the default from ), the value can only be Compressed or NULL.
PAGE_SIZE
Page size in bytes for this tablespace. Until , this was the value of the innodb_page_size variable. From MariaDB 10.6.0, contains the physical page size of a page (previously ZIP_PAGE_SIZE).
ZIP_PAGE_SIZE
Zip page size for this tablespace. Removed in MariaDB 10.6.0.
SPACE_TYPE
Tablespace type. Can be General for general tablespaces or Single for file-per-table tablespaces. Removed .
FS_BLOCK_SIZE
File system block size.
FILE_SIZE
Maximum size of the file, uncompressed.
ALLOCATED_SIZE
Actual size of the file as per space allocated on disk.
FILENAME
Tablespace datafile path, previously part of the INNODB_SYS_DATAFILES table. Added in MariaDB 10.6.0.
-- session 1
START TRANSACTION;
UPDATE t SET id = 15 WHERE id = 10;
-- session 2
DELETE FROM t WHERE id = 10;
-- session 1
USE information_schema;
SELECT l.*, t.*
FROM information_schema.INNODB_LOCKS l
JOIN information_schema.INNODB_TRX t
ON l.lock_trx_id = t.trx_id
WHERE trx_state = 'LOCK WAIT' \G
*************************** 1. row ***************************
lock_id: 840:40:3:2
lock_trx_id: 840
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t`
lock_index: PRIMARY
lock_space: 40
lock_page: 3
lock_rec: 2
lock_data: 10
trx_id: 840
trx_state: LOCK WAIT
trx_started: 2019-12-23 18:43:46
trx_requested_lock_id: 840:40:3:2
trx_wait_started: 2019-12-23 18:43:46
trx_weight: 2
trx_mysql_thread_id: 46
trx_query: DELETE FROM t WHERE id = 10
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_is_read_only: 0
trx_autocommit_non_locking: 0DESC information_schema.innodb_sys_tablespaces;
+----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+-------+
| SPACE | int(11) unsigned | NO | | 0 | |
| NAME | varchar(655) | NO | | | |
| FLAG | int(11) unsigned | NO | | 0 | |
| ROW_FORMAT | varchar(22) | YES | | NULL | |
| PAGE_SIZE | int(11) unsigned | NO | | 0 | |
| ZIP_PAGE_SIZE | int(11) unsigned | NO | | 0 | |
| SPACE_TYPE | varchar(10) | YES | | NULL | |
| FS_BLOCK_SIZE | int(11) unsigned | NO | | 0 | |
| FILE_SIZE | bigint(21) unsigned | NO | | 0 | |
| ALLOCATED_SIZE | bigint(21) unsigned | NO | | 0 | |
+----------------+---------------------+------+-----+---------+-------+SELECT * FROM information_schema.INNODB_SYS_TABLESPACES LIMIT 2\G
*************************** 1. row ***************************
SPACE: 2
NAME: mysql/innodb_table_stats
FLAG: 33
ROW_FORMAT: Dynamic
PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
FS_BLOCK_SIZE: 4096
FILE_SIZE: 98304
ALLOCATED_SIZE: 98304
*************************** 2. row ***************************
SPACE: 3
NAME: mysql/innodb_index_stats
FLAG: 33
ROW_FORMAT: Dynamic
PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
FS_BLOCK_SIZE: 4096
FILE_SIZE: 98304
ALLOCATED_SIZE: 98304