The data files that you define in the configuration file form the
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
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 the
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
InnoDB. (These segments are different from the
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 segment.
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
cannot be allocated to segments as a whole, but only as individual
When you ask for available free space in the tablespace by issuing
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,
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 14.2.13, “InnoDB Multi-Versioning”.)
To see information about the tablespace, use the Tablespace Monitor. See Section 14.15, “InnoDB Monitors”.
The maximum row length, except for variable-length columns
TEXT), is slightly less than half
of a database page. That is, the maximum row length is about 8000
must be less than 4GB, and the total row length, including
TEXT columns, must be less than
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
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.