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
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
|
# #############################################################
# wl6747 : Set merge threshold at index level
# Check with CREATE INDEX on all datatypes
# Check with by ALTER TABLE MODIFY COLUMN TYPE
# Check with ALTER TABLE ADD Index
# Check by setting at index level with CREATE TABLE
# Check with BLOB column at index level with CREATE Index
# Check with row_format=compressed and key_block_size=8k
# Check withe valid and invalid merge_threshold values.
#
# Check actual behavior for table, partitioned table and temporary table
# #############################################################
--source include/have_innodb.inc
--source include/have_innodb_16k.inc
--source include/have_partition.inc
SET @save_stats_persistent = @@GLOBAL.innodb_stats_persistent;
SET GLOBAL innodb_stats_persistent = 0;
# Check index merge threshold by create index on all datatypes
CREATE TABLE tab(a BIGINT PRIMARY KEY,c1 TINYTEXT,c2 TEXT,c3 MEDIUMTEXT,
c4 TINYBLOB,c5 BLOB,c6 MEDIUMBLOB,c7 LONGBLOB) ENGINE=InnoDB;
# check index merge threshold on all datatypes
CREATE INDEX index1 ON tab(c1(255)) COMMENT 'Check index level merge MERGE_THRESHOLD=51';
CREATE INDEX index2 ON tab(c2(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=-1';
CREATE INDEX index3 ON tab(c3(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=20';
CREATE INDEX index4 ON tab(c4(255)) COMMENT 'Check index level merge MERGE_THRESHOLD=25';
CREATE INDEX index5 ON tab(c5(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=30';
CREATE INDEX index6 ON tab(c6(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=35';
CREATE INDEX index7 ON tab(c7(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=40';
SHOW CREATE TABLE tab;
SELECT t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i
WHERE t.TABLE_ID = i.TABLE_ID AND t.NAME = 'test/tab';
ALTER TABLE tab comment='MERGE_THRESHOLD=49';
SHOW CREATE TABLE tab;
SELECT t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i
WHERE t.TABLE_ID = i.TABLE_ID AND t.NAME = 'test/tab';
ALTER TABLE tab MODIFY COLUMN c7 VARCHAR(2048) ;
SHOW CREATE TABLE tab;
SELECT t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i
WHERE t.TABLE_ID = i.TABLE_ID AND t.NAME = 'test/tab';
ALTER TABLE tab ADD INDEX index8 (c7(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=45';
SHOW CREATE TABLE tab;
SELECT t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i
WHERE t.TABLE_ID = i.TABLE_ID AND t.NAME = 'test/tab';
# Cleanup
DROP TABLE tab;
--echo #
--echo # behavior for deleting records
--echo #
--echo # test to confirm behavior (MERGE_THRESHOLD=50 (default))
CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB;
--source suite/innodb/include/innodb_merge_threshold_delete.inc
DROP TABLE tab1;
--echo # test to confirm behavior (MERGE_THRESHOLD=35)
CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB
COMMENT='MERGE_THRESHOLD=35';
--source suite/innodb/include/innodb_merge_threshold_delete.inc
DROP TABLE tab1;
--echo # test to confirm behavior (MERGE_THRESHOLD=25)
CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB
COMMENT='MERGE_THRESHOLD=25';
--source suite/innodb/include/innodb_merge_threshold_delete.inc
DROP TABLE tab1;
--echo # test to confirm partitioned table (MERGE_THRESHOLD=35)
CREATE TABLE tab1 (a bigint primary key, b varchar(2048))
COMMENT='MERGE_THRESHOLD=35'
PARTITION BY RANGE (a)
(PARTITION p0 VALUES LESS THAN (20) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
--source suite/innodb/include/innodb_merge_threshold_delete.inc
DROP TABLE tab1;
--echo #
--echo # behavior for updating to smaller records
--echo #
--echo # test to confirm behavior (MERGE_THRESHOLD=50 (default))
CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB;
--source suite/innodb/include/innodb_merge_threshold_update.inc
DROP TABLE tab1;
--echo # test to confirm behavior (MERGE_THRESHOLD=35)
CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB
COMMENT='MERGE_THRESHOLD=35';
--source suite/innodb/include/innodb_merge_threshold_update.inc
DROP TABLE tab1;
--echo # test to confirm behavior (MERGE_THRESHOLD=25)
CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB
COMMENT='MERGE_THRESHOLD=25';
--source suite/innodb/include/innodb_merge_threshold_update.inc
DROP TABLE tab1;
--echo # test to confirm explicit temporary table (MERGE_THRESHOLD=35)
--echo # (though not registered to SYS_TABLES,SYS_INDEXES, it works correctly)
# Temporary tables are not purged. so deleting records is not caused
# So, should be tested by updating case only
CREATE TEMPORARY TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB
COMMENT='MERGE_THRESHOLD=35';
--source suite/innodb/include/innodb_merge_threshold_update.inc
DROP TABLE tab1;
--echo #
--echo # behavior for secondary index with blob
--echo #
--echo # test to confirm behavior (MERGE_THRESHOLD=50 (default))
# not to cause page operation at primary key, row_format=dynamic and the key is blob
CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB row_format=dynamic;
CREATE INDEX index1 ON tab1(b(750));
--source suite/innodb/include/innodb_merge_threshold_secondary.inc
DROP TABLE tab1;
--echo # test to confirm behavior (MERGE_THRESHOLD=45)
CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB row_format=dynamic;
CREATE INDEX index1 ON tab1(b(750)) COMMENT 'MERGE_THRESHOLD=45';
--source suite/innodb/include/innodb_merge_threshold_secondary.inc
DROP TABLE tab1;
--echo # test to confirm behavior (MERGE_THRESHOLD=40)
CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB row_format=dynamic;
CREATE INDEX index1 ON tab1(b(750)) COMMENT 'MERGE_THRESHOLD=40';
--source suite/innodb/include/innodb_merge_threshold_secondary.inc
DROP TABLE tab1;
--echo # compressed table behaves same (MERGE_THRESHOLD=45)
CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
CREATE INDEX index1 ON tab1(b(750)) COMMENT 'MERGE_THRESHOLD=45';
--source suite/innodb/include/innodb_merge_threshold_secondary.inc
DROP TABLE tab1;
SET GLOBAL innodb_stats_persistent = @save_stats_persistent;
|
