W3cubDocs

Information Schema INNODB_SYS_TABLESPACES Table

MariaDB starting with 10.0.4

The INNODB_SYS_TABLESPACES table was added in MariaDB 10.0.4.

The Information Schema INNODB_SYS_TABLESPACES table contains information about InnoDB tablespaces.

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.
`ROW_FORMAT`	InnoDB storage format used for this tablespace. If the Antelope file format is used, this value is always `Compact or Redundant`.
`PAGE_SIZE`	Page size in bytes for this tablespace.
`ZIP_PAGE_SIZE`	Zip page size for this tablespace.
`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.

Examples

MariaDB 10.4:

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       |       |
| FILE_FORMAT    | varchar(10)         | YES  |     | NULL    |       |
| 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
   FILE_FORMAT: Barracuda
    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
   FILE_FORMAT: Barracuda
    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.

© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/information-schema-innodb_sys_tablespaces-table/