- 20 INFORMATION_SCHEMA Tables
- 20.1 The INFORMATION_SCHEMA CHARACTER_SETS Table
- 20.2 The INFORMATION_SCHEMA COLLATIONS Table
- 20.3 The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table
- 20.4 The INFORMATION_SCHEMA COLUMNS Table
- 20.5 The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table
- 20.6 The INFORMATION_SCHEMA ENGINES Table
- 20.7 The INFORMATION_SCHEMA EVENTS Table
- 20.8 The INFORMATION_SCHEMA FILES Table
- 20.9 The INFORMATION_SCHEMA GLOBAL_STATUS and SESSION_STATUS Tables
- 20.10 The INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES Tables
- 20.11 The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table
- 20.12 The INFORMATION_SCHEMA OPTIMIZER_TRACE Table
- 20.13 The INFORMATION_SCHEMA PARAMETERS Table
- 20.14 The INFORMATION_SCHEMA PARTITIONS Table
- 20.15 The INFORMATION_SCHEMA PLUGINS Table
- 20.16 The INFORMATION_SCHEMA PROCESSLIST Table
- 20.17 The INFORMATION_SCHEMA PROFILING Table
- 20.18 The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table
- 20.19 The INFORMATION_SCHEMA ROUTINES Table
- 20.20 The INFORMATION_SCHEMA SCHEMATA Table
- 20.21 The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table
- 20.22 The INFORMATION_SCHEMA STATISTICS Table
- 20.23 The INFORMATION_SCHEMA TABLES Table
- 20.24 The INFORMATION_SCHEMA TABLESPACES Table
- 20.25 The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table
- 20.26 The INFORMATION_SCHEMA TABLE_PRIVILEGES Table
- 20.27 The INFORMATION_SCHEMA TRIGGERS Table
- 20.28 The INFORMATION_SCHEMA USER_PRIVILEGES Table
- 20.29 The INFORMATION_SCHEMA VIEWS Table
- 20.30 INFORMATION_SCHEMA Tables for InnoDB
- 20.30.1 The INFORMATION_SCHEMA INNODB_CMP and INNODB_CMP_RESET Tables
- 20.30.2 The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables
- 20.30.3 The INFORMATION_SCHEMA INNODB_CMPMEM and INNODB_CMPMEM_RESET Tables
- 20.30.4 The INFORMATION_SCHEMA INNODB_TRX Table
- 20.30.5 The INFORMATION_SCHEMA INNODB_LOCKS Table
- 20.30.6 The INFORMATION_SCHEMA INNODB_LOCK_WAITS Table
- 20.30.7 The INFORMATION_SCHEMA INNODB_SYS_TABLES Table
- 20.30.8 The INFORMATION_SCHEMA INNODB_SYS_INDEXES Table
- 20.30.9 The INFORMATION_SCHEMA INNODB_SYS_COLUMNS Table
- 20.30.10 The INFORMATION_SCHEMA INNODB_SYS_FIELDS Table
- 20.30.11 The INFORMATION_SCHEMA INNODB_SYS_FOREIGN Table
- 20.30.12 The INFORMATION_SCHEMA INNODB_SYS_FOREIGN_COLS Table
- 20.30.13 The INFORMATION_SCHEMA INNODB_SYS_TABLESTATS View
- 20.30.14 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table
- 20.30.15 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU Table
- 20.30.16 The INFORMATION_SCHEMA INNODB_BUFFER_POOL_STATS Table
- 20.30.17 The INFORMATION_SCHEMA INNODB_METRICS Table
- 20.30.18 The INFORMATION_SCHEMA INNODB_FT_CONFIG Table
- 20.30.19 The INFORMATION_SCHEMA INNODB_FT_DEFAULT_STOPWORD Table
- 20.30.20 The INFORMATION_SCHEMA INNODB_FT_INDEX_TABLE Table
- 20.30.21 The INFORMATION_SCHEMA INNODB_FT_INDEX_CACHE Table
- 20.30.22 The INFORMATION_SCHEMA INNODB_FT_DELETED Table
- 20.30.23 The INFORMATION_SCHEMA INNODB_FT_BEING_DELETED Table
- 20.31 Thread Pool INFORMATION_SCHEMA Tables
- 20.32 Extensions to SHOW Statements
[+/-]
- 20.30.1. The
INFORMATION_SCHEMA INNODB_CMPandINNODB_CMP_RESETTables - 20.30.2. The
INFORMATION_SCHEMA INNODB_CMP_PER_INDEXandINNODB_CMP_PER_INDEX_RESETTables - 20.30.3. The
INFORMATION_SCHEMA INNODB_CMPMEMandINNODB_CMPMEM_RESETTables - 20.30.4. The
INFORMATION_SCHEMA INNODB_TRXTable - 20.30.5. The
INFORMATION_SCHEMA INNODB_LOCKSTable - 20.30.6. The
INFORMATION_SCHEMA INNODB_LOCK_WAITSTable - 20.30.7. The
INFORMATION_SCHEMA INNODB_SYS_TABLESTable - 20.30.8. The
INFORMATION_SCHEMA INNODB_SYS_INDEXESTable - 20.30.9. The
INFORMATION_SCHEMA INNODB_SYS_COLUMNSTable - 20.30.10. The
INFORMATION_SCHEMA INNODB_SYS_FIELDSTable - 20.30.11. The
INFORMATION_SCHEMA INNODB_SYS_FOREIGNTable - 20.30.12. The
INFORMATION_SCHEMA INNODB_SYS_FOREIGN_COLSTable - 20.30.13. The
INFORMATION_SCHEMA INNODB_SYS_TABLESTATSView - 20.30.14. The
INFORMATION_SCHEMA INNODB_BUFFER_PAGETable - 20.30.15. The
INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRUTable - 20.30.16. The
INFORMATION_SCHEMA INNODB_BUFFER_POOL_STATSTable - 20.30.17. The
INFORMATION_SCHEMA INNODB_METRICSTable - 20.30.18. The
INFORMATION_SCHEMA INNODB_FT_CONFIGTable - 20.30.19. The
INFORMATION_SCHEMA INNODB_FT_DEFAULT_STOPWORDTable - 20.30.20. The
INFORMATION_SCHEMA INNODB_FT_INDEX_TABLETable - 20.30.21. The
INFORMATION_SCHEMA INNODB_FT_INDEX_CACHETable - 20.30.22. The
INFORMATION_SCHEMA INNODB_FT_DELETEDTable - 20.30.23. The
INFORMATION_SCHEMA INNODB_FT_BEING_DELETEDTable
The InnoDB tables related to the
InnoDB storage engine serve two purposes:
You can monitor ongoing
InnoDBactivity, to detect inefficiencies before they turn into issues, or to troubleshoot performance and capacity issues that do occur. 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. The monitoring information deals with:InnoDBtable compression, a feature whose use depends on a balance between I/O reduction, CPU usage, buffer pool management, and how much compression is possible for your data.Transactions and locks, features that balance high performance for a single operation, against the ability to run multiple operations concurrently. (Transactions are the high-level, user-visible aspect of concurrency. Locks are the low-level mechanism that transactions use to avoid reading or writing unreliable data.)
You can extract information about schema objects managed by
InnoDB, using theINNODB_SYS_*tables. This information comes from theInnoDBdata dictionary, which cannot be queried directly like regularInnoDBtables. Traditionally, you would get this type of information using the techniques from Section 14.2.4.4, “SHOW ENGINE INNODB STATUSand theInnoDBMonitors”, setting upInnoDBmonitors and parsing the output from theSHOW ENGINE INNODB STATUScommand. TheInnoDBinterface offers a simpler, familiar technique to access this data.
User Comments
Add your own comment.