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
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
|
# #############################################################
# 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_debug.inc
# purge the previous test results
--disable_query_log
set global innodb_purge_stop_now=ON;
set global innodb_purge_run_now=ON;
--enable_query_log
--source include/wait_innodb_all_purged.inc
# 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) charset latin1 ENGINE=InnoDB STATS_PERSISTENT=0;
# 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_TABLES t, INFORMATION_SCHEMA.INNODB_INDEXES i
WHERE t.TABLE_ID = i.TABLE_ID AND t.NAME = 'test/tab' ORDER BY i.NAME;
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_TABLES t, INFORMATION_SCHEMA.INNODB_INDEXES i
WHERE t.TABLE_ID = i.TABLE_ID AND t.NAME = 'test/tab' ORDER BY i.NAME;
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_TABLES t, INFORMATION_SCHEMA.INNODB_INDEXES i
WHERE t.TABLE_ID = i.TABLE_ID AND t.NAME = 'test/tab' ORDER BY i.NAME;
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_TABLES t, INFORMATION_SCHEMA.INNODB_INDEXES i
WHERE t.TABLE_ID = i.TABLE_ID AND t.NAME = 'test/tab' ORDER BY i.NAME;
# 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 STATS_PERSISTENT=0;
# Force purge
--source suite/innodb/include/force_purge.inc
--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 STATS_PERSISTENT=0
COMMENT='MERGE_THRESHOLD=35';
# Force purge
--source suite/innodb/include/force_purge.inc
--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 STATS_PERSISTENT=0
COMMENT='MERGE_THRESHOLD=25';
# Force purge
--source suite/innodb/include/force_purge.inc
--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)) STATS_PERSISTENT=0
COMMENT='MERGE_THRESHOLD=35'
PARTITION BY RANGE (a)
(PARTITION p0 VALUES LESS THAN (20) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
# Force purge
--source suite/innodb/include/force_purge.inc
--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 STATS_PERSISTENT=0;
# Force purge
--source suite/innodb/include/force_purge.inc
--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' STATS_PERSISTENT=0;
# Force purge
--source suite/innodb/include/force_purge.inc
--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' STATS_PERSISTENT=0;
# Force purge
--source suite/innodb/include/force_purge.inc
--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)
CREATE TEMPORARY TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB
COMMENT='MERGE_THRESHOLD=35' STATS_PERSISTENT=0;
--source suite/innodb/include/force_purge.inc
--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 STATS_PERSISTENT=0;
CREATE INDEX index1 ON tab1(b(750));
--source suite/innodb/include/force_purge.inc
--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 STATS_PERSISTENT=0;
CREATE INDEX index1 ON tab1(b(750)) COMMENT 'MERGE_THRESHOLD=45';
--source suite/innodb/include/force_purge.inc
--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 STATS_PERSISTENT=0;
CREATE INDEX index1 ON tab1(b(750)) COMMENT 'MERGE_THRESHOLD=40';
--source suite/innodb/include/force_purge.inc
--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 STATS_PERSISTENT=0
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
CREATE INDEX index1 ON tab1(b(750)) COMMENT 'MERGE_THRESHOLD=45';
--source suite/innodb/include/force_purge.inc
--source suite/innodb/include/innodb_merge_threshold_secondary.inc
DROP TABLE tab1;
|
