Bug #92105 Speed up innodb.fts_exec_interrupt
Submitted: 21 Aug 2018 16:28 Modified: 21 Aug 2018 18:45
Reporter: Vasil Dimov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Tests Severity:S5 (Performance)
Version:5.7.23 OS:Any
Assigned to: CPU Architecture:Any

[21 Aug 2018 16:28] Vasil Dimov
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) ;
[21 Aug 2018 18:45] Miguel Solorzano
Thank you for the bug report.
[26 Apr 11:47] Laurynas Biveinis
The test is heavily timing-dependent, and as such, unstable too, e.g. on 8.0.16:

190425  5:40:41 [ 99%] innodb.fts_exec_interrupt                w8  [ fail ]
        Test ended at 2019-04-25 05:40:41

CURRENT_TEST: innodb.fts_exec_interrupt
mysqltest: At line 31: Query 'SELECT /*+ max_execution_time(2000) */ COUNT(id) FROM fttest04 WHERE
MATCH (str,str2) AGAINST ('徳島県' IN BOOLEAN MODE) ' succeeded, should have failed with error '3024'

It should be rewritten as a debug_sync test instead, which would resolve both run time and stability issues.