1

By MySQL pages i mean this :

https://dev.mysql.com/doc/internals/en/innodb-page-structure.html

and operating system pages :

https://en.wikipedia.org/wiki/Page_(computer_memory)

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?

4
  • 2
    These 2 terms are not related. The fact that the same word is used means nothing.
    – Akina
    Dec 14, 2018 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, 2018 at 7:56
  • 2
    so it doesn't affect anything Yes.
    – Akina
    Dec 14, 2018 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, 2018 at 8:20

2 Answers 2

1

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.)

1
  • page in os memory means part of virtual memory. It may reside on disk 'cause this block of virtual memory is not being used, so it's not loaded into main memory now;
  • page in mysql means part of mysql items of a table. It may reside on disk 'cause this block of items is not being used, so it's not loaded into main memory now;

They are different things(virtual memory vs. mysql items), thus one can not rely on the other.

They are similar because they use similar concepts:

  • use page to represent the resources they manage;
  • use similar algorithm(eg: LRU) to swap out page;

etc.

Indeed, despite of so called page, page cache or disk cache looks much more like mysql buffer pool. They are all caches for parts of disk files to accelerate data read and write. They both have to consider dirty page and write them back to disk when flushing data out of main memory.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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