Chapter 22 INFORMATION_SCHEMA Tables

Table of Contents

22.1 The INFORMATION_SCHEMA CHARACTER_SETS Table
22.2 The INFORMATION_SCHEMA COLLATIONS Table
22.3 The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table
22.4 The INFORMATION_SCHEMA COLUMNS Table
22.5 The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table
22.6 The INFORMATION_SCHEMA ENGINES Table
22.7 The INFORMATION_SCHEMA EVENTS Table
22.8 The INFORMATION_SCHEMA FILES Table
22.9 The INFORMATION_SCHEMA GLOBAL_STATUS and SESSION_STATUS Tables
22.10 The INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES Tables
22.11 The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table
22.12 The INFORMATION_SCHEMA ndb_transid_mysql_connection_map Table
22.13 The INFORMATION_SCHEMA OPTIMIZER_TRACE Table
22.14 The INFORMATION_SCHEMA PARAMETERS Table
22.15 The INFORMATION_SCHEMA PARTITIONS Table
22.16 The INFORMATION_SCHEMA PLUGINS Table
22.17 The INFORMATION_SCHEMA PROCESSLIST Table
22.18 The INFORMATION_SCHEMA PROFILING Table
22.19 The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table
22.20 The INFORMATION_SCHEMA ROUTINES Table
22.21 The INFORMATION_SCHEMA SCHEMATA Table
22.22 The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table
22.23 The INFORMATION_SCHEMA STATISTICS Table
22.24 The INFORMATION_SCHEMA TABLES Table
22.25 The INFORMATION_SCHEMA TABLESPACES Table
22.26 The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table
22.27 The INFORMATION_SCHEMA TABLE_PRIVILEGES Table
22.28 The INFORMATION_SCHEMA TRIGGERS Table
22.29 The INFORMATION_SCHEMA USER_PRIVILEGES Table
22.30 The INFORMATION_SCHEMA VIEWS Table
22.31 INFORMATION_SCHEMA Tables for InnoDB
22.31.1 The INFORMATION_SCHEMA INNODB_CMP and INNODB_CMP_RESET Tables
22.31.2 The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables
22.31.3 The INFORMATION_SCHEMA INNODB_CMPMEM and INNODB_CMPMEM_RESET Tables
22.31.4 The INFORMATION_SCHEMA INNODB_TRX Table
22.31.5 The INFORMATION_SCHEMA INNODB_LOCKS Table
22.31.6 The INFORMATION_SCHEMA INNODB_LOCK_WAITS Table
22.31.7 The INFORMATION_SCHEMA INNODB_SYS_TABLES Table
22.31.8 The INFORMATION_SCHEMA INNODB_SYS_INDEXES Table
22.31.9 The INFORMATION_SCHEMA INNODB_SYS_COLUMNS Table
22.31.10 The INFORMATION_SCHEMA INNODB_SYS_FIELDS Table
22.31.11 The INFORMATION_SCHEMA INNODB_SYS_FOREIGN Table
22.31.12 The INFORMATION_SCHEMA INNODB_SYS_FOREIGN_COLS Table
22.31.13 The INFORMATION_SCHEMA INNODB_SYS_TABLESTATS View
22.31.14 The INFORMATION_SCHEMA INNODB_SYS_DATAFILES Table
22.31.15 The INFORMATION_SCHEMA INNODB_SYS_TABLESPACES Table
22.31.16 The INFORMATION_SCHEMA INNODB_SYS_VIRTUAL Table
22.31.17 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table
22.31.18 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU Table
22.31.19 The INFORMATION_SCHEMA INNODB_BUFFER_POOL_STATS Table
22.31.20 The INFORMATION_SCHEMA INNODB_METRICS Table
22.31.21 The INFORMATION_SCHEMA INNODB_FT_CONFIG Table
22.31.22 The INFORMATION_SCHEMA INNODB_FT_DEFAULT_STOPWORD Table
22.31.23 The INFORMATION_SCHEMA INNODB_FT_INDEX_TABLE Table
22.31.24 The INFORMATION_SCHEMA INNODB_FT_INDEX_CACHE Table
22.31.25 The INFORMATION_SCHEMA INNODB_FT_DELETED Table
22.31.26 The INFORMATION_SCHEMA INNODB_FT_BEING_DELETED Table
22.31.27 The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table
22.32 Thread Pool INFORMATION_SCHEMA Tables
22.32.1 The INFORMATION_SCHEMA TP_THREAD_STATE Table
22.32.2 The INFORMATION_SCHEMA TP_THREAD_GROUP_STATE Table
22.32.3 The INFORMATION_SCHEMA TP_THREAD_GROUP_STATS Table
22.33 Extensions to SHOW Statements

INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges. Other terms that are sometimes used for this information are data dictionary and system catalog.

Usage Notes for the INFORMATION_SCHEMA Database

INFORMATION_SCHEMA is a database within each MySQL instance, the place that stores information about all the other databases that the MySQL server maintains. The INFORMATION_SCHEMA database contains several read-only tables. They are actually views, not base tables, so there are no files associated with them, and you cannot set triggers on them. Also, there is no database directory with that name.

Although you can select INFORMATION_SCHEMA as the default database with a USE statement, you can only read the contents of tables, not perform INSERT, UPDATE, or DELETE operations on them.

Example

Here is an example of a statement that retrieves information from INFORMATION_SCHEMA:

mysql> SELECT table_name, table_type, engine
    -> FROM information_schema.tables
    -> WHERE table_schema = 'db5'
    -> ORDER BY table_name;
+------------+------------+--------+
| table_name | table_type | engine |
+------------+------------+--------+
| fk         | BASE TABLE | InnoDB |
| fk2        | BASE TABLE | InnoDB |
| goto       | BASE TABLE | MyISAM |
| into       | BASE TABLE | MyISAM |
| k          | BASE TABLE | MyISAM |
| kurs       | BASE TABLE | MyISAM |
| loop       | BASE TABLE | MyISAM |
| pk         | BASE TABLE | InnoDB |
| t          | BASE TABLE | MyISAM |
| t2         | BASE TABLE | MyISAM |
| t3         | BASE TABLE | MyISAM |
| t7         | BASE TABLE | MyISAM |
| tables     | BASE TABLE | MyISAM |
| v          | VIEW       | NULL   |
| v2         | VIEW       | NULL   |
| v3         | VIEW       | NULL   |
| v56        | VIEW       | NULL   |
+------------+------------+--------+
17 rows in set (0.01 sec)

Explanation: The statement requests a list of all the tables in database db5, showing just three pieces of information: the name of the table, its type, and its storage engine.

Character Set Considerations

The definition for character columns (for example, TABLES.TABLE_NAME) is generally VARCHAR(N) CHARACTER SET utf8 where N is at least 64. MySQL uses the default collation for this character set (utf8_general_ci) for all searches, sorts, comparisons, and other string operations on such columns.

Because some MySQL objects are represented as files, searches in INFORMATION_SCHEMA string columns can be affected by file system case sensitivity. For more information, see Section 11.1.8.8, “Collation and INFORMATION_SCHEMA Searches”.

INFORMATION_SCHEMA as Alternative to SHOW Statements

The SELECT ... FROM INFORMATION_SCHEMA statement is intended as a more consistent way to provide access to the information provided by the various SHOW statements that MySQL supports (SHOW DATABASES, SHOW TABLES, and so forth). Using SELECT has these advantages, compared to SHOW:

Because SHOW is familiar and widely used, the SHOW statements remain as an alternative. In fact, along with the implementation of INFORMATION_SCHEMA, there are enhancements to SHOW as described in Section 22.33, “Extensions to SHOW Statements”.

Privileges

Each MySQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges. In some cases (for example, the ROUTINE_DEFINITION column in the INFORMATION_SCHEMA.ROUTINES table), users who have insufficient privileges see NULL. These restrictions do not apply for InnoDB tables; you can see them with only the PROCESS privilege.

The same privileges apply to selecting information from INFORMATION_SCHEMA and viewing the same information through SHOW statements. In either case, you must have some privilege on an object to see information about it.

Performance Considerations

INFORMATION_SCHEMA queries that search for information from more than one database might take a long time and impact performance. To check the efficiency of a query, you can use EXPLAIN. For information about using EXPLAIN output to tune INFORMATION_SCHEMA queries, see Section 9.2.4, “Optimizing INFORMATION_SCHEMA Queries”.

Standards Considerations

The implementation for the INFORMATION_SCHEMA table structures in MySQL follows the ANSI/ISO SQL:2003 standard Part 11 Schemata. Our intent is approximate compliance with SQL:2003 core feature F021 Basic information schema.

Users of SQL Server 2000 (which also follows the standard) may notice a strong similarity. However, MySQL has omitted many columns that are not relevant for our implementation, and added columns that are MySQL-specific. One such column is the ENGINE column in the INFORMATION_SCHEMA.TABLES table.

Although other DBMSs use a variety of names, like syscat or system, the standard name is INFORMATION_SCHEMA.

To avoid using any name that is reserved in the standard or in DB2, SQL Server, or Oracle, we changed the names of some columns marked MySQL extension. (For example, we changed COLLATION to TABLE_COLLATION in the TABLES table.) See the list of reserved words near the end of this article: https://web.archive.org/web/20070428032454/http://www.dbazine.com/db2/db2-disarticles/gulutzan5.

Conventions in the INFORMATION_SCHEMA Reference Sections

The following sections describe each of the tables and columns in INFORMATION_SCHEMA. For each column, there are three pieces of information:

Many sections indicate what SHOW statement is equivalent to a SELECT that retrieves information from INFORMATION_SCHEMA. For SHOW statements that display information for the default database if you omit a FROM db_name clause, you can often select information for the default database by adding an AND TABLE_SCHEMA = SCHEMA() condition to the WHERE clause of a query that retrieves information from an INFORMATION_SCHEMA table.

For information about INFORMATION_SCHEMA tables specific to the InnoDB storage engine, see Section 22.31, “INFORMATION_SCHEMA Tables for InnoDB”.

For answers to questions that are often asked concerning the INFORMATION_SCHEMA database, see Section A.7, “MySQL 5.7 FAQ: INFORMATION_SCHEMA”.

22.1 The INFORMATION_SCHEMA CHARACTER_SETS Table

The CHARACTER_SETS table provides information about available character sets.

INFORMATION_SCHEMA NameSHOW NameRemarks
CHARACTER_SET_NAMECharset 
DEFAULT_COLLATE_NAMEDefault collation 
DESCRIPTIONDescriptionMySQL extension
MAXLENMaxlenMySQL extension

The following statements are equivalent:

SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS
  [WHERE CHARACTER_SET_NAME LIKE 'wild']

SHOW CHARACTER SET
  [LIKE 'wild']

22.2 The INFORMATION_SCHEMA COLLATIONS Table

The COLLATIONS table provides information about collations for each character set.

INFORMATION_SCHEMA NameSHOW NameRemarks
COLLATION_NAMECollation 
CHARACTER_SET_NAMECharsetMySQL extension
IDIdMySQL extension
IS_DEFAULTDefaultMySQL extension
IS_COMPILEDCompiledMySQL extension
SORTLENSortlenMySQL extension
  • COLLATION_NAME is the collation name.

  • CHARACTER_SET_NAME is the name of the character set with which the collation is associated.

  • ID is the collation ID.

  • IS_DEFAULT indicates whether the collation is the default for its character set.

  • IS_COMPILED indicates whether the character set is compiled into the server.

  • SORTLEN is related to the amount of memory required to sort strings expressed in the character set.

Collation information is also available from the SHOW COLLATION statement. The following statements are equivalent:

SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS
  [WHERE COLLATION_NAME LIKE 'wild']

SHOW COLLATION
  [LIKE 'wild']

22.3 The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table

The COLLATION_CHARACTER_SET_APPLICABILITY table indicates what character set is applicable for what collation. The columns are equivalent to the first two display fields that we get from SHOW COLLATION.

INFORMATION_SCHEMA NameSHOW NameRemarks
COLLATION_NAMECollation 
CHARACTER_SET_NAMECharset 

22.4 The INFORMATION_SCHEMA COLUMNS Table

The COLUMNS table provides information about columns in tables.

INFORMATION_SCHEMA NameSHOW NameRemarks
TABLE_CATALOG def
TABLE_SCHEMA  
TABLE_NAME  
COLUMN_NAMEField 
ORDINAL_POSITION see notes
COLUMN_DEFAULTDefault 
IS_NULLABLENull 
DATA_TYPEType 
CHARACTER_MAXIMUM_LENGTHType 
CHARACTER_OCTET_LENGTH  
NUMERIC_PRECISIONType 
NUMERIC_SCALEType 
DATETIME_PRECISIONType 
CHARACTER_SET_NAME  
COLLATION_NAMECollation 
COLUMN_TYPETypeMySQL extension
COLUMN_KEYKeyMySQL extension
EXTRAExtraMySQL extension
PRIVILEGESPrivilegesMySQL extension
COLUMN_COMMENTCommentMySQL extension
GENERATION_EXPRESSION MySQL extension

Notes:

  • In SHOW, the Type display includes values from several different COLUMNS columns.

  • ORDINAL_POSITION is necessary because you might want to say ORDER BY ORDINAL_POSITION. Unlike SHOW, SELECT does not have automatic ordering.

  • CHARACTER_OCTET_LENGTH should be the same as CHARACTER_MAXIMUM_LENGTH, except for multibyte character sets.

  • CHARACTER_SET_NAME can be derived from Collation. For example, if you say SHOW FULL COLUMNS FROM t, and you see in the Collation column a value of latin1_swedish_ci, the character set is what is before the first underscore: latin1.

  • GENERATION_EXPRESSION is nonempty for generated columns and displays the expression used to compute column values. For information about generated columns, see Section 14.1.18.5, “CREATE TABLE and Generated Columns”. This column was added in MySQL 5.7.6.

  • As of MySQL 5.7.6, the EXTRA column contains VIRTUAL GENERATED or VIRTUAL STORED for generated columns.

The following statements are nearly equivalent:

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'tbl_name'
  [AND table_schema = 'db_name']
  [AND column_name LIKE 'wild']

SHOW COLUMNS
  FROM tbl_name
  [FROM db_name]
  [LIKE 'wild']

22.5 The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table

The COLUMN_PRIVILEGES table provides information about column privileges. This information comes from the mysql.columns_priv grant table.

INFORMATION_SCHEMA NameSHOW NameRemarks
GRANTEE 'user_name'@'host_name' value
TABLE_CATALOG def
TABLE_SCHEMA  
TABLE_NAME  
COLUMN_NAME  
PRIVILEGE_TYPE  
IS_GRANTABLE  

Notes:

  • In the output from SHOW FULL COLUMNS, the privileges are all in one field and in lowercase, for example, select,insert,update,references. In COLUMN_PRIVILEGES, there is one privilege per row, in uppercase.

  • PRIVILEGE_TYPE can contain one (and only one) of these values: SELECT, INSERT, UPDATE, REFERENCES.

  • If the user has GRANT OPTION privilege, IS_GRANTABLE should be YES. Otherwise, IS_GRANTABLE should be NO. The output does not list GRANT OPTION as a separate privilege.

The following statements are not equivalent:

SELECT ... FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES

SHOW GRANTS ...

22.6 The INFORMATION_SCHEMA ENGINES Table

The ENGINES table provides information about storage engines.

INFORMATION_SCHEMA NameSHOW NameRemarks
ENGINEEngineMySQL extension
SUPPORTSupportMySQL extension
COMMENTCommentMySQL extension
TRANSACTIONSTransactionsMySQL extension
XAXAMySQL extension
SAVEPOINTSSavepointsMySQL extension

Notes:

See also Section 14.7.5.16, “SHOW ENGINES Syntax”.

22.7 The INFORMATION_SCHEMA EVENTS Table

The EVENTS table provides information about scheduled events, which are discussed in Section 21.4, “Using the Event Scheduler”. The SHOW Name values correspond to column names of the SHOW EVENTS statement.

INFORMATION_SCHEMA NameSHOW NameRemarks
EVENT_CATALOG def, MySQL extension
EVENT_SCHEMADbMySQL extension
EVENT_NAMENameMySQL extension
DEFINERDefinerMySQL extension
TIME_ZONETime zoneMySQL extension
EVENT_BODY MySQL extension
EVENT_DEFINITION MySQL extension
EVENT_TYPETypeMySQL extension
EXECUTE_ATExecute atMySQL extension
INTERVAL_VALUEInterval valueMySQL extension
INTERVAL_FIELDInterval fieldMySQL extension
SQL_MODE MySQL extension
STARTSStartsMySQL extension
ENDSEndsMySQL extension
STATUSStatusMySQL extension
ON_COMPLETION MySQL extension
CREATED MySQL extension
LAST_ALTERED MySQL extension
LAST_EXECUTED MySQL extension
EVENT_COMMENT MySQL extension
ORIGINATOROriginatorMySQL extension
CHARACTER_SET_CLIENTcharacter_set_clientMySQL extension
COLLATION_CONNECTIONcollation_connectionMySQL extension
DATABASE_COLLATIONDatabase CollationMySQL extension

