Fun MySQL fact of the day: to a greater extent

Yesterday, we discussed that an InnoDB index (remember: there is no table) consists of a collection of records stored in a collection of pages. Well, the fun doesn't stop there: pages are stored in a collection of extents, and like how records are stored in insertion-order inside pages, pages are appended to disk in extents in "creation" order.

Recalling back to yesterday, we discussed that an InnoDB page is 16KB by default, and records are stored contiguously in insert-order. This would mean, for example, that we could store (roughly) 16 1KB records, 8 2KB records, or 4 4KB records on a page. There are, of course, really big exceptions, technicalities, and limits to this, but let's ignore those for now. Instead, let's pretend that we have (roughly) 2KB records: we can store (roughly) 8 records per page, and all in insert-order. Once the page is full, we need another page, and when that page is full, we need another page, and so on. As you can imagine, if we were inserting a lot of data, we'd end up with an awful lot of disk fragmentation due to the growth rate of only 16KB at a time, and this is where extents come in.

Instead of growing an index 16KB pages at a time, InnoDB will grow it in 1 to 4 extents at a time. By default, an extent is 64 pages (thus 1MB), meaning that a "fast growing" index can grow in 1MB to 4MB increments. This larger growth rate helps to reduce fragmentation and helps keep disk access as sequential as possible.

This is especially true for monotonic (increasing) keys on a PRIMARY index, which, as you recall, just get "appended" to the end of the index. But what about non-monotonic indices? More fun facts about those next week.