Menu:
information-schema-innodb-trx-table:: The INFORMATION_SCHEMA INNODB_TRX Table
This section provides table definitions for 'INFORMATION_SCHEMA' 'InnoDB' tables. For related information and examples, see *note innodb-information-schema::.
'INFORMATION_SCHEMA' 'InnoDB' tables can be used to monitor ongoing 'InnoDB' activity, to detect inefficiencies before they turn into issues, or to troubleshoot performance and capacity issues. As your database becomes bigger and busier, running up against the limits of your hardware capacity, you monitor and tune these aspects to keep the database running smoothly.
File: manual.info.tmp, Node: information-schema-innodb-table-reference, Next: information-schema-innodb-buffer-page-table, Prev: innodb-information-schema-tables, Up: innodb-information-schema-tables
The following table summarizes 'INFORMATION_SCHEMA' InnoDB tables. For greater detail, see the individual table descriptions.
INFORMATION_SCHEMA InnoDB Tables
Table Name Description Deprecated
*note 'INNODB_BUFFER_PAGE': information-schema-innodb-buffer-page-table.
Pages in InnoDB buffer pool
*note 'INNODB_BUFFER_PAGE_LRU': information-schema-innodb-buffer-page-lru-table.
LRU ordering of pages in InnoDB buffer pool
*note 'INNODB_BUFFER_POOL_STATS': information-schema-innodb-buffer-pool-stats-table.
InnoDB buffer pool statistics
*note 'INNODB_CMP': information-schema-innodb-cmp-table.
Status for operations related to compressed InnoDB tables
*note 'INNODB_CMP_PER_INDEX': information-schema-innodb-cmp-per-index-table.
Status for operations related to compressed InnoDB tables and indexes
*note 'INNODB_CMP_PER_INDEX_RESET': information-schema-innodb-cmp-per-index-table.
Status for operations related to compressed InnoDB tables and indexes
*note 'INNODB_CMP_RESET': information-schema-innodb-cmp-table.
Status for operations related to compressed InnoDB tables
*note 'INNODB_CMPMEM': information-schema-innodb-cmpmem-table.
Status for compressed pages within InnoDB buffer pool
*note 'INNODB_CMPMEM_RESET': information-schema-innodb-cmpmem-table.
Status for compressed pages within InnoDB buffer pool
*note 'INNODB_FT_BEING_DELETED': information-schema-innodb-ft-being-deleted-table.
Snapshot of INNODB_FT_DELETED table
*note 'INNODB_FT_CONFIG': information-schema-innodb-ft-config-table.
Metadata for InnoDB table FULLTEXT index and associated processing
*note 'INNODB_FT_DEFAULT_STOPWORD': information-schema-innodb-ft-default-stopword-table.
Default list of stopwords for InnoDB FULLTEXT indexes
*note 'INNODB_FT_DELETED': information-schema-innodb-ft-deleted-table.
Rows deleted from InnoDB table FULLTEXT index
*note 'INNODB_FT_INDEX_CACHE': information-schema-innodb-ft-index-cache-table.
Token information for newly inserted rows in InnoDB FULLTEXT index
*note 'INNODB_FT_INDEX_TABLE': information-schema-innodb-ft-index-table-table.
Inverted index information for processing text searches against InnoDB table FULLTEXT index
*note 'INNODB_LOCK_WAITS': information-schema-innodb-lock-waits-table.
InnoDB 5.7.14 transaction
lock-wait information
*note 'INNODB_LOCKS': information-schema-innodb-locks-table.
InnoDB 5.7.14 transaction lock
information
*note 'INNODB_METRICS': information-schema-innodb-metrics-table.
InnoDB performance information
*note 'INNODB_SYS_COLUMNS': information-schema-innodb-sys-columns-table.
Columns in each InnoDB table
*note 'INNODB_SYS_DATAFILES': information-schema-innodb-sys-datafiles-table.
Data file path information for InnoDB file-per-table and general tablespaces
*note 'INNODB_SYS_FIELDS': information-schema-innodb-sys-fields-table.
Key columns of InnoDB indexes
*note 'INNODB_SYS_FOREIGN': information-schema-innodb-sys-foreign-table.
InnoDB foreign-key metadata
*note 'INNODB_SYS_FOREIGN_COLS': information-schema-innodb-sys-foreign-cols-table.
InnoDB foreign-key column status information
*note 'INNODB_SYS_INDEXES': information-schema-innodb-sys-indexes-table.
InnoDB index metadata
*note 'INNODB_SYS_TABLES': information-schema-innodb-sys-tables-table.
InnoDB table metadata
*note 'INNODB_SYS_TABLESPACES': information-schema-innodb-sys-tablespaces-table.
InnoDB file-per-table, general, and undo tablespace metadata
*note 'INNODB_SYS_TABLESTATS': information-schema-innodb-sys-tablestats-table.
InnoDB table low-level status information
*note 'INNODB_SYS_VIRTUAL': information-schema-innodb-sys-virtual-table.
InnoDB virtual generated column metadata
*note 'INNODB_TEMP_TABLE_INFO': information-schema-innodb-temp-table-info-table.
Information about active user-created InnoDB temporary tables
*note 'INNODB_TRX': information-schema-innodb-trx-table.
Active InnoDB transaction information
File: manual.info.tmp, Node: information-schema-innodb-buffer-page-table, Next: information-schema-innodb-buffer-page-lru-table, Prev: information-schema-innodb-table-reference, Up: innodb-information-schema-tables
The *note 'INNODB_BUFFER_PAGE': information-schema-innodb-buffer-page-table. table provides information about each page in the 'InnoDB' buffer pool.
For related usage information and examples, see *note innodb-information-schema-buffer-pool-tables::.
Warning:
Querying the *note 'INNODB_BUFFER_PAGE': information-schema-innodb-buffer-page-table. table can affect performance. Do not query this table on a production system unless you are aware of the performance impact and have determined it to be acceptable. To avoid impacting performance on a production system, reproduce the issue you want to investigate and query buffer pool statistics on a test instance.
The *note 'INNODB_BUFFER_PAGE': information-schema-innodb-buffer-page-table. table has these columns:
'POOL_ID'
The buffer pool ID. This is an identifier to distinguish between multiple buffer pool instances.
'BLOCK_ID'
The buffer pool block ID.
'SPACE'
The tablespace ID; the same value as 'INNODB_SYS_TABLES.SPACE'.
'PAGE_NUMBER'
The page number.
'PAGE_TYPE'
The page type. The following table shows the permitted values.
INNODB_BUFFER_PAGE.PAGE_TYPE Values
Page Type Description
'ALLOCATED' Freshly allocated page
'BLOB' Uncompressed BLOB page
'COMPRESSED_BLOB2' Subsequent comp BLOB page
'COMPRESSED_BLOB' First compressed BLOB page
'EXTENT_DESCRIPTOR' Extent descriptor page
'FILE_SPACE_HEADER' File space header
'IBUF_BITMAP' Insert buffer bitmap
'IBUF_FREE_LIST' Insert buffer free list
'IBUF_INDEX' Insert buffer index
'INDEX' B-tree node
'INODE' Index node
'RTREE_INDEX' R-tree index
'SYSTEM' System page
'TRX_SYSTEM' Transaction system data
'UNDO_LOG' Undo log page
'UNKNOWN' Unknown
'FLUSH_TYPE'
The flush type.
'FIX_COUNT'
The number of threads using this block within the buffer pool. When zero, the block is eligible to be evicted.
'IS_HASHED'
Whether a hash index has been built on this page.
'NEWEST_MODIFICATION'
The Log Sequence Number of the youngest modification.
'OLDEST_MODIFICATION'
The Log Sequence Number of the oldest modification.
'ACCESS_TIME'
An abstract number used to judge the first access time of the page.
'TABLE_NAME'
The name of the table the page belongs to. This column is applicable only to pages with a 'PAGE_TYPE' value of 'INDEX'.
'INDEX_NAME'
The name of the index the page belongs to. This can be the name of a clustered index or a secondary index. This column is applicable only to pages with a 'PAGE_TYPE' value of 'INDEX'.
'NUMBER_RECORDS'
The number of records within the page.
'DATA_SIZE'
The sum of the sizes of the records. This column is applicable only to pages with a 'PAGE_TYPE' value of 'INDEX'.
'COMPRESSED_SIZE'
The compressed page size. 'NULL' for pages that are not compressed.
'PAGE_STATE'
The page state. The following table shows the permitted values.
INNODB_BUFFER_PAGE.PAGE_STATE Values
Page State Description
'FILE_PAGE' A buffered file page
'MEMORY' Contains a main memory object
'NOT_USED' In the free list
'NULL' Clean compressed pages, compressed pages in the flush list, pages used as buffer pool watch sentinels
'READY_FOR_USE' A free page
'REMOVE_HASH' Hash index should be removed before placing in the free list
'IO_FIX'
Whether any I/O is pending for this page: 'IO_NONE' = no pending I/O, 'IO_READ' = read pending, 'IO_WRITE' = write pending.
'IS_OLD'
Whether the block is in the sublist of old blocks in the LRU list.
'FREE_PAGE_CLOCK'
The value of the 'freed_page_clock' counter when the block was the last placed at the head of the LRU list. The 'freed_page_clock' counter tracks the number of blocks removed from the end of the LRU list.
Example
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE LIMIT 1\G
*************************** 1. row ***************************
POOL_ID: 0
BLOCK_ID: 0
SPACE: 97
PAGE_NUMBER: 2473
PAGE_TYPE: INDEX
FLUSH_TYPE: 1
FIX_COUNT: 0
IS_HASHED: YES
NEWEST_MODIFICATION: 733855581
OLDEST_MODIFICATION: 0
ACCESS_TIME: 3378385672
TABLE_NAME: `employees`.`salaries`
INDEX_NAME: PRIMARY
NUMBER_RECORDS: 468
DATA_SIZE: 14976
COMPRESSED_SIZE: 0
PAGE_STATE: FILE_PAGE
IO_FIX: IO_NONE
IS_OLD: YES
FREE_PAGE_CLOCK: 66Notes
This table is useful primarily for expert-level performance monitoring, or when developing performance-related extensions for MySQL.
You must have the 'PROCESS' privilege to query this table.
Use the 'INFORMATION_SCHEMA' note 'COLUMNS': information-schema-columns-table. table or the note 'SHOW COLUMNS': show-columns. statement to view additional information about the columns of this table, including data types and default values.
When tables, table rows, partitions, or indexes are deleted, associated pages remain in the buffer pool until space is required for other data. The note 'INNODB_BUFFER_PAGE': information-schema-innodb-buffer-page-table. table reports information about these pages until they are evicted from the buffer pool. For more information about how the 'InnoDB' manages buffer pool data, see note innodb-buffer-pool::.
File: manual.info.tmp, Node: information-schema-innodb-buffer-page-lru-table, Next: information-schema-innodb-buffer-pool-stats-table, Prev: information-schema-innodb-buffer-page-table, Up: innodb-information-schema-tables
The *note 'INNODB_BUFFER_PAGE_LRU': information-schema-innodb-buffer-page-lru-table. table provides information about the pages in the 'InnoDB' buffer pool; in particular, how they are ordered in the LRU list that determines which pages to evict from the buffer pool when it becomes full.
The note 'INNODB_BUFFER_PAGE_LRU': information-schema-innodb-buffer-page-lru-table. table has the same columns as the note 'INNODB_BUFFER_PAGE': information-schema-innodb-buffer-page-table. table, except that the *note 'INNODB_BUFFER_PAGE_LRU': information-schema-innodb-buffer-page-lru-table. table has 'LRU_POSITION' and 'COMPRESSED' columns instead of 'BLOCK_ID' and 'PAGE_STATE' columns.
For related usage information and examples, see *note innodb-information-schema-buffer-pool-tables::.
Warning:
Querying the *note 'INNODB_BUFFER_PAGE_LRU': information-schema-innodb-buffer-page-lru-table. table can affect performance. Do not query this table on a production system unless you are aware of the performance impact and have determined it to be acceptable. To avoid impacting performance on a production system, reproduce the issue you want to investigate and query buffer pool statistics on a test instance.
The *note 'INNODB_BUFFER_PAGE_LRU': information-schema-innodb-buffer-page-lru-table. table has these columns:
'POOL_ID'
The buffer pool ID. This is an identifier to distinguish between multiple buffer pool instances.
'LRU_POSITION'
The position of the page in the LRU list.
'SPACE'
The tablespace ID; the same value as 'INNODB_SYS_TABLES.SPACE'.
'PAGE_NUMBER'
The page number.
'PAGE_TYPE'
The page type. The following table shows the permitted values.
INNODB_BUFFER_PAGE_LRU.PAGE_TYPE Values
Page Type Description
'ALLOCATED' Freshly allocated page
'BLOB' Uncompressed BLOB page
'COMPRESSED_BLOB2' Subsequent comp BLOB page
'COMPRESSED_BLOB' First compressed BLOB page
'EXTENT_DESCRIPTOR' Extent descriptor page
'FILE_SPACE_HEADER' File space header
'IBUF_BITMAP' Insert buffer bitmap
'IBUF_FREE_LIST' Insert buffer free list
'IBUF_INDEX' Insert buffer index
'INDEX' B-tree node
'INODE' Index node
'RTREE_INDEX' R-tree index
'SYSTEM' System page
'TRX_SYSTEM' Transaction system data
'UNDO_LOG' Undo log page
'UNKNOWN' Unknown
'FLUSH_TYPE'
The flush type.
'FIX_COUNT'
The number of threads using this block within the buffer pool. When zero, the block is eligible to be evicted.
'IS_HASHED'
Whether a hash index has been built on this page.
'NEWEST_MODIFICATION'
The Log Sequence Number of the youngest modification.
'OLDEST_MODIFICATION'
The Log Sequence Number of the oldest modification.
'ACCESS_TIME'
An abstract number used to judge the first access time of the page.
'TABLE_NAME'
The name of the table the page belongs to. This column is applicable only to pages with a 'PAGE_TYPE' value of 'INDEX'.
'INDEX_NAME'
The name of the index the page belongs to. This can be the name of a clustered index or a secondary index. This column is applicable only to pages with a 'PAGE_TYPE' value of 'INDEX'.
'NUMBER_RECORDS'
The number of records within the page.
'DATA_SIZE'
The sum of the sizes of the records. This column is applicable only to pages with a 'PAGE_TYPE' value of 'INDEX'.
'COMPRESSED_SIZE'
The compressed page size. 'NULL' for pages that are not compressed.
'COMPRESSED'
Whether the page is compressed.
'IO_FIX'
Whether any I/O is pending for this page: 'IO_NONE' = no pending I/O, 'IO_READ' = read pending, 'IO_WRITE' = write pending.
'IS_OLD'
Whether the block is in the sublist of old blocks in the LRU list.
'FREE_PAGE_CLOCK'
The value of the 'freed_page_clock' counter when the block was the last placed at the head of the LRU list. The 'freed_page_clock' counter tracks the number of blocks removed from the end of the LRU list.
Example
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU LIMIT 1\G
*************************** 1. row ***************************
POOL_ID: 0
LRU_POSITION: 0
SPACE: 97
PAGE_NUMBER: 1984
PAGE_TYPE: INDEX
FLUSH_TYPE: 1
FIX_COUNT: 0
IS_HASHED: YES
NEWEST_MODIFICATION: 719490396
OLDEST_MODIFICATION: 0
ACCESS_TIME: 3378383796
TABLE_NAME: `employees`.`salaries`
INDEX_NAME: PRIMARY
NUMBER_RECORDS: 468
DATA_SIZE: 14976
COMPRESSED_SIZE: 0
COMPRESSED: NO
IO_FIX: IO_NONE
IS_OLD: YES
FREE_PAGE_CLOCK: 0Notes
This table is useful primarily for expert-level performance monitoring, or when developing performance-related extensions for MySQL.
You must have the 'PROCESS' privilege to query this table.
Use the 'INFORMATION_SCHEMA' note 'COLUMNS': information-schema-columns-table. table or the note 'SHOW COLUMNS': show-columns. statement to view additional information about the columns of this table, including data types and default values.
Querying this table can require MySQL to allocate a large block of contiguous memory, more than 64 bytes times the number of active pages in the buffer pool. This allocation could potentially cause an out-of-memory error, especially for systems with multi-gigabyte buffer pools.
Querying this table requires MySQL to lock the data structure representing the buffer pool while traversing the LRU list, which can reduce concurrency, especially for systems with multi-gigabyte buffer pools.
When tables, table rows, partitions, or indexes are deleted, associated pages remain in the buffer pool until space is required for other data. The note 'INNODB_BUFFER_PAGE_LRU': information-schema-innodb-buffer-page-lru-table. table reports information about these pages until they are evicted from the buffer pool. For more information about how the 'InnoDB' manages buffer pool data, see note innodb-buffer-pool::.
File: manual.info.tmp, Node: information-schema-innodb-buffer-pool-stats-table, Next: information-schema-innodb-cmp-table, Prev: information-schema-innodb-buffer-page-lru-table, Up: innodb-information-schema-tables
The note 'INNODB_BUFFER_POOL_STATS': information-schema-innodb-buffer-pool-stats-table. table provides much of the same buffer pool information provided in note 'SHOW ENGINE INNODB STATUS': show-engine. output. Much of the same information may also be obtained using 'InnoDB' buffer pool *note server status variables: server-status-variables.
The idea of making pages in the buffer pool 'young' or 'not young' refers to transferring them between the sublists at the head and tail of the buffer pool data structure. Pages made 'young' take longer to age out of the buffer pool, while pages made 'not young' are moved much closer to the point of eviction.
For related usage information and examples, see *note innodb-information-schema-buffer-pool-tables::.
The *note 'INNODB_BUFFER_POOL_STATS': information-schema-innodb-buffer-pool-stats-table. table has these columns:
'POOL_ID'
The buffer pool ID. This is an identifier to distinguish between multiple buffer pool instances.
'POOL_SIZE'
The 'InnoDB' buffer pool size in pages.
'FREE_BUFFERS'
The number of free pages in the 'InnoDB' buffer pool.
'DATABASE_PAGES'
The number of pages in the 'InnoDB' buffer pool containing data. This number includes both dirty and clean pages.
'OLD_DATABASE_PAGES'
The number of pages in the 'old' buffer pool sublist.
'MODIFIED_DATABASE_PAGES'
The number of modified (dirty) database pages.
'PENDING_DECOMPRESS'
The number of pages pending decompression.
'PENDING_READS'
The number of pending reads.
'PENDING_FLUSH_LRU'
The number of pages pending flush in the LRU.
'PENDING_FLUSH_LIST'
The number of pages pending flush in the flush list.
'PAGES_MADE_YOUNG'
The number of pages made young.
'PAGES_NOT_MADE_YOUNG'
The number of pages not made young.
'PAGES_MADE_YOUNG_RATE'
The number of pages made young per second (pages made young since the last printout / time elapsed).
'PAGES_MADE_NOT_YOUNG_RATE'
The number of pages not made per second (pages not made young since the last printout / time elapsed).
'NUMBER_PAGES_READ'
The number of pages read.
'NUMBER_PAGES_CREATED'
The number of pages created.
'NUMBER_PAGES_WRITTEN'
The number of pages written.
'PAGES_READ_RATE'
The number of pages read per second (pages read since the last printout / time elapsed).
'PAGES_CREATE_RATE'
The number of pages created per second (pages created since the last printout / time elapsed).
'PAGES_WRITTEN_RATE'
The number of pages written per second (pages written since the last printout / time elapsed).
'NUMBER_PAGES_GET'
The number of logical read requests.
'HIT_RATE'
The buffer pool hit rate.
'YOUNG_MAKE_PER_THOUSAND_GETS'
The number of pages made young per thousand gets.
'NOT_YOUNG_MAKE_PER_THOUSAND_GETS'
The number of pages not made young per thousand gets.
'NUMBER_PAGES_READ_AHEAD'
The number of pages read ahead.
'NUMBER_READ_AHEAD_EVICTED'
The number of pages read into the 'InnoDB' buffer pool by the read-ahead background thread that were subsequently evicted without having been accessed by queries.
'READ_AHEAD_RATE'
The read-ahead rate per second (pages read ahead since the last printout / time elapsed).
'READ_AHEAD_EVICTED_RATE'
The number of read-ahead pages evicted without access per second (read-ahead pages not accessed since the last printout / time elapsed).
'LRU_IO_TOTAL'
Total LRU I/O.
'LRU_IO_CURRENT'
LRU I/O for the current interval.
'UNCOMPRESS_TOTAL'
The total number of pages decompressed.
'UNCOMPRESS_CURRENT'
The number of pages decompressed in the current interval.
Example
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS\G
*************************** 1. row ***************************
POOL_ID: 0
POOL_SIZE: 8192
FREE_BUFFERS: 1
DATABASE_PAGES: 8085
OLD_DATABASE_PAGES: 2964
MODIFIED_DATABASE_PAGES: 0
PENDING_DECOMPRESS: 0
PENDING_READS: 0
PENDING_FLUSH_LRU: 0
PENDING_FLUSH_LIST: 0
PAGES_MADE_YOUNG: 22821
PAGES_NOT_MADE_YOUNG: 3544303
PAGES_MADE_YOUNG_RATE: 357.62602199870594
PAGES_MADE_NOT_YOUNG_RATE: 0
NUMBER_PAGES_READ: 2389
NUMBER_PAGES_CREATED: 12385
NUMBER_PAGES_WRITTEN: 13111
PAGES_READ_RATE: 0
PAGES_CREATE_RATE: 0
PAGES_WRITTEN_RATE: 0
NUMBER_PAGES_GET: 33322210
HIT_RATE: 1000
YOUNG_MAKE_PER_THOUSAND_GETS: 18
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
NUMBER_PAGES_READ_AHEAD: 2024
NUMBER_READ_AHEAD_EVICTED: 0
READ_AHEAD_RATE: 0
READ_AHEAD_EVICTED_RATE: 0
LRU_IO_TOTAL: 0
LRU_IO_CURRENT: 0
UNCOMPRESS_TOTAL: 0
UNCOMPRESS_CURRENT: 0Notes
This table is useful primarily for expert-level performance monitoring, or when developing performance-related extensions for MySQL.
You must have the 'PROCESS' privilege to query this table.
Use the 'INFORMATION_SCHEMA' note 'COLUMNS': information-schema-columns-table. table or the note 'SHOW COLUMNS': show-columns. statement to view additional information about the columns of this table, including data types and default values.
File: manual.info.tmp, Node: information-schema-innodb-cmp-table, Next: information-schema-innodb-cmpmem-table, Prev: information-schema-innodb-buffer-pool-stats-table, Up: innodb-information-schema-tables
The note 'INNODB_CMP': information-schema-innodb-cmp-table. and note 'INNODB_CMP_RESET': information-schema-innodb-cmp-table. tables provide status information on operations related to compressed 'InnoDB' tables.
The note 'INNODB_CMP': information-schema-innodb-cmp-table. and note 'INNODB_CMP_RESET': information-schema-innodb-cmp-table. tables have these columns:
'PAGE_SIZE'
The compressed page size in bytes.
'COMPRESS_OPS'
The number of times a B-tree page of size 'PAGE_SIZE' has been compressed. Pages are compressed whenever an empty page is created or the space for the uncompressed modification log runs out.
'COMPRESS_OPS_OK'
The number of times a B-tree page of size 'PAGE_SIZE' has been successfully compressed. This count should never exceed 'COMPRESS_OPS'.
'COMPRESS_TIME'
The total time in seconds used for attempts to compress B-tree pages of size 'PAGE_SIZE'.
'UNCOMPRESS_OPS'
The number of times a B-tree page of size 'PAGE_SIZE' has been uncompressed. B-tree pages are uncompressed whenever compression fails or at first access when the uncompressed page does not exist in the buffer pool.
'UNCOMPRESS_TIME'
The total time in seconds used for uncompressing B-tree pages of the size 'PAGE_SIZE'.
Example
mysql> 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
*************************** 2. row ***************************
page_size: 2048
compress_ops: 0
compress_ops_ok: 0
compress_time: 0
uncompress_ops: 0
uncompress_time: 0
*************************** 3. row ***************************
page_size: 4096
compress_ops: 0
compress_ops_ok: 0
compress_time: 0
uncompress_ops: 0
uncompress_time: 0
*************************** 4. row ***************************
page_size: 8192
compress_ops: 86955
compress_ops_ok: 81182
compress_time: 27
uncompress_ops: 26828
uncompress_time: 5
*************************** 5. row ***************************
page_size: 16384
compress_ops: 0
compress_ops_ok: 0
compress_time: 0
uncompress_ops: 0
uncompress_time: 0Notes
Use these tables to measure the effectiveness of 'InnoDB' table compression in your database.
You must have the 'PROCESS' privilege to query this table.
Use the 'INFORMATION_SCHEMA' note 'COLUMNS': information-schema-columns-table. table or the note 'SHOW COLUMNS': show-columns. statement to view additional information about the columns of this table, including data types and default values.
For usage information, see note innodb-compression-tuning-monitoring:: and note innodb-information-schema-examples-compression-sect::. For general information about 'InnoDB' table compression, see *note innodb-compression::.
File: manual.info.tmp, Node: information-schema-innodb-cmpmem-table, Next: information-schema-innodb-cmp-per-index-table, Prev: information-schema-innodb-cmp-table, Up: innodb-information-schema-tables
The note 'INNODB_CMPMEM': information-schema-innodb-cmpmem-table. and note 'INNODB_CMPMEM_RESET': information-schema-innodb-cmpmem-table. tables provide status information on compressed pages within the 'InnoDB' buffer pool.
The note 'INNODB_CMPMEM': information-schema-innodb-cmpmem-table. and note 'INNODB_CMPMEM_RESET': information-schema-innodb-cmpmem-table. tables have these columns:
'PAGE_SIZE'
The block size in bytes. Each record of this table describes blocks of this size.
'BUFFER_POOL_INSTANCE'
A unique identifier for the buffer pool instance.
'PAGES_USED'
The number of blocks of size 'PAGE_SIZE' that are currently in use.
'PAGES_FREE'
The number of blocks of size 'PAGE_SIZE' that are currently available for allocation. This column shows the external fragmentation in the memory pool. Ideally, these numbers should be at most 1.
'RELOCATION_OPS'
The number of times a block of size 'PAGE_SIZE' has been relocated. The buddy system can relocate the allocated 'buddy neighbor' of a freed block when it tries to form a bigger freed block. Reading from the *note 'INNODB_CMPMEM_RESET': information-schema-innodb-cmpmem-table. table resets this count.
'RELOCATION_TIME'
The total time in microseconds used for relocating blocks of size 'PAGE_SIZE'. Reading from the table 'INNODB_CMPMEM_RESET' resets this count.
Example
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_CMPMEM\G
*************************** 1. row ***************************
page_size: 1024
buffer_pool_instance: 0
pages_used: 0
pages_free: 0
relocation_ops: 0
relocation_time: 0
*************************** 2. row ***************************
page_size: 2048
buffer_pool_instance: 0
pages_used: 0
pages_free: 0
relocation_ops: 0
relocation_time: 0
*************************** 3. row ***************************
page_size: 4096
buffer_pool_instance: 0
pages_used: 0
pages_free: 0
relocation_ops: 0
relocation_time: 0
*************************** 4. row ***************************
page_size: 8192
buffer_pool_instance: 0
pages_used: 7673
pages_free: 15
relocation_ops: 4638
relocation_time: 0
*************************** 5. row ***************************
page_size: 16384
buffer_pool_instance: 0
pages_used: 0
pages_free: 0
relocation_ops: 0
relocation_time: 0Notes
Use these tables to measure the effectiveness of 'InnoDB' table compression in your database.
You must have the 'PROCESS' privilege to query this table.
Use the 'INFORMATION_SCHEMA' note 'COLUMNS': information-schema-columns-table. table or the note 'SHOW COLUMNS': show-columns. statement to view additional information about the columns of this table, including data types and default values.
For usage information, see note innodb-compression-tuning-monitoring:: and note innodb-information-schema-examples-compression-sect::. For general information about 'InnoDB' table compression, see *note innodb-compression::.
File: manual.info.tmp, Node: information-schema-innodb-cmp-per-index-table, Next: information-schema-innodb-ft-being-deleted-table, Prev: information-schema-innodb-cmpmem-table, Up: innodb-information-schema-tables
The note 'INNODB_CMP_PER_INDEX': information-schema-innodb-cmp-per-index-table. and note 'INNODB_CMP_PER_INDEX_RESET': information-schema-innodb-cmp-per-index-table. tables provide status information on operations related to compressed 'InnoDB' tables and indexes, with separate statistics for each combination of database, table, and index, to help you evaluate the performance and usefulness of compression for specific tables.
For a compressed 'InnoDB' table, both the table data and all the secondary indexes are compressed. In this context, the table data is treated as just another index, one that happens to contain all the columns: the clustered index.
The note 'INNODB_CMP_PER_INDEX': information-schema-innodb-cmp-per-index-table. and note 'INNODB_CMP_PER_INDEX_RESET': information-schema-innodb-cmp-per-index-table. tables have these columns:
'DATABASE_NAME'
The schema (database) containing the applicable table.
'TABLE_NAME'
The table to monitor for compression statistics.
'INDEX_NAME'
The index to monitor for compression statistics.
'COMPRESS_OPS'
The number of compression operations attempted. Pages are compressed whenever an empty page is created or the space for the uncompressed modification log runs out.
'COMPRESS_OPS_OK'
The number of successful compression operations. Subtract from the 'COMPRESS_OPS' value to get the number of compression failures. Divide by the 'COMPRESS_OPS' value to get the percentage of compression failures.
'COMPRESS_TIME'
The total time in seconds used for compressing data in this index.
'UNCOMPRESS_OPS'
The number of uncompression operations performed. Compressed 'InnoDB' pages are uncompressed whenever compression fails, or the first time a compressed page is accessed in the buffer pool and the uncompressed page does not exist.
'UNCOMPRESS_TIME'
The total time in seconds used for uncompressing data in this index.
Example
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX\G
*************************** 1. row ***************************
database_name: employees
table_name: salaries
index_name: PRIMARY
compress_ops: 0
compress_ops_ok: 0
compress_time: 0
uncompress_ops: 23451
uncompress_time: 4
*************************** 2. row ***************************
database_name: employees
table_name: salaries
index_name: emp_no
compress_ops: 0
compress_ops_ok: 0
compress_time: 0
uncompress_ops: 1597
uncompress_time: 0Notes
Use these tables to measure the effectiveness of 'InnoDB' table compression for specific tables, indexes, or both.
You must have the 'PROCESS' privilege to query these tables.
Use the 'INFORMATION_SCHEMA' note 'COLUMNS': information-schema-columns-table. table or the note 'SHOW COLUMNS': show-columns. statement to view additional information about the columns of these tables, including data types and default values.
Because collecting separate measurements for every index imposes substantial performance overhead, note 'INNODB_CMP_PER_INDEX': information-schema-innodb-cmp-per-index-table. and note 'INNODB_CMP_PER_INDEX_RESET': information-schema-innodb-cmp-per-index-table. statistics are not gathered by default. You must enable the 'innodb_cmp_per_index_enabled' system variable before performing the operations on compressed tables that you want to monitor.
For usage information, see note innodb-compression-tuning-monitoring:: and note innodb-information-schema-examples-compression-sect::. For general information about 'InnoDB' table compression, see *note innodb-compression::.
File: manual.info.tmp, Node: information-schema-innodb-ft-being-deleted-table, Next: information-schema-innodb-ft-config-table, Prev: information-schema-innodb-cmp-per-index-table, Up: innodb-information-schema-tables
The note 'INNODB_FT_BEING_DELETED': information-schema-innodb-ft-being-deleted-table. table provides a snapshot of the note 'INNODB_FT_DELETED': information-schema-innodb-ft-deleted-table. table; it is used only during an note 'OPTIMIZE TABLE': optimize-table. maintenance operation. When note 'OPTIMIZE TABLE': optimize-table. is run, the note 'INNODB_FT_BEING_DELETED': information-schema-innodb-ft-being-deleted-table. table is emptied, and 'DOC_ID' values are removed from the note 'INNODB_FT_DELETED': information-schema-innodb-ft-deleted-table. table. Because the contents of note 'INNODB_FT_BEING_DELETED': information-schema-innodb-ft-being-deleted-table. typically have a short lifetime, this table has limited utility for monitoring or debugging. For information about running note 'OPTIMIZE TABLE': optimize-table. on tables with 'FULLTEXT' indexes, see *note fulltext-fine-tuning::.
This table is empty initially. Before querying it, set the value of the 'innodb_ft_aux_table' system variable to the name (including the database name) of the table that contains the 'FULLTEXT' index; for example 'test/articles'. The output appears similar to the example provided for the *note 'INNODB_FT_DELETED': information-schema-innodb-ft-deleted-table. table.
For related usage information and examples, see *note innodb-information-schema-fulltext_index-tables::.
The *note 'INNODB_FT_BEING_DELETED': information-schema-innodb-ft-being-deleted-table. table has these columns:
'DOC_ID'
The document ID of the row that is in the process of being deleted. This value might reflect the value of an ID column that you defined for the underlying table, or it can be a sequence value generated by 'InnoDB' when the table contains no suitable column. This value is used when you do text searches, to skip rows in the note 'INNODB_FT_INDEX_TABLE': information-schema-innodb-ft-index-table-table. table before data for deleted rows is physically removed from the 'FULLTEXT' index by an note 'OPTIMIZE TABLE': optimize-table. statement. For more information, see *note fulltext-optimize::.
Notes
You must have the 'PROCESS' privilege to query this table.
Use the 'INFORMATION_SCHEMA' note 'COLUMNS': information-schema-columns-table. table or the note 'SHOW COLUMNS': show-columns. statement to view additional information about the columns of this table, including data types and default values.
For more information about 'InnoDB' 'FULLTEXT' search, see note innodb-fulltext-index::, and note fulltext-search::.
File: manual.info.tmp, Node: information-schema-innodb-ft-config-table, Next: information-schema-innodb-ft-default-stopword-table, Prev: information-schema-innodb-ft-being-deleted-table, Up: innodb-information-schema-tables
The *note 'INNODB_FT_CONFIG': information-schema-innodb-ft-config-table. table provides metadata about the 'FULLTEXT' index and associated processing for an 'InnoDB' table.
This table is empty initially. Before querying it, set the value of the 'innodb_ft_aux_table' system variable to the name (including the database name) of the table that contains the 'FULLTEXT' index; for example 'test/articles'.
For related usage information and examples, see *note innodb-information-schema-fulltext_index-tables::.
The *note 'INNODB_FT_CONFIG': information-schema-innodb-ft-config-table. table has these columns:
'KEY'
The name designating an item of metadata for an 'InnoDB' table containing a 'FULLTEXT' index.
The values for this column might change, depending on the needs for performance tuning and debugging for 'InnoDB' full-text processing. The key names and their meanings include:
* 'optimize_checkpoint_limit': The number of seconds after which
an *note 'OPTIMIZE TABLE': optimize-table. run stops.
* 'synced_doc_id': The next 'DOC_ID' to be issued.
* 'stopword_table_name': The DATABASE/TABLE name for a
user-defined stopword table. The 'VALUE' column is empty if
there is no user-defined stopword table.
* 'use_stopword': Indicates whether a stopword table is used,
which is defined when the 'FULLTEXT' index is created.'VALUE'
The value associated with the corresponding 'KEY' column, reflecting some limit or current value for an aspect of a 'FULLTEXT' index for an 'InnoDB' table.
Example
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_CONFIG;
+---------------------------+-------------------+
| KEY | VALUE |
+---------------------------+-------------------+
| optimize_checkpoint_limit | 180 |
| synced_doc_id | 0 |
| stopword_table_name | test/my_stopwords |
| use_stopword | 1 |
+---------------------------+-------------------+Notes
This table is intended only for internal configuration. It is not intended for statistical information purposes.
You must have the 'PROCESS' privilege to query this table.
Use the 'INFORMATION_SCHEMA' note 'COLUMNS': information-schema-columns-table. table or the note 'SHOW COLUMNS': show-columns. statement to view additional information about the columns of this table, including data types and default values.
For more information about 'InnoDB' 'FULLTEXT' search, see note innodb-fulltext-index::, and note fulltext-search::.
File: manual.info.tmp, Node: information-schema-innodb-ft-default-stopword-table, Next: information-schema-innodb-ft-deleted-table, Prev: information-schema-innodb-ft-config-table, Up: innodb-information-schema-tables
The note 'INNODB_FT_DEFAULT_STOPWORD': information-schema-innodb-ft-default-stopword-table. table holds a list of stopwords that are used by default when creating a 'FULLTEXT' index on 'InnoDB' tables. For information about the default 'InnoDB' stopword list and how to define your own stopword lists, see note fulltext-stopwords::.
For related usage information and examples, see *note innodb-information-schema-fulltext_index-tables::.
The *note 'INNODB_FT_DEFAULT_STOPWORD': information-schema-innodb-ft-default-stopword-table. table has these columns:
'value'
A word that is used by default as a stopword for 'FULLTEXT' indexes on 'InnoDB' tables. This is not used if you override the default stopword processing with either the 'innodb_ft_server_stopword_table' or the 'innodb_ft_user_stopword_table' system variable.
Example
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
+-------+
| value |
+-------+
| a |
| about |
| an |
| are |
| as |
| at |
| be |
| by |
| com |
| de |
| en |
| for |
| from |
| how |
| i |
| in |
| is |
| it |
| la |
| of |
| on |
| or |
| that |
| the |
| this |
| to |
| was |
| what |
| when |
| where |
| who |
| will |
| with |
| und |
| the |
| www |
+-------+
36 rows in set (0.00 sec)Notes
You must have the 'PROCESS' privilege to query this table.
Use the 'INFORMATION_SCHEMA' note 'COLUMNS': information-schema-columns-table. table or the note 'SHOW COLUMNS': show-columns. statement to view additional information about the columns of this table, including data types and default values.
For more information about 'InnoDB' 'FULLTEXT' search, see note innodb-fulltext-index::, and note fulltext-search::.
File: manual.info.tmp, Node: information-schema-innodb-ft-deleted-table, Next: information-schema-innodb-ft-index-cache-table, Prev: information-schema-innodb-ft-default-stopword-table, Up: innodb-information-schema-tables
The note 'INNODB_FT_DELETED': information-schema-innodb-ft-deleted-table. table stores rows that are deleted from the 'FULLTEXT' index for an 'InnoDB' table. To avoid expensive index reorganization during DML operations for an 'InnoDB' 'FULLTEXT' index, the information about newly deleted words is stored separately, filtered out of search results when you do a text search, and removed from the main search index only when you issue an note 'OPTIMIZE TABLE': optimize-table. statement for the 'InnoDB' table. For more information, see *note fulltext-optimize::.
This table is empty initially. Before querying it, set the value of the 'innodb_ft_aux_table' system variable to the name (including the database name) of the table that contains the 'FULLTEXT' index; for example 'test/articles'.
For related usage information and examples, see *note innodb-information-schema-fulltext_index-tables::.
The *note 'INNODB_FT_DELETED': information-schema-innodb-ft-deleted-table. table has these columns:
'DOC_ID'
The document ID of the newly deleted row. This value might reflect the value of an ID column that you defined for the underlying table, or it can be a sequence value generated by 'InnoDB' when the table contains no suitable column. This value is used when you do text searches, to skip rows in the note 'INNODB_FT_INDEX_TABLE': information-schema-innodb-ft-index-table-table. table before data for deleted rows is physically removed from the 'FULLTEXT' index by an note 'OPTIMIZE TABLE': optimize-table. statement. For more information, see *note fulltext-optimize::.
Example
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
+--------+
| DOC_ID |
+--------+
| 6 |
| 7 |
| 8 |
+--------+Notes
You must have the 'PROCESS' privilege to query this table.
Use the 'INFORMATION_SCHEMA' note 'COLUMNS': information-schema-columns-table. table or the note 'SHOW COLUMNS': show-columns. statement to view additional information about the columns of this table, including data types and default values.
For more information about 'InnoDB' 'FULLTEXT' search, see note innodb-fulltext-index::, and note fulltext-search::.
File: manual.info.tmp, Node: information-schema-innodb-ft-index-cache-table, Next: information-schema-innodb-ft-index-table-table, Prev: information-schema-innodb-ft-deleted-table, Up: innodb-information-schema-tables
The note 'INNODB_FT_INDEX_CACHE': information-schema-innodb-ft-index-cache-table. table provides token information about newly inserted rows in a 'FULLTEXT' index. To avoid expensive index reorganization during DML operations, the information about newly indexed words is stored separately, and combined with the main search index only when note 'OPTIMIZE TABLE': optimize-table. is run, when the server is shut down, or when the cache size exceeds a limit defined by the 'innodb_ft_cache_size' or 'innodb_ft_total_cache_size' system variable.
This table is empty initially. Before querying it, set the value of the 'innodb_ft_aux_table' system variable to the name (including the database name) of the table that contains the 'FULLTEXT' index; for example 'test/articles'.
For related usage information and examples, see *note innodb-information-schema-fulltext_index-tables::.
The *note 'INNODB_FT_INDEX_CACHE': information-schema-innodb-ft-index-cache-table. table has these columns:
'WORD'
A word extracted from the text of a newly inserted row.
'FIRST_DOC_ID'
The first document ID in which this word appears in the 'FULLTEXT' index.
'LAST_DOC_ID'
The last document ID in which this word appears in the 'FULLTEXT' index.
'DOC_COUNT'
The number of rows in which this word appears in the 'FULLTEXT' index. The same word can occur several times within the cache table, once for each combination of 'DOC_ID' and 'POSITION' values.
'DOC_ID'
The document ID of the newly inserted row. This value might reflect the value of an ID column that you defined for the underlying table, or it can be a sequence value generated by 'InnoDB' when the table contains no suitable column.
'POSITION'
The position of this particular instance of the word within the relevant document identified by the 'DOC_ID' value. The value does not represent an absolute position; it is an offset added to the 'POSITION' of the previous instance of that word.
Notes
This table is empty initially. Before querying it, set the value of the 'innodb_ft_aux_table' system variable to the name (including the database name) of the table that contains the 'FULLTEXT' index; for example 'test/articles'. The following example demonstrates how to use the 'innodb_ft_aux_table' system variable to show information about a 'FULLTEXT' index for a specified table.
mysql> USE test;
mysql> CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
) ENGINE=InnoDB;
mysql> INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');
mysql> SET GLOBAL innodb_ft_aux_table = 'test/articles';
mysql> SELECT WORD, DOC_COUNT, DOC_ID, POSITION
FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE LIMIT 5;
+------------+-----------+--------+----------+
| WORD | DOC_COUNT | DOC_ID | POSITION |
+------------+-----------+--------+----------+
| 1001 | 1 | 4 | 0 |
| after | 1 | 2 | 22 |
| comparison | 1 | 5 | 44 |
| configured | 1 | 6 | 20 |
| database | 2 | 1 | 31 |
+------------+-----------+--------+----------+You must have the 'PROCESS' privilege to query this table.
Use the 'INFORMATION_SCHEMA' note 'COLUMNS': information-schema-columns-table. table or the note 'SHOW COLUMNS': show-columns. statement to view additional information about the columns of this table, including data types and default values.
For more information about 'InnoDB' 'FULLTEXT' search, see note innodb-fulltext-index::, and note fulltext-search::.
File: manual.info.tmp, Node: information-schema-innodb-ft-index-table-table, Next: information-schema-innodb-locks-table, Prev: information-schema-innodb-ft-index-cache-table, Up: innodb-information-schema-tables
The *note 'INNODB_FT_INDEX_TABLE': information-schema-innodb-ft-index-table-table. table provides information about the inverted index used to process text searches against the 'FULLTEXT' index of an 'InnoDB' table.
This table is empty initially. Before querying it, set the value of the 'innodb_ft_aux_table' system variable to the name (including the database name) of the table that contains the 'FULLTEXT' index; for example 'test/articles'.
For related usage information and examples, see *note innodb-information-schema-fulltext_index-tables::.
The *note 'INNODB_FT_INDEX_TABLE': information-schema-innodb-ft-index-table-table. table has these columns:
'WORD'
A word extracted from the text of the columns that are part of a 'FULLTEXT'.
'FIRST_DOC_ID'
The first document ID in which this word appears in the 'FULLTEXT' index.
'LAST_DOC_ID'
The last document ID in which this word appears in the 'FULLTEXT' index.
'DOC_COUNT'
The number of rows in which this word appears in the 'FULLTEXT' index. The same word can occur several times within the cache table, once for each combination of 'DOC_ID' and 'POSITION' values.
'DOC_ID'
The document ID of the row containing the word. This value might reflect the value of an ID column that you defined for the underlying table, or it can be a sequence value generated by 'InnoDB' when the table contains no suitable column.
'POSITION'
The position of this particular instance of the word within the relevant document identified by the 'DOC_ID' value.
Notes
This table is empty initially. Before querying it, set the value of the 'innodb_ft_aux_table' system variable to the name (including the database name) of the table that contains the 'FULLTEXT' index; for example 'test/articles'. The following example demonstrates how to use the 'innodb_ft_aux_table' system variable to show information about a 'FULLTEXT' index for a specified table. Before information for newly inserted rows appears in 'INNODB_FT_INDEX_TABLE', the 'FULLTEXT' index cache must be flushed to disk. This is accomplished by running an *note 'OPTIMIZE TABLE': optimize-table. operation on the indexed table with the 'innodb_optimize_fulltext_only' system variable enabled. (The example disables that variable again at the end because it is intended to be enabled only temporarily.)
mysql> USE test;
mysql> CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
) ENGINE=InnoDB;
mysql> INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');
mysql> SET GLOBAL innodb_optimize_fulltext_only=ON;
mysql> OPTIMIZE TABLE articles;
+---------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+----------+----------+----------+
| test.articles | optimize | status | OK |
+---------------+----------+----------+----------+
mysql> SET GLOBAL innodb_ft_aux_table = 'test/articles';
mysql> SELECT WORD, DOC_COUNT, DOC_ID, POSITION
FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE LIMIT 5;
+------------+-----------+--------+----------+
| WORD | DOC_COUNT | DOC_ID | POSITION |
+------------+-----------+--------+----------+
| 1001 | 1 | 4 | 0 |
| after | 1 | 2 | 22 |
| comparison | 1 | 5 | 44 |
| configured | 1 | 6 | 20 |
| database | 2 | 1 | 31 |
+------------+-----------+--------+----------+
mysql> SET GLOBAL innodb_optimize_fulltext_only=OFF;You must have the 'PROCESS' privilege to query this table.
Use the 'INFORMATION_SCHEMA' note 'COLUMNS': information-schema-columns-table. table or the note 'SHOW COLUMNS': show-columns. statement to view additional information about the columns of this table, including data types and default values.
For more information about 'InnoDB' 'FULLTEXT' search, see note innodb-fulltext-index::, and note fulltext-search::.
File: manual.info.tmp, Node: information-schema-innodb-locks-table, Next: information-schema-innodb-lock-waits-table, Prev: information-schema-innodb-ft-index-table-table, Up: innodb-information-schema-tables
The *note 'INNODB_LOCKS': information-schema-innodb-locks-table. table provides information about each lock that an 'InnoDB' transaction has requested but not yet acquired, and each lock that a transaction holds that is blocking another transaction.
Note:
This table is deprecated as of MySQL 5.7.14 and is removed in MySQL 8.0.
The *note 'INNODB_LOCKS': information-schema-innodb-locks-table. table has these columns:
'LOCK_ID'
A unique lock ID number, internal to 'InnoDB'. Treat it as an opaque string. Although 'LOCK_ID' currently contains 'TRX_ID', the format of the data in 'LOCK_ID' is subject to change at any time. Do not write applications that parse the 'LOCK_ID' value.
'LOCK_TRX_ID'
The ID of the transaction holding the lock. To obtain details about the transaction, join this column with the 'TRX_ID' column of the *note 'INNODB_TRX': information-schema-innodb-trx-table. table.
'LOCK_MODE'
How the lock is requested. Permitted lock mode descriptors are 'S', 'X', 'IS', 'IX', 'GAP', 'AUTO_INC', and 'UNKNOWN'. Lock mode descriptors may be used in combination to identify particular lock modes. For information about 'InnoDB' lock modes, see *note innodb-locking::.
'LOCK_TYPE'
The type of lock. Permitted values are 'RECORD' for a row-level lock, 'TABLE' for a table-level lock.
'LOCK_TABLE'
The name of the table that has been locked or contains locked records.
'LOCK_INDEX'
The name of the index, if 'LOCK_TYPE' is 'RECORD'; otherwise 'NULL'.
'LOCK_SPACE'
The tablespace ID of the locked record, if 'LOCK_TYPE' is 'RECORD'; otherwise 'NULL'.
'LOCK_PAGE'
The page number of the locked record, if 'LOCK_TYPE' is 'RECORD'; otherwise 'NULL'.
'LOCK_REC'
The heap number of the locked record within the page, if 'LOCK_TYPE' is 'RECORD'; otherwise 'NULL'.
'LOCK_DATA'
The data associated with the lock, if any. A value is shown if the 'LOCK_TYPE' is 'RECORD', otherwise the value is 'NULL'. Primary key values of the locked record are shown for a lock placed on the primary key index. Secondary index values of the locked record are shown for a lock placed on a unique secondary index. Secondary index values are shown with primary key values appended if the secondary index is not unique. If there is no primary key, 'LOCK_DATA' shows either the key values of a selected unique index or the unique 'InnoDB' internal row ID number, according to the rules governing 'InnoDB' clustered index use (see *note innodb-index-types::). 'LOCK_DATA' reports 'supremum pseudo-record' for a lock taken on a supremum pseudo-record. If the page containing the locked record is not in the buffer pool because it was written to disk while the lock was held, 'InnoDB' does not fetch the page from disk. Instead, 'LOCK_DATA' reports 'NULL'.
Example
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS\G
*************************** 1. row ***************************
lock_id: 3723:72:3:2
lock_trx_id: 3723
lock_mode: X
lock_type: RECORD
lock_table: `mysql`.`t`
lock_index: PRIMARY
lock_space: 72
lock_page: 3
lock_rec: 2
lock_data: 1, 9
*************************** 2. row ***************************
lock_id: 3722:72:3:2
lock_trx_id: 3722
lock_mode: S
lock_type: RECORD
lock_table: `mysql`.`t`
lock_index: PRIMARY
lock_space: 72
lock_page: 3
lock_rec: 2
lock_data: 1, 9Notes
Use this table to help diagnose performance problems that occur during times of heavy concurrent load. Its contents are updated as described in *note innodb-information-schema-internal-data::.
You must have the 'PROCESS' privilege to query this table.
Use the 'INFORMATION_SCHEMA' note 'COLUMNS': information-schema-columns-table. table or the note 'SHOW COLUMNS': show-columns. statement to view additional information about the columns of this table, including data types and default values.
For usage information, see *note innodb-information-schema-examples::.
File: manual.info.tmp, Node: information-schema-innodb-lock-waits-table, Next: information-schema-innodb-metrics-table, Prev: information-schema-innodb-locks-table, Up: innodb-information-schema-tables
The *note 'INNODB_LOCK_WAITS': information-schema-innodb-lock-waits-table. table contains one or more rows for each blocked 'InnoDB' transaction, indicating the lock it has requested and any locks that are blocking that request.
Note:
This table is deprecated as of MySQL 5.7.14 and is removed in MySQL 8.0.
The *note 'INNODB_LOCK_WAITS': information-schema-innodb-lock-waits-table. table has these columns:
'REQUESTING_TRX_ID'
The ID of the requesting (blocked) transaction.
'REQUESTED_LOCK_ID'
The ID of the lock for which a transaction is waiting. To obtain details about the lock, join this column with the 'LOCK_ID' column of the *note 'INNODB_LOCKS': information-schema-innodb-locks-table. table.
'BLOCKING_TRX_ID'
The ID of the blocking transaction.
'BLOCKING_LOCK_ID'
The ID of a lock held by a transaction blocking another transaction from proceeding. To obtain details about the lock, join this column with the 'LOCK_ID' column of the *note 'INNODB_LOCKS': information-schema-innodb-locks-table. table.
Example
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS\G
*************************** 1. row ***************************
requesting_trx_id: 3396
requested_lock_id: 3396:91:3:2
blocking_trx_id: 3395
blocking_lock_id: 3395:91:3:2Notes
Use this table to help diagnose performance problems that occur during times of heavy concurrent load. Its contents are updated as described in *note innodb-information-schema-internal-data::.
You must have the 'PROCESS' privilege to query this table.
Use the 'INFORMATION_SCHEMA' note 'COLUMNS': information-schema-columns-table. table or the note 'SHOW COLUMNS': show-columns. statement to view additional information about the columns of this table, including data types and default values.
For usage information, see *note innodb-information-schema-examples::.
File: manual.info.tmp, Node: information-schema-innodb-metrics-table, Next: information-schema-innodb-sys-columns-table, Prev: information-schema-innodb-lock-waits-table, Up: innodb-information-schema-tables
The *note 'INNODB_METRICS': information-schema-innodb-metrics-table. table provides a wide variety of 'InnoDB' performance information, complementing the specific focus areas of the Performance Schema tables for 'InnoDB'. With simple queries, you can check the overall health of the system. With more detailed queries, you can diagnose issues such as performance bottlenecks, resource shortages, and application issues.
Each monitor represents a point within the 'InnoDB' source code that is instrumented to gather counter information. Each counter can be started, stopped, and reset. You can also perform these actions for a group of counters using their common module name.
By default, relatively little data is collected. To start, stop, and reset counters, set one of the system variables 'innodb_monitor_enable', 'innodb_monitor_disable', 'innodb_monitor_reset', or 'innodb_monitor_reset_all', using the name of the counter, the name of the module, a wildcard match for such a name using the '%' character, or the special keyword 'all'.
For usage information, see *note innodb-information-schema-metrics-table::.
The *note 'INNODB_METRICS': information-schema-innodb-metrics-table. table has these columns:
'NAME'
A unique name for the counter.
'SUBSYSTEM'
The aspect of 'InnoDB' that the metric applies to.
'COUNT'
The value since the counter was enabled.
'MAX_COUNT'
The maximum value since the counter was enabled.
'MIN_COUNT'
The minimum value since the counter was enabled.
'AVG_COUNT'
The average value since the counter was enabled.
'COUNT_RESET'
The counter value since it was last reset. (The '_RESET' columns act like the lap counter on a stopwatch: you can measure the activity during some time interval, while the cumulative figures are still available in 'COUNT', 'MAX_COUNT', and so on.)
'MAX_COUNT_RESET'
The maximum counter value since it was last reset.
'MIN_COUNT_RESET'
The minimum counter value since it was last reset.
'AVG_COUNT_RESET'
The average counter value since it was last reset.
'TIME_ENABLED'
The timestamp of the last start.
'TIME_DISABLED'
The timestamp of the last stop.
'TIME_ELAPSED'
The elapsed time in seconds since the counter started.
'TIME_RESET'
The timestamp of the last reset.
'STATUS'
Whether the counter is still running ('enabled') or stopped ('disabled').
'TYPE'
Whether the item is a cumulative counter, or measures the current value of some resource.
'COMMENT'
The counter description.
Example
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME='dml_inserts'\G
*************************** 1. row ***************************
NAME: dml_inserts
SUBSYSTEM: dml
COUNT: 3
MAX_COUNT: 3
MIN_COUNT: NULL
AVG_COUNT: 0.046153846153846156
COUNT_RESET: 3
MAX_COUNT_RESET: 3
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: 2014-12-04 14:18:28
TIME_DISABLED: NULL
TIME_ELAPSED: 65
TIME_RESET: NULL
STATUS: enabled
TYPE: status_counter
COMMENT: Number of rows insertedNotes
You must have the 'PROCESS' privilege to query this table.
Use the 'INFORMATION_SCHEMA' note 'COLUMNS': information-schema-columns-table. table or the note 'SHOW COLUMNS': show-columns. statement to view additional information about the columns of this table, including data types and default values.
Transaction counter 'COUNT' values may differ from the number of transaction events reported in Performance Schema 'EVENTS_TRANSACTIONS_SUMMARY' tables. 'InnoDB' counts only those transactions that it executes, whereas Performance Schema collects events for all non-aborted transactions initiated by the server, including empty transactions.
File: manual.info.tmp, Node: information-schema-innodb-sys-columns-table, Next: information-schema-innodb-sys-datafiles-table, Prev: information-schema-innodb-metrics-table, Up: innodb-information-schema-tables
The *note 'INNODB_SYS_COLUMNS': information-schema-innodb-sys-columns-table. table provides metadata about 'InnoDB' table columns, equivalent to the information from the 'SYS_COLUMNS' table in the 'InnoDB' data dictionary.
For related usage information and examples, see *note innodb-information-schema-system-tables::.
The *note 'INNODB_SYS_COLUMNS': information-schema-innodb-sys-columns-table. table has these columns:
'TABLE_ID'
An identifier representing the table associated with the column; the same value as 'INNODB_SYS_TABLES.TABLE_ID'.
'NAME'
The name of the column. These names can be uppercase or lowercase depending on the 'lower_case_table_names' setting. There are no special system-reserved names for columns.
'POS'
The ordinal position of the column within the table, starting from 0 and incrementing sequentially. When a column is dropped, the remaining columns are reordered so that the sequence has no gaps. The 'POS' value for a virtual generated column encodes the column sequence number and ordinal position of the column. For more information, see the 'POS' column description in *note information-schema-innodb-sys-virtual-table::.
'MTYPE'
Stands for 'main type'. A numeric identifier for the column type. 1 = 'VARCHAR', 2 = 'CHAR', 3 = 'FIXBINARY', 4 = 'BINARY', 5 = 'BLOB', 6 = 'INT', 7 = 'SYS_CHILD', 8 = 'SYS', 9 = 'FLOAT', 10 = 'DOUBLE', 11 = 'DECIMAL', 12 = 'VARMYSQL', 13 = 'MYSQL', 14 = 'GEOMETRY'.
'PRTYPE'
The 'InnoDB' 'precise type', a binary value with bits representing MySQL data type, character set code, and nullability.
'LEN'
The column length, for example 4 for 'INT' and 8 for 'BIGINT'. For character columns in multibyte character sets, this length value is the maximum length in bytes needed to represent a definition such as 'VARCHAR(N)'; that is, it might be '2N', '3N', and so on depending on the character encoding.
Example
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS where TABLE_ID = 71\G
*************************** 1. row ***************************
TABLE_ID: 71
NAME: col1
POS: 0
MTYPE: 6
PRTYPE: 1027
LEN: 4
*************************** 2. row ***************************
TABLE_ID: 71
NAME: col2
POS: 1
MTYPE: 2
PRTYPE: 524542
LEN: 10
*************************** 3. row ***************************
TABLE_ID: 71
NAME: col3
POS: 2
MTYPE: 1
PRTYPE: 524303
LEN: 10Notes
You must have the 'PROCESS' privilege to query this table.
Use the 'INFORMATION_SCHEMA' note 'COLUMNS': information-schema-columns-table. table or the note 'SHOW COLUMNS': show-columns. statement to view additional information about the columns of this table, including data types and default values.
File: manual.info.tmp, Node: information-schema-innodb-sys-datafiles-table, Next: information-schema-innodb-sys-fields-table, Prev: information-schema-innodb-sys-columns-table, Up: innodb-information-schema-tables
The *note 'INNODB_SYS_DATAFILES': information-schema-innodb-sys-datafiles-table. table provides data file path information for 'InnoDB' file-per-table and general tablespaces, equivalent to the information in the 'SYS_DATAFILES' table in the 'InnoDB' data dictionary.
For related usage information and examples, see *note innodb-information-schema-system-tables::.
Note:
The 'INFORMATION_SCHEMA' *note 'FILES': information-schema-files-table. table reports metadata for all 'InnoDB' tablespace types including file-per-table tablespaces, general tablespaces, the system tablespace, the temporary tablespace, and undo tablespaces, if present.
The *note 'INNODB_SYS_DATAFILES': information-schema-innodb-sys-datafiles-table. table has these columns:
'SPACE'
The tablespace ID.
'PATH'
The tablespace data file path. If a file-per-table tablespace is created in a location outside the MySQL data directory, the path value is a fully qualified directory path. Otherwise, the path is relative to the data directory.
Example
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES WHERE SPACE = 57\G
*************************** 1. row ***************************
SPACE: 57
PATH: ./test/t1.ibdNotes
You must have the 'PROCESS' privilege to query this table.
Use the 'INFORMATION_SCHEMA' note 'COLUMNS': information-schema-columns-table. table or the note 'SHOW COLUMNS': show-columns. statement to view additional information about the columns of this table, including data types and default values.
File: manual.info.tmp, Node: information-schema-innodb-sys-fields-table, Next: information-schema-innodb-sys-foreign-table, Prev: information-schema-innodb-sys-datafiles-table, Up: innodb-information-schema-tables
The *note 'INNODB_SYS_FIELDS': information-schema-innodb-sys-fields-table. table provides metadata about the key columns (fields) of 'InnoDB' indexes, equivalent to the information from the 'SYS_FIELDS' table in the 'InnoDB' data dictionary.
For related usage information and examples, see *note innodb-information-schema-system-tables::.
The *note 'INNODB_SYS_FIELDS': information-schema-innodb-sys-fields-table. table has these columns:
'INDEX_ID'
An identifier for the index associated with this key field; the same value as 'INNODB_SYS_INDEXES.INDEX_ID'.
'NAME'
The name of the original column from the table; the same value as 'INNODB_SYS_COLUMNS.NAME'.
'POS'
The ordinal position of the key field within the index, starting from 0 and incrementing sequentially. When a column is dropped, the remaining columns are reordered so that the sequence has no gaps.
Example
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS WHERE INDEX_ID = 117\G
*************************** 1. row ***************************
INDEX_ID: 117
NAME: col1
POS: 0Notes
You must have the 'PROCESS' privilege to query this table.
Use the 'INFORMATION_SCHEMA' note 'COLUMNS': information-schema-columns-table. table or the note 'SHOW COLUMNS': show-columns. statement to view additional information about the columns of this table, including data types and default values.
File: manual.info.tmp, Node: information-schema-innodb-sys-foreign-table, Next: information-schema-innodb-sys-foreign-cols-table, Prev: information-schema-innodb-sys-fields-table, Up: innodb-information-schema-tables
The *note 'INNODB_SYS_FOREIGN': information-schema-innodb-sys-foreign-table. table provides metadata about 'InnoDB' foreign keys, equivalent to the information from the 'SYS_FOREIGN' table in the 'InnoDB' data dictionary.
For related usage information and examples, see *note innodb-information-schema-system-tables::.
The *note 'INNODB_SYS_FOREIGN': information-schema-innodb-sys-foreign-table. table has these columns:
'ID'
The name (not a numeric value) of the foreign key index, preceded by the schema (database) name (for example, 'test/products_fk').
'FOR_NAME'
The name of the child table in this foreign key relationship.
'REF_NAME'
The name of the parent table in this foreign key relationship.
'N_COLS'
The number of columns in the foreign key index.
'TYPE'
A collection of bit flags with information about the foreign key column, ORed together. 0 = 'ON DELETE/UPDATE RESTRICT', 1 = 'ON DELETE CASCADE', 2 = 'ON DELETE SET NULL', 4 = 'ON UPDATE CASCADE', 8 = 'ON UPDATE SET NULL', 16 = 'ON DELETE NO ACTION', 32 = 'ON UPDATE NO ACTION'.
Example
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN\G
*************************** 1. row ***************************
ID: test/fk1
FOR_NAME: test/child
REF_NAME: test/parent
N_COLS: 1
TYPE: 1Notes
You must have the 'PROCESS' privilege to query this table.
Use the 'INFORMATION_SCHEMA' note 'COLUMNS': information-schema-columns-table. table or the note 'SHOW COLUMNS': show-columns. statement to view additional information about the columns of this table, including data types and default values.
File: manual.info.tmp, Node: information-schema-innodb-sys-foreign-cols-table, Next: information-schema-innodb-sys-indexes-table, Prev: information-schema-innodb-sys-foreign-table, Up: innodb-information-schema-tables
The *note 'INNODB_SYS_FOREIGN_COLS': information-schema-innodb-sys-foreign-cols-table. table provides status information about the columns of 'InnoDB' foreign keys, equivalent to the information from the 'SYS_FOREIGN_COLS' table in the 'InnoDB' data dictionary.
For related usage information and examples, see *note innodb-information-schema-system-tables::.
The *note 'INNODB_SYS_FOREIGN_COLS': information-schema-innodb-sys-foreign-cols-table. table has these columns:
'ID'
The foreign key index associated with this index key field, using the same value as 'INNODB_SYS_FOREIGN.ID'.
'FOR_COL_NAME'
The name of the associated column in the child table.
'REF_COL_NAME'
The name of the associated column in the parent table.
'POS'
The ordinal position of this key field within the foreign key index, starting from 0.
Example
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS WHERE ID = 'test/fk1'\G
*************************** 1. row ***************************
ID: test/fk1
FOR_COL_NAME: parent_id
REF_COL_NAME: id
POS: 0Notes
You must have the 'PROCESS' privilege to query this table.
Use the 'INFORMATION_SCHEMA' note 'COLUMNS': information-schema-columns-table. table or the note 'SHOW COLUMNS': show-columns. statement to view additional information about the columns of this table, including data types and default values.
File: manual.info.tmp, Node: information-schema-innodb-sys-indexes-table, Next: information-schema-innodb-sys-tables-table, Prev: information-schema-innodb-sys-foreign-cols-table, Up: innodb-information-schema-tables
The *note 'INNODB_SYS_INDEXES': information-schema-innodb-sys-indexes-table. table provides metadata about 'InnoDB' indexes, equivalent to the information in the internal 'SYS_INDEXES' table in the 'InnoDB' data dictionary.
For related usage information and examples, see *note innodb-information-schema-system-tables::.
The *note 'INNODB_SYS_INDEXES': information-schema-innodb-sys-indexes-table. table has these columns:
'INDEX_ID'
An identifier for the index. Index identifiers are unique across all the databases in an instance.
'NAME'
The name of the index. Most indexes created implicitly by 'InnoDB' have consistent names but the index names are not necessarily unique. Examples: 'PRIMARY' for a primary key index, 'GEN_CLUST_INDEX' for the index representing a primary key when one is not specified, and 'ID_IND', 'FOR_IND', and 'REF_IND' for foreign key constraints.
'TABLE_ID'
An identifier representing the table associated with the index; the same value as 'INNODB_SYS_TABLES.TABLE_ID'.
'TYPE'
A numeric value derived from bit-level information that identifies the index type. 0 = nonunique secondary index; 1 = automatically generated clustered index ('GEN_CLUST_INDEX'); 2 = unique nonclustered index; 3 = clustered index; 32 = full-text index; 64 = spatial index; 128 = secondary index on a virtual generated column.
'N_FIELDS'
The number of columns in the index key. For 'GEN_CLUST_INDEX' indexes, this value is 0 because the index is created using an artificial value rather than a real table column.
'PAGE_NO'
The root page number of the index B-tree. For full-text indexes, the 'PAGE_NO' column is unused and set to -1 ('FIL_NULL') because the full-text index is laid out in several B-trees (auxiliary tables).
'SPACE'
An identifier for the tablespace where the index resides. 0 means the 'InnoDB' system tablespace. Any other number represents a table created with a separate '.ibd' file in file-per-table mode. This identifier stays the same after a *note 'TRUNCATE TABLE': truncate-table. statement. Because all indexes for a table reside in the same tablespace as the table, this value is not necessarily unique.
'MERGE_THRESHOLD'
The merge threshold value for index pages. If the amount of data in an index page falls below the note 'MERGE_THRESHOLD': index-page-merge-threshold. value when a row is deleted or when a row is shortened by an update operation, 'InnoDB' attempts to merge the index page with the neighboring index page. The default threshold value is 50%. For more information, see note index-page-merge-threshold::.
Example
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE TABLE_ID = 34\G
*************************** 1. row ***************************
INDEX_ID: 39
NAME: GEN_CLUST_INDEX
TABLE_ID: 34
TYPE: 1
N_FIELDS: 0
PAGE_NO: 3
SPACE: 23
MERGE_THRESHOLD: 50
*************************** 2. row ***************************
INDEX_ID: 40
NAME: i1
TABLE_ID: 34
TYPE: 0
N_FIELDS: 1
PAGE_NO: 4
SPACE: 23
MERGE_THRESHOLD: 50Notes
You must have the 'PROCESS' privilege to query this table.
Use the 'INFORMATION_SCHEMA' note 'COLUMNS': information-schema-columns-table. table or the note 'SHOW COLUMNS': show-columns. statement to view additional information about the columns of this table, including data types and default values.
File: manual.info.tmp, Node: information-schema-innodb-sys-tables-table, Next: information-schema-innodb-sys-tablespaces-table, Prev: information-schema-innodb-sys-indexes-table, Up: innodb-information-schema-tables
The *note 'INNODB_SYS_TABLES': information-schema-innodb-sys-tables-table. table provides metadata about 'InnoDB' tables, equivalent to the information from the 'SYS_TABLES' table in the 'InnoDB' data dictionary.
For related usage information and examples, see *note innodb-information-schema-system-tables::.
The *note 'INNODB_SYS_TABLES': information-schema-innodb-sys-tables-table. table has these columns:
'TABLE_ID'
An identifier for the 'InnoDB' table. This value is unique across all databases in the instance.
'NAME'
The name of the table, preceded by the schema (database) name where appropriate (for example, 'test/t1'). Names of databases and user tables are in the same case as they were originally defined, possibly influenced by the 'lower_case_table_names' setting.
'FLAG'
A numeric value that represents bit-level information about table format and storage characteristics.
'N_COLS'
The number of columns in the table. The number reported includes three hidden columns that are created by 'InnoDB' ('DB_ROW_ID', 'DB_TRX_ID', and 'DB_ROLL_PTR'). The number reported also includes virtual generated columns, if present.
'SPACE'
An identifier for the tablespace where the table resides. 0 means the 'InnoDB' system tablespace. Any other number represents either a file-per-table tablespace or a general tablespace. This identifier stays the same after a *note 'TRUNCATE TABLE': truncate-table. statement. For file-per-table tablespaces, this identifier is unique for tables across all databases in the instance.
'FILE_FORMAT'
The table's file format ('Antelope' or 'Barracuda').
'ROW_FORMAT'
The table's row format ('Compact', 'Redundant', 'Dynamic', or 'Compressed').
'ZIP_PAGE_SIZE'
The zip page size. Applies only to tables with a row format of 'Compressed'.
'SPACE_TYPE'
The type of tablespace to which the table belongs. Possible values include 'System' for the system tablespace, 'General' for general tablespaces, and 'Single' for file-per-table tablespaces. Tables assigned to the system tablespace using note 'CREATE TABLE': create-table. or note 'ALTER TABLE': alter-table. 'TABLESPACE=innodb_system' have a 'SPACE_TYPE' of 'General'. For more information, see *note 'CREATE TABLESPACE': create-tablespace.
Example
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE TABLE_ID = 214\G
*************************** 1. row ***************************
TABLE_ID: 214
NAME: test/t1
FLAG: 129
N_COLS: 4
SPACE: 233
FILE_FORMAT: Antelope
ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
SPACE_TYPE: GeneralNotes
You must have the 'PROCESS' privilege to query this table.
Use the 'INFORMATION_SCHEMA' note 'COLUMNS': information-schema-columns-table. table or the note 'SHOW COLUMNS': show-columns. statement to view additional information about the columns of this table, including data types and default values.
File: manual.info.tmp, Node: information-schema-innodb-sys-tablespaces-table, Next: information-schema-innodb-sys-tablestats-table, Prev: information-schema-innodb-sys-tables-table, Up: innodb-information-schema-tables
The *note 'INNODB_SYS_TABLESPACES': information-schema-innodb-sys-tablespaces-table. table provides metadata about 'InnoDB' file-per-table and general tablespaces, equivalent to the information in the 'SYS_TABLESPACES' table in the 'InnoDB' data dictionary.
For related usage information and examples, see *note innodb-information-schema-system-tables::.
Note:
The 'INFORMATION_SCHEMA' *note 'FILES': information-schema-files-table. table reports metadata for all 'InnoDB' tablespace types including file-per-table tablespaces, general tablespaces, the system tablespace, the temporary tablespace, and undo tablespaces, if present.
The *note 'INNODB_SYS_TABLESPACES': information-schema-innodb-sys-tablespaces-table. table has these columns:
'SPACE'
The tablespace ID.
'NAME'
The schema (database) and table name.
'FLAG'
A numeric value that represents bit-level information about tablespace format and storage characteristics.
'FILE_FORMAT'
The tablespace file format. For example, Antelope, Barracuda, or 'Any' (general tablespaces support any row format). The data in this field is interpreted from the tablespace flags information that resides in the .ibd file. For more information about 'InnoDB' file formats, see *note innodb-file-format::.
'ROW_FORMAT'
The tablespace row format ('Compact or Redundant', 'Dynamic', or 'Compressed'). The data in this column is interpreted from the tablespace flags information that resides in the '.ibd' file.
'PAGE_SIZE'
The tablespace page size. The data in this column is interpreted from the tablespace flags information that resides in the '.ibd' file.
'ZIP_PAGE_SIZE'
The tablespace zip page size. The data in this column is interpreted from the tablespace flags information that resides in the '.ibd' file.
'SPACE_TYPE'
The type of tablespace. Possible values include 'General' for general tablespaces and 'Single' for file-per-table tablespaces.
'FS_BLOCK_SIZE'
The file system block size, which is the unit size used for hole punching. This column pertains to the 'InnoDB' *note transparent page compression: innodb-page-compression. feature.
'FILE_SIZE'
The apparent size of the file, which represents the maximum size of the file, uncompressed. This column pertains to the 'InnoDB' *note transparent page compression: innodb-page-compression. feature.
'ALLOCATED_SIZE'
The actual size of the file, which is the amount of space allocated on disk. This column pertains to the 'InnoDB' *note transparent page compression: innodb-page-compression. feature.
Example
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE SPACE = 26\G
*************************** 1. row ***************************
SPACE: 26
NAME: test/t1
FLAG: 0
FILE_FORMAT: Antelope
ROW_FORMAT: Compact or Redundant
PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
FS_BLOCK_SIZE: 4096
FILE_SIZE: 98304
ALLOCATED_SIZE: 65536Notes
You must have the 'PROCESS' privilege to query this table.
Use the 'INFORMATION_SCHEMA' note 'COLUMNS': information-schema-columns-table. table or the note 'SHOW COLUMNS': show-columns. statement to view additional information about the columns of this table, including data types and default values.
Because tablespace flags are always zero for all Antelope file formats (unlike table flags), there is no way to determine from this flag integer if the tablespace row format is Redundant or Compact. As a result, the possible values for the 'ROW_FORMAT' field are 'Compact or Redundant', 'Compressed', or 'Dynamic.'
With the introduction of general tablespaces, 'InnoDB' system tablespace data (for SPACE 0) is exposed in 'INNODB_SYS_TABLESPACES'.
File: manual.info.tmp, Node: information-schema-innodb-sys-tablestats-table, Next: information-schema-innodb-sys-virtual-table, Prev: information-schema-innodb-sys-tablespaces-table, Up: innodb-information-schema-tables
The *note 'INNODB_SYS_TABLESTATS': information-schema-innodb-sys-tablestats-table. table provides a view of low-level status information about 'InnoDB' tables. This data is used by the MySQL optimizer to calculate which index to use when querying an 'InnoDB' table. This information is derived from in-memory data structures rather than data stored on disk. There is no corresponding internal 'InnoDB' system table.
'InnoDB' tables are represented in this view if they have been opened since the last server restart and have not aged out of the table cache. Tables for which persistent stats are available are always represented in this view.
Table statistics are updated only for note 'DELETE': delete. or note 'UPDATE': update. operations that modify indexed columns. Statistics are not updated by operations that modify only nonindexed columns.
*note 'ANALYZE TABLE': analyze-table. clears table statistics and sets the 'STATS_INITIALIZED' column to 'Uninitialized'. Statistics are collected again the next time the table is accessed.
For related usage information and examples, see *note innodb-information-schema-system-tables::.
The *note 'INNODB_SYS_TABLESTATS': information-schema-innodb-sys-tablestats-table. table has these columns:
'TABLE_ID'
An identifier representing the table for which statistics are available; the same value as 'INNODB_SYS_TABLES.TABLE_ID'.
'NAME'
The name of the table; the same value as 'INNODB_SYS_TABLES.NAME'.
'STATS_INITIALIZED'
The value is 'Initialized' if the statistics are already collected, 'Uninitialized' if not.
'NUM_ROWS'
The current estimated number of rows in the table. Updated after each DML operation. The value could be imprecise if uncommitted transactions are inserting into or deleting from the table.
'CLUST_INDEX_SIZE'
The number of pages on disk that store the clustered index, which holds the 'InnoDB' table data in primary key order. This value might be null if no statistics are collected yet for the table.
'OTHER_INDEX_SIZE'
The number of pages on disk that store all secondary indexes for the table. This value might be null if no statistics are collected yet for the table.
'MODIFIED_COUNTER'
The number of rows modified by DML operations, such as 'INSERT', 'UPDATE', 'DELETE', and also cascade operations from foreign keys. This column is reset each time table statistics are recalculated
'AUTOINC'
The next number to be issued for any auto-increment-based operation. The rate at which the 'AUTOINC' value changes depends on how many times auto-increment numbers have been requested and how many numbers are granted per request.
'REF_COUNT'
When this counter reaches zero, the table metadata can be evicted from the table cache.
Example
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS where TABLE_ID = 71\G
*************************** 1. row ***************************
TABLE_ID: 71
NAME: test/t1
STATS_INITIALIZED: Initialized
NUM_ROWS: 1
CLUST_INDEX_SIZE: 1
OTHER_INDEX_SIZE: 0
MODIFIED_COUNTER: 1
AUTOINC: 0
REF_COUNT: 1Notes
This table is useful primarily for expert-level performance monitoring, or when developing performance-related extensions for MySQL.
You must have the 'PROCESS' privilege to query this table.
Use the 'INFORMATION_SCHEMA' note 'COLUMNS': information-schema-columns-table. table or the note 'SHOW COLUMNS': show-columns. statement to view additional information about the columns of this table, including data types and default values.
File: manual.info.tmp, Node: information-schema-innodb-sys-virtual-table, Next: information-schema-innodb-temp-table-info-table, Prev: information-schema-innodb-sys-tablestats-table, Up: innodb-information-schema-tables
The *note 'INNODB_SYS_VIRTUAL': information-schema-innodb-sys-virtual-table. table provides metadata about 'InnoDB' virtual generated columns and columns upon which virtual generated columns are based, equivalent to information in the 'SYS_VIRTUAL' table in the 'InnoDB' data dictionary.
A row appears in the 'INNODB_SYS_VIRTUAL' table for each column upon which a virtual generated column is based.
The *note 'INNODB_SYS_VIRTUAL': information-schema-innodb-sys-virtual-table. table has these columns:
'TABLE_ID'
An identifier representing the table associated with the virtual column; the same value as 'INNODB_SYS_TABLES.TABLE_ID'.
'POS'
The position value of the virtual generated column. The value is large because it encodes the column sequence number and ordinal position. The formula used to calculate the value uses a bitwise operation:
((Nth virtual generated column for the InnoDB instance + 1) << 16)
+ the ordinal position of the virtual generated columnFor example, if the first virtual generated column in the 'InnoDB' instance is the third column of the table, the formula is '(0 + 1) << 16) + 2'. The first virtual generated column in the 'InnoDB' instance is always number 0. As the third column in the table, the ordinal position of the virtual generated column is 2. Ordinal positions are counted from 0.
'BASE_POS'
The ordinal position of the columns upon which a virtual generated column is based.
Example
mysql> CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) GENERATED ALWAYS AS (a+b) VIRTUAL,
`h` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_VIRTUAL
WHERE TABLE_ID IN
(SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES
WHERE NAME LIKE "test/t1");
+----------+-------+----------+
| TABLE_ID | POS | BASE_POS |
+----------+-------+----------+
| 95 | 65538 | 0 |
| 95 | 65538 | 1 |
+----------+-------+----------+Notes
If a constant value is assigned to a virtual generated column, as in the following table, an entry for the column does not appear in the 'INNODB_SYS_VIRTUAL' table. For an entry to appear, a virtual generated column must have a base column.
CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) GENERATED ALWAYS AS (5) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;However, metadata for such a column does appear in the *note 'INNODB_SYS_COLUMNS': information-schema-innodb-sys-columns-table. table.
You must have the 'PROCESS' privilege to query this table.
Use the 'INFORMATION_SCHEMA' note 'COLUMNS': information-schema-columns-table. table or the note 'SHOW COLUMNS': show-columns. statement to view additional information about the columns of this table, including data types and default values.
File: manual.info.tmp, Node: information-schema-innodb-temp-table-info-table, Next: information-schema-innodb-trx-table, Prev: information-schema-innodb-sys-virtual-table, Up: innodb-information-schema-tables
The note 'INNODB_TEMP_TABLE_INFO': information-schema-innodb-temp-table-info-table. table provides information about user-created 'InnoDB' temporary tables that are active in an 'InnoDB' instance. It does not provide information about internal 'InnoDB' temporary tables used by the optimizer. The note 'INNODB_TEMP_TABLE_INFO': information-schema-innodb-temp-table-info-table. table is created when first queried, exists only in memory, and is not persisted to disk.
For usage information and examples, see *note innodb-information-schema-temp-table-info::.
The *note 'INNODB_TEMP_TABLE_INFO': information-schema-innodb-temp-table-info-table. table has these columns:
'TABLE_ID'
The table ID of the temporary table.
'NAME'
The name of the temporary table.
'N_COLS'
The number of columns in the temporary table. The number includes three hidden columns created by 'InnoDB' ('DB_ROW_ID', 'DB_TRX_ID', and 'DB_ROLL_PTR').
'SPACE'
The ID of the temporary tablespace where the temporary table resides. In 5.7, non-compressed 'InnoDB' temporary tables reside in a shared temporary tablespace. The data file for the shared temporary tablespace is defined by the 'innodb_temp_data_file_path' system variable. By default, there is a single data file for the shared temporary tablespace named 'ibtmp1', which is located in the data directory. Compressed temporary tables reside in separate file-per-table tablespaces located in the temporary file directory defined by 'tmpdir'. The temporary tablespace ID is a nonzero value that is dynamically generated on server restart.
'PER_TABLE_TABLESPACE'
A value of 'TRUE' indicates that the temporary table resides in a separate file-per-table tablespace. A value of 'FALSE' indicates that the temporary table resides in the shared temporary tablespace.
'IS_COMPRESSED'
A value of 'TRUE' indicates that the temporary table is compressed.
Example
mysql> CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
*************************** 1. row ***************************
TABLE_ID: 38
NAME: #sql26cf_6_0
N_COLS: 4
SPACE: 52
PER_TABLE_TABLESPACE: FALSE
IS_COMPRESSED: FALSENotes
This table is useful primarily for expert-level monitoring.
You must have the 'PROCESS' privilege to query this table.
Use the 'INFORMATION_SCHEMA' note 'COLUMNS': information-schema-columns-table. table or the note 'SHOW COLUMNS': show-columns. statement to view additional information about the columns of this table, including data types and default values.
File: manual.info.tmp, Node: information-schema-innodb-trx-table, Prev: information-schema-innodb-temp-table-info-table, Up: innodb-information-schema-tables
The *note 'INNODB_TRX': information-schema-innodb-trx-table. table provides information about every transaction currently executing inside 'InnoDB', including whether the transaction is waiting for a lock, when the transaction started, and the SQL statement the transaction is executing, if any.
For usage information, see *note innodb-information-schema-examples::.
The *note 'INNODB_TRX': information-schema-innodb-trx-table. table has these columns:
'TRX_ID'
A unique transaction ID number, internal to 'InnoDB'. These IDs are not created for transactions that are read only and nonlocking. For details, see *note innodb-performance-ro-txn::.
'TRX_WEIGHT'
The weight of a transaction, reflecting (but not necessarily the exact count of) the number of rows altered and the number of rows locked by the transaction. To resolve a deadlock, 'InnoDB' selects the transaction with the smallest weight as the 'victim' to roll back. Transactions that have changed nontransactional tables are considered heavier than others, regardless of the number of altered and locked rows.
'TRX_STATE'
The transaction execution state. Permitted values are 'RUNNING', 'LOCK WAIT', 'ROLLING BACK', and 'COMMITTING'.
'TRX_STARTED'
The transaction start time.
'TRX_REQUESTED_LOCK_ID'
The ID of the lock the transaction is currently waiting for, if 'TRX_STATE' is 'LOCK WAIT'; otherwise 'NULL'. To obtain details about the lock, join this column with the 'LOCK_ID' column of the *note 'INNODB_LOCKS': information-schema-innodb-locks-table. table.
'TRX_WAIT_STARTED'
The time when the transaction started waiting on the lock, if 'TRX_STATE' is 'LOCK WAIT'; otherwise 'NULL'.
'TRX_MYSQL_THREAD_ID'
The MySQL thread ID. To obtain details about the thread, join this column with the 'ID' column of the 'INFORMATION_SCHEMA' note 'PROCESSLIST': information-schema-processlist-table. table, but see note innodb-information-schema-internal-data::.
'TRX_QUERY'
The SQL statement that is being executed by the transaction.
'TRX_OPERATION_STATE'
The transaction's current operation, if any; otherwise 'NULL'.
'TRX_TABLES_IN_USE'
The number of 'InnoDB' tables used while processing the current SQL statement of this transaction.
'TRX_TABLES_LOCKED'
The number of 'InnoDB' tables that the current SQL statement has row locks on. (Because these are row locks, not table locks, the tables can usually still be read from and written to by multiple transactions, despite some rows being locked.)
'TRX_LOCK_STRUCTS'
The number of locks reserved by the transaction.
'TRX_LOCK_MEMORY_BYTES'
The total size taken up by the lock structures of this transaction in memory.
'TRX_ROWS_LOCKED'
The approximate number or rows locked by this transaction. The value might include delete-marked rows that are physically present but not visible to the transaction.
'TRX_ROWS_MODIFIED'
The number of modified and inserted rows in this transaction.
'TRX_CONCURRENCY_TICKETS'
A value indicating how much work the current transaction can do before being swapped out, as specified by the 'innodb_concurrency_tickets' system variable.
'TRX_ISOLATION_LEVEL'
The isolation level of the current transaction.
'TRX_UNIQUE_CHECKS'
Whether unique checks are turned on or off for the current transaction. For example, they might be turned off during a bulk data load.
'TRX_FOREIGN_KEY_CHECKS'
Whether foreign key checks are turned on or off for the current transaction. For example, they might be turned off during a bulk data load.
'TRX_LAST_FOREIGN_KEY_ERROR'
The detailed error message for the last foreign key error, if any; otherwise 'NULL'.
'TRX_ADAPTIVE_HASH_LATCHED'
Whether the adaptive hash index is locked by the current transaction. When the adaptive hash index search system is partitioned, a single transaction does not lock the entire adaptive hash index. Adaptive hash index partitioning is controlled by 'innodb_adaptive_hash_index_parts', which is set to 8 by default.
'TRX_ADAPTIVE_HASH_TIMEOUT'
Deprecated in MySQL 5.7.8. Always returns 0.
Whether to relinquish the search latch immediately for the adaptive hash index, or reserve it across calls from MySQL. When there is no adaptive hash index contention, this value remains zero and statements reserve the latch until they finish. During times of contention, it counts down to zero, and statements release the latch immediately after each row lookup. When the adaptive hash index search system is partitioned (controlled by 'innodb_adaptive_hash_index_parts'), the value remains 0.
'TRX_IS_READ_ONLY'
A value of 1 indicates the transaction is read only.
'TRX_AUTOCOMMIT_NON_LOCKING'
A value of 1 indicates the transaction is a *note 'SELECT': select. statement that does not use the 'FOR UPDATE' or 'LOCK IN SHARED MODE' clauses, and is executing with 'autocommit' enabled so that the transaction contains only this one statement. When this column and 'TRX_IS_READ_ONLY' are both 1, 'InnoDB' optimizes the transaction to reduce the overhead associated with transactions that change table data.
Example
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 1510
trx_state: RUNNING
trx_started: 2014-11-19 13:24:40
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 586739
trx_mysql_thread_id: 2
trx_query: DELETE FROM employees.salaries WHERE salary > 65000
trx_operation_state: updating or deleting
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 3003
trx_lock_memory_bytes: 450768
trx_rows_locked: 1407513
trx_rows_modified: 583736
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_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0Notes
Use this table to help diagnose performance problems that occur during times of heavy concurrent load. Its contents are updated as described in *note innodb-information-schema-internal-data::.
You must have the 'PROCESS' privilege to query this table.
Use the 'INFORMATION_SCHEMA' note 'COLUMNS': information-schema-columns-table. table or the note 'SHOW COLUMNS': show-columns. statement to view additional information about the columns of this table, including data types and default values.
File: manual.info.tmp, Node: thread-pool-information-schema-tables, Next: connection-control-information-schema-tables, Prev: innodb-information-schema-tables, Up: information-schema