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