Description:
When using InnoDB online-DDL to add an index the history-list length will grow uncontrollably. This appears to be because the online-DDL code uses a single read view for the entire operation. It would be more efficient for garbage collection if the online-DDL code used multiple read views, one for each chunk or N chunks of rows that it processes.
How to repeat:
Setup:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
id INT NOT NULL PRIMARY KEY auto_increment,
b VARCHAR(60) NOT NULL,
c VARCHAR(60) NOT NULL,
INDEX (b)
);
INSERT INTO t1 SELECT NULL, 'aaa', 'bbb' FROM dual;
INSERT INTO t1 SELECT NULL, 'aaa', 'bbb' FROM t1 a JOIN t1 b JOIN t1 c;
INSERT INTO t1 SELECT NULL, 'aaa', 'bbb' FROM t1 a JOIN t1 b JOIN t1 c;
INSERT INTO t1 SELECT NULL, 'aaa', 'bbb' FROM t1 a JOIN t1 b JOIN t1 c;
INSERT INTO t1 SELECT NULL, 'aaa', 'bbb' FROM t1 a JOIN t1 b JOIN t1 c LIMIT 30000000;
Then start a script which intentionally adds to the history and another script to monitor history:
# session 1
while true; do mysql -e "update test.t1 SET b=HEX(RANDOM_BYTES(30)) WHERE id = 10000000" ; done
# session 2
while true; do mysql -e "SHOW ENGINE INNODB STATUS\G" | grep "History"; sleep 1; done
In a third session, add an index:
mysql -e "ALTER TABLE test.t1 ADD INDEX (b)"
You should see that the history bounces around slightly before the index add. But once the index is added it only ever grows:
History list length 58
History list length 67
History list length 56
History list length 35
History list length 30
History list length 45
History list length 52
History list length 60
History list length 28
History list length 43
History list length 43
History list length 64
History list length 7
History list length 81
History list length 68
History list length 10
History list length 29
History list length 47
History list length 61
History list length 63
History list length 66
History list length 52
History list length 51
History list length 53
History list length 74
History list length 65
History list length 67
History list length 53
History list length 48
History list length 51
History list length 4
History list length 7
History list length 12
History list length 19
History list length 31
History list length 47
History list length 57
History list length 62
History list length 62
History list length 36
History list length 19
History list length 27
History list length 97
History list length 66
History list length 73
History list length 1
History list length 0
History list length 75
History list length 5
History list length 6
History list length 3
History list length 75
History list length 58
History list length 34
History list length 37
History list length 6
History list length 5
History list length 79
History list length 12
History list length 25
History list length 27
History list length 36
History list length 16
History list length 15
History list length 14
History list length 88
History list length 159
History list length 222
History list length 294
History list length 368
History list length 442
History list length 514
History list length 587
History list length 658
History list length 730
History list length 806
History list length 881
History list length 955
History list length 1029
History list length 1103
History list length 1178
History list length 1253
History list length 1327
History list length 1401
History list length 1475
History list length 1548
History list length 1620
History list length 1688
History list length 1763
History list length 1837
History list length 1915
History list length 1992
History list length 2068
History list length 2142
History list length 2198
History list length 31
History list length 24
History list length 8
History list length 22
History list length 16
History list length 19
History list length 20
History list length 21
History list length 25
History list length 44
History list length 3
As soon as the index completes, the history cleans up immediately.
Suggested fix:
Online DDL has to keep track of changes to apply over the top, so in no way does it require a single read view. It can instead use multiple read views allowing purge to cleanup older versions and keep the system healthier while DDL is being applied.
In practice, this prevents the use of online DDL for moderately sized tables on busy systems (irrespective of that it blocks replication).
