| 1 |
#******************************************************************* |
| 2 |
# This testcase is to test the funcitionality of wl#6344 |
| 3 |
# When the innodb_compression_level=0 create a table with page size |
| 4 |
# 1K and load data |
| 5 |
# When the innodb_compression_level=9 create a table with page size |
| 6 |
# 1K and load data |
| 7 |
# compare the size of the both tables. |
| 8 |
# The size of the table when compression level=0 should be |
| 9 |
# greater than the |
| 10 |
# the size of the table when compression level=9 |
| 11 |
#******************************************************************* |
| 12 |
--source include/have_innodb_max_16k.inc |
| 13 |
|
| 14 |
USE test; |
| 15 |
DROP TABLE IF EXISTS tab5; |
| 16 |
DROP TABLE IF EXISTS tab6; |
| 17 |
|
| 18 |
--echo #set the other madatory flags before test starts |
| 19 |
SET GLOBAL Innodb_file_per_table=on; |
| 20 |
let $innodb_compression_level = `SELECT @@global.innodb_compression_level`; |
| 21 |
|
| 22 |
--echo #set the compression level=0 (No compress) |
| 23 |
SET global innodb_compression_level=0; |
| 24 |
|
| 25 |
-- echo #check the compression level and the compressed_pages is default |
| 26 |
SELECT @@innodb_compression_level; |
| 27 |
SELECT @@Innodb_file_per_table; |
| 28 |
|
| 29 |
-- echo #create table with 1K block size |
| 30 |
CREATE TABLE tab5 (col_1 CHAR (255) , |
| 31 |
col_2 VARCHAR (255), col_3 longtext, |
| 32 |
col_4 longtext,col_5 longtext, |
| 33 |
col_6 longtext , col_7 longtext , |
| 34 |
col_8 longtext ,col_9 longtext , |
| 35 |
col_10 longtext ,col_11 int auto_increment primary key) |
| 36 |
ENGINE = innodb ROW_FORMAT=compressed key_block_size=1; |
| 37 |
|
| 38 |
-- echo #create indexes |
| 39 |
CREATE INDEX idx1 ON tab5(col_4(10)); |
| 40 |
CREATE INDEX idx2 ON tab5(col_5(10)); |
| 41 |
CREATE INDEX idx3 ON tab5(col_6(10)); |
| 42 |
CREATE INDEX idx4 ON tab5(col_7(10)); |
| 43 |
CREATE INDEX idx5 ON tab5(col_8(10)); |
| 44 |
CREATE INDEX idx6 ON tab5(col_11); |
| 45 |
|
| 46 |
--echo #load the with repeat function |
| 47 |
SET @col_1 = repeat('a', 100); |
| 48 |
SET @col_2 = repeat('b', 100); |
| 49 |
SET @col_3 = repeat('c', 100); |
| 50 |
SET @col_4 = repeat('d', 100); |
| 51 |
SET @col_5 = repeat('e', 100); |
| 52 |
SET @col_6 = repeat('f', 100); |
| 53 |
SET @col_7 = repeat('g', 100); |
| 54 |
SET @col_8 = repeat('h', 100); |
| 55 |
SET @col_9 = repeat('i', 100); |
| 56 |
SET @col_10 = repeat('j', 100); |
| 57 |
|
| 58 |
--echo #insert 10 records |
| 59 |
let $i = 10; |
| 60 |
while ($i) { |
| 61 |
|
| 62 |
eval INSERT INTO tab5(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) |
| 63 |
VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); |
| 64 |
dec $i; |
| 65 |
|
| 66 |
} |
| 67 |
|
| 68 |
--echo #set the compression level=9 (High compress) |
| 69 |
SET global innodb_compression_level=9; |
| 70 |
|
| 71 |
-- echo #create table with 1K block size |
| 72 |
CREATE TABLE tab6 (col_1 CHAR (255) , |
| 73 |
col_2 VARCHAR (255), col_3 longtext, |
| 74 |
col_4 longtext,col_5 longtext, |
| 75 |
col_6 longtext , col_7 longtext , |
| 76 |
col_8 longtext ,col_9 longtext , |
| 77 |
col_10 longtext ,col_11 int auto_increment primary key) |
| 78 |
ENGINE = innodb ROW_FORMAT=compressed key_block_size=1; |
| 79 |
|
| 80 |
-- echo #create indexes |
| 81 |
CREATE INDEX idx1 ON tab6(col_4(10)); |
| 82 |
CREATE INDEX idx2 ON tab6(col_5(10)); |
| 83 |
CREATE INDEX idx3 ON tab6(col_6(10)); |
| 84 |
CREATE INDEX idx4 ON tab6(col_7(10)); |
| 85 |
CREATE INDEX idx5 ON tab6(col_8(10)); |
| 86 |
CREATE INDEX idx6 ON tab6(col_11); |
| 87 |
|
| 88 |
--echo #load the with repeat function |
| 89 |
SET @col_1 = repeat('a', 100); |
| 90 |
SET @col_2 = repeat('b', 100); |
| 91 |
SET @col_3 = repeat('c', 100); |
| 92 |
SET @col_4 = repeat('d', 100); |
| 93 |
SET @col_5 = repeat('e', 100); |
| 94 |
SET @col_6 = repeat('f', 100); |
| 95 |
SET @col_7 = repeat('g', 100); |
| 96 |
SET @col_8 = repeat('h', 100); |
| 97 |
SET @col_9 = repeat('i', 100); |
| 98 |
SET @col_10 = repeat('j', 100); |
| 99 |
|
| 100 |
--echo #insert 10 records |
| 101 |
let $i = 10; |
| 102 |
while ($i) { |
| 103 |
|
| 104 |
eval INSERT INTO tab6(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) |
| 105 |
VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); |
| 106 |
dec $i; |
| 107 |
} |
| 108 |
|
| 109 |
ANALYZE TABLE tab5; |
| 110 |
ANALYZE TABLE tab6; |
| 111 |
|
| 112 |
-- echo #diff the sizes of the No compressed table and high compressed table |
| 113 |
SET @size=(SELECT |
| 114 |
(SELECT (SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024) |
| 115 |
FROM INFORMATION_SCHEMA.TABLES |
| 116 |
WHERE table_name='tab5' AND ENGINE='InnoDB' AND table_schema='test') |
| 117 |
- |
| 118 |
(SELECT SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024 |
| 119 |
FROM INFORMATION_SCHEMA.TABLES |
| 120 |
WHERE table_name='tab6' AND ENGINE='InnoDB' AND table_schema='test') |
| 121 |
FROM DUAL); |
| 122 |
|
| 123 |
--echo #check the size of the table, it should not be Negative value |
| 124 |
--echo #The results of this query Test pass = 1 and fail=0 |
| 125 |
SELECT @size >= 0; |
| 126 |
|
| 127 |
|
| 128 |
--echo # |
| 129 |
--echo # Cleanup |
| 130 |
--echo # |
| 131 |
DROP TABLE tab5; |
| 132 |
DROP TABLE tab6; |
| 133 |
|
| 134 |
--echo #reset back the compression_level to default. |
| 135 |
--disable_query_log |
| 136 |
eval SET GLOBAL innodb_compression_level=$innodb_compression_level; |
| 137 |
--enable_query_log |