The data files that you define in the configuration file form the
InnoDB system tablespace.
The files are logically concatenated to form the tablespace. There is no striping in use. Currently, you cannot
define where within the tablespace your tables are allocated. In a newly created tablespace,
InnoDB allocates space starting from the first data file.
To avoid the issues that come with storing all tables and indexes inside the system tablespace, you can turn on
innodb_file_per_table configuration option, which stores each newly created
table in a separate tablespace file (with extension
.ibd). For tables stored this
way, there is less fragmentation within the disk file, and when the table is truncated, the space is returned to
the operating system rather than still being reserved by InnoDB within the system tablespace.
Each tablespace consists of database pages.
Every tablespace in a MySQL instance has the same page size. By default, all
tablespaces have a page size of 16KB; you can reduce the page size to 8KB or 4KB by specifying the
innodb_page_size option when you create the MySQL instance.
The pages are grouped into extents of size
1MB (64 consecutive 16KB pages, or 128 8KB pages, or 256 4KB pages). The "files" inside a tablespace are called segments in
InnoDB. (These segments are different from the rollback
segment, which actually contains many tablespace segments.)
When a segment grows inside the tablespace,
InnoDB allocates the first 32 pages to
it one at a time. After that,
InnoDB starts to allocate whole extents to the
InnoDB can add up to 4 extents at a time to a large segment to ensure good
sequentiality of data.
Two segments are allocated for each index in
InnoDB. One is for nonleaf nodes of
the B-tree, the other is for the leaf nodes. Keeping the leaf nodes contiguous on disk enables better sequential
I/O operations, because these leaf nodes contain the actual table data.
Some pages in the tablespace contain bitmaps of other pages, and therefore a few extents in an
InnoDB tablespace cannot be allocated to segments as a whole, but only as individual
When you ask for available free space in the tablespace by issuing a
SHOW TABLE STATUS statement,
InnoDB reports the
extents that are definitely free in the tablespace.
InnoDB always reserves some
extents for cleanup and other internal purposes; these reserved extents are not included in the free space.
When you delete data from a table,
InnoDB contracts the corresponding B-tree
indexes. Whether the freed space becomes available for other users depends on whether the pattern of deletes
frees individual pages or extents to the tablespace. Dropping a table or deleting all rows from it is guaranteed
to release the space to other users, but remember that deleted rows are physically removed only by the purge operation, which happens
automatically some time after they are no longer needed for transaction rollbacks or consistent reads. (See Section 126.96.36.199, "
To see information about the tablespace, use the Tablespace Monitor. See Section
SHOW ENGINE INNODB STATUS and the
The maximum row length, except for variable-length columns (
is slightly less than half of a database page. That is, the maximum row length is about 8000 bytes.
columns must be less than 4GB, and the total row length, including
columns, must be less than 4GB.
If a row is less than half a page long, all of it is stored locally within the page. If it exceeds half a page,
variable-length columns are chosen for external off-page storage until the row fits within half a page. For a
column chosen for off-page storage,
InnoDB stores the first 768 bytes locally in
the row, and the rest externally into overflow pages. Each such column has its own list of overflow pages. The
768-byte prefix is accompanied by a 20-byte value that stores the true length of the column and points into the
overflow list where the rest of the value is stored.