Debian Codegraph @

mysql-test/suite/innodb/t/create_index_excess_memory.test

raw
1 #
2 # Bug #37233273 : DDL buffer increase has out of control memory usage
3 #
4
5 --source include/have_debug.inc
6
7 --echo # Create Table
8 CREATE TABLE t1 (
9 auditid VARCHAR(45) NOT NULL,
10 ip VARCHAR(39) NOT NULL DEFAULT '',
11 PRIMARY KEY (auditid)
12 ) ENGINE=INNODB;
13
14 --echo # Insert Values
15 INSERT INTO t1 VALUES
16 ('abcdefghi', '1.1.1.1'),
17 ('fghi', '192.168.1.1'),
18 ('jklmnop', '2.2.2.2'),
19 ('qrstu', '255.255.255.255'),
20 ('uvwxyzabcde', '2001:db8::1');
21
22 --echo # Create function for getting memory usage from perf schema
23 CREATE FUNCTION get_my_std_memory_usage() RETURNS INT READS SQL DATA
24 RETURN (
25 SELECT SUM_NUMBER_OF_BYTES_ALLOC
26 FROM performance_schema.memory_summary_global_by_event_name
27 WHERE EVENT_NAME = 'memory/innodb/std'
28 );
29
30 --echo # Test 1. Check memory usage during create index
31
32 --echo # Set the limit for memory usage
33 --let $limit = 4*1024*1024
34
35 --echo # Set Buffer Size
36 SET innodb_ddl_buffer_size = (4*1024*1024);
37
38 --echo # Number of bytes allocated before create index
39 SET @before_memory = get_my_std_memory_usage();
40
41 --echo # Create index
42 CREATE INDEX i1 ON t1(ip);
43
44 --echo # Number of bytes allocated after create index
45 SET @after_memory = get_my_std_memory_usage();
46
47 --echo # Bytes allocated in the process
48 SET @memory_diff = @after_memory - @before_memory;
49
50 --let $assert_text = Assert that memory usage is less than 4MB
51 --let $assert_cond = @memory_diff < $limit
52 --source include/assert.inc
53
54 --echo # Drop index
55 DROP INDEX i1 ON t1;
56
57
58 --echo # Set Buffer Size
59 SET innodb_ddl_buffer_size = (8*1024*1024);
60
61 --echo # Number of bytes allocated before create index
62 SET @before_memory = get_my_std_memory_usage();
63
64 --echo # Create index
65 CREATE INDEX i1 ON t1(ip);
66
67 --echo # Number of bytes allocated after create index
68 SET @after_memory = get_my_std_memory_usage();
69
70 --echo # Bytes allocated in the process
71 SET @memory_diff = @after_memory - @before_memory;
72
73 --let $assert_text = Assert that memory usage is less than 4MB
74 --let $assert_cond = @memory_diff < $limit
75 --source include/assert.inc
76
77 --echo # Drop index
78 DROP INDEX i1 ON t1;
79
80
81 --echo # Set Buffer Size
82 SET innodb_ddl_buffer_size = (16*1024*1024);
83
84 --echo # Number of bytes allocated before create index
85 SET @before_memory = get_my_std_memory_usage();
86
87 --echo # Create index
88 CREATE INDEX i1 ON t1(ip);
89
90 --echo # Number of bytes allocated after create index
91 SET @after_memory = get_my_std_memory_usage();
92
93 --echo # Bytes allocated in the process
94 SET @memory_diff = @after_memory - @before_memory;
95
96 --let $assert_text = Assert that memory usage is less than 4MB
97 --let $assert_cond = @memory_diff < $limit
98 --source include/assert.inc
99
100 --echo # Drop index
101 DROP INDEX i1 ON t1;
102
103
104 --echo # Set Buffer Size
105 SET innodb_ddl_buffer_size = (32*1024*1024);
106
107 --echo # Number of bytes allocated before create index
108 SET @before_memory = get_my_std_memory_usage();
109
110 --echo # Create index
111 CREATE INDEX i1 ON t1(ip);
112
113 --echo # Number of bytes allocated after create index
114 SET @after_memory = get_my_std_memory_usage();
115
116 --echo # Bytes allocated in the process
117 SET @memory_diff = @after_memory - @before_memory;
118
119 --let $assert_text = Assert that memory usage is less than 4MB
120 --let $assert_cond = @memory_diff < $limit
121 --source include/assert.inc
122
123 --echo # Drop index
124 DROP INDEX i1 ON t1;
125
126
127 --echo # Set Buffer Size
128 SET innodb_ddl_buffer_size = (64*1024*1024);
129
130 --echo # Number of bytes allocated before create index
131 SET @before_memory = get_my_std_memory_usage();
132
133 --echo # Create index
134 CREATE INDEX i1 ON t1(ip);
135
136 --echo # Number of bytes allocated after create index
137 SET @after_memory = get_my_std_memory_usage();
138
139 --echo # Bytes allocated in the process
140 SET @memory_diff = @after_memory - @before_memory;
141
142 --let $assert_text = Assert that memory usage is less than 4MB
143 --let $assert_cond = @memory_diff < $limit
144 --source include/assert.inc
145
146 --echo # Drop index
147 DROP INDEX i1 ON t1;
148
149
150 --echo # Set Buffer Size
151 SET innodb_ddl_buffer_size = (128*1024*1024);
152
153 --echo # Number of bytes allocated before create index
154 SET @before_memory = get_my_std_memory_usage();
155
156 --echo # Create index
157 CREATE INDEX i1 ON t1(ip);
158
159 --echo # Number of bytes allocated after create index
160 SET @after_memory = get_my_std_memory_usage();
161
162 --echo # Bytes allocated in the process
163 SET @memory_diff = @after_memory - @before_memory;
164
165 --let $assert_text = Assert that memory usage is less than 4MB
166 --let $assert_cond = @memory_diff < $limit
167 --source include/assert.inc
168
169 --echo # Drop index
170 DROP INDEX i1 ON t1;
171
172
173 --echo # Test 2. Test memory usage with minumum value of innodb_ddl_buffer_size
174 --echo # and maximum value of --innodb_parallel_read_threads
175 SET innodb_ddl_buffer_size = 65536;
176 SET innodb_parallel_read_threads = 256;
177
178 --echo # Number of bytes allocated before create index
179 SET @before_memory = get_my_std_memory_usage();
180
181 --echo # Create index
182 CREATE INDEX i1 ON t1(ip);
183
184 --echo # Number of bytes allocated after create index
185 SET @after_memory = get_my_std_memory_usage();
186
187 --echo # Bytes allocated in the process
188 SET @memory_diff = @after_memory - @before_memory;
189
190 --let $assert_text = Assert that memory usage is less than 4MB
191 --let $assert_cond = @memory_diff < $limit
192 --source include/assert.inc
193
194 --echo # Drop index
195 DROP INDEX i1 ON t1;
196
197
198 --echo # Test 3. Test memory usage with maximum value of innodb_ddl_buffer_size
199 --echo # and minimum value of --innodb_parallel_read_threads
200 SET innodb_ddl_buffer_size = 4294967295;
201 SET innodb_parallel_read_threads = 1;
202 --let $limit = 819*1024*1024 # 20% of innodb_ddl_buffer_size
203
204 --echo # Number of bytes allocated before create index
205 SET @before_memory = get_my_std_memory_usage();
206
207 --echo # Create index
208 CREATE INDEX i1 ON t1(ip);
209
210 --echo # Number of bytes allocated after create index
211 SET @after_memory = get_my_std_memory_usage();
212
213 --echo # Bytes allocated in the process
214 SET @memory_diff = @after_memory - @before_memory;
215
216 --let $assert_text = Assert that memory usage is less than 4MB
217 --let $assert_cond = @memory_diff < $limit
218 --source include/assert.inc
219
220 --echo # Drop index
221 DROP INDEX i1 ON t1;
222
223
224 --echo # Test 4. Test DB_OVERFLOW by setting tuple vector size to 2
225
226 --echo # Set Buffer Size
227 SET innodb_ddl_buffer_size = (4*1024*1024);
228 SET innodb_parallel_read_threads = default;
229
230 --echo # Create index
231 SET DEBUG = '+d, ddl_buf_add_two';
232 CREATE INDEX i1 ON t1(ip);
233 --echo # The warning "InnoDB rebuilding table to add column FTS_DOC_ID" is expected
234 CREATE FULLTEXT INDEX i2 ON t1(ip);
235 SET DEBUG = '-d, ddl_buf_add_two';
236
237 --echo # Drop index
238 DROP INDEX i1 ON t1;
239 DROP INDEX i2 ON t1;
240
241
242 --echo # Cleanup
243 DROP TABLE t1;
244 DROP FUNCTION get_my_std_memory_usage;
245
246 --echo # Restart server to set parameters to default
247 let $restart_parameters = restart;
248 --source include/restart_mysqld.inc
249
250 #END TEST