Debian Codegraph @

mysql-test/suite/perfschema/r/statement_digest_query_sample.result

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