1show profiles; 2Query_ID Duration Query 3show profile all; 4Status 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 5show session variables like 'profil%'; 6Variable_name Value 7profiling OFF 8profiling_history_size 15 9select @@profiling; 10@@profiling 110 12set @start_value= @@global.profiling_history_size; 13set global profiling_history_size=100; 14show global variables like 'profil%'; 15Variable_name Value 16profiling OFF 17profiling_history_size 100 18set session profiling = ON; 19set session profiling_history_size=30; 20show session variables like 'profil%'; 21Variable_name Value 22profiling ON 23profiling_history_size 30 24select @@profiling; 25@@profiling 261 27create table t1 ( 28a int, 29b int 30); 31insert into t1 values (1,1), (2,null), (3, 4); 32insert into t1 values (5,1), (6,null), (7, 4); 33insert into t1 values (1,1), (2,null), (3, 4); 34insert into t1 values (5,1), (6,null), (7, 4); 35select max(x) from (select sum(a) as x from t1 group by b) as teeone; 36max(x) 3720 38insert into t1 select * from t1; 39select count(*) from t1; 40count(*) 4124 42insert into t1 select * from t1; 43insert into t1 select * from t1; 44insert into t1 select * from t1; 45select count(*) from t1; 46count(*) 47192 48insert into t1 select * from t1; 49insert into t1 select * from t1; 50insert into t1 select * from t1; 51select count(*) from t1; 52count(*) 531536 54select sum(a) from t1; 55sum(a) 566144 57select sum(a) from t1 group by b; 58sum(a) 592048 601536 612560 62select sum(a) + sum(b) from t1 group by b; 63sum(a) + sum(b) 64NULL 652048 664608 67select max(x) from (select sum(a) as x from t1 group by b) as teeone; 68max(x) 692560 70select '012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890' as big_string; 71big_string 72012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890 73show profiles; 74Query_ID Duration Query 751 # set session profiling_history_size=30 762 # show session variables like 'profil%' 773 # select @@profiling 784 # create table t1 ( 79a int, 80b int 81) 825 # insert into t1 values (1,1), (2,null), (3, 4) 836 # insert into t1 values (5,1), (6,null), (7, 4) 847 # insert into t1 values (1,1), (2,null), (3, 4) 858 # insert into t1 values (5,1), (6,null), (7, 4) 869 # select max(x) from (select sum(a) as x from t1 group by b) as teeone 8710 # insert into t1 select * from t1 8811 # select count(*) from t1 8912 # insert into t1 select * from t1 9013 # insert into t1 select * from t1 9114 # insert into t1 select * from t1 9215 # select count(*) from t1 9316 # insert into t1 select * from t1 9417 # insert into t1 select * from t1 9518 # insert into t1 select * from t1 9619 # select count(*) from t1 9720 # select sum(a) from t1 9821 # select sum(a) from t1 group by b 9922 # select sum(a) + sum(b) from t1 group by b 10023 # select max(x) from (select sum(a) as x from t1 group by b) as teeone 10124 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 102show profile for query 15; 103show profile cpu for query 15; 104show profile cpu, block io for query 15; 105show profile cpu for query 9 limit 2 offset 2; 106show profile cpu for query 10 limit 0; 107show profile cpu for query 65534; 108show profile memory; 109show profile block io; 110show profile context switches; 111show profile page faults; 112show profile ipc; 113show profile swaps limit 1 offset 2; 114show profile source; 115show profile all for query 0 limit 0; 116show profile all for query 15; 117select * from information_schema.profiling; 118select query_id, state, duration from information_schema.profiling; 119select query_id, sum(duration) from information_schema.profiling group by query_id; 120select query_id, count(*) from information_schema.profiling group by query_id; 121select sum(duration) from information_schema.profiling; 122select query_id, count(*), sum(duration) from information_schema.profiling group by query_id; 123select 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; 124drop table if exists t1, t2, t3; 125Warnings: 126Note 1051 Unknown table 'test.t2,test.t3' 127create table t1 (id int ); 128create table t2 (id int not null); 129create table t3 (id int not null primary key); 130insert into t1 values (1), (2), (3); 131insert into t2 values (1), (2), (3); 132insert into t3 values (1), (2), (3); 133show profiles; 134Query_ID Duration Query 13510 # insert into t1 select * from t1 13611 # select count(*) from t1 13712 # insert into t1 select * from t1 13813 # insert into t1 select * from t1 13914 # insert into t1 select * from t1 14015 # select count(*) from t1 14116 # insert into t1 select * from t1 14217 # insert into t1 select * from t1 14318 # insert into t1 select * from t1 14419 # select count(*) from t1 14520 # select sum(a) from t1 14621 # select sum(a) from t1 group by b 14722 # select sum(a) + sum(b) from t1 group by b 14823 # select max(x) from (select sum(a) as x from t1 group by b) as teeone 14924 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 15025 # select * from information_schema.profiling 15126 # select query_id, state, duration from information_schema.profiling 15227 # select query_id, sum(duration) from information_schema.profiling group by query_id 15328 # select query_id, count(*) from information_schema.profiling group by query_id 15429 # select sum(duration) from information_schema.profiling 15530 # select query_id, count(*), sum(duration) from information_schema.profiling group by query_id 15631 # 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 15732 # drop table if exists t1, t2, t3 15833 # SHOW WARNINGS 15934 # create table t1 (id int ) 16035 # create table t2 (id int not null) 16136 # create table t3 (id int not null primary key) 16237 # insert into t1 values (1), (2), (3) 16338 # insert into t2 values (1), (2), (3) 16439 # insert into t3 values (1), (2), (3) 165select * from t1; 166id 1671 1682 1693 170show profiles; 171Query_ID Duration Query 17211 # select count(*) from t1 17312 # insert into t1 select * from t1 17413 # insert into t1 select * from t1 17514 # insert into t1 select * from t1 17615 # select count(*) from t1 17716 # insert into t1 select * from t1 17817 # insert into t1 select * from t1 17918 # insert into t1 select * from t1 18019 # select count(*) from t1 18120 # select sum(a) from t1 18221 # select sum(a) from t1 group by b 18322 # select sum(a) + sum(b) from t1 group by b 18423 # select max(x) from (select sum(a) as x from t1 group by b) as teeone 18524 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 18625 # select * from information_schema.profiling 18726 # select query_id, state, duration from information_schema.profiling 18827 # select query_id, sum(duration) from information_schema.profiling group by query_id 18928 # select query_id, count(*) from information_schema.profiling group by query_id 19029 # select sum(duration) from information_schema.profiling 19130 # select query_id, count(*), sum(duration) from information_schema.profiling group by query_id 19231 # 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 19332 # drop table if exists t1, t2, t3 19433 # SHOW WARNINGS 19534 # create table t1 (id int ) 19635 # create table t2 (id int not null) 19736 # create table t3 (id int not null primary key) 19837 # insert into t1 values (1), (2), (3) 19938 # insert into t2 values (1), (2), (3) 20039 # insert into t3 values (1), (2), (3) 20140 # select * from t1 202This ^^ should end in "select * from t1;" 203delete from t1; 204insert into t1 values (1), (2), (3); 205insert into t1 values (1), (2), (3); 206select * from t1; 207id 2081 2092 2103 2111 2122 2133 214show profiles; 215Query_ID Duration Query 21615 # select count(*) from t1 21716 # insert into t1 select * from t1 21817 # insert into t1 select * from t1 21918 # insert into t1 select * from t1 22019 # select count(*) from t1 22120 # select sum(a) from t1 22221 # select sum(a) from t1 group by b 22322 # select sum(a) + sum(b) from t1 group by b 22423 # select max(x) from (select sum(a) as x from t1 group by b) as teeone 22524 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 22625 # select * from information_schema.profiling 22726 # select query_id, state, duration from information_schema.profiling 22827 # select query_id, sum(duration) from information_schema.profiling group by query_id 22928 # select query_id, count(*) from information_schema.profiling group by query_id 23029 # select sum(duration) from information_schema.profiling 23130 # select query_id, count(*), sum(duration) from information_schema.profiling group by query_id 23231 # 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 23332 # drop table if exists t1, t2, t3 23433 # SHOW WARNINGS 23534 # create table t1 (id int ) 23635 # create table t2 (id int not null) 23736 # create table t3 (id int not null primary key) 23837 # insert into t1 values (1), (2), (3) 23938 # insert into t2 values (1), (2), (3) 24039 # insert into t3 values (1), (2), (3) 24140 # select * from t1 24241 # delete from t1 24342 # insert into t1 values (1), (2), (3) 24443 # insert into t1 values (1), (2), (3) 24544 # select * from t1 246set session profiling = OFF; 247select sum(id) from t1; 248sum(id) 24912 250show profiles; 251Query_ID Duration Query 25215 # select count(*) from t1 25316 # insert into t1 select * from t1 25417 # insert into t1 select * from t1 25518 # insert into t1 select * from t1 25619 # select count(*) from t1 25720 # select sum(a) from t1 25821 # select sum(a) from t1 group by b 25922 # select sum(a) + sum(b) from t1 group by b 26023 # select max(x) from (select sum(a) as x from t1 group by b) as teeone 26124 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 26225 # select * from information_schema.profiling 26326 # select query_id, state, duration from information_schema.profiling 26427 # select query_id, sum(duration) from information_schema.profiling group by query_id 26528 # select query_id, count(*) from information_schema.profiling group by query_id 26629 # select sum(duration) from information_schema.profiling 26730 # select query_id, count(*), sum(duration) from information_schema.profiling group by query_id 26831 # 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 26932 # drop table if exists t1, t2, t3 27033 # SHOW WARNINGS 27134 # create table t1 (id int ) 27235 # create table t2 (id int not null) 27336 # create table t3 (id int not null primary key) 27437 # insert into t1 values (1), (2), (3) 27538 # insert into t2 values (1), (2), (3) 27639 # insert into t3 values (1), (2), (3) 27740 # select * from t1 27841 # delete from t1 27942 # insert into t1 values (1), (2), (3) 28043 # insert into t1 values (1), (2), (3) 28144 # select * from t1 282set session profiling = ON; 283select @@profiling; 284@@profiling 2851 286create function f1() returns varchar(50) return 'hello'; 287select @@profiling; 288@@profiling 2891 290select * from t1 where id <> f1(); 291id 2921 2932 2943 2951 2962 2973 298Warnings: 299Warning 1292 Truncated incorrect DOUBLE value: 'hello' 300Warning 1292 Truncated incorrect DOUBLE value: 'hello' 301Warning 1292 Truncated incorrect DOUBLE value: 'hello' 302Warning 1292 Truncated incorrect DOUBLE value: 'hello' 303Warning 1292 Truncated incorrect DOUBLE value: 'hello' 304Warning 1292 Truncated incorrect DOUBLE value: 'hello' 305select @@profiling; 306@@profiling 3071 308set session profiling = OFF; 309drop table if exists profile_log; 310Warnings: 311Note 1051 Unknown table 'test.profile_log' 312create table profile_log (how_many int); 313drop procedure if exists p1; 314drop procedure if exists p2; 315drop procedure if exists p3; 316create procedure p1 () 317modifies sql data 318begin 319set profiling = ON; 320select 'This p1 should show up in profiling'; 321insert into profile_log select count(*) from information_schema.profiling; 322end// 323create procedure p2() 324deterministic 325begin 326set profiling = ON; 327call p1(); 328select 'This p2 should show up in profiling'; 329end// 330create procedure p3 () 331reads sql data 332begin 333set profiling = ON; 334select 'This p3 should show up in profiling'; 335show profile; 336end// 337first call to p1 338call p1; 339select * from profile_log; 340second call to p1 341call p1; 342select * from profile_log; 343third call to p1 344call p1; 345select * from profile_log; 346set session profiling = OFF; 347call p2; 348set session profiling = OFF; 349call p3; 350show profiles; 351drop procedure if exists p1; 352drop procedure if exists p2; 353drop procedure if exists p3; 354drop table if exists profile_log; 355set session profiling = ON; 356drop table if exists t2; 357create table t2 (id int not null); 358create trigger t2_bi before insert on t2 for each row set @x=0; 359select @@profiling; 360@@profiling 3611 362insert into t2 values (1), (2), (3); 363select @@profiling; 364@@profiling 3651 366set session profiling = ON; 367drop table if exists t1, t2; 368create table t1 (id int not null primary key); 369create table t2 (id int not null primary key, id1 int not null); 370select @@profiling; 371@@profiling 3721 373alter table t2 add foreign key (id1) references t1 (id) on delete cascade; 374select @@profiling; 375@@profiling 3761 377lock table t1 write; 378select @@profiling; 379@@profiling 3801 381unlock table; 382select @@profiling; 383@@profiling 3841 385set autocommit=0; 386select @@profiling, @@autocommit; 387@@profiling @@autocommit 3881 0 389begin; 390select @@profiling; 391@@profiling 3921 393insert into t1 values (1); 394insert into t2 values (1,1); 395testing referential integrity cascade 396delete from t1 where id = 1; 397select @@profiling; 398@@profiling 3991 400testing rollback 401rollback; 402select @@profiling; 403@@profiling 4041 405testing commit 406begin; 407select @@profiling; 408@@profiling 4091 410commit; 411select @@profiling; 412@@profiling 4131 414drop table if exists t1, t2, t3; 415drop view if exists v1; 416Warnings: 417Note 4092 Unknown VIEW: 'test.v1' 418drop function if exists f1; 419set session profiling = OFF; 420set global profiling_history_size= @start_value; 421End of 5.0 tests 422