Is Disabling InnoDB Doublewrite Buffering Safe with Innodb_page_size?

innodbMySQL

As I understand, doublewrite buffering is needed to avoid partial write, because Innodb_page_size default is 16K, while filesystem page size normallly is 4K; I wonder if I set "Innodb_page_size = filesystem page size", then I could disable InnoDB doublewrite buffering safely?

Best Answer

Background info... The "torn page" problem occurs when part of an InnoDB block is written to disk, but the physical write died before writing all the low level (usually 512-byte) blocks. This leads to an unreadable block for InnoDB. The double-write buffer, and its extra write, makes it possible to recover from a torn page.

The hardware needs to "guarantee" atomic writes of 16KB. FusionIO is the only drive that I know of to provide that guarantee. And that gives them a advantage by saying that you can turn off double-writes.

RAID controllers with a battery-backed-write-cache should be able to provide such a guarantee, but it is unclear whether the OS confuses the issue. But, then, writes are virtually free, so turning off double-write does not matter much.

I hesitate to say yes or no to your question because I don't know what is underneath the "filesystem". It may turn the InnoDB block into multiple 512-byte disk writes, thereby still allowing torn pages.

O_DIRECT is a related topic to look at.

Setting innodb_page_size to 4K has other issues (even if you could get the drive to play nice) --

  • Records are limited to about 2000 bytes instead of about 8000.
  • You are asking for more I/Os. This may be good or bad. Random point queries of small rows will work faster with 4K blocks; most other situations may work slower.