Description:
A new test innodb.fts_exec_interrupt was added in the latest 5.7 release which takes 22 minutes to execute. The test does 600220 INSERTs with autocommit=1 (that is - implicit commit after each INSERT).
How to repeat:
./mtr --big-test innodb.fts_exec_interrupt
Suggested fix:
commit 89a84b3c3c2
Parent: cd6c8ec8ceb
Author: Vasil Dimov <vasil.dimov@galeracluster.com>
AuthorDate: Mon Aug 20 18:41:21 2018 +0200
Commit: Daniele Sciascia <daniele.sciascia@galeracluster.com>
CommitDate: Tue Aug 21 17:30:54 2018 +0200
Speed up innodb.fts_exec_interrupt
* Do not (implicitly) commit after each INSERT
* Instead of 600k INSERTs use 19 INSERT INTO ... SELECT ...
This speeds up the test from about 22 minutes to about 2 minutes.
diff --git a/mysql-test/suite/innodb/r/fts_exec_interrupt.result b/mysql-test/suite/innodb/r/fts_exec_interrupt.result
index cf4968ea86f..a16de4fce8b 100644
--- a/mysql-test/suite/innodb/r/fts_exec_interrupt.result
+++ b/mysql-test/suite/innodb/r/fts_exec_interrupt.result
@@ -5,17 +5,22 @@ CREATE TABLE `fttest04` (
PRIMARY KEY (`id`),
FULLTEXT KEY `idx` (`str`,`str2`)
) ENGINE=InnoDB AUTO_INCREMENT=65520 DEFAULT CHARSET=utf8mb4;
CREATE PROCEDURE load_test_data()
BEGIN
DECLARE cnt INTEGER UNSIGNED DEFAULT 1;
-WHILE (cnt <= 600220) DO
INSERT INTO fttest04(str, str2) VALUES('徳島県','徳島県');
+WHILE (cnt <= 19) DO
+INSERT INTO fttest04(str, str2) SELECT str, str2 FROM fttest04;
SET cnt= cnt+1;
END WHILE;
+INSERT INTO fttest04(str, str2) SELECT str, str2 FROM fttest04 LIMIT 75932;
END|
+SET autocommit=OFF;
CALL load_test_data();
+COMMIT;
+SET autocommit=default;
DROP PROCEDURE load_test_data;
SELECT /*+ max_execution_time(2000) */ COUNT(id) FROM fttest04 WHERE
MATCH (str,str2) AGAINST ('徳島県' IN BOOLEAN MODE) ;
ERROR HY000: Query execution was interrupted, maximum statement execution time exceeded
DROP TABLE fttest04;
diff --git a/mysql-test/suite/innodb/t/fts_exec_interrupt.test b/mysql-test/suite/innodb/t/fts_exec_interrupt.test
index 0c9b25a0efc..675890dc71d 100644
--- a/mysql-test/suite/innodb/t/fts_exec_interrupt.test
+++ b/mysql-test/suite/innodb/t/fts_exec_interrupt.test
@@ -16,20 +16,25 @@ CREATE TABLE `fttest04` (
--disable_result_log
DELIMITER |;
CREATE PROCEDURE load_test_data()
BEGIN
DECLARE cnt INTEGER UNSIGNED DEFAULT 1;
- WHILE (cnt <= 600220) DO
- INSERT INTO fttest04(str, str2) VALUES('徳島県','徳島県');
+ INSERT INTO fttest04(str, str2) VALUES('徳島県','徳島県');
+ WHILE (cnt <= 19) DO
+ INSERT INTO fttest04(str, str2) SELECT str, str2 FROM fttest04;
SET cnt= cnt+1;
END WHILE;
+ INSERT INTO fttest04(str, str2) SELECT str, str2 FROM fttest04 LIMIT 75932;
END|
DELIMITER ;|
+SET autocommit=OFF;
CALL load_test_data();
+COMMIT;
+SET autocommit=default;
DROP PROCEDURE load_test_data;
--enable_result_log
--error ER_QUERY_TIMEOUT
SELECT /*+ max_execution_time(2000) */ COUNT(id) FROM fttest04 WHERE
MATCH (str,str2) AGAINST ('徳島県' IN BOOLEAN MODE) ;
Description: A new test innodb.fts_exec_interrupt was added in the latest 5.7 release which takes 22 minutes to execute. The test does 600220 INSERTs with autocommit=1 (that is - implicit commit after each INSERT). How to repeat: ./mtr --big-test innodb.fts_exec_interrupt Suggested fix: commit 89a84b3c3c2 Parent: cd6c8ec8ceb Author: Vasil Dimov <vasil.dimov@galeracluster.com> AuthorDate: Mon Aug 20 18:41:21 2018 +0200 Commit: Daniele Sciascia <daniele.sciascia@galeracluster.com> CommitDate: Tue Aug 21 17:30:54 2018 +0200 Speed up innodb.fts_exec_interrupt * Do not (implicitly) commit after each INSERT * Instead of 600k INSERTs use 19 INSERT INTO ... SELECT ... This speeds up the test from about 22 minutes to about 2 minutes. diff --git a/mysql-test/suite/innodb/r/fts_exec_interrupt.result b/mysql-test/suite/innodb/r/fts_exec_interrupt.result index cf4968ea86f..a16de4fce8b 100644 --- a/mysql-test/suite/innodb/r/fts_exec_interrupt.result +++ b/mysql-test/suite/innodb/r/fts_exec_interrupt.result @@ -5,17 +5,22 @@ CREATE TABLE `fttest04` ( PRIMARY KEY (`id`), FULLTEXT KEY `idx` (`str`,`str2`) ) ENGINE=InnoDB AUTO_INCREMENT=65520 DEFAULT CHARSET=utf8mb4; CREATE PROCEDURE load_test_data() BEGIN DECLARE cnt INTEGER UNSIGNED DEFAULT 1; -WHILE (cnt <= 600220) DO INSERT INTO fttest04(str, str2) VALUES('徳島県','徳島県'); +WHILE (cnt <= 19) DO +INSERT INTO fttest04(str, str2) SELECT str, str2 FROM fttest04; SET cnt= cnt+1; END WHILE; +INSERT INTO fttest04(str, str2) SELECT str, str2 FROM fttest04 LIMIT 75932; END| +SET autocommit=OFF; CALL load_test_data(); +COMMIT; +SET autocommit=default; DROP PROCEDURE load_test_data; SELECT /*+ max_execution_time(2000) */ COUNT(id) FROM fttest04 WHERE MATCH (str,str2) AGAINST ('徳島県' IN BOOLEAN MODE) ; ERROR HY000: Query execution was interrupted, maximum statement execution time exceeded DROP TABLE fttest04; diff --git a/mysql-test/suite/innodb/t/fts_exec_interrupt.test b/mysql-test/suite/innodb/t/fts_exec_interrupt.test index 0c9b25a0efc..675890dc71d 100644 --- a/mysql-test/suite/innodb/t/fts_exec_interrupt.test +++ b/mysql-test/suite/innodb/t/fts_exec_interrupt.test @@ -16,20 +16,25 @@ CREATE TABLE `fttest04` ( --disable_result_log DELIMITER |; CREATE PROCEDURE load_test_data() BEGIN DECLARE cnt INTEGER UNSIGNED DEFAULT 1; - WHILE (cnt <= 600220) DO - INSERT INTO fttest04(str, str2) VALUES('徳島県','徳島県'); + INSERT INTO fttest04(str, str2) VALUES('徳島県','徳島県'); + WHILE (cnt <= 19) DO + INSERT INTO fttest04(str, str2) SELECT str, str2 FROM fttest04; SET cnt= cnt+1; END WHILE; + INSERT INTO fttest04(str, str2) SELECT str, str2 FROM fttest04 LIMIT 75932; END| DELIMITER ;| +SET autocommit=OFF; CALL load_test_data(); +COMMIT; +SET autocommit=default; DROP PROCEDURE load_test_data; --enable_result_log --error ER_QUERY_TIMEOUT SELECT /*+ max_execution_time(2000) */ COUNT(id) FROM fttest04 WHERE MATCH (str,str2) AGAINST ('徳島県' IN BOOLEAN MODE) ;