mysql> show variables like '%double%write%'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | innodb_doublewrite | ON | +--------------------+-------+ 1 row in set (0.00 sec) 什么情況下可以關閉 doublewrite 特性能 1、Fursion-io 原子寫，如果每次寫16k就是16k，每次寫都是16k不會出現部分寫partial write寫4k的情況。設備帶來的益處。
二double write的缺點是什么? 位于共享表空間上的double write buffer實際上也是一個文件，寫DWB會導致系統有更多的fsync操作, 而硬盤的fsync性能, 所以它會降低mysql的整體性能. 但是并不會降低到原來的50%. 這主要是因為: 1) double write 是一個連接的存儲空間, 所以硬盤在寫數據的時候是順序寫, 而不是隨機寫, 這樣性能更高. 2) 將數據從double write buffer寫到真正的segment中的時候, 系統會自動合并連接空間刷新的方式, 每次可以刷新多個pages; 三 double write在恢復的時候是如何工作的? If there’s a partial page write to the doublewrite buffer itself, the original page will still be on disk in its real location.- --如果是寫doublewrite buffer本身失敗,那么這些數據不會被寫到磁盤,InnoDB此時會從磁盤載入原始的數據,然后通過InnoDB的事務日志來計算出正確的數據,重新 寫入到doublewrite buffer. When InnoDB recovers, it will use the original page instead of the corrupted copy in the doublewrite buffer. However, if the doublewrite buffer succeeds and the write to the page’s real location fails, InnoDB will use the copy in the doublewrite buffer during recovery. --如果 doublewrite buffer寫成功的話,但是寫磁盤失敗,InnoDB就不用通過事務日志來計算了,而是直接用buffer的數據再寫一遍. InnoDB knows when a page is corrupt because each page has a checksum at the end; the checksum is the last thing to be written, so if the page’s contents don’t match the checksum, the page is corrupt. Upon recovery, therefore, InnoDB just reads each page in the doublewrite buffer and verifies the checksums. If a page’s checksum is incorrect, it reads the page from its original location. --在恢復的時候,InnoDB直接比較頁面的checksum,如果不對的話,就從硬盤載入原始數據,再由事務日志 開始推演出正確的數據.所以InnoDB的恢復通常需要較長的時間. 四 我們是否一定需要 double write ？ In some cases, the doublewrite buffer really isn’t necessary—for example, you might want to disable it on slaves. Also, some filesystems (such as ZFS) do the same thing themselves, so it is redundant for InnoDB to do it. You can disable the doublewrite buffer by setting InnoDB_doublewrite to 0.
五 如何使用 double write InnoDB_doublewrite=1表示啟動double write show status like 'InnoDB_dblwr%'可以查詢double write的使用情況; 相關參數與狀態 Double write的使用情況： show status like "%InnoDB_dblwr%"; InnoDB_dblwr_pages_written 從bp flush 到 DBWB的個數 InnoDB_dblwr_writes 寫文件的次數 每次寫操作合并page的個數= InnoDB_dblwr_pages_written/InnoDB_dblwr_writes 。
