Debian Codegraph @

mysql-test/suite/special/t/innodb_dedicated_server.test

raw
1 #####################################################################################
2 # Test for autoscaling based on innodb_dedicated_server variable. #
3 # #
4 # #
5 # This test checks autoscaling of following variables based on value of #
6 # innodb_dedicated_server option #
7 # - innodb_buffer_pool_size #
8 # - innodb_redo_log_capacity #
9 # - innodb_flush_method #
10 # #
11 # This test also does basic testing for new variable innodb_dedicated_server #
12 # This test is diabled on Solaris and Wndows as they dont support O_DIRECT_NO_FSYNC #
13 #####################################################################################
14
15 # Make sure to run only on Linux
16 --source include/linux.inc
17
18 # Make sure this test is not run on PB2 machines
19 --source include/is_local_machine.inc
20 # This test takes long time, so only run it with the --big-test mtr-flag.
21 --source include/big_test.inc
22 # Make sure that there are no other tests running in parallel
23 --source include/not_parallel.inc
24
25 ##############################################################################
26 # Autoscaling is done as per below formula: #
27 # innodb_buffer_pool_size #
28 # server_memory < 1G ? 128M (same as current default) #
29 # server_memory <= 4G ? server_memory * 0.5 #
30 # server_memory > 4G ? server_memory * 0.75 #
31 # #
32 # innodb_redo_log_capacity #
33 # buf_pool_size < 1G ? 100M (same as current default) #
34 # buf_pool_size < 8G ? ROUND(buf_pool_size * 0.5) G #
35 # buf_pool_size <= 128G ? ROUND(buf_pool_size * 0.75) G #
36 # buf_pool_size > 128G ? 128G #
37 # #
38 # Following Perl module calculates server memory and evaluates #
39 # innodb_buffer_pool_size and #
40 # innodb_redo_log_capacity #
41 # based on above formula. #
42 # #
43 # NOTE : Memory considered here is in Kilo bytes #
44 ##############################################################################
45 perl;
46 use Sys::MemInfo qw(totalmem freemem totalswap);
47 my $dir = $ENV{'MYSQLTEST_VARDIR'};
48 $total_mem=&totalmem/1024;
49 my $filename="$dir/tmpfile.txt";
50 open(my $fh, '>', $filename) or die "Could not open file '$filename' $!";
51 print $fh "let \$total_mem = $total_mem\; \n";
52 close $fh;
53 EOF
54 # Perl module ends
55 --source $MYSQLTEST_VARDIR/tmpfile.txt
56
57 --let $mem_size1K = 1024
58 --let $mem_size1G = 1048576
59 --let $mem_size4G = 4194304
60 --let $mem_size8G = 8388608
61 --let $mem_size128G = 134217728
62 --let $zero_point_five = 0.5
63 --let $zero_point_seven_five = 0.75
64
65 # Set buffer pool size.
66 # Note: As MTR doesn't have 'else', following sequence of 'if' is important
67 if ($total_mem > $mem_size4G) {
68 --expr $calculated_buffer_pool_size = $total_mem * $zero_point_seven_five
69 }
70 if ($total_mem <= $mem_size4G) {
71 --expr $calculated_buffer_pool_size = $total_mem * $zero_point_five
72 }
73 if ($total_mem < $mem_size1G) {
74 --let $calculated_buffer_pool_size = 131072
75 }
76
77 --disable_query_log
78 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Resizing redo log");
79 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Starting to delete and rewrite redo log files.");
80 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* New redo log files created");
81 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Option innodb_dedicated_server is ignored ");
82 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* No argument was provided to --log-bin, and --log-bin-index was not used");
83 --enable_query_log
84
85 --echo
86 --echo ###########################################
87 --echo # INITIAL START #
88 --echo # innodb_dedicated_server = OFF #
89 --echo # innodb_buffer_pool_size = default #
90 --echo # innodb_redo_log_capacity = default #
91 --echo # innodb_flush_method = default #
92 --echo ###########################################
93
94 --disable_query_log
95 SELECT VARIABLE_VALUE AS INNODB_DEDICATED_SERVER
96 FROM performance_schema.global_variables
97 WHERE VARIABLE_NAME='innodb_dedicated_server';
98
99 SELECT VARIABLE_VALUE/(1024*1024) AS BUFFER_POOL_SIZE_IN_MB
100 FROM performance_schema.global_variables
101 WHERE VARIABLE_NAME='innodb_buffer_pool_size';
102
103 SELECT VARIABLE_VALUE/(1024*1024) AS LOG_CAPACITY_IN_MB
104 FROM performance_schema.global_variables
105 WHERE VARIABLE_NAME='innodb_redo_log_capacity';
106
107 SELECT VARIABLE_VALUE AS INNODB_FLUSH_METHOD
108 FROM performance_schema.global_variables
109 WHERE VARIABLE_NAME='innodb_flush_method';
110 --enable_query_log
111
112 --echo
113 --echo ###########################################
114 --echo # RESTART 1 #
115 --echo # innodb_dedicated_server = ON #
116 --echo # innodb_buffer_pool_size = autoscaled #
117 --echo # innodb_redo_log_capacity = autoscaled #
118 --echo # innodb_flush_method = autoscaled #
119 --echo ###########################################
120 let $restart_parameters = restart: --innodb_dedicated_server=ON;
121 let $explicit_default_wait_counter=100000;
122
123 --source include/restart_mysqld.inc
124
125 ###############################################################################
126 # Get expected values of Buffer pool size and Log file size #
127 ###############################################################################
128 # Note: If calculated value of innodb_buffer_pool_size evaluates to a value #
129 # that is not equal to or a multiple of #
130 # innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances, #
131 # then innodb_buffer_pool_size is automatically adjusted to a value that is #
132 # equal to or a multiple of #
133 # innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances #
134 # that is not less than the specified buffer pool size. #
135 ###############################################################################
136 --disable_query_log
137 set @innodb_chunk_instance_product = ROUND(@@global.innodb_buffer_pool_chunk_size/1024,2) * @@global.innodb_buffer_pool_instances;
138 let $expected_innodb_buffer_pool_size = `select ROUND(CEIL($calculated_buffer_pool_size/@innodb_chunk_instance_product) * @innodb_chunk_instance_product)`;
139
140 # Set innodb_redo_log_capacity now after buffer_pool_size has been decided.
141 # Note: As MTR doesn't have 'else', following sequence of 'if' is important
142 if ($expected_innodb_buffer_pool_size > $mem_size128G) {
143 --let $calculated_log_capacity = 134217728
144 }
145 if ($expected_innodb_buffer_pool_size <= $mem_size128G) {
146 --let $calculated_log_capacity = `SELECT ROUND($expected_innodb_buffer_pool_size*$zero_point_seven_five/$mem_size1G,0) * 1048576`
147 }
148 if ($expected_innodb_buffer_pool_size <= $mem_size8G) {
149 --let $calculated_log_capacity = `SELECT ROUND($expected_innodb_buffer_pool_size/($mem_size1K * $mem_size1K),0) * 524288`
150 }
151 if ($expected_innodb_buffer_pool_size < $mem_size1G) {
152 --let $calculated_log_capacity = 102400;
153 }
154 let $expected_innodb_redo_log_capacity = `select $calculated_log_capacity`;
155 --enable_query_log
156
157 --disable_query_log
158 SELECT VARIABLE_VALUE AS INNODB_DEDICATED_SERVER
159 FROM performance_schema.global_variables
160 WHERE VARIABLE_NAME='innodb_dedicated_server';
161
162 let $actual_innodb_buffer_pool_size = `select ROUND(@@global.innodb_buffer_pool_size/1024,2)`;
163 --disable_result_log
164 eval select $expected_innodb_buffer_pool_size = $actual_innodb_buffer_pool_size into @final_pool_size_result;
165 --enable_result_log
166 --echo 1 expected
167 select @final_pool_size_result;
168
169 let $actual_innodb_redo_log_capacity = `select ROUND(@@global.innodb_redo_log_capacity/1024,2)`;
170 --disable_result_log
171 eval select ROUND($expected_innodb_redo_log_capacity,2) = $actual_innodb_redo_log_capacity into @final_log_capacity_result;
172 --enable_result_log
173 --echo 1 expected
174 select @final_log_capacity_result;
175
176 SELECT VARIABLE_VALUE AS INNODB_FLUSH_METHOD
177 FROM performance_schema.global_variables
178 WHERE VARIABLE_NAME='innodb_flush_method';
179 --enable_query_log
180
181 --echo
182 --echo ###########################################
183 --echo # RESTART 2 #
184 --echo # innodb_dedicated_server = ON #
185 --echo # innodb_buffer_pool_size = explicit #
186 --echo # innodb_redo_log_capacity = autoscaled #
187 --echo # innodb_flush_method = autoscaled #
188 --echo ###########################################
189 let $restart_parameters = restart: --innodb_dedicated_server=ON --innodb_buffer_pool_size=256M;
190 let $explicit_default_wait_counter=100000;
191 --source include/restart_mysqld.inc
192
193 --disable_query_log
194 SELECT VARIABLE_VALUE AS INNODB_DEDICATED_SERVER
195 FROM performance_schema.global_variables
196 WHERE VARIABLE_NAME='innodb_dedicated_server';
197
198 SELECT VARIABLE_VALUE/(1024*1024) AS BUFFER_POOL_SIZE_IN_MB
199 FROM performance_schema.global_variables
200 WHERE VARIABLE_NAME='innodb_buffer_pool_size';
201
202 let $actual_innodb_redo_log_capacity = `select ROUND(@@global.innodb_redo_log_capacity/1024,2)`;
203 --disable_result_log
204 eval select ROUND($expected_innodb_redo_log_capacity,2) = $actual_innodb_redo_log_capacity into @final_log_capacity_result;
205 --enable_result_log
206 --echo 1 expected
207 select @final_log_capacity_result;
208
209 SELECT VARIABLE_VALUE AS INNODB_FLUSH_METHOD
210 FROM performance_schema.global_variables
211 WHERE VARIABLE_NAME='innodb_flush_method';
212 --enable_query_log
213
214 --echo # Make sure that the warnings are present in server log.
215 let server_log= $MYSQLTEST_VARDIR/log/mysqld.1.err;
216 let SEARCH_FILE= $server_log;
217 let SEARCH_PATTERN= \\[Warning\\] .*MY-\\d+.* Option innodb_dedicated_server is ignored for innodb_buffer_pool_size;
218 --source include/search_pattern.inc
219
220 --echo
221 --echo ###########################################
222 --echo # RESTART 3 #
223 --echo # innodb_dedicated_server = ON #
224 --echo # innodb_buffer_pool_size = autoscaled #
225 --echo # innodb_redo_log_capacity = explicit #
226 --echo # innodb_flush_method = autoscaled #
227 --echo ###########################################
228 let $restart_parameters = restart: --innodb_dedicated_server=ON --innodb_redo_log_capacity=50M;
229 let $explicit_default_wait_counter=100000;
230 --source include/restart_mysqld.inc
231
232 --disable_query_log
233 SELECT VARIABLE_VALUE AS INNODB_DEDICATED_SERVER
234 FROM performance_schema.global_variables
235 WHERE VARIABLE_NAME='innodb_dedicated_server';
236
237 let $actual_innodb_buffer_pool_size = `select ROUND(@@global.innodb_buffer_pool_size/1024,2)`;
238 --disable_result_log
239 eval select $expected_innodb_buffer_pool_size = $actual_innodb_buffer_pool_size into @final_pool_size_result;
240 --enable_result_log
241 --echo 1 expected
242 select @final_pool_size_result;
243
244 SELECT VARIABLE_VALUE/(1024*1024) AS LOG_CAPACITY_IN_MB
245 FROM performance_schema.global_variables
246 WHERE VARIABLE_NAME='innodb_redo_log_capacity';
247
248 SELECT VARIABLE_VALUE AS INNODB_FLUSH_METHOD
249 FROM performance_schema.global_variables
250 WHERE VARIABLE_NAME='innodb_flush_method';
251 --enable_query_log
252
253 --echo # Make sure that the warnings are present in server log.
254 let server_log= $MYSQLTEST_VARDIR/log/mysqld.1.err;
255 let SEARCH_FILE= $server_log;
256 let SEARCH_PATTERN= \\[Warning\\] .*MY-\\d+.* Option innodb_dedicated_server is ignored for innodb_redo_log_capacity;
257 --source include/search_pattern.inc
258
259 --echo
260 --echo ###########################################
261 --echo # RESTART 4 #
262 --echo # innodb_dedicated_server = ON #
263 --echo # innodb_buffer_pool_size = autoscaled #
264 --echo # innodb_redo_log_capacity = autoscaled #
265 --echo # innodb_flush_method = explicit #
266 --echo ###########################################
267 let $restart_parameters = restart: --innodb_dedicated_server=ON --innodb_flush_method=nosync;
268 let $explicit_default_wait_counter=100000;
269 --source include/restart_mysqld.inc
270
271 --disable_query_log
272 SELECT VARIABLE_VALUE AS INNODB_DEDICATED_SERVER
273 FROM performance_schema.global_variables
274 WHERE VARIABLE_NAME='innodb_dedicated_server';
275
276 let $actual_innodb_buffer_pool_size = `select @@global.innodb_buffer_pool_size/1024`;
277 --disable_result_log
278 eval select $expected_innodb_buffer_pool_size = $actual_innodb_buffer_pool_size into @final_pool_size_result;
279 --enable_result_log
280 --echo 1 expected
281 select @final_pool_size_result;
282
283 let $actual_innodb_redo_log_capacity = `select ROUND(@@global.innodb_redo_log_capacity/1024,2)`;
284 --disable_result_log
285 eval select ROUND($expected_innodb_redo_log_capacity,2) = $actual_innodb_redo_log_capacity into @final_log_capacity_result;
286 --enable_result_log
287 --echo 1 expected
288 select @final_log_capacity_result;
289
290 SELECT VARIABLE_VALUE AS INNODB_FLUSH_METHOD
291 FROM performance_schema.global_variables
292 WHERE VARIABLE_NAME='innodb_flush_method';
293 --enable_query_log
294
295 --echo # Make sure that the warnings are present in server log.
296 let server_log= $MYSQLTEST_VARDIR/log/mysqld.1.err;
297 let SEARCH_FILE= $server_log;
298 let SEARCH_PATTERN= \\[Warning\\] .*MY-\\d+.* Option innodb_dedicated_server is ignored for innodb_flush_method;
299 --source include/search_pattern.inc
300
301 --echo
302 --echo ###########################################
303 --echo # RESTART 5 #
304 --echo # innodb_dedicated_server = ON #
305 --echo # innodb_buffer_pool_size = explicit #
306 --echo # innodb_redo_log_capacity = explicit #
307 --echo # innodb_flush_method = explicit #
308 --echo ###########################################
309 let $restart_parameters = restart: --innodb_dedicated_server=ON --innodb_buffer_pool_size=256M --innodb_redo_log_capacity=50M --innodb_flush_method=nosync;
310 let $explicit_default_wait_counter=100000;
311 --source include/restart_mysqld.inc
312
313 --disable_query_log
314 SELECT VARIABLE_VALUE AS INNODB_DEDICATED_SERVER
315 FROM performance_schema.global_variables
316 WHERE VARIABLE_NAME='innodb_dedicated_server';
317
318 SELECT VARIABLE_VALUE/(1024*1024) AS BUFFER_POOL_SIZE_IN_MB
319 FROM performance_schema.global_variables
320 WHERE VARIABLE_NAME='innodb_buffer_pool_size';
321
322 SELECT VARIABLE_VALUE/(1024*1024) AS LOG_CAPACITY_IN_MB
323 FROM performance_schema.global_variables
324 WHERE VARIABLE_NAME='innodb_redo_log_capacity';
325
326 SELECT VARIABLE_VALUE AS INNODB_FLUSH_METHOD
327 FROM performance_schema.global_variables
328 WHERE VARIABLE_NAME='innodb_flush_method';
329 --enable_query_log
330
331 --echo # Make sure that the warnings are present in server log.
332 let server_log= $MYSQLTEST_VARDIR/log/mysqld.1.err;
333 let SEARCH_FILE= $server_log;
334 let SEARCH_PATTERN= \\[Warning\\] .*MY-\\d+.* Option innodb_dedicated_server is ignored for innodb_buffer_pool_size;
335 --source include/search_pattern.inc
336 let SEARCH_PATTERN= \\[Warning\\] .*MY-\\d+.* Option innodb_dedicated_server is ignored for innodb_redo_log_capacity;
337 --source include/search_pattern.inc
338 let SEARCH_PATTERN= \\[Warning\\] .*MY-\\d+.* Option innodb_dedicated_server is ignored for innodb_flush_method;
339 --source include/search_pattern.inc
340
341 --echo
342 --echo ###########################################
343 --echo # RESTART 6 #
344 --echo # innodb_dedicated_server = ON #
345 --echo # innodb_buffer_pool_size = autoscaled #
346 --echo # innodb_redo_log_capacity = explicit #
347 --echo # innodb_flush_method = explicit #
348 --echo ###########################################
349 let $restart_parameters = restart: --innodb_dedicated_server=ON --innodb_redo_log_capacity=50M --innodb_flush_method=nosync;
350 let $explicit_default_wait_counter=100000;
351 --source include/restart_mysqld.inc
352
353 --disable_query_log
354 SELECT VARIABLE_VALUE AS INNODB_DEDICATED_SERVER
355 FROM performance_schema.global_variables
356 WHERE VARIABLE_NAME='innodb_dedicated_server';
357
358 let $actual_innodb_buffer_pool_size = `select @@global.innodb_buffer_pool_size/1024`;
359 --disable_result_log
360 eval select $expected_innodb_buffer_pool_size = $actual_innodb_buffer_pool_size into @final_pool_size_result;
361 --enable_result_log
362 --echo 1 expected
363 select @final_pool_size_result;
364
365 SELECT VARIABLE_VALUE/(1024*1024) AS LOG_CAPACITY_IN_MB
366 FROM performance_schema.global_variables
367 WHERE VARIABLE_NAME='innodb_redo_log_capacity';
368
369 SELECT VARIABLE_VALUE AS INNODB_FLUSH_METHOD
370 FROM performance_schema.global_variables
371 WHERE VARIABLE_NAME='innodb_flush_method';
372 --enable_query_log
373
374 --echo # Make sure that the warnings are present in server log.
375 let server_log= $MYSQLTEST_VARDIR/log/mysqld.1.err;
376 let SEARCH_FILE= $server_log;
377 let SEARCH_PATTERN= \\[Warning\\] .*MY-\\d+.* Option innodb_dedicated_server is ignored for innodb_redo_log_capacity;
378 --source include/search_pattern.inc
379 let SEARCH_PATTERN= \\[Warning\\] .*MY-\\d+.* Option innodb_dedicated_server is ignored for innodb_flush_method;
380 --source include/search_pattern.inc
381
382 --enable_query_log
383 --remove_file $MYSQLTEST_VARDIR/tmpfile.txt
384
385 #######################################################################################
386 # Check if innodb_dedicated_server can be accessed with and without @@ sign #
387 #######################################################################################
388
389 --Error ER_INCORRECT_GLOBAL_LOCAL_VAR
390 SET innodb_dedicated_server = 0;
391 SELECT @@innodb_dedicated_server;
392
393 --Error ER_UNKNOWN_TABLE
394 SELECT local.innodb_dedicated_server;
395
396 --Error ER_INCORRECT_GLOBAL_LOCAL_VAR
397 SET global innodb_dedicated_server = 0;
398 SELECT @@global.innodb_dedicated_server;
399
400 --Error ER_INCORRECT_GLOBAL_LOCAL_VAR
401 set session innodb_dedicated_server=1;
402 SELECT @@innodb_dedicated_server;
403
404 show global variables like 'innodb_dedicated_server';
405 show session variables like 'innodb_dedicated_server';
406
407 ####################################################################################################
408 # Check if the value of innodb_dedicated_server in GLOBAL Table matches value in variable #
409 ####################################################################################################
410
411 --echo 1 Expected
412 SELECT @@global.innodb_dedicated_server;
413 select * from performance_schema.global_variables where variable_name='innodb_dedicated_server';
414 select * from performance_schema.session_variables where variable_name='innodb_dedicated_server';
415
416 ###################################################################################################
417 # Check if innodb_dedicated_server a non-persistent variable #
418 ###################################################################################################
419 --Error ER_INCORRECT_GLOBAL_LOCAL_VAR
420 set persist_only innodb_dedicated_server=off;
421