Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.1Mb
PDF (A4) - 38.1Mb
PDF (RPM) - 33.0Mb
HTML Download (TGZ) - 8.1Mb
HTML Download (Zip) - 8.1Mb
HTML Download (RPM) - 7.0Mb
Man Pages (TGZ) - 132.7Kb
Man Pages (Zip) - 189.0Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

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

24.36.25 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 reports metadata for all InnoDB tablespace types including file-per-table tablespaces, general tablespaces, the system tablespace, the temporary tablespace, and undo tablespaces.

The INNODB_TABLESPACES table has these columns:

  • SPACE

    The tablespace ID.

  • NAME

    The schema (database) and table name.

  • FLAG

    Bit-level data about tablespace format and storage characteristics.

  • ROW_FORMAT

    The 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_SIZE

    The tablespace page size. The data in this field is interpreted from the tablespace flags information that resides in the .ibd file.

  • ZIP_PAGE_SIZE

    The tablespace zip page size. The data in this field is interpreted from the tablespace flags information that resides in the .ibd file.

  • SPACE_TYPE

    The type of tablespace. Possible values include General for general tablespaces, Single for file-per-table tablespaces, and System for the system tablespace.

  • FS_BLOCK_SIZE

    The 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_SIZE

    The 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_SIZE

    The 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_VERSION

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

  • SPACE_VERSION

    Defines the tablespace version, used to track changes to the tablespace format.

  • ENCRYPTION

    Indicates whether the tablespace is encrypted. This column was added in MySQL 8.0.13.

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
    ENCRYPTION: N

Notes

  • You must have the PROCESS privilege to query this table.

  • Use the INFORMATION_SCHEMA COLUMNS table or the SHOW COLUMNS statement to view additional information about the columns of this table, including data types and default values.

  • 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 24.7 Bit Position Values for Interpreting INNODB_TABLESPACES FLAG Column Data

Bit Position Description Decimal Numeric Value
0 This 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-4 These 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

5 This 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-9 These 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

10 This 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

11 This 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.

12 This 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
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.