mirror of
https://github.com/MariaDB/server.git
synced 2025-08-05 13:16:09 +03:00
e581396b7a
Before commit6112853cdain MySQL 4.1.1 introduced the parameter innodb_file_per_table, all InnoDB data was written to the InnoDB system tablespace (often named ibdata1). A serious design problem is that once the system tablespace has grown to some size, it cannot shrink even if the data inside it has been deleted. There are also other design problems, such as the server hang MDEV-29930 that should only be possible when using innodb_file_per_table=0 and innodb_undo_tablespaces=0 (storing both tables and undo logs in the InnoDB system tablespace). The parameter innodb_change_buffering was deprecated in commitb5852ffbee. Starting with commitbaf276e6d4(MDEV-19229) the number of innodb_undo_tablespaces can be increased, so that the undo logs can be moved out of the system tablespace of an existing installation. If all these things (tables, undo logs, and the change buffer) are removed from the InnoDB system tablespace, the only variable-size data structure inside it is the InnoDB data dictionary. DDL operations on .ibd files was optimized in commit86dc7b4d4c(MDEV-24626). That should have removed any thinkable performance advantage of using innodb_file_per_table=0. Since there should be no benefit of setting innodb_file_per_table=0, the parameter should be deprecated. Starting with MySQL 5.6 and MariaDB Server 10.0, the default value is innodb_file_per_table=1.
1345 lines
49 KiB
Plaintext
1345 lines
49 KiB
Plaintext
--echo #******************************************************************
|
|
--echo # Test 1: Test the interaction between stats and compression level
|
|
--echo # and logging of compressed pages configuration
|
|
--echo # This testcase is to verify the table/idex level compression stats
|
|
--echo # When the flags are set as follows
|
|
--echo # innodb_cmp_per_index_enabled=ON and innodb_compression_level=0
|
|
--echo # page size 1K,2K,4K,8K,16K
|
|
--echo # check the size and compression stats of the table tab5
|
|
--echo #******************************************************************
|
|
|
|
# This test case needs InnoDB.
|
|
-- source include/have_innodb.inc
|
|
-- source include/not_embedded.inc
|
|
-- source include/have_innodb_16k.inc
|
|
-- source include/big_test.inc
|
|
|
|
-- vertical_results
|
|
|
|
let MYSQLD_DATADIR=`SELECT @@datadir`;
|
|
let $innodb_compression_level = `SELECT @@global.innodb_compression_level`;
|
|
|
|
--echo # set the flags
|
|
SET GLOBAL innodb_cmp_per_index_enabled=ON;
|
|
SET GLOBAL innodb_compression_level=0;
|
|
--disable_query_log
|
|
SET GLOBAL innodb_read_only_compressed=OFF;
|
|
--enable_query_log
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 1-1K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=0 with page size 1K
|
|
--echo #******************************************************************
|
|
|
|
--echo # create a table with page size=1K
|
|
--echo # create indexes on each column.(total 9 indexes)
|
|
let $block_size=1;
|
|
--source suite/innodb_zip/include/innodb_create_tab_indx.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # for deterministic reasons simple data should be inserted.
|
|
--echo # insert some 100 records
|
|
let $i = 100;
|
|
--source suite/innodb_zip/include/innodb_load_data.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=2;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=2;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed same page once again and check the stats
|
|
--echo # the stat figures should be same as above query
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=2;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
-- source include/restart_mysqld.inc
|
|
|
|
--echo # set the flag on (default off)
|
|
SET GLOBAL innodb_cmp_per_index_enabled=ON;
|
|
--disable_query_log
|
|
SET GLOBAL innodb_read_only_compressed=OFF;
|
|
--enable_query_log
|
|
|
|
--echo # set the flags
|
|
SET GLOBAL innodb_compression_level=0;
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--echo # The stats figure may be different/same for each restart.
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table
|
|
-- echo # testcase : pass = 1 fail = 0
|
|
SET @comp_val=0;
|
|
SET @uncomp_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_restart.inc
|
|
|
|
DROP TABLE tab5;
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 1-2K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=0 with page size 2K
|
|
--echo #******************************************************************
|
|
|
|
--echo # reset the stat table before starting next testcase
|
|
SET GLOBAL innodb_cmp_per_index_enabled=0;
|
|
SET GLOBAL innodb_cmp_per_index_enabled=1;
|
|
|
|
--echo # create a table with page size=2K
|
|
--echo # create indexes on each column.(total 9 indexes)
|
|
let $block_size=2;
|
|
--source suite/innodb_zip/include/innodb_create_tab_indx.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # for determintic resons simple data should be inserted.
|
|
--echo # insert some 100 records
|
|
let $i = 100;
|
|
--source suite/innodb_zip/include/innodb_load_data.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=2;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=2;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed same page once again and check the stats
|
|
--echo # the stat figures should be same as above query
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=2;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
-- source include/restart_mysqld.inc
|
|
|
|
--echo # set the flag on (default off)
|
|
SET GLOBAL innodb_cmp_per_index_enabled=ON;
|
|
|
|
--echo # set the flags
|
|
SET GLOBAL innodb_compression_level=0;
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--echo # The stats figure may be different/same for each restart.
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table
|
|
-- echo # testcase : pass = 1 fail = 0
|
|
SET @comp_val=0;
|
|
SET @uncomp_val=2;
|
|
--source suite/innodb_zip/include/innodb_stats_restart.inc
|
|
|
|
DROP TABLE tab5;
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 1-4K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=0 with page size 4K
|
|
--echo #******************************************************************
|
|
|
|
--echo # reset the stat table before starting next testcase
|
|
SET GLOBAL innodb_cmp_per_index_enabled=0;
|
|
SET GLOBAL innodb_cmp_per_index_enabled=1;
|
|
--disable_query_log
|
|
SET GLOBAL innodb_read_only_compressed=OFF;
|
|
--enable_query_log
|
|
|
|
--echo # create a table with page size=4K
|
|
--echo # create indexes on each column.(total 9 indexes)
|
|
let $block_size=4;
|
|
--source suite/innodb_zip/include/innodb_create_tab_indx.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # for determintic resons simple data should be inserted.
|
|
--echo # insert some 100 records
|
|
let $i = 100;
|
|
--source suite/innodb_zip/include/innodb_load_data.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed same page once again and check the stats
|
|
--echo # the stat figures should be same as above query
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
DROP TABLE tab5;
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 1-8K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=0 with page size 8K
|
|
--echo #******************************************************************
|
|
|
|
--echo # reset the stat table before starting next testcase
|
|
SET GLOBAL innodb_cmp_per_index_enabled=0;
|
|
SET GLOBAL innodb_cmp_per_index_enabled=1;
|
|
|
|
--echo # create a table with page size=8K
|
|
--echo # create indexes on each column.(total 9 indexes)
|
|
let $block_size=8;
|
|
--source suite/innodb_zip/include/innodb_create_tab_indx.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # for determintic resons simple data should be inserted.
|
|
--echo # insert some 100 records
|
|
let $i = 100;
|
|
--source suite/innodb_zip/include/innodb_load_data.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed same page once again and check the stats
|
|
--echo # the stat figures should be same as above query
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
DROP TABLE tab5;
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 1-16K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=0 with page size 16K
|
|
--echo #******************************************************************
|
|
|
|
--echo # reset the stat table before starting next testcase
|
|
SET GLOBAL innodb_cmp_per_index_enabled=0;
|
|
SET GLOBAL innodb_cmp_per_index_enabled=1;
|
|
|
|
--echo # create a table with page size=16K
|
|
--echo # create indexes on each column.(total 9 indexes)
|
|
let $block_size=16;
|
|
--source suite/innodb_zip/include/innodb_create_tab_indx.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # for determintic resons simple data should be inserted.
|
|
--echo # insert some 100 records
|
|
let $i = 100;
|
|
--source suite/innodb_zip/include/innodb_load_data.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed same page once again and check the stats
|
|
--echo # the stat figures should be same as above query
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
DROP TABLE tab5;
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 2: test the interaction between wl6347 & wl6344 (2.2)
|
|
--echo # This testcase is to verify the table/idex level compression stats
|
|
--echo # When the flags are set as follows
|
|
--echo # innodb_cmp_per_index_enabled=ON and innodb_compression_level=9
|
|
--echo # page size 1K,2K,4K,8K,16K
|
|
--echo # check the size and compression stats of the table tab5
|
|
--echo #******************************************************************
|
|
|
|
--echo # reset the stat table before starting next testcase
|
|
SET GLOBAL innodb_cmp_per_index_enabled=0;
|
|
SET GLOBAL innodb_cmp_per_index_enabled=1;
|
|
|
|
--echo # set the flags
|
|
SET GLOBAL innodb_compression_level=9;
|
|
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 2-1K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=9 with page size 1K
|
|
--echo #******************************************************************
|
|
|
|
--echo # create a table with page size=1K
|
|
--echo # create indexes on each column.(total 9 indexes)
|
|
let $block_size=1;
|
|
--source suite/innodb_zip/include/innodb_create_tab_indx.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
--echo # The size of the file with 0 compress = 65536
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # for determintic resons simple data should be inserted.
|
|
--echo # insert some 100 records
|
|
let $i = 100;
|
|
--source suite/innodb_zip/include/innodb_load_data.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
--echo # The size of the file with 0 compress = 2097152
|
|
SET @inl_val=2;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=2;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed same page once again and check the stats
|
|
--echo # the stat figures should be same as above query
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=2;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
-- source include/restart_mysqld.inc
|
|
|
|
--echo # set the flag on (default off)
|
|
SET GLOBAL innodb_cmp_per_index_enabled=ON;
|
|
|
|
--echo # set the flags
|
|
SET GLOBAL innodb_compression_level=9;
|
|
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--echo # The stats figure may be different/same for each restart.
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table
|
|
-- echo # testcase : pass = 1 fail = 0
|
|
SET @comp_val=0;
|
|
SET @uncomp_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_restart.inc
|
|
|
|
DROP TABLE tab5;
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 2-2K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=9 with page size 2K
|
|
--echo #******************************************************************
|
|
|
|
--echo # reset the stat table before starting next testcase
|
|
SET GLOBAL innodb_cmp_per_index_enabled=OFF;
|
|
SET GLOBAL innodb_cmp_per_index_enabled=ON;
|
|
--disable_query_log
|
|
SET GLOBAL innodb_read_only_compressed=OFF;
|
|
--enable_query_log
|
|
|
|
--echo # create a table with page size=2K
|
|
--echo # create indexes on each column.(total 9 indexes)
|
|
let $block_size=2;
|
|
--source suite/innodb_zip/include/innodb_create_tab_indx.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
--echo # The size of the file with 0 compress = 65536
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # for determintic resons simple data should be inserted.
|
|
--echo # insert some 100 records
|
|
let $i = 100;
|
|
--source suite/innodb_zip/include/innodb_load_data.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
--echo # The size of the file with 0 compress = 2097152
|
|
SET @inl_val=2;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=2;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed same page once again and check the stats
|
|
--echo # the stat figures should be same as above query
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=2;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
-- source include/restart_mysqld.inc
|
|
|
|
--echo # set the flag on (default off)
|
|
SET GLOBAL innodb_cmp_per_index_enabled=ON;
|
|
|
|
--echo # set the flags
|
|
SET GLOBAL innodb_compression_level=9;
|
|
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--echo # The stats figure may be different/same for each restart.
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table
|
|
-- echo # testcase : pass = 1 fail = 0
|
|
SET @comp_val=0;
|
|
SET @uncomp_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_restart.inc
|
|
|
|
DROP TABLE tab5;
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 2-4K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=9 with page size 4K
|
|
--echo #******************************************************************
|
|
|
|
--echo # reset the stat table before starting next testcase
|
|
SET GLOBAL innodb_cmp_per_index_enabled=OFF;
|
|
SET GLOBAL innodb_cmp_per_index_enabled=ON;
|
|
--disable_query_log
|
|
SET GLOBAL innodb_read_only_compressed=OFF;
|
|
--enable_query_log
|
|
|
|
--echo # create a table with page size=4K
|
|
--echo # create indexes on each column.(total 9 indexes)
|
|
let $block_size=4;
|
|
--source suite/innodb_zip/include/innodb_create_tab_indx.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
--echo # The size of the file with 0 compress = 65536
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # for determintic resons simple data should be inserted.
|
|
--echo # insert some 100 records
|
|
let $i = 100;
|
|
--source suite/innodb_zip/include/innodb_load_data.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
--echo # The size of the file with 0 compress = 159744
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed same page once again and check the stats
|
|
--echo # the stat figures should be same as above query
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
DROP TABLE tab5;
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 2-8K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=9 with page size 8K
|
|
--echo #******************************************************************
|
|
|
|
--echo # reset the stat table before starting next testcase
|
|
SET GLOBAL innodb_cmp_per_index_enabled=OFF;
|
|
SET GLOBAL innodb_cmp_per_index_enabled=ON;
|
|
|
|
--echo # create a table with page size=8K
|
|
--echo # create indexes on each column.(total 9 indexes)
|
|
let $block_size=8;
|
|
--source suite/innodb_zip/include/innodb_create_tab_indx.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
--echo # The size of the file with 0 compress = 122880
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # for determintic resons simple data should be inserted.
|
|
--echo # insert some 100 records
|
|
let $i = 100;
|
|
--source suite/innodb_zip/include/innodb_load_data.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
--echo # The size of the file with 0 compress = 212992
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed same page once again and check the stats
|
|
--echo # the stat figures should be same as above query
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
DROP TABLE tab5;
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 2-16K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=9 with page size 16K
|
|
--echo #******************************************************************
|
|
|
|
--echo # reset the stat table before starting next testcase
|
|
SET GLOBAL innodb_cmp_per_index_enabled=OFF;
|
|
SET GLOBAL innodb_cmp_per_index_enabled=ON;
|
|
|
|
--echo # create a table with page size=16K
|
|
--echo # create indexes on each column.(total 9 indexes)
|
|
let $block_size=16;
|
|
--source suite/innodb_zip/include/innodb_create_tab_indx.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
--echo # The size of the file with 0 compress = 245760
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # for determintic resons simple data should be inserted.
|
|
--echo # insert some 100 records
|
|
let $i = 100;
|
|
--source suite/innodb_zip/include/innodb_load_data.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
--echo # The size of the file with 0 compress = 344064
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed same page once again and check the stats
|
|
--echo # the stat figures should be same as above query
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
DROP TABLE tab5;
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 3: test the interaction between wl6347 & wl6344 (2.3)
|
|
--echo # This testcase is to verify the table/idex level compression stats
|
|
--echo # When the flags are set as follows
|
|
--echo # innodb_cmp_per_index_enabled=ON and
|
|
--echo # innodb_compression_level=6 (default)
|
|
--echo # page size 1K,2K,4K,8K,16K
|
|
--echo # check the size and compression stats of the table tab5
|
|
--echo #******************************************************************
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 3-1K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=Def with page size 1K
|
|
--echo #******************************************************************
|
|
|
|
--echo # reset the stat table before starting next testcase
|
|
SET GLOBAL innodb_cmp_per_index_enabled=OFF;
|
|
SET GLOBAL innodb_cmp_per_index_enabled=ON;
|
|
|
|
SET GLOBAL innodb_compression_level=default;
|
|
|
|
--echo # create a table with page size=1K
|
|
--echo # create indexes on each column.(total 9 indexes)
|
|
let $block_size=1;
|
|
--source suite/innodb_zip/include/innodb_create_tab_indx.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
--echo # The size of the file with 0 compress = 65536
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # for determintic resons simple data should be inserted.
|
|
--echo # insert some 100 records
|
|
let $i = 100;
|
|
--source suite/innodb_zip/include/innodb_load_data.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
--echo # The size of the file with 0 compress = 65536
|
|
SET @inl_val=2;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=2;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed same page once again and check the stats
|
|
--echo # the stat figures should be same as above query
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=2;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
DROP TABLE tab5;
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 3-2K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=Def with page size 2K
|
|
--echo #******************************************************************
|
|
|
|
--echo # reset the stat table before starting next testcase
|
|
SET GLOBAL innodb_cmp_per_index_enabled=OFF;
|
|
SET GLOBAL innodb_cmp_per_index_enabled=ON;
|
|
|
|
SET GLOBAL innodb_compression_level=default;
|
|
|
|
--echo # create a table with page size=2K
|
|
--echo # create indexes on each column.(total 9 indexes)
|
|
let $block_size=2;
|
|
--source suite/innodb_zip/include/innodb_create_tab_indx.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
--echo # The size of the file with 0 compress = 65536
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # for determintic resons simple data should be inserted.
|
|
--echo # insert some 100 records
|
|
let $i = 100;
|
|
--source suite/innodb_zip/include/innodb_load_data.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
--echo # The size of the file with 0 compress = 86016
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
DROP TABLE tab5;
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 3-4K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=Def with page size 4K
|
|
--echo #******************************************************************
|
|
|
|
--echo # reset the stat table before starting next testcase
|
|
SET GLOBAL innodb_cmp_per_index_enabled=OFF;
|
|
SET GLOBAL innodb_cmp_per_index_enabled=ON;
|
|
|
|
SET GLOBAL innodb_compression_level=default;
|
|
|
|
--echo # create a table with page size=4K
|
|
--echo # create indexes on each column.(total 9 indexes)
|
|
let $block_size=4;
|
|
--source suite/innodb_zip/include/innodb_create_tab_indx.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
--echo # The size of the file with 0 compress = 65536
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # for determintic resons simple data should be inserted.
|
|
--echo # insert some 100 records
|
|
let $i = 100;
|
|
--source suite/innodb_zip/include/innodb_load_data.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
--echo # The size of the file with 0 compress = 86016
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
DROP TABLE tab5;
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 3-8K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=Def with page size 8K
|
|
--echo #******************************************************************
|
|
|
|
--echo # reset the stat table before starting next testcase
|
|
SET GLOBAL innodb_cmp_per_index_enabled=OFF;
|
|
SET GLOBAL innodb_cmp_per_index_enabled=ON;
|
|
|
|
SET GLOBAL innodb_compression_level=default;
|
|
|
|
--echo # create a table with page size=8K
|
|
--echo # create indexes on each column.(total 9 indexes)
|
|
let $block_size=8;
|
|
--source suite/innodb_zip/include/innodb_create_tab_indx.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
--echo # The size of the file with 0 compress = 122880
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # for determintic resons simple data should be inserted.
|
|
--echo # insert some 100 records
|
|
let $i = 100;
|
|
--source suite/innodb_zip/include/innodb_load_data.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
--echo # The size of the file with 0 compress = 172032
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
DROP TABLE tab5;
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 3-16K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=Def with page size 16K
|
|
--echo #******************************************************************
|
|
|
|
--echo # reset the stat table before starting next testcase
|
|
SET GLOBAL innodb_cmp_per_index_enabled=OFF;
|
|
SET GLOBAL innodb_cmp_per_index_enabled=ON;
|
|
|
|
SET GLOBAL innodb_compression_level=default;
|
|
|
|
--echo # create a table with page size=16K
|
|
--echo # create indexes on each column.(total 9 indexes)
|
|
let $block_size=16;
|
|
--source suite/innodb_zip/include/innodb_create_tab_indx.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
--echo # The size of the file with 0 compress = 245760
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # for determintic resons simple data should be inserted.
|
|
--echo # insert some 100 records
|
|
let $i = 100;
|
|
--source suite/innodb_zip/include/innodb_load_data.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
--echo # The size of the file with 0 compress = 344064
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
DROP TABLE tab5;
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 4: test the interaction between wl6347 & wl6344 (2.5 & 2.6)
|
|
--echo # This testcase is to verify the table/idex level compression stats
|
|
--echo # When the flags are set as follows
|
|
--echo # innodb_cmp_per_index_enabled=ON and
|
|
--echo # Innodb_compression_failure_threshold_pct=0
|
|
--echo # page size 1K,2K,4K,8K,16K
|
|
--echo # check the size and compression stats of the table tab5
|
|
--echo #******************************************************************
|
|
|
|
--echo # reset the stat table before starting next testcase
|
|
SET GLOBAL innodb_cmp_per_index_enabled=OFF;
|
|
SET GLOBAL innodb_cmp_per_index_enabled=ON;
|
|
|
|
--echo # set the flags
|
|
SET GLOBAL innodb_compression_failure_threshold_pct=0;
|
|
SET GLOBAL innodb_cmp_per_index_enabled=ON;
|
|
|
|
--echo # check the flags
|
|
SELECT @@innodb_cmp_per_index_enabled;
|
|
SELECT @@innodb_compression_failure_threshold_pct;
|
|
SELECT @@innodb_compression_level;
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 4-1K: innodb_cmp_per_index_enabled=ON and Innodb_compression_failure_threshold_pct=0 with page size 1K
|
|
--echo #******************************************************************
|
|
|
|
--echo # create a table with page size=1K
|
|
--echo # create indexes on each column.(total 9 indexes)
|
|
let $block_size=1;
|
|
--source suite/innodb_zip/include/innodb_create_tab_indx.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # for determintic resons simple data should be inserted.
|
|
--echo # insert some 100 records
|
|
let $i = 100;
|
|
--source suite/innodb_zip/include/innodb_load_data.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=2;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=2;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed same page once again and check the stats
|
|
--echo # the stat figures should be same as above query
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=2;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
-- source include/restart_mysqld.inc
|
|
|
|
--echo # set the flag on (default off)
|
|
SET GLOBAL innodb_cmp_per_index_enabled=ON;
|
|
|
|
--echo # set the flags
|
|
SET GLOBAL innodb_compression_failure_threshold_pct=0;
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--echo # The stats figure may be different/same for each restart.
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table
|
|
-- echo # testcase : pass = 1 fail = 0
|
|
SET @comp_val=0;
|
|
SET @uncomp_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_restart.inc
|
|
|
|
DROP TABLE tab5;
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 4-2K: innodb_cmp_per_index_enabled=ON and Innodb_compression_failure_threshold_pct=0 with page size 2K
|
|
--echo #******************************************************************
|
|
|
|
--echo # reset the stat table before starting next testcase
|
|
SET GLOBAL innodb_cmp_per_index_enabled=OFF;
|
|
SET GLOBAL innodb_cmp_per_index_enabled=ON;
|
|
--disable_query_log
|
|
SET GLOBAL innodb_read_only_compressed=OFF;
|
|
--enable_query_log
|
|
|
|
--echo # create a table with page size=2K
|
|
--echo # create indexes on each column.(total 9 indexes)
|
|
let $block_size=2;
|
|
--source suite/innodb_zip/include/innodb_create_tab_indx.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # for determintic resons simple data should be inserted.
|
|
--echo # insert some 100 records
|
|
let $i = 100;
|
|
--source suite/innodb_zip/include/innodb_load_data.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=2;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=2;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed same page once again and check the stats
|
|
--echo # the stat figures should be same as above query
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=2;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
DROP TABLE tab5;
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 4-4K: innodb_cmp_per_index_enabled=ON and Innodb_compression_failure_threshold_pct=0 with page size 4K
|
|
--echo #******************************************************************
|
|
|
|
--echo # reset the stat table before starting next testcase
|
|
SET GLOBAL innodb_cmp_per_index_enabled=OFF;
|
|
SET GLOBAL innodb_cmp_per_index_enabled=ON;
|
|
|
|
--echo # create a table with page size=4K
|
|
--echo # create indexes on each column.(total 9 indexes)
|
|
let $block_size=4;
|
|
--source suite/innodb_zip/include/innodb_create_tab_indx.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # for determintic resons simple data should be inserted.
|
|
--echo # insert some 100 records
|
|
let $i = 100;
|
|
--source suite/innodb_zip/include/innodb_load_data.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed same page once again and check the stats
|
|
--echo # the stat figures should be same as above query
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
DROP TABLE tab5;
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 4-8K: innodb_cmp_per_index_enabled=ON and Innodb_compression_failure_threshold_pct=0 with page size 8K
|
|
--echo #******************************************************************
|
|
|
|
--echo # reset the stat table before starting next testcase
|
|
SET GLOBAL innodb_cmp_per_index_enabled=OFF;
|
|
SET GLOBAL innodb_cmp_per_index_enabled=ON;
|
|
|
|
--echo # create a table with page size=8K
|
|
--echo # create indexes on each column.(total 9 indexes)
|
|
let $block_size=8;
|
|
--source suite/innodb_zip/include/innodb_create_tab_indx.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # for determintic resons simple data should be inserted.
|
|
--echo # insert some 100 records
|
|
let $i = 100;
|
|
--source suite/innodb_zip/include/innodb_load_data.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed same page once again and check the stats
|
|
--echo # the stat figures should be same as above query
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
DROP TABLE tab5;
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 4-16K: innodb_cmp_per_index_enabled=ON and Innodb_compression_failure_threshold_pct=0 with page size 16K
|
|
--echo #******************************************************************
|
|
|
|
--echo # reset the stat table before starting next testcase
|
|
SET GLOBAL innodb_cmp_per_index_enabled=OFF;
|
|
SET GLOBAL innodb_cmp_per_index_enabled=ON;
|
|
|
|
--echo # create a table with page size=16K
|
|
--echo # create indexes on each column.(total 9 indexes)
|
|
let $block_size=16;
|
|
--source suite/innodb_zip/include/innodb_create_tab_indx.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # for determintic resons simple data should be inserted.
|
|
--echo # insert some 100 records
|
|
let $i = 100;
|
|
--source suite/innodb_zip/include/innodb_load_data.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed same page once again and check the stats
|
|
--echo # the stat figures should be same as above query
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
DROP TABLE tab5;
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 5: test the interaction between wl6347 & wl6344 (2.7)
|
|
--echo # This testcase is to verify the table/idex level compression stats
|
|
--echo # When the flags are set as follows
|
|
--echo # innodb_cmp_per_index_enabled=ON and
|
|
--echo # Innodb_compression_failure_threshold_pct=10
|
|
--echo # page size 1K,2K,4K,8K,16K
|
|
--echo # check the size and compression stats of the table tab5
|
|
--echo #******************************************************************
|
|
|
|
--echo # reset the stat table before starting next testcase
|
|
SET GLOBAL innodb_cmp_per_index_enabled=OFF;
|
|
SET GLOBAL innodb_cmp_per_index_enabled=ON;
|
|
|
|
--echo # set the flags
|
|
SET GLOBAL innodb_cmp_per_index_enabled=ON;
|
|
SET GLOBAL innodb_compression_failure_threshold_pct=10;
|
|
SET GLOBAL innodb_compression_level=Default;
|
|
|
|
|
|
--echo # check the flags
|
|
SELECT @@innodb_cmp_per_index_enabled;
|
|
SELECT @@innodb_compression_failure_threshold_pct;
|
|
SELECT @@innodb_compression_level;
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 5-1K: innodb_cmp_per_index_enabled=ON and Innodb_compression_failure_threshold_pct=10 with page size 1K
|
|
--echo #******************************************************************
|
|
|
|
--echo # create a table with page size=1K
|
|
--echo # create indexes on each column.(total 9 indexes)
|
|
let $block_size=1;
|
|
--source suite/innodb_zip/include/innodb_create_tab_indx.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # for determintic resons simple data should be inserted.
|
|
--echo # insert some 100 records
|
|
let $i = 100;
|
|
--source suite/innodb_zip/include/innodb_load_data.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=2;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=2;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed same page once again and check the stats
|
|
--echo # the stat figures should be same as above query
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=2;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
-- source include/restart_mysqld.inc
|
|
|
|
--echo # set the flag on (default off)
|
|
SET GLOBAL innodb_cmp_per_index_enabled=ON;
|
|
|
|
--echo # set the flags
|
|
SET GLOBAL innodb_compression_failure_threshold_pct=10;
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--echo # The stats figure may be different/same for each restart.
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table
|
|
-- echo # testcase : pass = 1 fail = 0
|
|
SET @comp_val=0;
|
|
SET @uncomp_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_restart.inc
|
|
|
|
DROP TABLE tab5;
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 5-2K: innodb_cmp_per_index_enabled=ON and Innodb_compression_failure_threshold_pct=10 with page size 2K
|
|
--echo #******************************************************************
|
|
|
|
--echo # reset the stat table before starting next testcase
|
|
SET GLOBAL innodb_cmp_per_index_enabled=OFF;
|
|
SET GLOBAL innodb_cmp_per_index_enabled=ON;
|
|
SET GLOBAL innodb_compression_failure_threshold_pct=10;
|
|
--disable_query_log
|
|
SET @save_innodb_read_only_compressed=@@GLOBAL.innodb_read_only_compressed;
|
|
SET GLOBAL innodb_read_only_compressed=OFF;
|
|
--enable_query_log
|
|
|
|
--echo # create a table with page size=2K
|
|
--echo # create indexes on each column.(total 9 indexes)
|
|
let $block_size=2;
|
|
--source suite/innodb_zip/include/innodb_create_tab_indx.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # for determintic resons simple data should be inserted.
|
|
--echo # insert some 100 records
|
|
let $i = 100;
|
|
--source suite/innodb_zip/include/innodb_load_data.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=2;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=2;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed same page once again and check the stats
|
|
--echo # the stat figures should be same as above query
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=2;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
DROP TABLE tab5;
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 5-4K: innodb_cmp_per_index_enabled=ON and Innodb_compression_failure_threshold_pct=10 with page size 4K
|
|
--echo #******************************************************************
|
|
|
|
--echo # reset the stat table before starting next testcase
|
|
SET GLOBAL innodb_cmp_per_index_enabled=OFF;
|
|
SET GLOBAL innodb_cmp_per_index_enabled=ON;
|
|
|
|
--echo # create a table with page size=4K
|
|
--echo # create indexes on each column.(total 9 indexes)
|
|
let $block_size=4;
|
|
--source suite/innodb_zip/include/innodb_create_tab_indx.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # for determintic resons simple data should be inserted.
|
|
--echo # insert some 100 records
|
|
let $i = 100;
|
|
--source suite/innodb_zip/include/innodb_load_data.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed same page once again and check the stats
|
|
--echo # the stat figures should be same as above query
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
DROP TABLE tab5;
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 5-8K: innodb_cmp_per_index_enabled=ON and Innodb_compression_failure_threshold_pct=10 with page size 8K
|
|
--echo #******************************************************************
|
|
|
|
--echo # reset the stat table before starting next testcase
|
|
SET GLOBAL innodb_cmp_per_index_enabled=OFF;
|
|
SET GLOBAL innodb_cmp_per_index_enabled=ON;
|
|
SET GLOBAL innodb_compression_failure_threshold_pct=10;
|
|
|
|
--echo # create a table with page size=8K
|
|
--echo # create indexes on each column.(total 9 indexes)
|
|
let $block_size=8;
|
|
--source suite/innodb_zip/include/innodb_create_tab_indx.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # for determintic resons simple data should be inserted.
|
|
--echo # insert some 100 records
|
|
let $i = 100;
|
|
--source suite/innodb_zip/include/innodb_load_data.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed same page once again and check the stats
|
|
--echo # the stat figures should be same as above query
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
DROP TABLE tab5;
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 5-16K: innodb_cmp_per_index_enabled=ON and Innodb_compression_failure_threshold_pct=10 with page size 16K
|
|
--echo #******************************************************************
|
|
|
|
--echo # reset the stat table before starting next testcase
|
|
SET GLOBAL innodb_cmp_per_index_enabled=OFF;
|
|
SET GLOBAL innodb_cmp_per_index_enabled=ON;
|
|
|
|
--echo # create a table with page size=16K
|
|
--echo # create indexes on each column.(total 9 indexes)
|
|
let $block_size=16;
|
|
--source suite/innodb_zip/include/innodb_create_tab_indx.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # for determintic resons simple data should be inserted.
|
|
--echo # insert some 100 records
|
|
let $i = 100;
|
|
--source suite/innodb_zip/include/innodb_load_data.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed page and check the stats
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
--echo # fetch the compressed same page once again and check the stats
|
|
--echo # the stat figures should be same as above query
|
|
--source suite/innodb_zip/include/innodb_fetch_records.inc
|
|
|
|
--echo # check the stats of the table & size of the table
|
|
SET @inl_val=1;
|
|
--source suite/innodb_zip/include/innodb_stats_comp_index.inc
|
|
|
|
DROP TABLE tab5;
|
|
|
|
--echo #******************************************************************
|
|
--echo # Test 6: Create multiple tables & indexes having same name in 2 diff DB's
|
|
--echo # Check the stats of the table. (1.1)
|
|
--echo #******************************************************************
|
|
|
|
--echo # reset the stat table before starting next testcase
|
|
SET GLOBAL innodb_cmp_per_index_enabled=0;
|
|
SET GLOBAL innodb_cmp_per_index_enabled=1;
|
|
|
|
SET GLOBAL innodb_compression_level=default;
|
|
SET GLOBAL innodb_compression_failure_threshold_pct=default;
|
|
|
|
|
|
--echo # create a table page size=1K
|
|
CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB,
|
|
col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT,
|
|
col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255))
|
|
ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
|
|
|
|
CREATE INDEX idx1 ON tab5(col_1(10));
|
|
|
|
--echo # check the stats of the table
|
|
SELECT database_name,table_name,index_name,compress_ops,compress_ops_ok
|
|
FROM information_schema.innodb_cmp_per_index
|
|
WHERE database_name='test' and table_name='tab5'
|
|
ORDER BY index_name,table_name,database_name ;
|
|
|
|
CREATE DATABASE sb;
|
|
USE sb;
|
|
|
|
--echo # create a table page size=1K (testcase-1)
|
|
CREATE TABLE tab5(col_1 TINYBLOB, col_2 TINYTEXT,col_3 BLOB,
|
|
col_4 TEXT,col_5 MEDIUMBLOB,col_6 MEDIUMTEXT,
|
|
col_7 LONGBLOB,col_8 LONGTEXT,col_9 VARCHAR(255))
|
|
ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
|
|
|
|
CREATE INDEX idx1 ON tab5(col_1(10));
|
|
|
|
SELECT database_name,table_name,index_name,compress_ops,compress_ops_ok
|
|
FROM information_schema.innodb_cmp_per_index
|
|
WHERE database_name='sb' and table_name='tab5'
|
|
ORDER BY index_name,table_name,database_name ;
|
|
|
|
DROP TABLE tab5, test.tab5;
|
|
DROP DATABASE sb;
|
|
|
|
--echo # reset the flags
|
|
eval SET GLOBAL innodb_cmp_per_index_enabled=default;
|
|
--disable_query_log
|
|
eval SET GLOBAL innodb_compression_level=$innodb_compression_level;
|
|
SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed;
|
|
--enable_query_log
|
|
eval SET GLOBAL innodb_compression_failure_threshold_pct=default;
|