Notes:

  • The EVENTS table is a nonstandard table.

  • EVENT_CATALOG: The value of this column is always def.

  • EVENT_SCHEMA: The name of the schema (database) to which this event belongs.

  • EVENT_NAME: The name of the event.

  • DEFINER: The account of the user who created the event, in 'user_name'@'host_name' format.

  • TIME_ZONE: The event time zone, which is the time zone used for scheduling the event and that is in effect within the event as it executes. The default value is SYSTEM.

  • EVENT_BODY: The language used for the statements in the event's DO clause; in MySQL 5.7, this is always SQL.

    This column is not to be confused with the column of the same name (now named EVENT_DEFINITION) that existed in earlier MySQL versions.

  • EVENT_DEFINITION: The text of the SQL statement making up the event's DO clause; in other words, the statement executed by this event.

  • EVENT_TYPE: The event repetition type, either ONE TIME (transient) or RECURRING (repeating).

  • EXECUTE_AT: For a one-time event, this is the DATETIME value specified in the AT clause of the CREATE EVENT statement used to create the event, or of the last ALTER EVENT statement that modified the event. The value shown in this column reflects the addition or subtraction of any INTERVAL value included in the event's AT clause. For example, if an event is created using ON SCHEDULE AT CURRENT_TIMESTAMP + '1:6' DAY_HOUR, and the event was created at 2006-02-09 14:05:30, the value shown in this column would be '2006-02-10 20:05:30'.

    If the event's timing is determined by an EVERY clause instead of an AT clause (that is, if the event is recurring), the value of this column is NULL.

  • INTERVAL_VALUE: For recurring events, this column contains the numeric portion of the event's EVERY clause.

    For a one-time event (that is, an event whose timing is determined by an AT clause), this column is NULL.

  • INTERVAL_FIELD: For recurring events, this column contains the units portion of the EVERY clause governing the timing of the event. Thus, this column contains a value such as 'YEAR', 'QUARTER', 'DAY', and so on.

    For a one-time event (that is, an event whose timing is determined by an AT clause), this column is NULL.

  • SQL_MODE: The SQL mode in effect when the event was created or altered, and under which the event executes. For the permitted values, see Section 6.1.7, “Server SQL Modes”.

  • STARTS: For a recurring event whose definition includes a STARTS clause, this column contains the corresponding DATETIME value. As with the EXECUTE_AT column, this value resolves any expressions used.

    If there is no STARTS clause affecting the timing of the event, this column is NULL

  • ENDS: For a recurring event whose definition includes a ENDS clause, this column contains the corresponding DATETIME value. As with the EXECUTE_AT column, this value resolves any expressions used.

    If there is no ENDS clause affecting the timing of the event, this column is NULL.

  • STATUS: One of the three values ENABLED, DISABLED, or SLAVESIDE_DISABLED.

    SLAVESIDE_DISABLED indicates that the creation of the event occurred on another MySQL server acting as a replication master and was replicated to the current MySQL server which is acting as a slave, but the event is not presently being executed on the slave. See Section 18.4.1.12, “Replication of Invoked Features”, for more information.

  • ON_COMPLETION: One of the two values PRESERVE or NOT PRESERVE.

  • CREATED: The date and time when the event was created. This is a TIMESTAMP value.

  • LAST_ALTERED: The date and time when the event was last modified. This is a TIMESTAMP value. If the event has not been modified since its creation, this column holds the same value as the CREATED column.

  • LAST_EXECUTED: The date and time when the event last executed. A DATETIME value. If the event has never executed, this column is NULL.

    LAST_EXECUTED indicates when the event started. As a result, the ENDS column is never less than LAST_EXECUTED.

  • EVENT_COMMENT: The text of a comment, if the event has one. If not, the value of this column is an empty string.

  • ORIGINATOR: The server ID of the MySQL server on which the event was created; used in replication. The default value is 0.

  • CHARACTER_SET_CLIENT: The session value of the character_set_client system variable when the event was created.

  • COLLATION_CONNECTION: The session value of the collation_connection system variable when the event was created.

  • DATABASE_COLLATION: The collation of the database with which the event is associated.

Example: Suppose that the user jon@ghidora creates an event named e_daily, and then modifies it a few minutes later using an ALTER EVENT statement, as shown here:

DELIMITER |

CREATE EVENT e_daily
    ON SCHEDULE
      EVERY 1 DAY
    COMMENT 'Saves total number of sessions then clears the table each day'
    DO
      BEGIN
        INSERT INTO site_activity.totals (time, total)
          SELECT CURRENT_TIMESTAMP, COUNT(*)
            FROM site_activity.sessions;
        DELETE FROM site_activity.sessions;
      END |

DELIMITER ;

ALTER EVENT e_daily
    ENABLED;

(Note that comments can span multiple lines.)

This user can then run the following SELECT statement, and obtain the output shown:

mysql> SELECT * FROM INFORMATION_SCHEMA.EVENTS
     > WHERE EVENT_NAME = 'e_daily'
     > AND EVENT_SCHEMA = 'myschema'\G
*************************** 1. row ***************************
       EVENT_CATALOG: def
        EVENT_SCHEMA: test
          EVENT_NAME: e_daily
             DEFINER: me@localhost
           TIME_ZONE: SYSTEM
          EVENT_BODY: SQL
    EVENT_DEFINITION: BEGIN
        INSERT INTO site_activity.totals (time, total)
          SELECT CURRENT_TIMESTAMP, COUNT(*)
            FROM site_activity.sessions;
        DELETE FROM site_activity.sessions;
      END
          EVENT_TYPE: RECURRING
          EXECUTE_AT: NULL
      INTERVAL_VALUE: 1
      INTERVAL_FIELD: DAY
            SQL_MODE:
              STARTS: 2008-09-03 12:13:39
                ENDS: NULL
              STATUS: ENABLED
       ON_COMPLETION: NOT PRESERVE
             CREATED: 2008-09-03 12:13:39
        LAST_ALTERED: 2008-09-03 12:13:39
       LAST_EXECUTED: NULL
       EVENT_COMMENT: Saves total number of sessions then clears the
                      table each day
          ORIGINATOR: 1
CHARACTER_SET_CLIENT: latin1
COLLATION_CONNECTION: latin1_swedish_ci
  DATABASE_COLLATION: latin1_swedish_ci

Times in the EVENTS table are displayed using the event time zone or the current session time zone, as described in Section 21.4.4, “Event Metadata”.

See also Section 14.7.5.18, “SHOW EVENTS Syntax”.

22.8 The INFORMATION_SCHEMA FILES Table

The FILES table provides information about the files in which MySQL tablespace data is stored.

INFORMATION_SCHEMA.FILES provides information about InnoDB data files (MySQL 5.7.8 and later). In MySQL Cluster this table also provides information about the files in which MySQL Cluster Disk Data tables are stored. For information specific to InnoDB, see InnoDB Notes, later in this section; for information specific to MySQL Cluster, see NDB Notes.

INFORMATION_SCHEMA NameSHOW NameRemarks
FILE_ID MySQL extension
FILE_NAME MySQL extension
FILE_TYPE MySQL extension
TABLESPACE_NAME MySQL extension
TABLE_CATALOG MySQL extension
TABLE_SCHEMA MySQL extension
TABLE_NAME MySQL extension
LOGFILE_GROUP_NAME MySQL extension
LOGFILE_GROUP_NUMBER MySQL extension
ENGINE MySQL extension
FULLTEXT_KEYS MySQL extension
DELETED_ROWS MySQL extension
UPDATE_COUNT MySQL extension
FREE_EXTENTS MySQL extension
TOTAL_EXTENTS MySQL extension
EXTENT_SIZE MySQL extension
INITIAL_SIZE MySQL extension
MAXIMUM_SIZE MySQL extension
AUTOEXTEND_SIZE MySQL extension
CREATION_TIME MySQL extension
LAST_UPDATE_TIME MySQL extension
LAST_ACCESS_TIME MySQL extension
RECOVER_TIME MySQL extension
TRANSACTION_COUNTER MySQL extension
VERSION MySQL extension
ROW_FORMAT MySQL extension
TABLE_ROWS MySQL extension
AVG_ROW_LENGTH MySQL extension
DATA_LENGTH MySQL extension
MAX_DATA_LENGTH MySQL extension
INDEX_LENGTH MySQL extension
DATA_FREE MySQL extension
CREATE_TIME MySQL extension
UPDATE_TIME MySQL extension
CHECK_TIME MySQL extension
CHECKSUM MySQL extension
STATUS MySQL extension
EXTRA MySQL extension

InnoDB Notes

The following notes apply to InnoDB data files. INFORMATION_SCHEMA.FILES fields that are not described below are not applicable to InnoDB and report a NULL value.

  • Data reported by INFORMATION_SCHEMA.FILES is reported from the InnoDB in-memory cache for open files. By comparison, INFORMATION_SCHEMA.INNODB_SYS_DATAFILES reports data from the InnoDB SYS_DATAFILES internal data dictionary table.

  • The data reported by INFORMATION_SCHEMA.FILES includes temporary tablespace data. This data is not available in the internal SYS_DATAFILES data dictionary table, and is therefore not reported by INNODB_SYS_DATAFILES.

  • Undo tablespace data is reported by INFORMATION_SCHEMA.FILES when separate undo tablespaces are configured using the innodb_undo_tablespaces configuration option.

  • FILE_ID is the tablespace ID, also referred to as the space_id or fil_space_t::id.

  • FILE_NAME is the name of the data file. File-per-table and general tablespaces have a .ibd file name extension. Undo tablespaces are prefixed by undo. The system tablespace is prefixed by ibdata. Temporary tablespaces are prefixed by ibtmp. The file name includes the file path, which may be relative to the MySQL data directory (datadir).

  • FILE_TYPE is the tablespace file type. There are three possible file types for InnoDB files. TABLESPACE is the file type for any system, general, or file-per-table tablespace file that holds tables, indexes, or other forms of user data. TEMPORARY is the file type for temporary tablespaces. UNDO LOG is the file type for undo log tablespaces, which hold undo records. By default, undo records are stored in the system tablespace. Separate undo log tablespaces can be added using the innodb_undo_tablespaces option.

  • TABLESPACE_NAME is the SQL name for the tablespace. A general tablespace name is the SYS_TABLESPACES.NAME value. For other tablespace files, names start with innodb_, such as innodb_system, innodb_undo, and innodb_file_per_table. The file-per-table tablespace name format is innodb_file_per_table_##, where ## is the tablespace ID.

  • ENGINE is the storage engine. For InnoDB files, the value is always InnoDB.

  • FREE_EXTENTS is the number of fully free extents in the current data file.

  • TOTAL_EXTENTS is the number of full extents used in the current data file. Any partial extent at the end of the file is not counted.

  • EXTENT_SIZE is 1048576 (1MB) for files with a 4k, 8k, or 16k page size. Extent size is 2097152 bytes (2MB) for files with a 32k page size, and 4194304 (4MB) for files with a 64k page size. INFORMATION_SCHEMA.FILES does not report InnoDB page size. Page size is defined by the innodb_page_size option. Extent size information can also be retrieved from INNODB_SYS_TABLESPACES where FILES.FILE_ID = INNODB_SYS_TABLESPACES.SPACE_ID.

  • INITIAL_SIZE is the initial size of the file, in bytes.

  • MAXIMUM_SIZE is the maximum number of bytes allowed in the file. The value is NULL for all data files except for predefined system tablespace data files. Maximum system tablespace file size is defined by innodb_data_file_path. Maximum temporary tablespace file size is defined by innodb_temp_data_file_path. A NULL value for a predefined system tablespace data file indicates that a file size limit was not defined explicitly.

  • AUTOEXTEND_SIZE is the auto-extend size defined by innodb_data_file_path for the system tablespace, or defined by innodb_temp_data_file_path for temporary tablespaces.

  • DATA_FREE is the total amount of free space (in bytes) for the entire tablespace. Predefined system tablespaces, which include the system tablespace and temporary table tablespaces, may have one or more data files.

  • STATUS is NORMAL by default. InnoDB file-per-table tablespaces may report IMPORTING, which indicates that the tablespace is not yet available.

  • The following query returns all data pertinent to InnoDB tablespaces.

    mysql> SELECT FILE_ID, FILE_NAME, FILE_TYPE, TABLESPACE_NAME, FREE_EXTENTS, TOTAL_EXTENTS, 
    EXTENT_SIZE, INITIAL_SIZE, MAXIMUM_SIZE, AUTOEXTEND_SIZE, DATA_FREE, STATUS ENGINE 
    FROM INFORMATION_SCHEMA.FILES \G

NDB Notes

  • The FILES table provides information about Disk Data files only; you cannot use it for determining disk space allocation or availability for individual NDB tables. However, it is possible to see how much space is allocated for each NDB table having data stored on disk—as well as how much remains available for storage of data on disk for that table—using ndb_desc. For more information, see Section 19.4.10, “ndb_desc — Describe NDB Tables”.

  • FILE_ID column values are auto-generated.

  • FILE_NAME is the name of an UNDO log file created by CREATE LOGFILE GROUP or ALTER LOGFILE GROUP, or of a data file created by CREATE TABLESPACE or ALTER TABLESPACE.

  • FILE_TYPE is one of the values UNDOFILE, DATAFILE, or TABLESPACE.

  • TABLESPACE_NAME is the name of the tablespace with which the file is associated.

  • Currently, the value of the TABLESPACE_CATALOG column is always NULL.

  • TABLE_NAME is the name of the Disk Data table with which the file is associated, if any.

  • The LOGFILE_GROUP_NAME column gives the name of the log file group to which the log file or data file belongs.

  • For an UNDO log file, the LOGFILE_GROUP_NUMBER contains the auto-generated ID number of the log file group to which the log file belongs.

  • For a MySQL Cluster Disk Data log file or data file, the value of the ENGINE column is always NDB or NDBCLUSTER.

  • For a MySQL Cluster Disk Data log file or data file, the value of the FULLTEXT_KEYS column is always empty.

  • The FREE EXTENTS column displays the number of extents which have not yet been used by the file. The TOTAL EXTENTS column show the total number of extents allocated to the file.

    The difference between these two columns is the number of extents currently in use by the file:

    SELECT TOTAL_EXTENTS - FREE_EXTENTS AS extents_used
        FROM INFORMATION_SCHEMA.FILES
        WHERE FILE_NAME = 'myfile.dat';
    

    You can approximate the amount of disk space in use by the file by multiplying this difference by the value of the EXTENT_SIZE column, which gives the size of an extent for the file in bytes:

    SELECT (TOTAL_EXTENTS - FREE_EXTENTS) * EXTENT_SIZE AS bytes_used
        FROM INFORMATION_SCHEMA.FILES
        WHERE FILE_NAME = 'myfile.dat';
    

    Similarly, you can estimate the amount of space that remains available in a given file by multiplying FREE_EXTENTS by EXTENT_SIZE:

    SELECT FREE_EXTENTS * EXTENT_SIZE AS bytes_free
        FROM INFORMATION_SCHEMA.FILES
        WHERE FILE_NAME = 'myfile.dat';
    
    Important

    The byte values produced by the preceding queries are approximations only, and their precision is inversely proportional to the value of EXTENT_SIZE. That is, the larger EXTENT_SIZE becomes, the less accurate the approximations are.

    It is also important to remember that once an extent is used, it cannot be freed again without dropping the data file of which it is a part. This means that deletes from a Disk Data table do not release disk space.

    The extent size can be set in a CREATE TABLESPACE statement. See Section 14.1.19, “CREATE TABLESPACE Syntax”, for more information.

  • The INITIAL_SIZE column shows the size in bytes of the file. This is the same value that was used in the INITIAL_SIZE clause of the CREATE LOGFILE GROUP, ALTER LOGFILE GROUP, CREATE TABLESPACE, or ALTER TABLESPACE statement used to create the file.

    For MySQL Cluster Disk Data files, the value of the MAXIMUM_SIZE column is always the same as INITIAL_SIZE, and the AUTOEXTEND_SIZE column is always empty.

  • The CREATION_TIME column shows the date and time when the file was created. The LAST_UPDATE_TIME column displays the date and time when the file was last modified. The LAST_ACCESSED column provides the date and time when the file was last accessed by the server.

    Currently, the values of these columns are as reported by the operating system, and are not supplied by the NDB storage engine. Where no value is provided by the operating system, these columns display 0000-00-00 00:00:00.

  • For MySQL Cluster Disk Data files, the value of the RECOVER_TIME and TRANSACTION_COUNTER columns is always 0.

  • For MySQL Cluster Disk Data files, the following columns are always NULL:

    • VERSION

    • ROW_FORMAT

    • TABLE_ROWS

    • AVG_ROW_LENGTH

    • DATA_LENGTH

    • MAX_DATA_LENGTH

    • INDEX_LENGTH

    • DATA_FREE

    • CREATE_TIME

    • UPDATE_TIME

    • CHECK_TIME

    • CHECKSUM

  • For MySQL Cluster Disk Data files, the value of the STATUS column is always NORMAL.

  • For MySQL Cluster Disk Data files, the EXTRA column shows which data node the file belongs to, as each data node has its own copy of the file. Suppose that you use this statement on a MySQL Cluster with four data nodes:

    CREATE LOGFILE GROUP mygroup
        ADD UNDOFILE 'new_undo.dat'
        INITIAL_SIZE 2G
        ENGINE NDB;
    

    After running the CREATE LOGFILE GROUP statement successfully, you should see a result similar to the one shown here for this query against the FILES table:

    mysql> SELECT LOGFILE_GROUP_NAME, FILE_TYPE, EXTRA
        ->     FROM INFORMATION_SCHEMA.FILES
        ->     WHERE FILE_NAME = 'new_undo.dat';
    +--------------------+-------------+----------------+
    | LOGFILE_GROUP_NAME | FILE_TYPE   | EXTRA          |
    +--------------------+-------------+----------------+
    | mygroup            | UNDO FILE   | CLUSTER_NODE=3 |
    | mygroup            | UNDO FILE   | CLUSTER_NODE=4 |
    | mygroup            | UNDO FILE   | CLUSTER_NODE=5 |
    | mygroup            | UNDO FILE   | CLUSTER_NODE=6 |
    +--------------------+-------------+----------------+
    4 rows in set (0.01 sec)
    
  • The FILES table is a nonstandard table.

  • An additional row is present in the FILES table following the creation of a logfile group. This row has NULL for the value of the FILE_NAME column. For this row, the value of the FILE_ID column is always 0, that of the FILE_TYPE column is always UNDO FILE, and that of the STATUS column is always NORMAL. Currently, the value of the ENGINE column is always NDBCLUSTER.

    The FREE_EXTENTS column in this row shows the total number of free extents available to all undo files belonging to a given log file group whose name and number are shown in the LOGFILE_GROUP_NAME and LOGFILE_GROUP_NUMBER columns, respectively.

    Suppose there are no existing log file groups on your MySQL Cluster, and you create one using the following statement:

    mysql> CREATE LOGFILE GROUP lg1
        ->   ADD UNDOFILE 'undofile.dat'
        ->   INITIAL_SIZE = 16M
        ->   UNDO_BUFFER_SIZE = 1M
        ->   ENGINE = NDB;
    Query OK, 0 rows affected (3.81 sec)
    

    You can now see this NULL row when you query the FILES table:

    mysql> SELECT DISTINCT
        ->   FILE_NAME AS File,
        ->   FREE_EXTENTS AS Free,
        ->   TOTAL_EXTENTS AS Total,
        ->   EXTENT_SIZE AS Size,
        ->   INITIAL_SIZE AS Initial
        ->   FROM INFORMATION_SCHEMA.FILES;
    +--------------+---------+---------+------+----------+
    | File         | Free    | Total   | Size | Initial  |
    +--------------+---------+---------+------+----------+
    | undofile.dat |    NULL | 4194304 |    4 | 16777216 |
    | NULL         | 4184068 |    NULL |    4 |     NULL |
    +--------------+---------+---------+------+----------+
    2 rows in set (0.01 sec)
    

    The total number of free extents available for undo logging is always somewhat less than the sum of the TOTAL_EXTENTS column values for all undo files in the log file group due to overhead required for maintaining the undo files. This can be seen by adding a second undo file to the log file group, then repeating the previous query against the FILES table:

    mysql> ALTER LOGFILE GROUP lg1
        ->   ADD UNDOFILE 'undofile02.dat'
        ->   INITIAL_SIZE = 4M
        ->   ENGINE = NDB;
    Query OK, 0 rows affected (1.02 sec)
    
    mysql> SELECT DISTINCT
        ->   FILE_NAME AS File,
        ->   FREE_EXTENTS AS Free,
        ->   TOTAL_EXTENTS AS Total,
        ->   EXTENT_SIZE AS Size,
        ->   INITIAL_SIZE AS Initial
        ->   FROM INFORMATION_SCHEMA.FILES;
    +----------------+---------+---------+------+----------+
    | File           | Free    | Total   | Size | Initial  |
    +----------------+---------+---------+------+----------+
    | undofile.dat   |    NULL | 4194304 |    4 | 16777216 |
    | undofile02.dat |    NULL | 1048576 |    4 |  4194304 |
    | NULL           | 5223944 |    NULL |    4 |     NULL |
    +----------------+---------+---------+------+----------+
    3 rows in set (0.01 sec)
    

    The amount of free space in bytes which is available for undo logging by Disk Data tables using this log file group can be approximated by multiplying the number of free extents by the initial size:

    mysql> SELECT
        ->   FREE_EXTENTS AS 'Free Extents',
        ->   FREE_EXTENTS * EXTENT_SIZE AS 'Free Bytes'
        ->   FROM INFORMATION_SCHEMA.FILES
        ->   WHERE LOGFILE_GROUP_NAME = 'lg1'
        ->   AND FILE_NAME IS NULL;
    +--------------+------------+
    | Free Extents | Free Bytes |
    +--------------+------------+
    |      5223944 |   20895776 |
    +--------------+------------+
    1 row in set (0.02 sec)
    

    If you create a MySQL Cluster Disk Data table and then insert some rows into it, you can see approximately how much space remains for undo logging afterward, for example:

    mysql> CREATE TABLESPACE ts1
        ->   ADD DATAFILE 'data1.dat'
        ->   USE LOGFILE GROUP lg1
        ->   INITIAL_SIZE 512M
        ->   ENGINE = NDB;
    Query OK, 0 rows affected (8.71 sec)
    
    mysql> CREATE TABLE dd (
        ->   c1 INT NOT NULL PRIMARY KEY,
        ->   c2 INT,
        ->   c3 DATE
        ->   )
        ->   TABLESPACE ts1 STORAGE DISK
        ->   ENGINE = NDB;
    Query OK, 0 rows affected (2.11 sec)
    
    mysql> INSERT INTO dd VALUES
        ->   (NULL, 1234567890, '2007-02-02'),
        ->   (NULL, 1126789005, '2007-02-03'),
        ->   (NULL, 1357924680, '2007-02-04'),
        ->   (NULL, 1642097531, '2007-02-05');
    Query OK, 4 rows affected (0.01 sec)
    
    mysql> SELECT
        ->   FREE_EXTENTS AS 'Free Extents',
        ->   FREE_EXTENTS * EXTENT_SIZE AS 'Free Bytes'
        ->   FROM INFORMATION_SCHEMA.FILES
        ->   WHERE LOGFILE_GROUP_NAME = 'lg1'
        ->   AND FILE_NAME IS NULL;
    +--------------+------------+
    | Free Extents | Free Bytes |
    +--------------+------------+
    |      5207565 |   20830260 |
    +--------------+------------+
    1 row in set (0.01 sec)
    
  • An additional row is present in the FILES table for any MySQL Cluster tablespace, whether or not any data files are associated with the tablespace. This row has NULL for the value of the FILE_NAME column. For this row, the value of the FILE_ID column is always 0, that of the FILE_TYPE column is always TABLESPACE, and that of the STATUS column is always NORMAL. Currently, the value of the ENGINE column is always NDBCLUSTER.

  • There are no SHOW statements associated with the FILES table.

  • For additional information, and examples of creating and dropping MySQL Cluster Disk Data objects, see Section 19.5.13, “MySQL Cluster Disk Data Tables”.

