Thursday, December 12, 2013

Can your data survive system crash? -- MyISAM vs InnoDB with MySQL

The MySQL's website says that: The MySQL updates the files on disk with the write() system call after every SQL statement and before the client is notified about the result.  This means that data file contents are safe even if mysqld crashes, because the operating system ensures that the unflushed data is written to disk. You can force MySQL to flush everything to disk after every SQL statement by starting mysqld with the --flush option.

However, the flush option is off by default. Turning it on can increase the disk I/O operations thus impact the performance. When using the MyISAM engine with the --flush option off, a system crash (such as power cut off or hardware failure) can cause the permanent loss of the unflushed data.

It is a different story with the InnoDB engine. (Quoted:) InnoDB must flush the log to disk at each transaction commit if that transaction made modifications to the database, although you can change the innodb_flush_log_at_trx_commit parameter to other values to change this behavior. Therefore, upon a system crash, InnoDB can recover by replaying the logs. And the beauty is that it is done automatically. (Quoted:) To recover from a crash of your MySQL server, the only requirement is to restart it. InnoDB automatically checks the logs and performs a roll-forward of the database to the present. InnoDB automatically rolls back uncommitted transactions that were present at the time of the crash.

No comments:

Get This <