my update statement is
update ptest set amount = amount - 2000 where id = 2
table ptest is
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;
when system variable log_bin was set to: log_bin=mysql_bin, jmeter test result is 237.4 transaction/second . when log_bin is comment out #log_bin=mysql_bin, jmeter test result is 3500.2 transaction/second .
on both setting the insert rate is similar, about 8000 transaction/second.
why log_bin has terrible performance impact on mysql? how can I improve update performance when log_bin is turn on?
Short answer is no. You can't improve the performance while binary logging is on. I think this is one of the biggest tradeoffs in MySQL. The long answer comes from an article by Baron Schwartz, the lead author of the MySQL performance blog:
You can read the full article here.