| 1 |
# |
| 2 |
# Bug#32831765 SERVER HITS OOM CONDITION WHEN LOADING TWO |
| 3 |
# INNODB TABLES WITH FTS INDEXES |
| 4 |
# |
| 5 |
create table t1 ( `id` int unsigned NOT NULL AUTO_INCREMENT, `col01` text, |
| 6 |
`col02` text, `col03` text, `col04` text, `col05` text, `col06` text, `col07` |
| 7 |
text, `col08` text, `col09` text, `col10` text, `col11` text, `col12` text, |
| 8 |
`col13` text, `col14` text, `col15` text, `col16` text, `col17` text, `col18` |
| 9 |
text, `col19` text, `col20` text, `col21` text, `col22` text, `col23` text, |
| 10 |
`col24` text, `col25` text, `col26` text, `col27` text, `col28` text, `col29` |
| 11 |
text, `col30` text, PRIMARY KEY (`id`), FULLTEXT KEY (`col01`), FULLTEXT KEY |
| 12 |
(`col02`), FULLTEXT KEY (`col03`), FULLTEXT KEY (`col04`), FULLTEXT KEY |
| 13 |
(`col05`), FULLTEXT KEY (`col06`), FULLTEXT KEY (`col07`), FULLTEXT KEY |
| 14 |
(`col08`), FULLTEXT KEY (`col09`), FULLTEXT KEY (`col10`), FULLTEXT KEY |
| 15 |
(`col11`), FULLTEXT KEY (`col12`), FULLTEXT KEY (`col13`), FULLTEXT KEY |
| 16 |
(`col14`), FULLTEXT KEY (`col15`), FULLTEXT KEY (`col16`), FULLTEXT KEY |
| 17 |
(`col17`), FULLTEXT KEY (`col18`), FULLTEXT KEY (`col19`), FULLTEXT KEY |
| 18 |
(`col20`), FULLTEXT KEY (`col21`), FULLTEXT KEY (`col22`), FULLTEXT KEY |
| 19 |
(`col23`), FULLTEXT KEY (`col24`), FULLTEXT KEY (`col25`), FULLTEXT KEY |
| 20 |
(`col26`), FULLTEXT KEY (`col27`), FULLTEXT KEY (`col28`), FULLTEXT KEY |
| 21 |
(`col29`), FULLTEXT KEY (`col30`)) engine=innodb; |
| 22 |
create table t2 ( `id` int unsigned NOT NULL AUTO_INCREMENT, `col01` text, |
| 23 |
`col02` text, `col03` text, `col04` text, `col05` text, `col06` text, `col07` |
| 24 |
text, `col08` text, `col09` text, `col10` text, `col11` text, `col12` text, |
| 25 |
`col13` text, `col14` text, `col15` text, `col16` text, `col17` text, `col18` |
| 26 |
text, `col19` text, `col20` text, `col21` text, `col22` text, `col23` text, |
| 27 |
`col24` text, `col25` text, `col26` text, `col27` text, `col28` text, `col29` |
| 28 |
text, `col30` text, PRIMARY KEY (`id`), FULLTEXT KEY (`col01`), FULLTEXT KEY |
| 29 |
(`col02`), FULLTEXT KEY (`col03`), FULLTEXT KEY (`col04`), FULLTEXT KEY |
| 30 |
(`col05`), FULLTEXT KEY (`col06`), FULLTEXT KEY (`col07`), FULLTEXT KEY |
| 31 |
(`col08`), FULLTEXT KEY (`col09`), FULLTEXT KEY (`col10`), FULLTEXT KEY |
| 32 |
(`col11`), FULLTEXT KEY (`col12`), FULLTEXT KEY (`col13`), FULLTEXT KEY |
| 33 |
(`col14`), FULLTEXT KEY (`col15`), FULLTEXT KEY (`col16`), FULLTEXT KEY |
| 34 |
(`col17`), FULLTEXT KEY (`col18`), FULLTEXT KEY (`col19`), FULLTEXT KEY |
| 35 |
(`col20`), FULLTEXT KEY (`col21`), FULLTEXT KEY (`col22`), FULLTEXT KEY |
| 36 |
(`col23`), FULLTEXT KEY (`col24`), FULLTEXT KEY (`col25`), FULLTEXT KEY |
| 37 |
(`col26`), FULLTEXT KEY (`col27`), FULLTEXT KEY (`col28`), FULLTEXT KEY |
| 38 |
(`col29`), FULLTEXT KEY (`col30`)) engine=innodb; |
| 39 |
create table t3 ( `id` int unsigned NOT NULL AUTO_INCREMENT, `col01` text, |
| 40 |
`col02` text, `col03` text, `col04` text, `col05` text, `col06` text, `col07` |
| 41 |
text, `col08` text, `col09` text, `col10` text, `col11` text, `col12` text, |
| 42 |
`col13` text, `col14` text, `col15` text, `col16` text, `col17` text, `col18` |
| 43 |
text, `col19` text, `col20` text, `col21` text, `col22` text, `col23` text, |
| 44 |
`col24` text, `col25` text, `col26` text, `col27` text, `col28` text, `col29` |
| 45 |
text, `col30` text, PRIMARY KEY (`id`), FULLTEXT KEY (`col01`), FULLTEXT KEY |
| 46 |
(`col02`), FULLTEXT KEY (`col03`), FULLTEXT KEY (`col04`), FULLTEXT KEY |
| 47 |
(`col05`), FULLTEXT KEY (`col06`), FULLTEXT KEY (`col07`), FULLTEXT KEY |
| 48 |
(`col08`), FULLTEXT KEY (`col09`), FULLTEXT KEY (`col10`), FULLTEXT KEY |
| 49 |
(`col11`), FULLTEXT KEY (`col12`), FULLTEXT KEY (`col13`), FULLTEXT KEY |
| 50 |
(`col14`), FULLTEXT KEY (`col15`), FULLTEXT KEY (`col16`), FULLTEXT KEY |
| 51 |
(`col17`), FULLTEXT KEY (`col18`), FULLTEXT KEY (`col19`), FULLTEXT KEY |
| 52 |
(`col20`), FULLTEXT KEY (`col21`), FULLTEXT KEY (`col22`), FULLTEXT KEY |
| 53 |
(`col23`), FULLTEXT KEY (`col24`), FULLTEXT KEY (`col25`), FULLTEXT KEY |
| 54 |
(`col26`), FULLTEXT KEY (`col27`), FULLTEXT KEY (`col28`), FULLTEXT KEY |
| 55 |
(`col29`), FULLTEXT KEY (`col30`)) engine=innodb; |
| 56 |
create table t4 ( `id` int unsigned NOT NULL AUTO_INCREMENT, `col01` text, |
| 57 |
`col02` text, `col03` text, `col04` text, `col05` text, `col06` text, `col07` |
| 58 |
text, `col08` text, `col09` text, `col10` text, `col11` text, `col12` text, |
| 59 |
`col13` text, `col14` text, `col15` text, `col16` text, `col17` text, `col18` |
| 60 |
text, `col19` text, `col20` text, `col21` text, `col22` text, `col23` text, |
| 61 |
`col24` text, `col25` text, `col26` text, `col27` text, `col28` text, `col29` |
| 62 |
text, `col30` text, PRIMARY KEY (`id`), FULLTEXT KEY (`col01`), FULLTEXT KEY |
| 63 |
(`col02`), FULLTEXT KEY (`col03`), FULLTEXT KEY (`col04`), FULLTEXT KEY |
| 64 |
(`col05`), FULLTEXT KEY (`col06`), FULLTEXT KEY (`col07`), FULLTEXT KEY |
| 65 |
(`col08`), FULLTEXT KEY (`col09`), FULLTEXT KEY (`col10`), FULLTEXT KEY |
| 66 |
(`col11`), FULLTEXT KEY (`col12`), FULLTEXT KEY (`col13`), FULLTEXT KEY |
| 67 |
(`col14`), FULLTEXT KEY (`col15`), FULLTEXT KEY (`col16`), FULLTEXT KEY |
| 68 |
(`col17`), FULLTEXT KEY (`col18`), FULLTEXT KEY (`col19`), FULLTEXT KEY |
| 69 |
(`col20`), FULLTEXT KEY (`col21`), FULLTEXT KEY (`col22`), FULLTEXT KEY |
| 70 |
(`col23`), FULLTEXT KEY (`col24`), FULLTEXT KEY (`col25`), FULLTEXT KEY |
| 71 |
(`col26`), FULLTEXT KEY (`col27`), FULLTEXT KEY (`col28`), FULLTEXT KEY |
| 72 |
(`col29`), FULLTEXT KEY (`col30`)) engine=innodb; |
| 73 |
#create procedure to inset into the table. |
| 74 |
CREATE PROCEDURE `proc_insert`(IN tab_name VARCHAR(40)) |
| 75 |
BEGIN |
| 76 |
DECLARE i INT DEFAULT 1; |
| 77 |
SET @insert_tbl =CONCAT('INSERT INTO ', tab_name, '( `col01`, `col02`, |
| 78 |
`col03`, `col04`, `col05`, `col06`, `col07`, `col08`, `col09`, `col10`, |
| 79 |
`col11`, `col12`, `col13`, `col14`, `col15`, `col16`, `col17`, `col18`, |
| 80 |
`col19`, `col20`, `col21`, `col22`, `col23`, `col24`, `col25`, `col26`, |
| 81 |
`col27`, `col28`, `col29`, `col30`) |
| 82 |
VALUES ( MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), |
| 83 |
MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), |
| 84 |
MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), |
| 85 |
MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), |
| 86 |
MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), |
| 87 |
MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), |
| 88 |
MD5(RAND()))'); |
| 89 |
PREPARE ins_stmt FROM @insert_tbl; |
| 90 |
while (i <= 2000) DO |
| 91 |
EXECUTE ins_stmt; |
| 92 |
SET i = i + 1; |
| 93 |
END WHILE; |
| 94 |
DEALLOCATE PREPARE ins_stmt; |
| 95 |
END | |
| 96 |
call proc_insert('t1');; |
| 97 |
call proc_insert('t1');; |
| 98 |
call proc_insert('t2');; |
| 99 |
call proc_insert('t2');; |
| 100 |
call proc_insert('t3');; |
| 101 |
call proc_insert('t3');; |
| 102 |
call proc_insert('t4');; |
| 103 |
call proc_insert('t4');; |
| 104 |
SET GLOBAL DEBUG="+d,fts_optimize_wq_count_check"; |
| 105 |
SET GLOBAL DEBUG="-d,fts_optimize_wq_count_check"; |
| 106 |
# clean up |
| 107 |
DROP TABLE t1; |
| 108 |
DROP TABLE t2; |
| 109 |
DROP TABLE t3; |
| 110 |
DROP TABLE t4; |
| 111 |
DROP PROCEDURE proc_insert; |