GNU Info

(mysql.info)InnoDB File space

---

Next: InnoDB File Defragmenting Prev: InnoDB Disk i/o Up: File space management

---

Enter node , (file) or (file)node

File space management
.....................

The data files you define in the configuration file form the tablespace
of InnoDB. The files are simply catenated to form the tablespace, there
is no striping in use.  Currently you cannot directly instruct where
the space is allocated for your tables, except by using the following
fact: from a newly created tablespace InnoDB will allocate space
starting from the low end.

The tablespace consists of database pages whose default size is 16 kB.
The pages are grouped into extents of 64 consecutive pages. The 'files'
inside a tablespace are called segments in InnoDB. The name of the
rollback segment is somewhat misleading because it actually contains
many segments in the tablespace.

For each index in InnoDB we allocate two segments: one is for non-leaf
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 the
data.

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.  InnoDB can add to a large segment up to 4
extents at a time to ensure good sequentiality of 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 pages.

When you issue a query `SHOW TABLE STATUS FROM ... LIKE ...' to ask for
available free space in the tablespace, InnoDB will report you the
space which is certainly usable in totally free extents of the
tablespace. InnoDB always reserves some extents for clean-up and other
internal purposes; these reserved extents are not included in the free
space.

When you delete data from a table, InnoDB will contract the
corresponding B-tree indexes. It depends on the pattern of deletes if
that frees individual pages or extents to the tablespace, so that the
freed space is available for other users. Dropping a table or deleting
all rows from it is guaranteed to release the space to other users, but
remember that deleted rows can be physically removed only in a purge
operation after they are no longer needed in transaction rollback or
consistent read.