| 1 |
--echo # |
| 2 |
--echo # Bug#32831765 SERVER HITS OOM CONDITION WHEN LOADING TWO |
| 3 |
--echo # INNODB TABLES WITH FTS INDEXES |
| 4 |
--echo # |
| 5 |
|
| 6 |
--source include/have_debug.inc |
| 7 |
--source include/not_valgrind.inc |
| 8 |
|
| 9 |
create table t1 ( `id` int unsigned NOT NULL AUTO_INCREMENT, `col01` text, |
| 10 |
`col02` text, `col03` text, `col04` text, `col05` text, `col06` text, `col07` |
| 11 |
text, `col08` text, `col09` text, `col10` text, `col11` text, `col12` text, |
| 12 |
`col13` text, `col14` text, `col15` text, `col16` text, `col17` text, `col18` |
| 13 |
text, `col19` text, `col20` text, `col21` text, `col22` text, `col23` text, |
| 14 |
`col24` text, `col25` text, `col26` text, `col27` text, `col28` text, `col29` |
| 15 |
text, `col30` text, PRIMARY KEY (`id`), FULLTEXT KEY (`col01`), FULLTEXT KEY |
| 16 |
(`col02`), FULLTEXT KEY (`col03`), FULLTEXT KEY (`col04`), FULLTEXT KEY |
| 17 |
(`col05`), FULLTEXT KEY (`col06`), FULLTEXT KEY (`col07`), FULLTEXT KEY |
| 18 |
(`col08`), FULLTEXT KEY (`col09`), FULLTEXT KEY (`col10`), FULLTEXT KEY |
| 19 |
(`col11`), FULLTEXT KEY (`col12`), FULLTEXT KEY (`col13`), FULLTEXT KEY |
| 20 |
(`col14`), FULLTEXT KEY (`col15`), FULLTEXT KEY (`col16`), FULLTEXT KEY |
| 21 |
(`col17`), FULLTEXT KEY (`col18`), FULLTEXT KEY (`col19`), FULLTEXT KEY |
| 22 |
(`col20`), FULLTEXT KEY (`col21`), FULLTEXT KEY (`col22`), FULLTEXT KEY |
| 23 |
(`col23`), FULLTEXT KEY (`col24`), FULLTEXT KEY (`col25`), FULLTEXT KEY |
| 24 |
(`col26`), FULLTEXT KEY (`col27`), FULLTEXT KEY (`col28`), FULLTEXT KEY |
| 25 |
(`col29`), FULLTEXT KEY (`col30`)) engine=innodb; |
| 26 |
|
| 27 |
create table t2 ( `id` int unsigned NOT NULL AUTO_INCREMENT, `col01` text, |
| 28 |
`col02` text, `col03` text, `col04` text, `col05` text, `col06` text, `col07` |
| 29 |
text, `col08` text, `col09` text, `col10` text, `col11` text, `col12` text, |
| 30 |
`col13` text, `col14` text, `col15` text, `col16` text, `col17` text, `col18` |
| 31 |
text, `col19` text, `col20` text, `col21` text, `col22` text, `col23` text, |
| 32 |
`col24` text, `col25` text, `col26` text, `col27` text, `col28` text, `col29` |
| 33 |
text, `col30` text, PRIMARY KEY (`id`), FULLTEXT KEY (`col01`), FULLTEXT KEY |
| 34 |
(`col02`), FULLTEXT KEY (`col03`), FULLTEXT KEY (`col04`), FULLTEXT KEY |
| 35 |
(`col05`), FULLTEXT KEY (`col06`), FULLTEXT KEY (`col07`), FULLTEXT KEY |
| 36 |
(`col08`), FULLTEXT KEY (`col09`), FULLTEXT KEY (`col10`), FULLTEXT KEY |
| 37 |
(`col11`), FULLTEXT KEY (`col12`), FULLTEXT KEY (`col13`), FULLTEXT KEY |
| 38 |
(`col14`), FULLTEXT KEY (`col15`), FULLTEXT KEY (`col16`), FULLTEXT KEY |
| 39 |
(`col17`), FULLTEXT KEY (`col18`), FULLTEXT KEY (`col19`), FULLTEXT KEY |
| 40 |
(`col20`), FULLTEXT KEY (`col21`), FULLTEXT KEY (`col22`), FULLTEXT KEY |
| 41 |
(`col23`), FULLTEXT KEY (`col24`), FULLTEXT KEY (`col25`), FULLTEXT KEY |
| 42 |
(`col26`), FULLTEXT KEY (`col27`), FULLTEXT KEY (`col28`), FULLTEXT KEY |
| 43 |
(`col29`), FULLTEXT KEY (`col30`)) engine=innodb; |
| 44 |
|
| 45 |
|
| 46 |
create table t3 ( `id` int unsigned NOT NULL AUTO_INCREMENT, `col01` text, |
| 47 |
`col02` text, `col03` text, `col04` text, `col05` text, `col06` text, `col07` |
| 48 |
text, `col08` text, `col09` text, `col10` text, `col11` text, `col12` text, |
| 49 |
`col13` text, `col14` text, `col15` text, `col16` text, `col17` text, `col18` |
| 50 |
text, `col19` text, `col20` text, `col21` text, `col22` text, `col23` text, |
| 51 |
`col24` text, `col25` text, `col26` text, `col27` text, `col28` text, `col29` |
| 52 |
text, `col30` text, PRIMARY KEY (`id`), FULLTEXT KEY (`col01`), FULLTEXT KEY |
| 53 |
(`col02`), FULLTEXT KEY (`col03`), FULLTEXT KEY (`col04`), FULLTEXT KEY |
| 54 |
(`col05`), FULLTEXT KEY (`col06`), FULLTEXT KEY (`col07`), FULLTEXT KEY |
| 55 |
(`col08`), FULLTEXT KEY (`col09`), FULLTEXT KEY (`col10`), FULLTEXT KEY |
| 56 |
(`col11`), FULLTEXT KEY (`col12`), FULLTEXT KEY (`col13`), FULLTEXT KEY |
| 57 |
(`col14`), FULLTEXT KEY (`col15`), FULLTEXT KEY (`col16`), FULLTEXT KEY |
| 58 |
(`col17`), FULLTEXT KEY (`col18`), FULLTEXT KEY (`col19`), FULLTEXT KEY |
| 59 |
(`col20`), FULLTEXT KEY (`col21`), FULLTEXT KEY (`col22`), FULLTEXT KEY |
| 60 |
(`col23`), FULLTEXT KEY (`col24`), FULLTEXT KEY (`col25`), FULLTEXT KEY |
| 61 |
(`col26`), FULLTEXT KEY (`col27`), FULLTEXT KEY (`col28`), FULLTEXT KEY |
| 62 |
(`col29`), FULLTEXT KEY (`col30`)) engine=innodb; |
| 63 |
|
| 64 |
create table t4 ( `id` int unsigned NOT NULL AUTO_INCREMENT, `col01` text, |
| 65 |
`col02` text, `col03` text, `col04` text, `col05` text, `col06` text, `col07` |
| 66 |
text, `col08` text, `col09` text, `col10` text, `col11` text, `col12` text, |
| 67 |
`col13` text, `col14` text, `col15` text, `col16` text, `col17` text, `col18` |
| 68 |
text, `col19` text, `col20` text, `col21` text, `col22` text, `col23` text, |
| 69 |
`col24` text, `col25` text, `col26` text, `col27` text, `col28` text, `col29` |
| 70 |
text, `col30` text, PRIMARY KEY (`id`), FULLTEXT KEY (`col01`), FULLTEXT KEY |
| 71 |
(`col02`), FULLTEXT KEY (`col03`), FULLTEXT KEY (`col04`), FULLTEXT KEY |
| 72 |
(`col05`), FULLTEXT KEY (`col06`), FULLTEXT KEY (`col07`), FULLTEXT KEY |
| 73 |
(`col08`), FULLTEXT KEY (`col09`), FULLTEXT KEY (`col10`), FULLTEXT KEY |
| 74 |
(`col11`), FULLTEXT KEY (`col12`), FULLTEXT KEY (`col13`), FULLTEXT KEY |
| 75 |
(`col14`), FULLTEXT KEY (`col15`), FULLTEXT KEY (`col16`), FULLTEXT KEY |
| 76 |
(`col17`), FULLTEXT KEY (`col18`), FULLTEXT KEY (`col19`), FULLTEXT KEY |
| 77 |
(`col20`), FULLTEXT KEY (`col21`), FULLTEXT KEY (`col22`), FULLTEXT KEY |
| 78 |
(`col23`), FULLTEXT KEY (`col24`), FULLTEXT KEY (`col25`), FULLTEXT KEY |
| 79 |
(`col26`), FULLTEXT KEY (`col27`), FULLTEXT KEY (`col28`), FULLTEXT KEY |
| 80 |
(`col29`), FULLTEXT KEY (`col30`)) engine=innodb; |
| 81 |
|
| 82 |
delimiter |; |
| 83 |
|
| 84 |
--echo #create procedure to inset into the table. |
| 85 |
CREATE PROCEDURE `proc_insert`(IN tab_name VARCHAR(40)) |
| 86 |
BEGIN |
| 87 |
DECLARE i INT DEFAULT 1; |
| 88 |
SET @insert_tbl =CONCAT('INSERT INTO ', tab_name, '( `col01`, `col02`, |
| 89 |
`col03`, `col04`, `col05`, `col06`, `col07`, `col08`, `col09`, `col10`, |
| 90 |
`col11`, `col12`, `col13`, `col14`, `col15`, `col16`, `col17`, `col18`, |
| 91 |
`col19`, `col20`, `col21`, `col22`, `col23`, `col24`, `col25`, `col26`, |
| 92 |
`col27`, `col28`, `col29`, `col30`) |
| 93 |
VALUES ( MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), |
| 94 |
MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), |
| 95 |
MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), |
| 96 |
MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), |
| 97 |
MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), |
| 98 |
MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), |
| 99 |
MD5(RAND()))'); |
| 100 |
PREPARE ins_stmt FROM @insert_tbl; |
| 101 |
while (i <= 2000) DO |
| 102 |
EXECUTE ins_stmt; |
| 103 |
SET i = i + 1; |
| 104 |
END WHILE; |
| 105 |
DEALLOCATE PREPARE ins_stmt; |
| 106 |
END | |
| 107 |
|
| 108 |
delimiter ;| |
| 109 |
|
| 110 |
connect (con1,localhost,root,,); |
| 111 |
connect (con2,localhost,root,,); |
| 112 |
connect (con3,localhost,root,,); |
| 113 |
connect (con4,localhost,root,,); |
| 114 |
connect (con5,localhost,root,,); |
| 115 |
connect (con6,localhost,root,,); |
| 116 |
connect (con7,localhost,root,,); |
| 117 |
connect (con8,localhost,root,,); |
| 118 |
|
| 119 |
connection con1; |
| 120 |
--send call proc_insert('t1'); |
| 121 |
|
| 122 |
connection con2; |
| 123 |
--send call proc_insert('t1'); |
| 124 |
|
| 125 |
connection con3; |
| 126 |
--send call proc_insert('t2'); |
| 127 |
|
| 128 |
connection con4; |
| 129 |
--send call proc_insert('t2'); |
| 130 |
|
| 131 |
connection con5; |
| 132 |
--send call proc_insert('t3'); |
| 133 |
|
| 134 |
connection con6; |
| 135 |
--send call proc_insert('t3'); |
| 136 |
|
| 137 |
connection con7; |
| 138 |
--send call proc_insert('t4'); |
| 139 |
|
| 140 |
connection con8; |
| 141 |
--send call proc_insert('t4'); |
| 142 |
|
| 143 |
connection default; |
| 144 |
|
| 145 |
# Check if number of SYNC requests are more than 1000. |
| 146 |
SET GLOBAL DEBUG="+d,fts_optimize_wq_count_check"; |
| 147 |
|
| 148 |
# Wait for 1 minute to make sure INSERTs into FTS table will trigger |
| 149 |
# FTS_SYNC request |
| 150 |
let $loop=60; |
| 151 |
while($loop > 1) |
| 152 |
{ |
| 153 |
sleep 1; |
| 154 |
dec $loop; |
| 155 |
} |
| 156 |
|
| 157 |
SET GLOBAL DEBUG="-d,fts_optimize_wq_count_check"; |
| 158 |
|
| 159 |
connection con1; |
| 160 |
reap; |
| 161 |
disconnect con1; |
| 162 |
|
| 163 |
connection con2; |
| 164 |
reap; |
| 165 |
disconnect con2; |
| 166 |
|
| 167 |
connection con3; |
| 168 |
reap; |
| 169 |
disconnect con3; |
| 170 |
|
| 171 |
connection con4; |
| 172 |
reap; |
| 173 |
disconnect con4; |
| 174 |
|
| 175 |
connection con5; |
| 176 |
reap; |
| 177 |
disconnect con5; |
| 178 |
|
| 179 |
connection con6; |
| 180 |
reap; |
| 181 |
disconnect con6; |
| 182 |
|
| 183 |
connection con7; |
| 184 |
reap; |
| 185 |
disconnect con7; |
| 186 |
|
| 187 |
connection con8; |
| 188 |
reap; |
| 189 |
disconnect con8; |
| 190 |
|
| 191 |
connection default; |
| 192 |
--echo # clean up |
| 193 |
DROP TABLE t1; |
| 194 |
DROP TABLE t2; |
| 195 |
DROP TABLE t3; |
| 196 |
DROP TABLE t4; |
| 197 |
DROP PROCEDURE proc_insert; |