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