These system tables provide detailed metadata about InnoDB tables, including their structure, storage, and other crucial properties for analysis.
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:
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.
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_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
Buffer Pool identifier. From MariaDB 10.5.1 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 related INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU table contains the same information, but with an LRU (least recently used) position rather than block id.
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
...This page is licensed: CC BY-SA / Gnu FDL
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 MariaDB 10.5.1 returns a value of 0, since multiple InnoDB buffer pool instances has been removed.
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 related INFORMATION_SCHEMA.INNODB_BUFFER_PAGE table contains the same information, but with a block id rather than LRU position.
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
...This page is licensed: CC BY-SA / Gnu FDL
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.
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
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.
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
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:
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
The Information Schema INNODB_BUFFER_POOL_STATS table contains information about pages in the buffer pool, similar to what is returned with the SHOW ENGINE INNODB STATUS statement.
The PROCESS privilege is required to view the table.
It has the following columns:
POOL_ID
Buffer Pool identifier. From MariaDB 10.5.1 returns a value of 0, since multiple InnoDB buffer pool instances has been removed.
POOL_SIZE
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 innodb_old_blocks 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
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 | |
+----------------------------------+---------------------+------+-----+---------+-------+This page is licensed: CC BY-SA / Gnu FDL
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
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
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.
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.
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.
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
...Other tables that can be used to monitor XtraDB/InnoDB compressed tables:
This page is licensed: CC BY-SA / Gnu FDL
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.
BUFFER_POOL_INSTANCE
Buffer Pool identifier. From MariaDB 10.5.1 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.
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.
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: 0Other tables that can be used to monitor InnoDB compressed tables:
This page is licensed: CC BY-SA / Gnu FDL
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:
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
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.
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 |
+---------------------------+-------+This page is licensed: CC BY-SA / Gnu FDL
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 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.
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: wwwThis page is licensed: CC BY-SA / Gnu FDL
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:
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.
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
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
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.
Note that for OPTIMIZE TABLE to process InnoDB fulltext index data, the innodb_optimize_fulltext_only 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 INNODB_FT_INDEX_TABLE table will be updated.
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)The OPTIMIZE TABLE statement has no effect, because the innodb_optimize_fulltext_only variable wasn't set:
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)This page is licensed: CC BY-SA / Gnu FDL
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
Word 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.
Note that for OPTIMIZE TABLE to process InnoDB fulltext index data, the innodb_optimize_fulltext_only 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 INNODB_FT_INDEX_TABLE table will be updated.
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 |
+------------+--------------+-------------+-----------+--------+----------+This page is licensed: CC BY-SA / Gnu FDL
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 INNODB_TRX table.
REQUESTED_LOCK_ID
Lock ID from the INNODB.LOCKS table for the waiting transaction.
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.
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
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 INNODB_TRX.TRX_ID column.
LOCK_MODE
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
The table is often used in conjunction with the INNODB_LOCK_WAITS and INNODB_TRX tables to diagnose problematic locks and transactions
-- 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: 0.
This page is licensed: CC BY-SA / Gnu FDL
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.
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.
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 innodb_monitor_enable system variable. You can either enable a variable by its name, for example:
SET GLOBAL innodb_monitor_enable = icp_match;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_metadata
metadata
module_lock
lock
module_buffer
buffer
module_buf_page
buffer_page_io
module_os
os
module_trx
transaction
module_purge
purge
module_compress
compression
module_file
file_system
module_index
index
module_adaptive_hash
adaptive_hash_index From MariaDB 10.6.2, if innodb_adaptive_hash_index 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
There are four counters in the icp subsystem:
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 |
+------------------+-----------+To enable them all, use the associated module name from the table above, module_icp.
SET GLOBAL innodb_monitor_enable = module_icp;The % wildcard, used to represent any number of characters, can also be used when naming counters, for example:
SET GLOBAL innodb_monitor_enable = 'buffer%'To disable counters, use the innodb_monitor_disable 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 innodb_monitor_reset (for individual counters) and innodb_monitor_reset_all (for all counters) system variables.
MariaDB starting with 10.6
From MariaDB 10.6, the interface was simplified by removing the following:
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: innodb_buffer_pool_pages_LRU_flushed
buffer_LRU_batch_evict_total_pages: innodb_buffer_pool_pages_LRU_freed
The intention is to eventually remove the interface entirely (see MDEV-15706).
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 matchesThis page is licensed: CC BY-SA / Gnu FDL
The INNODB_MUTEXES table monitors mutex and rw locks waits. It has the following columns:
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.
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
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 INNODB_SYS_TABLES.TABLE_ID.
NAME
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.
The column MTYPE uses a numeric column type identifier, which has the following values:
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)This page is licensed: CC BY-SA / Gnu FDL
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 MariaDB 10.6. The PROCESS privilege was required to view the table.
It contains the following columns:
SPACE
Numeric tablespace. Matches the INNODB_SYS_TABLES.SPACE value.
PATH
Tablespace datafile path.
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
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:
INDEX_ID
Index identifier, matching the value from INNODB_SYS_INDEXES.INDEX_ID.
NAME
Field name, matching the value from INNODB_SYS_COLUMNS.NAME.
POS
Ordinal position of the field within the index, starting from 0. This is adjusted as columns are removed.
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
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
Database name and foreign key name.
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.
The TYPE column provides a bit flag with information about the foreign key. This information is OR'ed together to read:
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_SYS_FOREIGN\G
*************************** 1. row ***************************
ID: mysql/innodb_index_stats_ibfk_1
FOR_NAME: mysql/innodb_index_stats
REF_NAME: mysql/innodb_table_stats
N_COLS: 2
TYPE: 0
...This page is licensed: CC BY-SA / Gnu FDL
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:
ID
Foreign key index associated with this column, matching the INNODB_SYS_FOREIGN.ID field.
FOR_COL_NAME
Child column name.
REF_COL_NAME
Parent column name.
POS
Ordinal position of the column in the table, starting from 0.
This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_SYS_INDEXES table contains information about InnoDB indexes.
The PROCESS privilege is required to view the table.
It has the following columns:
INDEX_ID
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.
TYPE
int(11)
NO
0
Numeric type identifier; one of 0 (secondary index), 1 (clustered index), 2 (unique index), 3 (primary index), 32 (full-text index).
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 innodb_file_per_table system variable). Remains unchanged after a TRUNCATE TABLE statement, and not necessarily unique.
MERGE_THRESHOLD
int(11)
NO
0
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)This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_SYS_SEMAPHORE_WAITS table is meant to contain information about current semaphore waits. At present it is not correctly populated. See MDEV-21330.
The PROCESS privilege is required to view the table.
It contains the following columns:
THREAD_ID
Thread id waiting for semaphore
OBJECT_NAME
Semaphore name
FILE
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
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:
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.
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.
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
The flag field returns the dict_table_t::flags that correspond to the data dictionary record.
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
Normally 00, but 11 for "no-rollback tables" (MariaDB 10.3 CREATE SEQUENCE). In MariaDB 10.1, these bits could be 01 or 10 for ATOMIC_WRITES=ON or ATOMIC_WRITES=OFF.
Note that the table flags returned here are not the same as tablespace flags (FSP_SPACE_FLAGS).
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)This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_SYS_TABLESPACES table contains information about InnoDB tablespaces. Until MariaDB 10.5 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:
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
InnoDB file format. Removed in MariaDB 10.3.1
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 MariaDB 10.5), the value can only be Compressed or NULL.
PAGE_SIZE
Page size in bytes for this tablespace. Until MariaDB 10.5.0, 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. Introduced MariaDB 10.2.1. Removed MariaDB 10.5.0.
FS_BLOCK_SIZE
File system block size. Introduced MariaDB 10.2.1.
FILE_SIZE
Maximum size of the file, uncompressed. Introduced MariaDB 10.2.1.
ALLOCATED_SIZE
Actual size of the file as per space allocated on disk. Introduced MariaDB 10.2.1.
FILENAME
Tablespace datafile path, previously part of the INNODB_SYS_DATAFILES table. Added in MariaDB 10.6.0.
DESC 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: 98304This page is licensed: CC BY-SA / Gnu FDL
The Information Schema 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 privilege is required to view the table.
Note that the MySQL InnoDB and Percona XtraDB versions of the tables differ (see XtraDB and InnoDB).
It contains the following columns:
TABLE_ID
Table ID, matching the INNODB_SYS_TABLES.TABLE_ID value.
SCHEMA
Database name (XtraDB only).
NAME
Table name, matching the INNODB_SYS_TABLES.NAME 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
Auto_increment 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
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:
TABLE_ID
bigint(21) unsigned
NO
0
POS
int(11) unsigned
NO
0
BASE_POS
int(11) unsigned
NO
0
This page is licensed: CC BY-SA / Gnu FDL
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:
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.
When the InnoDB system tablespace is encrypted, it is represented in this table with the special name: innodb_system.
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)This page is licensed: CC BY-SA / Gnu FDL
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:
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.
ON_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
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)This page is licensed: CC BY-SA / Gnu FDL
The Information Schema INNODB_TRX table stores information about all currently executing InnoDB transactions.
It has the following columns:
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.
TRX_REQUESTED_LOCK_ID
If TRX_STATE is LOCK_WAIT, the INNODB_LOCKS.LOCK_ID 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 PROCESSLIST 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 innodb_concurrency_tickets system variable.
TRX_ISOLATION_LEVEL
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 SELECT 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.
-- 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: 0This page is licensed: CC BY-SA / Gnu FDL
The Information Schema 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:
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.
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 |
+----------+--------------+--------+-------+----------------------+---------------+Adding a compressed table:
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 |
+----------+--------------+--------+-------+----------------------+---------------+This page is licensed: CC BY-SA / Gnu FDL