Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 34.6Mb
PDF (A4) - 34.6Mb
PDF (RPM) - 32.2Mb
HTML Download (TGZ) - 8.2Mb
HTML Download (Zip) - 8.2Mb
HTML Download (RPM) - 7.0Mb
Man Pages (TGZ) - 147.5Kb
Man Pages (Zip) - 208.8Kb
Info (Gzip) - 3.1Mb
Info (Zip) - 3.1Mb


MySQL 8.0 Reference Manual  /  ...  /  The INFORMATION_SCHEMA INNODB_TABLESPACES Table

Pre-General Availability Draft: 2017-12-15

25.32.24 The INFORMATION_SCHEMA INNODB_TABLESPACES Table

The INNODB_TABLESPACES table provides metadata about InnoDB file-per-table and general tablespaces.

For related usage information and examples, see Section 15.14.3, “InnoDB INFORMATION_SCHEMA Schema Object Tables”.

Note

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 25.25 INNODB_TABLESPACES Columns

Column nameDescription
SPACETablespace Space ID.
NAMEThe database and table name.
FLAGBit-level data about tablespace format and storage characteristics.
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 general tablespaces, Single for file-per-table tablespaces, and System for the system tablespace.
FS_BLOCK_SIZEThe file system block size, which is the unit size used for hole punching. This column was added 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 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 with the introduction of the InnoDB transparent page compression feature.
SERVER_VERSIONDefines the MySQL version that created the tablespace, or the MySQL version into which the tablespace was imported, or the version of the last major MySQL version upgrade. The value is unchanged by a release series upgrade, such as an upgrade from MySQL 8.0.x to 8.0.y. The value can be considered a creation marker or certified marker for the tablespace. This column was added in MySQL 8.0.4.
SPACE_VERSIONDefines the tablespace version, used to changes to the tablespace format. This column was added in MySQL 8.0.4.

Example:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE = 26\G
*************************** 1. row ***************************
         SPACE: 26
          NAME: test/t1
          FLAG: 0
    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
SERVER_VERSION: 8.0.4
 SPACE_VERSION: 1

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.

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

Interpreting the INNODB_TABLESPACES.FLAG Column Value:

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

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

Table 25.26 Bit Position Values for Interpreting INNODB_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. If the bit is not set, the row format of tables in the tablespace may be either REDUNDANT or COMPACT. If it is a file-per-table tablespace, you can query INNODB_TABLES to determine if the row format is REDUNDANT or COMPACT.
  • 0 - REDUNDANT or COMPACT

  • 1 - DYNAMIC or COMPRESSED

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 that the tablespace holds REDUNDANT or COMPACT tables. 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).
  • 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. 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_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    |
+-----------------------+-------+

mysql> SHOW VARIABLES LIKE 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+

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

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE 'test/t1'\G
*************************** 1. row ***************************
        SPACE: 75
         NAME: test/t1
         FLAG: 33
   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.