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:
SCHEMATA
, TABLES
, and COLUMNS
contain information about the structure of databases, tables, columns, and so on.GLOBAL_VARIABLES
and SESSION_VARIABLES
tables list the values of the server's system variables. The GLOBAL_STATUS
and SESSION_STATUS
tables provide information about the operations performed by the server._PRIVILEGES
indicate users that have various permissions on objects.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
, INNODB_LOCK_WAITS
, and INNODB_TRX
tables contain information about active locks, waits, and transactions that acquired a lock or are waiting for a lock, respectively.INNODB_BUFFER_
are the buffer pool contents and page usage.INNODB_CMP
provide information about the performance of compressed pages.INNODB_FT_
provide information about full-text indexes in InnoDB tables.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.