22.9 The INFORMATION_SCHEMA GLOBAL_STATUS and SESSION_STATUS Tables

Note

As of MySQL 5.7.6, the value of the show_compatibility_56 system variable affects the information available from the tables described here. For details, see the description of that variable in Section 6.1.4, “Server System Variables”.

Note

As of MySQL 5.7.6, information available from the tables described here is also available from the Performance Schema. The INFORMATION_SCHEMA tables are deprecated in preference to the Performance Schema tables and will be removed in a future MySQL release. For advice on migrating away from the INFORMATION_SCHEMA tables to the Performance Schema tables, see Section 23.17, “Migrating to Performance Schema System and Status Variable Tables”.

The GLOBAL_STATUS and SESSION_STATUS tables provide information about server status variables. Their contents correspond to the information produced by the SHOW GLOBAL STATUS and SHOW SESSION STATUS statements (see Section 14.7.5.35, “SHOW STATUS Syntax”).

INFORMATION_SCHEMA NameSHOW NameRemarks
VARIABLE_NAMEVariable_name 
VARIABLE_VALUEValue 

Notes:

  • The VARIABLE_VALUE column for each of these tables is defined as VARCHAR(1024).

22.10 The INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES Tables

Note

As of MySQL 5.7.6, the value of the show_compatibility_56 system variable affects the information available from the tables described here. For details, see the description of that variable in Section 6.1.4, “Server System Variables”.

Note

As of MySQL 5.7.6, information available from the tables described here is also available from the Performance Schema. The INFORMATION_SCHEMA tables are deprecated in preference to the Performance Schema tables and will be removed in a future MySQL release. For advice on migrating away from the INFORMATION_SCHEMA tables to the Performance Schema tables, see Section 23.17, “Migrating to Performance Schema System and Status Variable Tables”.

The GLOBAL_VARIABLES and SESSION_VARIABLES tables provide information about server status variables. Their contents correspond to the information produced by the SHOW GLOBAL VARIABLES and SHOW SESSION VARIABLES statements (see Section 14.7.5.39, “SHOW VARIABLES Syntax”).

INFORMATION_SCHEMA NameSHOW NameRemarks
VARIABLE_NAMEVariable_name 
VARIABLE_VALUEValue 

Notes:

  • The VARIABLE_VALUE column for each of these tables is defined as VARCHAR(1024). For variables with very long values that are not completely displayed, use SELECT as a workaround. For example:

    SELECT @@GLOBAL.innodb_data_file_path;
    

22.11 The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table

The KEY_COLUMN_USAGE table describes which key columns have constraints.

INFORMATION_SCHEMA NameSHOW NameRemarks
CONSTRAINT_CATALOG def
CONSTRAINT_SCHEMA  
CONSTRAINT_NAME  
TABLE_CATALOG def
TABLE_SCHEMA  
TABLE_NAME  
COLUMN_NAME  
ORDINAL_POSITION  
POSITION_IN_UNIQUE_CONSTRAINT  
REFERENCED_TABLE_SCHEMA  
REFERENCED_TABLE_NAME  
REFERENCED_COLUMN_NAME  

Notes:

  • If the constraint is a foreign key, then this is the column of the foreign key, not the column that the foreign key references.

  • The value of ORDINAL_POSITION is the column's position within the constraint, not the column's position within the table. Column positions are numbered beginning with 1.

  • The value of POSITION_IN_UNIQUE_CONSTRAINT is NULL for unique and primary-key constraints. For foreign-key constraints, it is the ordinal position in key of the table that is being referenced.

    Suppose that there are two tables name t1 and t3 that have the following definitions:

    CREATE TABLE t1
    (
        s1 INT,
        s2 INT,
        s3 INT,
        PRIMARY KEY(s3)
    ) ENGINE=InnoDB;
    
    CREATE TABLE t3
    (
        s1 INT,
        s2 INT,
        s3 INT,
        KEY(s1),
        CONSTRAINT CO FOREIGN KEY (s2) REFERENCES t1(s3)
    ) ENGINE=InnoDB;
    

    For those two tables, the KEY_COLUMN_USAGE table has two rows:

    • One row with CONSTRAINT_NAME = 'PRIMARY', TABLE_NAME = 't1', COLUMN_NAME = 's3', ORDINAL_POSITION = 1, POSITION_IN_UNIQUE_CONSTRAINT = NULL.

    • One row with CONSTRAINT_NAME = 'CO', TABLE_NAME = 't3', COLUMN_NAME = 's2', ORDINAL_POSITION = 1, POSITION_IN_UNIQUE_CONSTRAINT = 1.

22.12 The INFORMATION_SCHEMA ndb_transid_mysql_connection_map Table

The ndb_transid_mysql_connection_map table provides a mapping between NDB transactions, NDB transaction coordinators, and MySQL Servers attached to a MySQL Cluster as API nodes. This information is used when populating the server_operations and server_transactions tables of the ndbinfo MySQL Cluster information database.

INFORMATION_SCHEMA NameSHOW NameRemarks
mysql_connection_id MySQL Server connection ID
node_id Transaction coordinator node ID
ndb_transid NDB transaction ID

The mysql_connection_id is the same as the connection or session ID shown in the output of SHOW PROCESSLIST.

There are no SHOW statements associated with this table.

This is a nonstandard table, specific to MySQL Cluster. It is implemented as an INFORMATION_SCHEMA plugin; you can verify that it is supported by checking the output of SHOW PLUGINS. If ndb_transid_mysql_connection_map support is enabled, the output from this statement includes a plugin having this name, of type INFORMATION SCHEMA, and having status ACTIVE, as shown here (using emphasized text):

mysql> SHOW PLUGINS;
+----------------------------------+--------+--------------------+---------+---------+
| Name                             | Status | Type               | Library | License |
+----------------------------------+--------+--------------------+---------+---------+
| binlog                           | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password            | ACTIVE | AUTHENTICATION     | NULL    | GPL     |
| mysql_old_password               | ACTIVE | AUTHENTICATION     | NULL    | GPL     |
| CSV                              | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                           | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| MRG_MYISAM                       | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| MyISAM                           | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| PERFORMANCE_SCHEMA               | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| BLACKHOLE                        | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                          | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| ndbcluster                       | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| ndbinfo                          | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| ndb_transid_mysql_connection_map | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| InnoDB                           | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX                       | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCKS                     | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCK_WAITS                | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP                       | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_RESET                 | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM                    | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM_RESET              | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| partition                        | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
+----------------------------------+--------+--------------------+---------+---------+
22 rows in set (0.00 sec)

The plugin is enabled by default. You can disable it (or force the server not to run unless the plugin starts) by starting the server with the --ndb-transid-mysql-connection-map option. If the plugin is disabled, the status is shown by SHOW PLUGINS as DISABLED. The plugin cannot be enabled or disabled at runtime.

Although the names of this table and its columns are displayed using lowercase, you can use uppercase or lowercase when referring to them in SQL statements.

For this table to be created, the MySQL Server must be a binary that wass supplied with the MySQL Cluster distribution, or one that was built from the MySQL Cluster sources with NDB storage engine support enabled. It is not available in the standard MySQL 5.6 Server.

22.13 The INFORMATION_SCHEMA OPTIMIZER_TRACE Table

The OPTIMIZER_TRACE table provides information produced by the optimizer tracing capability. To enable tracking, use the optimizer_trace system variable. For details, see MySQL Internals: Tracing the Optimizer.

22.14 The INFORMATION_SCHEMA PARAMETERS Table

The PARAMETERS table provides information about stored procedure and function parameters, and about return values for stored functions. Parameter information is similar to the contents of the param_list column in the mysql.proc table.

INFORMATION_SCHEMA Namemysql.proc NameRemarks
SPECIFIC_CATALOG def
SPECIFIC_SCHEMAdbroutine database
SPECIFIC_NAMEnameroutine name
ORDINAL_POSITION 1, 2, 3, ... for parameters, 0 for function RETURNS clause
PARAMETER_MODE IN, OUT, INOUT (NULL for RETURNS)
PARAMETER_NAME parameter name (NULL for RETURNS)
DATA_TYPE same as for COLUMNS table
CHARACTER_MAXIMUM_LENGTH same as for COLUMNS table
CHARACTER_OCTET_LENGTH same as for COLUMNS table
NUMERIC_PRECISION same as for COLUMNS table
NUMERIC_SCALE same as for COLUMNS table
DATETIME_PRECISION same as for COLUMNS table
CHARACTER_SET_NAME same as for COLUMNS table
COLLATION_NAME same as for COLUMNS table
DTD_IDENTIFIER same as for COLUMNS table
ROUTINE_TYPEtypesame as for ROUTINES table

Notes:

  • For successive parameters of a stored procedure or function, the ORDINAL_POSITION values are 1, 2, 3, and so forth. For a stored function, there is also a row that describes the data type for the RETURNS clause. The return value is not a true parameter, so the row that describes it has these unique characteristics:

    • The ORDINAL_POSITION value is 0.

    • The PARAMETER_NAME and PARAMETER_MODE values are NULL because the return value has no name and the mode does not apply.

22.15 The INFORMATION_SCHEMA PARTITIONS Table

The PARTITIONS table provides information about table partitions. See Chapter 20, Partitioning, for more information about partitioning tables.

INFORMATION_SCHEMA NameSHOW NameRemarks
TABLE_CATALOG MySQL extension
TABLE_SCHEMA MySQL extension
TABLE_NAME MySQL extension
PARTITION_NAME MySQL extension
SUBPARTITION_NAME MySQL extension
PARTITION_ORDINAL_POSITION MySQL extension
SUBPARTITION_ORDINAL_POSITION MySQL extension
PARTITION_METHOD MySQL extension
SUBPARTITION_METHOD MySQL extension
PARTITION_EXPRESSION MySQL extension
SUBPARTITION_EXPRESSION MySQL extension
PARTITION_DESCRIPTION MySQL extension
TABLE_ROWS MySQL extension
AVG_ROW_LENGTH MySQL extension
DATA_LENGTH MySQL extension
MAX_DATA_LENGTH MySQL extension
INDEX_LENGTH MySQL extension
DATA_FREE MySQL extension
CREATE_TIME MySQL extension
UPDATE_TIME MySQL extension
CHECK_TIME MySQL extension
CHECKSUM MySQL extension
PARTITION_COMMENT MySQL extension
NODEGROUP MySQL extension
TABLESPACE_NAME MySQL extension

