MySQL – When to Disable InnoDB Doublewrite Buffering

data integrityinnodblockingMySQLtransaction-log

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?

Best Answer

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
Related Question