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.