Debian Codegraph @

mysql-test/suite/special/t/innodb_dedicated_server_no_o_direct_no_fsync.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 (Not affected on Windows Platform) #
10 # #
11 # NOTE: For Windows platform, innodb_flush_method is not affected by #
12 # innodb_dedicated_server value. So we need not to check for that.#
13 # This test is disabled on Linux, as there is a separate test for #
14 # that. #
15 #########################################################################
16
17 # Make sure not to run on Linux
18 let $HAVE_LINUX = `SELECT CONVERT(@@version_compile_os using latin1) IN ("Linux")`;
19 if ($HAVE_LINUX)
20 {
21 --skip Test requires 'not to be run on linux'
22 }
23
24 # Make sure this test is not run on PB2 machines
25 --source include/is_local_machine.inc
26 # This test takes long time, so only run it with the --big-test mtr-flag.
27 --source include/big_test.inc
28 # Make sure that there are no other tests running in parallel
29 --source include/not_parallel.inc
30
31 ##############################################################################
32 # Autoscaling is done as per below formula: #
33 # innodb_buffer_pool_size #
34 # server_memory < 1G ? 128M (same as current default) #
35 # server_memory <= 4G ? server_memory * 0.5 #
36 # server_memory > 4G ? server_memory * 0.75 #
37 # #
38 # innodb_redo_log_capacity #
39 # buf_pool_size < 1G ? 100M (same as current default) #
40 # buf_pool_size < 8G ? ROUND(buf_pool_size * 0.5) G #
41 # buf_pool_size <= 128G ? ROUND(buf_pool_size * 0.75) G #
42 # buf_pool_size > 128G ? 128G #
43 # #
44 # Following Perl module calculates server memory and evaluates #
45 # innodb_buffer_pool_size and #
46 # innodb_redo_log_capacity #
47 # based on above formula. #
48 # #
49 # NOTE : Memory considered here is in Kilo bytes #
50 ##############################################################################
51 perl;
52 use Sys::MemInfo qw(totalmem freemem totalswap);
53 my $dir = $ENV{'MYSQLTEST_VARDIR'};
54 $total_mem=&totalmem/1024;
55 my $filename="$dir/tmpfile.txt";
56 open(my $fh, '>', $filename) or die "Could not open file '$filename' $!";
57 print $fh "let \$total_mem = $total_mem\; \n";
58 close $fh;
59 EOF
60 # Perl module ends
61 --source $MYSQLTEST_VARDIR/tmpfile.txt
62
63 --let $mem_size1G = 1048576
64 --let $mem_size4G = 4194304
65 --let $mem_size8G = 8388608
66 --let $mem_size128G = 137438953472
67 --let $zero_point_seven_five = 0.75
68 --let $zero_point_five = 0.5
69
70 # Set buffer pool size.
71 # Note: As MTR doesn't have 'else', following sequence of 'if' is important
72 if ($total_mem > $mem_size4G) {
73 --expr $calculated_buffer_pool_size = $total_mem * $zero_point_seven_five
74 }
75 if ($total_mem <= $mem_size4G) {
76 --expr $calculated_buffer_pool_size = $total_mem * $zero_point_five
77 }
78 if ($total_mem < $mem_size1G) {
79 --let $calculated_buffer_pool_size = 131072
80 }
81
82 --disable_query_log
83 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Resizing redo log");
84 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Starting to delete and rewrite redo log files.");
85 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* New redo log files created");
86 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Option innodb_dedicated_server is ignored ");
87 call mtr.add_suppression("\\[Warning\\] .*MY-\\D+.* No argument was provided to --log-bin, and --log-bin-index was not used");
88 --enable_query_log
89
90 --echo
91 --echo ##########################################
92 --echo # INITIAL START #
93 --echo # innodb_dedicated_server = OFF #
94 --echo # innodb_buffer_pool_size = default #
95 --echo # innodb_redo_log_capacity = default #
96 --echo ##########################################
97
98 --disable_query_log
99 SELECT VARIABLE_VALUE AS INNODB_DEDICATED_SERVER
100 FROM performance_schema.global_variables
101 WHERE VARIABLE_NAME='innodb_dedicated_server';
102
103 SELECT VARIABLE_VALUE/(1024*1024) AS BUFFER_POOL_SIZE_IN_MB
104 FROM performance_schema.global_variables
105 WHERE VARIABLE_NAME='innodb_buffer_pool_size';
106
107 SELECT VARIABLE_VALUE/(1024*1024) AS LOG_CAPACITY_IN_MB
108 FROM performance_schema.global_variables
109 WHERE VARIABLE_NAME='innodb_redo_log_capacity';
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 ###########################################
119 let $restart_parameters = restart: --innodb_dedicated_server=ON;
120 let $explicit_default_wait_counter=100000;
121 --source include/restart_mysqld.inc
122
123 ###############################################################################
124 # Get expected values of Buffer pool size and Log file size #
125 ###############################################################################
126 # Note: If calculated value of innodb_buffer_pool_size evaluates to a value #
127 # that is not equal to or a multiple of #
128 # innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances, #
129 # then innodb_buffer_pool_size is automatically adjusted to a value that is #
130 # equal to or a multiple of #
131 # innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances #
132 # that is not less than the specified buffer pool size. #
133 ###############################################################################
134 --disable_query_log
135 set @innodb_chunk_instance_product = @@global.innodb_buffer_pool_chunk_size/1024 * @@global.innodb_buffer_pool_instances;
136 let $expected_innodb_buffer_pool_size = `select ROUND(ceil($calculated_buffer_pool_size/@innodb_chunk_instance_product) * @innodb_chunk_instance_product)`;
137
138 # Set innodb_redo_log_capacity now after buffer_pool_size has been decided.
139 # Note: As MTR doesn't have 'else', following sequence of 'if' is important
140 if ($expected_innodb_buffer_pool_size > $mem_size128G) {
141 --let $calculated_log_capacity = 134217728
142 }
143 if ($expected_innodb_buffer_pool_size <= $mem_size128G) {
144 --let $calculated_log_capacity = `SELECT ROUND($expected_innodb_buffer_pool_size*$zero_point_seven_five/$mem_size1G)*1048576`
145 }
146 if ($expected_innodb_buffer_pool_size <= $mem_size8G) {
147 --let $calculated_log_capacity = `SELECT ROUND($expected_innodb_buffer_pool_size/$mem_size1G)*524288`
148 }
149 if ($expected_innodb_buffer_pool_size < $mem_size1G) {
150 --let $calculated_log_capacity = 102400;
151 }
152 let $expected_innodb_redo_log_capacity = `select $calculated_log_capacity`;
153 --enable_query_log
154
155 --disable_query_log
156 SELECT VARIABLE_VALUE AS INNODB_DEDICATED_SERVER
157 FROM performance_schema.global_variables
158 WHERE VARIABLE_NAME='innodb_dedicated_server';
159
160 let $actual_innodb_buffer_pool_size = `select ROUND(@@global.innodb_buffer_pool_size/1024,2)`;
161 --disable_result_log
162 eval select $expected_innodb_buffer_pool_size = $actual_innodb_buffer_pool_size into @final_pool_size_result;
163 --enable_result_log
164 --echo 1 expected
165 select @final_pool_size_result;
166
167 let $actual_innodb_redo_log_capacity = `select ROUND(@@global.innodb_redo_log_capacity/1024,2)`;
168 --disable_result_log
169 eval select $expected_innodb_redo_log_capacity = $actual_innodb_redo_log_capacity into @final_log_capacity_result;
170 --enable_result_log
171 --echo 1 expected
172 select @final_log_capacity_result;
173 --enable_query_log
174
175 --echo
176 --echo ###########################################
177 --echo # RESTART 2 #
178 --echo # innodb_dedicated_server = ON #
179 --echo # innodb_buffer_pool_size = explicit #
180 --echo # innodb_redo_log_capacity = autoscaled #
181 --echo ###########################################
182 let $restart_parameters = restart: --innodb_dedicated_server=ON --innodb_buffer_pool_size=256M;
183 --source include/restart_mysqld.inc
184
185 --disable_query_log
186 SELECT VARIABLE_VALUE AS INNODB_DEDICATED_SERVER
187 FROM performance_schema.global_variables
188 WHERE VARIABLE_NAME='innodb_dedicated_server';
189
190 SELECT VARIABLE_VALUE/(1024*1024) AS BUFFER_POOL_SIZE_IN_MB
191 FROM performance_schema.global_variables
192 WHERE VARIABLE_NAME='innodb_buffer_pool_size';
193
194 let $actual_innodb_redo_log_capacity = `select ROUND(@@global.innodb_redo_log_capacity/1024,2)`;
195 --disable_result_log
196 eval select $expected_innodb_redo_log_capacity = $actual_innodb_redo_log_capacity into @final_log_capacity_result;
197 --enable_result_log
198 --echo 1 expected
199 select @final_log_capacity_result;
200 --enable_query_log
201
202 --echo # Make sure that the warnings are present in server log.
203 let server_log= $MYSQLTEST_VARDIR/log/mysqld.1.err;
204 let SEARCH_FILE= $server_log;
205 let SEARCH_PATTERN= \\[Warning\\] .*MY-\\d+.* Option innodb_dedicated_server is ignored for innodb_buffer_pool_size;
206 # innodb_buffer_pool_size;
207 --source include/search_pattern.inc
208
209 --echo
210 --echo ###########################################
211 --echo # RESTART 3 #
212 --echo # innodb_dedicated_server = ON #
213 --echo # innodb_buffer_pool_size = autoscaled #
214 --echo # innodb_redo_log_capacity = explicit #
215 --echo ###########################################
216 let $restart_parameters = restart: --innodb_dedicated_server=ON --innodb_redo_log_capacity=50M;
217 let $explicit_default_wait_counter=100000;
218 --source include/restart_mysqld.inc
219
220 --disable_query_log
221 SELECT VARIABLE_VALUE AS INNODB_DEDICATED_SERVER
222 FROM performance_schema.global_variables
223 WHERE VARIABLE_NAME='innodb_dedicated_server';
224
225 let $actual_innodb_buffer_pool_size = `select ROUND(@@global.innodb_buffer_pool_size/1024,2)`;
226 --disable_result_log
227 eval select $expected_innodb_buffer_pool_size = $actual_innodb_buffer_pool_size into @final_pool_size_result;
228 --enable_result_log
229 --echo 1 expected
230 select @final_pool_size_result;
231
232 SELECT VARIABLE_VALUE/(1024*1024) AS LOG_CAPACITY_IN_MB
233 FROM performance_schema.global_variables
234 WHERE VARIABLE_NAME='innodb_redo_log_capacity';
235
236 --echo # Make sure that the warnings are present in server log.
237 let server_log= $MYSQLTEST_VARDIR/log/mysqld.1.err;
238 let SEARCH_FILE= $server_log;
239 let SEARCH_PATTERN= \\[Warning\\] .*MY-\\d+.* Option innodb_dedicated_server is ignored for innodb_redo_log_capacity;
240 --source include/search_pattern.inc
241
242 --echo
243 --echo ###########################################
244 --echo # RESTART 4 #
245 --echo # innodb_dedicated_server = ON #
246 --echo # innodb_buffer_pool_size = explicit #
247 --echo # innodb_redo_log_capacity = explicit #
248 --echo ###########################################
249 let $restart_parameters = restart: --innodb_dedicated_server=ON --innodb_buffer_pool_size=256M --innodb_redo_log_capacity=50M;
250 let $explicit_default_wait_counter=100000;
251 --source include/restart_mysqld.inc
252
253 --disable_query_log
254 select VARIABLE_VALUE as INNODB_DEDICATED_SERVER from performance_schema.global_variables where VARIABLE_NAME='innodb_dedicated_server';
255 --disable_query_log
256 SELECT VARIABLE_VALUE AS INNODB_DEDICATED_SERVER
257 FROM performance_schema.global_variables
258 WHERE VARIABLE_NAME='innodb_dedicated_server';
259
260 SELECT VARIABLE_VALUE/(1024*1024) AS BUFFER_POOL_SIZE_IN_MB
261 FROM performance_schema.global_variables
262 WHERE VARIABLE_NAME='innodb_buffer_pool_size';
263
264 SELECT VARIABLE_VALUE/(1024*1024) AS LOG_CAPACITY_IN_MB
265 FROM performance_schema.global_variables
266 WHERE VARIABLE_NAME='innodb_redo_log_capacity';
267
268 --echo # Make sure that the warnings are present in server log.
269 let server_log= $MYSQLTEST_VARDIR/log/mysqld.1.err;
270 let SEARCH_FILE= $server_log;
271 let SEARCH_PATTERN= \\[Warning\\] .*MY-\\d+.* Option innodb_dedicated_server is ignored for innodb_buffer_pool_size;
272 --source include/search_pattern.inc
273 let SEARCH_PATTERN= \\[Warning\\] .*MY-\\d+.* Option innodb_dedicated_server is ignored for innodb_redo_log_capacity;
274 --source include/search_pattern.inc
275
276 --enable_query_log
277 --remove_file $MYSQLTEST_VARDIR/tmpfile.txt
278
279 #######################################################################################
280 # Check if innodb_dedicated_server can be accessed with and without @@ sign #
281 #######################################################################################
282
283 --Error ER_INCORRECT_GLOBAL_LOCAL_VAR
284 SET innodb_dedicated_server = 0;
285 SELECT @@innodb_dedicated_server;
286
287 --Error ER_UNKNOWN_TABLE
288 SELECT local.innodb_dedicated_server;
289
290 --Error ER_INCORRECT_GLOBAL_LOCAL_VAR
291 SET global innodb_dedicated_server = 0;
292 SELECT @@global.innodb_dedicated_server;
293
294 --Error ER_INCORRECT_GLOBAL_LOCAL_VAR
295 set session innodb_dedicated_server=1;
296 SELECT @@innodb_dedicated_server;
297
298 show global variables like 'innodb_dedicated_server';
299 show session variables like 'innodb_dedicated_server';
300
301 ####################################################################################################
302 # Check if the value of innodb_dedicated_server in GLOBAL Table matches value in variable #
303 ####################################################################################################
304
305 --echo 1 Expected
306 SELECT @@global.innodb_dedicated_server;
307 select * from performance_schema.global_variables where variable_name='innodb_dedicated_server';
308 select * from performance_schema.session_variables where variable_name='innodb_dedicated_server';
309
310 ###################################################################################################
311 # Check if innodb_dedicated_server a non-persistent variable #
312 ###################################################################################################
313 --Error ER_INCORRECT_GLOBAL_LOCAL_VAR
314 set persist_only innodb_dedicated_server=off;
315