Debian Codegraph @

mysql-test/suite/innodb_zip/t/wl6347_comp_indx_stat.test

raw
1 --echo #******************************************************************
2 --echo # Test 1: Test the interaction between stats and compression level
3 --echo # and logging of compressed pages configuration
4 --echo # This testcase is to verify the table/idex level compression stats
5 --echo # When the flags are set as follows
6 --echo # innodb_cmp_per_index_enabled=ON and innodb_compression_level=0
7 --echo # page size 1K,2K,4K,8K,16K
8 --echo # check the size and compression stats of the table tab5
9 --echo #******************************************************************
10
11 # This test case needs InnoDB.
12 -- source include/have_innodb.inc
13 -- source include/not_embedded.inc
14 -- source include/have_innodb_16k.inc
15 -- source include/big_test.inc
16
17 -- vertical_results
18
19 let MYSQLD_DATADIR=`SELECT @@datadir`;
20 let $innodb_compression_level = `SELECT @@global.innodb_compression_level`;
21
22 --echo # set the flags
23 SET GLOBAL innodb_cmp_per_index_enabled=ON;
24 SET GLOBAL innodb_compression_level=0;
25 --disable_query_log
26 SET GLOBAL innodb_read_only_compressed=OFF;
27 --enable_query_log
28
29 --echo #******************************************************************
30 --echo # Test 1-1K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=0 with page size 1K
31 --echo #******************************************************************
32
33 --echo # create a table with page size=1K
34 --echo # create indexes on each column.(total 9 indexes)
35 let $block_size=1;
36 --source suite/innodb_zip/include/innodb_create_tab_indx.inc
37
38 --echo # check the stats of the table & size of the table
39 SET @inl_val=1;
40 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
41
42 --echo # for deterministic reasons simple data should be inserted.
43 --echo # insert some 100 records
44 let $i = 100;
45 --source suite/innodb_zip/include/innodb_load_data.inc
46
47 --echo # check the stats of the table & size of the table
48 SET @inl_val=2;
49 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
50
51 --echo # fetch the compressed page and check the stats
52 --source suite/innodb_zip/include/innodb_fetch_records.inc
53
54 --echo # check the stats of the table & size of the table
55 SET @inl_val=2;
56 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
57
58 --echo # fetch the compressed same page once again and check the stats
59 --echo # the stat figures should be same as above query
60 --source suite/innodb_zip/include/innodb_fetch_records.inc
61
62 --echo # check the stats of the table & size of the table
63 SET @inl_val=2;
64 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
65
66 -- source include/restart_mysqld.inc
67
68 --echo # set the flag on (default off)
69 SET GLOBAL innodb_cmp_per_index_enabled=ON;
70 --disable_query_log
71 SET GLOBAL innodb_read_only_compressed=OFF;
72 --enable_query_log
73
74 --echo # set the flags
75 SET GLOBAL innodb_compression_level=0;
76
77 --echo # fetch the compressed page and check the stats
78 --echo # The stats figure may be different/same for each restart.
79 --source suite/innodb_zip/include/innodb_fetch_records.inc
80
81 --echo # check the stats of the table
82 -- echo # testcase : pass = 1 fail = 0
83 SET @comp_val=0;
84 SET @uncomp_val=1;
85 --source suite/innodb_zip/include/innodb_stats_restart.inc
86
87 DROP TABLE tab5;
88
89 --echo #******************************************************************
90 --echo # Test 1-2K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=0 with page size 2K
91 --echo #******************************************************************
92
93 --echo # reset the stat table before starting next testcase
94 SET GLOBAL innodb_cmp_per_index_enabled=0;
95 SET GLOBAL innodb_cmp_per_index_enabled=1;
96
97 --echo # create a table with page size=2K
98 --echo # create indexes on each column.(total 9 indexes)
99 let $block_size=2;
100 --source suite/innodb_zip/include/innodb_create_tab_indx.inc
101
102 --echo # check the stats of the table & size of the table
103 SET @inl_val=1;
104 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
105
106 --echo # for determintic resons simple data should be inserted.
107 --echo # insert some 100 records
108 let $i = 100;
109 --source suite/innodb_zip/include/innodb_load_data.inc
110
111 --echo # check the stats of the table & size of the table
112 SET @inl_val=2;
113 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
114
115 --echo # fetch the compressed page and check the stats
116 --source suite/innodb_zip/include/innodb_fetch_records.inc
117
118 --echo # check the stats of the table & size of the table
119 SET @inl_val=2;
120 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
121
122 --echo # fetch the compressed same page once again and check the stats
123 --echo # the stat figures should be same as above query
124 --source suite/innodb_zip/include/innodb_fetch_records.inc
125
126 --echo # check the stats of the table & size of the table
127 SET @inl_val=2;
128 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
129
130 -- source include/restart_mysqld.inc
131
132 --echo # set the flag on (default off)
133 SET GLOBAL innodb_cmp_per_index_enabled=ON;
134
135 --echo # set the flags
136 SET GLOBAL innodb_compression_level=0;
137
138 --echo # fetch the compressed page and check the stats
139 --echo # The stats figure may be different/same for each restart.
140 --source suite/innodb_zip/include/innodb_fetch_records.inc
141
142 --echo # check the stats of the table
143 -- echo # testcase : pass = 1 fail = 0
144 SET @comp_val=0;
145 SET @uncomp_val=2;
146 --source suite/innodb_zip/include/innodb_stats_restart.inc
147
148 DROP TABLE tab5;
149
150 --echo #******************************************************************
151 --echo # Test 1-4K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=0 with page size 4K
152 --echo #******************************************************************
153
154 --echo # reset the stat table before starting next testcase
155 SET GLOBAL innodb_cmp_per_index_enabled=0;
156 SET GLOBAL innodb_cmp_per_index_enabled=1;
157 --disable_query_log
158 SET GLOBAL innodb_read_only_compressed=OFF;
159 --enable_query_log
160
161 --echo # create a table with page size=4K
162 --echo # create indexes on each column.(total 9 indexes)
163 let $block_size=4;
164 --source suite/innodb_zip/include/innodb_create_tab_indx.inc
165
166 --echo # check the stats of the table & size of the table
167 SET @inl_val=1;
168 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
169
170 --echo # for determintic resons simple data should be inserted.
171 --echo # insert some 100 records
172 let $i = 100;
173 --source suite/innodb_zip/include/innodb_load_data.inc
174
175 --echo # check the stats of the table & size of the table
176 SET @inl_val=1;
177 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
178
179 --echo # fetch the compressed page and check the stats
180 --source suite/innodb_zip/include/innodb_fetch_records.inc
181
182 --echo # check the stats of the table & size of the table
183 SET @inl_val=1;
184 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
185
186 --echo # fetch the compressed same page once again and check the stats
187 --echo # the stat figures should be same as above query
188 --source suite/innodb_zip/include/innodb_fetch_records.inc
189
190 --echo # check the stats of the table & size of the table
191 SET @inl_val=1;
192 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
193
194 DROP TABLE tab5;
195
196 --echo #******************************************************************
197 --echo # Test 1-8K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=0 with page size 8K
198 --echo #******************************************************************
199
200 --echo # reset the stat table before starting next testcase
201 SET GLOBAL innodb_cmp_per_index_enabled=0;
202 SET GLOBAL innodb_cmp_per_index_enabled=1;
203
204 --echo # create a table with page size=8K
205 --echo # create indexes on each column.(total 9 indexes)
206 let $block_size=8;
207 --source suite/innodb_zip/include/innodb_create_tab_indx.inc
208
209 --echo # check the stats of the table & size of the table
210 SET @inl_val=1;
211 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
212
213 --echo # for determintic resons simple data should be inserted.
214 --echo # insert some 100 records
215 let $i = 100;
216 --source suite/innodb_zip/include/innodb_load_data.inc
217
218 --echo # check the stats of the table & size of the table
219 SET @inl_val=1;
220 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
221
222 --echo # fetch the compressed page and check the stats
223 --source suite/innodb_zip/include/innodb_fetch_records.inc
224
225 --echo # check the stats of the table & size of the table
226 SET @inl_val=1;
227 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
228
229 --echo # fetch the compressed same page once again and check the stats
230 --echo # the stat figures should be same as above query
231 --source suite/innodb_zip/include/innodb_fetch_records.inc
232
233 --echo # check the stats of the table & size of the table
234 SET @inl_val=1;
235 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
236
237 DROP TABLE tab5;
238
239 --echo #******************************************************************
240 --echo # Test 1-16K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=0 with page size 16K
241 --echo #******************************************************************
242
243 --echo # reset the stat table before starting next testcase
244 SET GLOBAL innodb_cmp_per_index_enabled=0;
245 SET GLOBAL innodb_cmp_per_index_enabled=1;
246
247 --echo # create a table with page size=16K
248 --echo # create indexes on each column.(total 9 indexes)
249 let $block_size=16;
250 --source suite/innodb_zip/include/innodb_create_tab_indx.inc
251
252 --echo # check the stats of the table & size of the table
253 SET @inl_val=1;
254 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
255
256 --echo # for determintic resons simple data should be inserted.
257 --echo # insert some 100 records
258 let $i = 100;
259 --source suite/innodb_zip/include/innodb_load_data.inc
260
261 --echo # check the stats of the table & size of the table
262 SET @inl_val=1;
263 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
264
265 --echo # fetch the compressed page and check the stats
266 --source suite/innodb_zip/include/innodb_fetch_records.inc
267
268 --echo # check the stats of the table & size of the table
269 SET @inl_val=1;
270 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
271
272 --echo # fetch the compressed same page once again and check the stats
273 --echo # the stat figures should be same as above query
274 --source suite/innodb_zip/include/innodb_fetch_records.inc
275
276 --echo # check the stats of the table & size of the table
277 SET @inl_val=1;
278 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
279
280 DROP TABLE tab5;
281
282 --echo #******************************************************************
283 --echo # Test 2: test the interaction between wl6347 & wl6344 (2.2)
284 --echo # This testcase is to verify the table/idex level compression stats
285 --echo # When the flags are set as follows
286 --echo # innodb_cmp_per_index_enabled=ON and innodb_compression_level=9
287 --echo # page size 1K,2K,4K,8K,16K
288 --echo # check the size and compression stats of the table tab5
289 --echo #******************************************************************
290
291 --echo # reset the stat table before starting next testcase
292 SET GLOBAL innodb_cmp_per_index_enabled=0;
293 SET GLOBAL innodb_cmp_per_index_enabled=1;
294
295 --echo # set the flags
296 SET GLOBAL innodb_compression_level=9;
297
298
299 --echo #******************************************************************
300 --echo # Test 2-1K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=9 with page size 1K
301 --echo #******************************************************************
302
303 --echo # create a table with page size=1K
304 --echo # create indexes on each column.(total 9 indexes)
305 let $block_size=1;
306 --source suite/innodb_zip/include/innodb_create_tab_indx.inc
307
308 --echo # check the stats of the table & size of the table
309 --echo # The size of the file with 0 compress = 65536
310 SET @inl_val=1;
311 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
312
313 --echo # for determintic resons simple data should be inserted.
314 --echo # insert some 100 records
315 let $i = 100;
316 --source suite/innodb_zip/include/innodb_load_data.inc
317
318 --echo # check the stats of the table & size of the table
319 --echo # The size of the file with 0 compress = 2097152
320 SET @inl_val=2;
321 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
322
323 --echo # fetch the compressed page and check the stats
324 --source suite/innodb_zip/include/innodb_fetch_records.inc
325
326 --echo # check the stats of the table & size of the table
327 SET @inl_val=2;
328 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
329
330 --echo # fetch the compressed same page once again and check the stats
331 --echo # the stat figures should be same as above query
332 --source suite/innodb_zip/include/innodb_fetch_records.inc
333
334 --echo # check the stats of the table & size of the table
335 SET @inl_val=2;
336 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
337
338 -- source include/restart_mysqld.inc
339
340 --echo # set the flag on (default off)
341 SET GLOBAL innodb_cmp_per_index_enabled=ON;
342
343 --echo # set the flags
344 SET GLOBAL innodb_compression_level=9;
345
346
347 --echo # fetch the compressed page and check the stats
348 --echo # The stats figure may be different/same for each restart.
349 --source suite/innodb_zip/include/innodb_fetch_records.inc
350
351 --echo # check the stats of the table
352 -- echo # testcase : pass = 1 fail = 0
353 SET @comp_val=0;
354 SET @uncomp_val=1;
355 --source suite/innodb_zip/include/innodb_stats_restart.inc
356
357 DROP TABLE tab5;
358
359 --echo #******************************************************************
360 --echo # Test 2-2K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=9 with page size 2K
361 --echo #******************************************************************
362
363 --echo # reset the stat table before starting next testcase
364 SET GLOBAL innodb_cmp_per_index_enabled=OFF;
365 SET GLOBAL innodb_cmp_per_index_enabled=ON;
366 --disable_query_log
367 SET GLOBAL innodb_read_only_compressed=OFF;
368 --enable_query_log
369
370 --echo # create a table with page size=2K
371 --echo # create indexes on each column.(total 9 indexes)
372 let $block_size=2;
373 --source suite/innodb_zip/include/innodb_create_tab_indx.inc
374
375 --echo # check the stats of the table & size of the table
376 --echo # The size of the file with 0 compress = 65536
377 SET @inl_val=1;
378 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
379
380 --echo # for determintic resons simple data should be inserted.
381 --echo # insert some 100 records
382 let $i = 100;
383 --source suite/innodb_zip/include/innodb_load_data.inc
384
385 --echo # check the stats of the table & size of the table
386 --echo # The size of the file with 0 compress = 2097152
387 SET @inl_val=2;
388 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
389
390 --echo # fetch the compressed page and check the stats
391 --source suite/innodb_zip/include/innodb_fetch_records.inc
392
393 --echo # check the stats of the table & size of the table
394 SET @inl_val=2;
395 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
396
397 --echo # fetch the compressed same page once again and check the stats
398 --echo # the stat figures should be same as above query
399 --source suite/innodb_zip/include/innodb_fetch_records.inc
400
401 --echo # check the stats of the table & size of the table
402 SET @inl_val=2;
403 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
404
405 -- source include/restart_mysqld.inc
406
407 --echo # set the flag on (default off)
408 SET GLOBAL innodb_cmp_per_index_enabled=ON;
409
410 --echo # set the flags
411 SET GLOBAL innodb_compression_level=9;
412
413
414 --echo # fetch the compressed page and check the stats
415 --echo # The stats figure may be different/same for each restart.
416 --source suite/innodb_zip/include/innodb_fetch_records.inc
417
418 --echo # check the stats of the table
419 -- echo # testcase : pass = 1 fail = 0
420 SET @comp_val=0;
421 SET @uncomp_val=1;
422 --source suite/innodb_zip/include/innodb_stats_restart.inc
423
424 DROP TABLE tab5;
425
426 --echo #******************************************************************
427 --echo # Test 2-4K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=9 with page size 4K
428 --echo #******************************************************************
429
430 --echo # reset the stat table before starting next testcase
431 SET GLOBAL innodb_cmp_per_index_enabled=OFF;
432 SET GLOBAL innodb_cmp_per_index_enabled=ON;
433 --disable_query_log
434 SET GLOBAL innodb_read_only_compressed=OFF;
435 --enable_query_log
436
437 --echo # create a table with page size=4K
438 --echo # create indexes on each column.(total 9 indexes)
439 let $block_size=4;
440 --source suite/innodb_zip/include/innodb_create_tab_indx.inc
441
442 --echo # check the stats of the table & size of the table
443 --echo # The size of the file with 0 compress = 65536
444 SET @inl_val=1;
445 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
446
447 --echo # for determintic resons simple data should be inserted.
448 --echo # insert some 100 records
449 let $i = 100;
450 --source suite/innodb_zip/include/innodb_load_data.inc
451
452 --echo # check the stats of the table & size of the table
453 --echo # The size of the file with 0 compress = 159744
454 SET @inl_val=1;
455 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
456
457 --echo # fetch the compressed page and check the stats
458 --source suite/innodb_zip/include/innodb_fetch_records.inc
459
460 --echo # check the stats of the table & size of the table
461 SET @inl_val=1;
462 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
463
464 --echo # fetch the compressed same page once again and check the stats
465 --echo # the stat figures should be same as above query
466 --source suite/innodb_zip/include/innodb_fetch_records.inc
467
468 --echo # check the stats of the table & size of the table
469 SET @inl_val=1;
470 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
471
472 DROP TABLE tab5;
473
474 --echo #******************************************************************
475 --echo # Test 2-8K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=9 with page size 8K
476 --echo #******************************************************************
477
478 --echo # reset the stat table before starting next testcase
479 SET GLOBAL innodb_cmp_per_index_enabled=OFF;
480 SET GLOBAL innodb_cmp_per_index_enabled=ON;
481
482 --echo # create a table with page size=8K
483 --echo # create indexes on each column.(total 9 indexes)
484 let $block_size=8;
485 --source suite/innodb_zip/include/innodb_create_tab_indx.inc
486
487 --echo # check the stats of the table & size of the table
488 --echo # The size of the file with 0 compress = 122880
489 SET @inl_val=1;
490 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
491
492 --echo # for determintic resons simple data should be inserted.
493 --echo # insert some 100 records
494 let $i = 100;
495 --source suite/innodb_zip/include/innodb_load_data.inc
496
497 --echo # check the stats of the table & size of the table
498 SET @inl_val=1;
499 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
500
501 --echo # fetch the compressed page and check the stats
502 --source suite/innodb_zip/include/innodb_fetch_records.inc
503
504 --echo # check the stats of the table & size of the table
505 --echo # The size of the file with 0 compress = 212992
506 SET @inl_val=1;
507 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
508
509 --echo # fetch the compressed same page once again and check the stats
510 --echo # the stat figures should be same as above query
511 --source suite/innodb_zip/include/innodb_fetch_records.inc
512
513 --echo # check the stats of the table & size of the table
514 SET @inl_val=1;
515 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
516
517 DROP TABLE tab5;
518
519 --echo #******************************************************************
520 --echo # Test 2-16K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=9 with page size 16K
521 --echo #******************************************************************
522
523 --echo # reset the stat table before starting next testcase
524 SET GLOBAL innodb_cmp_per_index_enabled=OFF;
525 SET GLOBAL innodb_cmp_per_index_enabled=ON;
526
527 --echo # create a table with page size=16K
528 --echo # create indexes on each column.(total 9 indexes)
529 let $block_size=16;
530 --source suite/innodb_zip/include/innodb_create_tab_indx.inc
531
532 --echo # check the stats of the table & size of the table
533 --echo # The size of the file with 0 compress = 245760
534 SET @inl_val=1;
535 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
536
537 --echo # for determintic resons simple data should be inserted.
538 --echo # insert some 100 records
539 let $i = 100;
540 --source suite/innodb_zip/include/innodb_load_data.inc
541
542 --echo # check the stats of the table & size of the table
543 SET @inl_val=1;
544 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
545
546 --echo # fetch the compressed page and check the stats
547 --source suite/innodb_zip/include/innodb_fetch_records.inc
548
549 --echo # check the stats of the table & size of the table
550 --echo # The size of the file with 0 compress = 344064
551 SET @inl_val=1;
552 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
553
554 --echo # fetch the compressed same page once again and check the stats
555 --echo # the stat figures should be same as above query
556 --source suite/innodb_zip/include/innodb_fetch_records.inc
557
558 --echo # check the stats of the table & size of the table
559 SET @inl_val=1;
560 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
561
562 DROP TABLE tab5;
563
564 --echo #******************************************************************
565 --echo # Test 3: test the interaction between wl6347 & wl6344 (2.3)
566 --echo # This testcase is to verify the table/idex level compression stats
567 --echo # When the flags are set as follows
568 --echo # innodb_cmp_per_index_enabled=ON and
569 --echo # innodb_compression_level=6 (default)
570 --echo # page size 1K,2K,4K,8K,16K
571 --echo # check the size and compression stats of the table tab5
572 --echo #******************************************************************
573
574 --echo #******************************************************************
575 --echo # Test 3-1K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=Def with page size 1K
576 --echo #******************************************************************
577
578 --echo # reset the stat table before starting next testcase
579 SET GLOBAL innodb_cmp_per_index_enabled=OFF;
580 SET GLOBAL innodb_cmp_per_index_enabled=ON;
581
582 SET GLOBAL innodb_compression_level=default;
583
584 --echo # create a table with page size=1K
585 --echo # create indexes on each column.(total 9 indexes)
586 let $block_size=1;
587 --source suite/innodb_zip/include/innodb_create_tab_indx.inc
588
589 --echo # check the stats of the table & size of the table
590 --echo # The size of the file with 0 compress = 65536
591 SET @inl_val=1;
592 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
593
594 --echo # for determintic resons simple data should be inserted.
595 --echo # insert some 100 records
596 let $i = 100;
597 --source suite/innodb_zip/include/innodb_load_data.inc
598
599 --echo # check the stats of the table & size of the table
600 --echo # The size of the file with 0 compress = 65536
601 SET @inl_val=2;
602 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
603
604 --echo # fetch the compressed page and check the stats
605 --source suite/innodb_zip/include/innodb_fetch_records.inc
606
607 --echo # check the stats of the table & size of the table
608 SET @inl_val=2;
609 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
610
611 --echo # fetch the compressed same page once again and check the stats
612 --echo # the stat figures should be same as above query
613 --source suite/innodb_zip/include/innodb_fetch_records.inc
614
615 --echo # check the stats of the table & size of the table
616 SET @inl_val=2;
617 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
618
619 DROP TABLE tab5;
620
621 --echo #******************************************************************
622 --echo # Test 3-2K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=Def with page size 2K
623 --echo #******************************************************************
624
625 --echo # reset the stat table before starting next testcase
626 SET GLOBAL innodb_cmp_per_index_enabled=OFF;
627 SET GLOBAL innodb_cmp_per_index_enabled=ON;
628
629 SET GLOBAL innodb_compression_level=default;
630
631 --echo # create a table with page size=2K
632 --echo # create indexes on each column.(total 9 indexes)
633 let $block_size=2;
634 --source suite/innodb_zip/include/innodb_create_tab_indx.inc
635
636 --echo # check the stats of the table & size of the table
637 --echo # The size of the file with 0 compress = 65536
638 SET @inl_val=1;
639 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
640
641 --echo # for determintic resons simple data should be inserted.
642 --echo # insert some 100 records
643 let $i = 100;
644 --source suite/innodb_zip/include/innodb_load_data.inc
645
646 --echo # check the stats of the table & size of the table
647 --echo # The size of the file with 0 compress = 86016
648 SET @inl_val=1;
649 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
650
651 --echo # fetch the compressed page and check the stats
652 --source suite/innodb_zip/include/innodb_fetch_records.inc
653
654 --echo # check the stats of the table & size of the table
655 SET @inl_val=1;
656 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
657
658 DROP TABLE tab5;
659
660 --echo #******************************************************************
661 --echo # Test 3-4K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=Def with page size 4K
662 --echo #******************************************************************
663
664 --echo # reset the stat table before starting next testcase
665 SET GLOBAL innodb_cmp_per_index_enabled=OFF;
666 SET GLOBAL innodb_cmp_per_index_enabled=ON;
667
668 SET GLOBAL innodb_compression_level=default;
669
670 --echo # create a table with page size=4K
671 --echo # create indexes on each column.(total 9 indexes)
672 let $block_size=4;
673 --source suite/innodb_zip/include/innodb_create_tab_indx.inc
674
675 --echo # check the stats of the table & size of the table
676 --echo # The size of the file with 0 compress = 65536
677 SET @inl_val=1;
678 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
679
680 --echo # for determintic resons simple data should be inserted.
681 --echo # insert some 100 records
682 let $i = 100;
683 --source suite/innodb_zip/include/innodb_load_data.inc
684
685 --echo # check the stats of the table & size of the table
686 --echo # The size of the file with 0 compress = 86016
687 SET @inl_val=1;
688 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
689
690 --echo # fetch the compressed page and check the stats
691 --source suite/innodb_zip/include/innodb_fetch_records.inc
692
693 --echo # check the stats of the table & size of the table
694 SET @inl_val=1;
695 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
696
697 DROP TABLE tab5;
698
699 --echo #******************************************************************
700 --echo # Test 3-8K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=Def with page size 8K
701 --echo #******************************************************************
702
703 --echo # reset the stat table before starting next testcase
704 SET GLOBAL innodb_cmp_per_index_enabled=OFF;
705 SET GLOBAL innodb_cmp_per_index_enabled=ON;
706
707 SET GLOBAL innodb_compression_level=default;
708
709 --echo # create a table with page size=8K
710 --echo # create indexes on each column.(total 9 indexes)
711 let $block_size=8;
712 --source suite/innodb_zip/include/innodb_create_tab_indx.inc
713
714 --echo # check the stats of the table & size of the table
715 --echo # The size of the file with 0 compress = 122880
716 SET @inl_val=1;
717 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
718
719 --echo # for determintic resons simple data should be inserted.
720 --echo # insert some 100 records
721 let $i = 100;
722 --source suite/innodb_zip/include/innodb_load_data.inc
723
724 --echo # check the stats of the table & size of the table
725 --echo # The size of the file with 0 compress = 172032
726 SET @inl_val=1;
727 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
728
729 --echo # fetch the compressed page and check the stats
730 --source suite/innodb_zip/include/innodb_fetch_records.inc
731
732 --echo # check the stats of the table & size of the table
733 SET @inl_val=1;
734 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
735
736 DROP TABLE tab5;
737
738 --echo #******************************************************************
739 --echo # Test 3-16K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=Def with page size 16K
740 --echo #******************************************************************
741
742 --echo # reset the stat table before starting next testcase
743 SET GLOBAL innodb_cmp_per_index_enabled=OFF;
744 SET GLOBAL innodb_cmp_per_index_enabled=ON;
745
746 SET GLOBAL innodb_compression_level=default;
747
748 --echo # create a table with page size=16K
749 --echo # create indexes on each column.(total 9 indexes)
750 let $block_size=16;
751 --source suite/innodb_zip/include/innodb_create_tab_indx.inc
752
753 --echo # check the stats of the table & size of the table
754 --echo # The size of the file with 0 compress = 245760
755 SET @inl_val=1;
756 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
757
758 --echo # for determintic resons simple data should be inserted.
759 --echo # insert some 100 records
760 let $i = 100;
761 --source suite/innodb_zip/include/innodb_load_data.inc
762
763 --echo # check the stats of the table & size of the table
764 --echo # The size of the file with 0 compress = 344064
765 SET @inl_val=1;
766 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
767
768 --echo # fetch the compressed page and check the stats
769 --source suite/innodb_zip/include/innodb_fetch_records.inc
770
771 --echo # check the stats of the table & size of the table
772 SET @inl_val=1;
773 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
774
775 DROP TABLE tab5;
776
777 --echo #******************************************************************
778 --echo # Test 4: test the interaction between wl6347 & wl6344 (2.5 & 2.6)
779 --echo # This testcase is to verify the table/idex level compression stats
780 --echo # When the flags are set as follows
781 --echo # innodb_cmp_per_index_enabled=ON and
782 --echo # Innodb_compression_failure_threshold_pct=0
783 --echo # page size 1K,2K,4K,8K,16K
784 --echo # check the size and compression stats of the table tab5
785 --echo #******************************************************************
786
787 --echo # reset the stat table before starting next testcase
788 SET GLOBAL innodb_cmp_per_index_enabled=OFF;
789 SET GLOBAL innodb_cmp_per_index_enabled=ON;
790
791 --echo # set the flags
792 SET GLOBAL innodb_compression_failure_threshold_pct=0;
793 SET GLOBAL innodb_cmp_per_index_enabled=ON;
794
795 --echo # check the flags
796 SELECT @@innodb_cmp_per_index_enabled;
797 SELECT @@innodb_compression_failure_threshold_pct;
798 SELECT @@innodb_compression_level;
799
800 --echo #******************************************************************
801 --echo # Test 4-1K: innodb_cmp_per_index_enabled=ON and Innodb_compression_failure_threshold_pct=0 with page size 1K
802 --echo #******************************************************************
803
804 --echo # create a table with page size=1K
805 --echo # create indexes on each column.(total 9 indexes)
806 let $block_size=1;
807 --source suite/innodb_zip/include/innodb_create_tab_indx.inc
808
809 --echo # check the stats of the table & size of the table
810 SET @inl_val=1;
811 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
812
813 --echo # for determintic resons simple data should be inserted.
814 --echo # insert some 100 records
815 let $i = 100;
816 --source suite/innodb_zip/include/innodb_load_data.inc
817
818 --echo # check the stats of the table & size of the table
819 SET @inl_val=2;
820 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
821
822 --echo # fetch the compressed page and check the stats
823 --source suite/innodb_zip/include/innodb_fetch_records.inc
824
825 --echo # check the stats of the table & size of the table
826 SET @inl_val=2;
827 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
828
829 --echo # fetch the compressed same page once again and check the stats
830 --echo # the stat figures should be same as above query
831 --source suite/innodb_zip/include/innodb_fetch_records.inc
832
833 --echo # check the stats of the table & size of the table
834 SET @inl_val=2;
835 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
836
837 -- source include/restart_mysqld.inc
838
839 --echo # set the flag on (default off)
840 SET GLOBAL innodb_cmp_per_index_enabled=ON;
841
842 --echo # set the flags
843 SET GLOBAL innodb_compression_failure_threshold_pct=0;
844
845 --echo # fetch the compressed page and check the stats
846 --echo # The stats figure may be different/same for each restart.
847 --source suite/innodb_zip/include/innodb_fetch_records.inc
848
849 --echo # check the stats of the table
850 -- echo # testcase : pass = 1 fail = 0
851 SET @comp_val=0;
852 SET @uncomp_val=1;
853 --source suite/innodb_zip/include/innodb_stats_restart.inc
854
855 DROP TABLE tab5;
856
857 --echo #******************************************************************
858 --echo # Test 4-2K: innodb_cmp_per_index_enabled=ON and Innodb_compression_failure_threshold_pct=0 with page size 2K
859 --echo #******************************************************************
860
861 --echo # reset the stat table before starting next testcase
862 SET GLOBAL innodb_cmp_per_index_enabled=OFF;
863 SET GLOBAL innodb_cmp_per_index_enabled=ON;
864 --disable_query_log
865 SET GLOBAL innodb_read_only_compressed=OFF;
866 --enable_query_log
867
868 --echo # create a table with page size=2K
869 --echo # create indexes on each column.(total 9 indexes)
870 let $block_size=2;
871 --source suite/innodb_zip/include/innodb_create_tab_indx.inc
872
873 --echo # check the stats of the table & size of the table
874 SET @inl_val=1;
875 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
876
877 --echo # for determintic resons simple data should be inserted.
878 --echo # insert some 100 records
879 let $i = 100;
880 --source suite/innodb_zip/include/innodb_load_data.inc
881
882 --echo # check the stats of the table & size of the table
883 SET @inl_val=2;
884 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
885
886 --echo # fetch the compressed page and check the stats
887 --source suite/innodb_zip/include/innodb_fetch_records.inc
888
889 --echo # check the stats of the table & size of the table
890 SET @inl_val=2;
891 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
892
893 --echo # fetch the compressed same page once again and check the stats
894 --echo # the stat figures should be same as above query
895 --source suite/innodb_zip/include/innodb_fetch_records.inc
896
897 --echo # check the stats of the table & size of the table
898 SET @inl_val=2;
899 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
900
901 DROP TABLE tab5;
902
903 --echo #******************************************************************
904 --echo # Test 4-4K: innodb_cmp_per_index_enabled=ON and Innodb_compression_failure_threshold_pct=0 with page size 4K
905 --echo #******************************************************************
906
907 --echo # reset the stat table before starting next testcase
908 SET GLOBAL innodb_cmp_per_index_enabled=OFF;
909 SET GLOBAL innodb_cmp_per_index_enabled=ON;
910
911 --echo # create a table with page size=4K
912 --echo # create indexes on each column.(total 9 indexes)
913 let $block_size=4;
914 --source suite/innodb_zip/include/innodb_create_tab_indx.inc
915
916 --echo # check the stats of the table & size of the table
917 SET @inl_val=1;
918 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
919
920 --echo # for determintic resons simple data should be inserted.
921 --echo # insert some 100 records
922 let $i = 100;
923 --source suite/innodb_zip/include/innodb_load_data.inc
924
925 --echo # check the stats of the table & size of the table
926 SET @inl_val=1;
927 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
928
929 --echo # fetch the compressed page and check the stats
930 --source suite/innodb_zip/include/innodb_fetch_records.inc
931
932 --echo # check the stats of the table & size of the table
933 SET @inl_val=1;
934 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
935
936 --echo # fetch the compressed same page once again and check the stats
937 --echo # the stat figures should be same as above query
938 --source suite/innodb_zip/include/innodb_fetch_records.inc
939
940 --echo # check the stats of the table & size of the table
941 SET @inl_val=1;
942 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
943
944 DROP TABLE tab5;
945
946 --echo #******************************************************************
947 --echo # Test 4-8K: innodb_cmp_per_index_enabled=ON and Innodb_compression_failure_threshold_pct=0 with page size 8K
948 --echo #******************************************************************
949
950 --echo # reset the stat table before starting next testcase
951 SET GLOBAL innodb_cmp_per_index_enabled=OFF;
952 SET GLOBAL innodb_cmp_per_index_enabled=ON;
953
954 --echo # create a table with page size=8K
955 --echo # create indexes on each column.(total 9 indexes)
956 let $block_size=8;
957 --source suite/innodb_zip/include/innodb_create_tab_indx.inc
958
959 --echo # check the stats of the table & size of the table
960 SET @inl_val=1;
961 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
962
963 --echo # for determintic resons simple data should be inserted.
964 --echo # insert some 100 records
965 let $i = 100;
966 --source suite/innodb_zip/include/innodb_load_data.inc
967
968 --echo # check the stats of the table & size of the table
969 SET @inl_val=1;
970 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
971
972 --echo # fetch the compressed page and check the stats
973 --source suite/innodb_zip/include/innodb_fetch_records.inc
974
975 --echo # check the stats of the table & size of the table
976 SET @inl_val=1;
977 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
978
979 --echo # fetch the compressed same page once again and check the stats
980 --echo # the stat figures should be same as above query
981 --source suite/innodb_zip/include/innodb_fetch_records.inc
982
983 --echo # check the stats of the table & size of the table
984 SET @inl_val=1;
985 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
986
987 DROP TABLE tab5;
988
989 --echo #******************************************************************
990 --echo # Test 4-16K: innodb_cmp_per_index_enabled=ON and Innodb_compression_failure_threshold_pct=0 with page size 16K
991 --echo #******************************************************************
992
993 --echo # reset the stat table before starting next testcase
994 SET GLOBAL innodb_cmp_per_index_enabled=OFF;
995 SET GLOBAL innodb_cmp_per_index_enabled=ON;
996
997 --echo # create a table with page size=16K
998 --echo # create indexes on each column.(total 9 indexes)
999 let $block_size=16;
1000 --source suite/innodb_zip/include/innodb_create_tab_indx.inc
1001
1002 --echo # check the stats of the table & size of the table
1003 SET @inl_val=1;
1004 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
1005
1006 --echo # for determintic resons simple data should be inserted.
1007 --echo # insert some 100 records
1008 let $i = 100;
1009 --source suite/innodb_zip/include/innodb_load_data.inc
1010
1011 --echo # check the stats of the table & size of the table
1012 SET @inl_val=1;
1013 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
1014
1015 --echo # fetch the compressed page and check the stats
1016 --source suite/innodb_zip/include/innodb_fetch_records.inc
1017
1018 --echo # check the stats of the table & size of the table
1019 SET @inl_val=1;
1020 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
1021
1022 --echo # fetch the compressed same page once again and check the stats
1023 --echo # the stat figures should be same as above query
1024 --source suite/innodb_zip/include/innodb_fetch_records.inc
1025
1026 --echo # check the stats of the table & size of the table
1027 SET @inl_val=1;
1028 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
1029
1030 DROP TABLE tab5;
1031
1032 --echo #******************************************************************
1033 --echo # Test 5: test the interaction between wl6347 & wl6344 (2.7)
1034 --echo # This testcase is to verify the table/idex level compression stats
1035 --echo # When the flags are set as follows
1036 --echo # innodb_cmp_per_index_enabled=ON and
1037 --echo # Innodb_compression_failure_threshold_pct=10
1038 --echo # page size 1K,2K,4K,8K,16K
1039 --echo # check the size and compression stats of the table tab5
1040 --echo #******************************************************************
1041
1042 --echo # reset the stat table before starting next testcase
1043 SET GLOBAL innodb_cmp_per_index_enabled=OFF;
1044 SET GLOBAL innodb_cmp_per_index_enabled=ON;
1045
1046 --echo # set the flags
1047 SET GLOBAL innodb_cmp_per_index_enabled=ON;
1048 SET GLOBAL innodb_compression_failure_threshold_pct=10;
1049 SET GLOBAL innodb_compression_level=Default;
1050
1051
1052 --echo # check the flags
1053 SELECT @@innodb_cmp_per_index_enabled;
1054 SELECT @@innodb_compression_failure_threshold_pct;
1055 SELECT @@innodb_compression_level;
1056
1057 --echo #******************************************************************
1058 --echo # Test 5-1K: innodb_cmp_per_index_enabled=ON and Innodb_compression_failure_threshold_pct=10 with page size 1K
1059 --echo #******************************************************************
1060
1061 --echo # create a table with page size=1K
1062 --echo # create indexes on each column.(total 9 indexes)
1063 let $block_size=1;
1064 --source suite/innodb_zip/include/innodb_create_tab_indx.inc
1065
1066 --echo # check the stats of the table & size of the table
1067 SET @inl_val=1;
1068 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
1069
1070 --echo # for determintic resons simple data should be inserted.
1071 --echo # insert some 100 records
1072 let $i = 100;
1073 --source suite/innodb_zip/include/innodb_load_data.inc
1074
1075 --echo # check the stats of the table & size of the table
1076 SET @inl_val=2;
1077 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
1078
1079 --echo # fetch the compressed page and check the stats
1080 --source suite/innodb_zip/include/innodb_fetch_records.inc
1081
1082 --echo # check the stats of the table & size of the table
1083 SET @inl_val=2;
1084 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
1085
1086 --echo # fetch the compressed same page once again and check the stats
1087 --echo # the stat figures should be same as above query
1088 --source suite/innodb_zip/include/innodb_fetch_records.inc
1089
1090 --echo # check the stats of the table & size of the table
1091 SET @inl_val=2;
1092 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
1093
1094 -- source include/restart_mysqld.inc
1095
1096 --echo # set the flag on (default off)
1097 SET GLOBAL innodb_cmp_per_index_enabled=ON;
1098
1099 --echo # set the flags
1100 SET GLOBAL innodb_compression_failure_threshold_pct=10;
1101
1102 --echo # fetch the compressed page and check the stats
1103 --echo # The stats figure may be different/same for each restart.
1104 --source suite/innodb_zip/include/innodb_fetch_records.inc
1105
1106 --echo # check the stats of the table
1107 -- echo # testcase : pass = 1 fail = 0
1108 SET @comp_val=0;
1109 SET @uncomp_val=1;
1110 --source suite/innodb_zip/include/innodb_stats_restart.inc
1111
1112 DROP TABLE tab5;
1113
1114 --echo #******************************************************************
1115 --echo # Test 5-2K: innodb_cmp_per_index_enabled=ON and Innodb_compression_failure_threshold_pct=10 with page size 2K
1116 --echo #******************************************************************
1117
1118 --echo # reset the stat table before starting next testcase
1119 SET GLOBAL innodb_cmp_per_index_enabled=OFF;
1120 SET GLOBAL innodb_cmp_per_index_enabled=ON;
1121 SET GLOBAL innodb_compression_failure_threshold_pct=10;
1122 --disable_query_log
1123 SET @save_innodb_read_only_compressed=@@GLOBAL.innodb_read_only_compressed;
1124 SET GLOBAL innodb_read_only_compressed=OFF;
1125 --enable_query_log
1126
1127 --echo # create a table with page size=2K
1128 --echo # create indexes on each column.(total 9 indexes)
1129 let $block_size=2;
1130 --source suite/innodb_zip/include/innodb_create_tab_indx.inc
1131
1132 --echo # check the stats of the table & size of the table
1133 SET @inl_val=1;
1134 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
1135
1136 --echo # for determintic resons simple data should be inserted.
1137 --echo # insert some 100 records
1138 let $i = 100;
1139 --source suite/innodb_zip/include/innodb_load_data.inc
1140
1141 --echo # check the stats of the table & size of the table
1142 SET @inl_val=2;
1143 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
1144
1145 --echo # fetch the compressed page and check the stats
1146 --source suite/innodb_zip/include/innodb_fetch_records.inc
1147
1148 --echo # check the stats of the table & size of the table
1149 SET @inl_val=2;
1150 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
1151
1152 --echo # fetch the compressed same page once again and check the stats
1153 --echo # the stat figures should be same as above query
1154 --source suite/innodb_zip/include/innodb_fetch_records.inc
1155
1156 --echo # check the stats of the table & size of the table
1157 SET @inl_val=2;
1158 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
1159
1160 DROP TABLE tab5;
1161
1162 --echo #******************************************************************
1163 --echo # Test 5-4K: innodb_cmp_per_index_enabled=ON and Innodb_compression_failure_threshold_pct=10 with page size 4K
1164 --echo #******************************************************************
1165
1166 --echo # reset the stat table before starting next testcase
1167 SET GLOBAL innodb_cmp_per_index_enabled=OFF;
1168 SET GLOBAL innodb_cmp_per_index_enabled=ON;
1169
1170 --echo # create a table with page size=4K
1171 --echo # create indexes on each column.(total 9 indexes)
1172 let $block_size=4;
1173 --source suite/innodb_zip/include/innodb_create_tab_indx.inc
1174
1175 --echo # check the stats of the table & size of the table
1176 SET @inl_val=1;
1177 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
1178
1179 --echo # for determintic resons simple data should be inserted.
1180 --echo # insert some 100 records
1181 let $i = 100;
1182 --source suite/innodb_zip/include/innodb_load_data.inc
1183
1184 --echo # check the stats of the table & size of the table
1185 SET @inl_val=1;
1186 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
1187
1188 --echo # fetch the compressed page and check the stats
1189 --source suite/innodb_zip/include/innodb_fetch_records.inc
1190
1191 --echo # check the stats of the table & size of the table
1192 SET @inl_val=1;
1193 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
1194
1195 --echo # fetch the compressed same page once again and check the stats
1196 --echo # the stat figures should be same as above query
1197 --source suite/innodb_zip/include/innodb_fetch_records.inc
1198
1199 --echo # check the stats of the table & size of the table
1200 SET @inl_val=1;
1201 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
1202
1203 DROP TABLE tab5;
1204
1205 --echo #******************************************************************
1206 --echo # Test 5-8K: innodb_cmp_per_index_enabled=ON and Innodb_compression_failure_threshold_pct=10 with page size 8K
1207 --echo #******************************************************************
1208
1209 --echo # reset the stat table before starting next testcase
1210 SET GLOBAL innodb_cmp_per_index_enabled=OFF;
1211 SET GLOBAL innodb_cmp_per_index_enabled=ON;
1212 SET GLOBAL innodb_compression_failure_threshold_pct=10;
1213
1214 --echo # create a table with page size=8K
1215 --echo # create indexes on each column.(total 9 indexes)
1216 let $block_size=8;
1217 --source suite/innodb_zip/include/innodb_create_tab_indx.inc
1218
1219 --echo # check the stats of the table & size of the table
1220 SET @inl_val=1;
1221 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
1222
1223 --echo # for determintic resons simple data should be inserted.
1224 --echo # insert some 100 records
1225 let $i = 100;
1226 --source suite/innodb_zip/include/innodb_load_data.inc
1227
1228 --echo # check the stats of the table & size of the table
1229 SET @inl_val=1;
1230 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
1231
1232 --echo # fetch the compressed page and check the stats
1233 --source suite/innodb_zip/include/innodb_fetch_records.inc
1234
1235 --echo # check the stats of the table & size of the table
1236 SET @inl_val=1;
1237 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
1238
1239 --echo # fetch the compressed same page once again and check the stats
1240 --echo # the stat figures should be same as above query
1241 --source suite/innodb_zip/include/innodb_fetch_records.inc
1242
1243 --echo # check the stats of the table & size of the table
1244 SET @inl_val=1;
1245 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
1246
1247 DROP TABLE tab5;
1248
1249 --echo #******************************************************************
1250 --echo # Test 5-16K: innodb_cmp_per_index_enabled=ON and Innodb_compression_failure_threshold_pct=10 with page size 16K
1251 --echo #******************************************************************
1252
1253 --echo # reset the stat table before starting next testcase
1254 SET GLOBAL innodb_cmp_per_index_enabled=OFF;
1255 SET GLOBAL innodb_cmp_per_index_enabled=ON;
1256
1257 --echo # create a table with page size=16K
1258 --echo # create indexes on each column.(total 9 indexes)
1259 let $block_size=16;
1260 --source suite/innodb_zip/include/innodb_create_tab_indx.inc
1261
1262 --echo # check the stats of the table & size of the table
1263 SET @inl_val=1;
1264 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
1265
1266 --echo # for determintic resons simple data should be inserted.
1267 --echo # insert some 100 records
1268 let $i = 100;
1269 --source suite/innodb_zip/include/innodb_load_data.inc
1270
1271 --echo # check the stats of the table & size of the table
1272 SET @inl_val=1;
1273 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
1274
1275 --echo # fetch the compressed page and check the stats
1276 --source suite/innodb_zip/include/innodb_fetch_records.inc
1277
1278 --echo # check the stats of the table & size of the table
1279 SET @inl_val=1;
1280 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
1281
1282 --echo # fetch the compressed same page once again and check the stats
1283 --echo # the stat figures should be same as above query
1284 --source suite/innodb_zip/include/innodb_fetch_records.inc
1285
1286 --echo # check the stats of the table & size of the table
1287 SET @inl_val=1;
1288 --source suite/innodb_zip/include/innodb_stats_comp_index.inc
1289
1290 DROP TABLE tab5;
1291
1292 --echo #******************************************************************
1293 --echo # Test 6: Create multiple tables & indexes having same name in 2 diff DB's
1294 --echo # Check the stats of the table. (1.1)
1295 --echo #******************************************************************
1296
1297 --echo # reset the stat table before starting next testcase
1298 SET GLOBAL innodb_cmp_per_index_enabled=0;
1299 SET GLOBAL innodb_cmp_per_index_enabled=1;
1300
1301 SET GLOBAL innodb_compression_level=default;
1302 SET GLOBAL innodb_compression_failure_threshold_pct=default;
1303
1304
1305 --echo # create a table page size=1K
1306 CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB,
1307 col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT,
1308 col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255))
1309 ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
1310
1311 CREATE INDEX idx1 ON tab5(col_1(10));
1312
1313 --echo # check the stats of the table
1314 SELECT database_name,table_name,index_name,compress_ops,compress_ops_ok
1315 FROM information_schema.innodb_cmp_per_index
1316 WHERE database_name='test' and table_name='tab5'
1317 ORDER BY index_name,table_name,database_name ;
1318
1319 CREATE DATABASE sb;
1320 USE sb;
1321
1322 --echo # create a table page size=1K (testcase-1)
1323 CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB,
1324 col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT,
1325 col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255))
1326 ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
1327
1328 CREATE INDEX idx1 ON tab5(col_1(10));
1329
1330 SELECT database_name,table_name,index_name,compress_ops,compress_ops_ok
1331 FROM information_schema.innodb_cmp_per_index
1332 WHERE database_name='sb' and table_name='tab5'
1333 ORDER BY index_name,table_name,database_name ;
1334
1335 DROP TABLE tab5, test.tab5;
1336 DROP DATABASE sb;
1337
1338 --echo # reset the flags
1339 eval SET GLOBAL innodb_cmp_per_index_enabled=default;
1340 --disable_query_log
1341 eval SET GLOBAL innodb_compression_level=$innodb_compression_level;
1342 SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed;
1343 --enable_query_log
1344 eval SET GLOBAL innodb_compression_failure_threshold_pct=default;