summaryrefslogtreecommitdiff
path: root/mysql-test/main/profiling.test
blob: 1e1aada7c152fab0f2797d38edcd3054ac7cf81f (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
--source include/have_profiling.inc

# Verify that the protocol isn't violated if we ask for profiling info
# before profiling has recorded anything.
show profiles;
show profile all;

# default is OFF
show session variables like 'profil%';
select @@profiling;

# But size is okay
set @start_value= @@global.profiling_history_size;
set global profiling_history_size=100;
show global variables like 'profil%';

# turn on for testing
set session profiling = ON;
set session profiling_history_size=30;  # small enough to overflow

# verify it is active
show session variables like 'profil%';
select @@profiling;

#   Profiling is a descriptive look into the way the server operated
#   in retrospect.  Chad doesn't think it's wise to include the result
#   log, as this creates a proscriptive specification about how the 
#   server should work in the future -- or it forces everyone who 
#   changes the server significantly to record the test results again,
#   and that violates the spirit of our tests.  Please don't include
#   execution-specific data here, as in all of the "show profile" and
#   information_schema.profiling results.

create table t1 (
  a int,
  b int
);
insert into t1 values (1,1), (2,null), (3, 4);
insert into t1 values (5,1), (6,null), (7, 4);
insert into t1 values (1,1), (2,null), (3, 4);
insert into t1 values (5,1), (6,null), (7, 4);
select max(x) from (select sum(a) as x from t1 group by b) as teeone;
insert into t1 select * from t1;
select count(*) from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
select count(*) from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
select count(*) from t1;
select sum(a) from t1;
select sum(a) from t1 group by b;
select sum(a) + sum(b) from t1 group by b;
select max(x) from (select sum(a) as x from t1 group by b) as teeone;
select '012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890' as big_string;

--enable_result_log
--replace_column 2 #
show profiles;

--disable_result_log
###--replace_column 2 # 3 # 4 #
show profile for query 15;
###--replace_column 2 # 3 # 4 #
show profile cpu for query 15;
###--replace_column 2 # 3 # 4 # 5 # 6 #
show profile cpu, block io for query 15;
###--replace_column 2 # 3 # 4 #
show profile cpu for query 9 limit 2 offset 2;
show profile cpu for query 10 limit 0;
--error 0,ER_WRONG_ARGUMENTS
show profile cpu for query 65534;
###--replace_column 2 #
show profile memory;
###--replace_column 2 # 3 # 4 #
show profile block io;
###--replace_column 2 # 3 # 4 #
show profile context switches;
###--replace_column 2 # 3 # 4 #
show profile page faults;
###--replace_column 2 # 3 # 4 #
show profile ipc;
###--replace_column 2 #
show profile swaps limit 1 offset 2;
###--replace_column 2 # 5 #
show profile source;
show profile all for query 0 limit 0;
###--replace_column 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 10 # 11 # 12 # 13 # 16 #
show profile all for query 15;
###--replace_column 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 10 # 11 # 12 # 13 # 16 #

select * from information_schema.profiling;
select query_id, state, duration from information_schema.profiling;
select query_id, sum(duration) from information_schema.profiling group by query_id;
select query_id, count(*) from information_schema.profiling group by query_id;
select sum(duration) from information_schema.profiling;

# Broken down into number of stages and duration of each query.
select query_id, count(*), sum(duration) from information_schema.profiling group by query_id;
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;


--enable_result_log
drop table if exists t1, t2, t3;
create table t1 (id int );
create table t2 (id int not null);
create table t3 (id int not null primary key);
insert into t1 values (1), (2), (3);
insert into t2 values (1), (2), (3);
insert into t3 values (1), (2), (3);

--replace_column 2 #
show profiles;

select * from t1;
--replace_column 2 #
show profiles;
--echo This ^^ should end in "select * from t1;"

delete from t1;
insert into t1 values (1), (2), (3);
insert into t1 values (1), (2), (3);

select * from t1;
--replace_column 2 #
show profiles;

# Turning profiling off does freeze it
set session profiling = OFF;
select sum(id) from t1;
--replace_column 2 #
show profiles;

##  Verify that the various juggling of THD contexts doesn't affect profiling.

##  Functions and procedures
set session profiling = ON;
select @@profiling;
create function f1() returns varchar(50) return 'hello';
select @@profiling;
select * from t1 where id <> f1();
select @@profiling;

set session profiling = OFF;
drop table if exists profile_log;
create table profile_log (how_many int);

--disable_warnings
drop procedure if exists p1;
drop procedure if exists p2;
drop procedure if exists p3;
--enable_warnings

delimiter //;
create procedure p1 () 
  modifies sql data 
begin 
  set profiling = ON; 
  select 'This p1 should show up in profiling'; 
  insert into profile_log select count(*) from information_schema.profiling; 
end//
create procedure p2() 
  deterministic 
begin 
  set profiling = ON; 
  call p1(); 
  select 'This p2 should show up in profiling'; 
end//
create procedure p3 () 
  reads sql data 
begin 
  set profiling = ON; 
  select 'This p3 should show up in profiling'; 
  show profile; 
end//
delimiter ;//

--disable_result_log
--echo first call to p1
call p1;
select * from profile_log;
--echo second call to p1
call p1;
select * from profile_log;
--echo third call to p1
call p1;
select * from profile_log;
set session profiling = OFF;
call p2;
set session profiling = OFF;
call p3;

--replace_column 1 # 2 #
show profiles;
--enable_result_log

drop procedure if exists p1;
drop procedure if exists p2;
drop procedure if exists p3;
drop table if exists profile_log;

##  Triggers
set session profiling = ON;
drop table if exists t2;
create table t2 (id int not null);
create trigger t2_bi before insert on t2 for each row set @x=0;
select @@profiling;
insert into t2 values (1), (2), (3);
select @@profiling;

##  ALTER TABLE
set session profiling = ON;
drop table if exists t1, t2;
create table t1 (id int not null primary key);
create table t2 (id int not null primary key, id1 int not null);
select @@profiling;
alter table t2 add foreign key (id1) references t1 (id) on delete cascade;
select @@profiling;

##  Table LOCKing
lock table t1 write; 
select @@profiling;
unlock table; 
select @@profiling;

##  Transactions
set autocommit=0;
select @@profiling, @@autocommit;
begin;
select @@profiling;
insert into t1 values (1);
insert into t2 values (1,1);
--echo testing referential integrity cascade
delete from t1 where id = 1;
select @@profiling;
--echo testing rollback
--disable_warnings
rollback;
--enable_warnings
select @@profiling;
--echo testing commit
begin;
select @@profiling;
commit;
select @@profiling;

drop table if exists t1, t2, t3;
drop view if exists v1;
drop function if exists f1;

## Multiple queries in one packet.  Combo statements don't work with ps-proto.
#--eval select 1; select 2; select 3;
## two continuations, one starting
#select state from information_schema.profiling where seq=1 order by query_id desc limit 3;


## last thing in the file
set session profiling = OFF;
set global profiling_history_size= @start_value;
##
--echo End of 5.0 tests