21.29.15 The INFORMATION_SCHEMA INNODB_SYS_TABLESPACES Table

The INNODB_SYS_TABLESPACES table provides metadata about InnoDB tablespaces, equivalent to the information in the SYS_TABLESPACES table in the InnoDB data dictionary.

Table 21.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 or Barracuda). 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 14.8, “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.

Example:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE SPACE = 57 \G
*************************** 1. row ***************************
        SPACE: 57
         NAME: test/t1
         FLAG: 0
  FILE_FORMAT: Antelope
   ROW_FORMAT: Compact or Redundant
    PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
1 row in set (0.00 sec)

Notes:

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 21.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

5This bit is set for file-per-table tablespaces if the row format of the table is DYNAMIC or COMPRESSED.
  • 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

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


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)