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)
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
tomy.cnf
- Run
SET GLOBAL innodb_fast_shutdown = 0;
- Restart mysql
- Load mysqldump
- Remove
innodb_doublewrite = 0
frommy.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.