On-disk anatomy

Everything lives under datadir. Here is what is actually there on an 8.x server, and what each file is for:

-- Where is everything?
SELECT @@datadir, @@innodb_file_per_table,
       @@innodb_undo_directory, @@innodb_redo_log_capacity;

-- Every tablespace this server knows about, with its file.
SELECT space, name, file_size, allocated_size
FROM information_schema.innodb_tablespaces
ORDER BY file_size DESC
LIMIT 10;

-- In a shell, look at the actual directory:
--   sudo ls -lh $(mysql -Nse "SELECT @@datadir")

A tablespace is a logical container of pages, backed by one or more files. InnoDB has five kinds, and knowing which is which explains several operational surprises.

This is the central object of the whole module. Every read, every write, every lock, every checksum operates on a page. Default size 16384 bytes. Here is the entire layout:

Three consequences worth internalizing

• The linked list plus directory is why page-internal search is cheap. Records are a singly-linked list in key order, but the directory gives you binary search over ~1/6 of them, then a short walk. So finding a key inside a page is roughly O(log n) with a small constant, not a linear scan.
• The prev/next pointers in the FIL header chain the leaves. That is what makes a range scan fast: descend the tree once, then follow leaf pointers sideways without going back through the root. It is also why a B+tree beats a B-tree for range queries (chapter 24).
• The checksum appears twice, at the very start and the very end. That is not redundancy for its own sake — it is a torn-page detector. If the OS wrote the first half of a page and died, header and trailer disagree.

-- Page size, and the checksum algorithm in use.
SELECT @@innodb_page_size, @@innodb_checksum_algorithm;

-- Peek at real pages in the buffer pool: type, and how full they are.
SELECT page_type, COUNT(*) AS pages,
       ROUND(AVG(data_size)) AS avg_bytes_used,
       ROUND(AVG(number_records)) AS avg_rows
FROM information_schema.innodb_buffer_page
WHERE page_type IS NOT NULL
GROUP BY page_type
ORDER BY pages DESC;

-- avg_bytes_used on INDEX pages tells you your real fill factor.
-- Much below ~8000 on a write-heavy table means fragmentation (ch 30).

InnoDB does not allocate one page at a time once a table grows. The hierarchy:

tablespace  (a .ibd file)
   └── segment   -- one per index, split into leaf + non-leaf
         └── extent    -- 64 contiguous pages = 1MB (at 16K pages)
               └── page      -- 16KB
                     └── row

A small table's first 32 pages come from shared "fragment" extents, so a table with three rows does not cost a megabyte. Past that, InnoDB allocates whole extents at a time. The point is physical contiguity: 64 adjacent pages can be read with sequential IO, which on a spinning disk was the difference between fast and unusable, and on SSD still matters for read-ahead (chapter 72).

Each index gets two segments — one for leaf pages, one for internal nodes. Keeping them apart means a tree traversal does not have to jump between leaves and internal nodes scattered together, and it lets read-ahead work on the leaf chain specifically.

A row on a page has a header before it. What is in that header depends on the ROW_FORMAT, and the differences are not cosmetic.

Those two hidden columns — DB_TRX_ID and DB_ROLL_PTR — are the entire basis of MVCC, and they cost 13 bytes on every row of every table. Chapter 43 uses them properly.

-- Any table still on an old format?
SELECT name, row_format, space_type
FROM information_schema.innodb_tables
WHERE row_format NOT IN ('Dynamic', 'Compressed')
  AND name NOT LIKE 'mysql/%'
  AND name NOT LIKE 'sys/%';

-- Convert one (rebuilds the table — see ch 100 before doing this in prod):
-- ALTER TABLE t ROW_FORMAT=DYNAMIC, ALGORITHM=INPLACE, LOCK=NONE;

-- Prove the 13-byte MVCC overhead exists on every row:
-- a table of one TINYINT still costs far more than 1 byte per row.
SELECT table_name, table_rows, data_length,
       ROUND(data_length / NULLIF(table_rows,0)) AS bytes_per_row
FROM information_schema.tables
WHERE table_schema = DATABASE();

A row must fit in about half a page — roughly 8126 bytes — because InnoDB requires at least two rows per page to keep the tree balanced. When a row exceeds that, InnoDB moves its longest variable-length columns off-page, into overflow pages, leaving a 20-byte pointer behind.

The pointer is: 4 bytes space id, 4 bytes first overflow page number, 4 bytes offset, 8 bytes total length. Overflow pages chain together for values larger than one page.

The 767-byte number you have definitely hit

Under COMPACT and REDUNDANT, an index could only cover the first 767 bytes of a column, because that was the on-page prefix limit. With utf8mb4 at 4 bytes per character, that is 191 characters — which is exactly why so much legacy code declares VARCHAR(191), and why older Rails and WordPress schemas are full of that number. It is a fossil of a page-layout constraint.

DYNAMIC raises the limit to 3072 bytes (768 characters at utf8mb4), so the constraint is largely gone — but only if the table is actually DYNAMIC and innodb_large_prefix semantics apply, which is another reason to check chapter 20's query on old schemas.

You can set 4K, 8K, 16K (default), 32K or 64K — but only when initializing the data directory. It cannot be changed afterwards without a dump and reload, so it is effectively a permanent decision.

Everything above is theory until you look at the bytes. This is the exercise that converts this part from reading into knowing.

Method 1 — innodb_ruby (the good tool)

# Jeremy Cole's library. The single best way to see InnoDB structures.
gem install innodb_ruby

# What is every page in this file?
innodb_space -s ibdata1 -T yourdb/users space-page-type-summary

# Walk the index tree, level by level.
innodb_space -s ibdata1 -T yourdb/users -I PRIMARY index-recurse

# Dump one page completely: header, records, directory, trailer.
innodb_space -s ibdata1 -T yourdb/users -p 4 page-dump

# Fill factor across the whole index — the fragmentation view.
innodb_space -s ibdata1 -T yourdb/users -I PRIMARY index-fill-factor

Method 2 — hexdump, no tools required

# Flush first, or you are reading a stale file.
mysql -e "FLUSH TABLES yourdb.users FOR EXPORT;"

# The first 38 bytes of page 0 — the FIL header.
sudo hexdump -C -n 38 /var/lib/mysql/yourdb/users.ibd

#   bytes 0-3   checksum
#   bytes 4-7   page number        (00 00 00 00 = page 0)
#   bytes 8-11  previous page       (ff ff ff ff = none)
#   bytes 12-15 next page           (ff ff ff ff = none)
#   bytes 16-23 LSN of last change
#   bytes 24-25 page type           (0x45bf = INDEX, 0x0008 = FSP_HDR)

# Page 3 is usually the clustered index root. Skip 3 × 16384 bytes.
sudo hexdump -C -s 49152 -n 64 /var/lib/mysql/yourdb/users.ibd

# Find your actual data — grep the raw file for a value you inserted.
sudo strings /var/lib/mysql/yourdb/users.ibd | head -40

mysql -e "UNLOCK TABLES;"

You now know what a page is. Part 3 is about the structure those pages form: the B+tree, how it splits, and why your choice of primary key is the most consequential schema decision you make.