-1

If the whole idea of the B+-tree structure, that the MySQL clustered index uses, is to be efficient in accessing disk data. Then it's logical to make index page size equal to the disk block size which is usually 512 bytes.

Then one physical disk lookup (required rotations and other movements) will provide us with one index page data. So why index page size is more like 16k instead? (As I understood, retrieving such a page requires reading of different disk blocks, not always sequential, and a lot of disk rotations).

What am I missing here?

3 Answers3

1

InnoDB is full of compromises between speed and space.

InnoDB was designed before SSDs, so reading multiple adjacent disk sectors was a good idea, especially if you are likely to need more than just one piece of the 16KB block.

If you look at the disk usage for the entire database, you may find a "waste" of only a few percent when measuring space.

Also, today, most applications are small enough to fit entirely in RAM. So, the block size makes little difference. Actually, 16KB blocks is slightly faster on the initial load due to fewer I/Os, even for SSDs.

If you have a huge dataset and are I/O-bound you may get into performance difficulties if you mostly do random reads of one small row. Example: key-value dataset with UUIDs for ids. In this one use case, a smaller block size on SSDs might be noticeably faster.

Back to tradeoffs -- That UUID (etc) example is not very common.

Rick James
  • 80,479
  • 5
  • 52
  • 119
0

InnoDB requires that at least two rows fit in a given page. This rule is bent a bit because the variable-length data types VARCHAR, VARBINARY, TEXT, and BLOB may overflow to additional pages. Both otherwise, a given row must fit within a little less than 8KB.

If the InnoDB page size were smaller than the current default of 16KB, the size limit of a row would be lower than it currently is. In fact, you can reconfigure InnoDB to use different page sizes, and this does change the maximum row size.

The default of 16KB is considered appropriate for most cases. It's true that there's a chance it does not align with the disk sector size exactly, but hopefully when performance is crucial your queries read from the buffer pool, not from disk.

Bill Karwin
  • 16,963
  • 3
  • 31
  • 45
0

retrieving such a page requires reading of different disk blocks, not always sequential, and a lot of disk rotations

That is incorrect. A page is the unit of database I/O; the DBMS will read and write at least one page at a time atomically, possibly more (i.e. multiple pages), especially when reading. This means a page is always contiguous on media (barring relocation at the physical drive level).

it's logical to make index page size equal to the disk block size which is usually 512 bytes

That is also somewhat incorrect. When choosing the page size one needs to find a sweet spot between the efficiency of large sequential I/O operations and wasted bandwidth when you only need a small portion of data from what you read. There is also another constraint: an index key cannot span pages, so your index page size (minus overhead) must be able to accommodate at least two key values; 512 byte pages won't be practical from that point of view.

Some DBMSes allow the DBA to choose different page sizes for different tables and indexes, thus enabling more precise tuning of I/O performance for particular scenarios.

See also this somewhat related Q&A -- it's not about MySQL but it shows some reasoning behind the page size choices.

mustaccio
  • 28,207
  • 24
  • 60
  • 76