11

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?

Improve this question
0

5 Answers 5

Reset to default
14

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
Improve this answer
2
  • Did you mean to say SET GLOBAL innodb_fast_shutdown = 0; in the 2nd to last bullet point? Shouldn't that be setting it back to 1?
    – Tyler Christian
    Dec 6, 2019 at 21:00
  • 1
    @TylerChristian No, it must be 0 twice. When mysqld comes back up, it will be back at the default value of 1 (Unless innodb_fast_shutdown=0 is in my.cnf)
    – RolandoMySQLDBA
    Dec 6, 2019 at 21:11
9

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.

Improve this answer
3
  • 7
    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, 2017 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, 2017 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, 2017 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.

Improve this answer
4
  • 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, 2016 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, 2017 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, 2017 at 4:26
  • 2
    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, 2018 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
Improve this answer
0

When the writes are single sector, and OS guarantees that single sector writes are atomic.

At the time being, it is just Windows with innodb-page-size=4K, and 4K sector size disk. Nothing definitive was ever said about Linux single sector writes being atomic, although I presume it could be, in some circumstances, and depend on the file system.

Improve this answer

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.