When is it safe to disable InnoDB doublewrite buffering?

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

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.


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.