7

MySQL InnoDB allows us to disable doublewrite buffering by setting innodb_doublewrite = 0. Other databases doesn't seem to allow this setting to be tweaked.

How could InnoDB still be able to maintain data integrity and ACID if we disable doublewrite buffering?

In what situations will it be safe to turn off InnoDB doublewrite buffer?

12

The only situation I can think of is reloading a large mysqldump. Why ?

Check out this Pictorial Representation of InnoDB (Percona CTO Vadim Tkachenko)

InnoDB Architecture

From the picture, you can see that the InnoDB Buffer Pool writes dirty pages to

  • Log Buffer
  • Insert Buffer in ibdata1
  • Double Write Buffer in ibdata1
  • .ibd file for each InnoDB table

Shutting off the Double Write Buffer will let a mysqldump write data and index pages in the tables faster since it does not have to write the same 16K pages to ibdata1.

Production Servers should never have the Double Write Buffer disabled. If you do so for loading data faster (during maintenance of course), enable it immediately after reloading the DB Server.

In other words,

  • Add innodb_doublewrite = 0 to my.cnf
  • Run SET GLOBAL innodb_fast_shutdown = 0;
  • Restart mysql
  • Load mysqldump
  • Remove innodb_doublewrite = 0 from my.cnf
  • Run SET GLOBAL innodb_fast_shutdown = 0;
  • Restart mysql
8

This issue was well dealt with in this post by Yves Trudeau who seems to suggest that it is safe - his conclusion is that

Conclusion

Like ZFS, ext4 can be transactional and replacing the InnoDB double write buffer with the file system transaction journal yield a 55% increase in performance for write intensive workload. Performance gains are also expected for SSD and mixed spinning/SSD configurations

He's basically saying that if you have a suitable file system, then yes, it can be safe.

Percona's people really know their stuff.

  • 2
    After this was posted, Trudeau added an update to his post: do not do this, this has been proven to corrupt data! – carla Jun 14 '17 at 13:50
  • Trudeau updated saying it's fine with ZFS: percona.com/blog/2015/06/17/… Another source: assets.en.oreilly.com/1/event/21/… – ItalyPaleAle Dec 15 '17 at 4:26
  • @Qualcuno - The message I'm getting is that journalling file systems are OK for disabling the double write buffer - let the FS/OS handle things - with battery backed cache. – Vérace Dec 15 '17 at 7:38
4

Updates on Yves Trudeau blog: https://www.percona.com/blog/2015/06/17/update-on-the-innodb-double-write-buffer-and-ext4-transactions/

In short, it's probably not safe.

The comments seems point out that - while it will survive a pull-the-plug test if the FS is ext4 with journal, or ZFS, it won't survive a simple kill (or OOM I suspect) because the FS won't reject the partial written data from app layer.

  • Not sure that I've grasped everything, but from the comments (near the end), it appears that if the commit size is 16kb or less, it appears reliable (search for "32K append16kb.txt" comment by Yves Trudeau. For bigger size commits, it appears unreliable (see comment with line " I see a distribution now" - but, since InnoDB's page size is 16kb, it appears not to be a risk. Yves Trudeau said he would check that out but hasn't posted anything since. Seems that the point is still open? :-) – Vérace Jun 26 '16 at 16:11
  • After this was posted, Trudeau added an update to his post: IMPORTANT: DON’T TRY THIS IN PRODUCTION. As demonstrated by Marko (see comments), it may corrupt your data. – carla Jun 14 '17 at 13:51
  • 1
    According to percona.com/blog/2015/06/17/… it's safe to do this with ZFS, not with EXT4 – ItalyPaleAle Dec 15 '17 at 4:26
  • 1
    The comment at the end by Trudeau is that "ZFS is entirely different than ext4, the ZIL acts as the doublewrite buffer so it is safe on ZFS to disable the double write buffer." - so, for some file systems, it's OK! – Vérace Jan 27 '18 at 13:57
0

My tentative list of situations where it is OK to turn off the doublewrite buffer:

  • FusionIO (or whatever name it goes by now) -- The drive handles it
  • Galera -- Rebuild the node if it crashes
  • Slaves -- Rebuild the slave if it crashes
  • ZFS -- The driver handles it

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.