Notes:

  • The PARTITIONS table is a nonstandard table.

    Each record in this table corresponds to an individual partition or subpartition of a partitioned table.

  • TABLE_CATALOG: This column is always def.

  • TABLE_SCHEMA: This column contains the name of the database to which the table belongs.

  • TABLE_NAME: This column contains the name of the table containing the partition.

  • PARTITION_NAME: The name of the partition.

  • SUBPARTITION_NAME: If the PARTITIONS table record represents a subpartition, then this column contains the name of subpartition; otherwise it is NULL.

  • PARTITION_ORDINAL_POSITION: All partitions are indexed in the same order as they are defined, with 1 being the number assigned to the first partition. The indexing can change as partitions are added, dropped, and reorganized; the number shown is this column reflects the current order, taking into account any indexing changes.

  • SUBPARTITION_ORDINAL_POSITION: Subpartitions within a given partition are also indexed and reindexed in the same manner as partitions are indexed within a table.

  • PARTITION_METHOD: One of the values RANGE, LIST, HASH, LINEAR HASH, KEY, or LINEAR KEY; that is, one of the available partitioning types as discussed in Section 20.2, “Partitioning Types”.

  • SUBPARTITION_METHOD: One of the values HASH, LINEAR HASH, KEY, or LINEAR KEY; that is, one of the available subpartitioning types as discussed in Section 20.2.6, “Subpartitioning”.

  • PARTITION_EXPRESSION: This is the expression for the partitioning function used in the CREATE TABLE or ALTER TABLE statement that created the table's current partitioning scheme.

    For example, consider a partitioned table created in the test database using this statement:

    CREATE TABLE tp (
        c1 INT,
        c2 INT,
        c3 VARCHAR(25)
    )
    PARTITION BY HASH(c1 + c2)
    PARTITIONS 4;
    

    The PARTITION_EXPRESSION column in a PARTITIONS table record for a partition from this table displays c1 + c2, as shown here:

    mysql> SELECT DISTINCT PARTITION_EXPRESSION
         >     FROM INFORMATION_SCHEMA.PARTITIONS
         >     WHERE TABLE_NAME='tp' AND TABLE_SCHEMA='test';
    +----------------------+
    | PARTITION_EXPRESSION |
    +----------------------+
    | c1 + c2              |
    +----------------------+
    1 row in set (0.09 sec)
    
  • SUBPARTITION_EXPRESSION: This works in the same fashion for the subpartitioning expression that defines the subpartitioning for a table as PARTITION_EXPRESSION does for the partitioning expression used to define a table's partitioning.

    If the table has no subpartitions, then this column is NULL.

  • PARTITION_DESCRIPTION: This column is used for RANGE and LIST partitions. For a RANGE partition, it contains the value set in the partition's VALUES LESS THAN clause, which can be either an integer or MAXVALUE. For a LIST partition, this column contains the values defined in the partition's VALUES IN clause, which is a comma-separated list of integer values.

    For partitions whose PARTITION_METHOD is other than RANGE or LIST, this column is always NULL.

  • TABLE_ROWS: The number of table rows in the partition.

    For partitioned InnoDB tables, the row count given in the TABLE_ROWS column is only an estimated value used in SQL optimization, and may not always be exact.

  • AVG_ROW_LENGTH: The average length of the rows stored in this partition or subpartition, in bytes.

    This is the same as DATA_LENGTH divided by TABLE_ROWS.

  • DATA_LENGTH: The total length of all rows stored in this partition or subpartition, in bytes—that is, the total number of bytes stored in the partition or subpartition.

  • MAX_DATA_LENGTH: The maximum number of bytes that can be stored in this partition or subpartition.

  • INDEX_LENGTH: The length of the index file for this partition or subpartition, in bytes.

  • DATA_FREE: The number of bytes allocated to the partition or subpartition but not used.

  • CREATE_TIME: The time of the partition's or subpartition's creation.

    Prior to MySQL 5.7.8, for partitioned InnoDB tables, this column was always NULL. The correct creation time is shown in MySQL 5.7.8 and later. (Bug #17299181, Bug #69990)

  • UPDATE_TIME: The time that the partition or subpartition was last modified.

    Prior to MySQL 5.7.8, for partitioned InnoDB tables, this column was always NULL. The correct modification time is shown in MySQL 5.7.8 and later. (Bug #17299181, Bug #69990)

  • CHECK_TIME: The last time that the table to which this partition or subpartition belongs was checked.

    For partitioned InnoDB tables, this column is always NULL.

  • CHECKSUM: The checksum value, if any; otherwise, this column is NULL.

  • PARTITION_COMMENT: This column contains the text of any comment made for the partition.

    In MySQL 5.7, the maximum length for a partition comment is defined as 1024 characters, and the display width of the PARTITION_COMMENT column is also 1024, characters to match this limit (Bug #11748924, Bug #37728).

    The default value for this column is an empty string.

  • NODEGROUP: This is the nodegroup to which the partition belongs. This is relevant only to MySQL Cluster tables; otherwise the value of this column is always 0.

  • TABLESPACE_NAME: This column contains the name of the tablespace to which the partition belongs. The value of this column is always DEFAULT.

  • A nonpartitioned table has one record in INFORMATION_SCHEMA.PARTITIONS; however, the values of the PARTITION_NAME, SUBPARTITION_NAME, PARTITION_ORDINAL_POSITION, SUBPARTITION_ORDINAL_POSITION, PARTITION_METHOD, SUBPARTITION_METHOD, PARTITION_EXPRESSION, SUBPARTITION_EXPRESSION, and PARTITION_DESCRIPTION columns are all NULL. (The PARTITION_COMMENT column in this case is blank.)

22.16 The INFORMATION_SCHEMA PLUGINS Table

The PLUGINS table provides information about server plugins.

INFORMATION_SCHEMA NameSHOW NameRemarks
PLUGIN_NAMENameMySQL extension
PLUGIN_VERSION MySQL extension
PLUGIN_STATUSStatusMySQL extension
PLUGIN_TYPETypeMySQL extension
PLUGIN_TYPE_VERSION MySQL extension
PLUGIN_LIBRARYLibraryMySQL extension
PLUGIN_LIBRARY_VERSION MySQL extension
PLUGIN_AUTHOR MySQL extension
PLUGIN_DESCRIPTION MySQL extension
PLUGIN_LICENSELicenseMySQL extension
LOAD_OPTION MySQL extension

Notes:

  • The PLUGINS table is a nonstandard table.

  • PLUGIN_NAME is the name used to refer to the plugin in statements such as INSTALL PLUGIN and UNINSTALL PLUGIN.

  • PLUGIN_VERSION is the version from the plugin's general type descriptor.

  • PLUGIN_STATUS indicates the plugin status, one of ACTIVE, INACTIVE, DISABLED, or DELETED.

  • PLUGIN_TYPE indicates the type of plugin, such as STORAGE ENGINE, INFORMATION_SCHEMA, or AUTHENTICATION.

  • PLUGIN_TYPE_VERSION is the version from the plugin's type-specific descriptor.

  • PLUGIN_LIBRARY is the name of the plugin shared library file. This is the name used to refer to the plugin file in statements such as INSTALL PLUGIN and UNINSTALL PLUGIN. This file is located in the directory named by the plugin_dir system variable. If the library name is NULL, the plugin is compiled in and cannot be uninstalled with UNINSTALL PLUGIN.

  • PLUGIN_LIBRARY_VERSION indicates the plugin API interface version.

  • PLUGIN_AUTHOR names the plugin author.

  • PLUGIN_DESCRIPTION provides a short description of the plugin.

  • PLUGIN_LICENSE indicates how the plugin is licensed; for example, GPL.

  • LOAD_OPTION indicates how the plugin was loaded. The value is OFF, ON, FORCE, or FORCE_PLUS_PERMANENT. See Section 6.5.2, “Installing and Uninstalling Plugins”.

For plugins installed with INSTALL PLUGIN, the PLUGIN_NAME and PLUGIN_LIBRARY values are also registered in the mysql.plugin table.

These statements are equivalent:

SELECT
  PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE,
  PLUGIN_LIBRARY, PLUGIN_LICENSE
FROM INFORMATION_SCHEMA.PLUGINS;

SHOW PLUGINS;

For information about plugin data structures that form the basis of the information in the PLUGINS table, see Section 26.2, “The MySQL Plugin API”.

Plugin information is also available using the SHOW PLUGINS statement. See Section 14.7.5.25, “SHOW PLUGINS Syntax”.

22.17 The INFORMATION_SCHEMA PROCESSLIST Table

The PROCESSLIST table provides information about which threads are running.

INFORMATION_SCHEMA NameSHOW NameRemarks
IDIdMySQL extension
USERUserMySQL extension
HOSTHostMySQL extension
DBdbMySQL extension
COMMANDCommandMySQL extension
TIMETimeMySQL extension
STATEStateMySQL extension
INFOInfoMySQL extension

For an extensive description of the table columns, see Section 14.7.5.29, “SHOW PROCESSLIST Syntax”.

Notes:

  • The PROCESSLIST table is a nonstandard table.

  • Like the output from the corresponding SHOW statement, the PROCESSLIST table will only show information about your own threads, unless you have the PROCESS privilege, in which case you will see information about other threads, too. As an anonymous user, you cannot see any rows at all.

  • If an SQL statement refers to INFORMATION_SCHEMA.PROCESSLIST, MySQL populates the entire table once, when statement execution begins, so there is read consistency during the statement. There is no read consistency for a multi-statement transaction, though.

  • Process information is also available from the performance_schema.threads table. However, access to threads does not require a mutex and has minimal impact on server performance. INFORMATION_SCHEMA.PROCESSLIST and SHOW PROCESSLIST have negative performance consequences because they require a mutex. threads also shows information about background threads, which INFORMATION_SCHEMA.PROCESSLIST and SHOW PROCESSLIST do not. This means that threads can be used to monitor activity the other thread information sources cannot.

The following statements are equivalent:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST

SHOW FULL PROCESSLIST

22.18 The INFORMATION_SCHEMA PROFILING Table

The PROFILING table provides statement profiling information. Its contents correspond to the information produced by the SHOW PROFILES and SHOW PROFILE statements (see Section 14.7.5.31, “SHOW PROFILES Syntax”). The table is empty unless the profiling session variable is set to 1.

Note

This table is deprecated as of MySQL 5.7.2 and will be removed in a future MySQL release. Use the Performance Schema instead; see Chapter 23, MySQL Performance Schema.

INFORMATION_SCHEMA NameSHOW NameRemarks
QUERY_IDQuery_ID 
SEQ 
STATEStatus 
DURATIONDuration 
CPU_USERCPU_user 
CPU_SYSTEMCPU_system 
CONTEXT_VOLUNTARYContext_voluntary 
CONTEXT_INVOLUNTARYContext_involuntary 
BLOCK_OPS_INBlock_ops_in 
BLOCK_OPS_OUTBlock_ops_out 
MESSAGES_SENTMessages_sent 
MESSAGES_RECEIVEDMessages_received 
PAGE_FAULTS_MAJORPage_faults_major 
PAGE_FAULTS_MINORPage_faults_minor 
SWAPSSwaps 
SOURCE_FUNCTIONSource_function 
SOURCE_FILESource_file 
SOURCE_LINESource_line 

Notes:

  • QUERY_ID is a numeric statement identifier.

  • SEQ is a sequence number indicating the display order for rows with the same QUERY_ID value.

  • STATE is the profiling state to which the row measurements apply.

  • DURATION indicates how long statement execution remained in the given state, in seconds.

  • CPU_USER and CPU_SYSTEM indicate user and system CPU use, in seconds.

  • CONTEXT_VOLUNTARY and CONTEXT_INVOLUNTARY indicate how many voluntary and involuntary context switches occurred.

  • BLOCK_OPS_IN and BLOCK_OPS_OUT indicate the number of block input and output operations.

  • MESSAGES_SENT and MESSAGES_RECEIVED indicate the number of communication messages sent and received.

  • PAGE_FAULTS_MAJOR and PAGE_FAULTS_MINOR indicate the number of major and minor page faults.

  • SWAPS indicates how many swaps occurred.

  • SOURCE_FUNCTION, SOURCE_FILE, and SOURCE_LINE provide information indicating where in the source code the profiled state executes.

22.19 The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table

The REFERENTIAL_CONSTRAINTS table provides information about foreign keys.

INFORMATION_SCHEMA NameSHOW NameRemarks
CONSTRAINT_CATALOG def
CONSTRAINT_SCHEMA  
CONSTRAINT_NAME  
UNIQUE_CONSTRAINT_CATALOG def
UNIQUE_CONSTRAINT_SCHEMA  
UNIQUE_CONSTRAINT_NAME  
MATCH_OPTION  
UPDATE_RULE  
DELETE_RULE  
TABLE_NAME  
REFERENCED_TABLE_NAME  

Notes:

  • TABLE_NAME has the same value as TABLE_NAME in INFORMATION_SCHEMA.TABLE_CONSTRAINTS.

  • CONSTRAINT_SCHEMA and CONSTRAINT_NAME identify the foreign key.

  • UNIQUE_CONSTRAINT_SCHEMA, UNIQUE_CONSTRAINT_NAME, and REFERENCED_TABLE_NAME identify the referenced key.

  • The only valid value at this time for MATCH_OPTION is NONE.

  • The possible values for UPDATE_RULE or DELETE_RULE are CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION.

22.20 The INFORMATION_SCHEMA ROUTINES Table

The ROUTINES table provides information about stored routines (both procedures and functions). The ROUTINES table does not include user-defined functions (UDFs).

The column named mysql.proc name indicates the mysql.proc table column that corresponds to the INFORMATION_SCHEMA.ROUTINES table column, if any.

INFORMATION_SCHEMA Namemysql.proc NameRemarks
SPECIFIC_NAMEspecific_name 
ROUTINE_CATALOG def
ROUTINE_SCHEMAdb 
ROUTINE_NAMEname 
ROUTINE_TYPEtype{PROCEDURE|FUNCTION}
DATA_TYPE same as for COLUMNS table
CHARACTER_MAXIMUM_LENGTH same as for COLUMNS table
CHARACTER_OCTET_LENGTH same as for COLUMNS table
NUMERIC_PRECISION same as for COLUMNS table
NUMERIC_SCALE same as for COLUMNS table
DATETIME_PRECISION same as for COLUMNS table
CHARACTER_SET_NAME same as for COLUMNS table
COLLATION_NAME same as for COLUMNS table
DTD_IDENTIFIER data type descriptor
ROUTINE_BODY SQL
ROUTINE_DEFINITIONbody_utf8 
EXTERNAL_NAME NULL
EXTERNAL_LANGUAGElanguageNULL
PARAMETER_STYLE SQL
IS_DETERMINISTICis_deterministic 
SQL_DATA_ACCESSsql_data_access 
SQL_PATH NULL
SECURITY_TYPEsecurity_type 
CREATEDcreated 
LAST_ALTEREDmodified 
SQL_MODEsql_modeMySQL extension
ROUTINE_COMMENTcommentMySQL extension
DEFINERdefinerMySQL extension
CHARACTER_SET_CLIENT MySQL extension
COLLATION_CONNECTION MySQL extension
DATABASE_COLLATION MySQL extension

Notes:

  • MySQL calculates EXTERNAL_LANGUAGE thus:

    • If mysql.proc.language='SQL', EXTERNAL_LANGUAGE is NULL

    • Otherwise, EXTERNAL_LANGUAGE is what is in mysql.proc.language. However, we do not have external languages yet, so it is always NULL.

  • CREATED: The date and time when the routine was created. This is a TIMESTAMP value.

  • LAST_ALTERED: The date and time when the routine was last modified. This is a TIMESTAMP value. If the routine has not been modified since its creation, this column holds the same value as the CREATED column.

  • SQL_MODE: The SQL mode in effect when the routine was created or altered, and under which the routine executes. For the permitted values, see Section 6.1.7, “Server SQL Modes”.

  • CHARACTER_SET_CLIENT: The session value of the character_set_client system variable when the routine was created.

  • COLLATION_CONNECTION: The session value of the collation_connection system variable when the routine was created.

  • DATABASE_COLLATION: The collation of the database with which the routine is associated.

  • The DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, DATETIME_PRECISION, CHARACTER_SET_NAME, and COLLATION_NAME columns provide information about the data type for the RETURNS clause of stored functions. If a stored routine is a stored procedure, these columns all are NULL.

  • Information about stored function RETURNS data types is also available in the PARAMETERS table. The return value data type row for a function can be identified as the row that has an ORDINAL_POSITION value of 0.

22.21 The INFORMATION_SCHEMA SCHEMATA Table

A schema is a database, so the SCHEMATA table provides information about databases.

INFORMATION_SCHEMA NameSHOW NameRemarks
CATALOG_NAME def
SCHEMA_NAMEDatabase 
DEFAULT_CHARACTER_SET_NAME  
DEFAULT_COLLATION_NAME  
SQL_PATH NULL

The following statements are equivalent:

SELECT SCHEMA_NAME AS `Database`
  FROM INFORMATION_SCHEMA.SCHEMATA
  [WHERE SCHEMA_NAME LIKE 'wild']

SHOW DATABASES
  [LIKE 'wild']

22.22 The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table

The SCHEMA_PRIVILEGES table provides information about schema (database) privileges. This information comes from the mysql.db grant table.

INFORMATION_SCHEMA NameSHOW NameRemarks
GRANTEE 'user_name'@'host_name' value, MySQL extension
TABLE_CATALOG def, MySQL extension
TABLE_SCHEMA MySQL extension
PRIVILEGE_TYPE MySQL extension
IS_GRANTABLE MySQL extension

Notes:

  • This is a nonstandard table. It takes its values from the mysql.db table.

22.23 The INFORMATION_SCHEMA STATISTICS Table

The STATISTICS table provides information about table indexes.

INFORMATION_SCHEMA NameSHOW NameRemarks
TABLE_CATALOG def
TABLE_SCHEMA = Database
TABLE_NAMETable 
NON_UNIQUENon_unique 
INDEX_SCHEMA = Database
INDEX_NAMEKey_name 
SEQ_IN_INDEXSeq_in_index 
COLUMN_NAMEColumn_name 
COLLATIONCollation 
CARDINALITYCardinality 
SUB_PARTSub_partMySQL extension
PACKEDPackedMySQL extension
NULLABLENullMySQL extension
INDEX_TYPEIndex_typeMySQL extension
COMMENTCommentMySQL extension
INDEX_COMMENTIndex_commentMySQL extension

Notes:

  • There is no standard table for indexes. The preceding list is similar to what SQL Server 2000 returns for sp_statistics, except that we replaced the name QUALIFIER with CATALOG and we replaced the name OWNER with SCHEMA.

    Clearly, the preceding table and the output from SHOW INDEX are derived from the same parent. So the correlation is already close.

The following statements are equivalent:

SELECT * FROM INFORMATION_SCHEMA.STATISTICS
  WHERE table_name = 'tbl_name'
  AND table_schema = 'db_name'

SHOW INDEX
  FROM tbl_name
  FROM db_name

22.24 The INFORMATION_SCHEMA TABLES Table

The TABLES table provides information about tables in databases.

INFORMATION_SCHEMA NameSHOW NameRemarks
TABLE_CATALOG def
TABLE_SCHEMATable_... 
TABLE_NAMETable_... 
TABLE_TYPE  
ENGINEEngineMySQL extension
VERSIONVersionThe version number of the table's .frm file, MySQL extension
ROW_FORMATRow_formatMySQL extension
TABLE_ROWSRowsMySQL extension
AVG_ROW_LENGTHAvg_row_lengthMySQL extension
DATA_LENGTHData_lengthMySQL extension
MAX_DATA_LENGTHMax_data_lengthMySQL extension
INDEX_LENGTHIndex_lengthMySQL extension
DATA_FREEData_freeMySQL extension
AUTO_INCREMENTAuto_incrementMySQL extension
CREATE_TIMECreate_timeMySQL extension
UPDATE_TIMEUpdate_timeMySQL extension
CHECK_TIMECheck_timeMySQL extension
TABLE_COLLATIONCollationMySQL extension
CHECKSUMChecksumMySQL extension
CREATE_OPTIONSCreate_optionsMySQL extension
TABLE_COMMENTCommentMySQL extension

Notes:

  • Refer to SHOW TABLE STATUS for field descriptions.

  • TABLE_SCHEMA and TABLE_NAME are a single field in a SHOW display, for example Table_in_db1.

  • TABLE_TYPE should be BASE TABLE or VIEW. The TABLES table does not list TEMPORARY tables.

  • For partitioned tables, the ENGINE column shows the name of the storage engine used by all partitions. (Previously, this column showed PARTITION for such tables.)

  • The TABLE_ROWS column is NULL if the table is in the INFORMATION_SCHEMA database.

    For InnoDB tables, the row count is only a rough estimate used in SQL optimization. (This is also true if the InnoDB table is partitioned.)

  • The DATA_FREE column shows the free space in bytes for InnoDB tables.

  • Prior to MySQL 5.7.8, for partitioned InnoDB tables, the CREATE_TIME column always showed NULL. This column shows the correct table creation time for such tables in MySQL 5.7.8 and later. (Bug #17299181, Bug #69990)

  • Beginning with MySQL 5.7.2, UPDATE_TIME displays a timestamp value for the last UPDATE, INSERT, or DELETE performed on InnoDB tables that are not partitioned. Previously, UPDATE_TIME displayed a NULL value for InnoDB tables. For MVCC, the timestamp value reflects the COMMIT time, which is considered the last update time. Timestamps are not persisted when the server is restarted or when the table is evicted from the InnoDB data dictionary cache.

    The UPDATE_TIME column also shows this information for partitioned InnoDB tables in MySQL 5.7.8 and later. Previously this column was always NULL for such tables. (Bug #17299181, Bug #69990)

  • For partitioned InnoDB tables, the CHECK_TIME column is always NULL.

  • We have nothing for the table's default character set. TABLE_COLLATION is close, because collation names begin with a character set name.

  • The CREATE_OPTIONS column shows partitioned if the table is partitioned.

The following statements are equivalent:

SELECT table_name FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'db_name'
  [AND table_name LIKE 'wild']

SHOW TABLES
  FROM db_name
  [LIKE 'wild']

22.25 The INFORMATION_SCHEMA TABLESPACES Table

The TABLESPACES table provides information about active tablespaces.

INFORMATION_SCHEMA NameSHOW NameRemarks
TABLESPACE_NAME MySQL extension
ENGINE MySQL extension
TABLESPACE_TYPE MySQL extension
LOGFILE_GROUP_NAME MySQL extension
EXTENT_SIZE MySQL extension
AUTOEXTEND_SIZE MySQL extension
MAXIMUM_SIZE MySQL extension
NODEGROUP_ID MySQL extension
TABLESPACE_COMMENT MySQL extension

Notes:

The INFORMATION_SCHEMA.TABLESPACES table does not provide information about InnoDB tablespaces. For InnoDB tablespace metadata, see INNODB_SYS_TABLESPACES and INNODB_SYS_DATAFILES. As of MySQL 5.7.8, the INFORMATION_SCHEMA.FILES table also provides metadata for InnoDB tablespaces.

22.26 The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table

The TABLE_CONSTRAINTS table describes which tables have constraints.

INFORMATION_SCHEMA NameSHOW NameRemarks
CONSTRAINT_CATALOG def
CONSTRAINT_SCHEMA  
CONSTRAINT_NAME  
TABLE_SCHEMA  
TABLE_NAME  
CONSTRAINT_TYPE  

Notes:

  • The CONSTRAINT_TYPE value can be UNIQUE, PRIMARY KEY, or FOREIGN KEY.

  • The UNIQUE and PRIMARY KEY information is about the same as what you get from the Key_name field in the output from SHOW INDEX when the Non_unique field is 0.

  • The CONSTRAINT_TYPE column can contain one of these values: UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK. This is a CHAR (not ENUM) column. The CHECK value is not available until we support CHECK.

22.27 The INFORMATION_SCHEMA TABLE_PRIVILEGES Table

The TABLE_PRIVILEGES table provides information about table privileges. This information comes from the mysql.tables_priv grant table.

INFORMATION_SCHEMA NameSHOW NameRemarks
GRANTEE 'user_name'@'host_name' value
TABLE_CATALOG def
TABLE_SCHEMA  
TABLE_NAME  
PRIVILEGE_TYPE  
IS_GRANTABLE  

Notes:

The following statements are not equivalent:

SELECT ... FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES

SHOW GRANTS ...

22.28 The INFORMATION_SCHEMA TRIGGERS Table

The TRIGGERS table provides information about triggers. To see information about a table's triggers, you must have the TRIGGER privilege for the table.

INFORMATION_SCHEMA NameSHOW NameRemarks
TRIGGER_CATALOG def
TRIGGER_SCHEMA  
TRIGGER_NAMETrigger 
EVENT_MANIPULATIONEvent 
EVENT_OBJECT_CATALOG def
EVENT_OBJECT_SCHEMA  
EVENT_OBJECT_TABLETable 
ACTION_ORDER  
ACTION_CONDITION NULL
ACTION_STATEMENTStatement 
ACTION_ORIENTATION ROW
ACTION_TIMINGTiming 
ACTION_REFERENCE_OLD_TABLE NULL
ACTION_REFERENCE_NEW_TABLE NULL
ACTION_REFERENCE_OLD_ROW OLD
ACTION_REFERENCE_NEW_ROW NEW
CREATEDCreated 
SQL_MODEsql_modeMySQL extension
DEFINERDefinerMySQL extension
CHARACTER_SET_CLIENTcharacter_set_clientMySQL extension
COLLATION_CONNECTIONcollation_connectionMySQL extension
DATABASE_COLLATIONDatabase CollationMySQL extension

Notes:

  • The names in the SHOW Name column refer to the SHOW TRIGGERS statement, not SHOW CREATE TRIGGER. See Section 14.7.5.38, “SHOW TRIGGERS Syntax”.

  • TRIGGER_SCHEMA and TRIGGER_NAME: The name of the database in which the trigger occurs and the trigger name, respectively.

  • EVENT_MANIPULATION: The trigger event. This is the type of operation on the associated table for which the trigger activates. The value is 'INSERT' (a row was inserted), 'DELETE' (a row was deleted), or 'UPDATE' (a row was modified).

  • EVENT_OBJECT_SCHEMA and EVENT_OBJECT_TABLE: As noted in Section 21.3, “Using Triggers”, every trigger is associated with exactly one table. These columns indicate the database in which this table occurs, and the table name, respectively.

  • ACTION_ORDER: The ordinal position of the trigger's action within the list of triggers on the same table with the same EVENT_MANIPULATION and ACTION_TIMING values. Before MySQL 5.7.2, this value is always 0 because it is not possible for a table to have more than one trigger with the same EVENT_MANIPULATION and ACTION_TIMING values.

  • ACTION_STATEMENT: The trigger body; that is, the statement executed when the trigger activates. This text uses UTF-8 encoding.

  • ACTION_ORIENTATION: Always contains the value 'ROW'.

  • ACTION_TIMING: Whether the trigger activates before or after the triggering event. The value is 'BEFORE' or 'AFTER'.

  • ACTION_REFERENCE_OLD_ROW and ACTION_REFERENCE_NEW_ROW: The old and new column identifiers, respectively. This means that ACTION_REFERENCE_OLD_ROW always contains the value 'OLD' and ACTION_REFERENCE_NEW_ROW always contains the value 'NEW'.

  • CREATED: The date and time when the trigger was created. This is a TIMESTAMP(2) value (with a fractional part in hundredths of seconds) for triggers created in MySQL 5.7.2 or later, NULL for triggers created prior to 5.7.2.

  • SQL_MODE: The SQL mode in effect when the trigger was created, and under which the trigger executes. For the permitted values, see Section 6.1.7, “Server SQL Modes”.

  • DEFINER: The account of the user who created the trigger, in 'user_name'@'host_name' format.

  • CHARACTER_SET_CLIENT: The session value of the character_set_client system variable when the trigger was created.

  • COLLATION_CONNECTION: The session value of the collation_connection system variable when the trigger was created.

  • DATABASE_COLLATION: The collation of the database with which the trigger is associated.

  • The following columns currently always contain NULL: ACTION_CONDITION, ACTION_REFERENCE_OLD_TABLE, and ACTION_REFERENCE_NEW_TABLE.

Example, using the ins_sum trigger defined in Section 21.3, “Using Triggers”:

mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS
    -> WHERE TRIGGER_SCHEMA='test' AND TRIGGER_NAME='ins_sum'\G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: test
              TRIGGER_NAME: ins_sum
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: test
        EVENT_OBJECT_TABLE: account
              ACTION_ORDER: 1
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: SET @sum = @sum + NEW.amount
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: 2013-07-05 07:41:21.26
                  SQL_MODE: NO_ENGINE_SUBSTITUTION
                   DEFINER: me@localhost
      CHARACTER_SET_CLIENT: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: latin1_swedish_ci

22.29 The INFORMATION_SCHEMA USER_PRIVILEGES Table

The USER_PRIVILEGES table provides information about global privileges. This information comes from the mysql.user grant table.

INFORMATION_SCHEMA NameSHOW NameRemarks
GRANTEE 'user_name'@'host_name' value, MySQL extension
TABLE_CATALOG def, MySQL extension
PRIVILEGE_TYPE MySQL extension
IS_GRANTABLE MySQL extension

Notes:

  • This is a nonstandard table. It takes its values from the mysql.user table.

22.30 The INFORMATION_SCHEMA VIEWS Table

The VIEWS table provides information about views in databases. You must have the SHOW VIEW privilege to access this table.

INFORMATION_SCHEMA NameSHOW NameRemarks
TABLE_CATALOG def
TABLE_SCHEMA  
TABLE_NAME  
VIEW_DEFINITION  
CHECK_OPTION  
IS_UPDATABLE  
DEFINER  
SECURITY_TYPE  
CHARACTER_SET_CLIENT MySQL extension
COLLATION_CONNECTION MySQL extension

Notes:

  • The VIEW_DEFINITION column has most of what you see in the Create Table field that SHOW CREATE VIEW produces. Skip the words before SELECT and skip the words WITH CHECK OPTION. Suppose that the original statement was:

    CREATE VIEW v AS
      SELECT s2,s1 FROM t
      WHERE s1 > 5
      ORDER BY s1
      WITH CHECK OPTION;
    

    Then the view definition looks like this:

    SELECT s2,s1 FROM t WHERE s1 > 5 ORDER BY s1
    
  • The CHECK_OPTION column has a value of NONE, CASCADE, or LOCAL.

  • MySQL sets a flag, called the view updatability flag, at CREATE VIEW time. The flag is set to YES (true) if UPDATE and DELETE (and similar operations) are legal for the view. Otherwise, the flag is set to NO (false). The IS_UPDATABLE column in the VIEWS table displays the status of this flag.

    If a view is not updatable, statements such UPDATE, DELETE, and INSERT are illegal and will be rejected. (Note that even if a view is updatable, it might not be possible to insert into it; for details, refer to Section 21.5.3, “Updatable and Insertable Views”.)

    The IS_UPDATABLE flag may be unreliable if a view depends on one or more other views, and one of these underlying views is updated. Regardless of the IS_UPDATABLE value, the server keeps track of the updatability of a view and correctly rejects data change operations to views that are not updatable. If the IS_UPDATABLE value for a view has become inaccurate to due to changes to underlying views, the value can be updated by deleting and recreating the view.

  • DEFINER: The account of the user who created the view, in 'user_name'@'host_name' format. SECURITY_TYPE has a value of DEFINER or INVOKER.

  • CHARACTER_SET_CLIENT: The session value of the character_set_client system variable when the view was created.

  • COLLATION_CONNECTION: The session value of the collation_connection system variable when the view was created.

MySQL lets you use different sql_mode settings to tell the server the type of SQL syntax to support. For example, you might use the ANSI SQL mode to ensure MySQL correctly interprets the standard SQL concatenation operator, the double bar (||), in your queries. If you then create a view that concatenates items, you might worry that changing the sql_mode setting to a value different from ANSI could cause the view to become invalid. But this is not the case. No matter how you write out a view definition, MySQL always stores it the same way, in a canonical form. Here is an example that shows how the server changes a double bar concatenation operator to a CONCAT() function:

mysql> SET sql_mode = 'ANSI';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW test.v AS SELECT 'a' || 'b' as col1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
    -> WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';
+----------------------------------+
| VIEW_DEFINITION                  |
+----------------------------------+
| select concat('a','b') AS `col1` |
+----------------------------------+
1 row in set (0.00 sec)

The advantage of storing a view definition in canonical form is that changes made later to the value of sql_mode will not affect the results from the view. However an additional consequence is that comments prior to SELECT are stripped from the definition by the server.

22.31 INFORMATION_SCHEMA Tables for InnoDB

22.31.1 The INFORMATION_SCHEMA INNODB_CMP and INNODB_CMP_RESET Tables
22.31.2 The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables
22.31.3 The INFORMATION_SCHEMA INNODB_CMPMEM and INNODB_CMPMEM_RESET Tables
22.31.4 The INFORMATION_SCHEMA INNODB_TRX Table
22.31.5 The INFORMATION_SCHEMA INNODB_LOCKS Table
22.31.6 The INFORMATION_SCHEMA INNODB_LOCK_WAITS Table
22.31.7 The INFORMATION_SCHEMA INNODB_SYS_TABLES Table
22.31.8 The INFORMATION_SCHEMA INNODB_SYS_INDEXES Table
22.31.9 The INFORMATION_SCHEMA INNODB_SYS_COLUMNS Table
22.31.10 The INFORMATION_SCHEMA INNODB_SYS_FIELDS Table
22.31.11 The INFORMATION_SCHEMA INNODB_SYS_FOREIGN Table
22.31.12 The INFORMATION_SCHEMA INNODB_SYS_FOREIGN_COLS Table
22.31.13 The INFORMATION_SCHEMA INNODB_SYS_TABLESTATS View
22.31.14 The INFORMATION_SCHEMA INNODB_SYS_DATAFILES Table
22.31.15 The INFORMATION_SCHEMA INNODB_SYS_TABLESPACES Table
22.31.16 The INFORMATION_SCHEMA INNODB_SYS_VIRTUAL Table
22.31.17 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table
22.31.18 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU Table
22.31.19 The INFORMATION_SCHEMA INNODB_BUFFER_POOL_STATS Table
22.31.20 The INFORMATION_SCHEMA INNODB_METRICS Table
22.31.21 The INFORMATION_SCHEMA INNODB_FT_CONFIG Table
22.31.22 The INFORMATION_SCHEMA INNODB_FT_DEFAULT_STOPWORD Table
22.31.23 The INFORMATION_SCHEMA INNODB_FT_INDEX_TABLE Table
22.31.24 The INFORMATION_SCHEMA INNODB_FT_INDEX_CACHE Table
22.31.25 The INFORMATION_SCHEMA INNODB_FT_DELETED Table
22.31.26 The INFORMATION_SCHEMA INNODB_FT_BEING_DELETED Table
22.31.27 The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table

This section provides table definitions for InnoDB INFORMATION_SCHEMA tables. For related information and examples, see Section 15.15, “InnoDB INFORMATION_SCHEMA Tables”.

InnoDB INFORMATION_SCHEMA 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.

22.31.1 The INFORMATION_SCHEMA INNODB_CMP and INNODB_CMP_RESET Tables

The INNODB_CMP and INNODB_CMP_RESET tables contain status information on operations related to compressed InnoDB tables.

Table 22.1 Columns of INNODB_CMP and INNODB_CMP_RESET

Column nameDescription
PAGE_SIZECompressed page size in bytes.
COMPRESS_OPSNumber of times a B-tree page of the 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_OKNumber of times a B-tree page of the size PAGE_SIZE has been successfully compressed. This count should never exceed COMPRESS_OPS.
COMPRESS_TIMETotal time in seconds spent in attempts to compress B-tree pages of the size PAGE_SIZE.
UNCOMPRESS_OPSNumber of times a B-tree page of the 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_TIMETotal time in seconds spent in 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: 0

Notes:

22.31.2 The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables

The INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET tables contain 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.

Table 22.2 Columns of INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET

Column nameDescription
DATABASE_NAMEDatabase containing the applicable table.
TABLE_NAMETable to monitor for compression statistics.
INDEX_NAMEIndex to monitor for compression statistics.
COMPRESS_OPSNumber 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_OKNumber 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_TIMETotal amount of CPU time, in seconds, used for compressing data in this index.
UNCOMPRESS_OPSNumber 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_TIMETotal amount of CPU 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: 0

Notes:

22.31.3 The INFORMATION_SCHEMA INNODB_CMPMEM and INNODB_CMPMEM_RESET Tables

The INNODB_CMPMEM and INNODB_CMPMEM_RESET tables contain status information on compressed pages within the InnoDB buffer pool.

Table 22.3 Columns of INNODB_CMPMEM and INNODB_CMPMEM_RESET

Column nameDescription
PAGE_SIZEBlock size in bytes. Each record of this table describes blocks of this size.
BUFFER_POOL_INSTANCEA unique identifier for the buffer pool instance.
PAGES_USEDNumber of blocks of the size PAGE_SIZE that are currently in use.
PAGES_FREENumber of blocks of the 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_OPSNumber of times a block of the 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 table INNODB_CMPMEM_RESET resets this count.
RELOCATION_TIMETotal time in microseconds spent in relocating blocks of the 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: 0

Notes:

22.31.4 The INFORMATION_SCHEMA INNODB_TRX Table

The INNODB_TRX table contains information about every transaction (excluding read-only transactions) 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.

Table 22.4 INNODB_TRX Columns

Column nameDescription
TRX_IDUnique transaction ID number, internal to InnoDB. (Starting in MySQL 5.6, these IDs are not created for transactions that are read-only and non-locking. See Section 9.5.3, “Optimizing InnoDB Read-Only Transactions” for details.)
TRX_WEIGHTThe 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 rollback. Transactions that have changed non-transactional tables are considered heavier than others, regardless of the number of altered and locked rows.
TRX_STATETransaction execution state. One of RUNNING, LOCK WAIT, ROLLING BACK or COMMITTING.
TRX_STARTEDTransaction start time.
TRX_REQUESTED_LOCK_IDID of the lock the transaction is currently waiting for (if TRX_STATE is LOCK WAIT, otherwise NULL). Details about the lock can be found by joining with INNODB_LOCKS on LOCK_ID.
TRX_WAIT_STARTEDTime when the transaction started waiting on the lock (if TRX_STATE is LOCK WAIT, otherwise NULL).
TRX_MYSQL_THREAD_IDMySQL thread ID. Can be used for joining with PROCESSLIST on ID. See Section 15.15.2.3.1, “Potential Inconsistency with PROCESSLIST Data”.
TRX_QUERYThe SQL query that is being executed by the transaction.
TRX_OPERATION_STATEThe transaction's current operation, or NULL.
TRX_TABLES_IN_USEThe number of InnoDB tables used while processing the current SQL statement of this transaction.
TRX_TABLES_LOCKEDNumber 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_STRUCTSThe number of locks reserved by the transaction.
TRX_LOCK_MEMORY_BYTESTotal size taken up by the lock structures of this transaction in memory.
TRX_ROWS_LOCKEDApproximate 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_MODIFIEDThe number of modified and inserted rows in this transaction.
TRX_CONCURRENCY_TICKETSA value indicating how much work the current transaction can do before being swapped out, as specified by the innodb_concurrency_tickets option.
TRX_ISOLATION_LEVELThe isolation level of the current transaction.
TRX_UNIQUE_CHECKSWhether unique checks are turned on or off for the current transaction. (They might be turned off during a bulk data load, for example.)
TRX_FOREIGN_KEY_CHECKSWhether foreign key checks are turned on or off for the current transaction. (They might be turned off during a bulk data load, for example.)
TRX_LAST_FOREIGN_KEY_ERRORDetailed error message for last FK error, or NULL.
TRX_ADAPTIVE_HASH_LATCHEDWhether or not 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_TIMEOUTWhether to relinquish the search latch immediately for the adaptive hash index, or reserve it across calls from MySQL. When there is no AHI 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. (5.6.4 and up.)

TRX_AUTOCOMMIT_NON_LOCKING

A value of 1 indicates the transaction is a SELECT statement that does not use the FOR UPDATE or LOCK IN SHARED MODE clauses, and is executing with the autocommit setting turned on so that the transaction will only contain this one statement. (5.6.4 and up.) 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: 0
1 row in set (0.00 sec)

Notes:

22.31.5 The INFORMATION_SCHEMA INNODB_LOCKS Table

The INNODB_LOCKS table contains 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 will be removed in a future MySQL release.

Table 22.5 INNODB_LOCKS Columns

Column nameDescription
LOCK_IDUnique 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 not guaranteed to remain the same in future releases. Do not write programs that parse the LOCK_ID value.
LOCK_TRX_IDID of the transaction holding this lock. Details about the transaction can be found by joining with INNODB_TRX on TRX_ID.
LOCK_MODEMode of the lock. One of S[,GAP], X[,GAP], IS[,GAP], IX[,GAP], AUTO_INC, or UNKNOWN. Lock modes other than AUTO_INC and UNKNOWN will indicate GAP locks, if present. Refer to Section 15.5.1, “InnoDB Locking” for information about S, X, IS, and IX lock modes. Refer to Section 15.5.1, “InnoDB Locking” for information about GAP locks.
LOCK_TYPEType of the lock. One of RECORD or TABLE for record (row) level or table level locks, respectively.
LOCK_TABLEName of the table that has been locked or contains locked records.
LOCK_INDEXName of the index if LOCK_TYPE='RECORD', otherwise NULL.
LOCK_SPACETablespace ID of the locked record if LOCK_TYPE='RECORD', otherwise NULL.
LOCK_PAGEPage number of the locked record if LOCK_TYPE='RECORD', otherwise NULL.
LOCK_RECHeap number of the locked record within the page if LOCK_TYPE='RECORD', otherwise NULL.
LOCK_DATAPrimary key value(s) of the locked record if LOCK_TYPE='RECORD', otherwise NULL. This column contains the value(s) of the primary key column(s) in the locked row, formatted as a valid SQL string (ready to be copied to SQL statements). If there is no primary key then the InnoDB internal unique row ID number is used. If a gap lock is taken for key values or ranges above the largest value in the index, LOCK_DATA reports supremum pseudo-record. When the page containing the locked record is not in the buffer pool (in the case that it was paged out to disk while the lock was held), InnoDB does not fetch the page from disk, to avoid unnecessary disk operations. Instead, LOCK_DATA is set to 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, 9

Notes:

22.31.6 The INFORMATION_SCHEMA INNODB_LOCK_WAITS Table

The INNODB_LOCK_WAITS 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 will be removed in a future MySQL release.

Table 22.6 INNODB_LOCK_WAITS Columns

Column nameDescription
REQUESTING_TRX_IDID of the requesting transaction.
REQUESTED_LOCK_IDID of the lock for which a transaction is waiting. Details about the lock can be found by joining with INNODB_LOCKS on LOCK_ID.
BLOCKING_TRX_IDID of the blocking transaction.
BLOCKING_LOCK_IDID of a lock held by a transaction blocking another transaction from proceeding. Details about the lock can be found by joining with INNODB_LOCKS on LOCK_ID.

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:2
1 row in set (0.00 sec)

Notes:

22.31.7 The INFORMATION_SCHEMA INNODB_SYS_TABLES Table

The INNODB_SYS_TABLES 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 Section 15.15.3, “InnoDB INFORMATION_SCHEMA System Tables”.

Table 22.7 INNODB_SYS_TABLES Columns

Column nameDescription
TABLE_IDAn identifier for each InnoDB table that is unique across all databases in the instance.
NAMEThe name of the table. Preceded by the database name where appropriate, for example test/t1. InnoDB system table names are in all uppercase. 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.
FLAGThis value provides bit level information about table format and storage characteristics including row format, compressed page size (if applicable), and whether or not the DATA DIRECTORY clause was used with CREATE TABLE or ALTER TABLE.
N_COLSThe 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 generated virtual columns, if present.
SPACEAn 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 TRUNCATE TABLE statement. For file-per-table tablespaces, this identifier is unique for tables across all databases in the instance.
FILE_FORMATThe table's file format (Antelope or Barracuda).
ROW_FORMATThe table's row format (Compact, Redundant, Dynamic, or Compressed).
ZIP_PAGE_SIZEThe zip page size. Only applies to tables that use the Compressed row format.
SPACE_TYPEThe type of tablespace to which the table belongs. Possible values include System (for the InnoDB system tablespace), General (for InnoDB general tablespaces created using CREATE TABLESPACE, and Single (for InnoDB file-per-table tablespaces). Tables assigned to the system tablespace using the CREATE TABLE or ALTER TABLE TABLESPACE=innodb_system clause have a General SPACE_TYPE. The SPACE_TYPE column was added in MySQL 5.7.6 with the introduction of InnoDB general tablespaces. For more information, see 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: General
1 row in set (0.00 sec)

Notes:

  • Use DESCRIBE or SHOW COLUMNS to view additional information about the columns of this table including data types and default values.

  • You must have the PROCESS privilege to query this table.

Interpreting the INNODB_SYS_TABLES.FLAG Column Value:

The INNODB_SYS_TABLES.FLAG column provides bit-level information about the table's format and storage characteristics. You can interpret the FLAG column value by adding together the applicable decimal numeric values that are provided in the following table.

Table 22.8 Bit Position Values for Interpreting INNODB_SYS_TABLES FLAG Column Data

Bit PositionDescriptionDecimal Numeric Value
0This bit is set if the row format is not REDUNDANT. In other words, it is set if the row format is COMPACT, DYNAMIC or COMPRESSED.
  • 0 - REDUNDANT

  • 1 - COMPACT, DYNAMIC or COMPRESSED

1-4These four bits contain a small number that represents the compressed page size of the table. The INNODB_SYS_TABLES.ZIP_PAGE_SIZE field also reports the compressed page size, if applicable.
  • 0 - Not Compressed

  • 2 - 1024 Byte Compressed Page Size

  • 4 - 2048 Byte Compressed Page Size

  • 6 - 4096 Byte Compressed Page Size

  • 8 - 8192 Byte Compressed Page Size

  • 10 - 16384 Byte Compressed Page Size

5This bit is set if the row format is DYNAMIC or COMPRESSED.
  • 0 - REDUNDANT or COMPACT

  • 32 - DYNAMIC or COMPRESSED

6This bit is set if the DATA DIRECTORY option is used with CREATE TABLE or ALTER TABLE. This bit is set for file-per-table tablespaces that are located in directories other than the default data directory (datadir). For these tables, a tablename.isl file is present in the same location as the tablename.frm file. The tablename.isl file stores the actual directory path to the tablename.ibd file-per-table tablespace file.
  • 0 - Not a remote file-per-table tablespace

  • 64 - A remote file-per-table tablespace

7This bit is set if the table is assigned to a shared tablespace (either a general tablespace or a system tablespace) using the CREATE TABLE or ALTER TABLE TABLESPACE=tablespace_name option.
  • 0 - Table is located in a default location depending on the value of the innodb_file_per_table.

  • 128 - The table is explicitly assigned to a shared tablespace.


In the following, table t1 uses ROW_FORMAT=DYNAMIC and has a FLAG value of 33. Based on the information in the preceding table, we can see that bit position 0 would be set to 1, and bit position 5 would be set to 32 for a table with a DYNAMIC row format. These values add up to a FLAG value of 33.

mysql> use test;
Database changed

mysql> SET GLOBAL innodb_file_format=Barracuda;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (c1 int) ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/t1' \G
*************************** 1. row ***************************
     TABLE_ID: 89
         NAME: test/t1
         FLAG: 33
       N_COLS: 4
        SPACE: 75
  FILE_FORMAT: Barracuda
   ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
1 row in set (0.01 sec)

22.31.8 The INFORMATION_SCHEMA INNODB_SYS_INDEXES Table

The INNODB_SYS_INDEXES 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 Section 15.15.3, “InnoDB INFORMATION_SCHEMA System Tables”.

Table 22.9 INNODB_SYS_INDEXES Columns

Column nameDescription
INDEX_IDAn identifier for each index that is unique across all the databases in an instance.
NAMEThe name of the index. Most indexes created implicitly by InnoDB have consistent names but the index names are not necessarily unique. For example, 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_IDAn identifier representing the table associated with the index; the same value from INNODB_SYS_TABLES.TABLE_ID.
TYPEA numeric identifier signifying the kind of index. 0 = Secondary Index, 1 = Clustered Index, 2 = Unique Index, 3 = Primary Index, 32 = Full-text Index, 64 = Spatial Index, 128 = A secondary index that includes a generated virtual column.
N_FIELDSThe number of columns in the index key. For the GEN_CLUST_INDEX indexes, this value is 0 because the index is created using an artificial value rather than a real table column.
PAGE_NOThe root page number of the index B-tree. For full-text indexes, the PAGE_NO field is unused and set to -1 (FIL_NULL) because the full-text index is laid out in several B-trees (auxiliary tables).
SPACEAn identifier for the tablespace where the index resides. 0 means the InnoDB system tablespace. Any other number represents a table created in file-per-table mode with a separate .ibd file. This identifier stays the same after a TRUNCATE TABLE statement. Because all indexes for a table reside in the same tablespace as the table, this value is not necessarily unique.
MERGE_THRESHOLDThe merge threshold value for index pages. If the amount of data in an index page falls below the 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%. The MERGE_THRESHOLD column was added to INNODB_SYS_INDEXES in MySQL 5.7.6. For more information, see Section 15.6.12, “Configuring the Merge Threshold for Index Pages”.

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: 50

Notes:

  • Use DESCRIBE or SHOW COLUMNS to view additional information about the columns of this table including data types and default values.

  • You must have the PROCESS privilege to query this table.

22.31.9 The INFORMATION_SCHEMA INNODB_SYS_COLUMNS Table

The INNODB_SYS_COLUMNS 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 Section 15.15.3, “InnoDB INFORMATION_SCHEMA System Tables”.

Table 22.10 INNODB_SYS_COLUMNS Columns

Column nameDescription
TABLE_IDAn identifier representing the table associated with the column; the same value from INNODB_SYS_TABLES.TABLE_ID.
NAMEThe name of each column in each table. These names can be uppercase or lowercase depending on the lower_case_table_names setting. There are no special system-reserved names for columns.
POSThe 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 Section 22.31.16, “The INFORMATION_SCHEMA INNODB_SYS_VIRTUAL Table”.
MTYPEStands 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.
PRTYPEThe InnoDB precise type, a binary value with bits representing MySQL data type, character set code, and nullability.
LENThe 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 2*N, 3*N, 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: 10

Notes:

  • Use DESCRIBE or SHOW COLUMNS to view additional information about the columns of this table including data types and default values.

  • You must have the PROCESS privilege to query this table.

22.31.10 The INFORMATION_SCHEMA INNODB_SYS_FIELDS Table

The INNODB_SYS_FIELDS 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 Section 15.15.3, “InnoDB INFORMATION_SCHEMA System Tables”.

Table 22.11 INNODB_SYS_FIELDS Columns

Column nameDescription
INDEX_IDAn identifier for the index associated with this key field, using the same value as in INNODB_SYS_INDEXES.INDEX_ID.
NAMEThe name of the original column from the table, using the same value as in INNODB_SYS_COLUMNS.NAME.
POSThe 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: 0
1 row in set (0.00 sec)

Notes:

  • Use DESCRIBE or SHOW COLUMNS to view additional information about the columns of this table including data types and default values.

  • You must have the PROCESS privilege to query this table.

22.31.11 The INFORMATION_SCHEMA INNODB_SYS_FOREIGN Table

The INNODB_SYS_FOREIGN 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 Section 15.15.3, “InnoDB INFORMATION_SCHEMA System Tables”.

Table 22.12 INNODB_SYS_FOREIGN Columns

Column nameDescription
IDThe name (not a numeric value) of the foreign key index. Preceded by the database name, for example, test/products_fk.
FOR_NAMEThe name of the child table in this foreign key relationship.
REF_NAMEThe name of the parent table in this foreign key relationship.
N_COLSThe number of columns in the foreign key index.
TYPEA collection of bit flags with information about the foreign key column, ORed together. 1 = ON DELETE CASCADE, 2 = ON UPDATE SET NULL, 4 = ON UPDATE CASCADE, 8 = ON UPDATE SET NULL, 16 = ON DELETE NO ACTION, 32 = ON UPDATE NO ACTION.

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: 1
1 row in set (0.00 sec)

Notes:

  • Use DESCRIBE or SHOW COLUMNS to view additional information about the columns of this table including data types and default values.

  • You must have the PROCESS privilege to query this table.

22.31.12 The INFORMATION_SCHEMA INNODB_SYS_FOREIGN_COLS Table

The INNODB_SYS_FOREIGN_COLS 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 Section 15.15.3, “InnoDB INFORMATION_SCHEMA System Tables”.

Table 22.13 INNODB_SYS_FOREIGN_COLS Columns

Column nameDescription
IDThe foreign key index associated with this index key field, using the same value as INNODB_SYS_FOREIGN.ID.
FOR_COL_NAMEThe name of the associated column in the child table.
REF_COL_NAMEThe name of the associated column in the parent table.
POSThe 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: 0
1 row in set (0.00 sec)

Notes:

  • Use DESCRIBE or SHOW COLUMNS to view additional information about the columns of this table including data types and default values.

  • You must have the PROCESS privilege to query this table.

22.31.13 The INFORMATION_SCHEMA INNODB_SYS_TABLESTATS View

The INNODB_SYS_TABLESTATS 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 corresponding to 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 not aged out of the table cache. Tables for which persistent stats are available are always represented in this view.

Table statistics are only updated for DELETE or UPDATE operations that modify indexed columns. Statistics are not updated by operations that only modify non-indexed columns.

For related usage information and examples, see Section 15.15.3, “InnoDB INFORMATION_SCHEMA System Tables”.

Table 22.14 INNODB_SYS_TABLESTATS Columns

Column nameDescription
TABLE_IDAn identifier representing the table for which statistics are available, using the same value as INNODB_SYS_TABLES.TABLE_ID.
NAMEThe name of the table, using the same value as INNODB_SYS_TABLES.NAME.
STATS_INITIALIZEDThe value is Initialized if the statistics are already collected, Uninitialized if not.
NUM_ROWSThe 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_SIZENumber 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_SIZENumber 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_COUNTERThe 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
AUTOINCThe 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_COUNTWhen 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: 1
1 row in set (0.00 sec)

Notes:

  • This table is primarily useful for expert-level performance monitoring, or when developing performance-related extensions for MySQL.

  • Use DESCRIBE or SHOW COLUMNS to view additional information about the columns of this table including data types and default values.

  • You must have the PROCESS privilege to query this table.

22.31.14 The INFORMATION_SCHEMA INNODB_SYS_DATAFILES Table

The INNODB_SYS_DATAFILES 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 Section 15.15.3, “InnoDB INFORMATION_SCHEMA System Tables”.

Note

As of MySQL 5.7.8, the INFORMATION_SCHEMA.FILES table provides data file path information and other metadata about all InnoDB tablespace types including file-per-table tablespaces, general tablespaces, the system tablespace, temporary tablespaces, and undo tablespaces (if present).

Table 22.15 INNODB_SYS_DATAFILES Columns

Column nameDescription
SPACEThe tablespace Space ID.
PATHThe tablespace data file path (for example, .\world\innodb\city.ibd). If a file-per-table tablespace is created in a location outside the MySQL data directory using the DATA DIRECTORY clause of the CREATE TABLE statement, the tablespace PATH field shows the fully qualified directory path.

Example:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES WHERE SPACE = 57 \G 
*************************** 1. row ***************************
SPACE: 57
 PATH: ./test/t1.ibd
1 row in set (0.01 sec)

Notes:

  • Use DESCRIBE or SHOW COLUMNS to view additional information about the columns of this table including data types and default values.

  • You must have the PROCESS privilege to query this table.

22.31.15 The INFORMATION_SCHEMA INNODB_SYS_TABLESPACES Table

The INNODB_SYS_TABLESPACES 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 Section 15.15.3, “InnoDB INFORMATION_SCHEMA System Tables”.

Note

As of MySQL 5.7.8, the INFORMATION_SCHEMA.FILES table provides metadata about all InnoDB tablespace types including file-per-table tablespaces, general tablespaces, the system tablespace, temporary tablespaces, and undo tablespaces (if present).

Table 22.16 INNODB_SYS_TABLESPACES Columns

Column nameDescription
SPACETablespace Space ID.
NAMEThe database and table name (for example, world_innodb\city)
FLAGThis value provides bit level information about tablespace format and storage characteristics.
FILE_FORMATThe 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 Section 15.10, “InnoDB File-Format Management”.
ROW_FORMATThe tablespace row format (Compact or Redundant, Dynamic, or Compressed). The data in this field is interpreted from the tablespace flags information that resides in the .ibd file.
PAGE_SIZEThe tablespace page size. The data in this field is interpreted from the tablespace flags information that resides in the .ibd file.
ZIP_PAGE_SIZEThe tablespace zip page size. The data in this field is interpreted from the tablespace flags information that resides in the .ibd file.
SPACE_TYPEThe type of tablespace. Possible values include General (for InnoDB general tablespaces created using CREATE TABLESPACE and Single (for InnoDB file-per-table tablespaces). The SPACE_TYPE column was added in MySQL 5.7.6 with the introduction of InnoDB general tablespaces. For more information, see CREATE TABLESPACE.
FS_BLOCK_SIZEThe file system block size, which is the unit size used for hole punching. This column was added in MySQL 5.7.8 with the introduction of the InnoDB transparent page compression feature.
FILE_SIZEThe apparent size of the file, which represents the maximum size of the file, uncompressed. This column was added in MySQL 5.7.8 with the introduction of the InnoDB transparent page compression feature.
ALLOCATED_SIZEThe actual size of the file, which is the amount of space allocated on disk. This column was added in MySQL 5.7.8 with the introduction of the InnoDB transparent page compression feature.
COMPRESSIONThe current tablespace setting for page compression (Zlib, Lz4, or None). A table may contain a mix of pages with different compression settings. This column was added in MySQL 5.7.8 with the introduction of the InnoDB transparent page compression feature. This column displays incorrect data after a server restart (Bug #78197) and is removed in 5.7.10. Use SHOW CREATE TABLE to view the current page compression setting.

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: 65536
   COMPRESSION: LZ4
1 row in set (0.00 sec)

Notes:

  • Use DESCRIBE or SHOW COLUMNS to view additional information about the columns of this table including data types and default values.

  • You must have the PROCESS privilege to query this table.

  • 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 in MySQl 5.7.6, InnoDB system tablespace data (for SPACE 0) is exposed in INNODB_SYS_TABLESPACES.

Interpreting the INNODB_SYS_TABLESPACES.FLAG Column Value:

The INNODB_SYS_TABLESPACES.FLAG column provides bit-level information about tablespace format and storage characteristics.

Until MySQL 5.6, table and tablespace flags were the same except for the bit position 0 settings. In MySQL 5.6, support was added for 4K and 8K pages, which required an additional 4 bits to hold the logical page size. Also in MySQL 5.6, support was added for the CREATE TABLE and ALTER TABLE DATA DIRECTORY clause, which allows file-per-table tablespaces to be stored in a location outside of the MySQL data directory. This feature required an additional bit for both table and tablespace flags, but not at the same position.

You can interpret the tablespace FLAG column value by adding together the applicable decimal numeric values that are provided in the following table.

Table 22.17 Bit Position Values for Interpreting INNODB_SYS_TABLESPACES FLAG Column Data

Bit PositionDescriptionDecimal Numeric Value
0This bit is set if the row format of tables in the tablespace is DYNAMIC or COMPRESSED. This information can help you distinguish between Antelope and Barracuda file formats but not between REDUNDANT and COMPACT file formats (DYNAMIC and COMPRESSED row formats require the Barracuda file format). If it is a file-per-table tablespace, you must query INNODB_SYS_TABLES to determine which of the two Antelope row formats is used (REDUNDANT or COMPACT).
  • 0 - REDUNDANT or COMPACT (FILE_FORMAT=Antelope)

  • 1 - DYNAMIC or COMPRESSED (FILE_FORMAT=Barracuda)

1-4These four bits contain a small number that represents the compressed page size (the KEY_BLOCK_SIZE or physical block size) of the tablespace.
  • 0 - Not Compressed

  • 2 - 1024 Byte Compressed Page Size

  • 4 - 2048 Byte Compressed Page Size

  • 6 - 4096 Byte Compressed Page Size

  • 8 - 8192 Byte Compressed Page Size

  • 10 - 16384 Byte Compressed Page Size

  • 12 - 32768 Byte Compressed Page Size

  • 14 - 65536 Byte Compressed Page Size

5This bit is set for file-per-table tablespaces if the row format of the table is DYNAMIC or COMPRESSED. General tablespaces that do not contain compressed tables will have the first 6 bits set to zero, including this bit, making it appear to be the Antelope file format. But actually, general tablespaces may contain any combination of REDUNDANT, COMPACT and DYNAMIC tables. For more information about general tablespaces, see CREATE TABLESPACE.
  • 0 - REDUNDANT or COMPACT

  • 32 - DYNAMIC or COMPRESSED

6-9These four bits contain a small number that represents the uncompressed page size (logical page size) of the tablespace. The setting is zero if the logical page size is the original InnoDB default page size of 16K.
  • 192 - 4096 Byte Logical/Uncompressed Page Size

  • 256 - 8192 Byte Logical/Uncompressed Page Size

  • 0 - 16384 Byte Logical/Uncompressed Page size

  • 384 - 32768 Byte Logical/Uncompressed Page Size

  • 448 - 65536 Byte Logical/Uncompressed Page Size

10This bit is set if the DATA DIRECTORY option is used with CREATE TABLE or ALTER TABLE. This bit is set for file-per-table tablespaces that are located in directories other than the default data directory (datadir). For these tables, a tablename.isl file is present in the same location as the tablename.frm file. The tablename.isl file stores the actual directory path to the tablename.ibd file-per-table tablespace file.
  • 0 - Not a remote file-per-table tablespace

  • 1024 - A remote file-per-table tablespace

11This bit is set if the tablespace is a shared general tablespace created using CREATE TABLESPACE.
  • 0 - Table is located in a default location depending on the value of the innodb_file_per_table setting.

  • 2048 - The table was explicitly assigned to a shared tablespace.

12This bit is set if the tablespace is dedicated to temporary tables. In MySQL 5.7, only the predefined ibtmp1 tablespace uses this flag.
  • 0 - The tablespace does not contain temporary tables, so it is not recreated upon startup.

  • 4096 - The tablespace contains temporary tables and is recreated on startup.


In the following example, table t1 is created with innodb_file_per_table=ON, which creates table t1 in its own tablespace. When querying INNODB_SYS_TABLESPACES, we see that the tablespace has a FLAG value of 33. To determine how this value is arrived at, review the bit values described in the preceding table. Bit 0 has a value of 1 because table t1 uses the DYNAMIC row format. Bit 5 has a value of 32 because the tablespace is a file-per-table tablespace that uses a DYNAMIC row format. Bit position 6-9 is 0 because innodb_page_size is set to the default 16K value. The other bit values are not applicable and are therefore set to 0. The values for bit position 0 and bit position 5 add up to a FLAG value of 33.

mysql> use test;
Database changed

mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL innodb_file_format=Barracuda;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (c1 int) ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE NAME LIKE 'test/t1' \G
*************************** 1. row ***************************
        SPACE: 75
         NAME: test/t1
         FLAG: 33
  FILE_FORMAT: Barracuda
   ROW_FORMAT: Dynamic
    PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
1 row in set (0.00 sec)

22.31.16 The INFORMATION_SCHEMA INNODB_SYS_VIRTUAL Table

The INNODB_SYS_VIRTUAL table provides metadata about InnoDB generated virtual columns and columns upon which generated virtual 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 generated virtual column is based.

Table 22.18 INNODB_SYS_VIRTUAL Columns

Column nameDescription
TABLE_IDAn identifier representing the table associated with the virtual column; the same value as INNODB_SYS_TABLES.TABLE_ID.
POSThe position value of the generated virtual 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. The formula is ((nth virtual generated column for the InnoDB instance + 1) << 16) + the ordinal position of the generated virtual column. For 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 generated virtual column in the InnoDB instance is always number 0. As the third column in the table, the ordinal position of the generated virtual column is 2. Ordinal positions are counted from 0.
BASE_POSThe ordinal position of the columns upon which a generated virtual 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=latin1;

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_VIRTUAL 
    -> WHERE TABLE_ID IN (SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE "test/t1");
+----------+-------+----------+
| TABLE_ID | POS   | BASE_POS |
+----------+-------+----------+
|       45 | 65538 |        0 |
|       45 | 65538 |        1 |
+----------+-------+----------+

Notes:

  • If a constant value is assigned to a generated virtual column, as in the following example, an entry for the column does not appear in the INNODB_SYS_VIRTUAL table. For an entry to appear, a generated virtual column must have a base column.

    mysql> 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=latin1;

    However, metadata for such a column appears in the INNODB_SYS_COLUMNS table.

  • Use DESCRIBE or SHOW COLUMNS to view additional information about the columns of this table including data types and default values.

  • You must have the PROCESS privilege to query this table.

22.31.17 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table

The INNODB_BUFFER_PAGE table holds information about each page in the InnoDB buffer pool.

For related usage information and examples, see Section 15.15.5, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables”.

Warning

Querying the INNODB_BUFFER_PAGE table can introduce significant performance overhead. Do not query this table on a production system unless you are aware of the performance impact that your query may have and have determined it to be acceptable. To avoid impacting performance, reproduce the issue you want to investigate on a test instance and query the INNODB_BUFFER_PAGE table on the test instance.

Table 22.19 INNODB_BUFFER_PAGE Columns

Column nameDescription
POOL_IDBuffer Pool ID. An identifier to distinguish between multiple buffer pool instances.
BLOCK_IDBuffer Pool Block ID.
SPACETablespace ID. Uses the same value as in INNODB_SYS_TABLES.SPACE.
PAGE_NUMBERPage number.
PAGE_TYPEPage type. One of ALLOCATED (Freshly allocated page), INDEX (B-tree node), UNDO_LOG (Undo log page), INODE (Index node), IBUF_FREE_LIST (Insert buffer free list), IBUF_BITMAP (Insert buffer bitmap), SYSTEM (System page), TRX_SYSTEM (Transaction system data), FILE_SPACE_HEADER (File space header), EXTENT_DESCRIPTOR (Extent descriptor page), BLOB (Uncompressed BLOB page), COMPRESSED_BLOB (First compressed BLOB page), COMPRESSED_BLOB2 (Subsequent comp BLOB page), IBUF_INDEX (Insert buffer index), RTREE_INDEX (spatial index), UNKNOWN (unknown).
FLUSH_TYPEFlush type.
FIX_COUNTNumber of threads using this block within the buffer pool. When zero, the block is eligible to be evicted.
IS_HASHEDWhether hash index has been built on this page.
NEWEST_MODIFICATIONLog Sequence Number of the youngest modification.
OLDEST_MODIFICATIONLog Sequence Number of the oldest modification.
ACCESS_TIMEAn abstract number used to judge the first access time of the page.
TABLE_NAMEName of the table the page belongs to. This column is only applicable to pages of type INDEX.
INDEX_NAMEName of the index the page belongs to. It can be the name of a clustered index or a secondary index. This column is only applicable to pages of type INDEX.
NUMBER_RECORDSNumber of records within the page.
DATA_SIZESum of the sizes of the records. This column is only applicable to pages of type INDEX.
COMPRESSED_SIZECompressed page size. Null for pages that are not compressed.
PAGE_STATEPage state. A page with valid data has one of the following states: FILE_PAGE (buffers a page of data from a file), MEMORY (buffers a page from an in-memory object), COMPRESSED. Other possible states (managed by InnoDB) are: NULL, READY_FOR_USE, NOT_USED, REMOVE_HASH.
IO_FIXSpecifies whether any I/O is pending for this page: IO_NONE = no pending I/O, IO_READ = read pending, IO_WRITE = write pending.
IS_OLDSpecifies whether or not the block is in the sublist of old blocks in the LRU list.
FREE_PAGE_CLOCKThe 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: 66
1 row in set (0.03 sec)

Notes:

  • This table is primarily useful for expert-level performance monitoring, or when developing performance-related extensions for MySQL.

  • Use DESCRIBE or SHOW COLUMNS to view additional information about the columns of this table including data types and default values.

  • You must have the PROCESS privilege to query this table.

  • When tables, table rows, partitions, or indexes are deleted, associated pages remain in the buffer pool until space is required for other data. The INNODB_BUFFER_PAGE 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 Section 15.6.3.1, “The InnoDB Buffer Pool”.

22.31.18 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU Table

The INNODB_BUFFER_PAGE_LRU table holds 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 INNODB_BUFFER_PAGE_LRU table has the same columns as the INNODB_BUFFER_PAGE table, except that the INNODB_BUFFER_PAGE_LRU table has an LRU_POSITION column instead of a BLOCK_ID column.

For related usage information and examples, see Section 15.15.5, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables”.

Warning

Querying the INNODB_BUFFER_PAGE_LRU table can introduce significant performance overhead. Do not query this table on a production system unless you are aware of the performance impact that your query may have, and have determined it to be acceptable. To avoid impacting performance, reproduce the issue you want to investigate on a test instance and query the INNODB_BUFFER_PAGE_LRU table on the test instance.

Table 22.20 INNODB_BUFFER_PAGE_LRU Columns

Column nameDescription
POOL_IDBuffer Pool ID. An identifier to distinguish between multiple buffer pool instances.
LRU_POSITIONThe position of the page in the LRU list.
SPACETablespace ID. Uses the same value as in INNODB_SYS_TABLES.SPACE.
PAGE_NUMBERPage number.
PAGE_TYPEPage type. One of ALLOCATED (Freshly allocated page), INDEX (B-tree node), UNDO_LOG (Undo log page), INODE (Index node), IBUF_FREE_LIST (Insert buffer free list), IBUF_BITMAP (Insert buffer bitmap), SYSTEM (System page), TRX_SYSTEM (Transaction system data), FILE_SPACE_HEADER (File space header), EXTENT_DESCRIPTOR (Extent descriptor page), BLOB (Uncompressed BLOB page), COMPRESSED_BLOB (First compressed BLOB page), COMPRESSED_BLOB2 (Subsequent comp BLOB page), IBUF_INDEX (Insert buffer index), RTREE_INDEX (spatial index), UNKNOWN (unknown).
FLUSH_TYPEFlush type.
FIX_COUNTNumber of threads using this block within the buffer pool. When zero, the block is eligible to be evicted.
IS_HASHEDWhether hash index has been built on this page.
NEWEST_MODIFICATIONLog Sequence Number of the youngest modification.
OLDEST_MODIFICATIONLog Sequence Number of the oldest modification.
ACCESS_TIMEAn abstract number used to judge the first access time of the page.
TABLE_NAMEName of the table the page belongs to. This column is only applicable to pages of type INDEX.
INDEX_NAMEName of the index the page belongs to. It can be the name of a clustered index or a secondary index. This column is only applicable to pages of type INDEX.
NUMBER_RECORDSNumber of records within the page.
DATA_SIZESum of the sizes of the records. This column is only applicable to pages of type INDEX.
COMPRESSED_SIZECompressed page size. Null for pages that are not compressed.
PAGE_STATEPage state. A page with valid data has one of the following states: FILE_PAGE (buffers a page of data from a file), MEMORY (buffers a page from an in-memory object), COMPRESSED. Other possible states (managed by InnoDB) are: NULL, READY_FOR_USE, NOT_USED, REMOVE_HASH.
IO_FIXSpecifies whether any I/O is pending for this page: IO_NONE = no pending I/O, IO_READ = read pending, IO_WRITE = write pending.
IS_OLDSpecifies whether or not the block is in the sublist of old blocks in the LRU list.
FREE_PAGE_CLOCKThe 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: 0
1 row in set (0.02 sec)

Notes

  • This table is primarily useful for expert-level performance monitoring, or when developing performance-related extensions for MySQL.

  • You must have the PROCESS privilege to query this table.

  • Use DESCRIBE or SHOW COLUMNS 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 time 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 INNODB_BUFFER_PAGE_LRU 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 Section 15.6.3.1, “The InnoDB Buffer Pool”.

22.31.19 The INFORMATION_SCHEMA INNODB_BUFFER_POOL_STATS Table

The INNODB_BUFFER_POOL_STATS table provides much of the same buffer pool information provided in SHOW ENGINE INNODB STATUS output. Much of the same information may also be obtained using InnoDB buffer pool 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 Section 15.15.5, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables”.

Table 22.21 INNODB_BUFFER_POOL_STATS Columns

Column nameDescription
POOL_IDBuffer Pool ID. A unique identifier to distinguish between multiple buffer pool instances.
POOL_SIZEThe InnoDB buffer pool size in pages.
FREE_BUFFERSThe number of free pages in the InnoDB buffer pool
DATABASE_PAGESThe number of pages in the InnoDB buffer pool containing data. The number includes both dirty and clean pages.
OLD_DATABASE_PAGESThe number of pages in the old buffer pool sublist.
MODIFIED_DATABASE_PAGESThe number of modified (dirty) database pages
PENDING_DECOMPRESSThe number of pages pending decompression
PENDING_READSThe number of pending reads
PENDING_FLUSH_LRUThe number of pages pending flush in the LRU
PENDING_FLUSH_LISTThe number of pages pending flush in the flush list
PAGES_MADE_YOUNGThe number of pages made young
PAGES_NOT_MADE_YOUNGThe number of pages not made young
PAGES_MADE_YOUNG_RATEThe number of pages made young per second (pages made young since the last printout / time elapsed)
PAGES_MADE_NOT_YOUNG_RATEThe number of pages not made per second (pages not made young since the last printout / time elapsed)
NUMBER_PAGES_READThe number of pages read
NUMBER_PAGES_CREATEDThe number of pages created
NUMBER_PAGES_WRITTENThe number of pages written
PAGES_READ_RATEThe number of pages read per second (pages read since the last printout / time elapsed)
PAGES_CREATE_RATEThe number of pages created per second (pages created since the last printout / time elapsed)
PAGES_WRITTEN_RATEThe number of pages written per second (pages written since the last printout / time elapsed)
NUMBER_PAGES_GETThe number of logical read requests.
HIT_RATEThe buffer pool hit rate
YOUNG_MAKE_PER_THOUSAND_GETSThe number of pages made young per thousand gets
NOT_YOUNG_MAKE_PER_THOUSAND_GETSThe number of pages not made young per thousand gets
NUMBER_PAGES_READ_AHEADThe number of pages read ahead
NUMBER_READ_AHEAD_EVICTEDThe 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_RATEThe read ahead rate per second (pages read ahead since the last printout / time elapsed)
READ_AHEAD_EVICTED_RATEThe number of read ahead pages evicted without access per second (read ahead pages not accessed since the last printout / time elapsed)
LRU_IO_TOTALLRU IO total
LRU_IO_CURRENTLRU IO for the current interval
UNCOMPRESS_TOTALTotal number of pages decompressed
UNCOMPRESS_CURRENTThe 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: 0
1 row in set (0.00 sec)

Notes:

  • This table is primarily useful for expert-level performance monitoring, or when developing performance-related extensions for MySQL.

  • Use DESCRIBE or SHOW COLUMNS to view additional information about the columns of this table including data types and default values.

  • You must have the PROCESS privilege to query this table.

22.31.20 The INFORMATION_SCHEMA INNODB_METRICS Table

This INFORMATION_SCHEMA table presents 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, you set one of the configuration options 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 Section 15.15.6, “InnoDB INFORMATION_SCHEMA Metrics Table”.

Table 22.22 INNODB_METRICS Columns

Column nameDescription
NAMEUnique name for the counter.
SUBSYSTEMThe aspect of InnoDB that the metric applies to. See the list following the table for the corresponding module names to use with the SET GLOBAL syntax.
COUNTValue since the counter is enabled.
MAX_COUNTMaximum value since the counter is enabled.
MIN_COUNTMinimum value since the counter is enabled.
AVG_COUNTAverage value since the counter is enabled.
COUNT_RESETCounter value since it was last reset. (The _RESET fields 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 the COUNT, MAX_COUNT, and so on fields.)
MAX_COUNT_RESETMaximum counter value since it was last reset.
MIN_COUNT_RESETMinimum counter value since it was last reset.
AVG_COUNT_RESETAverage counter value since it was last reset.
TIME_ENABLEDTimestamp of last start.
TIME_DISABLEDTimestamp of last stop.
TIME_ELAPSEDElapsed time in seconds since the counter started.
TIME_RESETTimestamp of last stop.
STATUSWhether the counter is still running (enabled) or stopped (disabled).
TYPEWhether the item is a cumulative counter, or measures the current value of some resource.
COMMENTCounter 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 inserted
1 row in set (0.00 sec)

Notes:

  • You must have the PROCESS privilege to query this table.

  • Use DESCRIBE or SHOW COLUMNS to view additional information about the columns of this table including data types and default values.

22.31.21 The INFORMATION_SCHEMA INNODB_FT_CONFIG Table

The INNODB_FT_CONFIG table displays metadata about the FULLTEXT index and associated processing for an InnoDB table.

Before you query this table, set the configuration variable innodb_ft_aux_table 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 Section 15.15.4, “InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables”.

Table 22.23 INNODB_FT_CONFIG Columns

Column nameDescription
KEYThe name designating an item of metadata for an InnoDB table containing a FULLTEXT index.
VALUEThe 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 only intended for internal configuration. It is not intended for statistical information purposes.

  • Use DESCRIBE or SHOW COLUMNS to view additional information about the columns of this table including data types and default values.

  • You must have the PROCESS privilege to query this table.

  • The values for the KEY column might evolve depending on the needs for performance tuning and debugging for InnoDB full-text processing. The key values include:

    • optimize_checkpoint_limit: The number of seconds after which an OPTIMIZE TABLE run will stop.

    • synced_doc_id: The next DOC_ID to be issued.

    • stopword_table_name: The database/table name for a user defined stopword table. This field appears empty if there is no user-defined stopword table.

    • use_stopword: Indicates whether or not a stopword table is used, which is defined when the FULLTEXT index is created.

  • For more information about InnoDB FULLTEXT search, see Section 15.8.10, “InnoDB FULLTEXT Indexes”, and Section 13.9, “Full-Text Search Functions”.

22.31.22 The INFORMATION_SCHEMA INNODB_FT_DEFAULT_STOPWORD Table

The INNODB_FT_DEFAULT_STOPWORD table holds a list of stopwords that are used by default when creating a FULLTEXT index on an InnoDB table. For information about the default InnoDB stopword list and how to define your own stopword lists, see Section 13.9.4, “Full-Text Stopwords”.

For related usage information and examples, see Section 15.15.4, “InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables”.

Table 22.24 INNODB_FT_DEFAULT_STOPWORD Columns

Column nameDescription
valueA word that is used by default as a stopword for FULLTEXT indexes on InnoDB tables. Not used if you override the default stopword processing with either the innodb_ft_server_stopword_table or the innodb_ft_user_stopword_table option.

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:

22.31.23 The INFORMATION_SCHEMA INNODB_FT_INDEX_TABLE Table

The INNODB_FT_INDEX_TABLE table displays information about the inverted index used to process text searches against the FULLTEXT index of an InnoDB table.

For related usage information and examples, see Section 15.15.4, “InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables”.

Before you query this table, set the configuration variable innodb_ft_aux_table to the name (including the database name) of the table that contains the FULLTEXT index, for example test/articles.

Table 22.25 INNODB_FT_INDEX_TABLE Columns

Column nameDescription
WORDA word extracted from the text of the columns that are part of a FULLTEXT.
FIRST_DOC_IDThe first document ID that this word appears in the FULLTEXT index.
LAST_DOC_IDThe last document ID that this word appears in the FULLTEXT index.
DOC_COUNTThe number of rows 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_IDThe 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 does not contain a suitable column.
POSITIONThe position of this particular instance of the word within the relevant document identified by the DOC_ID value.

Notes:

  • This table initially appears empty, until you set the value of the configuration variable innodb_ft_aux_table. The following example demonstrates how to use the innodb_ft_aux_table option 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 OPTIMIZE TABLE operation on the indexed table with innodb_optimize_fulltext_only=ON.

    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 will 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;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> OPTIMIZE TABLE articles;
    +---------------+----------+----------+----------+
    | Table         | Op       | Msg_type | Msg_text |
    +---------------+----------+----------+----------+
    | test.articles | optimize | status   | OK       |
    +---------------+----------+----------+----------+
    1 row in set (0.00 sec)
    
    mysql> SET GLOBAL innodb_ft_aux_table = 'test/articles';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> USE INFORMATION_SCHEMA;
    
    mysql> SELECT word, doc_count, doc_id, position FROM 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 |
    +------------+-----------+--------+----------+
  • Use DESCRIBE or SHOW COLUMNS to view additional information about the columns of this table including data types and default values.

  • You must have the PROCESS privilege to query this table.

  • For more information about InnoDB FULLTEXT search, see Section 15.8.10, “InnoDB FULLTEXT Indexes”, and Section 13.9, “Full-Text Search Functions”.

22.31.24 The INFORMATION_SCHEMA INNODB_FT_INDEX_CACHE Table

INNODB_FT_INDEX_CACHE: Contains 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 OPTIMIZE TABLE is run, when the server is shut down, or when the cache size exceeds a limit defined by innodb_ft_cache_size or innodb_ft_total_cache_size.

Before you query this table, set the configuration variable innodb_ft_aux_table 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 Section 15.15.4, “InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables”.

Table 22.26 INNODB_FT_INDEX_CACHE Columns

Column nameDescription
WORDA word extracted from the text of a newly inserted row.
FIRST_DOC_IDThe first document ID that this word appears in the FULLTEXT index.
LAST_DOC_IDThe last document ID that this word appears in the FULLTEXT index.
DOC_COUNTThe number of rows 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_IDThe 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 does not contain a suitable column.
POSITIONThe 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 initially appears empty, until you set the value of the configuration variable innodb_ft_aux_table. The following example demonstrates how to use the innodb_ft_aux_table option 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 will 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';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> USE INFORMATION_SCHEMA;
    
    mysql> SELECT word, doc_count, doc_id, position FROM 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 |
    +------------+-----------+--------+----------+
            
  • Use DESCRIBE or SHOW COLUMNS to view additional information about the columns of this table including data types and default values.

  • You must have the PROCESS privilege to query this table.

  • For more information about InnoDB FULLTEXT search, see Section 15.8.10, “InnoDB FULLTEXT Indexes”, and Section 13.9, “Full-Text Search Functions”.

22.31.25 The INFORMATION_SCHEMA INNODB_FT_DELETED Table

The INNODB_FT_DELETED table records 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 the OPTIMIZE TABLE statement for the InnoDB table. See Optimizing InnoDB Full-Text Indexes for more information.

This table initially appears empty, until you set the value of the configuration variable innodb_ft_aux_table 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 Section 15.15.4, “InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables”.

Table 22.27 INNODB_FT_DELETED Columns

Column nameDescription
DOC_IDThe 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 does not contain a suitable column. This value is used to skip rows in the innodb_ft_index_table table, when you do text searches before data for deleted rows is physically removed from the FULLTEXT index by an OPTIMIZE TABLE statement. See Optimizing InnoDB Full-Text Indexes for more information.

Example:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
+--------+
| DOC_ID |
+--------+
|      6 |
|      7 |
|      8 |
+--------+

Notes:

22.31.26 The INFORMATION_SCHEMA INNODB_FT_BEING_DELETED Table

The INNODB_FT_BEING_DELETED table is a snapshot of the INNODB_FT_DELETED table that is only used during an OPTIMIZE TABLE maintenance operation. When OPTIMIZE TABLE is run, the INNODB_FT_BEING_DELETED table is emptied, and DOC_IDs are removed from the INNODB_FT_DELETED table. Because the contents of INNODB_FT_BEING_DELETED typically have a short lifetime, this table has limited utility for monitoring or debugging. For information about running OPTIMIZE TABLE on tables with FULLTEXT indexes, see Section 13.9.6, “Fine-Tuning MySQL Full-Text Search”.

This table initially appears empty, until you set the value of the configuration variable innodb_ft_aux_table. The output appears similar to the example provided for the INNODB_FT_DELETED table.

For related usage information and examples, see Section 15.15.4, “InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables”.

Table 22.28 INNODB_FT_BEING_DELETED Columns

Column nameDescription
DOC_IDThe 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 does not contain a suitable column. This value is used to skip rows in the innodb_ft_index_table table, when you do text searches before data for deleted rows is physically removed from the FULLTEXT index by an OPTIMIZE TABLE statement. See Optimizing InnoDB Full-Text Indexes for more information.

Notes:

22.31.27 The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table

INNODB_TEMP_TABLE_INFO contains metadata about active InnoDB temporary tables. With the exception of optimized internal temporary tables used by InnoDB, INNODB_TEMP_TABLE_INFO reports on all user and system-created temporary tables that are active within a given InnoDB instance. The table is maintained in memory and not persisted to disk.

Prior to the introduction of the INNODB_TEMP_TABLE_INFO table in MySQL 5.7.1, InnoDB temporary table metadata was stored in InnoDB system tables.

For usage information and examples, see Section 15.15.7, “InnoDB INFORMATION_SCHEMA Temporary Table Information Table”.

Table 22.29 INNODB_TEMP_TABLE_INFO Columns

Column nameDescription
TABLE_IDThe table ID of the active temporary table.
NAMEThe name of the active temporary table.
N_COLSThe number of columns in the temporary table. The number always includes three hidden columns created by InnoDB (DB_ROW_ID, DB_TRX_ID, and DB_ROLL_PTR).
SPACEThe tablespace identifier (a numerical value) for the tablespace in which the temporary table resides. As of MySQL 5.7.1, all non-compressed InnoDB temporary tables reside in a shared temporary tablespace, as defined by innodb_temp_data_file_path. By default the shared temporary tablespace is named ibtmp1 and located in the data directory. Compressed temporary tables reside in separate per-table tablespaces located in the temporary file directory, as defined by tmpdir. The SPACE ID is always a non-zero value and is dynamically generated on server restart.
PER_TABLE_SPACEA value of TRUE indicates that the temporary table resides in a separate per-table tablespace. A value of FALSE indicates that the temporary table resides in the shared temporary tablespace.
IS_COMPRESSEDA value of TRUE indicates that the temporary table is compressed.

Example:

mysql> CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
*************************** 1. row ***************************
            TABLE_ID: 32
                NAME: #sqlaf56_2_0
              N_COLS: 4
               SPACE: 19
PER_TABLE_TABLESPACE: FALSE
       IS_COMPRESSED: FALSE
1 row in set (0.00 sec)

Notes:

  • This table is primarily useful for expert level monitoring.

  • Use DESCRIBE or SHOW COLUMNS to view additional information about the columns of this table including data types and default values.

  • You must have the PROCESS privilege to query this table.

22.32 Thread Pool INFORMATION_SCHEMA Tables

The following sections describe the INFORMATION_SCHEMA tables associated with the thread pool plugin. They provide information about thread pool operation:

Rows in these tables represent snapshots in time. In the case of TP_THREAD_STATE, all rows for a thread group comprise a snapshot in time. Thus, the MySQL server holds the mutex of the thread group while producing the snapshot. But it does not hold mutexes on all thread groups at the same time, to prevent a statement against TP_THREAD_STATE from blocking the entire MySQL server.

The thread pool INFORMATION_SCHEMA tables are implemented by individual plugins and the decision whether to load one can be made independently of the others (see Section 6.5.4.2, “Thread Pool Installation”). However, the content of all the tables depends on the thread pool plugin being enabled. If a table plugin is enabled but the thread pool plugin is not, the table becomes visible and can be accessed but will be empty.

22.32.1 The INFORMATION_SCHEMA TP_THREAD_STATE Table

This table has one row per thread created by the thread pool to handle connections. The table has these columns:

  • TP_GROUP_ID

    The thread group ID.

  • TP_THREAD_NUMBER

    The ID of the thread within its thread group. TP_GROUP_ID and TP_THREAD_NUMBER together provide a unique key within the table.

  • PROCESS_COUNT

    The 10ms interval in which the statement that uses this thread is currently executing. 0 means no statement is executing, 1 means it is in the first 10ms, and so forth.

  • WAIT_TYPE

    The type of wait for the thread. NULL means the thread is not blocked. Otherwise, the thread is blocked by a call to thd_wait_begin() and the value specifies the type of wait. The xxx_WAIT columns of the TP_THREAD_GROUP_STATS table accumulate counts for each wait type.

    The WAIT_TYPE value is a string that describes the type of wait, as shown in the following table.

    Table 22.30 WAIT_TYPE Values

    Wait TypeMeaning
    THD_WAIT_SLEEPWaiting for sleep
    THD_WAIT_DISKIOWaiting for Disk IO
    THD_WAIT_ROW_LOCKWaiting for row lock
    THD_WAIT_GLOBAL_LOCKWaiting for global lock
    THD_WAIT_META_DATA_LOCKWaiting for metadata lock
    THD_WAIT_TABLE_LOCKWaiting for table lock
    THD_WAIT_USER_LOCKWaiting for user lock
    THD_WAIT_BINLOGWaiting for binlog
    THD_WAIT_GROUP_COMMITWaiting for group commit
    THD_WAIT_SYNCWaiting for fsync

22.32.2 The INFORMATION_SCHEMA TP_THREAD_GROUP_STATE Table

This table has one row per thread group in the thread pool. Each row provides information about the current state of a group. The table has these columns:

  • TP_GROUP_ID

    The thread group ID. This is a unique key within the table.

  • CONSUMER THREADS

    The number of consumer threads. There is at most one thread ready to start executing if the active threads become stalled or blocked.

  • RESERVE_THREADS

    The number of threads in the reserved state. This means that they will not be started until there is a need to wake a new thread and there is no consumer thread. This is where most threads end up when the thread group has created more threads than needed for normal operation. Often a thread group needs additional threads for a short while and then does not need them again for a while. In this case, they go into the reserved state and remain until needed again. They take up some extra memory resources, but no extra computing resources.

  • CONNECTION_COUNT

    The number of connections using this thread group.

  • QUEUED_QUERIES

    The number of statements waiting in the high-priority queue.

  • QUEUED_TRANSACTIONS

    The number of statements waiting in the low-priority queue. These are the initial statements for transactions that have not started, so they also represent queued transactions.

  • STALL_LIMIT

    The value of the thread_pool_stall_limit variable on the thread group. This is the same value for all thread groups.

  • PRIO_KICKUP_TIMER

    The value of the thread_pool_prio_kickup_timer on the thread group. This is the same value for all thread groups.

  • ALGORITHM

    The value of the thread_pool_algorithm on the thread group. This is the same value for all thread groups.

  • THREAD_COUNT

    The number of threads started in the thread pool as part of this thread group.

  • ACTIVE_THREAD_COUNT

    The number of threads active executing statements.

  • MAX_THREAD_IDS_IN_GROUP

    The maximum thread ID of the threads in the group. This is the same as MAX(TP_THREAD_NUMBER) for the threads when selected from the TP_THREAD_GROUP_STATE table. That is, these two queries are equivalent:

    SELECT TP_GROUP_ID, MAX_THREAD_IDS_IN_GROUP
    FROM TP_THREAD_GROUP_STATE;
    
    SELECT TP_GROUP_ID, MAX(TP_THREAD_NUMBER)
    FROM TP_THREAD_STATE GROUP BY TP_GROUP_ID;
    
  • STALLED_THREAD_COUNT

    The number of stalled statements in the thread group. A stalled statement could be executing, but from a thread pool perspective it is stalled and making no progress. A long-running statement quickly ends up in this category.

  • WAITING_THREAD_NUMBER

    If there is a thread handling the polling of statements in the thread group, this specifies the thread number within this thread group. It is possible that this thread could be executing a statement.

  • OLDEST_QUEUED

    How long in milliseconds the oldest queued statement has been waiting for execution.

22.32.3 The INFORMATION_SCHEMA TP_THREAD_GROUP_STATS Table

This table reports statistics per thread group. There is one row per group. The table has these columns:

  • TP_GROUP_ID

    The thread group ID. This is a unique key within the table.

  • CONNECTIONS_STARTED

    The number of connections started.

  • CONNECTIONS_CLOSED

    The number of connections closed.

  • QUERIES_EXECUTED

    The number of statements executed. This number is incremented when a statement starts executing, not when it finishes.

  • QUERIES_QUEUED

    The number of statements received that were queued for execution. This does not count statements that the thread group was able to begin executing immediately without queuing, which can happen under the conditions described in Section 6.5.4.3, “Thread Pool Operation”.

  • THREADS_STARTED

    The number of threads started.

  • PRIO_KICKUPS

    The number of statements that have been moved from low-priority queue to high-priority queue based on the value of the thread_pool_prio_kickup_timer system variable. If this number increases quickly, consider increasing the value of that variable. A quickly increasing counter means that the priority system is not keeping transactions from starting too early. For InnoDB, this most likely means deteriorating performance due to too many concurrent transactions..

  • STALLED_QUERIES_EXECUTED

    The number of statements that have become defined as stalled due to executing for a time longer than the value of the thread_pool_stall_limit system variable.

  • BECOME_CONSUMER_THREAD

    The number of times thread have been assigned the consumer thread role.

  • BECOME_RESERVE_THREAD

    The number of times threads have been assigned the reserve thread role.

  • BECOME_WAITING_THREAD

    The number of times threads have been assigned the waiter thread role. When statements are queued, this happens very often, even in normal operation, so rapid increases in this value are normal in the case of a highly loaded system where statements are queued up.

  • WAKE_THREAD_STALL_CHECKER

    The number of times the stall check thread decided to wake or create a thread to possibly handle some statements or take care of the waiter thread role.

  • SLEEP_WAITS

    The number of THD_WAIT_SLEEP waits. These occur when threads go to sleep; for example, by calling the SLEEP() function.

  • DISK_IO_WAITS

    The number of THD_WAIT_DISKIO waits. These occur when threads perform disk I/O that is likely to not hit the file system cache. Such waits occur when the buffer pool reads and writes data to disk, not for normal reads from and writes to files.

  • ROW_LOCK_WAITS

    The number of THD_WAIT_ROW_LOCK waits for release of a row lock by another transaction.

  • GLOBAL_LOCK_WAITS

    The number of THD_WAIT_GLOBAL_LOCK waits for a global lock to be released.

  • META_DATA_LOCK_WAITS

    The number of THD_WAIT_META_DATA_LOCK waits for a metadata lock to be released.

  • TABLE_LOCK_WAITS

    The number of THD_WAIT_TABLE_LOCK waits for a table to be unlocked that the statement needs to access.

  • USER_LOCK_WAITS

    The number of THD_WAIT_USER_LOCK waits for a special lock constructed by the user thread.

  • BINLOG_WAITS

    The number of THD_WAIT_BINLOG_WAITS waits for the binary log to become free.

  • GROUP_COMMIT_WAITS

    The number of THD_WAIT_GROUP_COMMIT waits. These occur when a group commit must wait for the other parties to complete their part of a transaction.

  • FSYNC_WAITS

    The number of THD_WAIT_SYNC waits for a file sync operation.

22.33 Extensions to SHOW Statements

Some extensions to SHOW statements accompany the implementation of INFORMATION_SCHEMA:

  • SHOW can be used to get information about the structure of INFORMATION_SCHEMA itself.

  • Several SHOW statements accept a WHERE clause that provides more flexibility in specifying which rows to display.

INFORMATION_SCHEMA is an information database, so its name is included in the output from SHOW DATABASES. Similarly, SHOW TABLES can be used with INFORMATION_SCHEMA to obtain a list of its tables:

mysql> SHOW TABLES FROM INFORMATION_SCHEMA;
+---------------------------------------+
| Tables_in_INFORMATION_SCHEMA          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_COLUMN_USAGE                      |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
+---------------------------------------+
27 rows in set (0.00 sec)

SHOW COLUMNS and DESCRIBE can display information about the columns in individual INFORMATION_SCHEMA tables.

SHOW statements that accept a LIKE clause to limit the rows displayed also permit a WHERE clause that specifies more general conditions that selected rows must satisfy:

SHOW CHARACTER SET
SHOW COLLATION
SHOW COLUMNS
SHOW DATABASES
SHOW FUNCTION STATUS
SHOW INDEX
SHOW OPEN TABLES
SHOW PROCEDURE STATUS
SHOW STATUS
SHOW TABLE STATUS
SHOW TABLES
SHOW TRIGGERS
SHOW VARIABLES

The WHERE clause, if present, is evaluated against the column names displayed by the SHOW statement. For example, the SHOW CHARACTER SET statement produces these output columns:

mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
...

To use a WHERE clause with SHOW CHARACTER SET, you would refer to those column names. As an example, the following statement displays information about character sets for which the default collation contains the string 'japanese':

mysql> SHOW CHARACTER SET WHERE `Default collation` LIKE '%japanese%';
+---------+---------------------------+---------------------+--------+
| Charset | Description               | Default collation   | Maxlen |
+---------+---------------------------+---------------------+--------+
| ujis    | EUC-JP Japanese           | ujis_japanese_ci    |      3 |
| sjis    | Shift-JIS Japanese        | sjis_japanese_ci    |      2 |
| cp932   | SJIS for Windows Japanese | cp932_japanese_ci   |      2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci |      3 |
+---------+---------------------------+---------------------+--------+

This statement displays the multibyte character sets:

mysql> SHOW CHARACTER SET WHERE Maxlen > 1;
+---------+---------------------------+---------------------+--------+
| Charset | Description               | Default collation   | Maxlen |
+---------+---------------------------+---------------------+--------+
| big5    | Big5 Traditional Chinese  | big5_chinese_ci     |      2 |
| ujis    | EUC-JP Japanese           | ujis_japanese_ci    |      3 |
| sjis    | Shift-JIS Japanese        | sjis_japanese_ci    |      2 |
| euckr   | EUC-KR Korean             | euckr_korean_ci     |      2 |
| gb2312  | GB2312 Simplified Chinese | gb2312_chinese_ci   |      2 |
| gbk     | GBK Simplified Chinese    | gbk_chinese_ci      |      2 |
| utf8    | UTF-8 Unicode             | utf8_general_ci     |      3 |
| ucs2    | UCS-2 Unicode             | ucs2_general_ci     |      2 |
| cp932   | SJIS for Windows Japanese | cp932_japanese_ci   |      2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci |      3 |
+---------+---------------------------+---------------------+--------+