How InnoDB confirms maximum transaction durability in MySQL 8 ?

In transaction processing systems we have to guarantee the transaction committed will be durable permanently. For example, In an online commerce platform, The completed transaction will remain safe even if the system crashes. This can be achieved by flushing the transactional log records to non-volatile storage devices before acknowledging the commit. MySQL guarantees maximum durability of transaction by optimally setting following system variables :

innodb_doublewrite (enabled by default) 

InnoDB stores all data twice, first to doublewrite buffer (storage area in system tablespace to write pages that are flushed from InnoDB buffer pool, before written in data file). If ever operating system / storage / mysqld process crash during the middle of page write, InnoDB can still find a durable copy of the page from doublewrite buffer for recovery. Though data is written twice,  the doublewrite buffer does not require twice as much I/O overhead or twice as many I/O operations,  . Because, Data is written to the doublewrite buffer itself as a large sequential chunk, with a single fsync() call to the operating system. If system tablespace data file (ibdata* files) are located on Fusion-io devices that support atomic writes, doublewrite buffering is automatically disabled and Fusion-io atomic writes are used for all data files, set innodb_flush_method system variable to O_DIRECT to gain maximum advantage of this feature.

innodb_flush_log_at_trx_commit (default value is “1”)

innodb_flush_log_trx_commit guarantees ultimate durability, The default value “1” writes the contents of InnoDB log buffer to the log file with each transaction commit and the log file is flushed to disk. If value is “0”, No writes from log buffer to log file will be performed at transaction commit, The contents of the InnoDB log buffer are written to log file once per second and the log file is flushed to disk.  Because the flush to disk operation only occurs approximately once per second, you can lose up to a second of transactions with any mysqld process crash. The value “2” flushes contents of InnoDB log buffer to the log file after each transaction commit and log file is flushed to disk approximately once per second.  Once-per-second flushing is not guaranteed to happen every second due to process scheduling issues. Regardless of innodb_flush_log_at_trx_commit  setting, Transaction will be either rolled back or committed. We recommend you to retain default value “1” for “innodb_flush_log_at_trx_commit” for maximum transaction durability.

innodb_flush_log_at_timeout  (default value is “1”)

innodb_flush_log_at_timeout  system variable setting allows you to set log flushing frequency to N seconds (N is between 1 & 2700, the default value is 1 second), However, any mysqld process crash can erase up to N seconds of transactions. The DDL changes and other internal InnoDB activities flush the InnoDB log independently of the innodb_flush_log_at_trx_commit setting, DDL logs are always flushed at transaction commit.

sync_binlog (default value is “1”)

When sync_binlog=1, all transaction are synchronized to the binary log before they are committed. Therefore, even in the event of an unexpected restart, any transactions that are missing from the binary log are only in prepared state. This causes the server’s automatic recovery routine to roll back those transactions. This guarantees that no transaction is lost from the binary log, and is the safest option. However this can have a negative impact on performance because of an increased number of disk writes. Using a higher value improves performance, but with the increased risk of data loss. If sync_binlog=0 or greater than 1, transactions are committed without having been synchronized to disk. Therefore, in the event of a power failure or operating system crash, it is possible that the server has committed some transactions that have not been synchronized to the binary log. Therefore it is impossible for the recovery routine to recover these transactions and they will be lost from the binary log.

 

About MinervaDB Corporation 88 Articles
Independent and vendor neutral consulting, support, remote DBA services and training for MySQL, MariaDB, Percona Server, PostgreSQL and ClickHouse with core expertize in performance, scalability and high availability . We are an virtual corporation, all of us work from home on multiple timezones and stay connected via Email, Skype, Google Hangouts, Phone and IRC supporting over 250 customers worldwide
UA-155183614-1