log_bin打开时mysql 5.6.21更新语句非常慢

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 性能博客的主要作者 Baron Schwartz 的一篇文章:

Enabling the binary log reduces MySQL’s performance dramatically. It is not the logging itself that’s the problem — writing the log is usually not much additional work. It’s ensuring consistency and durability that is expensive. Flushing it to disk adds an fsync call for every transaction. And the server performs an XA transaction between InnoDB and the binary log. This adds more fsync calls, and causes mutex contention, and prevents group commit, and probably other things that aren’t coming to mind now.

您可以阅读full article here 。