MySQL :: MySQL 8.0 Reference Manual :: 24.31.24 The INFORMATION_SCHEMA INNODB_SYS

version 8.0

5.7 current
5.6

5.6 Japanese

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

Pre-General Availability Draft: 2017-07-17

24.31.24 The INFORMATION_SCHEMA INNODB_SYS_TABLESPACES Table

The INNODB_SYS_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 System 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 24.25 INNODB_SYS_TABLESPACES Columns

Column name	Description
`SPACE`	Tablespace Space ID.
`NAME`	The database and table name (for example, `world_innodb\city`)
`FLAG`	This value provides bit level information 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 `InnoDB` general tablespaces created using `CREATE TABLESPACE` and `Single` (for `InnoDB` file-per-table tablespaces).
`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.

Example:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_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

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_SYS_TABLESPACES.FLAG Column Value:

The INNODB_SYS_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.26 Bit Position Values for Interpreting INNODB_SYS_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_SYS_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_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    |
+-----------------------+-------+

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_SYS_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)

PREV HOME UP NEXT

User Comments