By MySQL pages i mean this :


and operating system pages :


I understand both of them, but i don't understand their relation, so DBMS is basically a program that runs on top of our O.S correct? so why are DBMSes like MySQL don't just use the page size of the operating system and instead let the DBA decide what should the page size be?

what happens when the O.S page size is 8KB but DBMS is 16KB? and what if its vice versa?

Does a difference between these two affect performance or anything else?

  • 1
    These 2 terms are not related. The fact that the same word is used means nothing. – Akina Dec 14 '18 at 7:34
  • @Akina so it doesn't affect anything if the database page size is the same as the O.S page size vs when they are not the same? for example performance? – John P Dec 14 '18 at 7:56
  • 1
    so it doesn't affect anything Yes. – Akina Dec 14 '18 at 8:03
  • That’s actually more complex than that - there’s also disk block size to take into account. It impacts performance but also consistency (page tears etc) It’s usually hard to optimize db workload => os page size => disk blocks. So they are left default usually – cohenjo Dec 14 '18 at 8:20

The OS decided that XKB (often 4KB today) was a good size. (In the old days, it was 1KB, or even less. Note that disks are frozen in 512-byte units!)

InnoDB decided that 16KB was a good size. InnoDB also implements tablespaces (eg ibdata1 or .ibd files) as files, not as raw disk partitions. So, InnoDB is at the mercy of what the OS is going to do when asked to "in file y.ibd, seek to offset z, and read 16KB bytes. (Side note: InnoDB can be configured to use 4/8/16/32/64KB, but there are disadvantages, and I have yet to hear of anyone actually using such.)

One can hope, but not necessarily control, that a tablespace assigned at some disk address that is a multiple of 16KB, or even that it is aligned with the pages that the OS sees. But, the OS will deliver the 16KB, regardless of the details of where the piece(s) are.

InnoDB takes pains to avoid the "torn page" problem. This is where the system crashes in the middle of writing, say, the four 4KB blocks for the one InnoDB block. Such a half-written block would have the effect of destroying the data integrity for that table. To compensate, InnoDB has a "double-write" buffer in which it effectively writes some things twice. Upon recovery, it will be able to discover and fix the "torn page" if necessary.

Since the OS might choose to scatter its pieces of InnoDB's 16KB, there is a potential performance problem with extra head seeks (on HDD). For SSD, this does not matter much, if at all.

Hardware RAID controllers with "Battery Backed Write Cache" are an excellent way to both speed up write (making them virtually instantaneous without risk of loss) and avoid the scattering problem, even for HDDs.

Now enter some disk manufacturers. To be "the best" in the MySQL market, their disk (SSDs) can be configured such that 16KB is the "atomic" write size. With that kind of disk, InnoDB can be configured to turn off the double-write, hence run faster.

O_DIRECT is usually optimal for InnoDB because it avoids a copy of the 16KB to/from an OS buffer. But this depends on the details of the disk driver.

innodb_flush_neighbors/innodb_flush_neighbor_pages is a setting to flush adjacent pages at the same time, thereby avoiding a disk seek; it has some performance benefit for HDDs. It may have a negative impact on SDDs.

(Sorry for the TMI, sometimes my fingers just keep going.)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.