Information Schema INNODB_SYS_TABLESPACES Table
The Information Schema INNODB_SYS_TABLESPACES
table contains information about InnoDB tablespaces. Until MariaDB 10.5 it was based on the internal SYS_TABLESPACES
table. This internal table was removed in MariaDB 10.6.0, so this Information Schema table has been repurposed
to directly reflect the filesystem (fil_system.space_list).
The PROCESS
privilege is required to view the table.
It has the following columns:
Column | Description |
---|---|
SPACE | Unique InnoDB tablespace identifier. |
NAME | Database and table name separated by a backslash, or the uppercase InnoDB system table name. |
FLAG | 1 if a DATA DIRECTORY option has been specified in CREATE TABLE , otherwise 0 . |
FILE_FORMAT | InnoDB file format. Removed in MariaDB 10.3.1 |
ROW_FORMAT | InnoDB storage format used for this tablespace. If the Antelope file format is used, this value is always Compact or Redundant . When a table's checksum algorithm is full_crc32 (the default from MariaDB 10.5), the value can only be Compressed or NULL . |
PAGE_SIZE | Page size in bytes for this tablespace. Until MariaDB 10.5.0, this was the value of the innodb_page_size variable. From MariaDB 10.6.0, contains the physical page size of a page (previously ZIP_PAGE_SIZE ). |
ZIP_PAGE_SIZE | Zip page size for this tablespace. Removed in MariaDB 10.6.0. |
SPACE_TYPE | Tablespace type. Can be General for general tablespaces or Single for file-per-table tablespaces. Introduced MariaDB 10.2.1. Removed MariaDB 10.5.0. |
FS_BLOCK_SIZE | File system block size. Introduced MariaDB 10.2.1. |
FILE_SIZE | Maximum size of the file, uncompressed. Introduced MariaDB 10.2.1. |
ALLOCATED_SIZE | Actual size of the file as per space allocated on disk. Introduced MariaDB 10.2.1. |
FILENAME | Tablespace datafile path, previously part of the INNODB_SYS_DATAFILES table. Added in MariaDB 10.6.0. |
Examples
DESC information_schema.innodb_sys_tablespaces; +----------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+---------------------+------+-----+---------+-------+ | SPACE | int(11) unsigned | NO | | 0 | | | NAME | varchar(655) | NO | | | | | FLAG | int(11) unsigned | NO | | 0 | | | ROW_FORMAT | varchar(22) | YES | | NULL | | | PAGE_SIZE | int(11) unsigned | NO | | 0 | | | ZIP_PAGE_SIZE | int(11) unsigned | NO | | 0 | | | SPACE_TYPE | varchar(10) | YES | | NULL | | | FS_BLOCK_SIZE | int(11) unsigned | NO | | 0 | | | FILE_SIZE | bigint(21) unsigned | NO | | 0 | | | ALLOCATED_SIZE | bigint(21) unsigned | NO | | 0 | | +----------------+---------------------+------+-----+---------+-------+
From MariaDB 10.4:
SELECT * FROM information_schema.INNODB_SYS_TABLESPACES LIMIT 2\G *************************** 1. row *************************** SPACE: 2 NAME: mysql/innodb_table_stats FLAG: 33 ROW_FORMAT: Dynamic PAGE_SIZE: 16384 ZIP_PAGE_SIZE: 0 SPACE_TYPE: Single FS_BLOCK_SIZE: 4096 FILE_SIZE: 98304 ALLOCATED_SIZE: 98304 *************************** 2. row *************************** SPACE: 3 NAME: mysql/innodb_index_stats FLAG: 33 ROW_FORMAT: Dynamic PAGE_SIZE: 16384 ZIP_PAGE_SIZE: 0 SPACE_TYPE: Single FS_BLOCK_SIZE: 4096 FILE_SIZE: 98304 ALLOCATED_SIZE: 98304
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.