Bug #81803 Mysql 5.1 and later Update performance regression
Submitted: 10 Jun 2016 12:35 Modified: 15 Jun 2016 16:57
Reporter: ludovic halleux Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:>=5.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: innodb, UPDATE

[10 Jun 2016 12:35] ludovic halleux
Description:
When Migrating from 5.0 we discovered a speed regression in UPDATE statement.

Mysql 5.0 only updates rows when value set is different than stored.

From Mysql 5.1 each row gets updated even when the set value is the same than the stored value. 

Exepted if table contains a on update current timestamp field.

This problem has a massive performance impact on large tables. In our case up to 15x slower.

How to repeat:
Consider the table :

CREATE TABLE `test_handler_update` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` varchar(255) DEFAULT NULL,
  `flag` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

Poupulate it:
-- ----------------------------
-- Records of test_handler_update
-- ----------------------------
INSERT INTO `test_handler_update` VALUES ('1', '1', '1');
INSERT INTO `test_handler_update` VALUES ('2', '2', '1');
INSERT INTO `test_handler_update` VALUES ('3', '3', '1');
INSERT INTO `test_handler_update` VALUES ('4', '4', '1');
INSERT INTO `test_handler_update` VALUES ('5', '5', '1');
INSERT INTO `test_handler_update` VALUES ('6', '6', '1');
INSERT INTO `test_handler_update` VALUES ('7', '7', '1');
INSERT INTO `test_handler_update` VALUES ('8', '8', '1');
INSERT INTO `test_handler_update` VALUES ('9', '9', '1');
INSERT INTO `test_handler_update` VALUES ('10', '10', '1');

Now you can see the Handler_update variable status: 
FLUSH STATUS ;
UPDATE test_handler_update set flag=1 ;
Show SESSION status WHERE Variable_name = 'Handler_update'

Here is the results: 

5.0 : Handler_update	0
5.1 : Handler_update	10
5.7 :  Handler_update	10

----

This problem disappears when add a on update current_timestamp field to the table.

ALTER TABLE test_handler_update ADD date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; 

The results became :
5.0 : Handler_update	0
5.1 : Handler_update	0
5.7 :  Handler_update	0

Suggested fix:
Only write modified rows
[15 Jun 2016 16:02] Sinisa Milivojevic
I have managed to repeat the exact behavior that you reported. This is not a change for good, in my humble opinion, but I will check it out and will come back to you on this subject.
[15 Jun 2016 16:57] Sinisa Milivojevic
I do not consider that reasons for killing this optimization are good enough to outweigh the benefits.

Hence, I would like to see it fixed in on of the next versions.

Verified , fully !!!!!