Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.4Mb
PDF (A4) - 35.4Mb
PDF (RPM) - 34.5Mb
EPUB - 8.7Mb
HTML Download (TGZ) - 8.5Mb
HTML Download (Zip) - 8.5Mb
HTML Download (RPM) - 7.3Mb
Eclipse Doc Plugin (TGZ) - 9.3Mb
Eclipse Doc Plugin (Zip) - 11.5Mb
Man Pages (TGZ) - 201.4Kb
Man Pages (Zip) - 306.7Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  The INFORMATION_SCHEMA INNODB_SYS_TABLESPACES 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)

User Comments
Sign Up Login You must be logged in to post a comment.