- 19.30 INFORMATION_SCHEMA Tables for InnoDB
- 19.30.1 The INFORMATION_SCHEMA INNODB_CMP and INNODB_CMP_RESET Tables
- 19.30.2 The INFORMATION_SCHEMA INNODB_CMPMEM and INNODB_CMPMEM_RESET Tables
- 19.30.3 The INFORMATION_SCHEMA INNODB_TRX Table
- 19.30.4 The INFORMATION_SCHEMA INNODB_LOCKS Table
- 19.30.5 The INFORMATION_SCHEMA INNODB_LOCK_WAITS Table
- 19.30.6 The INFORMATION_SCHEMA INNODB_SYS_TABLES Table
- 19.30.7 The INFORMATION_SCHEMA INNODB_SYS_INDEXES Table
- 19.30.8 The INFORMATION_SCHEMA INNODB_SYS_COLUMNS Table
- 19.30.9 The INFORMATION_SCHEMA INNODB_SYS_FIELDS Table
- 19.30.10 The INFORMATION_SCHEMA INNODB_SYS_FOREIGN Table
- 19.30.11 The INFORMATION_SCHEMA INNODB_SYS_FOREIGN_COLS Table
- 19.30.12 The INFORMATION_SCHEMA INNODB_SYS_TABLESTATS View
- 19.30.13 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table
- 19.30.14 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU Table
- 19.30.15 The INFORMATION_SCHEMA INNODB_BUFFER_POOL_STATS Table
- 19.30.16 The INFORMATION_SCHEMA INNODB_METRICS Table
- 19.30.17 The INFORMATION_SCHEMA INNODB_FT_CONFIG Table
- 19.30.18 The INFORMATION_SCHEMA INNODB_FT_DEFAULT_STOPWORD Table
- 19.30.19 The INFORMATION_SCHEMA INNODB_FT_INDEX_TABLE Table
- 19.30.20 The INFORMATION_SCHEMA INNODB_FT_INDEX_CACHE Table
- 19.30.21 The INFORMATION_SCHEMA INNODB_FT_INSERTED Table
- 19.30.22 The INFORMATION_SCHEMA INNODB_FT_DELETED Table
- 19.30.23 The INFORMATION_SCHEMA INNODB_FT_BEING_DELETED Table
The INNODB_SYS_TABLESTATS view provides status
information about performance statistics for
InnoDB tables. These statistics represent
low-level information 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 corresponding to data stored on disk.
InnoDB tables are represented in this view if
they have been opened since the last server restart, and not aged
out of the table cache. Tables for which persistent stats are
available are always represented in this view.
Table 19.12. INNODB_SYS_TABLESTATS Columns
| Column name | Description |
|---|---|
TABLE_ID |
An identifier representing the table for which statistics are available,
using the same value as
INNODB_SYS_TABLES.TABLE_ID. |
NAME |
The name of the table, using 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. Could be imprecise if uncommitted transactions are inserting into or deleting from the table. |
CLUST_INDEX_SIZE |
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 |
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. |
AUTOINC |
|
REF_COUNT |
When this counter reaches zero, the table metadata can be evicted from the table cache. |
Notes:
This table is primarily useful for expert-level performance monitoring, or when developing performance-related extensions for MySQL.
Since the
INFORMATION_SCHEMAis a general-purpose way to monitor the MySQL server, use this table rather than the correspondingInnoDBsystem table for any new monitoring application you develop.You must have the
PROCESSprivilege to query this table.
User Comments
Add your own comment.