The
information_schema database (often called I_S for brevity) is a virtual database that contains informative tables. These tables can be divided into several groups:
Metadata tables: Tables such as
SCHEMATA,TABLES, andCOLUMNScontain information about the structure of databases, tables, columns, and so on.Status and variables tables: The
GLOBAL_VARIABLESandSESSION_VARIABLEStables list the values of the server's system variables. TheGLOBAL_STATUSandSESSION_STATUStables provide information about the operations performed by the server.Privilege tables: The tables whose names end with
_PRIVILEGESindicate users that have various permissions on objects.The PROFILING table: This table can be used to monitor the queries executed during the current session, and see which low-level operations are performed by the server.
The PROCESSLIST table: This table shows the active sessions and their status.
Several tables provide information about InnoDB. Some of them are XtraDB-specific. These table names begin with INNODB_ or XTRADB_ if they only exist for XtraDB, discussed as follows:
InnoDB locks tables: The
INNODB_LOCKS,INNODB_LOCK_WAITS, andINNODB_TRXtables contain information about active locks, waits, and transactions that acquired a lock or are waiting for a lock, respectively.InnoDB buffer pool tables: Tables whose names start with
INNODB_BUFFER_are the buffer pool contents and page usage.The INNODB_METRICS table: This table provides information about some low-level operations performed by InnoDB.
InnoDB compression tables: Tables whose names start with
INNODB_CMPprovide information about the performance of compressed pages.InnoDB full-text tables: Tables whose names start with
INNODB_FT_provide information about full-text indexes in InnoDB tables.InnoDB data dictionary tables: Tables whose names start with
INNODB_SYS_provide metadata about InnoDB tables, columns, and foreign keys. They are similar to the more generic tables that contain metadata, but these tables are specific to InnoDB. They also contain statistics and information about files.
Generally, the information that can be read from information_schema can also be obtained with the SHOW statements and vice versa. Querying information_schema is a more flexible and standard way to retrieve such information, but is also more verbose.
Information on the InnoDB activities can also be obtained in a human-readable form via the SHOW ENGINE InnoDB STATUS and SHOW ENGINE InnoDB MUTEX statements.
To answer the queries of information_schema, the server opens and reads the database files, which can be a slow operation. For this reason, the queries that are often executed on a production server should be optimized to only read the necessary files. This can usually be done with a good WHERE clause.