what pros/cons of storing binary log in an InnoDB table?
Zardosht Kasheff <[email protected]> Sun, 27 Jan 2013 20:34:45 -0500
| Newsgroups | gmane.comp.db.mysql.devel |
|---|---|
| Message-ID | <CABFd+SFhHFSCbKh5tj5r8pVcfj1rQcbBtR5J3R1i5CbJH2LsnQ@mail.gmail.com> |
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 -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/internals