InnoDB - how MySQL InnoDB pages works with underlying OS file system

Question

I've studied and summarized how MySQL InnoDB pages works with underlying OS file system when accessing secondary storage.

Would you mind to confirm that my understanding is correct?

• InnoDB store table data (including indexes) on tablespace
• Each tablespace is stored to each file in .ibd data file
• A tablespace contains multiple pages (I called the page as DB page from now on)
• Default DB page size is 16KB
• A DB page loaded from secondary storage is in buffer pool
• A DB page is data transfer unit between buffer pool and secondary storage
• In terms of OS memory management, if memory page size is 8KB, then each DB page would be stored in 2 memory pages
• Whenever InnoDB reads/writes a DB page from/to secondary storage, the DB page which is composed of 2 memory pages are divided by OS file system block size and transfer based on the blocks
• So, if file system block size is 4KB, then DB page are divided into 4 blocks and transferred from/to secondary storage
• If secondary storage is HDD and its sector size is 512B, then the DB page is stored to/from 32 sectors
• If secondary storage is SSD and its page size is 4KB, then the DB page is stored to/from 4 SSD pages (of course SSD write is more complicated because it has erase but overwrite)
• In all the progress, DB page would or would not be stored in contiguous OS pages or sectors or SSD pages physically. Both cases are possible, and we don't know which case occurs for each specific situation
• When searching some record with index, it starts from root DB page(= B+ tree root node) and find next child DB page and loading it from secondary storage, then move to the child page and going again with same step until finding target key in a leaf DB page which has pointer to data DB page
• If find, then finally read the data DB page from secondary storage and extract target data from the page and return it

Is my understanding correct?

Answer 1

What you say is good. Here are some details, mostly minor:

TABLESPACE comes in multiple flavors:

• The system tablespace, ibdata1. This always exists; it optionally contains some or all of the user's tables.
• innodb_file_per_table - A tablespace (.ibd file) for each table.
• User-defined tablespaces. (such as, a tablespace for each database.)
• ibtmp1 -- for undos? for temp tables? (Deprecated in 8.0.14?)

A DB_page is 16KB -- This can be set to other values, but only system-wide. Essentially no one makes this configuration change.

Different OSs have different "OS page" sizes; that is "8KB" is not universal. (Your 5 bullet items on this topic could be simplified to 1.) Remember CDROMs? They had 2KB. Or DOS's FAT-16 with up to 32KB! CPM had a sector size of 128.

Some disk subsystems will guarantee the atomicity of 16KB chunks -- this lets you avoid the "double-write-buffer" that is a small overhead for InnoDB to achieve ACID.

Each 16KB DB_page may be scattered around the disk -- This matters for HDD, but not for SSD. Some 'tricks' are performed bo try to avoid scattering. One thing is that InnoDB allocates disk space in 8MB (or is it 4MB?) "extents" and hopes that they are close. Then it is willing to "write "neighboring" 'dirty' pages at the same time. (Again, no benefit for SSD.)

Another point: BTree block splits. When a row is inserted in the middle of the table (based on the PRIMARY KEY) or the middle of an index BTree (based on the key), the 16KB block may need to be split into two blocks. Again, SSDs don't care where the new block goes; HDDs hope that the new block can be allocated in the same "extent".

Yes, an index leaf page has pointers to the data pages. Of note is that the "pointer" is the column(s) of the PRIMARY KEY. That leads to a second drill-down in a second BTree when looking up a row via a secondary index.

If all the columns needed for a SELECT are included in the secondary index plus the PK, then the index is "covering" and that second lookup is not needed.

The + in B+Tree means that the logically consecutive DB_pages are directly connected. This makes "range scans" (based on either the PK or a secondary index) slightly more efficient.