Debian Codegraph @

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