Debian Codegraph @

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

raw
1 # This is a script for MTR with hybrid use.
2 # a) As regression test
3 # Mostly some brute force attempt to stress the internal sx locks of
4 # InnoDB which were introduced by WL#6326+WL#6363.
5 # The file with expected results fits to this variant.
6 # The impact on code coverage is quite good.
7 # b) As testbed for attempts to extend or improve the RQG test wl6326_sql.yy.
8 # The MTR based test uses
9 # - a table t1 with the same layout
10 # - the same stored functions
11 # - the same stored procedure proc_fill_t1 for inserting a configurable
12 # amount of records into t1
13 # like the RQG test wl6326_sql.yy.
14 # Feel free to modify parameters like $max_row_count, $max_con,
15 # $high_load_duration or switch debugging on (let $test_debug= 1).
16 # But please be aware that MTR will most probably report that the test
17 # failed because it got a difference to expected results.
18 # Reasons:
19 # - In general: The file with expected results fits to a) only.
20 # - The actual results might dependend on $max_row_count.
21 # - Additional result sets might be printed.
22 #
23
24 # WL#6326 is about the sx locks (InnoDB feature only).
25 # Runtime properties:
26 # Notebook i5 dual core with HT, MySQL binaries compiled with debug,
27 # max_row_count=10000 rows
28 # vardir on tmpfs : ~ 375
29 # vardir on disk : ~ 546
30
31 --source include/big_test.inc
32
33 # Its intentional to not take the risk that a run with valgrind times out.
34 --source include/not_valgrind.inc
35
36 # FIXME:
37 # Increase the code coverage provided by the current test by
38 # trying "InnoDB Tablespace Monitor" as soon as some bug is fixed
39 # or wait till the deprecated "InnoDB Tablespace Monitor" is
40 # removed.
41
42 # Setup of some parameters
43 # ------------------------
44 # Number of records within every chunk to be added to t1.
45 let $load_unit= 10000;
46 #
47 # Rough number of records in t1 to achieve.
48 # We add chunks of $load_unit rows till the actual number
49 # of rows in the table t1 exceeds $max_row_count.
50 # let $max_row_count= 1000000;
51 # let $max_row_count= 300000;
52 # let $max_row_count= 100000;
53 # let $max_row_count= 30000;
54 let $max_row_count= 10000; # ~ 322s on tmpfs (NB)
55 #
56 # Determine which variant to run.
57 let $test_debug= 0;
58 #
59 # Number of concurrent sessions to be used in the high load test.
60 let $max_con= 10;
61 # Duration of the high load test in seconds.
62 let $high_load_duration= 60;
63
64 # Putting all objects into the SCHEMA my_schema makes the final cleanup easier.
65 # We simply run than DROP SCHEMA my_schema.
66 CREATE SCHEMA my_schema;
67 USE my_schema;
68 CREATE FUNCTION f_thread_id (i INT) RETURNS CHAR(4) DETERMINISTIC
69 RETURN CONCAT(LPAD(CAST(i AS CHAR),3,'_'),'_') ;
70 SELECT CONCAT('->', f_thread_id( 1), '<-');
71 SELECT CONCAT('->', f_thread_id(12), '<-');
72
73 # Definition of parameters used in functions.
74 # We use here a "1" in order to make the impact on the results of the functions
75 # good visible.
76 SET @extra_int = 1;
77 SET @extra_string = f_thread_id(@extra_int);
78 SELECT @extra_int , @extra_string;
79
80 # The different functions are used later when filling t1 and also during
81 # RQG testing. They serve to generate the difference between column values
82 # in different rows in different areas of the column.
83 # Fictional example:
84 # row 1 col_int0=1 colx='1abcdefgh' coly='abcd1efgh' colz='abcdefgh1'
85 # row 2 col_int0=2 colx='2abcdefgh' coly='abcd2efgh' colz='abcdefgh2'
86 # The function f_<pattern> is for the column with the name <pattern>.
87 # There is a function
88 # - for every column except col_int0
89 # - even if the SQL for generating the value is simple.
90 # The reason for this is the architecture of the RQG test.
91
92 let $part= AS my_result
93 FROM (SELECT 1 AS my_col UNION SELECT 12 UNION SELECT 123
94 UNION SELECT 1234 UNION SELECT 12345) AS tx;
95
96 let $function_name= f_col_int1;
97 eval CREATE FUNCTION $function_name (i INT) RETURNS INT(20) DETERMINISTIC
98 RETURN i * 1000 + @extra_int ;
99 eval SELECT $function_name(my_col) $part;
100
101 let $function_name= f_col_int2;
102 eval CREATE FUNCTION $function_name (i INT) RETURNS INT(20) DETERMINISTIC
103 RETURN @extra_int * 10000000 + i ;
104 eval SELECT $function_name(my_col) $part;
105
106 let $function_name= f_col_int3;
107 eval CREATE FUNCTION $function_name (i INT) RETURNS INT(20) DETERMINISTIC
108 RETURN @extra_int ;
109 eval SELECT $function_name(my_col) $part;
110
111 let $function_name= f_col_blob;
112 eval CREATE FUNCTION $function_name (i INT) RETURNS BLOB DETERMINISTIC
113 RETURN RPAD(@extra_string,(@@innodb_page_size / 2 ) + 1,'a');
114 eval SELECT CONCAT('->', SUBSTR($function_name(my_col) FROM 1 FOR 10),
115 '<-.....->', SUBSTR($function_name(my_col) FROM -10 FOR 10), '<-') $part;
116
117 let $function_name= f_col_char0;
118 eval CREATE FUNCTION $function_name (i INT) RETURNS CHAR(255) DETERMINISTIC
119 RETURN LPAD(CAST(i AS CHAR),255,' ');
120 eval SELECT CONCAT('->', $function_name(my_col), '<-') $part;
121
122 let $function_name= f_col_char1;
123 eval CREATE FUNCTION $function_name (i INT) RETURNS CHAR(26) DETERMINISTIC
124 RETURN
125 CONCAT('B',
126 LPAD(SUBSTR(CAST(i AS CHAR),1,(LENGTH(CAST(i AS CHAR)) DIV 2)),10,' '),
127 @extra_string,
128 RPAD(SUBSTR(CAST(i AS CHAR), -((LENGTH(CAST(i AS CHAR)) + 1) DIV 2)),10,' '),
129 'E') ;
130 eval SELECT CONCAT('->', $function_name(my_col), '<-') $part;
131
132 let $function_name= f_col_char2;
133 eval CREATE FUNCTION $function_name (i INT) RETURNS CHAR(26) DETERMINISTIC
134 RETURN
135 CONCAT('B',
136 RPAD(SUBSTR(CAST(i AS CHAR),1,(LENGTH(CAST(i AS CHAR)) DIV 2)),10,' '),
137 @extra_string,
138 LPAD(SUBSTR(CAST(i AS CHAR), -((LENGTH(CAST(i AS CHAR)) + 1) DIV 2)),10,' '),
139 'E');
140 eval SELECT CONCAT('->', $function_name(my_col), '<-') $part;
141
142 let $function_name= f_col_char3;
143 eval CREATE FUNCTION $function_name (i INT) RETURNS CHAR(26) DETERMINISTIC
144 RETURN
145 CONCAT('B',@extra_string,LPAD(CAST(i AS CHAR),20,' '),'E');
146 eval SELECT CONCAT('->', $function_name(my_col), '<-') $part;
147
148 let $function_name= f_col_char4;
149 eval CREATE FUNCTION $function_name (i INT) RETURNS CHAR(26) DETERMINISTIC
150 RETURN
151 CONCAT('B',RPAD(CAST(i AS CHAR),20,' '),@extra_string,'E');
152 eval SELECT CONCAT('->', $function_name(my_col), '<-') $part;
153
154 # Auxiliary table for figuring out the impact of scenarios on
155 # information_schema.innodb_metrics content.
156 CREATE TABLE my_metrics LIKE information_schema.innodb_metrics;
157 ALTER TABLE my_metrics ADD COLUMN phase ENUM('after', 'before'),
158 DROP COLUMN SUBSYSTEM, DROP COLUMN TYPE, DROP COLUMN COMMENT,
159 ADD PRIMARY KEY (NAME,phase);
160 let $empty_my_metrics= DELETE FROM my_metrics;
161 let $before_my_metrics= INSERT INTO my_metrics
162 SELECT NAME, COUNT, MAX_COUNT, MIN_COUNT, AVG_COUNT,
163 COUNT_RESET, MAX_COUNT_RESET, MIN_COUNT_RESET, AVG_COUNT_RESET,
164 TIME_ENABLED, TIME_DISABLED, TIME_ELAPSED, TIME_RESET,
165 STATUS, 'before'
166 FROM information_schema.innodb_metrics
167 WHERE NAME LIKE 'innodb_rwlock_sx_%';
168 let $after_my_metrics= INSERT INTO my_metrics
169 SELECT NAME, COUNT, MAX_COUNT, MIN_COUNT, AVG_COUNT,
170 COUNT_RESET, MAX_COUNT_RESET, MIN_COUNT_RESET, AVG_COUNT_RESET,
171 TIME_ENABLED, TIME_DISABLED, TIME_ELAPSED, TIME_RESET,
172 STATUS, 'after'
173 FROM information_schema.innodb_metrics
174 WHERE NAME LIKE 'innodb_rwlock_sx_%';
175 let $print_metrics= SELECT NAME, COUNT, MAX_COUNT, MIN_COUNT, AVG_COUNT,
176 COUNT_RESET, MAX_COUNT_RESET, MIN_COUNT_RESET, AVG_COUNT_RESET,
177 TIME_ENABLED, TIME_DISABLED, TIME_ELAPSED, TIME_RESET, STATUS
178 FROM information_schema.innodb_metrics
179 WHERE NAME LIKE 'innodb_rwlock_sx_%'
180 ORDER BY NAME;
181
182 # The main table for testing.
183 CREATE TABLE t1 (
184 col_int0 BIGINT,
185 col_int1 BIGINT,
186 col_int2 BIGINT,
187 col_int3 BIGINT,
188 col_blob BLOB,
189 col_char0 VARCHAR(255),
190 col_char1 VARCHAR(30),
191 col_char2 VARCHAR(30),
192 col_char3 VARCHAR(30),
193 col_char4 VARCHAR(30)
194 ) ENGINE = InnoDB charset latin1;
195
196 # Use many indexes with mostly significant size in order to cause
197 # some heavy use of sx locks during data generation.
198 ALTER TABLE t1 ADD UNIQUE KEY uidx_col_int0 (col_int0),
199 ADD UNIQUE KEY uidx1 (col_int1, col_char0),
200 ADD UNIQUE KEY uidx2 (col_int2, col_char0, col_int1),
201 ADD UNIQUE KEY uidx3 (col_int3, col_int2, col_char0),
202 ADD UNIQUE KEY uidx4 (col_char1, col_char0),
203 ADD UNIQUE KEY uidx5 (col_char2, col_char0, col_char1),
204 ADD UNIQUE KEY uidx6 (col_char3, col_char2, col_char0),
205 ADD UNIQUE KEY uidx7 (col_int1, col_int2, col_int3, col_char4,
206 col_char1, col_char2, col_char3, col_char0),
207 ADD KEY idx8 (col_blob(10), col_char4);
208
209 delimiter |;
210 CREATE PROCEDURE proc_fill_t1 (max_row_count INT, load_unit INT)
211 BEGIN
212 DECLARE my_count INTEGER DEFAULT 0;
213 DECLARE max_load_count INTEGER DEFAULT 0;
214 DROP TABLE IF EXISTS t0;
215 CREATE TEMPORARY TABLE t0 (col_int0 BIGINT, PRIMARY KEY(col_int0));
216 WHILE (my_count < load_unit ) DO
217 SET my_count = my_count + 1;
218 INSERT INTO t0 SET col_int0 = my_count;
219 END WHILE;
220 SET max_load_count = (SELECT (max_row_count DIV load_unit) + 1 );
221 SELECT COUNT(col_int0) INTO @val FROM t1;
222 SET my_count = 0;
223 REPEAT
224 INSERT INTO t1 (col_int0, col_int1, col_int2, col_int3, col_blob,
225 col_char0, col_char1, col_char2,col_char3,col_char4)
226 SELECT col_int0 + @val,
227 f_col_int1(col_int0 + @val),
228 f_col_int2(col_int0 + @val),
229 f_col_int3(col_int0 + @val),
230 f_col_blob(col_int0 + @val),
231 f_col_char0(col_int0 + @val),
232 f_col_char1(col_int0 + @val),
233 f_col_char2(col_int0 + @val),
234 f_col_char3(col_int0 + @val),
235 f_col_char4(col_int0 + @val)
236 FROM t0;
237 COMMIT;
238 SELECT MAX(col_int0) INTO @val FROM t1;
239 SET my_count = my_count + 1;
240 UNTIL( my_count > max_load_count OR @val >= max_row_count )
241 END REPEAT;
242 DROP TEMPORARY TABLE t0;
243 END|
244 delimiter ;|
245
246 delimiter |;
247 CREATE PROCEDURE proc_dml (max_duration INT, t1_stripe_half INT)
248 BEGIN
249 DECLARE aux INTEGER DEFAULT 0;
250 DECLARE start_time INT;
251 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND BEGIN END;
252
253 SET @extra_int = CONNECTION_ID();
254 SET @extra_string = f_thread_id(@extra_int);
255 SELECT ROUND(MAX(col_int0) / 2 ) INTO @t1_half FROM t1;
256 # The user lock 'Blocker' should be already set by some other session S1.
257 # S1 starts the race by releasing that lock.
258 # Wait till the lock is released and the lock can be obtained.
259 # In order to prevent endless waiting in case of non foreseen problems
260 # limit the timespan to 30 seconds.
261 SELECT GET_LOCK('Blocker', 30) INTO @aux;
262 # Release the lock immediate so that the other "runner" sessions start too.
263 SELECT RELEASE_LOCK('Blocker') INTO @aux;
264 SET start_time = UNIX_TIMESTAMP();
265
266 WHILE (UNIX_TIMESTAMP() - start_time < max_duration) DO
267 SET @aux = @t1_half - t1_stripe_half + ROUND(RAND() * t1_stripe_half * 2);
268 UPDATE t1 SET
269 col_int1 = f_col_int1(col_int0),
270 col_int2 = f_col_int2(col_int0),
271 col_int3 = f_col_int3(col_int0),
272 col_blob = f_col_blob(col_int0),
273 col_char0 = f_col_char0(col_int0),
274 col_char1 = f_col_char1(col_int0),
275 col_char2 = f_col_char2(col_int0),
276 col_char3 = f_col_char3(col_int0),
277 col_char4 = f_col_char4(col_int0)
278 WHERE col_int0 = @aux;
279 COMMIT;
280 END WHILE;
281 END|
282 delimiter ;|
283
284 SET GLOBAL innodb_monitor_disable = "innodb_rwlock_sx_%";
285 SET @pre_reset_ts = NOW();
286 --sleep 1.1
287 SET GLOBAL innodb_monitor_reset = "innodb_rwlock_sx_%";
288 SET @pre_enable_ts = NOW();
289 --sleep 1.1
290 SET GLOBAL innodb_monitor_enable = "innodb_rwlock_sx_%";
291 --sleep 1.1
292 SET @pre_collect_ts = NOW();
293 eval $empty_my_metrics;
294 eval $before_my_metrics;
295 --echo # TC-01 There are exact three entries "innodb_rwlock_sx_%" with the
296 --echo # with the name which follow in innodb_metrics.
297 let $check_statement=
298 SELECT COUNT(*) <> 3 FROM my_metrics
299 WHERE NAME IN ('innodb_rwlock_sx_spin_waits',
300 'innodb_rwlock_sx_spin_rounds',
301 'innodb_rwlock_sx_os_waits');
302 if(`$check_statement`)
303 {
304 --echo # fail
305 eval $check_statement;
306 SELECT NAME FROM my_metrics
307 ORDER BY NAME;
308 exit;
309 }
310 --echo # pass
311
312 SELECT COUNT(*) INTO @sx_count FROM my_metrics;
313
314 --echo # TC-02 Counting is now enabled. ALL = @sx_count entries show that.
315 let $check_statement=
316 SELECT COUNT(*) <> @sx_count FROM my_metrics
317 WHERE STATUS = 'enabled';
318 if(`$check_statement`)
319 {
320 --echo # fail
321 eval $check_statement;
322 SELECT NAME, STATUS FROM my_metrics
323 ORDER BY NAME;
324 exit;
325 }
326 --echo # pass
327
328 --echo # TC-03 @pre_reset_ts < TIME_RESET. ALL = @sx_count entries show that.
329 let $check_statement=
330 SELECT COUNT(*) <> @sx_count FROM my_metrics
331 WHERE @pre_reset_ts < TIME_RESET;
332 if(`$check_statement`)
333 {
334 --echo # fail
335 eval $check_statement;
336 SELECT NAME, @pre_reset_ts, TIME_RESET FROM my_metrics
337 ORDER BY NAME;
338 exit;
339 }
340 --echo # pass
341
342 --echo # TC-04 @pre_enable_ts < TIME_ENABLED. ALL = @sx_count entries show that.
343 let $check_statement=
344 SELECT COUNT(*) <> @sx_count FROM my_metrics
345 WHERE @pre_enable_ts < TIME_ENABLED;
346 if(`$check_statement`)
347 {
348 --echo # fail
349 eval $check_statement;
350 SELECT NAME, @pre_enable_ts, TIME_ENABLED FROM my_metrics
351 ORDER BY NAME;
352 exit;
353 }
354 --echo # pass
355
356 --echo # TC-05 TIME_RESET < TIME_ENABLED AND TIME_ENABLED < @pre_collect_ts
357 --echo # AND TIME_ELAPSED > 0. ALL = @sx_count entries show that.
358 let $check_statement=
359 SELECT COUNT(*) <> @sx_count FROM my_metrics
360 WHERE TIME_RESET < TIME_ENABLED AND TIME_ENABLED < @pre_collect_ts
361 AND TIME_ELAPSED > 0;
362 if(`$check_statement`)
363 {
364 --echo # fail
365 eval $check_statement;
366 SELECT NAME, @pre_collect_ts, TIME_RESET, TIME_ENABLED, TIME_ELAPSED
367 FROM my_metrics
368 ORDER BY NAME;
369 exit;
370 }
371 --echo # pass
372
373 --echo # TC-06 COUNT_RESET = MAX_COUNT_RESET. ALL = @sx_count entries show that.
374 let $check_statement=
375 SELECT COUNT(*) <> @sx_count FROM my_metrics
376 WHERE COUNT_RESET = MAX_COUNT_RESET;
377 if(`$check_statement`)
378 {
379 --echo # fail
380 eval $check_statement;
381 SELECT NAME, COUNT_RESET, MAX_COUNT_RESET FROM my_metrics
382 ORDER BY NAME;
383 exit;
384 }
385 --echo # pass
386
387 SET GLOBAL innodb_monitor_reset = "innodb_rwlock_sx_%";
388 --disable_result_log
389 if($test_debug)
390 {
391 --enable_result_log
392 eval $print_metrics;
393 }
394 SHOW ENGINE INNODB STATUS;
395 --enable_result_log
396
397 eval $empty_my_metrics;
398 eval $before_my_metrics;
399 # These values (the "0") help to identify later if some record is in its
400 # initial state or already modified.
401 SET @extra_string = '__0_';
402 SET @extra_int = 0;
403
404 --echo # TC-07 One session inserts some significant amount of rows into t1.
405 --echo # The system MUST survive that.
406 --replace_result $max_row_count <max_row_count>
407 eval SET @max_row_count = $max_row_count;
408 --replace_result $load_unit <load_unit>
409 eval SET @load_unit = $load_unit;
410 SET @start_time = UNIX_TIMESTAMP();
411 SET AUTOCOMMIT = OFF;
412 CALL proc_fill_t1 (@max_row_count, @load_unit);
413 --echo # pass
414 SET AUTOCOMMIT = ON;
415 SELECT col_int0 INTO @t1_half FROM t1
416 WHERE col_int0 >= (@val DIV 2) ORDER BY col_int0 LIMIT 1;
417
418 --disable_result_log
419 if($test_debug)
420 {
421 --enable_result_log
422 SELECT COUNT(*) AS table_row_count,
423 UNIX_TIMESTAMP() - @start_time AS fill_run_time
424 FROM t1;
425 eval $print_metrics;
426 }
427 SHOW ENGINE INNODB STATUS;
428 --enable_result_log
429
430 # Show that the value distribution is according to the plan.
431 --vertical_results
432 SELECT col_int0, col_int1, col_int2, col_int3,
433 CONCAT('->', SUBSTR(col_blob FROM 1 FOR 10),
434 '<-.....->', SUBSTR(col_blob FROM -10 FOR 10), '<-') AS col_blobx,
435 CONCAT('->',col_char0,'<-') AS col_char0x,
436 CONCAT('->',col_char1,'<-') AS col_char1x,
437 CONCAT('->',col_char2,'<-') AS col_char2x,
438 CONCAT('->',col_char3,'<-') AS col_char3x,
439 CONCAT('->',col_char4,'<-') AS col_char4x
440 FROM t1 WHERE col_int0 between 98 AND 102;
441 --horizontal_results
442
443 # For experiments/interest only. Please do not remove that.
444 if (0)
445 {
446 ANALYZE TABLE t1;
447 SELECT n_rows, clustered_index_size, sum_of_other_index_sizes
448 FROM mysql.innodb_table_stats;
449
450 # SELECT * FROM mysql.innodb_index_stats;
451 # idx_col_int3_int0 n_diff_pfx01 1 col_int3
452 # idx_col_int3_int0 n_diff_pfx02 10000 col_int3,col_int0
453 # idx_col_int3_int0 n_diff_pfx03 10000 col_int3,col_int0,DB_ROW_ID
454 # idx_col_int3_int0 n_leaf_pages 19 Number of leaf pages in the index
455 # idx_col_int3_int0 size 20 Number of pages in the index
456
457 --vertical_results
458 SELECT t1.index_name, t1.stat_value AS idx_pages, t2.stat_value AS idx_leaf_pages,
459 (t1.stat_value - t2.stat_value - 1) / t1.stat_value AS sx_page_ratio
460 FROM mysql.innodb_index_stats t1, mysql.innodb_index_stats t2
461 WHERE t1.index_name = t2.index_name
462 AND t1.stat_name = 'size' AND t2.stat_name = 'n_leaf_pages'
463 ORDER BY t1.index_name;
464 --horizontal_results
465 }
466
467 --echo # TC-11 Several concurrent sessions perform updates in t1 like mad.
468 --echo # The system MUST survive this.
469 --echo # Printing of statements is partially suppressed.
470 SET @start_time = UNIX_TIMESTAMP();
471 SELECT 1 FROM t1 WHERE col_int0 = @t1_half FOR UPDATE;
472 SELECT GET_LOCK('Blocker', 1000) ;
473 --disable_query_log
474 let $num= $max_con;
475 while ($num)
476 {
477 --connect (con$num,localhost,root,,)
478 USE my_schema;
479 # The second parameter of the procedure is size of the affected stripe / 2.
480 # A smaller stripe causes some smaller counter growth but most probably
481 # also more stress around locking in general.
482 # Example # (nnnn) = half stripe size
483 # NAME | COUNT_RESET (5000) | COUNT_RESET (100)
484 # -----------------------------+--------------------+----------------
485 # innodb_rwlock_sx_os_waits | 1412 | 486
486 # innodb_rwlock_sx_spin_rounds | 44061 | 17031
487 # innodb_rwlock_sx_spin_waits | 996 | 515
488 --send
489 eval CALL proc_dml($high_load_duration,@t1_half);
490 dec $num;
491 }
492 --connection default
493 SELECT RELEASE_LOCK('Blocker') ;
494 --sleep 3
495 COMMIT;
496 let $num= $max_con;
497 while ($num)
498 {
499 --connection con$num
500 --reap
501 dec $num;
502 }
503 --echo # pass
504 --connection default
505 --enable_query_log
506
507 # let $wait_timeout= 181;
508 # --source include/wait_condition.inc
509 # eval $after_my_metrics;
510 --disable_result_log
511 if($test_debug)
512 {
513 --enable_result_log
514 SELECT UNIX_TIMESTAMP() - @start_time AS update_battle_run_time;
515 eval $print_metrics;
516 }
517 SHOW ENGINE INNODB STATUS;
518 --enable_result_log
519
520 --echo # TC-13 One session performs ALTER TABLE t1 ADD KEY ... on the fat table t1.
521 --echo # The system MUST survive this.
522 SET @start_time = UNIX_TIMESTAMP();
523 ALTER TABLE t1 ADD KEY idx_col_char4_col_char0 (col_char4,col_char0);
524 --disable_result_log
525 if($test_debug)
526 {
527 --enable_result_log
528 SELECT UNIX_TIMESTAMP() - @start_time AS add_key_run_time;
529 eval $print_metrics;
530 }
531 SHOW ENGINE INNODB STATUS;
532 --enable_result_log
533 --echo # pass
534
535 --echo # TC-15 One session performs a fat update on the fat table t1.
536 --echo # The system MUST survive this.
537 SET @start_time = UNIX_TIMESTAMP();
538 SET @extra_int = 13;
539 SET @extra_string = f_thread_id(@extra_int);
540 eval UPDATE t1 SET
541 col_int1 = f_col_int1(col_int0), col_int2 = f_col_int2(col_int0),
542 col_int3 = f_col_int3(col_int0), col_blob = f_col_blob(col_int0),
543 col_char0 = f_col_char0(col_int0), col_char1 = f_col_char1(col_int0),
544 col_char2 = f_col_char2(col_int0), col_char3 = f_col_char3(col_int0),
545 col_char4 = f_col_char4(col_int0)
546 WHERE col_int0 BETWEEN @t1_half - 2500 AND @t1_half + 2500;
547 COMMIT;
548 --disable_result_log
549 if($test_debug)
550 {
551 --enable_result_log
552 SELECT UNIX_TIMESTAMP() - @start_time AS total_update_run_time;
553 eval $print_metrics;
554 }
555 SHOW ENGINE INNODB STATUS;
556 --enable_result_log
557 --echo # pass
558
559 # Basically every of the big activities causes some counter growth.
560 # But caused by
561 # - the architecture of InnoDB (certain things happen asynchronous)
562 # - the actual test configuration (server/InnoDB options)
563 # - conditions like parallel (./mtr --parallel=auto?) load on the testing box
564 # this might be not fulfilled per single big activity every time except
565 # we go with huge waits or similar.
566 # Observation:
567 # - non debug binaries: expectation frequent not fulfilled
568 # - debug binaries: expectation rare not fulfilled
569 #
570 --echo # NOTE: The fix for Bug#32225367: RW_LOCK_STATS OPERATION HAS REGRESSION FROM 5.6, LOWER CACHE EFFICIENCY
571 --echo # deprecated the "innodb_rwlock_*" counter growth for performance.
572 eval $after_my_metrics;
573
574 --echo # TC-16 The following activities happend after reset in innodb_metrics
575 --echo # - Insert some significant amount of rows into t1.
576 --echo # - Several concurrent users perform excessive updates in t1.
577 --echo # - ALTER TABLE ... ADD KEY <sufficient big enough structure>
578 --echo # - One UPDATE statement modifying a huge slice of t1.
579 --echo # Any of them causes heavy use of SX lock and therefore COUNT_RESET
580 --echo # must have grown for ALL = @sx_count entries.
581 # The former testcases TC-10 and TC12 had to be made a part of this testcase
582 # because their results were unstable.
583 --echo # NOTE: The fix for Bug#32225367: RW_LOCK_STATS OPERATION HAS REGRESSION FROM 5.6, LOWER CACHE EFFICIENCY
584 --echo # deprecated the "innodb_rwlock_*" counter growth for performance.
585 --echo # skip
586
587 --echo # TC-09 Heavy activity after reset.
588 --echo # COUNT_RESET = MAX_COUNT_RESET for ALL = @sx_count entries
589 --echo # needs to stay valid though he counters will have grown.
590 let $check_statement=
591 SELECT COUNT(*) <> @sx_count FROM my_metrics
592 WHERE phase = 'after' AND COUNT_RESET = MAX_COUNT_RESET;
593 if(`$check_statement`)
594 {
595 --echo # fail
596 eval $check_statement;
597 SELECT * FROM my_metrics
598 ORDER BY NAME, phase;
599 exit;
600 }
601 --echo # pass
602
603 eval $empty_my_metrics;
604 eval $before_my_metrics;
605 SET GLOBAL innodb_monitor_reset = "innodb_rwlock_sx_%";
606 eval $after_my_metrics;
607 --echo # TC-08 There was a reset. COUNT_RESET = MAX_COUNT_RESET for ALL
608 --echo # = @sx_count entries.
609 let $check_statement=
610 SELECT COUNT(*) <> @sx_count FROM my_metrics
611 WHERE phase = 'before' AND COUNT_RESET = MAX_COUNT_RESET;
612 if(`$check_statement`)
613 {
614 --echo # fail
615 eval $check_statement;
616 SELECT * FROM my_metrics
617 ORDER BY NAME, phase;
618 exit;
619 }
620 --echo # pass
621
622 --echo # TC-17 We had heavy activity causing big counters and after that a reset.
623 --echo # Reset causes COUNT > COUNT_RESET AND MAX_COUNT > MAX_COUNT_RESET
624 --echo # for ALL @sx_count entries.
625 --echo # NOTE: The fix for Bug#32225367: RW_LOCK_STATS OPERATION HAS REGRESSION FROM 5.6, LOWER CACHE EFFICIENCY
626 --echo # deprecated the "innodb_rwlock_*" counter growth for performance.
627 --echo # skip
628
629 --echo # TC-18 We had some reset but this must not decrease COUNT or MAX_COUNT
630 --echo # after.COUNT >= before.COUNT AND
631 --echo # after.MAX_COUNT >= before.MAX_COUNT for ALL @sx_count entries.
632 let $check_statement=
633 SELECT COUNT(*) <> @sx_count FROM my_metrics t_after JOIN my_metrics t_before
634 ON t_after.COUNT >= t_before.COUNT AND t_after.MAX_COUNT >= t_before.MAX_COUNT
635 AND t_after.NAME = t_before.NAME
636 WHERE t_after.phase = 'after' AND t_before.phase = 'before';
637 if(`$check_statement`)
638 {
639 --echo # fail
640 eval $check_statement;
641 SELECT * FROM my_metrics
642 ORDER BY NAME, phase;
643 exit;
644 }
645 --echo # pass
646
647 --echo # TC-19 We had some reset after heavy activity and this must cause
648 --echo # after.COUNT_RESET < before.COUNT_RESET
649 --echo # AND after.MAX_COUNT_RESET < before.MAX_COUNT_RESET AND
650 --echo # for ALL @sx_count entries.
651 --echo # NOTE: The fix for Bug#32225367: RW_LOCK_STATS OPERATION HAS REGRESSION FROM 5.6, LOWER CACHE EFFICIENCY
652 --echo # deprecated the "innodb_rwlock_*" counter growth for performance.
653 --echo # skip
654
655 # Cleanup
656 let $num= $max_con;
657 while ($num)
658 {
659 --connection con$num
660 --disconnect con$num
661 --source include/wait_until_disconnected.inc
662 dec $num;
663 }
664 --connection default
665 USE test;
666 DROP SCHEMA my_schema;
667 SET GLOBAL innodb_monitor_disable = all;
668 SET GLOBAL innodb_monitor_reset_all = all;
669 SET GLOBAL innodb_monitor_disable = "innodb_rwlock_sx_%";
670 SET GLOBAL innodb_monitor_reset = "innodb_rwlock_sx_%";
671
672 --source include/innodb_monitor_restore.inc