1--echo # 2--echo # Bug #84958 InnoDB's MVCC has O(N^2) behaviors 3--echo # https://bugs.mysql.com/bug.php?id=84958 4--echo # 5--echo # Set up the test with a procedure and a function. 6--echo # 7 8--source include/have_innodb.inc 9SET @saved_frequency= @@GLOBAL.innodb_purge_rseg_truncate_frequency; 10SET GLOBAL innodb_purge_rseg_truncate_frequency= 1; 11 12DELIMITER ~~; 13CREATE PROCEDURE insert_n(start int, end int) 14BEGIN 15 DECLARE i INT DEFAULT start; 16 START TRANSACTION; 17 WHILE i <= end do 18 INSERT INTO t1 VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE c = i; 19 SET i = i + 1; 20 END WHILE; 21 COMMIT; 22END~~ 23 24CREATE FUNCTION num_pages_get() 25RETURNS INT 26BEGIN 27 DECLARE ret INT; 28 SELECT variable_value INTO ret 29 FROM information_schema.global_status 30 WHERE variable_name = 'innodb_buffer_pool_read_requests'; 31 RETURN ret; 32END~~ 33DELIMITER ;~~ 34 35--echo # 36--echo # Create a table with one record in it and start an RR transaction 37--echo # 38CREATE TABLE t1 (a INT, b INT, c INT, PRIMARY KEY(a,b), KEY (b,c)) 39ENGINE=InnoDB; 40BEGIN; 41SELECT * FROM t1; 42 43--echo # 44--echo # Create 100 newer record versions in con2 and con3 45--echo # 46connect (con2, localhost, root,,); 47connection con2; 48INSERT INTO t1 VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE c = NULL; 49--send CALL insert_n(1, 50); 50 51connect (con3, localhost, root,,); 52connection con3; 53--send CALL insert_n(51, 100); 54 55connection con2; 56reap; 57connection con3; 58reap; 59INSERT INTO t1 VALUES (1, 2, 1) ON DUPLICATE KEY UPDATE c = NULL; 60 61connection default; 62 63--echo # 64--echo # Connect to default and record how many pages were accessed 65--echo # when selecting the record using the secondary key. 66--echo # 67--let $wait_all_purged=4 68--source include/wait_all_purged.inc 69SET @num_pages_1 = num_pages_get(); 70SELECT * FROM t1 force index (b); 71SET @num_pages_2= num_pages_get(); 72 73SELECT IF(@num_pages_2 - @num_pages_1 < 5000, 'OK', @num_pages_2 - @num_pages_1) num_pages_diff; 74 75--echo # 76--echo # Commit and show the final record. 77--echo # 78SELECT * FROM t1; 79SELECT * FROM t1 force index (b); 80COMMIT; 81SELECT * FROM t1 force index (b); 82SELECT * FROM t1; 83CHECK TABLE t1; 84 85--echo # 86--echo # Cleanup 87--echo # 88disconnect con2; 89disconnect con3; 90SET GLOBAL innodb_purge_rseg_truncate_frequency= @saved_frequency; 91DROP TABLE t1; 92DROP PROCEDURE insert_n; 93DROP FUNCTION num_pages_get; 94