| blob | c96074eb83022ff5ca167fbe6369eb0fbb56f893 |
1 show profiles;
2 Query_ID Duration Query
3 show profile all;
4 Status Duration CPU_user CPU_system Context_voluntary Context_involuntary Block_ops_in Block_ops_out Messages_sent Messages_received Page_faults_major Page_faults_minor Swaps Source_function Source_file Source_line
5 show session variables like 'profil%';
6 Variable_name Value
7 profiling OFF
8 profiling_history_size 15
9 select @@profiling;
10 @@profiling
11 0
12 set global profiling = ON;
13 ERROR HY000: Variable 'profiling' is a SESSION variable and can't be used with SET GLOBAL
14 set @start_value= @@global.profiling_history_size;
15 set global profiling_history_size=100;
16 show global variables like 'profil%';
17 Variable_name Value
18 profiling OFF
19 profiling_history_size 100
20 set session profiling = ON;
21 set session profiling_history_size=30;
22 show session variables like 'profil%';
23 Variable_name Value
24 profiling ON
25 profiling_history_size 30
26 select @@profiling;
27 @@profiling
28 1
29 create table t1 (
30 a int,
31 b int
32 );
33 insert into t1 values (1,1), (2,null), (3, 4);
34 insert into t1 values (5,1), (6,null), (7, 4);
35 insert into t1 values (1,1), (2,null), (3, 4);
36 insert into t1 values (5,1), (6,null), (7, 4);
37 select max(x) from (select sum(a) as x from t1 group by b) as teeone;
38 max(x)
39 20
40 insert into t1 select * from t1;
41 select count(*) from t1;
42 count(*)
43 24
44 insert into t1 select * from t1;
45 insert into t1 select * from t1;
46 insert into t1 select * from t1;
47 select count(*) from t1;
48 count(*)
49 192
50 insert into t1 select * from t1;
51 insert into t1 select * from t1;
52 insert into t1 select * from t1;
53 select count(*) from t1;
54 count(*)
55 1536
56 select sum(a) from t1;
57 sum(a)
58 6144
59 select sum(a) from t1 group by b;
60 sum(a)
61 2048
62 1536
63 2560
64 select sum(a) + sum(b) from t1 group by b;
65 sum(a) + sum(b)
66 NULL
67 2048
68 4608
69 select max(x) from (select sum(a) as x from t1 group by b) as teeone;
70 max(x)
71 2560
72 select '012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890' as big_string;
73 big_string
74 012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890
75 show profiles;
76 Query_ID Duration Query
77 1 # set session profiling_history_size=30
78 2 # show session variables like 'profil%'
79 3 # select @@profiling
80 4 # create table t1 (
81 a int,
82 b int
83 )
84 5 # insert into t1 values (1,1), (2,null), (3, 4)
85 6 # insert into t1 values (5,1), (6,null), (7, 4)
86 7 # insert into t1 values (1,1), (2,null), (3, 4)
87 8 # insert into t1 values (5,1), (6,null), (7, 4)
88 9 # select max(x) from (select sum(a) as x from t1 group by b) as teeone
89 10 # insert into t1 select * from t1
90 11 # select count(*) from t1
91 12 # insert into t1 select * from t1
92 13 # insert into t1 select * from t1
93 14 # insert into t1 select * from t1
94 15 # select count(*) from t1
95 16 # insert into t1 select * from t1
96 17 # insert into t1 select * from t1
97 18 # insert into t1 select * from t1
98 19 # select count(*) from t1
99 20 # select sum(a) from t1
100 21 # select sum(a) from t1 group by b
101 22 # select sum(a) + sum(b) from t1 group by b
102 23 # select max(x) from (select sum(a) as x from t1 group by b) as teeone
103 24 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345
104 show profile for query 15;
105 show profile cpu for query 15;
106 show profile cpu, block io for query 15;
107 show profile cpu for query 9 limit 2 offset 2;
108 show profile cpu for query 10 limit 0;
109 show profile cpu for query 65534;
110 show profile memory;
111 show profile block io;
112 show profile context switches;
113 show profile page faults;
114 show profile ipc;
115 show profile swaps limit 1 offset 2;
116 show profile source;
117 show profile all for query 0 limit 0;
118 show profile all for query 15;
119 select * from information_schema.profiling;
120 select query_id, state, duration from information_schema.profiling;
121 select query_id, sum(duration) from information_schema.profiling group by query_id;
122 select query_id, count(*) from information_schema.profiling group by query_id;
123 select sum(duration) from information_schema.profiling;
124 select query_id, count(*), sum(duration) from information_schema.profiling group by query_id;
125 select CPU_user, CPU_system, Context_voluntary, Context_involuntary, Block_ops_in, Block_ops_out, Messages_sent, Messages_received, Page_faults_major, Page_faults_minor, Swaps, Source_function, Source_file, Source_line from information_schema.profiling;
126 drop table if exists t1, t2, t3;
127 Warnings:
128 Note 1051 Unknown table 't2'
129 Note 1051 Unknown table 't3'
130 create table t1 (id int );
131 create table t2 (id int not null);
132 create table t3 (id int not null primary key);
133 insert into t1 values (1), (2), (3);
134 insert into t2 values (1), (2), (3);
135 insert into t3 values (1), (2), (3);
136 show profiles;
137 Query_ID Duration Query
138 10 # insert into t1 select * from t1
139 11 # select count(*) from t1
140 12 # insert into t1 select * from t1
141 13 # insert into t1 select * from t1
142 14 # insert into t1 select * from t1
143 15 # select count(*) from t1
144 16 # insert into t1 select * from t1
145 17 # insert into t1 select * from t1
146 18 # insert into t1 select * from t1
147 19 # select count(*) from t1
148 20 # select sum(a) from t1
149 21 # select sum(a) from t1 group by b
150 22 # select sum(a) + sum(b) from t1 group by b
151 23 # select max(x) from (select sum(a) as x from t1 group by b) as teeone
152 24 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345
153 25 # select * from information_schema.profiling
154 26 # select query_id, state, duration from information_schema.profiling
155 27 # select query_id, sum(duration) from information_schema.profiling group by query_id
156 28 # select query_id, count(*) from information_schema.profiling group by query_id
157 29 # select sum(duration) from information_schema.profiling
158 30 # select query_id, count(*), sum(duration) from information_schema.profiling group by query_id
159 31 # select CPU_user, CPU_system, Context_voluntary, Context_involuntary, Block_ops_in, Block_ops_out, Messages_sent, Messages_received, Page_faults_major, Page_faults_minor, Swaps, Source_function, Source_file, Source_line from information_schema.profiling
160 32 # drop table if exists t1, t2, t3
161 33 # SHOW WARNINGS
162 34 # create table t1 (id int )
163 35 # create table t2 (id int not null)
164 36 # create table t3 (id int not null primary key)
165 37 # insert into t1 values (1), (2), (3)
166 38 # insert into t2 values (1), (2), (3)
167 39 # insert into t3 values (1), (2), (3)
168 select * from t1;
169 id
170 1
171 2
172 3
173 show profiles;
174 Query_ID Duration Query
175 11 # select count(*) from t1
176 12 # insert into t1 select * from t1
177 13 # insert into t1 select * from t1
178 14 # insert into t1 select * from t1
179 15 # select count(*) from t1
180 16 # insert into t1 select * from t1
181 17 # insert into t1 select * from t1
182 18 # insert into t1 select * from t1
183 19 # select count(*) from t1
184 20 # select sum(a) from t1
185 21 # select sum(a) from t1 group by b
186 22 # select sum(a) + sum(b) from t1 group by b
187 23 # select max(x) from (select sum(a) as x from t1 group by b) as teeone
188 24 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345
189 25 # select * from information_schema.profiling
190 26 # select query_id, state, duration from information_schema.profiling
191 27 # select query_id, sum(duration) from information_schema.profiling group by query_id
192 28 # select query_id, count(*) from information_schema.profiling group by query_id
193 29 # select sum(duration) from information_schema.profiling
194 30 # select query_id, count(*), sum(duration) from information_schema.profiling group by query_id
195 31 # select CPU_user, CPU_system, Context_voluntary, Context_involuntary, Block_ops_in, Block_ops_out, Messages_sent, Messages_received, Page_faults_major, Page_faults_minor, Swaps, Source_function, Source_file, Source_line from information_schema.profiling
196 32 # drop table if exists t1, t2, t3
197 33 # SHOW WARNINGS
198 34 # create table t1 (id int )
199 35 # create table t2 (id int not null)
200 36 # create table t3 (id int not null primary key)
201 37 # insert into t1 values (1), (2), (3)
202 38 # insert into t2 values (1), (2), (3)
203 39 # insert into t3 values (1), (2), (3)
204 40 # select * from t1
205 This ^^ should end in "select * from t1;"
206 delete from t1;
207 insert into t1 values (1), (2), (3);
208 insert into t1 values (1), (2), (3);
209 select * from t1;
210 id
211 1
212 2
213 3
214 1
215 2
216 3
217 show profiles;
218 Query_ID Duration Query
219 15 # select count(*) from t1
220 16 # insert into t1 select * from t1
221 17 # insert into t1 select * from t1
222 18 # insert into t1 select * from t1
223 19 # select count(*) from t1
224 20 # select sum(a) from t1
225 21 # select sum(a) from t1 group by b
226 22 # select sum(a) + sum(b) from t1 group by b
227 23 # select max(x) from (select sum(a) as x from t1 group by b) as teeone
228 24 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345
229 25 # select * from information_schema.profiling
230 26 # select query_id, state, duration from information_schema.profiling
231 27 # select query_id, sum(duration) from information_schema.profiling group by query_id
232 28 # select query_id, count(*) from information_schema.profiling group by query_id
233 29 # select sum(duration) from information_schema.profiling
234 30 # select query_id, count(*), sum(duration) from information_schema.profiling group by query_id
235 31 # select CPU_user, CPU_system, Context_voluntary, Context_involuntary, Block_ops_in, Block_ops_out, Messages_sent, Messages_received, Page_faults_major, Page_faults_minor, Swaps, Source_function, Source_file, Source_line from information_schema.profiling
236 32 # drop table if exists t1, t2, t3
237 33 # SHOW WARNINGS
238 34 # create table t1 (id int )
239 35 # create table t2 (id int not null)
240 36 # create table t3 (id int not null primary key)
241 37 # insert into t1 values (1), (2), (3)
242 38 # insert into t2 values (1), (2), (3)
243 39 # insert into t3 values (1), (2), (3)
244 40 # select * from t1
245 41 # delete from t1
246 42 # insert into t1 values (1), (2), (3)
247 43 # insert into t1 values (1), (2), (3)
248 44 # select * from t1
249 set session profiling = OFF;
250 select sum(id) from t1;
251 sum(id)
252 12
253 show profiles;
254 Query_ID Duration Query
255 15 # select count(*) from t1
256 16 # insert into t1 select * from t1
257 17 # insert into t1 select * from t1
258 18 # insert into t1 select * from t1
259 19 # select count(*) from t1
260 20 # select sum(a) from t1
261 21 # select sum(a) from t1 group by b
262 22 # select sum(a) + sum(b) from t1 group by b
263 23 # select max(x) from (select sum(a) as x from t1 group by b) as teeone
264 24 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345
265 25 # select * from information_schema.profiling
266 26 # select query_id, state, duration from information_schema.profiling
267 27 # select query_id, sum(duration) from information_schema.profiling group by query_id
268 28 # select query_id, count(*) from information_schema.profiling group by query_id
269 29 # select sum(duration) from information_schema.profiling
270 30 # select query_id, count(*), sum(duration) from information_schema.profiling group by query_id
271 31 # select CPU_user, CPU_system, Context_voluntary, Context_involuntary, Block_ops_in, Block_ops_out, Messages_sent, Messages_received, Page_faults_major, Page_faults_minor, Swaps, Source_function, Source_file, Source_line from information_schema.profiling
272 32 # drop table if exists t1, t2, t3
273 33 # SHOW WARNINGS
274 34 # create table t1 (id int )
275 35 # create table t2 (id int not null)
276 36 # create table t3 (id int not null primary key)
277 37 # insert into t1 values (1), (2), (3)
278 38 # insert into t2 values (1), (2), (3)
279 39 # insert into t3 values (1), (2), (3)
280 40 # select * from t1
281 41 # delete from t1
282 42 # insert into t1 values (1), (2), (3)
283 43 # insert into t1 values (1), (2), (3)
284 44 # select * from t1
285 set session profiling = ON;
286 select @@profiling;
287 @@profiling
288 1
289 create function f1() returns varchar(50) return 'hello';
290 select @@profiling;
291 @@profiling
292 1
293 select * from t1 where id <> f1();
294 id
295 1
296 2
297 3
298 1
299 2
300 3
301 select @@profiling;
302 @@profiling
303 1
304 set session profiling = OFF;
305 drop table if exists profile_log;
306 Warnings:
307 Note 1051 Unknown table 'profile_log'
308 create table profile_log (how_many int);
309 drop procedure if exists p1;
310 drop procedure if exists p2;
311 drop procedure if exists p3;
312 create procedure p1 ()
313 modifies sql data
314 begin
315 set profiling = ON;
316 select 'This p1 should show up in profiling';
317 insert into profile_log select count(*) from information_schema.profiling;
318 end//
319 create procedure p2()
320 deterministic
321 begin
322 set profiling = ON;
323 call p1();
324 select 'This p2 should show up in profiling';
325 end//
326 create procedure p3 ()
327 reads sql data
328 begin
329 set profiling = ON;
330 select 'This p3 should show up in profiling';
331 show profile;
332 end//
333 first call to p1
334 call p1;
335 select * from profile_log;
336 second call to p1
337 call p1;
338 select * from profile_log;
339 third call to p1
340 call p1;
341 select * from profile_log;
342 set session profiling = OFF;
343 call p2;
344 set session profiling = OFF;
345 call p3;
346 show profiles;
347 drop procedure if exists p1;
348 drop procedure if exists p2;
349 drop procedure if exists p3;
350 drop table if exists profile_log;
351 set session profiling = ON;
352 drop table if exists t2;
353 create table t2 (id int not null);
354 create trigger t2_bi before insert on t2 for each row set @x=0;
355 select @@profiling;
356 @@profiling
357 1
358 insert into t2 values (1), (2), (3);
359 select @@profiling;
360 @@profiling
361 1
362 set session profiling = ON;
363 drop table if exists t1, t2;
364 create table t1 (id int not null primary key);
365 create table t2 (id int not null primary key, id1 int not null);
366 select @@profiling;
367 @@profiling
368 1
369 alter table t2 add foreign key (id1) references t1 (id) on delete cascade;
370 select @@profiling;
371 @@profiling
372 1
373 lock table t1 write;
374 select @@profiling;
375 @@profiling
376 1
377 unlock table;
378 select @@profiling;
379 @@profiling
380 1
381 set autocommit=0;
382 select @@profiling, @@autocommit;
383 @@profiling @@autocommit
384 1 0
385 begin;
386 select @@profiling;
387 @@profiling
388 1
389 insert into t1 values (1);
390 insert into t2 values (1,1);
391 testing referential integrity cascade
392 delete from t1 where id = 1;
393 select @@profiling;
394 @@profiling
395 1
396 testing rollback
397 rollback;
398 select @@profiling;
399 @@profiling
400 1
401 testing commit
402 begin;
403 select @@profiling;
404 @@profiling
405 1
406 commit;
407 select @@profiling;
408 @@profiling
409 1
410 drop table if exists t1, t2, t3;
411 drop view if exists v1;
412 Warnings:
413 Note 1051 Unknown table 'test.v1'
414 drop function if exists f1;
415 set session profiling = OFF;
416 set global profiling_history_size= @start_value;
417 End of 5.0 tests