Debian Codegraph @

mysql-test/suite/perfschema/t/statement_digest_query_sample.test

raw
1 --echo
2 --echo ## Test Statement Digest Query Sample WL#9830
3 --echo ##
4 --echo ## A query sample is collected for each new maximum wait time, and is
5 --echo ## displayed in events_statements_summary_by_digest.
6 --echo ##
7 --echo ## TEST 1 - Query sampled when max age disabled, max wait exceeded
8 --echo ## TEST 2 - Query sampled when age expires, max wait not exceeded
9 --echo ## TEST 3 - Query not sampled before max age expires, max wait not exceeded
10 --echo ## TEST 4 - Query sampled before max age, max wait exceeded
11 --echo ## TEST 5 - Verify QUERY_SAMPLE_TEXT is limited by performance_schema_max_sql_text_length
12 --echo ## TEST 6 - Verify QUERY_SAMPLE_TIMER_WAIT and QUERY_SAMPLE_SEEN
13
14 # A query sample is taken when:
15 # - It is the first query, or
16 # - The wait time exceeds max_timer_wait, or
17 # - The age of the previous query sample exceeds the maximum query age.
18 #
19 # For other than the first query, the wait time is always examined.
20 # The age criteria forces a query sample refresh when the wait time remains below max_timer_wait.
21
22 --source include/no_protocol.inc
23
24 let $query_select = digest, max_timer_wait, last_seen, query_sample_text, query_sample_seen, query_sample_timer_wait;
25 let $query_into_1 = @digest_1, @max_timer_wait_1, @last_seen_1, @query_sample_text_1, @query_sample_seen_1, @query_sample_timer_wait_1;
26 let $query_into_2 = @digest_2, @max_timer_wait_2, @last_seen_2, @query_sample_text_2, @query_sample_seen_2, @query_sample_timer_wait_2;
27 let $query_into_3 = @digest_3, @max_timer_wait_3, @last_seen_3, @query_sample_text_3, @query_sample_seen_3, @query_sample_timer_wait_3;
28 let $query_from = performance_schema.events_statements_summary_by_digest;
29 let $query_where = digest_text = "UPDATE `t1` SET `s1` = ?";
30
31 let $query_sample_1 =
32 SELECT $query_select
33 INTO $query_into_1
34 FROM $query_from
35 WHERE $query_where;
36
37 let $query_sample_2 =
38 SELECT $query_select
39 INTO $query_into_2
40 FROM $query_from
41 WHERE $query_where;
42
43 let $query_sample_3 =
44 SELECT $query_select
45 INTO $query_into_3
46 FROM $query_from
47 WHERE $query_where;
48
49 let $query_debug_1 = SELECT $query_into_1;
50 let $query_debug_2 = SELECT $query_into_2;
51 let $query_debug_3 = SELECT $query_into_3;
52
53 #--echo DEBUG
54 #--echo $query_sample_1;
55 #--echo $query_sample_2;
56 #--echo $query_sample_3;
57
58 --echo
59 --echo ##### TEST 1 - QUERY SAMPLED WITH NEW MAX WAIT TIME
60 --echo
61 --echo ## 1.1 SETUP
62 USE test;
63 CREATE TABLE t1 (s1 INT);
64 INSERT INTO t1 VALUES (100), (200), (300);
65
66 --echo
67 --echo ## 1.2 DISABLE MAX SAMPLE AGE
68 let $max_age_save = `SELECT @@global.performance_schema_max_digest_sample_age`;
69 SET @@global.performance_schema_max_digest_sample_age = 0;
70
71 TRUNCATE performance_schema.events_statements_summary_by_digest;
72
73 --echo
74 --echo ## 1.3 QUERY_1: UPDATE 3 ROWS
75 UPDATE t1 SET s1 = 400;
76
77 --echo
78 --echo ## 1.4 COLLECT DIGEST, MAX WAIT, QUERY SAMPLE
79 eval $query_sample_1;
80
81 --echo
82 --echo ## 1.5 ADD 48K ROWS
83 --echo
84 INSERT INTO t1 SELECT * FROM t1;
85 INSERT INTO t1 SELECT * FROM t1;
86 INSERT INTO t1 SELECT * FROM t1;
87 INSERT INTO t1 SELECT * FROM t1;
88 INSERT INTO t1 SELECT * FROM t1;
89 INSERT INTO t1 SELECT * FROM t1;
90 INSERT INTO t1 SELECT * FROM t1;
91 INSERT INTO t1 SELECT * FROM t1;
92 INSERT INTO t1 SELECT * FROM t1;
93 INSERT INTO t1 SELECT * FROM t1;
94 INSERT INTO t1 SELECT * FROM t1;
95 INSERT INTO t1 SELECT * FROM t1;
96 INSERT INTO t1 SELECT * FROM t1;
97 INSERT INTO t1 SELECT * FROM t1;
98 INSERT INTO t1 SELECT * FROM t1;
99 FLUSH TABLE test.t1;
100
101 --echo
102 --echo ## 1.6 QUERY_2: UPDATE 12K ROWS
103 UPDATE t1 SET s1 = 500;
104
105 --echo
106 --echo ## 1.7 COLLECT DIGEST, MAX WAIT, QUERY SAMPLE
107 eval $query_sample_2;
108
109 --echo
110 --echo ## 1.8 TRUNCATE, ADD 24 ROWS
111 --echo
112 TRUNCATE t1;
113 INSERT INTO t1 VALUES (100), (200), (300);
114 INSERT INTO t1 SELECT * FROM t1;
115 INSERT INTO t1 SELECT * FROM t1;
116 INSERT INTO t1 SELECT * FROM t1;
117
118 --echo
119 --echo ## 1.9 QUERY_3: UPDATE 24 ROWS
120 UPDATE t1 SET s1 = 600;
121
122 --echo
123 --echo ## 1.10 COLLECT DIGEST, MAX WAIT, QUERY SAMPLE
124 eval $query_sample_3;
125
126 #--echo
127 #--echo ## DEBUG
128 #--echo QUERY_1
129 #eval $query_debug_1;
130 #--echo QUERY_2
131 #eval $query_debug_2;
132 #--echo QUERY_3
133 #eval $query_debug_3;
134
135 --echo
136 --echo ## 1.11 VERIFY THAT DIGESTS MATCH
137 --echo
138 SELECT @digest_1 = @digest_2 AS 'Expect 1';
139 --echo
140 SELECT @digest_2 = @digest_3 AS 'Expect 1';
141
142 --echo
143 --echo ## 1.12 VERIFY MAX_TIMER_WAITS
144 --echo
145 --echo # Max timer wait 1 < max wait 2
146 SELECT @max_timer_wait_1 < @max_timer_wait_2 AS 'Expect 1';
147
148 if (`SELECT @max_timer_wait_1 >= @max_timer_wait_2`)
149 {
150 --echo FAILED: Expected max_timer_wait_1 < max_timer_wait_2
151 --echo QUERY_1
152 eval $query_debug_1;
153 --echo QUERY_2
154 eval $query_debug_2;
155 }
156
157 --echo
158 --echo # Max timer wait 3 = max wait 2, no change
159 SELECT @max_timer_wait_3 = @max_timer_wait_2 AS 'Expect 1';
160
161 --echo
162 --echo ## 1.13. VERIFY QUERY SAMPLES
163 --echo
164 --echo # Query_1: Sample taken, first query
165 SELECT @query_sample_text_1 AS 'Expect UPDATE t1 SET s1 = 400';
166 --echo
167 --echo # Query_2: Sample taken, max_timer_wait exceeded
168 SELECT @query_sample_text_2 AS 'Expect UPDATE t1 SET s1 = 500';
169 --echo
170 --echo # Query_3: No sample taken, max_timer_wait not exceeded, no change
171 SELECT @query_sample_text_3 AS 'Expect UPDATE t1 SET s1 = 500';
172
173
174 --echo
175 --echo ##### TEST 2 - QUERY SAMPLED WHEN MAX SAMPLE AGE EXPIRES, MAX WAIT NOT EXCEEDED
176 --echo
177 --echo ## 2.1 SET MAX SAMPLE AGE TO 3 SECONDS
178 SET @@global.performance_schema_max_digest_sample_age = 3;
179 TRUNCATE performance_schema.events_statements_summary_by_digest;
180 --echo
181 TRUNCATE t1;
182 INSERT INTO t1 VALUES (100), (200), (300);
183 --echo
184 --echo ## 2.2 ADD 12K ROWS
185 --echo
186 INSERT INTO t1 SELECT * FROM t1;
187 INSERT INTO t1 SELECT * FROM t1;
188 INSERT INTO t1 SELECT * FROM t1;
189 INSERT INTO t1 SELECT * FROM t1;
190 INSERT INTO t1 SELECT * FROM t1;
191 INSERT INTO t1 SELECT * FROM t1;
192 INSERT INTO t1 SELECT * FROM t1;
193 INSERT INTO t1 SELECT * FROM t1;
194 INSERT INTO t1 SELECT * FROM t1;
195 INSERT INTO t1 SELECT * FROM t1;
196 INSERT INTO t1 SELECT * FROM t1;
197 INSERT INTO t1 SELECT * FROM t1;
198 FLUSH TABLE test.t1;
199
200 --echo
201 --echo ## 2.3 QUERY_1: UPDATE 12K ROWS TO SET MAX_TIMER_WAIT
202 UPDATE t1 SET s1 = 700;
203
204 --echo
205 --echo ## 2.4 WAIT 10 SECONDS
206 sleep 10;
207
208 --echo
209 --echo ## 2.5 COLLECT DIGEST, MAX WAIT, QUERY SAMPLE, QUERY SEEN
210 --echo
211 eval $query_sample_1;
212
213 --echo
214 --echo ## 2.6 QUERY_2: UPDATE 3 ROWS AGAIN, QUERY SHOULD BE SAMPLED
215 TRUNCATE t1;
216 INSERT INTO t1 VALUES (100), (200), (300);
217 UPDATE t1 SET s1 = 800;
218
219 --echo
220 --echo ## 2.7 COLLECT DIGEST, MAX WAIT, QUERY SAMPLE, QUERY SEEN
221 eval $query_sample_2;
222
223 --echo
224 --echo ## 2.8 VERIFY THAT DIGESTS MATCH
225 --echo
226 SELECT @digest_1 = @digest_2 AS 'Expect 1';
227
228 --echo
229 --echo ## 2.9 VERIFY MAX_TIMER_WAITS, QUERY_SAMPLE_TIMER_WAIT
230 --echo
231 --echo # Max timer wait 1 = max wait 2
232 SELECT @max_timer_wait_1 = @max_timer_wait_2 AS 'Expect 1';
233 SELECT @query_sample_timer_wait_1 > @query_sample_timer_wait_2 AS 'Expect 1';
234 --echo
235
236 if (`SELECT @query_sample_timer_wait_1 <= @query_sample_timer_wait_2`)
237 {
238 --echo FAILED: Expected query_sample_timer_wait_1 > query_sample_timer_wait_2
239 --echo QUERY_1
240 eval $query_debug_1;
241 --echo QUERY_2
242 eval $query_debug_2;
243 }
244
245 --echo ## 2.10 VERIFY QUERY SAMPLES
246 --echo
247 --echo # Query_1: Sample taken, first query
248 SELECT @query_sample_text_1 AS 'Expect UPDATE t1 SET s1 = 700';
249 --echo
250 --echo # Query_2: Sample taken, age expired
251 SELECT @query_sample_text_2 AS 'Expect UPDATE t1 SET s1 = 800';
252 --echo
253
254
255 --echo
256 --echo ##### TEST 3 - QUERY NOT SAMPLED BEFORE AGE EXPIRES, MAX WAIT NOT EXCEEDED
257 --echo
258
259 --echo ## 3.1 SET MAX SAMPLE AGE TO 1000 SECONDS
260 SET @@global.performance_schema_max_digest_sample_age = 1000;
261
262 --echo
263 TRUNCATE t1;
264 INSERT INTO t1 VALUES (100), (200), (300);
265 --echo
266 --echo ## 3.2 ADD 12K ROWS
267 --echo
268 INSERT INTO t1 SELECT * FROM t1;
269 INSERT INTO t1 SELECT * FROM t1;
270 INSERT INTO t1 SELECT * FROM t1;
271 INSERT INTO t1 SELECT * FROM t1;
272 INSERT INTO t1 SELECT * FROM t1;
273 INSERT INTO t1 SELECT * FROM t1;
274 INSERT INTO t1 SELECT * FROM t1;
275 INSERT INTO t1 SELECT * FROM t1;
276 INSERT INTO t1 SELECT * FROM t1;
277 INSERT INTO t1 SELECT * FROM t1;
278 INSERT INTO t1 SELECT * FROM t1;
279 INSERT INTO t1 SELECT * FROM t1;
280 FLUSH TABLE test.t1;
281 TRUNCATE performance_schema.events_statements_summary_by_digest;
282
283 --echo
284 --echo ## 3.2 QUERY_1: UPDATE 12K ROWS TO SET MAX_TIMER_WAIT
285 UPDATE t1 SET s1 = 700;
286
287 --echo
288 --echo ## 3.3 COLLECT DIGEST, MAX WAIT, query_sample_timer_wait, QUERY SAMPLE, QUERY SEEN, LAST SEEN
289 eval $query_sample_1;
290
291 --echo
292 --echo ## 3.4 WAIT 10 SECONDS
293 sleep 10;
294
295 --echo
296 --echo ## 3.5 QUERY_2: UPDATE 3 ROWS, QUERY SHOULD *NOT* BE SAMPLED
297 TRUNCATE t1;
298 INSERT INTO t1 VALUES (100), (200), (300);
299 --echo
300 UPDATE t1 SET s1 = 800;
301
302 --echo
303 --echo ## 3.6 COLLECT DIGEST, MAX WAIT, query_sample_timer_wait, QUERY SAMPLE, QUERY SEEN, LAST SEEN
304 eval $query_sample_2;
305
306 #--echo
307 #--echo ## DEBUG
308 #--echo
309 #SELECT @digest_1, @max_timer_wait_1, @query_sample_timer_wait_1, @query_sample_text_1, @query_sample_seen_1;
310 #--echo
311 #SELECT @digest_2, @max_timer_wait_2, @query_sample_timer_wait_2, @query_sample_text_2, @query_sample_seen_2;
312
313 --echo
314 --echo ## 3.7 VERIFY THAT DIGESTS MATCH
315 --echo
316 SELECT @digest_1 = @digest_2 AS 'Expect 1';
317
318 --echo
319 --echo ## 3.8 VERIFY MAX_TIMER_WAITS, QUERY_SAMPLE_TIMER_WAIT
320 --echo
321 --echo # Max timer wait 1 = max wait 2
322 SELECT @max_timer_wait_1 = @max_timer_wait_2 AS 'Expect 1 because query is not sampled';
323 --echo
324 SELECT @query_sample_timer_wait_1 = @query_sample_timer_wait_2 AS 'Expect 1 because query is not sampled';
325 --echo
326 SELECT @last_seen_1 < @last_seen_2 AS 'Expect 1 because a more recent query with same digest was queried';
327 --echo
328
329 --echo
330 --echo ## 3.9 VERIFY QUERY SAMPLES - Query 1 == Query 2
331 --echo
332 --echo # Query_1: Sampled, first query
333 SELECT @query_sample_text_1 AS 'Expect UPDATE t1 SET s1 = 700';
334 --echo
335 --echo # Query_2: Not sampled, age not expired, max_timer_wait not exceeded, no change
336 SELECT @query_sample_text_2 AS 'Expect UPDATE t1 SET s1 = 700';
337
338
339 --echo
340 --echo ##### TEST 4 - QUERY SAMPLED WHEN MAX SAMPLE AGE DOES NOT EXPIRE BUT MAX WAIT IS EXCEEDED
341 --echo
342 SET @@global.performance_schema_max_digest_sample_age = 10000;
343 --disable_warnings
344 DROP TABLE IF EXISTS t1;
345 --enable_warnings
346 CREATE TABLE t1 (s1 int);
347 INSERT INTO t1 values (100),(200),(300);
348 TRUNCATE performance_schema.events_statements_summary_by_digest;
349
350 --echo
351 --echo ## 4.1 Query 1 to test
352 --echo
353 UPDATE t1 SET s1 = 400;
354 --echo
355 eval $query_sample_1;
356
357 INSERT INTO t1 SELECT * FROM t1;
358 INSERT INTO t1 SELECT * FROM t1;
359 INSERT INTO t1 SELECT * FROM t1;
360 INSERT INTO t1 SELECT * FROM t1;
361 INSERT INTO t1 SELECT * FROM t1;
362 INSERT INTO t1 SELECT * FROM t1;
363 INSERT INTO t1 SELECT * FROM t1;
364 INSERT INTO t1 SELECT * FROM t1;
365 INSERT INTO t1 SELECT * FROM t1;
366 INSERT INTO t1 SELECT * FROM t1;
367
368 --echo
369 --echo ## 4.2 Query 2 to test
370 --echo
371 # It should take longer, and therefore be sampled again,
372 # even when not matching the max age criteria.
373 UPDATE t1 SET s1 = 500;
374 --echo
375 eval $query_sample_2;
376
377 # --echo DEBUG
378 # SELECT @digest_1, @max_timer_wait_1, @query_sample_timer_wait_1, @query_sample_text_1;
379 # SELECT @digest_2, @max_timer_wait_2, @query_sample_timer_wait_2, @query_sample_text_2;
380
381 --echo
382 --echo ## Verify: Digests should be the same
383 SELECT @digest_1 = @digest_2 AS "check_digest";
384 --echo
385 --echo ## The second query is expected to be slower
386 SELECT @max_timer_wait_1 < @max_timer_wait_2 AS "check_wait";
387 --echo
388 --echo ## The sampled query should be SET s1 = 400, then SET s1 = 500
389 SELECT @query_sample_text_1 AS "Expect UPDATE t1 SET s1 = 400";
390 --echo
391 SELECT @query_sample_text_2 AS "Expect UPDATE t1 SET s1 = 500";
392
393
394 --echo
395 --echo
396 --echo ##### TEST 5 - VERIFY QUERY_SAMPLE_SEEN AND QUERY_SAMPLE_TIMER_WAIT
397 --echo
398
399 # Execute a long update to establish a large max_timer_wait.
400 # Set the max age to 2 seconds, then sleep and let the age expire. The next query will be sampled.
401 # Execute an update with query_sample_timer_wait < max_timer_wait.
402 # Verify query_sample_seen == last_seen.
403 # Verify max_timer_wait > query_sample_timer_wait.
404 # Verify query_sample_timer_wait == events_statements_history.timer_wait.
405
406 --disable_warnings
407 DROP TABLE IF EXISTS t1;
408 --enable_warnings
409 CREATE TABLE t1 (s1 int);
410
411 --echo ## 5.1 SET MAX SAMPLE AGE TO 2 SECONDS
412 SET @@global.performance_schema_max_digest_sample_age = 2;
413 TRUNCATE performance_schema.events_statements_summary_by_digest;
414 TRUNCATE performance_schema.events_statements_history;
415 --echo
416 INSERT INTO t1 VALUES (100), (200), (300);
417
418 --echo
419 --echo ## 5.2 ADD 12K ROWS
420 --echo
421 INSERT INTO t1 SELECT * FROM t1;
422 INSERT INTO t1 SELECT * FROM t1;
423 INSERT INTO t1 SELECT * FROM t1;
424 INSERT INTO t1 SELECT * FROM t1;
425 INSERT INTO t1 SELECT * FROM t1;
426 INSERT INTO t1 SELECT * FROM t1;
427 INSERT INTO t1 SELECT * FROM t1;
428 INSERT INTO t1 SELECT * FROM t1;
429 INSERT INTO t1 SELECT * FROM t1;
430 INSERT INTO t1 SELECT * FROM t1;
431 INSERT INTO t1 SELECT * FROM t1;
432 INSERT INTO t1 SELECT * FROM t1;
433 FLUSH TABLE test.t1;
434
435 --echo
436 --echo ## 5.3 QUERY_1: UPDATE 12K ROWS
437 # Set new query sample and max_timer_wait
438 UPDATE t1 SET s1 = 1000;
439
440 --echo
441 --echo ## 5.3a COLLECT DIGEST, MAX WAIT, QUERY SAMPLE, QUERY SEEN
442 eval $query_sample_1;
443
444 #--echo
445 #--echo DEBUG QUERY_1
446 #SELECT @digest_1, @max_timer_wait_1, @last_seen_1, @query_sample_text_1, @query_sample_seen_1, @query_sample_timer_wait_1;
447
448 --echo
449 --echo ## 5.4. WAIT 10 SECONDS, FORCE AGE
450 sleep 10;
451
452 --echo
453 --echo ## 5.5. QUERY_2: UPDATE 3 ROWS
454 # Query should be sampled due to max age
455 TRUNCATE t1;
456 INSERT INTO t1 VALUES (100), (200), (300);
457 UPDATE t1 SET s1 = 1100;
458
459 --echo
460 --echo ## 5.5a COLLECT DIGEST, MAX WAIT, QUERY SAMPLE, QUERY SEEN
461 eval $query_sample_2;
462
463 #--echo
464 #--echo DEBUG QUERY_2
465 #SELECT @digest_2, @max_timer_wait_2, @last_seen_2, @query_sample_text_2, @query_sample_seen_2, @query_sample_timer_wait_2;
466
467 --echo
468 --echo ## 5.6 VERIFY MAX_TIMER_WAIT VS QUERY_SAMPLE_TIMER_WAIT
469 --echo
470 --echo # Verify max_timer_wait has not changed
471 SELECT @max_timer_wait_1 = @max_timer_wait_2 AS 'Expect 1';
472
473 --echo
474 --echo # Verify max_timer_wait > query_sample_timer_wait
475 SELECT @max_timer_wait_2 > @query_sample_timer_wait_2 AS 'Expect 1';
476
477 --echo
478 --echo ## 5.7 VERIFY QUERY_SAMPLE_TIMER_WAIT VS. STATEMENTS HISTORY
479 --echo
480 # Verify that query_sample_timer_wait is the same as in the statement event
481 SELECT COUNT(*) FROM performance_schema.events_statements_history
482 WHERE digest = @digest_2 AND timer_wait = @query_sample_timer_wait_2
483 AND sql_text = @query_sample_text_2;
484
485 --echo
486 --echo ## 5.8 VERIFY QUERY_SAMPLE_SEEN == LAST_SEEN
487 --echo
488 SELECT @query_sample_seen_2 = @last_seen_2 AS 'Expect 1';
489
490
491 --echo
492 --echo ## CLEAN UP
493 --echo
494 DROP TABLE t1;
495 --echo
496 --echo # Restore max sample age
497 eval SET @@global.performance_schema_max_digest_sample_age = $max_age_save;