What is the relation between MySQL pages and Operating system pages?

Question

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?

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

Answer 2

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