当log_bin打开时，mysql 5.6.21 update语句速度非常慢

Question

我的更新语句是

update ptest set amount = amount - 2000 where id = 2

表ptest为

CREATE TABLE `ptest` (
  `id` bigint(19) NOT NULL AUTO_INCREMENT,
  `developerId` bigint(19) DEFAULT NULL,
  `appId` bigint(19) DEFAULT NULL,
  `caller` varchar(20) DEFAULT NULL,
  `callerDisplay` varchar(20) DEFAULT NULL,
  `called` varchar(20) DEFAULT NULL,
  `calledDisplay` varchar(20) DEFAULT NULL,
  `startTime` datetime DEFAULT NULL,
  `endTime` datetime DEFAULT NULL,
  `callTime` int(11) DEFAULT NULL,
  `callId` varchar(32) NOT NULL ,
  `billingTime` int(11) DEFAULT NULL,
  `callResult` varchar(10) DEFAULT NULL,
  `amount` bigint(20) DEFAULT NULL ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=200001 DEFAULT CHARSET=utf8;

当系统变量log_bin设置为: log_bin=mysql_bin时，jmeter测试结果为237.4事务/秒。当log_bin注释掉#log_bin=mysql_bin时，jmeter测试结果是3500.2事务/秒。

在这两个设置上，插入速率是相似的，大约是8000事务/秒。

为什么log_bin对mysql的性能影响很大？如何在打开log_bin时提高更新性能？

Answer 1

简短的回答是否定的。当二进制日志记录处于打开状态时，您无法提高性能。我认为这是MySQL中最大的权衡之一。这个长长的答案来自MySQL performance博客的主要作者Baron Schwartz的一篇文章：

启用二进制日志会显著降低

的性能。日志本身并不是问题所在--写入日志通常不会有太多额外的工作。它确保了一致性和持久性，这是昂贵的。将其刷新到磁盘会为每个事务添加一个fsync调用。并且服务器在InnoDB和二进制日志之间执行XA事务。这增加了更多的fsync调用，并导致互斥争用，并阻止组提交，以及现在可能没有想到的其他事情。

您可以阅读full article here。