mysql分组提交和实时fsync

核心提示：Group commit and real fsync分组提交和实时fsyncDuring the recent months I’ve seen few cases of customers upgrading to MySQL 5.0 and having serious performance slo

Group commit and real fsync

分组提交和实时fsync

During the recent months I’ve seen few cases of customers upgrading to MySQL 5.0 and having serious performance slow downs, up to 10 times in certain cases. What was the most surprising for them is the problem was hardware and even OS specific - it could show up with one OS version but not in the other. Even more interesting performance may be dramatically affected by –log-bin settings, which usually has just couple of percent overhead. So what is going on?

最近这几个月，我已经碰到少数几个案例：一些客户升级到 MySQL 5.0，结果性能严重下降，某些特定情况下甚至达到10倍以下。然而令他们最为惊讶的是，产生这些问题竟然是由于硬件甚至是操作系统 -- 在某个版本的操作系统上存在这些问题但在其他版本则没有。更有趣的是，MySQL 的性能竟然戏剧性地受到 log-bin 设置的影响 -- 这通常只是对系统性能有 2% 的影响。那么，到底发生什么事了呢？

Actually we’re looking at two issues here which interleave such funny way

让我们来找找这2个有趣的问题中交叉的地方吧：

Group commit is broken in MySQL 5.0 if binary loging is enabled (as it enables XA) 在 MySQL 5.0 中如果启用二进制日志(binary log)(启用XA也是如此)，则分组提交中断了 Certain OS/Hardware configurations still fake fsync delivering great performance at the cost of being non ACID 某些操作系统/硬件配置仍旧只是实现了伪 fsync，由于它是 非ACID，结果导致大量的性能损失

First one can be tracked by this bug. In the nutshell the problem is - new feature - XA was implemented in MySQL 5.0 which did not work with former group commit code. The new code for group commit however was never implemented. XA allows to keep different transactonal storage engines in sync, together with binary log. XA is enabled if binary log is enabled this is why this issue is trigered by enabled binary log. if binary log is disabled, so is XA and old group commit code works just fine.