Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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
.ibd file for each InnoDB tableShutting 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,
innodb_doublewrite = 0 to my.cnfSET GLOBAL innodb_fast_shutdown = 0;innodb_doublewrite = 0 from my.cnfSET GLOBAL innodb_fast_shutdown = 0;
SET GLOBAL innodb_fast_shutdown = 0; in the 2nd to last bullet point? Shouldn't that be setting it back to 1?
Dec 6, 2019 at 21:00
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)
Dec 6, 2019 at 21:11
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.
My tentative list of situations where it is OK to turn off the doublewrite buffer:
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.
