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