| Bug #79786 | unnecessarily generate undo logs while update nothing | ||
|---|---|---|---|
| Submitted: | 28 Dec 2015 14:29 | Modified: | 8 Jan 2016 11:37 |
| Reporter: | zhai weixiang (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.7,5.6, 5.7.10 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[8 Jan 2016 11:37]
MySQL Verification Team
Hello Zhai, Thank you for the report and test case. Observed this with 5.7.10 build. Thanks, Umesh
[8 Jan 2016 11:37]
MySQL Verification Team
// 5.7.10
[root@cluster-repo jemalloc-4.0.3]# mysql -uroot -p test
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11026
Server version: 5.7.10-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE `t1` (
-> `a` int(11) NOT NULL,
-> `b` int(11) DEFAULT NULL,
-> PRIMARY KEY (`a`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1 values(1,2),(2,3);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+---+------+
| a | b |
+---+------+
| 1 | 2 |
| 2 | 3 |
+---+------+
2 rows in set (0.00 sec)
mysql> set session binlog_row_image = FULL;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t1 set b = 2 where a =1;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> pager grep 'undo log entries'
PAGER set to 'grep 'undo log entries''
mysql> show engine innodb status\G
1 row in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> set session binlog_row_image = minimal;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t1 set b = 2 where a =1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> show engine innodb status\G
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
1 row in set (0.00 sec)
mysql> update t1 set b = 2 where a =1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> show engine innodb status\G
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
1 row in set (0.00 sec)
mysql> update t1 set b = 2 where a =1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> show engine innodb status\G
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 3
1 row in set (0.00 sec)
// restart mysqld iwth log_bin truned off
[root@cluster-repo jemalloc-4.0.3]# mysql -uroot -p test
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.10 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0.00 sec)
mysql> select * from t1;
+---+------+
| a | b |
+---+------+
| 1 | 2 |
| 2 | 3 |
+---+------+
2 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t1 set b = 2 where a =1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> pager grep 'undo log entries'
PAGER set to 'grep 'undo log entries''
mysql> show engine innodb status\G
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
1 row in set (0.00 sec)
mysql> update t1 set b = 2 where a =1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> show engine innodb status\G
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
1 row in set (0.00 sec)
Description: If binlog is disabled or binlog_row_image is set to MINIMAL, it will generate undo logs for statements that changes nothing. How to repeat: mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> select * from t1; +---+------+ | a | b | +---+------+ | 1 | 2 | | 2 | 3 | +---+------+ 2 rows in set (0.00 sec) mysql> set session binlog_row_image = FULL; Query OK, 0 rows affected (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update t1 set b = 2 where a =1; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> pager grep 'undo log entries' PAGER set to 'grep 'undo log entries'' mysql> show engine innodb status\G 1 row in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> set session binlog_row_image = minimal; Query OK, 0 rows affected (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update t1 set b = 2 where a =1; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> show engine innodb status\G 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1 1 row in set (0.00 sec) mysql> update t1 set b = 2 where a =1; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> show engine innodb status\G 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2 1 row in set (0.00 sec) mysql> update t1 set b = 2 where a =1; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> show engine innodb status\G 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 3 1 row in set (0.00 sec) disable binary log mysql> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | OFF | +---------------+-------+ 1 row in set (0.00 sec) mysql> select * from t1; +---+------+ | a | b | +---+------+ | 1 | 2 | | 2 | 3 | +---+------+ 2 rows in set (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update t1 set b = 2 where a =1; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> pager grep 'undo log entries' PAGER set to 'grep 'undo log entries'' mysql> show engine innodb status\G 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1 1 row in set (0.00 sec) mysql> update t1 set b = 2 where a =1; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> show engine innodb status\G 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2 1 row in set (0.00 sec) Suggested fix: From the case above, if table->write_set is not a subset of table->read_set, it will skip comparing records and always run into engine layer to try to update something. Since I am not an expert of server layer, just file the bug to confirm if this is a designed behaviour or not ?