StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>To begin with...</p> <p><a href="http://yoshinorimatsunobu.blogspot.com/2009/08/great-performance-effect-of-fixing.html" rel="nofollow">http://yoshinorimatsunobu.blogspot.com/2009/08/great-performance-effect-of-fixing.html</a></p> <p>Before InnoDB Plugin 1.0.4, it was like:</p> <pre><code>obtain mutex write innodb log and fsync, for prepare-phase (skip if innodb_support_xa=0) write binlog (fsync as appropriate if sync_binlog > 0) write innodb log and fsync, for commit-phase release mutex </code></pre> <p>On and after InnoDB Plugin 1.0.4 (and MySQL 5.5), it is now:</p> <pre><code>write innodb log and fsync, for prepare-phase (skip if innodb_support_xa=0) obtain mutex write binlog (fsync as appropriate if sync_binlog > 0) write innodb log, for commit-phase release mutex fsync innodb log, for commit-phase </code></pre> <p>As you can see, in the new version, nothing (except in case <code>sync_binlog</code> > 0) is fsync'd in the critical section. That way, group commit now works and ensures far better concurrent throughput.</p> <p>For instance, with the previous "broken" version, if you had 100 threads concurrent commits, all fsyncs were serialized and you would get 100 fsyncs for prepare and another 100 fsyncs for commit. Therefore group commit was completely broken.</p> <p>Now with the newer implementation, fsyncs are grouped depending on the concurrency of transactions, while ensuring operation ordering between innodb log and binlog. It also means that if there's only one thread, there's no performance gain.</p> <p>As to your question that, when crash occurs after a transaction is written to the binlog, but before it's written to the transaction log - I'm on the same page as you.</p> <p>If the server crashed before the final step, there's a slight chance that you have a discrepancy between innodb log and binlog (either one could be ahead of the other), but it is guaranteed that <strong>you have all the information on what to examine</strong> in the innodb log, as it is recorded in the prepare-phase.</p> <p>However, what to do with the uncommitted is still nondeterministic. For instance, unless <code>sync_binlog = 1</code> there's a chance that a slave received the data but not fully fsync'd the binlog on the master yet. You can't just redo the failed transaction as it may have already run on one of the slaves.</p> <p>Which also means, the binlog could be shorter than the innodb log, returning "The binary log [file_name] is shorter than its expected size." as described in the official doc, and you have to rebuild the slave from scratch. Not very human friendly.</p> <p><a href="http://dev.mysql.com/doc/refman/5.1/en/binary-log.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.1/en/binary-log.html</a></p> <p>As consistency in terms of operation ordering is guaranteed independent of the <code>innodb_support_xa</code> setting (which contradicts what's said in the official doc on <code>innodb_support_xa</code>, maybe because it was written about the stock innodb 5.0.3 far before the concurrency fix), and consistency between innodb log on the master and relay log on the slave is not strictly guaranteed even with <code>innodb_support_xa</code>, I don't see any point in using <code>innodb_support_xa</code>. It's scary to not follow the official recommendation, though, however it seems stale and wrong in many points.</p> <p>I'm wondering if there's any correlation between the <code>innodb_flush_log_at_trx_commit</code> setting and the <code>innodb_support_xa</code> behavior when the former is set to 2 or 0.</p> <p>One practical way of thinking is that, failover to the slave is safe - after all, the failed transaction was something that you wanted to get done - but never fail-back to master, as there might be some discrepancy in the data. You need to fully copy the data from the slave, before you make the master a new slave. In other words, when the master crashed, trust the slave from then on - that way, you don't need to mess with innodb log for crash recovery.</p> <p>Also note that MySQL 5.5 supports semi-synchronous replication, along the same line as "trust the slave" - thought you might be interested.</p> <p><a href="http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload