The data files that you define in the configuration file form
InnoDB tablespace. The files are
logically concatenated to form the tablespace. There is no
striping in use. You cannot define where within the tablespace
your tables are allocated. However, in a newly created
InnoDB allocates space starting
from the first data file.
The tablespace consists of database pages with a default size of
16KB. The pages are grouped into extents of size 1MB (64
consecutive pages). The “files” inside a tablespace
are called segments in
InnoDB. The term “rollback
segment” is somewhat confusing because it actually
contains many tablespace segments.
When a segment grows inside the tablespace,
InnoDB allocates the first 32 pages to it
individually. 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. The idea here is to
achieve better sequentiality for the leaf nodes, which contain
Some pages in the tablespace contain bitmaps of other pages, and
therefore a few extents in an
tablespace cannot be allocated to segments as a whole, but only
as individual pages.
When you ask for available free space in the tablespace by
SHOW TABLE STATUS
InnoDB reports the extents that
are definitely free in the tablespace.
always reserves some extents for cleanup and other internal
purposes; these reserved extents are not included in the free
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 in an (automatic)
purge operation after they are no longer needed for transaction
rollbacks or consistent reads. (See
Section 14.2.9, “InnoDB Multi-Versioning”.)
To see information about the tablespace, use the Tablespace Monitor. See Section 220.127.116.11, “SHOW ENGINE INNODB STATUS and the 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
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.