Page vs. Extent vs. Segment vs. Tablespace

Question

Mysql's Innodb has concepts of page, extent, segment, tablespace but I don't know how they interact with each other. How are they related? What does it mean a tablespace has 16K of pages? How does this relate to the physical rows and is 'pages' something in the memory?

I would assume that Oracle might have the same concept but I'm not sure where to look for it. Is there a good reference / book that talks about this in detail?

Answer 1

For innodb specifically:

A tablespace is both a physical and a logical concept. It used to be that all innodb tables had their data mixed together in the ibdata files. Within those files, a tablespace is a not-necessarily-contiguous collection of segments that make up the table. In newer versions of mysql, a single table can be put in to its own file, which is also called a tablespace.

In either case, a tablespace contains:

• segments, which contain 1 or more extents for that table.

• extents contain 64 pages. I don't know why 64.

• pages are 16k, which is theoretically optimized for efficient storage and retrieval at the disk level. pages are allocated in extent quantities when more pages are needed.

See page 11 of this pdf.