From: Mayank Prasad Date: January 11 2013 12:27pm Subject: bzr push into mysql-trunk-wl5766 branch (mayank.prasad:5165 to 5166) WL#5766 List-Archive: http://lists.mysql.com/commits/145599 Message-Id: <20130111122737.18219.82401.5166@mayank-pc> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 5166 Mayank Prasad 2013-01-11 WL#5766 : PERFORMANCE SCHEMA, sub statements instrumentation Details : - Added code to have nesting event type/id for substatements. - Added a test case to test Function, Stored Procedure and trigger's instrumentation. added: mysql-test/suite/perfschema/r/sub_statement.result mysql-test/suite/perfschema/t/sub_statement.test modified: storage/perfschema/pfs.cc 5165 Mayank Prasad 2013-01-09 WL#5766 : PERFORMANCE SCHEMA, sub statements instrumentation. Details: - First patch for WL. modified: include/mysql/psi/mysql_statement.h include/mysql/psi/psi.h include/mysql/psi/psi_abi_v1.h.pp mysql-test/suite/perfschema/r/ortho_iter.result mysql-test/suite/perfschema/r/privilege_table_io.result mysql-test/suite/perfschema/r/sizing_off.result mysql-test/suite/perfschema/r/start_server_disable_idle.result mysql-test/suite/perfschema/r/start_server_disable_stages.result mysql-test/suite/perfschema/r/start_server_disable_statements.result mysql-test/suite/perfschema/r/start_server_disable_waits.result mysql-test/suite/perfschema/r/start_server_innodb.result mysql-test/suite/perfschema/r/start_server_no_account.result mysql-test/suite/perfschema/r/start_server_no_cond_class.result mysql-test/suite/perfschema/r/start_server_no_cond_inst.result mysql-test/suite/perfschema/r/start_server_no_file_class.result mysql-test/suite/perfschema/r/start_server_no_file_inst.result mysql-test/suite/perfschema/r/start_server_no_host.result mysql-test/suite/perfschema/r/start_server_no_mutex_class.result mysql-test/suite/perfschema/r/start_server_no_mutex_inst.result mysql-test/suite/perfschema/r/start_server_no_rwlock_class.result mysql-test/suite/perfschema/r/start_server_no_rwlock_inst.result mysql-test/suite/perfschema/r/start_server_no_setup_actors.result mysql-test/suite/perfschema/r/start_server_no_setup_objects.result mysql-test/suite/perfschema/r/start_server_no_socket_class.result mysql-test/suite/perfschema/r/start_server_no_socket_inst.result mysql-test/suite/perfschema/r/start_server_no_stage_class.result mysql-test/suite/perfschema/r/start_server_no_stages_history.result mysql-test/suite/perfschema/r/start_server_no_stages_history_long.result mysql-test/suite/perfschema/r/start_server_no_statement_class.result mysql-test/suite/perfschema/r/start_server_no_statements_history.result mysql-test/suite/perfschema/r/start_server_no_statements_history_long.result mysql-test/suite/perfschema/r/start_server_no_table_hdl.result mysql-test/suite/perfschema/r/start_server_no_table_inst.result mysql-test/suite/perfschema/r/start_server_no_thread_class.result mysql-test/suite/perfschema/r/start_server_no_thread_inst.result mysql-test/suite/perfschema/r/start_server_no_user.result mysql-test/suite/perfschema/r/start_server_no_waits_history.result mysql-test/suite/perfschema/r/start_server_no_waits_history_long.result mysql-test/suite/perfschema/r/start_server_nothing.result mysql-test/suite/perfschema/r/start_server_off.result mysql-test/suite/perfschema/r/start_server_on.result mysql-test/suite/perfschema/r/table_aggregate_global_2u_2t.result mysql-test/suite/perfschema/r/table_aggregate_global_2u_3t.result mysql-test/suite/perfschema/r/table_aggregate_global_4u_2t.result mysql-test/suite/perfschema/r/table_aggregate_global_4u_3t.result mysql-test/suite/perfschema/r/table_aggregate_hist_2u_2t.result mysql-test/suite/perfschema/r/table_aggregate_hist_2u_3t.result mysql-test/suite/perfschema/r/table_aggregate_hist_4u_2t.result mysql-test/suite/perfschema/r/table_aggregate_hist_4u_3t.result mysql-test/suite/perfschema/r/table_aggregate_off.result mysql-test/suite/perfschema/r/table_aggregate_thread_2u_2t.result mysql-test/suite/perfschema/r/table_aggregate_thread_2u_3t.result mysql-test/suite/perfschema/r/table_aggregate_thread_4u_2t.result mysql-test/suite/perfschema/r/table_aggregate_thread_4u_3t.result mysql-test/suite/perfschema/r/table_io_aggregate_global_2u_2t.result mysql-test/suite/perfschema/r/table_io_aggregate_global_2u_3t.result mysql-test/suite/perfschema/r/table_io_aggregate_global_4u_2t.result mysql-test/suite/perfschema/r/table_io_aggregate_global_4u_3t.result mysql-test/suite/perfschema/r/table_io_aggregate_hist_2u_2t.result mysql-test/suite/perfschema/r/table_io_aggregate_hist_2u_3t.result mysql-test/suite/perfschema/r/table_io_aggregate_hist_4u_2t.result mysql-test/suite/perfschema/r/table_io_aggregate_hist_4u_3t.result mysql-test/suite/perfschema/r/table_io_aggregate_thread_2u_2t.result mysql-test/suite/perfschema/r/table_io_aggregate_thread_2u_3t.result mysql-test/suite/perfschema/r/table_io_aggregate_thread_4u_2t.result mysql-test/suite/perfschema/r/table_io_aggregate_thread_4u_3t.result mysql-test/suite/perfschema/r/table_lock_aggregate_global_2u_2t.result mysql-test/suite/perfschema/r/table_lock_aggregate_global_2u_3t.result mysql-test/suite/perfschema/r/table_lock_aggregate_global_4u_2t.result mysql-test/suite/perfschema/r/table_lock_aggregate_global_4u_3t.result mysql-test/suite/perfschema/r/table_lock_aggregate_hist_2u_2t.result mysql-test/suite/perfschema/r/table_lock_aggregate_hist_2u_3t.result mysql-test/suite/perfschema/r/table_lock_aggregate_hist_4u_2t.result mysql-test/suite/perfschema/r/table_lock_aggregate_hist_4u_3t.result mysql-test/suite/perfschema/r/table_lock_aggregate_thread_2u_2t.result mysql-test/suite/perfschema/r/table_lock_aggregate_thread_2u_3t.result mysql-test/suite/perfschema/r/table_lock_aggregate_thread_4u_2t.result mysql-test/suite/perfschema/r/table_lock_aggregate_thread_4u_3t.result mysys/psi_noop.c sql/mysqld.cc sql/sp_head.cc sql/sp_head.h sql/sp_instr.cc sql/sp_instr.h sql/sys_vars.cc storage/perfschema/pfs.cc storage/perfschema/pfs_column_types.h storage/perfschema/pfs_events_statements.h storage/perfschema/pfs_instr.cc storage/perfschema/pfs_server.h storage/perfschema/table_events_statements.cc storage/perfschema/table_events_statements.h storage/perfschema/table_helper.cc storage/perfschema/unittest/pfs-t.cc === added file 'mysql-test/suite/perfschema/r/sub_statement.result' --- a/mysql-test/suite/perfschema/r/sub_statement.result 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/perfschema/r/sub_statement.result revid:mayank.prasad@stripped @@ -0,0 +1,53 @@ +#################################### +# SETUP +#################################### +CREATE DATABASE sub_statements; +USE sub_statements; +CREATE TABLE t1(c1 INT); +CREATE TABLE t2(c1 INT); +CREATE TRIGGER simple_trigger BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO +t2 VALUES('12'); +CREATE PROCEDURE simple_procedure (OUT param1 INT) +BEGIN +SELECT COUNT(*) INTO PARAM1 FROM t1; +INSERT INTO t1 values('2'); +END // +CREATE FUNCTION simple_function (s CHAR(20)) RETURNS CHAR(50) +BEGIN +INSERT INTO t1 values('3'); +RETURN s; +END // +TRUNCATE TABLE performance_schema.events_statements_history; +#################################### +# EXECUTING QUERIES +#################################### +INSERT INTO t1 VALUES('1'); +CALL simple_procedure(@a); +SELECT simple_function('Hello'); +simple_function('Hello') +Hello +#################################### +# QUERYING PS STATEMENT HISTORY +#################################### +SELECT THREAD_ID, EVENT_ID, EVENT_NAME, SQL_TEXT, CURRENT_SCHEMA, OBJECT_TYPE, OBJECT_SCHEMA, +OBJECT_NAME, NESTING_EVENT_ID, NESTING_EVENT_TYPE from performance_schema.events_statements_history where CURRENT_SCHEMA='sub_statements'; +THREAD_ID EVENT_ID EVENT_NAME SQL_TEXT CURRENT_SCHEMA OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME NESTING_EVENT_ID NESTING_EVENT_TYPE +17 1108 statement/sql/select SELECT simple_function('Hello') sub_statements NULL NULL NULL NULL NULL +17 920 statement/sp/sp_instr_stmt INSERT INTO +t2 VALUES('12') sub_statements TRIGGER sub_statements simple_trigger 820 STATEMENT +17 820 statement/sql/insert INSERT INTO t1 VALUES('1') sub_statements NULL NULL NULL NULL NULL +17 1002 statement/sp/sp_instr_stmt SELECT COUNT(*) INTO PARAM1 FROM t1 sub_statements PROCEDURE sub_statements simple_procedure 971 STATEMENT +17 1059 statement/sp/sp_instr_stmt INSERT INTO +t2 VALUES('12') sub_statements TRIGGER sub_statements simple_trigger 1030 STATEMENT +17 1030 statement/sp/sp_instr_stmt INSERT INTO t1 values('2') sub_statements PROCEDURE sub_statements simple_procedure 971 STATEMENT +17 971 statement/sql/call_procedure CALL simple_procedure(@a) sub_statements NULL NULL NULL NULL NULL +17 1176 statement/sp/sp_instr_stmt INSERT INTO +t2 VALUES('12') sub_statements TRIGGER sub_statements simple_trigger 1162 STATEMENT +17 1162 statement/sp/sp_instr_stmt INSERT INTO t1 values('3') sub_statements FUNCTION sub_statements simple_function 1108 STATEMENT +17 1203 statement/sp/sp_instr_freturn NULL sub_statements FUNCTION sub_statements simple_function 1108 STATEMENT +#################################### +# CLEANUP +#################################### +DROP TABLE t1; +DROP TABLE t2; +DROP DATABASE IF EXISTS sub_statements; === added file 'mysql-test/suite/perfschema/t/sub_statement.test' --- a/mysql-test/suite/perfschema/t/sub_statement.test 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/perfschema/t/sub_statement.test revid:mayank.prasad@stripped @@ -0,0 +1,60 @@ +# ----------------------------------------------------------------------- +# Tests for the performance schema statement Digests. +# ----------------------------------------------------------------------- + +--source include/not_embedded.inc +--source include/have_perfschema.inc + +--echo #################################### +--echo # SETUP +--echo #################################### +--disable_warnings +CREATE DATABASE sub_statements; +USE sub_statements; +CREATE TABLE t1(c1 INT); +CREATE TABLE t2(c1 INT); +--enable_warnings + +# Creating Trigger +CREATE TRIGGER simple_trigger BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO + t2 VALUES('12'); + +# Creaing Stored Procedure +DELIMITER //; +CREATE PROCEDURE simple_procedure (OUT param1 INT) +BEGIN + SELECT COUNT(*) INTO PARAM1 FROM t1; + INSERT INTO t1 values('2'); +END // + +# Creating Function +CREATE FUNCTION simple_function (s CHAR(20)) RETURNS CHAR(50) +BEGIN + INSERT INTO t1 values('3'); + RETURN s; +END // +DELIMITER ;// + +TRUNCATE TABLE performance_schema.events_statements_history; + +--echo #################################### +--echo # EXECUTING QUERIES +--echo #################################### +INSERT INTO t1 VALUES('1'); +CALL simple_procedure(@a); +SELECT simple_function('Hello'); + +--echo #################################### +--echo # QUERYING PS STATEMENT HISTORY +--echo #################################### +SELECT THREAD_ID, EVENT_ID, EVENT_NAME, SQL_TEXT, CURRENT_SCHEMA, OBJECT_TYPE, OBJECT_SCHEMA, + OBJECT_NAME, NESTING_EVENT_ID, NESTING_EVENT_TYPE from performance_schema.events_statements_history where CURRENT_SCHEMA='sub_statements'; + +--echo #################################### +--echo # CLEANUP +--echo #################################### +--disable_warnings +DROP TABLE t1; +DROP TABLE t2; +DROP DATABASE IF EXISTS sub_statements; +--enable_warnings === modified file 'storage/perfschema/pfs.cc' --- a/storage/perfschema/pfs.cc revid:mayank.prasad@stripped +++ b/storage/perfschema/pfs.cc revid:mayank.prasad@stripped @@ -4427,6 +4427,10 @@ get_thread_statement_locker_v1(PSI_state child_wait->m_nesting_event_id= event_id; child_wait->m_nesting_event_type= EVENT_TYPE_STATEMENT; + PFS_events_statements *parent= & pfs_thread->m_statement_stack[pfs_thread->m_events_statements_count-1]; + pfs->m_nesting_event_id= parent->m_event_id; + pfs->m_nesting_event_type= EVENT_TYPE_STATEMENT; + state->m_statement= pfs; flags|= STATE_FLAG_EVENT; No bundle (reason: useless for push emails).