MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
The InnoDB
Tablespace Monitor is deprecated
and may be removed in a future release.
The InnoDB
Tablespace Monitor prints
information about the file segments in the shared tablespace and
validates the tablespace allocation data structures. The
Tablespace Monitor does not describe file-per-table tablespaces
created with the
innodb_file_per_table
option.
Example InnoDB
Tablespace Monitor output:
================================================ 090408 21:28:09 INNODB TABLESPACE MONITOR OUTPUT ================================================ FILE SPACE INFO: id 0 size 13440, free limit 3136, free extents 28 not full frag extents 2: used pages 78, full frag extents 3 first seg id not used 0 23845 SEGMENT id 0 1 space 0; page 2; res 96 used 46; full ext 0 fragm pages 32; free extents 0; not full extents 1: pages 14 SEGMENT id 0 2 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 3 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 ... SEGMENT id 0 15 space 0; page 2; res 160 used 160; full ext 2 fragm pages 32; free extents 0; not full extents 0: pages 0 SEGMENT id 0 488 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 17 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 ... SEGMENT id 0 171 space 0; page 2; res 592 used 481; full ext 7 fragm pages 16; free extents 0; not full extents 2: pages 17 SEGMENT id 0 172 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 173 space 0; page 2; res 96 used 44; full ext 0 fragm pages 32; free extents 0; not full extents 1: pages 12 ... SEGMENT id 0 601 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 NUMBER of file segments: 73 Validating tablespace Validation ok --------------------------------------- END OF INNODB TABLESPACE MONITOR OUTPUT =======================================
The Tablespace Monitor output includes information about the shared tablespace as a whole, followed by a list containing a breakdown for each segment within the tablespace.
In this example using the default page size, the tablespace consists of database pages that are 16KB each. The pages are grouped into extents of size 1MB (64 consecutive pages).
The initial part of the output that displays overall tablespace information has this format:
FILE SPACE INFO: id 0 size 13440, free limit 3136, free extents 28 not full frag extents 2: used pages 78, full frag extents 3 first seg id not used 0 23845
Overall tablespace information includes these values:
id
The tablespace ID. A value of 0 refers to the shared tablespace.
size
The current tablespace size in pages.
free limit
The minimum page number for which the free list has not been initialized. Pages at or above this limit are free.
free extents
The number of free extents.
not full frag extents
, used
pages
The number of fragment extents that are not completely filled, and the number of pages in those extents that have been allocated.
full frag extents
The number of completely full fragment extents.
first seg id not used
The first unused segment ID.
Individual segment information has this format:
SEGMENT id 0 15 space 0; page 2; res 160 used 160; full ext 2 fragm pages 32; free extents 0; not full extents 0: pages 0
Segment information includes these values:
id
:
The segment ID.
space
, page
The tablespace number and page within the tablespace where the
segment “inode” is located. A tablespace number
of 0 indicates the shared tablespace.
InnoDB
uses inodes to keep track of
segments in the tablespace. The other fields displayed for a
segment (id
, res
, and so
forth) are derived from information in the inode.
res
The number of pages allocated (reserved) for the segment.
used
The number of allocated pages in use by the segment.
full ext
The number of extents allocated for the segment that are completely used.
fragm pages
The number of initial pages that have been allocated to the segment.
free extents
The number of extents allocated for the segment that are completely unused.
not full extents
The number of extents allocated for the segment that are partially used.
pages
The number of pages used within the not-full extents.
When a segment grows, it starts as a single page, and
InnoDB
allocates the first pages for it one at
a time, up to 32 pages (this is the fragm pages
value). After that, InnoDB
allocates complete
extents. InnoDB
can add up to 4 extents at a
time to a large segment to ensure good sequentiality of data.
For the example segment shown earlier, it has 32 fragment pages, plus 2 full extents (64 pages each), for a total of 160 pages used out of 160 pages allocated. The following segment has 32 fragment pages and one partially full extent using 14 pages for a total of 46 pages used out of 96 pages allocated:
SEGMENT id 0 1 space 0; page 2; res 96 used 46; full ext 0 fragm pages 32; free extents 0; not full extents 1: pages 14
It is possible for a segment that has extents allocated to it to
have a fragm pages
value less than 32 if some
of the individual pages have been deallocated subsequent to extent
allocation.