From: Zardosht Kasheff Date: January 28 2013 1:34am Subject: what pros/cons of storing binary log in an InnoDB table? List-Archive: http://lists.mysql.com/internals/38696 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Hello all, What do people think are the pros and cons of the following idea? Instead of having the binary log be stored as a log file, a table with a transactional engine (like InnoDB) is used. I have NOT done any investigation into whether this is possible, but I'm wondering what people abstractly think of this idea. Here are some pros: - As I understand it, for two phase commit, storage engines are currently expected to fsync once after a transaction is prepared and once after a transaction is committed. If the binary log is an InnoDB table, then two-phase commit is unnecessary and the number of fsyncs on the system is cut in half. - With crash safe slaves on MySQL 5.6, one theoretically does not need to enable fsyncs on transaction commit on slaves. If some data gets lost after a crash, then the data may be replayed from the master. However, to get GTIDs working, one must enable the binary log on slaves, which requires two-phase commit, which require fsyncs. By having the binary log be an InnoDB table, then two-phase commit is not needed and fsyncs may be turned off on slaves. Here are some cons: - I imagine writing to an InnoDB table is slower than writing to a log. My major motivation is to find a way to have users not be forced to fsync on every transaction commit on slaves. With crash safety of slaves in 5.6, this seems possible as long as two-phase commit is not needed. However, with GTIDs requiring binary logging to be enabled, users are now forced to fsync. Thanks -Zardosht