diff options
Diffstat (limited to 'mysql-test/suite/perfschema/include')
67 files changed, 7419 insertions, 0 deletions
diff --git a/mysql-test/suite/perfschema/include/binlog_common.inc b/mysql-test/suite/perfschema/include/binlog_common.inc new file mode 100644 index 00000000..1c8651a0 --- /dev/null +++ b/mysql-test/suite/perfschema/include/binlog_common.inc @@ -0,0 +1,41 @@ +# Tests for PERFORMANCE_SCHEMA + +RESET MASTER; + +select count(*) > 0 from performance_schema.setup_instruments; + +# Note: +# Do not include records that could depend on +# compiling options (storage engines, SSL), +# to ensure the expected output in the binlog is predictable. +update performance_schema.setup_instruments set enabled='NO' + where name like "wait/synch/rwlock/sql/%" + and name not in ("wait/synch/rwlock/sql/CRYPTO_dynlock_value::lock"); + +select count(*) > 0 from performance_schema.events_waits_current; + +--disable_warnings +drop table if exists test.t1; +drop table if exists test.t2; +--enable_warnings + +create table test.t1 (thread_id integer); +create table test.t2 (name varchar(128)); + +insert into test.t1 + select thread_id from performance_schema.events_waits_current; + +insert into test.t2 + select name from performance_schema.setup_instruments + where name like "wait/synch/rwlock/sql/%" + and name not in ("wait/synch/rwlock/sql/CRYPTO_dynlock_value::lock"); + +drop table test.t1; +drop table test.t2; + +update performance_schema.setup_instruments set enabled='YES' + where name like "wait/synch/rwlock/sql/%" + and name not in ("wait/synch/rwlock/sql/CRYPTO_dynlock_value::lock"); + +--source include/show_binlog_events.inc + diff --git a/mysql-test/suite/perfschema/include/binlog_edge_common.inc b/mysql-test/suite/perfschema/include/binlog_edge_common.inc new file mode 100644 index 00000000..6ecd50af --- /dev/null +++ b/mysql-test/suite/perfschema/include/binlog_edge_common.inc @@ -0,0 +1,188 @@ +# Test replication, when using special non-replicated tables. +# +# This test involve special statements that use non-replicated tables. +# Changes affecting non replicated tables are never written to the binlog. +# Executing these statements may or may not work, as the statements involved +# are edge cases. +# In MIXED or ROW binlog format, execution should succeed, +# and only partial data (the rows affecting replicated tables only) +# should be written to the binlog. +# In STATEMENT binlog format, execution should +# raise a warning (ER_BINLOG_UNSAFE_STATEMENT) if a non replicated table is +# only read from, or fail with an error (ER_BINLOG_STMT_MODE_AND_NO_REPL_TABLES) +# if a non replicated table is written to. +# +# SHOW ERRORS will print in the +# test .result file the exact outcome. + +RESET MASTER; + +--disable_warnings +drop database if exists my_replicated_db; +--enable_warnings + +call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); + +create database my_replicated_db; + +create table my_replicated_db.my_tx_table(a bigint) engine = innodb; +create table my_replicated_db.my_non_tx_table(a bigint) engine = myisam; +create table my_replicated_db.my_bh_table(a bigint) engine = blackhole; + +use test; +drop table if exists marker_start; + +use my_replicated_db; + +insert into my_tx_table(a) + values (1000), (2000), (3000); + +insert into my_non_tx_table(a) + values (1000), (2000), (3000); + +insert into my_bh_table(a) + values (1000), (2000), (3000); + +use test; +drop table if exists marker_insert_select; + +use my_replicated_db; + +# Note: +# The queries used here do not make any sense (no semantic). +# What this test is interrested in, is check the behavior +# when replicating queries that mix both: +# - non replicated tables +# - replicated tables + +insert into my_tx_table(a) + select thread_id from performance_schema.threads; + +insert into my_non_tx_table(a) + select thread_id from performance_schema.threads; + +insert into my_bh_table(a) + select thread_id from performance_schema.threads; + +# For the information_schema, +# no error is enforced yet. +# Documenting the current behavior + +insert into my_tx_table(a) + select id from information_schema.processlist; + +insert into my_non_tx_table(a) + select id from information_schema.processlist; + +insert into my_bh_table(a) + select id from information_schema.processlist; + +insert into my_tx_table(a) + select thread_id from mysql.general_log; + +insert into my_non_tx_table(a) + select thread_id from mysql.general_log; + +insert into my_bh_table(a) + select thread_id from mysql.general_log; + +insert into my_tx_table(a) + select thread_id from mysql.slow_log; + +insert into my_non_tx_table(a) + select thread_id from mysql.slow_log; + +insert into my_bh_table(a) + select thread_id from mysql.slow_log; + +insert into my_tx_table(a) + select Relay_log_pos from mysql.slave_relay_log_info; + +insert into my_non_tx_table(a) + select Relay_log_pos from mysql.slave_relay_log_info; + +insert into my_bh_table(a) + select Relay_log_pos from mysql.slave_relay_log_info; + +insert into my_tx_table(a) + select Master_log_pos from mysql.slave_master_info; + +insert into my_non_tx_table(a) + select Master_log_pos from mysql.slave_master_info; + +insert into my_bh_table(a) + select Master_log_pos from mysql.slave_master_info; + +insert into my_tx_table(a) + select Relay_log_pos from mysql.slave_worker_info; + +insert into my_non_tx_table(a) + select Relay_log_pos from mysql.slave_worker_info; + +insert into my_bh_table(a) + select Relay_log_pos from mysql.slave_worker_info; + +use test; +drop table if exists marker_multi_update; + +use my_replicated_db; + +--error 0, ER_BINLOG_STMT_MODE_AND_NO_REPL_TABLES +update my_tx_table, performance_schema.setup_instruments + set my_tx_table.a = my_tx_table.a + 1, + performance_schema.setup_instruments.timed= 'NO'; + +--error 0, ER_BINLOG_STMT_MODE_AND_NO_REPL_TABLES +update my_non_tx_table, performance_schema.setup_instruments + set my_non_tx_table.a = my_non_tx_table.a + 1, + performance_schema.setup_instruments.timed= 'NO'; + +--error 0, ER_BINLOG_STMT_MODE_AND_NO_REPL_TABLES +update my_bh_table, performance_schema.setup_instruments + set my_bh_table.a = my_bh_table.a + 1, + performance_schema.setup_instruments.timed= 'NO'; + +use test; +drop table if exists marker_multi_delete; + +use my_replicated_db; + +insert into performance_schema.setup_actors + values ('FOO', 'FOO', 'FOO', 'YES', 'YES'); + +--error 0, ER_BINLOG_STMT_MODE_AND_NO_REPL_TABLES +delete my_tx_table.*, performance_schema.setup_actors.* + from my_tx_table, performance_schema.setup_actors + where my_tx_table.a != 1000 + or performance_schema.setup_actors.role='FOO'; + +insert into performance_schema.setup_actors + values ('BAR', 'BAR', 'BAR', 'YES', 'YES'); + +--error 0, ER_BINLOG_STMT_MODE_AND_NO_REPL_TABLES +delete my_non_tx_table.*, performance_schema.setup_actors.* + from my_non_tx_table, performance_schema.setup_actors + where my_non_tx_table.a != 1000 + or performance_schema.setup_actors.role='BAR'; + +insert into performance_schema.setup_actors + values ('BAZ', 'BAZ', 'BAZ', 'YES', 'YES'); + +--error 0, ER_BINLOG_STMT_MODE_AND_NO_REPL_TABLES +delete my_bh_table.*, performance_schema.setup_actors.* + from my_bh_table, performance_schema.setup_actors + where my_bh_table.a != 1000 + or performance_schema.setup_actors.role='BAZ'; + +use test; +drop table if exists marker_end; + +drop database my_replicated_db; + +--source include/show_binlog_events.inc + +# Restore performance_schema.setup_actors, damaged by this script + +truncate table performance_schema.setup_actors; +insert into performance_schema.setup_actors values ('%', '%', '%', 'YES', 'YES'); + diff --git a/mysql-test/suite/perfschema/include/binlog_ok_common.inc b/mysql-test/suite/perfschema/include/binlog_ok_common.inc new file mode 100644 index 00000000..d537c6cf --- /dev/null +++ b/mysql-test/suite/perfschema/include/binlog_ok_common.inc @@ -0,0 +1,146 @@ +# Test replication, when using special non-replicated tables. +# +# This test involve special statements that use non-replicated tables. +# Changes affecting non replicated tables are never written to the binlog. +# Executing these statements should work nicely with replication in all cases: +# - STATEMENT binlog format +# - MIXED binlog format +# - ROW binlog format. + +RESET MASTER; + +--disable_warnings +drop database if exists my_local_db; +--enable_warnings + +create database my_local_db; + +create table my_local_db.my_tx_table(a bigint) engine = innodb; +create table my_local_db.my_non_tx_table(a bigint) engine = myisam; + +use test; +drop table if exists marker_start; + +# --binlog-ignore-db only works with the current database. +use my_local_db; + +insert into my_tx_table(a) + values (1000), (2000), (3000); + +insert into my_non_tx_table(a) + values (1000), (2000), (3000); + +use test; +drop table if exists marker_truncate; + +use performance_schema; +truncate table events_waits_history; +truncate table events_waits_history_long; + +use test; +truncate table performance_schema.events_statements_history_long; +truncate table performance_schema.host_cache; + +use test; +drop table if exists marker_update; + +use performance_schema; +update setup_instruments set enabled='NO'; +update setup_instruments set timed='NO'; +use test; +update performance_schema.setup_instruments set enabled='YES', timed='YES'; +update performance_schema.threads set instrumented='YES'; + +use test; +drop table if exists marker_insert; + +insert into performance_schema.setup_actors(`user`, `host`, `role`) + values ('XXX', 'XXX', 'XXX'), + ('YYY', 'YYY', 'YYY'), + ('ZZZ', 'ZZZ', 'ZZZ'); + +select * from performance_schema.setup_actors + where user in ('XXX', 'YYY', 'ZZZ') order by user; + +insert into performance_schema.setup_objects + (object_type, object_schema, object_name, enabled, timed) + values ('TABLE', 'DB1', 'AAA', 'YES', 'YES'), + ('TABLE', 'DB1', 'BBB', 'NO', 'NO'), + ('TABLE', 'DB2', 'CCC', 'YES', 'NO'), + ('TABLE', 'DB2', 'DDD', 'NO', 'YES'); + +select * from performance_schema.setup_objects + where object_schema like 'DB%' order by object_name; + +use test; +drop table if exists marker_insert_select; + +# Note: +# The queries used here do not make any sense (no semantic). +# What this test is interrested in, is check the behavior +# when replicating queries that mix both: +# - non replicated tables +# - replicated tables + +use my_local_db; +insert into my_tx_table(a) + select thread_id from performance_schema.threads; + +insert into my_non_tx_table(a) + select thread_id from performance_schema.threads; + +insert into my_tx_table(a) + select id from information_schema.processlist; + +insert into my_non_tx_table(a) + select id from information_schema.processlist; + +insert into my_tx_table(a) + select thread_id from mysql.general_log; + +insert into my_non_tx_table(a) + select thread_id from mysql.general_log; + +insert into my_tx_table(a) + select thread_id from mysql.slow_log; + +insert into my_non_tx_table(a) + select thread_id from mysql.slow_log; + +insert into my_tx_table(a) + select Relay_log_pos from mysql.slave_relay_log_info; + +insert into my_non_tx_table(a) + select Relay_log_pos from mysql.slave_relay_log_info; + +insert into my_tx_table(a) + select Master_log_pos from mysql.slave_master_info; + +insert into my_non_tx_table(a) + select Master_log_pos from mysql.slave_master_info; + +insert into my_tx_table(a) + select Relay_log_pos from mysql.slave_worker_info; + +insert into my_non_tx_table(a) + select Relay_log_pos from mysql.slave_worker_info; + +use test; +drop table if exists marker_delete; + +delete from performance_schema.setup_actors + where user in ('XXX', 'YYY', 'ZZZ'); + +delete from performance_schema.setup_objects + where object_schema like 'DB%'; + +use test; +drop table if exists marker_end; + +drop database my_local_db; + +# The content of the binlog dumped in the result file +# should not contain any references to non-replicated tables. + +--source include/show_binlog_events.inc + diff --git a/mysql-test/suite/perfschema/include/cleanup_helper.inc b/mysql-test/suite/perfschema/include/cleanup_helper.inc new file mode 100644 index 00000000..24b2cbec --- /dev/null +++ b/mysql-test/suite/perfschema/include/cleanup_helper.inc @@ -0,0 +1,17 @@ + +# Tests for PERFORMANCE_SCHEMA + +update performance_schema.setup_instruments set enabled='YES'; + +connection con1; +disconnect con1; +--source include/wait_until_disconnected.inc +connection con2; +disconnect con2; +--source include/wait_until_disconnected.inc +connection con3; +disconnect con3; +--source include/wait_until_disconnected.inc + +connection default; + diff --git a/mysql-test/suite/perfschema/include/connection_cleanup.inc b/mysql-test/suite/perfschema/include/connection_cleanup.inc new file mode 100644 index 00000000..761540b4 --- /dev/null +++ b/mysql-test/suite/perfschema/include/connection_cleanup.inc @@ -0,0 +1,27 @@ +# Tests for the performance schema + +# ===================================== +# HELPER include/connection_cleanup.inc +# ===================================== + +--disable_query_log +revoke all privileges, grant option from user1@localhost; +revoke all privileges, grant option from user2@localhost; +revoke all privileges, grant option from user3@localhost; +revoke all privileges, grant option from user4@localhost; +revoke all privileges, grant option from user5@localhost; +drop user user1@localhost; +drop user user2@localhost; +drop user user3@localhost; +drop user user4@localhost; +drop user user5@localhost; +flush privileges; + +drop procedure dump_all; + +truncate table performance_schema.accounts; +truncate table performance_schema.users; +truncate table performance_schema.hosts; + +--enable_query_log + diff --git a/mysql-test/suite/perfschema/include/connection_load.inc b/mysql-test/suite/perfschema/include/connection_load.inc new file mode 100644 index 00000000..02c8e2ad --- /dev/null +++ b/mysql-test/suite/perfschema/include/connection_load.inc @@ -0,0 +1,245 @@ +# Tests for the performance schema + +# ======================================= +# HELPER include/connection_load.inc +# ======================================= + +call dump_all(); + +connect (con1a, localhost, user1, , ); +select "user1 in con1a" as status; +call dump_all(); + +connect (con1b, localhost, user1, , ); +select "user1 in con1b" as status; +call dump_all(); + +connect (con1c, localhost, user1, , ); +select "user1 in con1c" as status; +call dump_all(); + +connect (con2a, localhost, user2, , ); +select "user2 in con2a" as status; +call dump_all(); + +connect (con2b, localhost, user2, , ); +select "user2 in con2b" as status; +call dump_all(); + +connect (con2c, localhost, user2, , ); +select "user2 in con2c" as status; +call dump_all(); + +connect (con3a, localhost, user3, , ); +select "user3 in con3a" as status; +call dump_all(); + +connect (con3b, localhost, user3, , ); +select "user3 in con3b" as status; +call dump_all(); + +connect (con3c, localhost, user3, , ); +select "user3 in con3c" as status; +call dump_all(); + +--connection default + +--disconnect con1a + +# Wait for the disconnect to complete +let $wait_condition= + select count(*) = 8 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER like 'user%'; +--source include/wait_condition.inc + +select "con1a disconnected" as status; +call dump_all(); + +--disconnect con2a + +# Wait for the disconnect to complete +let $wait_condition= + select count(*) = 7 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER like 'user%'; +--source include/wait_condition.inc + +select "con2a disconnected" as status; +call dump_all(); + +--disconnect con3a + +# Wait for the disconnect to complete +let $wait_condition= + select count(*) = 6 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER like 'user%'; +--source include/wait_condition.inc + +select "con3a disconnected" as status; +call dump_all(); + +truncate table performance_schema.accounts; +call dump_all(); +truncate table performance_schema.users; +call dump_all(); +truncate table performance_schema.hosts; +call dump_all(); + +connect (con4a, localhost, user4, , ); +select "user4 in con4a" as status; +call dump_all(); + +connect (con4b, localhost, user4, , ); +select "user4 in con4b" as status; +call dump_all(); + +connect (con4c, localhost, user4, , ); +select "user4 in con4c" as status; +call dump_all(); + +--connection default + +--disconnect con1b + +# Wait for the disconnect to complete +let $wait_condition= + select count(*) = 8 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER like 'user%'; +--source include/wait_condition.inc + +select "con1b disconnected" as status; +call dump_all(); + +--disconnect con2b + +# Wait for the disconnect to complete +let $wait_condition= + select count(*) = 7 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER like 'user%'; +--source include/wait_condition.inc + +select "con2b disconnected" as status; +call dump_all(); + +--disconnect con3b + +# Wait for the disconnect to complete +let $wait_condition= + select count(*) = 6 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER like 'user%'; +--source include/wait_condition.inc + +select "con3b disconnected" as status; +call dump_all(); + +--disconnect con1c + +# Wait for the disconnect to complete +let $wait_condition= + select count(*) = 5 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER like 'user%'; +--source include/wait_condition.inc + +select "con1c disconnected" as status; +call dump_all(); + +--disconnect con2c + +# Wait for the disconnect to complete +let $wait_condition= + select count(*) = 4 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER like 'user%'; +--source include/wait_condition.inc + +select "con2c disconnected" as status; +call dump_all(); + +--disconnect con3c + +# Wait for the disconnect to complete +let $wait_condition= + select count(*) = 3 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER like 'user%'; +--source include/wait_condition.inc + +select "con3c disconnected" as status; +call dump_all(); + +truncate table performance_schema.accounts; +call dump_all(); +truncate table performance_schema.users; +call dump_all(); +truncate table performance_schema.hosts; +call dump_all(); + +connect (con5a, localhost, user5, , ); +select "user5 in con5a" as status; +call dump_all(); + +connect (con5b, localhost, user5, , ); +select "user5 in con5b" as status; +call dump_all(); + +connect (con5c, localhost, user5, , ); +select "user5 in con5c" as status; +call dump_all(); + +--connection default + +--disconnect con4a + +# Wait for the disconnect to complete +let $wait_condition= + select count(*) = 5 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER like 'user%'; +--source include/wait_condition.inc + +--disconnect con4b + +# Wait for the disconnect to complete +let $wait_condition= + select count(*) = 4 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER like 'user%'; +--source include/wait_condition.inc + +--disconnect con4c + +# Wait for the disconnect to complete +let $wait_condition= + select count(*) = 3 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER like 'user%'; +--source include/wait_condition.inc + +--disconnect con5a + +# Wait for the disconnect to complete +let $wait_condition= + select count(*) = 2 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER like 'user%'; +--source include/wait_condition.inc + +--disconnect con5b + +# Wait for the disconnect to complete +let $wait_condition= + select count(*) = 1 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER like 'user%'; +--source include/wait_condition.inc + +--disconnect con5c + +# Wait for the disconnect to complete +let $wait_condition= + select count(*) = 0 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER like 'user%'; +--source include/wait_condition.inc + +select "con 5a, 5b, 5c, 6a, 6b, 6c disconnected" as status; +call dump_all(); + +truncate table performance_schema.hosts; +call dump_all(); +truncate table performance_schema.users; +call dump_all(); +truncate table performance_schema.accounts; +call dump_all(); + diff --git a/mysql-test/suite/perfschema/include/connection_setup.inc b/mysql-test/suite/perfschema/include/connection_setup.inc new file mode 100644 index 00000000..93b8e959 --- /dev/null +++ b/mysql-test/suite/perfschema/include/connection_setup.inc @@ -0,0 +1,114 @@ +# Tests for the performance schema + +# ============= +# DOCUMENTATION +# ============= + +# Verify how connections are counted into various tables: +# - accounts +# - users +# - hosts +# +# The tests are written with the following helpers: +# - include/connection_setup.inc +# - include/connection_load.inc +# - include/connection_cleanup.inc +# +# Helpers are intended to be used as follows. +# +# A Typical test t/connection_xxx.test will consist of: +# --source ../include/connection_setup.inc +# --source ../include/connection_load.inc +# --source ../include/connection_cleanup.inc +# and a t/connection_xxx-master.opt file +# +# Naming conventions for t/connection_xxx.test are as follows: +# t/connection_<account><user><host> +# +# <account> corresponds to different sizing settings for +# the variable performance-schema-accounts-size +# - (blank): accounts-size sufficient to represent all records +# - 3a: accounts-size set to 3 +# - no_a: accounts-size set to 0 +# +# <user> corresponds to different sizing settings for +# the variable performance-schema-users-size +# - (blank): users-size sufficient to represent all records +# - 3u: users-size set to 3 +# - no_u: users-size set to 0 +# +# <host> corresponds to different sizing settings for +# the variable performance-schema-hosts-size +# - (blank): hosts-size sufficient to represent all records +# - no_h: hosts-size set to 0 + +# ======================================== +# HELPER include/event_aggregate_setup.inc +# ======================================== + +--source include/not_embedded.inc +--source include/have_perfschema.inc +--source include/no_protocol.inc +--source ../include/wait_for_pfs_thread_count.inc + +--disable_query_log + +create user user1@localhost; +grant ALL on *.* to user1@localhost; +create user user2@localhost; +grant ALL on *.* to user2@localhost; +create user user3@localhost; +grant ALL on *.* to user3@localhost; +create user user4@localhost; +grant ALL on *.* to user4@localhost; +create user user5@localhost; +grant ALL on *.* to user5@localhost; + +flush privileges; + +# Purge old users, hosts, user/host from previous tests +truncate table performance_schema.accounts; +truncate table performance_schema.users; +truncate table performance_schema.hosts; + +# Save the setup + +# Start from a known clean state, to avoid noise from previous tests +flush tables; +flush status; + +--disable_warnings +drop procedure if exists dump_all; +--enable_warnings + +delimiter $$; + +create procedure dump_all() +begin + select processlist_user, processlist_host + from performance_schema.threads + where (processlist_user is not null) and (processlist_host is not null) + order by processlist_user; + + select * from performance_schema.accounts + where (user is not null) and (host is not null) + order by user, host; + + select * from performance_schema.users + where user is not null + order by user; + + select * from performance_schema.hosts + where host is not null + order by host; + + select variable_name, variable_value from information_schema.global_status + where variable_name in ('PERFORMANCE_SCHEMA_ACCOUNTS_LOST', + 'PERFORMANCE_SCHEMA_USERS_LOST', + 'PERFORMANCE_SCHEMA_HOSTS_LOST'); +end +$$ + +delimiter ;$$ + +--enable_query_log diff --git a/mysql-test/suite/perfschema/include/default_mysqld_autosize.cnf b/mysql-test/suite/perfschema/include/default_mysqld_autosize.cnf new file mode 100644 index 00000000..6bcf7a09 --- /dev/null +++ b/mysql-test/suite/perfschema/include/default_mysqld_autosize.cnf @@ -0,0 +1,52 @@ + +# Default values that applies to all MySQL Servers +[mysqld] +local-infile +character-set-server= latin1 +default-storage-engine=myisam + +# Increase default connect_timeout to avoid intermittent +# disconnects when test servers are put under load see BUG#28359 +connect-timeout= 60 + +log-bin-trust-function-creators=1 +key_buffer_size= 1M +sort_buffer_size= 256K +max_heap_table_size= 1M + +loose-innodb_data_file_path= ibdata1:10M:autoextend +loose-innodb_buffer_pool_size= 8M +loose-innodb_lru_scan_depth= 100 +loose-innodb_write_io_threads= 2 +loose-innodb_read_io_threads= 2 +loose-innodb_log_buffer_size= 2M +loose-innodb_log_file_size= 10M + +slave-net-timeout=120 + +log-bin=mysqld-bin + +# No performance schema sizing provided + +# Disable everything, we only need the sizing data, +# and also need a stable output for show engine performance_schema status +loose-performance-schema-consumer-global-instrumentation=OFF + +loose-performance-schema-instrument='%=ON' + +loose-performance-schema-consumer-events-stages-current=ON +loose-performance-schema-consumer-events-stages-history=ON +loose-performance-schema-consumer-events-stages-history-long=ON +loose-performance-schema-consumer-events-statements-current=ON +loose-performance-schema-consumer-events-statements-history=ON +loose-performance-schema-consumer-events-statements-history-long=ON +loose-performance-schema-consumer-events-transactions-current=ON +loose-performance-schema-consumer-events-transactions-history=ON +loose-performance-schema-consumer-events-transactions-history-long=ON +loose-performance-schema-consumer-events-waits-current=ON +loose-performance-schema-consumer-events-waits-history=ON +loose-performance-schema-consumer-events-waits-history-long=ON +loose-performance-schema-consumer-thread-instrumentation=ON + +binlog-direct-non-transactional-updates + diff --git a/mysql-test/suite/perfschema/include/digest_cleanup.inc b/mysql-test/suite/perfschema/include/digest_cleanup.inc new file mode 100644 index 00000000..47dd7618 --- /dev/null +++ b/mysql-test/suite/perfschema/include/digest_cleanup.inc @@ -0,0 +1,14 @@ +--echo #################################### +--echo # CLEANUP +--echo #################################### +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +DROP TABLE IF EXISTS t3; +DROP TABLE IF EXISTS t4; +DROP TABLE IF EXISTS t5; +DROP TABLE IF EXISTS t6; +DROP TABLE IF EXISTS t11; +DROP TABLE IF EXISTS t12; +DROP DATABASE IF EXISTS statements_digest; +--enable_warnings diff --git a/mysql-test/suite/perfschema/include/digest_execution.inc b/mysql-test/suite/perfschema/include/digest_execution.inc new file mode 100644 index 00000000..cf83bf9f --- /dev/null +++ b/mysql-test/suite/perfschema/include/digest_execution.inc @@ -0,0 +1,130 @@ +--echo #################################### +--echo # EXECUTION +--echo #################################### + +# ----------------------------------- +# SQL Queries to test normalizations. +# ----------------------------------- +SELECT 1 FROM t1; +SELECT 1 FROM `t1`; +SELECT 1,2 FROM t1; +SELECT 1, 2, 3, 4 FROM t1; +SELECT 1 FROM t2; +SELECT 1,2 FROM t2; +SELECT 1, 2, 3, 4 FROM t2; + +# (NUM) => (#) +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (1); + +# (NUM,NUM) => (#,#) +INSERT INTO t3 VALUES (1, 2); +INSERT INTO t4 VALUES (1, 2); +# (NUM,NUM,NUM) => (#,#) +INSERT INTO t5 VALUES (1, 2, 3); + +# (NUM),(NUM) => (#),(#) +INSERT INTO t1 VALUES (1), (2), (3); +# (NUM),(NUM),(NUM) => (#),(#) +INSERT INTO t1 VALUES (1), (2), (3), (4); + +# (NUM,NUM),(NUM,NUM) => (#,#),(#,#) +INSERT INTO t3 VALUES (1, 2), (3, 4), (5, 6); +# (NUM,NUM,NUM),(NUM,NUM,NUM),(NUM,NUM,NUM) => (#,#),(#,#) +INSERT INTO t5 VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9); + +# ----------------------------------------------------------------------- +# Test case to handle NULL. If alone, not normalized otherwise normalized. +# ----------------------------------------------------------------------- +INSERT INTO t1 VALUES (NULL); +INSERT INTO t3 VALUES (NULL,NULL); +INSERT INTO t3 VALUES (1,NULL); +INSERT INTO t3 VALUES (NULL,1); +INSERT INTO t6 VALUES (NULL, NULL, NULL, NULL); +INSERT INTO t6 VALUES (1, NULL, NULL, NULL); +INSERT INTO t6 VALUES (NULL, 2, NULL, NULL); +INSERT INTO t6 VALUES (1, 2, 3, NULL); +INSERT INTO t6 VALUES (1, 2, NULL, 4); + +# ----------------------------------------------------------------------- +# Test case for handling spaces in statement. +# ----------------------------------------------------------------------- + +SELECT 1 + 1; + +# ----------------------------------------------------------------------- +# Test case for handling comments. +# ----------------------------------------------------------------------- + +# comment starting with "--" +# TODO : SELECT 1; -- This comment continues to the end of line +# comment starting from "#" +SELECT 1; # This comment continues to the end of line + +# Inline comment +SELECT 1 /* This is an inline comment */ + 1; + +# Multiple line comments + SELECT 1+ + /* + this is a + multiple-line comment + */ + 1; + +# ----------------------------------------------------------------------- +# Tests to show how the digest behaves with tokens that can have multiple +# names (such as DATABASE = "DATABASE" or "SCHEMA", SUBSTRING, STD_SYM, +# VARIANCE_SYM ... ) +# ----------------------------------------------------------------------- + +--disable_warnings +CREATE SCHEMA statements_digest_temp; +DROP SCHEMA statements_digest_temp; +CREATE DATABASE statements_digest_temp; +DROP DATABASE statements_digest_temp; +# TODO : add more +--enable_warnings + +# ----------------------------------------------------------------------- +# Test case to show stats for statements giving ERRORS/WARNINGS, are also +# captured. +# ----------------------------------------------------------------------- +--ERROR ER_NO_SUCH_TABLE +SELECT 1 FROM no_such_table; +CREATE TABLE dup_table (c char(4)); +--ERROR ER_TABLE_EXISTS_ERROR +CREATE TABLE dup_table (c char(4)); +DROP TABLE dup_table; +INSERT IGNORE INTO t11 VALUES("MySQL"); + +# ----------------------------------------------------------------------- +# Tests to show sub-statements for following statements are not +# instrumented. +# - Prepared Statements +# - Stored Procedures/Functions. +# - Table Triggers +# ----------------------------------------------------------------------- +PREPARE stmt FROM "SELECT * FROM t12"; +EXECUTE stmt; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; + +DELIMITER //; +CREATE PROCEDURE p1() BEGIN SELECT * FROM t12; END// +DELIMITER ;// +CALL p1(); +CALL p1(); +DROP PROCEDURE p1; + +DELIMITER //; +CREATE FUNCTION `func`(a INT, b INT) RETURNS int(11) RETURN a+b // +DELIMITER ;// +select func(3,4); +select func(13,42); +DROP FUNCTION func; + +CREATE TRIGGER trg BEFORE INSERT ON t12 FOR EACH ROW SET @a:=1; +INSERT INTO t12 VALUES ("abc"); +INSERT INTO t12 VALUES ("def"); +DROP TRIGGER trg; diff --git a/mysql-test/suite/perfschema/include/digest_setup.inc b/mysql-test/suite/perfschema/include/digest_setup.inc new file mode 100644 index 00000000..6efab880 --- /dev/null +++ b/mysql-test/suite/perfschema/include/digest_setup.inc @@ -0,0 +1,24 @@ +# Making sure not to run when ps-protocol is set. +--source include/no_protocol.inc + +--echo #################################### +--echo # SETUP +--echo #################################### +# Database setup +--disable_warnings +CREATE DATABASE statements_digest; +--enable_warnings +USE statements_digest; + +# Table set up for queries +--disable_warnings +CREATE TABLE t1(a int); +CREATE TABLE t2(a int); +CREATE TABLE t3(a int, b int); +CREATE TABLE t4(a int, b int); +CREATE TABLE t5(a int, b int, c int); +CREATE TABLE t6(a int, b int, c int, d int); +CREATE TABLE t11 (c CHAR(4)); +CREATE TABLE t12 (c CHAR(4)); +--enable_warnings + diff --git a/mysql-test/suite/perfschema/include/disable_instruments.inc b/mysql-test/suite/perfschema/include/disable_instruments.inc new file mode 100644 index 00000000..b0a4ddec --- /dev/null +++ b/mysql-test/suite/perfschema/include/disable_instruments.inc @@ -0,0 +1,23 @@ +# Tests for the performance schema + +# ========================================== +# HELPER include/disable_instruments +# ========================================== +# +# ==== Usage ==== +# +# 1. Define the instrument to be disabled/enabled: +# +# let $pfs_instrument=<instrument> +# +# Where <instrument> is the instrument name with or without wildcards, e.g. +# +# let $pfs_instrument='%statement/sql%' +# +--disable_query_log ONCE + +eval update performance_schema.setup_instruments + set enabled='no', timed='no' + where name like $pfs_instrument; + + diff --git a/mysql-test/suite/perfschema/include/enable_instruments.inc b/mysql-test/suite/perfschema/include/enable_instruments.inc new file mode 100644 index 00000000..94d63aee --- /dev/null +++ b/mysql-test/suite/perfschema/include/enable_instruments.inc @@ -0,0 +1,23 @@ +# Tests for the performance schema + +# ========================================== +# HELPER include/enable_instruments +# ========================================== +# +# ==== Usage ==== +# +# 1. Define the instrument to be enabled/disabled: +# +# let $pfs_instrument=<instrument> +# +# Where <instrument> is the instrument name with or without wildcards, e.g. +# +# let $pfs_instrument='%statement/sql%' +# +--disable_query_log ONCE + +eval update performance_schema.setup_instruments + set enabled='yes', timed='yes' + where name like $pfs_instrument; + + diff --git a/mysql-test/suite/perfschema/include/event_aggregate_cleanup.inc b/mysql-test/suite/perfschema/include/event_aggregate_cleanup.inc new file mode 100644 index 00000000..2cb8a69e --- /dev/null +++ b/mysql-test/suite/perfschema/include/event_aggregate_cleanup.inc @@ -0,0 +1,64 @@ +# Tests for the performance schema + +# ========================================== +# HELPER include/event_aggregate_cleanup.inc +# ========================================== + +--disable_query_log +revoke all privileges, grant option from user1@localhost; +revoke all privileges, grant option from user2@localhost; +revoke all privileges, grant option from user3@localhost; +revoke all privileges, grant option from user4@localhost; +drop user user1@localhost; +drop user user2@localhost; +drop user user3@localhost; +drop user user4@localhost; +flush privileges; + +drop procedure dump_thread; +drop procedure dump_one_thread; + +drop prepare dump_waits_account; +drop prepare dump_waits_user; +drop prepare dump_waits_host; +drop prepare dump_waits_history; +drop prepare dump_waits_global; + +drop prepare dump_stages_account; +drop prepare dump_stages_user; +drop prepare dump_stages_host; +drop prepare dump_stages_history; +drop prepare dump_stages_global; + +drop prepare dump_statements_account; +drop prepare dump_statements_user; +drop prepare dump_statements_host; +drop prepare dump_statements_history; +drop prepare dump_statements_global; + +drop prepare dump_transactions_account; +drop prepare dump_transactions_user; +drop prepare dump_transactions_host; +drop prepare dump_transactions_history; +drop prepare dump_transactions_global; + +drop prepare dump_users; +drop prepare dump_hosts; +drop prepare dump_accounts; + +truncate table performance_schema.accounts; +truncate table performance_schema.users; +truncate table performance_schema.hosts; + +truncate table performance_schema.setup_actors; +insert into performance_schema.setup_actors + select * from test.setup_actors; +drop table test.setup_actors; +drop table test.t1; +drop function test.f; + +update performance_schema.threads set instrumented='YES'; +update performance_schema.setup_instruments set enabled='YES', timed='YES'; + +--enable_query_log + diff --git a/mysql-test/suite/perfschema/include/event_aggregate_load.inc b/mysql-test/suite/perfschema/include/event_aggregate_load.inc new file mode 100644 index 00000000..a4815eaf --- /dev/null +++ b/mysql-test/suite/perfschema/include/event_aggregate_load.inc @@ -0,0 +1,1197 @@ +# Tests for the performance schema + +# ======================================= +# HELPER include/event_aggregate_load.inc +# ======================================= + +echo "================== Step 1 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +# Notes about this test +# +# The main thread does not count for BY_ACCOUNT / BY_HOST. +# The user thread does count for BY_ACCOUNT, BY_HOST +# +# Each uuid_short() causes 1 wait/synch/mutex/sql/LOCK_uuid_generator +# +# To avoid noise from main, the background threads are disabled. + +connect (con1, localhost, user1, ,test); + +--connection default + +# Wait for the connect to complete +let $wait_condition= + select count(*) = 1 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user1'; +--source include/wait_condition.inc + +echo "================== Step 2 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +--connection con1 + +select uuid_short() <> 1; +select uuid_short() <> 1; +start transaction; +insert into test.t1 values ("marker"); +commit; +select test.f(10,20); + +--connection default + +# Wait for the payload to complete +let $wait_condition= + select count(*) = 1 from performance_schema.events_waits_current + where EVENT_NAME= 'idle'; +--source include/wait_condition.inc + +echo "================== Step 3 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +# Debugging helpers +# select * from performance_schema.events_waits_history_long; +# select PROCESSLIST_USER, PROCESSLIST_HOST, INSTRUMENTED from performance_schema.threads; + +connect (con2, localhost, user2, ,test); + +--connection default + +# Wait for the connect to complete +let $wait_condition= + select count(*) = 1 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user2'; +--source include/wait_condition.inc + +echo "================== Step 4 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +--connection con2 + +select uuid_short() <> 1; +select uuid_short() <> 1; +start transaction; +insert into test.t1 values ("marker"); +commit; +select test.f(10,20); + +--connection default + +# Wait for the payload to complete +let $wait_condition= + select count(*) = 2 from performance_schema.events_waits_current + where EVENT_NAME= 'idle'; +--source include/wait_condition.inc + +echo "================== Step 5 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +connect (con3, localhost, user3, ,test); + +--connection default + +# Wait for the connect to complete +let $wait_condition= + select count(*) = 1 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user3'; +--source include/wait_condition.inc + +echo "================== Step 6 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +--connection con3 + +select uuid_short() <> 1; +select uuid_short() <> 1; +start transaction; +insert into test.t1 values ("marker"); +commit; +select test.f(10,20); + +--connection default + +# Wait for the payload to complete +let $wait_condition= + select count(*) = 3 from performance_schema.events_waits_current + where EVENT_NAME= 'idle'; +--source include/wait_condition.inc + +echo "================== Step 7 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +connect (con4, localhost, user4, ,test); + +--connection default + +# Wait for the connect to complete +let $wait_condition= + select count(*) = 1 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user4'; +--source include/wait_condition.inc + +echo "================== Step 8 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +--connection con4 + +select uuid_short() <> 1; +select uuid_short() <> 1; +start transaction; +insert into test.t1 values ("marker"); +commit; +select test.f(10,20); + +--connection default + +# Wait for the payload to complete +let $wait_condition= + select count(*) = 4 from performance_schema.events_waits_current + where EVENT_NAME= 'idle'; +--source include/wait_condition.inc + +echo "================== Step 9 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +--disconnect con1 + +--connection default + +# Wait for the disconnect to complete +let $wait_condition= + select count(*) = 0 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user1'; +--source include/wait_condition.inc + +echo "================== Step 10 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +--disconnect con2 + +--connection default + +# Wait for the disconnect to complete +let $wait_condition= + select count(*) = 0 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user2'; +--source include/wait_condition.inc + +echo "================== Step 11 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +--disconnect con3 + +--connection default + +# Wait for the disconnect to complete +let $wait_condition= + select count(*) = 0 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user3'; +--source include/wait_condition.inc + +echo "================== Step 12 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +--disconnect con4 + +--connection default + +# Wait for the disconnect to complete +let $wait_condition= + select count(*) = 0 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user4'; +--source include/wait_condition.inc + +echo "================== Step 13 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +--connection default + +truncate performance_schema.events_waits_summary_by_thread_by_event_name; + +echo "================== WAITS_BY_THREAD truncated =================="; + +echo "================== Step 14 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.events_waits_summary_by_account_by_event_name; + +echo "================== WAITS_BY_ACCOUNT truncated =================="; + +echo "================== Step 15 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.events_waits_summary_by_user_by_event_name; + +echo "================== WAITS_BY_USER truncated =================="; + +echo "================== Step 16 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.events_waits_summary_by_host_by_event_name; + +echo "================== WAITS_BY_HOST truncated =================="; + +echo "================== Step 17 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.events_waits_summary_global_by_event_name; + +echo "================== WAITS_GLOBAL truncated =================="; + +echo "================== Step 18 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.events_stages_summary_by_thread_by_event_name; + +echo "================== STAGES_BY_THREAD truncated =================="; + +echo "================== Step 19 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.events_stages_summary_by_account_by_event_name; + +echo "================== STAGES_BY_ACCOUNT truncated =================="; + +echo "================== Step 20 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.events_stages_summary_by_user_by_event_name; + +echo "================== STAGES_BY_USER truncated =================="; + +echo "================== Step 21 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.events_stages_summary_by_host_by_event_name; + +echo "================== STAGES_BY_HOST truncated =================="; + +echo "================== Step 22 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.events_stages_summary_global_by_event_name; + +echo "================== STAGES_GLOBAL truncated =================="; + +echo "================== Step 23 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.events_statements_summary_by_thread_by_event_name; + +echo "================== STATEMENTS_BY_THREAD truncated =================="; + +echo "================== Step 24 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.events_statements_summary_by_account_by_event_name; + +echo "================== STATEMENTS_BY_ACCOUNT truncated =================="; + +echo "================== Step 25 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.events_statements_summary_by_user_by_event_name; + +echo "================== STATEMENTS_BY_USER truncated =================="; + +echo "================== Step 26 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.events_statements_summary_by_host_by_event_name; + +echo "================== STATEMENTS_BY_HOST truncated =================="; + +echo "================== Step 27 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.events_statements_summary_global_by_event_name; + +echo "================== STATEMENTS_GLOBAL truncated =================="; + +echo "================== Step 28 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.events_transactions_summary_by_thread_by_event_name; + +echo "================== TRANSACTIONS_BY_THREAD truncated =================="; + +echo "================== Step 29 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.events_transactions_summary_by_account_by_event_name; + +echo "================== TRANSACTIONS_BY_ACCOUNT truncated =================="; + +echo "================== Step 30 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.events_transactions_summary_by_user_by_event_name; + +echo "================== TRANSACTIONS_BY_USER truncated =================="; + +echo "================== Step 31 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.events_transactions_summary_by_host_by_event_name; + +echo "================== TRANSACTIONS_BY_HOST truncated =================="; + +echo "================== Step 32 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.events_transactions_summary_global_by_event_name; + +echo "================== TRANSACTIONS_GLOBAL truncated =================="; + +echo "================== Step 33 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.accounts; + +echo "================== ACCOUNTS truncated =================="; + +echo "================== Step 34 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.users; + +echo "================== USERS truncated =================="; + +echo "================== Step 35 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.hosts; + +echo "================== HOSTS truncated =================="; + +echo "================== Step 36 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_stages_account; +execute dump_stages_user; +execute dump_stages_host; +execute dump_stages_global; +execute dump_stages_history; +execute dump_statements_account; +execute dump_statements_user; +execute dump_statements_host; +execute dump_statements_global; +execute dump_statements_history; +execute dump_transactions_account; +execute dump_transactions_user; +execute dump_transactions_host; +execute dump_transactions_global; +execute dump_transactions_history; +execute dump_accounts; +execute dump_users; +execute dump_hosts; + diff --git a/mysql-test/suite/perfschema/include/event_aggregate_setup.inc b/mysql-test/suite/perfschema/include/event_aggregate_setup.inc new file mode 100644 index 00000000..03f88e97 --- /dev/null +++ b/mysql-test/suite/perfschema/include/event_aggregate_setup.inc @@ -0,0 +1,413 @@ +# Tests for the performance schema + +# ============= +# DOCUMENTATION +# ============= + +# Verify how events are aggregated into various tables +# +# In the thread dimension: +# - events_waits_summary_by_thread_by_event_name +# - events_waits_summary_by_account_by_event_name +# - events_waits_summary_by_user_by_event_name +# - events_waits_summary_by_host_by_event_name +# - events_stages_summary_by_thread_by_event_name +# - events_stages_summary_by_account_by_event_name +# - events_stages_summary_by_user_by_event_name +# - events_stages_summary_by_host_by_event_name +# - events_statements_summary_by_thread_by_event_name +# - events_statements_summary_by_account_by_event_name +# - events_statements_summary_by_user_by_event_name +# - events_statements_summary_by_host_by_event_name +# - events_transactions_summary_by_thread_by_event_name +# - events_transactions_summary_by_account_by_event_name +# - events_transactions_summary_by_user_by_event_name +# - events_transactions_summary_by_host_by_event_name +# +# Globally: +# - events_waits_summary_global_by_event_name +# - events_stages_summary_global_by_event_name +# - events_statements_summary_global_by_event_name +# - events_transactions_summary_global_by_event_name +# +# The tests are written with the following helpers: +# - include/event_aggregate_setup.inc +# - include/event_aggregate_load.inc +# - include/event_aggregate_cleanup.inc +# +# Helpers are intended to be used as follows. +# +# A Typical test t/event_aggregate_xxx.test will consist of: +# --source ../include/event_aggregate_setup.inc +# --source ../include/event_aggregate_load.inc +# --source ../include/event_aggregate_cleanup.inc +# and a t/event_aggregate_xxx-master.opt file +# +# Naming conventions for t/event_aggregate_xxx.test are as follows: +# t/event_aggregate_<account><user><host> +# +# <account> corresponds to different sizing settings for +# the variable performance-schema-accounts-size +# - (blank): accounts-size sufficient to represent all records +# - no_a: accounts-size set to 0 +# +# <user> corresponds to different sizing settings for +# the variable performance-schema-users-size +# - (blank): users-size sufficient to represent all records +# - no_u: users-size set to 0 +# +# <host> corresponds to different sizing settings for +# the variable performance-schema-hosts-size +# - (blank): hosts-size sufficient to represent all records +# - no_h: hosts-size set to 0 + +# ======================================== +# HELPER include/event_aggregate_setup.inc +# ======================================== + +--source include/not_embedded.inc +--source include/have_perfschema.inc +--source include/no_protocol.inc +--source ../include/wait_for_pfs_thread_count.inc + +--disable_query_log +create user user1@localhost; +grant ALL on *.* to user1@localhost; +create user user2@localhost; +grant ALL on *.* to user2@localhost; +create user user3@localhost; +grant ALL on *.* to user3@localhost; +create user user4@localhost; +grant ALL on *.* to user4@localhost; + +flush privileges; + +# Purge old users, hosts, user/host from previous tests +truncate table performance_schema.accounts; +truncate table performance_schema.users; +truncate table performance_schema.hosts; + +# Save the setup + +--disable_warnings +drop table if exists test.setup_actors; +drop table if exists test.t1; +--enable_warnings + +create table test.t1(a varchar(64)); +create function test.f(a int, b int) returns int +return a+b; + +create table test.setup_actors as + select * from performance_schema.setup_actors; + +# Only instrument the user connections +truncate table performance_schema.setup_actors; +insert into performance_schema.setup_actors + set host= 'localhost', user= 'user1', role= '%'; +insert into performance_schema.setup_actors + set host= 'localhost', user= 'user2', role= '%'; +insert into performance_schema.setup_actors + set host= 'localhost', user= 'user3', role= '%'; +insert into performance_schema.setup_actors + set host= 'localhost', user= 'user4', role= '%'; + +update performance_schema.threads set instrumented='NO'; + +# Only instrument a few events of each kind +update performance_schema.setup_instruments set enabled='NO', timed='NO'; + +update performance_schema.setup_instruments set enabled='YES', timed='YES' + where name in ('wait/synch/mutex/sql/LOCK_connection_count', + 'wait/synch/mutex/sql/LOCK_uuid_generator', + 'wait/synch/rwlock/sql/LOCK_grant', + 'wait/io/file/sql/query_log', + 'idle'); + +update performance_schema.setup_instruments set enabled='YES', timed='YES' + where name in ('stage/sql/starting', + 'stage/sql/init', + 'stage/sql/checking permissions', + 'stage/sql/Opening tables', + 'stage/sql/Closing tables'); + +update performance_schema.setup_instruments set enabled='YES', timed='YES' + where name in ('statement/sql/select', + 'statement/sql/insert', + 'statement/abstract/new_packet', + 'statement/abstract/Query', + 'statement/com/Quit', + 'statement/com/error', + 'statement/sp/freturn'); + +update performance_schema.setup_instruments set enabled='YES', timed='YES' + where name in ('transaction'); + +# Start from a known clean state, to avoid noise from previous tests +flush tables; +flush status; +truncate performance_schema.events_waits_summary_by_thread_by_event_name; +truncate performance_schema.events_waits_summary_by_account_by_event_name; +truncate performance_schema.events_waits_summary_by_user_by_event_name; +truncate performance_schema.events_waits_summary_by_host_by_event_name; +truncate performance_schema.events_waits_summary_global_by_event_name; +truncate performance_schema.events_waits_history_long; + +truncate performance_schema.events_stages_summary_by_thread_by_event_name; +truncate performance_schema.events_stages_summary_by_account_by_event_name; +truncate performance_schema.events_stages_summary_by_user_by_event_name; +truncate performance_schema.events_stages_summary_by_host_by_event_name; +truncate performance_schema.events_stages_summary_global_by_event_name; +truncate performance_schema.events_stages_history_long; + +truncate performance_schema.events_statements_summary_by_thread_by_event_name; +truncate performance_schema.events_statements_summary_by_account_by_event_name; +truncate performance_schema.events_statements_summary_by_user_by_event_name; +truncate performance_schema.events_statements_summary_by_host_by_event_name; +truncate performance_schema.events_statements_summary_global_by_event_name; +truncate performance_schema.events_statements_history_long; + +truncate performance_schema.events_transactions_summary_by_thread_by_event_name; +truncate performance_schema.events_transactions_summary_by_account_by_event_name; +truncate performance_schema.events_transactions_summary_by_user_by_event_name; +truncate performance_schema.events_transactions_summary_by_host_by_event_name; +truncate performance_schema.events_transactions_summary_global_by_event_name; +truncate performance_schema.events_transactions_history_long; + +--disable_warnings +drop procedure if exists dump_thread; +drop procedure if exists dump_one_thread; +--enable_warnings + +delimiter $$; + +create procedure dump_thread() +begin + call dump_one_thread('user1'); + call dump_one_thread('user2'); + call dump_one_thread('user3'); + call dump_one_thread('user4'); +end +$$ + +create procedure dump_one_thread(in username varchar(64)) +begin + declare my_thread_id int; + + set my_thread_id = (select thread_id from performance_schema.threads + where processlist_user=username); + + if (my_thread_id is not null) then + select username, event_name, count_star + from performance_schema.events_waits_summary_by_thread_by_event_name + where event_name in ('wait/synch/mutex/sql/LOCK_connection_count', + 'wait/synch/mutex/sql/LOCK_uuid_generator', + 'wait/synch/rwlock/sql/LOCK_grant', + 'wait/io/file/sql/query_log') + and thread_id = my_thread_id + order by event_name; + else + select username, "not found" as status; + end if; +end +$$ + +delimiter ;$$ + +prepare dump_waits_account from + "select user, host, event_name, count_star + from performance_schema.events_waits_summary_by_account_by_event_name + where user like \'user%\' + and event_name in ('wait/synch/mutex/sql/LOCK_connection_count', + 'wait/synch/mutex/sql/LOCK_uuid_generator', + 'wait/synch/rwlock/sql/LOCK_grant', + 'wait/io/file/sql/query_log') + order by user, host, event_name;"; + +prepare dump_waits_user from + "select user, event_name, count_star + from performance_schema.events_waits_summary_by_user_by_event_name + where user like \'user%\' + and event_name in ('wait/synch/mutex/sql/LOCK_connection_count', + 'wait/synch/mutex/sql/LOCK_uuid_generator', + 'wait/synch/rwlock/sql/LOCK_grant', + 'wait/io/file/sql/query_log') + order by user, event_name;"; + +prepare dump_waits_host from + "select host, event_name, count_star + from performance_schema.events_waits_summary_by_host_by_event_name + where host=\'localhost\' + and event_name in ('wait/synch/mutex/sql/LOCK_connection_count', + 'wait/synch/mutex/sql/LOCK_uuid_generator', + 'wait/synch/rwlock/sql/LOCK_grant', + 'wait/io/file/sql/query_log') + order by host, event_name;"; + +prepare dump_waits_global from + "select event_name, count_star + from performance_schema.events_waits_summary_global_by_event_name + where event_name in ('wait/synch/mutex/sql/LOCK_connection_count', + 'wait/synch/mutex/sql/LOCK_uuid_generator', + 'wait/synch/rwlock/sql/LOCK_grant', + 'wait/io/file/sql/query_log') + order by event_name;"; + +prepare dump_waits_history from + "select event_name, count(event_name) + from performance_schema.events_waits_history_long + where event_name in ('wait/synch/mutex/sql/LOCK_connection_count', + 'wait/synch/mutex/sql/LOCK_uuid_generator', + 'wait/synch/rwlock/sql/LOCK_grant', + 'wait/io/file/sql/query_log') + group by event_name order by event_name;"; + +prepare dump_stages_account from + "select user, host, event_name, count_star + from performance_schema.events_stages_summary_by_account_by_event_name + where user like \'user%\' + and event_name in ('stage/sql/starting', + 'stage/sql/init', + 'stage/sql/checking permissions', + 'stage/sql/Opening tables', + 'stage/sql/closing tables') + order by user, host, event_name;"; + +prepare dump_stages_user from + "select user, event_name, count_star + from performance_schema.events_stages_summary_by_user_by_event_name + where user like \'user%\' + and event_name in ('stage/sql/starting', + 'stage/sql/init', + 'stage/sql/checking permissions', + 'stage/sql/Opening tables', + 'stage/sql/closing tables') + order by user, event_name;"; + +prepare dump_stages_host from + "select host, event_name, count_star + from performance_schema.events_stages_summary_by_host_by_event_name + where host=\'localhost\' + and event_name in ('stage/sql/starting', + 'stage/sql/init', + 'stage/sql/checking permissions', + 'stage/sql/Opening tables', + 'stage/sql/closing tables') + order by host, event_name;"; + +prepare dump_stages_global from + "select event_name, count_star + from performance_schema.events_stages_summary_global_by_event_name + where event_name in ('stage/sql/starting', + 'stage/sql/init', + 'stage/sql/checking permissions', + 'stage/sql/Opening tables', + 'stage/sql/closing tables') + order by event_name;"; + +prepare dump_stages_history from + "select event_name, count(event_name) + from performance_schema.events_stages_history_long + where event_name in ('stage/sql/starting', + 'stage/sql/init', + 'stage/sql/checking permissions', + 'stage/sql/Opening tables', + 'stage/sql/closing tables') + group by event_name order by event_name;"; + +prepare dump_statements_account from + "select user, host, event_name, count_star + from performance_schema.events_statements_summary_by_account_by_event_name + where user like \'user%\' + and event_name in ('statement/sql/select', + 'statement/sql/insert', + 'statement/com/Quit', + 'statement/com/error', + 'statement/sp/freturn') + order by user, host, event_name;"; + +prepare dump_statements_user from + "select user, event_name, count_star + from performance_schema.events_statements_summary_by_user_by_event_name + where user like \'user%\' + and event_name in ('statement/sql/select', + 'statement/sql/insert', + 'statement/com/Quit', + 'statement/com/error', + 'statement/sp/freturn') + order by user, event_name;"; + +prepare dump_statements_host from + "select host, event_name, count_star + from performance_schema.events_statements_summary_by_host_by_event_name + where host=\'localhost\' + and event_name in ('statement/sql/select', + 'statement/sql/insert', + 'statement/com/Quit', + 'statement/com/error', + 'statement/sp/freturn') + order by host, event_name;"; + +prepare dump_statements_global from + "select event_name, count_star + from performance_schema.events_statements_summary_global_by_event_name + where event_name in ('statement/sql/select', + 'statement/sql/insert', + 'statement/com/Quit', + 'statement/com/error', + 'statement/sp/freturn') + order by event_name;"; + +prepare dump_statements_history from + "select event_name, count(event_name) + from performance_schema.events_statements_history_long + where event_name in ('statement/sql/select', + 'statement/sql/insert', + 'statement/com/Quit', + 'statement/com/error', + 'statement/sp/freturn') + group by event_name order by event_name;"; + +prepare dump_transactions_account from + "select user, host, event_name, count_star + from performance_schema.events_transactions_summary_by_account_by_event_name + where user like \'user%\' + and event_name in ('transaction') + order by user, host, event_name;"; + +prepare dump_transactions_user from + "select user, event_name, count_star + from performance_schema.events_transactions_summary_by_user_by_event_name + where user like \'user%\' + and event_name in ('transaction') + order by user, event_name;"; + +prepare dump_transactions_host from + "select host, event_name, count_star + from performance_schema.events_transactions_summary_by_host_by_event_name + where host=\'localhost\' + and event_name in ('transaction') + order by host, event_name;"; + +prepare dump_transactions_global from + "select event_name, count_star + from performance_schema.events_transactions_summary_global_by_event_name + where event_name in ('transaction') + order by event_name;"; + +prepare dump_transactions_history from + "select event_name, count(event_name) + from performance_schema.events_transactions_history_long + where event_name in ('transaction') + group by event_name order by event_name;"; + +prepare dump_users from + "select * from performance_schema.users where user is not null order by user;"; + +prepare dump_hosts from + "select * from performance_schema.hosts where host is not null order by host;"; + +prepare dump_accounts from + "select * from performance_schema.accounts where (user is not null) and (host is not null) order by user, host;"; + +--enable_query_log diff --git a/mysql-test/suite/perfschema/include/have_aligned_memory.inc b/mysql-test/suite/perfschema/include/have_aligned_memory.inc new file mode 100644 index 00000000..d420f0e0 --- /dev/null +++ b/mysql-test/suite/perfschema/include/have_aligned_memory.inc @@ -0,0 +1,10 @@ +# The performance schema internal structures are compiled with PFS_ALIGN, +# and the sizeof() structures is platform dependent. +# +# For tests sensitive to the internal sizes (show engine performance_schema +# status), make sure we use a platform with aligned memory. + +if (`SELECT count(*)=0 from performance_schema.session_connect_attrs where PROCESSLIST_ID = connection_id() and ATTR_NAME = '_os' and ATTR_VALUE in ('Linux', 'Windows')`) +{ + skip Need a platform with aligned memory; +} diff --git a/mysql-test/suite/perfschema/include/have_timer_cycle.inc b/mysql-test/suite/perfschema/include/have_timer_cycle.inc new file mode 100644 index 00000000..b801ea25 --- /dev/null +++ b/mysql-test/suite/perfschema/include/have_timer_cycle.inc @@ -0,0 +1,4 @@ +if (!`SELECT count(*) FROM performance_schema.performance_timers WHERE timer_name='CYCLE' AND timer_frequency IS NOT NULL`) +{ + Skip Need performance timer CYCLE; +} diff --git a/mysql-test/suite/perfschema/include/hostcache_dump.inc b/mysql-test/suite/perfschema/include/hostcache_dump.inc new file mode 100644 index 00000000..1d595ba2 --- /dev/null +++ b/mysql-test/suite/perfschema/include/hostcache_dump.inc @@ -0,0 +1,39 @@ +# Helper for hostcache_*.test + +--echo # Dumping performance_schema.host_cache + +--disable_query_log +--vertical_results +select + IP, HOST, HOST_VALIDATED, SUM_CONNECT_ERRORS, + COUNT_HOST_BLOCKED_ERRORS, + COUNT_NAMEINFO_TRANSIENT_ERRORS, + COUNT_NAMEINFO_PERMANENT_ERRORS, + COUNT_FORMAT_ERRORS, + COUNT_ADDRINFO_TRANSIENT_ERRORS, + COUNT_ADDRINFO_PERMANENT_ERRORS, + COUNT_FCRDNS_ERRORS, + COUNT_HOST_ACL_ERRORS, + COUNT_NO_AUTH_PLUGIN_ERRORS, + COUNT_AUTH_PLUGIN_ERRORS, + COUNT_HANDSHAKE_ERRORS, + COUNT_PROXY_USER_ERRORS, + COUNT_PROXY_USER_ACL_ERRORS, + COUNT_AUTHENTICATION_ERRORS, + COUNT_SSL_ERRORS, + COUNT_MAX_USER_CONNECTIONS_ERRORS, + COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS, + COUNT_DEFAULT_DATABASE_ERRORS, + COUNT_INIT_CONNECT_ERRORS, + COUNT_LOCAL_ERRORS, + COUNT_UNKNOWN_ERRORS, + if (FIRST_ERROR_SEEN is not null, + if (FIRST_ERROR_SEEN > date("2012-01-01"), "set", "wrong epoch"), + "null") as FIRST_ERROR_SEEN, + if (LAST_ERROR_SEEN is not null, + if (FIRST_ERROR_SEEN > date("2012-01-01"), "set", "wrong epoch"), + "null") as LAST_ERROR_SEEN + from performance_schema.host_cache; +--horizontal_results +--enable_query_log + diff --git a/mysql-test/suite/perfschema/include/hostcache_set_state.inc b/mysql-test/suite/perfschema/include/hostcache_set_state.inc new file mode 100644 index 00000000..29f32123 --- /dev/null +++ b/mysql-test/suite/perfschema/include/hostcache_set_state.inc @@ -0,0 +1,22 @@ +# Helper for hostcache_*.test + +# Set a known initial state for the test + +flush status; +flush hosts; +flush user_resources; +flush privileges; + +# Print critical setup + +#select @@global.debug; +#select @@global.max_connect_errors; +#select @@global.max_user_connections; +#select @@global.max_connections; + +# Make sure there are no remaining records that can change the test outcome + +select `User`, `Host` from mysql.`user` where `host` like '%\\%%'; +select `User`, `Host` from mysql.`user` where `user` like '192.%'; +select `User`, `Host` from mysql.`user` where `user` like '2001:%'; +select `User`, `Host` from mysql.`user` where `user` like 'santa.claus.%'; diff --git a/mysql-test/suite/perfschema/include/memory_aggregate_cleanup.inc b/mysql-test/suite/perfschema/include/memory_aggregate_cleanup.inc new file mode 100644 index 00000000..1e2cfa99 --- /dev/null +++ b/mysql-test/suite/perfschema/include/memory_aggregate_cleanup.inc @@ -0,0 +1,45 @@ +# Tests for the performance schema + +# =========================================== +# HELPER include/memory_aggregate_cleanup.inc +# =========================================== + +--disable_query_log +revoke all privileges, grant option from user1@localhost; +revoke all privileges, grant option from user2@localhost; +revoke all privileges, grant option from user3@localhost; +revoke all privileges, grant option from user4@localhost; +drop user user1@localhost; +drop user user2@localhost; +drop user user3@localhost; +drop user user4@localhost; +flush privileges; + +drop procedure dump_thread; +drop procedure dump_one_thread; + +drop prepare dump_memory_account; +drop prepare dump_memory_user; +drop prepare dump_memory_host; +drop prepare dump_memory_global; + +drop prepare dump_users; +drop prepare dump_hosts; +drop prepare dump_accounts; + +truncate table performance_schema.accounts; +truncate table performance_schema.users; +truncate table performance_schema.hosts; + +truncate table performance_schema.setup_actors; +insert into performance_schema.setup_actors + select * from test.setup_actors; +drop table test.setup_actors; + +set global query_cache_size=0; + +update performance_schema.threads set instrumented='YES'; +update performance_schema.setup_instruments set enabled='YES', timed='YES'; + +--enable_query_log + diff --git a/mysql-test/suite/perfschema/include/memory_aggregate_load.inc b/mysql-test/suite/perfschema/include/memory_aggregate_load.inc new file mode 100644 index 00000000..7a54d252 --- /dev/null +++ b/mysql-test/suite/perfschema/include/memory_aggregate_load.inc @@ -0,0 +1,506 @@ +# Tests for the performance schema + +# ======================================== +# HELPER include/memory_aggregate_load.inc +# ======================================== + +echo "================== Step 1 =================="; +--vertical_results +call dump_thread(); +execute dump_memory_account; +execute dump_memory_user; +execute dump_memory_host; +execute dump_memory_global; +--horizontal_results +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +# Notes about this test +# +# get_lock("marker") causes the following memory allocation: +# memory/sql/User_level_lock, 1 malloc, size 16 (size 8 for 32-bit systems). +# release_lock("marker") causes the following memory free: +# memory/sql/User_level_lock, 1 free, size 16 (size 8 for 32-bit systems). +# +# To avoid noise from main, the background threads are disabled. + +connect (con1, localhost, user1, , ); + +echo "================== con1 connected =================="; + +--connection default + +# Wait for the connect to complete +let $wait_condition= + select count(*) = 1 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user1'; +--source include/wait_condition.inc + +echo "================== Step 2 =================="; +--vertical_results +call dump_thread(); +execute dump_memory_account; +execute dump_memory_user; +execute dump_memory_host; +execute dump_memory_global; +--horizontal_results +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +--connection con1 + +set GLOBAL query_cache_size=1000*1024; +select get_lock("marker_1", 10); +select release_lock("marker_1"); +set @v1 = repeat("a", 1000); +set @v2 = repeat("b", 2000); +set @v3 = repeat("c", 4000); + +echo "================== con1 marker =================="; + +--connection default + +# Wait for the payload to complete +let $wait_condition= + select count(*) = 1 from performance_schema.events_waits_current + where EVENT_NAME= 'idle'; +--source include/wait_condition.inc + +echo "================== Step 3 =================="; +--vertical_results +call dump_thread(); +execute dump_memory_account; +execute dump_memory_user; +execute dump_memory_host; +execute dump_memory_global; +--horizontal_results +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +# Debugging helpers +# select * from performance_schema.events_waits_history_long; +# select PROCESSLIST_USER, PROCESSLIST_HOST, INSTRUMENTED from performance_schema.threads; + +connect (con2, localhost, user2, , ); + +echo "================== con2 connected =================="; + +--connection default + +# Wait for the connect to complete +let $wait_condition= + select count(*) = 1 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user2'; +--source include/wait_condition.inc + +echo "================== Step 4 =================="; +--vertical_results +call dump_thread(); +execute dump_memory_account; +execute dump_memory_user; +execute dump_memory_host; +execute dump_memory_global; +--horizontal_results +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +--connection con2 + +set GLOBAL query_cache_size=2000*1024; +select get_lock("marker_2", 10); +select release_lock("marker_2"); +set @v1 = repeat("a", 1000); +set @v2 = repeat("b", 2000); +set @v3 = repeat("c", 4000); + +echo "================== con2 marker =================="; + +--connection default + +# Wait for the payload to complete +let $wait_condition= + select count(*) = 2 from performance_schema.events_waits_current + where EVENT_NAME= 'idle'; +--source include/wait_condition.inc + +echo "================== Step 5 =================="; +--vertical_results +call dump_thread(); +execute dump_memory_account; +execute dump_memory_user; +execute dump_memory_host; +execute dump_memory_global; +--horizontal_results +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate table performance_schema.memory_summary_by_thread_by_event_name; + +echo "================== MEMORY_BY_THREAD truncated =================="; + +echo "================== Step 5b =================="; +--vertical_results +call dump_thread(); +execute dump_memory_account; +execute dump_memory_user; +execute dump_memory_host; +execute dump_memory_global; +--horizontal_results +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +connect (con3, localhost, user3, , ); + +echo "================== con3 connected =================="; + +--connection default + +# Wait for the connect to complete +let $wait_condition= + select count(*) = 1 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user3'; +--source include/wait_condition.inc + +echo "================== Step 6 =================="; +--vertical_results +call dump_thread(); +execute dump_memory_account; +execute dump_memory_user; +execute dump_memory_host; +execute dump_memory_global; +--horizontal_results +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +--connection con3 + +set GLOBAL query_cache_size=500*1024; +select get_lock("marker_3", 10); +select release_lock("marker_3"); +set @v1 = repeat("a", 1000); +set @v2 = repeat("b", 2000); +set @v3 = repeat("c", 4000); + +echo "================== con3 marker =================="; + +--connection default + +# Wait for the payload to complete +let $wait_condition= + select count(*) = 3 from performance_schema.events_waits_current + where EVENT_NAME= 'idle'; +--source include/wait_condition.inc + +echo "================== Step 7 =================="; +--vertical_results +call dump_thread(); +execute dump_memory_account; +execute dump_memory_user; +execute dump_memory_host; +execute dump_memory_global; +--horizontal_results +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +connect (con4, localhost, user4, , ); +connect (con5, localhost, user4, , ); + +echo "================== con4/con5 (both user4) connected =================="; + +--connection default + +# Wait for the connects to complete +let $wait_condition= + select count(*) = 2 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user4'; +--source include/wait_condition.inc + +echo "================== Step 8 =================="; +--vertical_results +call dump_thread(); +execute dump_memory_account; +execute dump_memory_user; +execute dump_memory_host; +execute dump_memory_global; +--horizontal_results +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +--connection con4 + +set GLOBAL query_cache_size=4000*1024; +select get_lock("marker_4", 10); +select release_lock("marker_4"); +set @v1 = repeat("a", 1000); +set @v2 = repeat("b", 2000); +set @v3 = repeat("c", 4000); + +--connection con5 +select get_lock("marker_5", 10); +select release_lock("marker_5"); +set @v1 = repeat("a", 1000); +set @v2 = repeat("b", 2000); +set @v3 = repeat("c", 4000); + +echo "================== con4/con5 marker =================="; + +--connection default + +# Wait for the payload to complete +let $wait_condition= + select count(*) = 5 from performance_schema.events_waits_current + where EVENT_NAME= 'idle'; +--source include/wait_condition.inc + +echo "================== Step 9 =================="; +--vertical_results +call dump_thread(); +execute dump_memory_account; +execute dump_memory_user; +execute dump_memory_host; +execute dump_memory_global; +--horizontal_results +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +--disconnect con1 +--disconnect con5 + +--connection default + +# Wait for the disconnects to complete +let $wait_condition= + select count(*) = 0 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user1'; +--source include/wait_condition.inc +let $wait_condition= + select count(*) = 1 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user4'; +--source include/wait_condition.inc + +echo "================== con1/con5 disconnected =================="; + +echo "================== Step 10 =================="; +--vertical_results +call dump_thread(); +execute dump_memory_account; +execute dump_memory_user; +execute dump_memory_host; +execute dump_memory_global; +--horizontal_results +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +--disconnect con2 + +--connection default + +# Wait for the disconnect to complete +let $wait_condition= + select count(*) = 0 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user2'; +--source include/wait_condition.inc + +echo "================== con2 disconnected =================="; + +echo "================== Step 11 =================="; +--vertical_results +call dump_thread(); +execute dump_memory_account; +execute dump_memory_user; +execute dump_memory_host; +execute dump_memory_global; +--horizontal_results +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +--disconnect con3 + +--connection default + +# Wait for the disconnect to complete +let $wait_condition= + select count(*) = 0 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user3'; +--source include/wait_condition.inc + +echo "================== con3 disconnected =================="; + +echo "================== Step 12 =================="; +--vertical_results +call dump_thread(); +execute dump_memory_account; +execute dump_memory_user; +execute dump_memory_host; +execute dump_memory_global; +--horizontal_results +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +--disconnect con4 + +--connection default + +# Wait for the disconnect to complete +let $wait_condition= + select count(*) = 0 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user4'; +--source include/wait_condition.inc + +echo "================== con4 disconnected =================="; + +echo "================== Step 13 =================="; +--vertical_results +call dump_thread(); +execute dump_memory_account; +execute dump_memory_user; +execute dump_memory_host; +execute dump_memory_global; +--horizontal_results +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +--connection default + +truncate performance_schema.memory_summary_by_thread_by_event_name; + +echo "================== MEMORY_BY_THREAD truncated =================="; + +echo "================== Step 14 =================="; +--vertical_results +call dump_thread(); +execute dump_memory_account; +execute dump_memory_user; +execute dump_memory_host; +execute dump_memory_global; +--horizontal_results +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.memory_summary_by_account_by_event_name; + +echo "================== MEMORY_BY_ACCOUNT truncated =================="; + +echo "================== Step 15 =================="; +--vertical_results +call dump_thread(); +execute dump_memory_account; +execute dump_memory_user; +execute dump_memory_host; +execute dump_memory_global; +--horizontal_results +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.memory_summary_by_user_by_event_name; + +echo "================== MEMORY_BY_USER truncated =================="; + +echo "================== Step 16 =================="; +--vertical_results +call dump_thread(); +execute dump_memory_account; +execute dump_memory_user; +execute dump_memory_host; +execute dump_memory_global; +--horizontal_results +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.memory_summary_by_host_by_event_name; + +echo "================== MEMORY_BY_HOST truncated =================="; + +echo "================== Step 17 =================="; +--vertical_results +call dump_thread(); +execute dump_memory_account; +execute dump_memory_user; +execute dump_memory_host; +execute dump_memory_global; +--horizontal_results +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.memory_summary_global_by_event_name; + +echo "================== MEMORY_GLOBAL truncated =================="; + +echo "================== Step 18 =================="; +--vertical_results +call dump_thread(); +execute dump_memory_account; +execute dump_memory_user; +execute dump_memory_host; +execute dump_memory_global; +--horizontal_results +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.accounts; + +echo "================== ACCOUNTS truncated =================="; + +echo "================== Step 19 =================="; +--vertical_results +call dump_thread(); +execute dump_memory_account; +execute dump_memory_user; +execute dump_memory_host; +execute dump_memory_global; +--horizontal_results +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.users; + +echo "================== USERS truncated =================="; + +echo "================== Step 20 =================="; +--vertical_results +call dump_thread(); +execute dump_memory_account; +execute dump_memory_user; +execute dump_memory_host; +execute dump_memory_global; +--horizontal_results +execute dump_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.hosts; + +echo "================== HOSTS truncated =================="; + +echo "================== Step 21 =================="; +--vertical_results +call dump_thread(); +execute dump_memory_account; +execute dump_memory_user; +execute dump_memory_host; +execute dump_memory_global; +--horizontal_results +execute dump_accounts; +execute dump_users; +execute dump_hosts; + diff --git a/mysql-test/suite/perfschema/include/memory_aggregate_setup.inc b/mysql-test/suite/perfschema/include/memory_aggregate_setup.inc new file mode 100644 index 00000000..1272c092 --- /dev/null +++ b/mysql-test/suite/perfschema/include/memory_aggregate_setup.inc @@ -0,0 +1,208 @@ +# Tests for the performance schema + +# ============= +# DOCUMENTATION +# ============= + +# Verify how memory stats are aggregated into various tables +# +# In the thread dimension: +# - memory_summary_by_thread_by_event_name +# - memory_summary_by_account_by_event_name +# - memory_summary_by_user_by_event_name +# - memory_summary_by_host_by_event_name +# +# Globally: +# - memory_summary_global_by_event_name +# +# The tests are written with the following helpers: +# - include/memory_aggregate_setup.inc +# - include/memory_aggregate_load.inc +# - include/memory_aggregate_cleanup.inc +# +# Helpers are intended to be used as follows. +# +# A Typical test t/memory_aggregate_xxx.test will consist of: +# --source ../include/memory_aggregate_setup.inc +# --source ../include/memory_aggregate_load.inc +# --source ../include/memory_aggregate_cleanup.inc +# and a t/memory_aggregate_xxx-master.opt file +# +# Naming conventions for t/memory_aggregate_xxx.test are as follows: +# t/memory_aggregate_<account><user><host> +# +# <account> corresponds to different sizing settings for +# the variable performance-schema-accounts-size +# - (blank): accounts-size sufficient to represent all records +# - no_a: accounts-size set to 0 +# +# <user> corresponds to different sizing settings for +# the variable performance-schema-users-size +# - (blank): users-size sufficient to represent all records +# - no_u: users-size set to 0 +# +# <host> corresponds to different sizing settings for +# the variable performance-schema-hosts-size +# - (blank): hosts-size sufficient to represent all records +# - no_h: hosts-size set to 0 + +# ========================================= +# HELPER include/memory_aggregate_setup.inc +# ========================================= + +--source include/not_embedded.inc +--source include/have_perfschema.inc +--source include/no_protocol.inc +--source ../include/wait_for_pfs_thread_count.inc +--source include/not_windows.inc + +--disable_query_log + +set @orig_sql_mode= @@sql_mode; +set sql_mode= (select replace(@@sql_mode,'NO_AUTO_CREATE_USER','')); +grant ALL on *.* to user1@localhost; +grant ALL on *.* to user2@localhost; +grant ALL on *.* to user3@localhost; +grant ALL on *.* to user4@localhost; +set sql_mode= @orig_sql_mode; + +flush privileges; + +# Purge old users, hosts, user/host from previous tests +truncate table performance_schema.accounts; +truncate table performance_schema.users; +truncate table performance_schema.hosts; + +# Save the setup + +--disable_warnings +drop table if exists test.setup_actors; +--enable_warnings + +create table test.setup_actors as + select * from performance_schema.setup_actors; + +# Only instrument the user connections +truncate table performance_schema.setup_actors; +insert into performance_schema.setup_actors + set host= 'localhost', user= 'user1', role= '%'; +insert into performance_schema.setup_actors + set host= 'localhost', user= 'user2', role= '%'; +insert into performance_schema.setup_actors + set host= 'localhost', user= 'user3', role= '%'; +insert into performance_schema.setup_actors + set host= 'localhost', user= 'user4', role= '%'; + +update performance_schema.threads set instrumented='NO'; + +# Only instrument a few events of each kind +update performance_schema.setup_instruments set enabled='NO', timed='NO'; + +update performance_schema.setup_instruments set enabled='YES', timed='YES' + where name in ('memory/sql/user_var_entry::value', + 'memory/sql/User_level_lock', + 'memory/sql/Query_cache', + 'idle'); + +# Start from a known clean state, to avoid noise from previous tests +flush tables; +flush status; +truncate performance_schema.memory_summary_by_thread_by_event_name; +truncate performance_schema.memory_summary_by_account_by_event_name; +truncate performance_schema.memory_summary_by_user_by_event_name; +truncate performance_schema.memory_summary_by_host_by_event_name; +truncate performance_schema.memory_summary_global_by_event_name; + +--disable_warnings +drop procedure if exists dump_thread; +drop procedure if exists dump_one_thread; +--enable_warnings + +delimiter $$; + +create procedure dump_thread() +begin + call dump_one_thread('user1'); + call dump_one_thread('user2'); + call dump_one_thread('user3'); + call dump_one_thread('user4'); +end +$$ + +create procedure dump_one_thread(in username varchar(64)) +begin + declare my_thread_id int; + + set my_thread_id = (select thread_id from performance_schema.threads + where processlist_user=username LIMIT 1); + + if (my_thread_id is not null) then + select username, + EVENT_NAME, COUNT_ALLOC, COUNT_FREE, + SUM_NUMBER_OF_BYTES_ALLOC, SUM_NUMBER_OF_BYTES_FREE, + LOW_COUNT_USED, CURRENT_COUNT_USED, HIGH_COUNT_USED, + LOW_NUMBER_OF_BYTES_USED, CURRENT_NUMBER_OF_BYTES_USED, HIGH_NUMBER_OF_BYTES_USED + from performance_schema.memory_summary_by_thread_by_event_name + where event_name in ('memory/sql/user_var_entry::value', + 'memory/sql/User_level_lock', + 'memory/sql/Query_cache') + and thread_id = my_thread_id + order by event_name; + else + select username, "not found" as status; + end if; +end +$$ + +delimiter ;$$ + +prepare dump_memory_account from + "select * + from performance_schema.memory_summary_by_account_by_event_name + where user like \'user%\' + and event_name in ('memory/sql/user_var_entry::value', + 'memory/sql/User_level_lock', + 'memory/sql/Query_cache') + order by user, host, event_name;"; + +prepare dump_memory_user from + "select * + from performance_schema.memory_summary_by_user_by_event_name + where user like \'user%\' + and event_name in ('memory/sql/user_var_entry::value', + 'memory/sql/User_level_lock', + 'memory/sql/Query_cache') + order by user, event_name;"; + +prepare dump_memory_host from + "select * + from performance_schema.memory_summary_by_host_by_event_name + where host=\'localhost\' + and event_name in ('memory/sql/user_var_entry::value', + 'memory/sql/User_level_lock', + 'memory/sql/Query_cache') + order by host, event_name;"; + +prepare dump_memory_global from + "select * + from performance_schema.memory_summary_global_by_event_name + where event_name in ('memory/sql/user_var_entry::value', + 'memory/sql/User_level_lock', + 'memory/sql/Query_cache') + order by event_name;"; + +prepare dump_users from + "select * from performance_schema.users where user is not null order by user;"; + +prepare dump_hosts from + "select * from performance_schema.hosts where host is not null order by host;"; + +prepare dump_accounts from + "select * from performance_schema.accounts where (user is not null) and (host is not null) order by user, host;"; + +--enable_query_log + +# Make sure all the instrumentation is present +show global status like "performance_schema_memory_classes_lost"; + + diff --git a/mysql-test/suite/perfschema/include/pfs_no_running_event_scheduler.inc b/mysql-test/suite/perfschema/include/pfs_no_running_event_scheduler.inc new file mode 100644 index 00000000..eff3d7df --- /dev/null +++ b/mysql-test/suite/perfschema/include/pfs_no_running_event_scheduler.inc @@ -0,0 +1,10 @@ +# threads are removed from: +# - information_schema.processlist +# - performance_schema.threads +# at different times, so we may have to wait a little more +# for the event_scheduler to shutdown +# +let $wait_condition= + SELECT COUNT(*) = 0 FROM performance_schema.threads + WHERE name like 'thread/sql/event%'; +--source include/wait_condition.inc diff --git a/mysql-test/suite/perfschema/include/pfs_running_event_scheduler.inc b/mysql-test/suite/perfschema/include/pfs_running_event_scheduler.inc new file mode 100644 index 00000000..219a4105 --- /dev/null +++ b/mysql-test/suite/perfschema/include/pfs_running_event_scheduler.inc @@ -0,0 +1,10 @@ +# threads are removed from: +# - information_schema.processlist +# - performance_schema.threads +# at different times, so we may have to wait a little more +# for the event_scheduler to shutdown +# +let $wait_condition= + SELECT COUNT(*) = 1 FROM performance_schema.threads + WHERE name like 'thread/sql/event%'; +--source include/wait_condition.inc diff --git a/mysql-test/suite/perfschema/include/pfs_upgrade.inc b/mysql-test/suite/perfschema/include/pfs_upgrade.inc new file mode 100644 index 00000000..8d9dca0a --- /dev/null +++ b/mysql-test/suite/perfschema/include/pfs_upgrade.inc @@ -0,0 +1,112 @@ +# Tests for PERFORMANCE_SCHEMA +# Make sure mysql_upgrade does not destroy data in a 'performance_schema' +# database. +# + +# Some initial settings + Preemptive cleanup +let $MYSQLD_DATADIR= `SELECT @@datadir`; +let $err_file= $MYSQLTEST_VARDIR/tmp/pfs_upgrade.err; +let $out_file= $MYSQLTEST_VARDIR/tmp/pfs_upgrade.out; +--error 0,1 +--remove_file $out_file +--error 0,1 +--remove_file $err_file + +--disable_warnings +drop table if exists test.user_table; +drop procedure if exists test.user_proc; +drop function if exists test.user_func; +drop event if exists test.user_event; +--enable_warnings + + +--echo # Testing mysql_upgrade with TABLE performance_schema.user_table + +create table test.user_table(a int); + +--error 0,1 +--remove_file $MYSQLD_DATADIR/performance_schema/user_table.frm +--copy_file $MYSQLD_DATADIR/test/user_table.frm $MYSQLD_DATADIR/performance_schema/user_table.frm + +# Make sure the table is visible +use performance_schema; +show tables like "user_table"; + +--source suite/perfschema/include/upgrade_check.inc + +# Make sure the table is still visible +show tables like "user_table"; + +use test; + +--remove_file $MYSQLD_DATADIR/performance_schema/user_table.frm +drop table test.user_table; + + +--echo # Testing mysql_upgrade with VIEW performance_schema.user_view + +create view test.user_view as select "Not supposed to be here"; + +--error 0,1 +--remove_file $MYSQLD_DATADIR/performance_schema/user_view.frm +--copy_file $MYSQLD_DATADIR/test/user_view.frm $MYSQLD_DATADIR/performance_schema/user_view.frm + +# Make sure the view is visible +use performance_schema; +show tables like "user_view"; + +--source suite/perfschema/include/upgrade_check.inc + +# Make sure the view is still visible +show tables like "user_view"; + +use test; + +--remove_file $MYSQLD_DATADIR/performance_schema/user_view.frm +drop view test.user_view; + + +--echo # Testing mysql_upgrade with PROCEDURE performance_schema.user_proc + +create procedure test.user_proc() + select "Not supposed to be here"; + +update mysql.proc set db='performance_schema' where name='user_proc'; + +--source suite/perfschema/include/upgrade_check.inc + +select name from mysql.proc where db='performance_schema'; + +update mysql.proc set db='test' where name='user_proc'; +drop procedure test.user_proc; + + +--echo # Testing mysql_upgrade with FUNCTION performance_schema.user_func + +create function test.user_func() returns integer + return 0; + +update mysql.proc set db='performance_schema' where name='user_func'; + +--source suite/perfschema/include/upgrade_check.inc + +select name from mysql.proc where db='performance_schema'; + +update mysql.proc set db='test' where name='user_func'; +drop function test.user_func; + + +--echo # Testing mysql_upgrade with EVENT performance_schema.user_event + +create event test.user_event on schedule every 1 day do + select "not supposed to be here"; + +update mysql.event set db='performance_schema' where name='user_event'; + +--source suite/perfschema/include/upgrade_check.inc + +select name from mysql.event where db='performance_schema'; + +update mysql.event set db='test' where name='user_event'; +drop event test.user_event; + diff --git a/mysql-test/suite/perfschema/include/prepared_stmts_deallocation.inc b/mysql-test/suite/perfschema/include/prepared_stmts_deallocation.inc new file mode 100644 index 00000000..1601c027 --- /dev/null +++ b/mysql-test/suite/perfschema/include/prepared_stmts_deallocation.inc @@ -0,0 +1,11 @@ +# +# Deallocates all the prepared statements +# created in prepares_stmts_setup.inc +# + +DEALLOCATE PREPARE st1; +DEALLOCATE PREPARE st2; +DEALLOCATE PREPARE st3; +DEALLOCATE PREPARE st4; + +DROP TABLE t1; diff --git a/mysql-test/suite/perfschema/include/prepared_stmts_execution.inc b/mysql-test/suite/perfschema/include/prepared_stmts_execution.inc new file mode 100644 index 00000000..dbb0696f --- /dev/null +++ b/mysql-test/suite/perfschema/include/prepared_stmts_execution.inc @@ -0,0 +1,18 @@ +# +# Execution of all the prepared statements created in +# prepared_statements_setup.inc. +# + +SET @a = 3; +SET @b = 4; +EXECUTE st1 USING @a, @b; + +#SET @table = 't1'; +EXECUTE st2; + +SET @c=3; +EXECUTE st3 using @c; + +EXECUTE st4; + + diff --git a/mysql-test/suite/perfschema/include/prepared_stmts_setup.inc b/mysql-test/suite/perfschema/include/prepared_stmts_setup.inc new file mode 100644 index 00000000..0fb14a36 --- /dev/null +++ b/mysql-test/suite/perfschema/include/prepared_stmts_setup.inc @@ -0,0 +1,20 @@ +# +# Creating various prepared statements. +# HELPER include/prepared_stmts_setup.inc +# + +CREATE TABLE t1 (a INT NOT NULL); +INSERT INTO t1 VALUES (4), (8), (11), (32), (80); + +# Prepared statments + +PREPARE st1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'; + +SET @table = 't1'; +SET @s = CONCAT('SELECT * FROM ', @table); +PREPARE st2 FROM @s; + +PREPARE st3 FROM 'INSERT INTO t1 SELECT * FROM t1 WHERE a<=?'; + +PREPARE st4 FROM + '(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a'; diff --git a/mysql-test/suite/perfschema/include/privilege.inc b/mysql-test/suite/perfschema/include/privilege.inc new file mode 100644 index 00000000..f01511b6 --- /dev/null +++ b/mysql-test/suite/perfschema/include/privilege.inc @@ -0,0 +1,164 @@ +# Tests for PERFORMANCE_SCHEMA + +--disable_warnings +drop table if exists test.t1; +--enable_warnings + +## The result of show grants is not consistent across platforms ... +## show grants; + +## Not enforced yet: deny CREATE_ACL and DROP_ACL +## Waiting to remove .FRM files first +## +## --error ER_DBACCESS_DENIED_ERROR +## create table performance_schema.t1(a int); +## +## --error ER_DBACCESS_DENIED_ERROR +## drop table performance_schema.t1; +## +## --error ER_DBACCESS_DENIED_ERROR +## create table performance_schema.setup_instruments(a int); +## +## --error ER_DBACCESS_DENIED_ERROR +## create table performance_schema.events_waits_current(a int); +## +## --error ER_DBACCESS_DENIED_ERROR +## create table performance_schema.file_instances(a int); +## +## --error ER_DBACCESS_DENIED_ERROR +## drop table performance_schema.setup_instruments; +## +## --error ER_DBACCESS_DENIED_ERROR +## drop table performance_schema.events_waits_current; +## +## --error ER_DBACCESS_DENIED_ERROR +## drop table performance_schema.file_instances; + +--error ER_DBACCESS_DENIED_ERROR +rename table performance_schema.setup_instruments to test.t1; + +--error ER_DBACCESS_DENIED_ERROR +rename table performance_schema.events_waits_current to test.t1; + +--error ER_DBACCESS_DENIED_ERROR +rename table performance_schema.file_instances to test.t1; + +--error ER_DBACCESS_DENIED_ERROR +rename table performance_schema.setup_instruments to performance_schema.t1; + +--error ER_DBACCESS_DENIED_ERROR +rename table performance_schema.events_waits_current to performance_schema.t1; + +--error ER_DBACCESS_DENIED_ERROR +rename table performance_schema.file_instances to performance_schema.t1; + +--error ER_DBACCESS_DENIED_ERROR +rename table performance_schema.setup_instruments + to performance_schema.events_waits_current; + +--error ER_DBACCESS_DENIED_ERROR +rename table performance_schema.events_waits_current + to performance_schema.setup_instruments; + +--error ER_DBACCESS_DENIED_ERROR +create procedure performance_schema.my_proc() begin end; + +--error ER_DBACCESS_DENIED_ERROR +create function performance_schema.my_func() returns int return 0; + +--error ER_DBACCESS_DENIED_ERROR +create event performance_schema.my_event on schedule every 15 minute +do begin end; + +--error ER_DBACCESS_DENIED_ERROR +create trigger performance_schema.bi_setup_instruments + before insert on performance_schema.setup_instruments + for each row begin end; + +--error ER_DBACCESS_DENIED_ERROR +create trigger performance_schema.bi_events_waits_current + before insert on performance_schema.events_waits_current + for each row begin end; + +--error ER_DBACCESS_DENIED_ERROR +create trigger performance_schema.bi_file_instances + before insert on performance_schema.file_instances + for each row begin end; + +--error ER_CANT_CREATE_TABLE +create table test.t1(a int) engine=PERFORMANCE_SCHEMA; + +--error ER_CANT_CREATE_TABLE +create table test.t1 like performance_schema.setup_instruments; + +--error ER_CANT_CREATE_TABLE +create table test.t1 like performance_schema.events_waits_current; + +--error ER_CANT_CREATE_TABLE +create table test.t1 like performance_schema.file_instances; + +--error ER_TABLEACCESS_DENIED_ERROR +insert into performance_schema.setup_instruments + set name="foo"; + +--error ER_TABLEACCESS_DENIED_ERROR +insert into performance_schema.events_waits_current + set name="foo"; + +--error ER_TABLEACCESS_DENIED_ERROR +insert into performance_schema.file_instances + set name="foo"; + +--error ER_TABLEACCESS_DENIED_ERROR +delete from performance_schema.setup_instruments; + +--error ER_TABLEACCESS_DENIED_ERROR +delete from performance_schema.events_waits_current; + +--error ER_TABLEACCESS_DENIED_ERROR +delete from performance_schema.file_instances; + +lock table performance_schema.setup_instruments read; +unlock tables; + +lock table performance_schema.setup_instruments write; +unlock tables; + +--error ER_TABLEACCESS_DENIED_ERROR +lock table performance_schema.events_waits_current read; +unlock tables; + +--error ER_TABLEACCESS_DENIED_ERROR +lock table performance_schema.events_waits_current write; +unlock tables; + +--error ER_TABLEACCESS_DENIED_ERROR +lock table performance_schema.file_instances read; +unlock tables; + +--error ER_TABLEACCESS_DENIED_ERROR +lock table performance_schema.file_instances write; +unlock tables; + +--echo # +--echo # WL#4818, NFS2: Can use grants to give normal user access +--echo # to view data from _current and _history tables +--echo # +--echo # Should work as pfs_user_1 and pfs_user_2, but not as pfs_user_3. +--echo # (Except for events_waits_current, which is granted.) + +# Errors here will be caught by the diff afterwards +--disable_abort_on_error + +SELECT "can select" FROM performance_schema.events_waits_history LIMIT 1; + +SELECT "can select" FROM performance_schema.events_waits_history_long LIMIT 1; + +SELECT "can select" FROM performance_schema.events_waits_current LIMIT 1; + +SELECT "can select" FROM performance_schema.events_waits_summary_by_instance LIMIT 1; + +SELECT "can select" FROM performance_schema.file_summary_by_instance LIMIT 1; + +--enable_abort_on_error + diff --git a/mysql-test/suite/perfschema/include/processlist_load.inc b/mysql-test/suite/perfschema/include/processlist_load.inc new file mode 100644 index 00000000..5a14ea17 --- /dev/null +++ b/mysql-test/suite/perfschema/include/processlist_load.inc @@ -0,0 +1,39 @@ +# ../include/processlist_load.inc +# +# SUMMARY +# +# Execute PROCESSLIST queries, sorted by user +# +# USAGE +# +# Example: Using processlist_set.inc to set @@global.performance_schema_show_processlist +# +# let $pfs_spl = on/off +# --source ../include/processlist_set.inc +# --source ../include/processlist_load.inc +# +# Columns +# 1 <Id> 2 <User> 3 <Host> 4 <db> 5 <Command> 6 <Time> 7 <State> 8 <Info> + +# Sort SHOW PROCESSLIST by User instead of Id because Id is not zero-padded +# Unique usernames give best results +--echo +--replace_column 1 <Id> 3 <Host> 6 <Time> 7 <State> +--replace_regex /Daemon/<Command>/ /Connect/<Command>/ /Sleep/<Command>/ +--sorted_result +SHOW FULL PROCESSLIST; + +--echo +--echo # Performance Schema processlist table +--echo +--replace_column 1 <Id> 3 <Host> 6 <Time> 7 <State> +--replace_regex /Daemon/<Command>/ /Connect/<Command>/ /Sleep/<Command>/ +select * from performance_schema.processlist order by user, id; + +--echo +--echo # Information Schema processlist table +--echo +--replace_column 1 <Id> 3 <Host> 6 <Time> 7 <State> +--replace_regex /Daemon/<Command>/ /Connect/<Command>/ /Sleep/<Command>/ +select * from information_schema.processlist order by user, id; + diff --git a/mysql-test/suite/perfschema/include/processlist_set.inc b/mysql-test/suite/perfschema/include/processlist_set.inc new file mode 100644 index 00000000..a15e09fd --- /dev/null +++ b/mysql-test/suite/perfschema/include/processlist_set.inc @@ -0,0 +1,17 @@ +# ../include/processlist_set.inc +# +# SUMMARY +# +# Set the value of performance_schema_show_proceslist then +# wait for the operation to complete +# +# USAGE +# +# let $pfs_spl = on; +# --source ../include/processlist_set.inc + +eval set @@global.performance_schema_show_processlist = $pfs_spl; + +let $wait_condition = show variables where variable_name like '%show_processlist%' and value = '$pfs_spl'; +--source include/wait_condition.inc + diff --git a/mysql-test/suite/perfschema/include/program_cleanup.inc b/mysql-test/suite/perfschema/include/program_cleanup.inc new file mode 100644 index 00000000..ffcedccd --- /dev/null +++ b/mysql-test/suite/perfschema/include/program_cleanup.inc @@ -0,0 +1,31 @@ +# +# clean up of set-up created in +# suite/perfschema/include/program_setup.inc +# + +--disable_warnings +DROP PROCEDURE SampleProc1; +DROP PROCEDURE SampleProc2; +DROP PROCEDURE SampleProc3; +DROP PROCEDURE SampleProc4; + +DROP FUNCTION wt_avg; +DROP FUNCTION fac; +DROP FUNCTION append; + +DROP TRIGGER trg1; +DROP TRIGGER trg2; +DROP TRIGGER trg3; +DROP TRIGGER trg4; +DROP TRIGGER trg5; + +DROP EVENT IF EXISTS e1; + +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP TABLE t4; +DROP TABLE table_t; + +DROP DATABASE stored_programs; +--enable_warnings diff --git a/mysql-test/suite/perfschema/include/program_execution.inc b/mysql-test/suite/perfschema/include/program_execution.inc new file mode 100644 index 00000000..8c0bc691 --- /dev/null +++ b/mysql-test/suite/perfschema/include/program_execution.inc @@ -0,0 +1,54 @@ +# +# Execute the stored programs created in +# suite/perfschema/include/program_setup.inc +# + +--echo ##################### +--echo # Executing queries # +--echo ##################### +INSERT INTO t1 VALUES (10,20); +CALL SampleProc1(30,40,50); +SET @a=1; +SELECT @a; +CALL SampleProc2("Jwalamukhi",34); +SELECT @a; +CALL SampleProc3(); +CALL SampleProc4(); +SET @change=1; +SELECT @change; +UPDATE t2 SET id=22 WHERE name="Jwalamukhi"; +SELECT @change; +SET @del=1; +SELECT @del; +DELETE FROM t1 WHERE i=76; +SELECT @del; +SELECT wt_avg(1, 12, 1990, 1121990); +SELECT fac(5); +SELECT append("Bolly", "wood"); + +--echo # Event + +SET GLOBAL event_scheduler=ON; + +CREATE TABLE table_t(a INT); +DELIMITER |; +CREATE EVENT e1 ON SCHEDULE EVERY 2 SECOND DO +BEGIN + INSERT INTO table_t VALUES(1); +END| +DELIMITER ;| + +# Let e1 insert 1 record into the table table_t + +--let $wait_condition= select count(*) = 1 from table_t +--source include/wait_condition.inc +SELECT * FROM table_t; + +# Wait till the above one execution of event is instrumented. + +--let $wait_condition= select count(*) = 1 from performance_schema.events_statements_history_long where object_type='EVENT' +--source include/wait_condition.inc + +SET GLOBAL event_scheduler=OFF; +--source include/no_running_event_scheduler.inc + diff --git a/mysql-test/suite/perfschema/include/program_nested_cleanup.inc b/mysql-test/suite/perfschema/include/program_nested_cleanup.inc new file mode 100644 index 00000000..0f5b26f3 --- /dev/null +++ b/mysql-test/suite/perfschema/include/program_nested_cleanup.inc @@ -0,0 +1,21 @@ +# +# clean up if set-up created in +# suite/perfschema/include/program_nested_setup.inc +# + +DROP PROCEDURE c4; +DROP PROCEDURE c3; +DROP PROCEDURE c2; +DROP PROCEDURE c1; +DROP PROCEDURE inc; +DROP PROCEDURE inc2; +DROP PROCEDURE iotest; +DROP FUNCTION mul; +DROP FUNCTION inc; +DROP FUNCTION fac; +DROP FUNCTION fun; +DROP PROCEDURE ifac; +DROP TRIGGER trg; +DROP TABLE t1,t2; + +DROP DATABASE nested_sp; diff --git a/mysql-test/suite/perfschema/include/program_nested_execution.inc b/mysql-test/suite/perfschema/include/program_nested_execution.inc new file mode 100644 index 00000000..dbf0eafe --- /dev/null +++ b/mysql-test/suite/perfschema/include/program_nested_execution.inc @@ -0,0 +1,22 @@ +# +# Execute the nested stored programs created in +# suite/include/perfschema/program_nested_setup.inc +# + +--echo ##################### +--echo # Executing queries # +--echo ##################### + +CALL c1(42); +SELECT * FROM t1; +DELETE FROM t1; + +CALL iotest("io1", "io2", 1); +SELECT * FROM t1 ORDER BY data DESC; +DELETE FROM t1; + +SELECT fun(6,10); + +INSERT INTO t1 VALUES (20,13); + +SELECT * FROM t2; diff --git a/mysql-test/suite/perfschema/include/program_nested_setup.inc b/mysql-test/suite/perfschema/include/program_nested_setup.inc new file mode 100644 index 00000000..bbf88598 --- /dev/null +++ b/mysql-test/suite/perfschema/include/program_nested_setup.inc @@ -0,0 +1,87 @@ +# +# SET-UP - Creation of various nested stored programs +# + +--source include/no_protocol.inc + +--echo # SET-UP +CREATE DATABASE nested_sp; +USE nested_sp; + +CREATE TABLE t1( + id CHAR(16) NOT NULL DEFAULT '', + data INT NOT NULL +); + +CREATE TABLE t2( + n INT UNSIGNED NOT NULL, + f BIGINT UNSIGNED +); + +--echo ############################ +--echo # Creating Stored Programs # +--echo ############################ +DELIMITER |; + +# Nested Stored Procedure - 1 +CREATE PROCEDURE c1(x INT) + CALL c2("c", x)| +CREATE PROCEDURE c2(s CHAR(16), x INT) + CALL c3(x, s)| +CREATE PROCEDURE c3(x INT, s CHAR(16)) + CALL c4("level", x, s)| +CREATE PROCEDURE c4(l CHAR(8), x INT, s CHAR(16)) + INSERT INTO t1 VALUES (concat(l,s), x)| + +# Nested Stored Procedure - 2 +CREATE PROCEDURE iotest(x1 CHAR(16), x2 CHAR(16), y INT) +BEGIN + CALL inc2(x2, y); + INSERT INTO t1 VALUES (x1, y); +END| +CREATE PROCEDURE inc2(x CHAR(16), y INT) +BEGIN + CALL inc(y); + INSERT INTO t1 VALUES (x, y); +END| +CREATE PROCEDURE inc(inout io INT) + SET io = io + 1| + +# Nested Stored Function +CREATE FUNCTION mul(x INT, y INT) RETURNS INT + RETURN x*y| +CREATE FUNCTION inc(i INT) RETURNS INT + RETURN i+1| +CREATE FUNCTION fac(n INT UNSIGNED) RETURNS BIGINT UNSIGNED +BEGIN + DECLARE f BIGINT UNSIGNED DEFAULT 1; + WHILE n > 1 DO + SET f = f * n; + SET n = n - 1; + END WHILE; + RETURN f; +END| +CREATE FUNCTION fun(i INT, u INT UNSIGNED) RETURNS DOUBLE + RETURN mul(inc(i), fac(u))| +# +# A nested stored program +# +CREATE PROCEDURE ifac(n INT UNSIGNED) +BEGIN + DECLARE i BIGINT UNSIGNED DEFAULT 1; + + IF n > 20 THEN + SET n = 20; # bigint overflow otherwise + END IF; + WHILE i <= n DO + BEGIN + INSERT INTO t2 VALUES (i, fac(i)); + SET i = i + 1; + END; + END WHILE; +END| +CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW + CALL ifac(10)| + +DELIMITER ;| + diff --git a/mysql-test/suite/perfschema/include/program_setup.inc b/mysql-test/suite/perfschema/include/program_setup.inc new file mode 100644 index 00000000..a4e7f245 --- /dev/null +++ b/mysql-test/suite/perfschema/include/program_setup.inc @@ -0,0 +1,136 @@ +# +# SET-UP - Creation of various non-nested stored programs +# + +--source include/no_protocol.inc +--source include/have_innodb.inc + +--echo # SET-UP + +CREATE DATABASE stored_programs; +USE stored_programs; + +CREATE TABLE t1( + i INT NOT NULL, + j INT +) engine=innodb; + +CREATE TABLE t2( + name CHAR(16) NOT NULL DEFAULT '', + id INT NOT NULL +) engine=innodb; + +CREATE TABLE t3( + d DATE, + n INT, + f DOUBLE, + s VARCHAR(32) +); + +CREATE TABLE t4( + `k` int(10) unsigned NOT NULL AUTO_INCREMENT, + `word` varchar(100) NOT NULL, + `mean` varchar(300) NOT NULL, + PRIMARY KEY (`k`) +); + + +--echo ############################ +--echo # Creating Stored Programs # +--echo ############################ + +--echo # Stored Routine ( Procedure & Function ) + +DELIMITER |; +CREATE PROCEDURE SampleProc1(x1 INT, x2 INT, y INT) +BEGIN + INSERT INTO t1 VALUES (x1, y); + INSERT INTO t1 VALUES (x2, y); +END| + +CREATE PROCEDURE SampleProc2(x CHAR(16), y INT) +BEGIN + DECLARE z1, z2 INT; + SET z1 = y; + SET z2 = z1+2; + INSERT INTO t2 VALUES (x, z2); +END| + +CREATE PROCEDURE SampleProc3() +BEGIN + DECLARE ld DATE; + DECLARE li INT; + DECLARE lf DOUBLE; + DECLARE ls VARCHAR(32); + + SET ld = NULL, li = NULL, lf = NULL, ls = NULL; + INSERT INTO t3 VALUES (ld, li, lf, ls); + + INSERT INTO t3 (n, f, s) VALUES ((ld IS NULL), 1, "ld is null"), + ((li IS NULL), 1, "li is null"), + ((li = 0), NULL, "li = 0"), + ((lf IS NULL), 1, "lf is null"), + ((lf = 0), NULL, "lf = 0"), + ((ls IS NULL), 1, "ls is null"); +END| + + +CREATE PROCEDURE SampleProc4() +BEGIN + DECLARE x INT; + SET x = 1; + WHILE x <= 2 DO + INSERT INTO t4(word, mean) VALUES('a','a mean'); + SET x = x + 1; + END WHILE; +END| + +CREATE FUNCTION append(s1 CHAR(8), s2 CHAR(8)) RETURNS CHAR(16) + RETURN concat(s1, s2)| + +CREATE FUNCTION wt_avg(n1 INT, n2 INT, n3 INT, n4 INT) + RETURNS INT + DETERMINISTIC + BEGIN + DECLARE avg INT; + SET avg = (n1+n2+n3*2+n4*4)/8; + RETURN avg; + END| + +CREATE FUNCTION fac(n INT UNSIGNED) RETURNS BIGINT UNSIGNED +BEGIN + DECLARE f BIGINT UNSIGNED DEFAULT 1; + WHILE n > 1 DO + SET f = f * n; + SET n = n - 1; + END WHILE; + RETURN f; +END| + +--echo # Triggers + +# INSERT triggers +CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW +BEGIN + if isnull(new.j) then + SET new.j:= new.i * 10; + END if; +END| + +CREATE TRIGGER trg2 AFTER INSERT ON t2 FOR EACH ROW +BEGIN + UPDATE t1 SET i=new.id+i ; +END| + +# UPDATE trigger +CREATE TRIGGER trg3 AFTER UPDATE ON t2 FOR EACH ROW + SET @change:= @change + new.id - old.id| + +# DELETE triggers +CREATE TRIGGER trg4 BEFORE DELETE ON t1 FOR EACH ROW + SET @del:= @del + 1| + +CREATE TRIGGER trg5 AFTER DELETE ON t1 FOR EACH ROW + SET @del:= @del + 8 + old.j| + +DELIMITER ;| diff --git a/mysql-test/suite/perfschema/include/reset_transaction_gtid.inc b/mysql-test/suite/perfschema/include/reset_transaction_gtid.inc new file mode 100644 index 00000000..c8eaf53a --- /dev/null +++ b/mysql-test/suite/perfschema/include/reset_transaction_gtid.inc @@ -0,0 +1,20 @@ +# ==== Purpose ==== +# +# Auxiliary file used by transaction_gtid.test +# +# Invoked between transactions in order to reset the state: +# - set GTID_NEXT to AUTOMATIC since this is required after +# any transaction that has GTID_NEXT=UUID:NUMBER +# - RESET MASTER in order to clear @@global.gtid_executed, so +# that the same GTID can be executed again. +# - truncate the performance_schema.events_transaction_* tables +# +# All this is done on the connection 'server_1'. + +--disable_query_log +--connection server_1 +RESET MASTER; +TRUNCATE TABLE performance_schema.events_transactions_history; +TRUNCATE TABLE performance_schema.events_transactions_current; +--enable_query_log +--connection default diff --git a/mysql-test/suite/perfschema/include/rpl_statements_truncate.inc b/mysql-test/suite/perfschema/include/rpl_statements_truncate.inc new file mode 100644 index 00000000..afd1d31a --- /dev/null +++ b/mysql-test/suite/perfschema/include/rpl_statements_truncate.inc @@ -0,0 +1,15 @@ +# Tests for the performance schema + +# ========================================== +# HELPER include/rpl_truncate_statements.inc +# ========================================== + +--disable_query_log +update performance_schema.setup_instruments set enabled='no', timed='no' where name like '%statement/%'; +truncate table performance_schema.events_waits_history_long; +truncate table performance_schema.events_statements_summary_by_digest; +truncate table performance_schema.events_statements_history; +truncate table performance_schema.events_statements_history_long; +update performance_schema.setup_instruments set enabled='yes', timed='yes' where name like '%statement/%'; +--enable_query_log + diff --git a/mysql-test/suite/perfschema/include/schema.inc b/mysql-test/suite/perfschema/include/schema.inc new file mode 100644 index 00000000..8a8be180 --- /dev/null +++ b/mysql-test/suite/perfschema/include/schema.inc @@ -0,0 +1,76 @@ +# Tests for PERFORMANCE_SCHEMA +# Show existing objects and information about their structure + +show databases like 'performance_schema'; +show create database performance_schema; + +use performance_schema; + +show tables; + +show create table accounts; +show create table cond_instances; +show create table events_stages_current; +show create table events_stages_history; +show create table events_stages_history_long; +show create table events_stages_summary_by_host_by_event_name; +show create table events_stages_summary_by_thread_by_event_name; +show create table events_stages_summary_by_user_by_event_name; +show create table events_stages_summary_by_account_by_event_name; +show create table events_stages_summary_global_by_event_name; +show create table events_statements_current; +show create table events_statements_history; +show create table events_statements_history_long; +show create table events_statements_summary_by_digest; +show create table events_statements_summary_by_host_by_event_name; +show create table events_statements_summary_by_thread_by_event_name; +show create table events_statements_summary_by_user_by_event_name; +show create table events_statements_summary_by_account_by_event_name; +show create table events_statements_summary_global_by_event_name; +show create table events_transactions_current; +show create table events_transactions_history; +show create table events_transactions_history_long; +show create table events_transactions_summary_by_host_by_event_name; +show create table events_transactions_summary_by_thread_by_event_name; +show create table events_transactions_summary_by_user_by_event_name; +show create table events_transactions_summary_by_account_by_event_name; +show create table events_transactions_summary_global_by_event_name; +show create table events_waits_current; +show create table events_waits_history; +show create table events_waits_history_long; +show create table events_waits_summary_by_host_by_event_name; +show create table events_waits_summary_by_instance; +show create table events_waits_summary_by_thread_by_event_name; +show create table events_waits_summary_by_user_by_event_name; +show create table events_waits_summary_by_account_by_event_name; +show create table events_waits_summary_global_by_event_name; +show create table memory_summary_by_host_by_event_name; +show create table memory_summary_by_thread_by_event_name; +show create table memory_summary_by_user_by_event_name; +show create table memory_summary_by_account_by_event_name; +show create table memory_summary_global_by_event_name; +show create table metadata_locks; +show create table file_instances; +show create table file_summary_by_event_name; +show create table file_summary_by_instance; +show create table host_cache; +show create table hosts; +show create table mutex_instances; +show create table objects_summary_global_by_type; +show create table performance_timers; +show create table rwlock_instances; +show create table setup_actors; +show create table setup_consumers; +show create table setup_instruments; +show create table setup_objects; +show create table setup_timers; +show create table socket_instances; +show create table socket_summary_by_instance; +show create table socket_summary_by_event_name; +show create table table_handles; +show create table table_io_waits_summary_by_index_usage; +show create table table_io_waits_summary_by_table; +show create table table_lock_waits_summary_by_table; +show create table threads; +show create table users; + diff --git a/mysql-test/suite/perfschema/include/setup_helper.inc b/mysql-test/suite/perfschema/include/setup_helper.inc new file mode 100644 index 00000000..1a375916 --- /dev/null +++ b/mysql-test/suite/perfschema/include/setup_helper.inc @@ -0,0 +1,39 @@ +# Tests for PERFORMANCE_SCHEMA + +let $MYSQLD_DATADIR= `select @@datadir`; +let $MYSQLD_TMPDIR= `select @@tmpdir`; + +--disable_query_log +update performance_schema.setup_instruments set enabled='NO'; +update performance_schema.setup_consumers set enabled='YES'; +--enable_query_log + +connect (con1, localhost, root, , ); + +let $con1_THREAD_ID=`select thread_id from performance_schema.threads + where PROCESSLIST_ID = connection_id()`; + +connect (con2, localhost, root, , ); + +let $con2_THREAD_ID=`select thread_id from performance_schema.threads + where PROCESSLIST_ID = connection_id()`; + +connect (con3, localhost, root, , ); + +let $con3_THREAD_ID=`select thread_id from performance_schema.threads + where PROCESSLIST_ID = connection_id()`; + +connection default; + +--disable_query_log +prepare stmt_dump_events from + "select event_name, + left(source, locate(\":\", source)) as short_source, + operation, number_of_bytes + from performance_schema.events_waits_history_long + where thread_id=? order by event_id;"; + +prepare stmt_dump_thread from + "select name from performance_schema.threads where thread_id=? ;"; +--enable_query_log + diff --git a/mysql-test/suite/perfschema/include/show_aggregate.inc b/mysql-test/suite/perfschema/include/show_aggregate.inc new file mode 100644 index 00000000..4489cf7a --- /dev/null +++ b/mysql-test/suite/perfschema/include/show_aggregate.inc @@ -0,0 +1,125 @@ +############### suite/perfschema/include/show_aggregate.inc #################### +# # +# Gather status by thread, by user, by host, by account and global. # +# # +################################################################################ + +--echo #================= +--echo # Global results +--echo #================= +USE test; +UPDATE test.status_results sr, performance_schema.global_status sg + SET sr.stop = sg.variable_value + WHERE sr.variable_name = sg.variable_name + AND sg.variable_name IN ('handler_delete', 'handler_rollback'); +--echo # +--echo # Global deltas: END - START. +UPDATE test.status_results sr + SET sr.delta = sr.stop - sr.start; + +#--echo DEBUG +#SELECT * FROM test.status_results; +#SELECT * from performance_schema.global_status where variable_name in ('handler_delete', 'handler_rollback'); + +--echo #================= +--echo # Status by thread +--echo #================= +--echo # Thread results from CON1. +UPDATE test.status_results sr, performance_schema.status_by_thread sbt + SET sr.t1 = sbt.variable_value + WHERE sr.variable_name = sbt.variable_name + AND sbt.variable_name IN ('handler_delete', 'handler_rollback') + AND sbt.thread_id = @con1_id; +--echo # +--echo # Thread results from CON2. +UPDATE test.status_results sr, performance_schema.status_by_thread sbt + SET sr.t2 = sbt.variable_value + WHERE sr.variable_name = sbt.variable_name + AND sbt.variable_name IN ('handler_delete', 'handler_rollback') + AND sbt.thread_id = @con2_id; +--echo # +--echo # Thread results from CON3. +UPDATE test.status_results sr, performance_schema.status_by_thread sbt + SET sr.t3 = sbt.variable_value + WHERE sr.variable_name = sbt.variable_name + AND sbt.variable_name IN ('handler_delete', 'handler_rollback') + AND sbt.thread_id = @con3_id; +--echo # +--echo # Thread totals for 3 connections. +UPDATE test.status_results sr + SET sr.thread = sr.t1 + sr.t2 + sr.t3; + +--echo #================= +--echo # Status by user +--echo #================= +--echo # User1 +UPDATE test.status_results sr, performance_schema.status_by_user sbu + SET sr.u1 = sbu.variable_value + WHERE sr.variable_name = sbu.variable_name + AND sbu.variable_name IN ('handler_delete', 'handler_rollback') + AND sbu.user IN ('user1'); +--echo # +--echo # User2 +UPDATE test.status_results sr, performance_schema.status_by_user sbu + SET sr.u2 = sbu.variable_value + WHERE sr.variable_name = sbu.variable_name + AND sbu.variable_name IN ('handler_delete', 'handler_rollback') + AND sbu.user IN ('user2'); +--echo # +--echo # User3 +UPDATE test.status_results sr, performance_schema.status_by_user sbu + SET sr.u3 = sbu.variable_value + WHERE sr.variable_name = sbu.variable_name + AND sbu.variable_name IN ('handler_delete', 'handler_rollback') + AND sbu.user IN ('user3'); +--echo # +--echo # Status totals for 3 users. +UPDATE test.status_results sr + SET sr.user = sr.u1 + sr.u2 + sr.u3; + +--echo #=========================== +--echo # Status by host (localhost) +--echo #=========================== +--echo # +--echo # host1 = localhost +UPDATE test.status_results sr, performance_schema.status_by_host sbh + SET sr.h1 = sbh.variable_value + WHERE sr.variable_name = sbh.variable_name + AND sbh.variable_name IN ('handler_delete', 'handler_rollback') + AND sbh.host IN ('localhost'); +--echo # +--echo # Status totals for 'localhost' only. +UPDATE test.status_results sr + SET sr.host = sr.h1 + sr.h2 + sr.h3; + +--echo #================== +--echo # Status by account +--echo #================== +--echo # User1@localhost +UPDATE test.status_results sr, performance_schema.status_by_account sba + SET sr.a1 = sba.variable_value + WHERE sr.variable_name = sba.variable_name + AND sba.variable_name IN ('handler_delete', 'handler_rollback') + AND sba.user IN ('user1'); +--echo # +--echo # User2@localhost +UPDATE test.status_results sr, performance_schema.status_by_account sba + SET sr.a2 = sba.variable_value + WHERE sr.variable_name = sba.variable_name + AND sba.variable_name IN ('handler_delete', 'handler_rollback') + AND sba.user IN ('user2'); +--echo # +--echo # User3@localhost +UPDATE test.status_results sr, performance_schema.status_by_account sba + SET sr.a3 = sba.variable_value + WHERE sr.variable_name = sba.variable_name + AND sba.variable_name IN ('handler_delete', 'handler_rollback') + AND sba.user IN ('user3'); +--echo # +--echo # +--echo # Status totals for 3 accounts. +UPDATE test.status_results sr + SET sr.acct = sr.a1 + sr.a2 + sr.a3; + +#--echo DEBUG +#SELECT * FROM test.status_results; diff --git a/mysql-test/suite/perfschema/include/show_plugin_verifier.inc b/mysql-test/suite/perfschema/include/show_plugin_verifier.inc new file mode 100644 index 00000000..873205ad --- /dev/null +++ b/mysql-test/suite/perfschema/include/show_plugin_verifier.inc @@ -0,0 +1,29 @@ +--disable_warnings + +--echo # +SHOW GLOBAL STATUS LIKE "example_%"; +--echo # +SHOW SESSION STATUS LIKE "example_%"; +--echo # +SHOW GLOBAL VARIABLES LIKE "example_%"; +--echo # +SHOW SESSION VARIABLES LIKE "example_%"; +--echo # +SELECT variable_name, variable_value FROM performance_schema.global_status WHERE variable_name LIKE "example_%"; +--echo # +SELECT variable_name, variable_value FROM performance_schema.session_status WHERE variable_name LIKE "example_%"; +--echo # +SELECT variable_name, variable_value FROM performance_schema.global_variables WHERE variable_name LIKE "example_%"; +--echo # +SELECT variable_name, variable_value FROM performance_schema.session_variables WHERE variable_name LIKE "example_%"; + +# +# Force sync of local and global system variables. +# +--disable_result_log +--disable_query_log +SELECT variable_name, variable_value FROM performance_schema.variables_by_thread WHERE variable_name LIKE "example_%"; +--enable_query_log +--enable_result_log + +--enable_warnings diff --git a/mysql-test/suite/perfschema/include/show_transaction_gtid.inc b/mysql-test/suite/perfschema/include/show_transaction_gtid.inc new file mode 100644 index 00000000..3ad4564d --- /dev/null +++ b/mysql-test/suite/perfschema/include/show_transaction_gtid.inc @@ -0,0 +1,29 @@ +# ==== Purpose ==== +# +# Auxiliary file used by transaction_gtid.test +# +# Invoked to check the contents of the +# performance_schema.events_transaction_[current|history] tables and +# write the result to the result log. This is executed on the +# 'server_1' connection and shows only status of transactions on the +# 'default' connection. + +--connection server_1 + +--replace_result $server_uuid SERVER_UUID aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa A +--let $history_result= `SELECT GTID FROM performance_schema.events_transactions_history WHERE THREAD_ID = $thread_id` +if ($history_result == '') +{ + --let $history_result= none +} + +--replace_result $server_uuid SERVER_UUID aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa A +--let $current_result= `SELECT GTID FROM performance_schema.events_transactions_current WHERE THREAD_ID = $thread_id` +if ($current_result == '') +{ + --let $current_result= none +} + +--echo # - history=$history_result current=$current_result + +--connection default diff --git a/mysql-test/suite/perfschema/include/socket_check1.inc b/mysql-test/suite/perfschema/include/socket_check1.inc new file mode 100644 index 00000000..4f65e7d0 --- /dev/null +++ b/mysql-test/suite/perfschema/include/socket_check1.inc @@ -0,0 +1,129 @@ +# Example how to use this auxiliary script +#----------------------------------------- +# +### The table/tables used in $part must have the right content. +### $title_prefix is used for the generation of titles +# +# let $title_prefix= 4.3; +### $check_num is used for the generation of titles and gets incremented after +### every call of the current script. +# let $check_num= 1; +### $diff_column_list is used for the generation of error information and valid for +### every sub test. +# let $diff_column_list= +# t2.COUNT_READ - t1.COUNT_READ AS D_COUNT_READ, +# t2.COUNT_READ AS S2_COUNT_READ, +# t1.COUNT_READ AS S1_COUNT_READ, +# t2.SUM_NUMBER_OF_BYTES_READ - t1.SUM_NUMBER_OF_BYTES_READ AS D_SUM_NUMBER_OF_BYTES_READ, +# t2.SUM_NUMBER_OF_BYTES_READ AS S2_SUM_NUMBER_OF_BYTES_READ, +# t1.SUM_NUMBER_OF_BYTES_READ AS S1_SUM_NUMBER_OF_BYTES_READ, +# t2.COUNT_WRITE - t1.COUNT_WRITE AS D_COUNT_WRITE, +# t2.COUNT_WRITE AS S2_COUNT_WRITE, +# t1.COUNT_WRITE AS S1_COUNT_WRITE, +# t2.SUM_NUMBER_OF_BYTES_WRITE - t1.SUM_NUMBER_OF_BYTES_WRITE AS D_UM_NUMBER_OF_BYTES_WRITE, +# t2.SUM_NUMBER_OF_BYTES_WRITE AS S2_SUM_NUMBER_OF_BYTES_WRITE, +# t1.SUM_NUMBER_OF_BYTES_WRITE AS S1_SUM_NUMBER_OF_BYTES_WRITE, +# t2.COUNT_MISC - t1.COUNT_MISC AS D_COUNT_MISC, +# t2.COUNT_MISC AS S2_COUNT_MISC, +# t1.COUNT_MISC AS S1_COUNT_MISC; +### $part is used for the generation of "check" statements + error information +### and valid for every sub test. +# let $part= +# FROM mysqltest.socket_summary_by_instance_detail t1 +# JOIN mysqltest.socket_summary_by_instance_detail t2 +# USING (EVENT_NAME, OBJECT_INSTANCE_BEGIN, run) +# WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin +# AND EVENT_NAME LIKE ('%client_connection') +# AND run = 1; +# +# --echo # $title_prefix Check the differences caused by SQL statement +# +# let stmt1= SELECT col2 FROM does_not_exist; +# let stmt2= SELECT col2 FROM does_not_exist WHERE col1 = 0; +### $msg is used to generate some explanation of what we compare. +# let $msg= +# # One statement is longer than the other. +# # Both statements fail with the same error message (table does not exist); +# let $my_rules= +# t2.COUNT_READ - t1.COUNT_READ = 0 AND +# t2.SUM_NUMBER_OF_BYTES_READ - t1.SUM_NUMBER_OF_BYTES_READ = LENGTH('$stmt2') - LENGTH('$stmt1') AND +# t2.COUNT_WRITE - t1.COUNT_WRITE = 0 AND +# t2.SUM_NUMBER_OF_BYTES_WRITE - t1.SUM_NUMBER_OF_BYTES_WRITE = 0 AND +# t2.COUNT_MISC - t1.COUNT_MISC = 0; +# --source ../include/socket_check1.inc +# +# let stmt1= .... +# let stmt2= .... +# let $my_rules= ... +# --source ../include/socket_check1.inc +# +# ... +# + +--echo # $title_prefix.$check_num Compare impact of statements +--echo # $stmt2 +--echo # $stmt1 +--echo # $msg + +# Enable this when extending the checks for SQL statements. +if(0) +{ + if (`SELECT CONCAT("$stmt1","$stmt2","$my_rules") LIKE '%_not_set%'`) + { + --echo # INTERNAL ERROR: + --echo # At least one of the variables has no value (is like '%_not_set') + --echo # stmt1 : $stmt1 + --echo # stmt2 : $stmt2 + --echo # my_rules : $my_rules + --echo # Sorry, have to abort + exit; + } +} + +if(`SELECT NOT ( $my_rules ) + $part + AND t2.statement = '$stmt2' AND t1.statement = '$stmt1'`) +{ + --enable_query_log + --enable_result_log + --echo # The compared statistics looks suspicious + --echo # We expect + --echo $my_rules + --echo + --horizontal_results + eval + SELECT $my_rules AS Expect_1 + $part + AND t2.statement = '$stmt2' AND t1.statement = '$stmt1'; + --echo + --vertical_results + eval + SELECT $diff_column_list + $part + AND t1.statement = '$stmt1' AND t2.statement = '$stmt2'; + --echo + --horizontal_results + + eval + SELECT + LPAD(COUNT_READ, 8, ' ') AS CNT_READ, + LPAD(SUM_NUMBER_OF_BYTES_READ, 10,' ') AS BYTES_READ, + LPAD(COUNT_WRITE,9, ' ') AS CNT_WRITE, + LPAD(SUM_NUMBER_OF_BYTES_WRITE, 11,' ') AS BYTES_WRITE, + LPAD(COUNT_MISC, 8, ' ') AS CNT_MISC, statement + FROM mysqltest.socket_summary_by_instance_detail + WHERE OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin + AND EVENT_NAME LIKE ('%client_connection') + AND run = 1 + AND statement IN('$stmt2','$stmt1'); + let $print_details= 1; +} +# Initialize all variables which depend on the statements to be checked. +# This prevents that we run with wrong data. +let $stmt1= stmt1_not_set; +let $stmt2= stmt2_not_set; +let $my_rules= my_rules_not_set; +let $msg= msg_not_set; +inc $check_num; + + diff --git a/mysql-test/suite/perfschema/include/socket_event.inc b/mysql-test/suite/perfschema/include/socket_event.inc new file mode 100644 index 00000000..96c61045 --- /dev/null +++ b/mysql-test/suite/perfschema/include/socket_event.inc @@ -0,0 +1,236 @@ +# include/socket_event.inc +# +# Auxiliary routine running +# - some statement in connection con1 +# or +# - connect/disconnect +# $loop_rounds times and checking if the changes to values caused by the action +# are reasonable. +# +# Requirements: +# 1. Have socket_summary_by_instance_func running +# 2a. Have a connection con1 +# @con1_object_instance_begin needs to be the OBJECT_INSTANCE_BEGIN +# value of the "client_connction" entry belonging to con1 within +# socket_summary_by_instance. +# $statement needs to contain the statement to be executed by con1. +# or +# 2b. Have assigned values to the following variables +# $connect_host $connect_db $connect_user +# + +let $my_errno= 0; + +let $loop_round= 1; +while($loop_round <= $loop_rounds) +{ + +--disable_query_log + +# Collect the current state +#========================== +eval $truncate; +eval $insert_before; + +# Run the operation +#================== +if($is_connect) +{ + let $statement= Connect (con*,$connect_host,$connect_user,,$connect_db,,); + # Some statements fail with ER_ACCESS_DENIED_ERROR + --disable_abort_on_error + --connect (con$loop_round,$connect_host,$connect_user,,$connect_db,,) + --enable_abort_on_error + let $my_errno= $mysql_errno; + if(!$my_errno) + { + # Note(mleich): + # We are aware that this additional statement is overhead. + # But it ensures that SUM_NUMBER_OF_BYTES_READ and + # SUM_NUMBER_OF_BYTES_WRITE are updated. + # And this avoids the instabilities found when running + # the connect without this additional statement. + DO 1; + } + --connection default +} +if(!$is_connect) +{ + --connection con1 + # Print the statement outcome once. + if($loop_round == 1) + { + --enable_query_log + --enable_result_log + --horizontal_results + } + # One of the statements to be checked is expected to fail with ER_NO_SUCH_TABLE. + --disable_abort_on_error + eval $statement; + --connection default + --enable_abort_on_error + --disable_query_log + --disable_result_log +} + +# Wait till the operation is really finished. We expect that there will be no +# changes to the statistics of the additional connection after this point of time. +#================================================================================= +--connection default +# Variants: +#---------- +# 1. Connect failed ($my_errno <> 0) +# no entry in performance_schema.threads -> wait_till_sleep.inc cannot be used +# short life entry in socket_summary_by_instance -> wait till it doesn't exist +# 2. Connect with success ($my_errno = 0) +# entry in performance_schema.threads -> wait_till_sleep.inc can be used +# entry in socket_summary_by_instance -> wait till it does exist +# 3. SQL command failed ($my_errno <> 0) +# entry in performance_schema.threads -> wait_till_sleep.inc can be used +if($is_connect) +{ + let $part= + FROM performance_schema.socket_summary_by_instance + WHERE EVENT_NAME LIKE '%client_connection' + AND OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin; + + if(!$my_errno) + { + # Wait till the new connection is visible in performance_schema.threads + # and processlist_command is 'Sleep'. + --source ../include/wait_till_sleep.inc + + # A successful connect causes that a new second row in + # performance_schema.socket_summary_by_instance shows up. + # Wait till this row is there. + let $wait_timeout= 10; + let $wait_condition= + SELECT COUNT(*) = 1 + $part; + --source include/wait_condition.inc + if (!$success) + { + --echo # Error: We did not reach the expected state where a new + --echo # row in socket_summary_by_instance is visible + eval + SELECT * + $part; + --echo # abort + exit; + } + } + if($my_errno) + { + # Experiments with high parallel load showed that there is a very + # period of time where a "client_connection" entry for a failing + # Connect is visible. + # We hope that sleep 1 is long enough so that PERFORMANCE_SCHEMA + # can remove this row before we collect the after action state. + let $wait_timeout= 5; + let $wait_condition= + SELECT COUNT(*) = 0 + $part; + --source include/wait_condition.inc + if(!$success) + { + --echo # Error: We did not reach the expected state. + --echo # A failing connect causes a "client_connection" entry + --echo # within socket_summary_by_instance having an extreme + --echo # short lifetime. + --echo # This entry must have now disappeared. + eval + SELECT * + $part; + --echo # abort + exit; + } + } + # --sleep 3 +} +if(!$is_connect) +{ + --source ../include/wait_till_sleep.inc +} + +# Various checks +#=============== +# 1. Check statistics in general +#------------------------------- +# ../include/socket_summary_check.inc also inserts the 'After' state into +# mysqltest.my_socket_summary_by_instance. +--source ../include/socket_summary_check.inc +--disable_query_log +--disable_result_log + +if($is_connect) +{ + eval $get_object_instance_begin; + eval $insert_pseudo_before; +} + +eval $insert_delta; +# Correct the values of the columns statement and run +eval +UPDATE mysqltest.socket_summary_by_instance_detail +SET statement = '$statement' +WHERE statement IS NULL; +eval +UPDATE mysqltest.socket_summary_by_instance_detail +SET run = $loop_round +WHERE run IS NULL; + +if($is_connect) +{ + # Only in case the connect was successful ($my_errno = 0) than we have to disconnect. + if(!$my_errno) + { + --disconnect con$loop_round + # Wait till the connection using the DB = 'mysqltest' or + # 'mysqlsupertest' has disappeared from performance_schema.threads + let $part= + FROM performance_schema.threads + WHERE processlist_db IN ('mysqltest','mysqlsupertest'); + let $wait_timeout= 10; + let $wait_condition= + SELECT COUNT(*) = 0 + $part; + --source include/wait_condition.inc + if (!$success) + { + --echo # Error: The disconnect of the connection with processlist_db + --echo # IN ('mysqltest','mysqlsupertest') failed + SELECT * + $part; + --echo # abort + exit; + } + # Wait in addition till the corresponding 'client_connection' entry of + # the connection using the DB = 'mysqltest' or 'mysqlsupertest' has disappeared. + let $part= + FROM performance_schema.socket_summary_by_instance + WHERE EVENT_NAME LIKE '%client_connection' + AND OBJECT_INSTANCE_BEGIN <> @default_object_instance_begin; + let $wait_timeout= 10; + let $wait_condition= + SELECT COUNT(*) = 0 + $part; + --source include/wait_condition.inc + if (!$success) + { + --echo # Error: The entry of the disconnectd connection with processlist_db + --echo # IN ('mysqltest','mysqlsupertest') did not disappear + SELECT * + $part; + --echo # abort + exit; + } + } + # --sleep 3 +} +inc $loop_round; + +} + +--enable_query_log +--enable_result_log + diff --git a/mysql-test/suite/perfschema/include/socket_event_dbg.inc b/mysql-test/suite/perfschema/include/socket_event_dbg.inc new file mode 100644 index 00000000..75c4d480 --- /dev/null +++ b/mysql-test/suite/perfschema/include/socket_event_dbg.inc @@ -0,0 +1,130 @@ +# include/socket_event.inc +# +# Auxiliary routine +# - running some statement in connection con1 +# and checking the changes for the client_connction" entry belonging to con1 +# within socket_summary_by_instance and +# - checking if the changes to values caused by the statement execution are +# reasonable and stable +# +# Requirements: +# 1. Have socket_summary_by_instance_func running +# 2. Have a connection con1 +# @con1_object_instance_begin needs to be the OBJECT_INSTANCE_BEGIN +# value of the "client_connction" entry belonging to con1 within +# socket_summary_by_instance. +# 3. $statement needs to contain the statement to be executed by con1. +# + +let $my_errno= 0; + +let $loop_round= 1; +while($loop_round <= $loop_rounds) +{ + +if (!$my_socket_debug) +{ + --disable_query_log +} + +# Collect the current state +#========================== +eval $truncate; +eval $insert_before; + +# Run the operation +#================== +if($is_connect) +{ + let $statement= Connect (con1,$connect_host,$connect_user,,$connect_db,,); + # Some statements fail with ER_ACCESS_DENIED_ERROR + --disable_abort_on_error + --connect (con1,$connect_host,$connect_user,,$connect_db,,) + --enable_abort_on_error + let $my_errno= $mysql_errno; +} +if(!$is_connect) +{ + --connection con1 + # Print the statement outcome once. + if($loop_round == 1) + { + --enable_query_log + --enable_result_log + --horizontal_results + } + # One of the statements to be checked is expected to fail with ER_NO_SUCH_TABLE. + --disable_abort_on_error + eval $statement; + --enable_abort_on_error + + if (!$my_socket_debug) + { + --disable_query_log + --disable_result_log + } +} + +# Wait till the operation is really finished. We expect that there will be no +# changes to the statistics of the additional connection after this point of time. +#================================================================================= + +--connection default +if($my_errno) +{ + # Wait a bit and hope that the counter maintenence is finished. + --sleep 3 +} +if(!$my_errno) +{ + --source ../include/wait_till_sleep.inc +} + +# Various checks +#=============== +# 1. Check statistics in general +#------------------------------- +# ../include/socket_summary_check.inc also inserts the 'After' state into +# mysqltest.my_socket_summary_by_instance. +--source ../include/socket_summary_check_dbg.inc + +if (!$my_socket_debug) +{ + --disable_query_log + --disable_result_log +} + +if($is_connect) +{ + eval $get_object_instance_begin; + eval $insert_pseudo_before; +} + +eval $insert_delta; +# Correct the values of the columns statement and run +eval +UPDATE mysqltest.socket_summary_by_instance_detail +SET statement = '$statement' +WHERE statement IS NULL; +eval +UPDATE mysqltest.socket_summary_by_instance_detail +SET run = $loop_round +WHERE run IS NULL; + +if($is_connect) +{ + if(!$my_errno) + { + --connection con1 + --disconnect con1 + --source include/wait_until_disconnected.inc + --connection default + } +} +inc $loop_round; + +} + +--enable_query_log +--enable_result_log + diff --git a/mysql-test/suite/perfschema/include/socket_ipv6.inc b/mysql-test/suite/perfschema/include/socket_ipv6.inc new file mode 100644 index 00000000..0c9f47ab --- /dev/null +++ b/mysql-test/suite/perfschema/include/socket_ipv6.inc @@ -0,0 +1,117 @@ +#============================================================================== +# Set IP address defaults with respect to IPV6 support +# +# This file determines the level of support for IPV4, IPV4 mapped or IPV6, then +# sets the appropriate localhost IP format to use for 'connect()' commands. +# +# Input: $my_socket_debug - Print results of IP version check (optional) +# Output: $my_localhost - Default localhost IP +#============================================================================== + +let $check_ipv6_just_check= 1; +#--source include/check_ipv6.inc + +#============================================================================== +# Determine if IPV6 supported +# +# Parameters: +# $check_ipv6_just_check - Don't skip the test if IPv6 is unsupported, +# just set the variable $check_ipv6_supported +#============================================================================== +--disable_query_log +--disable_result_log +--disable_connect_log +--disable_abort_on_error + +let $check_ipv6_supported= 1; + +connect (checkcon123456789,::1,root,,test); + +if($mysql_errno) +{ + let $check_ipv6_supported=0; + if(!$check_ipv6_just_check) + { + skip No IPv6 support; + } +} + +if(!$mysql_errno) +{ + disconnect checkcon123456789; + --source include/wait_until_disconnected.inc +} + +connection default; + +--enable_abort_on_error +--enable_connect_log +--enable_result_log +--enable_query_log + +#============================================================================== +# +# Determine if IPV4 mapped to IPV6 supported +# +let $check_ipv4_mapped_just_check= 1; +#--source include/check_ipv4_mapped.inc +#============================================================================== +# Check if ipv4 mapped to ipv6 is available. +# +# Parameters: +# $check_ipv4_mapped_just_check - Don't skip the test if IPv4 mapped is unsupported, +# just set the variable $check_ipv4_mapped_supported +#============================================================================== +--disable_query_log +--disable_result_log +--disable_connect_log +--disable_abort_on_error + +let $check_ipv4_mapped_supported= 1; + +connect (checkcon123456789a,::FFFF:127.0.0.1,root,,test); + +if($mysql_errno) +{ + let $check_ipv4_mapped_supported=0; + if(!$check_ipv4_mapped_just_check) + { + skip No mapped IPv4 support; + } +} + +if(!$mysql_errno) +{ + disconnect checkcon123456789a; + --source include/wait_until_disconnected.inc +} + +connection default; + +--enable_abort_on_error +--enable_connect_log +--enable_result_log +--enable_query_log + +#============================================================================== +# Set the localhost IP default to use when establishing connections +# +#============================================================================== +let $my_localhost=127.0.0.1; + +if($check_ipv6_supported) +{ + let $my_localhost=::1; +} + +if($check_ipv4_mapped_supported) +{ + let $my_localhost=::ffff:127.0.0.1; +} + +if($my_socket_debug) +{ + --echo IPV6=$check_ipv6_supported, IPV4_MAPPED=$check_ipv4_mapped_supported, LOCALHOST=$my_localhost +} +#============================================================================== + diff --git a/mysql-test/suite/perfschema/include/socket_summary_check.inc b/mysql-test/suite/perfschema/include/socket_summary_check.inc new file mode 100644 index 00000000..10258cad --- /dev/null +++ b/mysql-test/suite/perfschema/include/socket_summary_check.inc @@ -0,0 +1,227 @@ +# include/socket_summary_check.inc +# +# Auxiliary routine to be sourced by socket_summary_by_instance_func.test +# or other routines sourced within this script. +# +# Purpose +# Various checks for the content of the table socket_summary_by_instance. +# +# It is intentional that we do not try to cram as much checks as possible into +# one single SQL statement. +# Reasons: +# - We check performance_schema here and NOT something like optimizer. +# - This test should work even if some other feature has become buggy. +# - In case some check gives unexpected results than we print the +# relevant content of the table and the values which we expect. +# In case of all checks in one statement such a printout would be too huge. +# +# IMPORTANT: +# The maximum number of rows which the table socket_summary_by_instance +# can keep is limited via the system variables max_socket_classes and +# max_socket_instances. We are running with the default values here. +# They are sufficient high so that these limits cannot harm the current test. +# FIXME: Check at the beginning of the test that the limits are sufficient +# for the current test. +# + +--disable_query_log +# Insert the current state into mysqltest.my_socket_summary_by_instance. +eval $insert_after; +--enable_query_log +--enable_result_log + + +# 1. The content of socket_summary_by_instance must be consistent to the +# content of socket_instances +#======================================================================= +let $part1= +FROM performance_schema.socket_summary_by_instance +WHERE (EVENT_NAME,OBJECT_INSTANCE_BEGIN) + NOT IN (SELECT EVENT_NAME,OBJECT_INSTANCE_BEGIN +FROM performance_schema.socket_instances); +if(`SELECT COUNT(*) $part1`) +{ + --echo # There is an inconsistency between the content of the tables + --echo # socket_instances and socket_summary_by_instance + --echo # + eval + SELECT 'not in socket_instances' AS state, EVENT_NAME, OBJECT_INSTANCE_BEGIN + $part1; +} + +--vertical_results + + +# 2. The computation of statistics must be roughly correct. +# +# If we run this check sufficient frequent than AVG_TIMER_* can be removed from other checks. +#=============================================================================================== +let $my_lo= 0.98; +let $my_hi= 1.02; + +let $my_rules= +COUNT_STAR * AVG_TIMER_WAIT BETWEEN SUM_TIMER_WAIT * $my_lo AND SUM_TIMER_WAIT * $my_hi AND +COUNT_READ * AVG_TIMER_READ BETWEEN SUM_TIMER_READ * $my_lo AND SUM_TIMER_READ * $my_hi AND +COUNT_WRITE * AVG_TIMER_WRITE BETWEEN SUM_TIMER_WRITE * $my_lo AND SUM_TIMER_WRITE * $my_hi AND +COUNT_MISC * AVG_TIMER_MISC BETWEEN SUM_TIMER_MISC * $my_lo AND SUM_TIMER_MISC * $my_hi; + +let $part= +SUM_TIMER_WAIT * $my_lo, COUNT_STAR * AVG_TIMER_WAIT, SUM_TIMER_WAIT * $my_hi, + COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT, +SUM_TIMER_READ * $my_lo, COUNT_READ * AVG_TIMER_READ, SUM_TIMER_READ * $my_hi, + COUNT_READ, SUM_TIMER_READ, AVG_TIMER_READ, +SUM_TIMER_WRITE * $my_lo, COUNT_WRITE * AVG_TIMER_WRITE, SUM_TIMER_WRITE * $my_hi, + COUNT_WRITE, SUM_TIMER_WRITE, AVG_TIMER_WRITE, +SUM_TIMER_MISC * $my_lo, COUNT_MISC * AVG_TIMER_MISC, SUM_TIMER_MISC * $my_hi, + COUNT_MISC, SUM_TIMER_MISC, AVG_TIMER_MISC; + +if(`SELECT SUM($my_rules) <> COUNT(*) FROM mysqltest.my_socket_summary_by_instance + WHERE pk = 'After'`) +{ + --echo # The statistics looks suspicious. + --echo # We expect + --echo # $my_rules + eval + SELECT EVENT_NAME, OBJECT_INSTANCE_BEGIN, + $part + FROM mysqltest.my_socket_summary_by_instance + WHERE pk = 'After' AND NOT ($my_rules) + ORDER BY EVENT_NAME, OBJECT_INSTANCE_BEGIN; + let $print_details= 1; +} + + +# 3. Check the relation between AVG_*, MIN_TIMER_* and MAX_TIMER_* +# +# If we run this check sufficient frequent than only the following +# additional checks are required: +# a) If (SUM_TIMER_*(new) - SUM_TIMER_*(old) < MIN_TIMER_*(old)) +# than MIN_TIMER_*(new) = SUM_TIMER_*(new) - SUM_TIMER_*(old). +# b) If (SUM_TIMER_*(new) - SUM_TIMER_*(old) > MAX_TIMER_*(old)) +# than MAX_TIMER_*(new) = SUM_TIMER_*(new) - SUM_TIMER_*(old). +# in order to remove MIN_TIMER_* and MAX_TIMER_* from other checks +# Between the states "new" and "old" must be exact one statement. +#----------------------------------------------------------------------------------------------- +let $my_rules= +AVG_TIMER_WAIT >= MIN_TIMER_WAIT AND MAX_TIMER_WAIT >= AVG_TIMER_WAIT AND +AVG_TIMER_READ >= MIN_TIMER_READ AND MAX_TIMER_READ >= AVG_TIMER_READ AND +AVG_TIMER_WRITE >= MIN_TIMER_WRITE AND MAX_TIMER_WRITE >= AVG_TIMER_WRITE AND +AVG_TIMER_MISC >= MIN_TIMER_MISC AND MAX_TIMER_MISC >= AVG_TIMER_MISC; + +let $part= +MIN_TIMER_WAIT, AVG_TIMER_WAIT, MAX_TIMER_WAIT, +MIN_TIMER_READ, AVG_TIMER_READ, MAX_TIMER_READ, +MIN_TIMER_WRITE, AVG_TIMER_WRITE, MAX_TIMER_WRITE, +MIN_TIMER_MISC, AVG_TIMER_MISC, MAX_TIMER_MISC; + +if(`SELECT SUM($my_rules) <> COUNT(*) FROM mysqltest.my_socket_summary_by_instance + WHERE pk = 'After'`) +{ + --echo # The statistics looks suspicious. + --echo # We expect + --echo # $my_rules + eval + SELECT EVENT_NAME, OBJECT_INSTANCE_BEGIN, + $part + FROM mysqltest.my_socket_summary_by_instance + WHERE pk = 'After' AND NOT ($my_rules) + ORDER BY EVENT_NAME, OBJECT_INSTANCE_BEGIN; + let $print_details= 1; +} + + +# 4. Check the aggregate columns COUNT_STAR and SUM_TIMER_WAIT +# +# The specification says: +# The columns COUNT_STAR, SUM/MIN/AVG/MAX TIMER_WAIT aggregates all operations. +# +# If we run this check sufficient frequent than COUNT_STAR and SUM_TIMER_WAIT +# can be removed from other checks. +#--------------------------------------------------------------------------------- +let $my_rules= +COUNT_STAR = COUNT_READ + COUNT_WRITE + COUNT_MISC AND +SUM_TIMER_WAIT = SUM_TIMER_READ + SUM_TIMER_WRITE + SUM_TIMER_MISC; + +let $part= +COUNT_STAR, COUNT_READ + COUNT_WRITE + COUNT_MISC, COUNT_READ, COUNT_WRITE, COUNT_MISC, +SUM_TIMER_WAIT, SUM_TIMER_READ + SUM_TIMER_WRITE + SUM_TIMER_MISC, SUM_TIMER_READ, +SUM_TIMER_WRITE, SUM_TIMER_MISC; + +if(`SELECT SUM($my_rules) <> COUNT(*) FROM mysqltest.my_socket_summary_by_instance + WHERE pk = 'After'`) +{ + --echo # The statistics looks suspicious. + --echo # We expect + --echo # $my_rules + --echo # + eval + SELECT EVENT_NAME, OBJECT_INSTANCE_BEGIN, + $part + FROM mysqltest.my_socket_summary_by_instance + WHERE pk = 'After' + ORDER BY EVENT_NAME, OBJECT_INSTANCE_BEGIN; + let $print_details= 1; +} + + +# 5. Check the aggregate column MIN_TIMER_WAIT +# +# If we run this check sufficient frequent than MIN_TIMER_WAIT +# can be removed from other checks. +#--------------------------------------------------------------------------------- +let $my_rules= +MIN_TIMER_WAIT >= mysqltest.min_of_triple(MIN_TIMER_READ,MIN_TIMER_WRITE,MIN_TIMER_MISC); + +let $part= +MIN_TIMER_WAIT, +mysqltest.min_of_triple(MIN_TIMER_READ, MIN_TIMER_WRITE, MIN_TIMER_MISC) AS "Min_of_Triple", +MIN_TIMER_READ, MIN_TIMER_WRITE, MIN_TIMER_MISC; + +if(`SELECT SUM($my_rules) <> COUNT(*) FROM mysqltest.my_socket_summary_by_instance + WHERE pk = 'After'`) +{ + --echo # The statistics looks suspicious. + --echo # We expect + --echo # $my_rules + --echo # + eval + SELECT EVENT_NAME, OBJECT_INSTANCE_BEGIN, + $part + FROM mysqltest.my_socket_summary_by_instance + WHERE pk = 'After' + ORDER BY EVENT_NAME, OBJECT_INSTANCE_BEGIN; + let $print_details= 1; +} + + +# 6. Check the aggregate column MAX_TIMER_WAIT +# +# If we run this check sufficient frequent than MAX_TIMER_WAIT +# can be removed from other checks. +#--------------------------------------------------------------------------------- +let $my_rules= +MAX_TIMER_WAIT >= mysqltest.max_of_triple(MAX_TIMER_READ,MAX_TIMER_WRITE,MAX_TIMER_MISC); + +let $part= +MAX_TIMER_WAIT, +mysqltest.max_of_triple(MAX_TIMER_READ, MAX_TIMER_WRITE, MAX_TIMER_MISC) AS "Max_of_Triple", +MAX_TIMER_READ, MAX_TIMER_WRITE, MAX_TIMER_MISC; + +if(`SELECT SUM($my_rules) <> COUNT(*) FROM mysqltest.my_socket_summary_by_instance + WHERE pk = 'After'`) +{ + --echo # The statistics looks suspicious. + --echo # We expect + --echo # $my_rules + --echo # + eval + SELECT EVENT_NAME, OBJECT_INSTANCE_BEGIN, + $part + FROM mysqltest.my_socket_summary_by_instance + WHERE pk = 'After' + ORDER BY EVENT_NAME, OBJECT_INSTANCE_BEGIN; + let $print_details= 1; +} + +--horizontal_results + diff --git a/mysql-test/suite/perfschema/include/socket_summary_check_dbg.inc b/mysql-test/suite/perfschema/include/socket_summary_check_dbg.inc new file mode 100644 index 00000000..64ee19c3 --- /dev/null +++ b/mysql-test/suite/perfschema/include/socket_summary_check_dbg.inc @@ -0,0 +1,236 @@ +# include/socket_summary_check.inc +# +# Auxiliary routine to be sourced by socket_summary_by_instance_func.test +# or other routines sourced within this script. +# +# Purpose +# Various checks for the content of the table socket_summary_by_instance. +# +# It is intentional that we do not try to cram as much checks as possible into +# one single SQL statement. +# Reasons: +# - We check performance_schema here and NOT something like optimizer. +# - This test should work even if some other feature has become buggy. +# - In case some check gives unexpected results than we print the +# relevant content of the table and the values which we expect. +# In case of all checks in one statement such a printout would be too huge. +# +# IMPORTANT: +# The maximum number of rows which the table socket_summary_by_instance +# can keep is limited via the system variables max_socket_classes and +# max_socket_instances. We are running with the default values here. +# They are sufficient high so that these limits cannot harm the current test. +# FIXME: Check at the beginning of the test that the limits are sufficient +# for the current test. +# + +--disable_query_log +# Insert the current state into mysqltest.my_socket_summary_by_instance. +eval $insert_after; +--enable_query_log + + +# 1. The content of socket_summary_by_instance must be consistent to the +# content of socket_instances +#----------------------------------------------------------------------- +let $part1= +FROM performance_schema.socket_summary_by_instance +WHERE (EVENT_NAME,OBJECT_INSTANCE_BEGIN) + NOT IN (SELECT EVENT_NAME,OBJECT_INSTANCE_BEGIN +FROM performance_schema.socket_instances); +if(`SELECT COUNT(*) $part1`) +{ + --echo # There is an inconsistency between the content of the tables + --echo # socket_instances and socket_summary_by_instance + --echo # + eval + SELECT 'not in socket_instances' AS state, EVENT_NAME, OBJECT_INSTANCE_BEGIN + $part1; + --echo # abort 1 + # exit; +} + +--vertical_results + + +# 2. The computation of statistics must be roughly correct. +# +# If we run this check sufficient frequent than AVG_TIMER_* can be removed from other checks. +#----------------------------------------------------------------------------------------------- +let $my_lo= 0.99; +let $my_hi= 1.01; + +let $my_rules= +COUNT_STAR * AVG_TIMER_WAIT BETWEEN SUM_TIMER_WAIT * $my_lo AND SUM_TIMER_WAIT * $my_hi AND +COUNT_READ * AVG_TIMER_READ BETWEEN SUM_TIMER_READ * $my_lo AND SUM_TIMER_READ * $my_hi AND +COUNT_WRITE * AVG_TIMER_WRITE BETWEEN SUM_TIMER_WRITE * $my_lo AND SUM_TIMER_WRITE * $my_hi AND +COUNT_MISC * AVG_TIMER_MISC BETWEEN SUM_TIMER_MISC * $my_lo AND SUM_TIMER_MISC * $my_hi; + +let $part= +SUM_TIMER_WAIT * $my_lo, COUNT_STAR * AVG_TIMER_WAIT, SUM_TIMER_WAIT * $my_hi, COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT, +SUM_TIMER_READ * $my_lo, COUNT_READ * AVG_TIMER_READ, SUM_TIMER_READ * $my_hi, COUNT_READ, SUM_TIMER_READ, AVG_TIMER_READ, +SUM_TIMER_WRITE * $my_lo, COUNT_WRITE * AVG_TIMER_WRITE, SUM_TIMER_WRITE * $my_hi, COUNT_WRITE, SUM_TIMER_WRITE, AVG_TIMER_WRITE, +SUM_TIMER_MISC * $my_lo, COUNT_MISC * AVG_TIMER_MISC, SUM_TIMER_MISC * $my_hi, COUNT_MISC, SUM_TIMER_MISC, AVG_TIMER_MISC; + +if(`SELECT SUM($my_rules) <> COUNT(*) FROM mysqltest.my_socket_summary_by_instance + WHERE pk = 'After'`) +{ + --echo # The statistics looks suspicious. + --echo # We expect + --echo # $my_rules + eval + SELECT EVENT_NAME, OBJECT_INSTANCE_BEGIN, + $part + FROM mysqltest.my_socket_summary_by_instance + WHERE pk = 'After' + ORDER BY EVENT_NAME, OBJECT_INSTANCE_BEGIN; + + -- echo # Debug 2a: Dump socket_summary_by_instance + eval + SELECT EVENT_NAME, OBJECT_INSTANCE_BEGIN, + $part + FROM performance_schema.socket_summary_by_instance + ORDER BY EVENT_NAME, OBJECT_INSTANCE_BEGIN; + --echo # abort 2 +# exit; +} + + +# 3. Check the relation between AVG_*, MIN_TIMER_* and MAX_TIMER_* +# +# If we run this check sufficient frequent than only the following +# additional checks are required: +# a) If (SUM_TIMER_*(new) - SUM_TIMER_*(old) < MIN_TIMER_*(old)) +# than MIN_TIMER_*(new) = SUM_TIMER_*(new) - SUM_TIMER_*(old). +# b) If (SUM_TIMER_*(new) - SUM_TIMER_*(old) > MAX_TIMER_*(old)) +# than MAX_TIMER_*(new) = SUM_TIMER_*(new) - SUM_TIMER_*(old). +# in order to remove MIN_TIMER_* and MAX_TIMER_* from other checks +# Between the states "new" and "old" must be exact one statement. +#----------------------------------------------------------------------------------------------- +let $my_rules= +AVG_TIMER_WAIT >= MIN_TIMER_WAIT AND MAX_TIMER_WAIT >= AVG_TIMER_WAIT AND +AVG_TIMER_READ >= MIN_TIMER_READ AND MAX_TIMER_READ >= AVG_TIMER_READ AND +AVG_TIMER_WRITE >= MIN_TIMER_WRITE AND MAX_TIMER_WRITE >= AVG_TIMER_WRITE AND +AVG_TIMER_MISC >= MIN_TIMER_MISC AND MAX_TIMER_MISC >= AVG_TIMER_MISC; + +let $part= +MIN_TIMER_WAIT, AVG_TIMER_WAIT, MAX_TIMER_WAIT, +MIN_TIMER_READ, AVG_TIMER_READ, MAX_TIMER_READ, +MIN_TIMER_WRITE, AVG_TIMER_WRITE, MAX_TIMER_WRITE, +MIN_TIMER_MISC, AVG_TIMER_MISC, MAX_TIMER_MISC; + +if(`SELECT SUM($my_rules) <> COUNT(*) FROM mysqltest.my_socket_summary_by_instance + WHERE pk = 'After'`) +{ + --echo # The statistics looks suspicious. + --echo # We expect + --echo # $my_rules + eval + SELECT EVENT_NAME, OBJECT_INSTANCE_BEGIN, + $part + FROM mysqltest.my_socket_summary_by_instance + WHERE pk = 'After' + ORDER BY EVENT_NAME, OBJECT_INSTANCE_BEGIN; + --echo # abort 3 +# exit; +} + + +# 4. Check the aggregate columns COUNT_STAR and SUM_TIMER_WAIT +# +# The specification says: +# The columns COUNT_STAR, SUM/MIN/AVG/MAX TIMER_WAIT aggregates all operations. +# +# If we run this check sufficient frequent than COUNT_STAR and SUM_TIMER_WAIT +# can be removed from other checks. +#--------------------------------------------------------------------------------- +let $my_rules= +COUNT_STAR = COUNT_READ + COUNT_WRITE + COUNT_MISC AND +SUM_TIMER_WAIT = SUM_TIMER_READ + SUM_TIMER_WRITE + SUM_TIMER_MISC; + +let $part= +COUNT_STAR, COUNT_READ + COUNT_WRITE + COUNT_MISC, COUNT_READ, COUNT_WRITE, COUNT_MISC, +SUM_TIMER_WAIT, SUM_TIMER_READ + SUM_TIMER_WRITE + SUM_TIMER_MISC, SUM_TIMER_READ, +SUM_TIMER_WRITE, SUM_TIMER_MISC; + +if(`SELECT SUM($my_rules) <> COUNT(*) FROM mysqltest.my_socket_summary_by_instance + WHERE pk = 'After'`) +{ + --echo # The statistics looks suspicious. + --echo # We expect + --echo # $my_rules + --echo # + eval + SELECT EVENT_NAME, OBJECT_INSTANCE_BEGIN, + $part + FROM mysqltest.my_socket_summary_by_instance + WHERE pk = 'After' + ORDER BY EVENT_NAME, OBJECT_INSTANCE_BEGIN; + --echo # abort 4 +# exit; +} + + +# 5. Check the aggregate column MIN_TIMER_WAIT +# +# If we run this check sufficient frequent than MIN_TIMER_WAIT +# can be removed from other checks. +#--------------------------------------------------------------------------------- +let $my_rules= +MIN_TIMER_WAIT >= mysqltest.min_of_triple(MIN_TIMER_READ,MIN_TIMER_WRITE,MIN_TIMER_MISC); + +let $part= +MIN_TIMER_WAIT, +mysqltest.min_of_triple(MIN_TIMER_READ, MIN_TIMER_WRITE, MIN_TIMER_MISC) AS "Min_of_Triple", +MIN_TIMER_READ, MIN_TIMER_WRITE, MIN_TIMER_MISC; + +if(`SELECT SUM($my_rules) <> COUNT(*) FROM mysqltest.my_socket_summary_by_instance + WHERE pk = 'After'`) +{ + --echo # The statistics looks suspicious. + --echo # We expect + --echo # $my_rules + --echo # + eval + SELECT EVENT_NAME, OBJECT_INSTANCE_BEGIN, + $part + FROM mysqltest.my_socket_summary_by_instance + WHERE pk = 'After' + ORDER BY EVENT_NAME, OBJECT_INSTANCE_BEGIN; + --echo # abort 5 +# exit; +} + + +# 6. Check the aggregate column MAX_TIMER_WAIT +# +# If we run this check sufficient frequent than MAX_TIMER_WAIT +# can be removed from other checks. +#--------------------------------------------------------------------------------- +let $my_rules= +MAX_TIMER_WAIT >= mysqltest.max_of_triple(MAX_TIMER_READ,MAX_TIMER_WRITE,MAX_TIMER_MISC); + +let $part= +MAX_TIMER_WAIT, +mysqltest.max_of_triple(MAX_TIMER_READ, MAX_TIMER_WRITE, MAX_TIMER_MISC) AS "Max_of_Triple", +MAX_TIMER_READ, MAX_TIMER_WRITE, MAX_TIMER_MISC; + +if(`SELECT SUM($my_rules) <> COUNT(*) FROM mysqltest.my_socket_summary_by_instance + WHERE pk = 'After'`) +{ + --echo # The statistics looks suspicious. + --echo # We expect + --echo # $my_rules + --echo # + eval + SELECT EVENT_NAME, OBJECT_INSTANCE_BEGIN, + $part + FROM mysqltest.my_socket_summary_by_instance + WHERE pk = 'After' + ORDER BY EVENT_NAME, OBJECT_INSTANCE_BEGIN; + --echo # abort 6 +# exit; +} + +--horizontal_results + diff --git a/mysql-test/suite/perfschema/include/stage_cleanup.inc b/mysql-test/suite/perfschema/include/stage_cleanup.inc new file mode 100644 index 00000000..c3c9d911 --- /dev/null +++ b/mysql-test/suite/perfschema/include/stage_cleanup.inc @@ -0,0 +1,31 @@ +# Tests for the performance schema + +# ================================ +# HELPER include/stage_cleanup.inc +# ================================ + +--disable_query_log +revoke all privileges, grant option from user1@localhost; +revoke all privileges, grant option from user2@localhost; +revoke all privileges, grant option from user3@localhost; +revoke all privileges, grant option from user4@localhost; +drop user user1@localhost; +drop user user2@localhost; +drop user user3@localhost; +drop user user4@localhost; +flush privileges; + +drop procedure dump_thread; +drop procedure dump_one_thread; + +truncate table performance_schema.setup_actors; +insert into performance_schema.setup_actors + select * from test.setup_actors; +drop table test.setup_actors; +drop table test.t1; + +update performance_schema.threads set instrumented='YES'; +update performance_schema.setup_instruments set enabled='YES', timed='YES'; + +--enable_query_log + diff --git a/mysql-test/suite/perfschema/include/stage_setup.inc b/mysql-test/suite/perfschema/include/stage_setup.inc new file mode 100644 index 00000000..683c4561 --- /dev/null +++ b/mysql-test/suite/perfschema/include/stage_setup.inc @@ -0,0 +1,140 @@ +# Tests for the performance schema + +# ============= +# DOCUMENTATION +# ============= + +# Verify critical stages of a statement +# +# The tests are written with the following helpers: +# - include/stage_setup.inc +# - include/stage_cleanup.inc +# +# Helpers are intended to be used as follows. +# +# A Typical test t/stage_xxx.test will consist of: +# --source ../include/stage_setup.inc +# ... test specific payload ... +# --source ../include/stage_cleanup.inc +# and a t/stage_xxx-master.opt file +# +# ============================== +# HELPER include/stage_setup.inc +# ============================== + +--source include/not_embedded.inc +--source include/have_perfschema.inc +--source include/no_protocol.inc + +--disable_query_log + +create user user1@localhost; +grant ALL on *.* to user1@localhost; +create user user2@localhost; +grant ALL on *.* to user2@localhost; +create user user3@localhost; +grant ALL on *.* to user3@localhost; +create user user4@localhost; +grant ALL on *.* to user4@localhost; + +flush privileges; + +# Save the setup + +--disable_warnings +drop table if exists test.setup_actors; +drop table if exists test.t1; +--enable_warnings + +create table test.t1(a varchar(64)); + +create table test.setup_actors as + select * from performance_schema.setup_actors; + +# Only instrument the user connections +truncate table performance_schema.setup_actors; +insert into performance_schema.setup_actors + set host= 'localhost', user= 'user1', role= '%'; +insert into performance_schema.setup_actors + set host= 'localhost', user= 'user2', role= '%'; +insert into performance_schema.setup_actors + set host= 'localhost', user= 'user3', role= '%'; +insert into performance_schema.setup_actors + set host= 'localhost', user= 'user4', role= '%'; + +update performance_schema.threads set instrumented='NO'; + +# Only instrument a few events of each kind +update performance_schema.setup_instruments set enabled='YES', timed='YES'; + +# Start from a known clean state, to avoid noise from previous tests +flush tables; +flush status; +truncate performance_schema.events_stages_summary_by_thread_by_event_name; +truncate performance_schema.events_stages_summary_global_by_event_name; +truncate performance_schema.events_stages_history; +truncate performance_schema.events_stages_history_long; +truncate performance_schema.events_statements_summary_by_thread_by_event_name; +truncate performance_schema.events_statements_summary_global_by_event_name; +truncate performance_schema.events_statements_history; +truncate performance_schema.events_statements_history_long; + +--disable_warnings +drop procedure if exists dump_thread; +drop procedure if exists dump_one_thread; +--enable_warnings + +delimiter $$; + +create procedure dump_thread() +begin + call dump_one_thread('user1'); + call dump_one_thread('user2'); + call dump_one_thread('user3'); + call dump_one_thread('user4'); +end +$$ + +create procedure dump_one_thread(in username varchar(64)) +begin + declare my_thread_id int; + declare my_statement_id int; + + set my_thread_id = (select thread_id from performance_schema.threads + where processlist_user=username); + + if (my_thread_id is not null) then + begin + # Dump the current statement for this thread + select username, event_name, sql_text + from performance_schema.events_statements_current + where thread_id = my_thread_id; + + # Get the current statement + set my_statement_id = (select event_id from + performance_schema.events_statements_current + where thread_id = my_thread_id); + + # Dump the stages for this statement + select username, event_name, nesting_event_type + from performance_schema.events_stages_current + where thread_id = my_thread_id + and nesting_event_id = my_statement_id + order by event_id asc; + # Ignore query cache as it may not be enabled + select username, event_name, nesting_event_type + from performance_schema.events_stages_history + where thread_id = my_thread_id + and nesting_event_id = my_statement_id and + event_name not like "%query cache%" + order by event_id asc; + end; + else + select username, "not found" as status; + end if; +end +$$ + +delimiter ;$$ + +--enable_query_log diff --git a/mysql-test/suite/perfschema/include/start_server_common.inc b/mysql-test/suite/perfschema/include/start_server_common.inc new file mode 100644 index 00000000..1297bcdf --- /dev/null +++ b/mysql-test/suite/perfschema/include/start_server_common.inc @@ -0,0 +1,120 @@ +# Tests for PERFORMANCE_SCHEMA + +show databases; + +select count(*) from performance_schema.performance_timers; +select count(*) from performance_schema.setup_consumers; +# wait/io/table/sql/handler is a native instrument +# wait/lock/table/sql/handler is a native instrument +# idle/io/socket is a native instrument +# wait/lock/metadata is a native instrument +select count(*) > 4 from performance_schema.setup_instruments; +select count(*) from performance_schema.setup_timers; + +# Make sure we don't crash, no matter what the starting parameters are + +--disable_result_log +select * from performance_schema.accounts; +select * from performance_schema.cond_instances; +select * from performance_schema.events_stages_current; +select * from performance_schema.events_stages_history; +select * from performance_schema.events_stages_history_long; +select * from performance_schema.events_stages_summary_by_account_by_event_name; +select * from performance_schema.events_stages_summary_by_host_by_event_name; +select * from performance_schema.events_stages_summary_by_thread_by_event_name; +select * from performance_schema.events_stages_summary_by_user_by_event_name; +select * from performance_schema.events_stages_summary_global_by_event_name; +select * from performance_schema.events_statements_current; +select * from performance_schema.events_statements_history; +select * from performance_schema.events_statements_history_long; +select * from performance_schema.events_statements_summary_by_account_by_event_name; +select * from performance_schema.events_statements_summary_by_digest; +select * from performance_schema.events_statements_summary_by_host_by_event_name; +select * from performance_schema.events_statements_summary_by_thread_by_event_name; +select * from performance_schema.events_statements_summary_by_user_by_event_name; +select * from performance_schema.events_statements_summary_global_by_event_name; +select * from performance_schema.events_transactions_current; +select * from performance_schema.events_transactions_history; +select * from performance_schema.events_transactions_history_long; +select * from performance_schema.events_transactions_summary_by_account_by_event_name; +select * from performance_schema.events_transactions_summary_by_host_by_event_name; +select * from performance_schema.events_transactions_summary_by_thread_by_event_name; +select * from performance_schema.events_transactions_summary_by_user_by_event_name; +select * from performance_schema.events_transactions_summary_global_by_event_name; +select * from performance_schema.events_waits_current; +select * from performance_schema.events_waits_history; +select * from performance_schema.events_waits_history_long; +select * from performance_schema.events_waits_summary_by_account_by_event_name; +select * from performance_schema.events_waits_summary_by_host_by_event_name; +select * from performance_schema.events_waits_summary_by_instance; +select * from performance_schema.events_waits_summary_by_thread_by_event_name; +select * from performance_schema.events_waits_summary_by_user_by_event_name; +select * from performance_schema.events_waits_summary_global_by_event_name; +select * from performance_schema.memory_summary_by_account_by_event_name; +select * from performance_schema.memory_summary_by_host_by_event_name; +select * from performance_schema.memory_summary_by_thread_by_event_name; +select * from performance_schema.memory_summary_by_user_by_event_name; +select * from performance_schema.memory_summary_global_by_event_name; +select * from performance_schema.file_instances; +select * from performance_schema.file_summary_by_event_name; +select * from performance_schema.file_summary_by_instance; +select * from performance_schema.host_cache; +select * from performance_schema.hosts; +select * from performance_schema.memory_summary_by_account_by_event_name; +select * from performance_schema.memory_summary_by_host_by_event_name; +select * from performance_schema.memory_summary_by_thread_by_event_name; +select * from performance_schema.memory_summary_by_user_by_event_name; +select * from performance_schema.memory_summary_global_by_event_name; +select * from performance_schema.metadata_locks; +select * from performance_schema.mutex_instances; +select * from performance_schema.objects_summary_global_by_type; +select * from performance_schema.performance_timers; +select * from performance_schema.rwlock_instances; +select * from performance_schema.session_account_connect_attrs; +select * from performance_schema.session_connect_attrs; +select * from performance_schema.setup_actors; +select * from performance_schema.setup_consumers; +select * from performance_schema.setup_instruments; +select * from performance_schema.setup_objects; +select * from performance_schema.setup_timers; +select * from performance_schema.socket_instances; +select * from performance_schema.socket_summary_by_instance; +select * from performance_schema.socket_summary_by_event_name; +select * from performance_schema.table_handles; +select * from performance_schema.table_io_waits_summary_by_index_usage; +select * from performance_schema.table_io_waits_summary_by_table; +select * from performance_schema.table_lock_waits_summary_by_table; +select * from performance_schema.threads; +select * from performance_schema.users; +select * from performance_schema.replication_connection_configuration; +#select * from performance_schema.replication_connection_status; +select * from performance_schema.replication_applier_configuration; +select * from performance_schema.replication_applier_status; +select * from performance_schema.replication_applier_status_by_coordinator; +#select * from performance_schema.replication_applier_status_by_worker; +select * from performance_schema.global_status; +select * from performance_schema.status_by_thread; +select * from performance_schema.status_by_user; +select * from performance_schema.status_by_host; +select * from performance_schema.status_by_account; +select * from performance_schema.session_status; +#select * from performance_schema.global_variables; +#select * from performance_schema.variables_by_thread; +#select * from performance_schema.session_variables; +--enable_result_log + +# This has a stable output, printing the result: +show global variables like "performance_schema%"; + +# This has an unrepeatable output, it does depends too much on +# - the platform hardware (sizeof structures, padding) +# - the compiler used (sizeof(enum)) +# - the platform header files (sizeof(size_t)) +# - the code path in the server (what gets executed and instrumented +# at runtime) + +--disable_result_log +show engine PERFORMANCE_SCHEMA status; +show global status like "performance_schema%"; +--enable_result_log + diff --git a/mysql-test/suite/perfschema/include/table_aggregate_cleanup.inc b/mysql-test/suite/perfschema/include/table_aggregate_cleanup.inc new file mode 100644 index 00000000..11b354d8 --- /dev/null +++ b/mysql-test/suite/perfschema/include/table_aggregate_cleanup.inc @@ -0,0 +1,53 @@ +# Tests for the performance schema +# + +# See comments in include/table_aggregate_setup.inc + +--disable_query_log + +revoke all privileges, grant option from user1@localhost; +revoke all privileges, grant option from user2@localhost; +revoke all privileges, grant option from user3@localhost; +revoke all privileges, grant option from user4@localhost; +drop user user1@localhost; +drop user user2@localhost; +drop user user3@localhost; +drop user user4@localhost; +flush privileges; + +drop procedure dump_thread; +drop procedure dump_one_thread; +drop prepare dump_waits_account; +drop prepare dump_waits_user; +drop prepare dump_waits_host; +drop prepare dump_waits_global; +drop prepare dump_waits_history; +drop prepare dump_waits_index_io; +drop prepare dump_waits_table_io; +drop prepare dump_waits_table_lock; +drop prepare dump_objects_summary; + +truncate table performance_schema.accounts; +truncate table performance_schema.users; +truncate table performance_schema.hosts; + +truncate table performance_schema.setup_actors; +insert into performance_schema.setup_actors + select * from test.setup_actors; +drop table test.setup_actors; + +truncate table performance_schema.setup_objects; +insert into performance_schema.setup_objects + select * from test.setup_objects; +drop table test.setup_objects; + +drop table test.t1; +drop table test.t2; +drop table test.t3; + +update performance_schema.threads set instrumented='YES'; +update performance_schema.setup_instruments set enabled='YES', timed='YES'; +update performance_schema.setup_consumers set enabled='YES'; + +--enable_query_log + diff --git a/mysql-test/suite/perfschema/include/table_aggregate_load.inc b/mysql-test/suite/perfschema/include/table_aggregate_load.inc new file mode 100644 index 00000000..9d757617 --- /dev/null +++ b/mysql-test/suite/perfschema/include/table_aggregate_load.inc @@ -0,0 +1,532 @@ +# Tests for the performance schema +# + +# See comments in include/table_aggregate_setup.inc +# +# Notes about the optimizer and query plans: +# The following statement +# SELECT * from t1 where b=5; +# can be executed either: +# - by using the index "index_b" on column b +# - by using a full table scan and the where clause. +# Which plan is used can be unpredictable. +# To ensure that the index is really used, +# so that table io against the index is measured, +# the payload in this test uses: +# SELECT * from t1 force index(index_b) where b=5; +# + +# Display the current setup used + +select * from performance_schema.setup_actors + order by USER, HOST, ROLE; + +select * from performance_schema.setup_objects + order by object_type, object_schema, object_name; + +select * from performance_schema.setup_consumers; + +# General cleanup + +flush tables; + +truncate performance_schema.objects_summary_global_by_type; +truncate performance_schema.table_io_waits_summary_by_index_usage; +truncate performance_schema.table_io_waits_summary_by_table; +truncate performance_schema.table_lock_waits_summary_by_table; +truncate performance_schema.events_waits_summary_by_thread_by_event_name; +truncate performance_schema.events_waits_summary_by_account_by_event_name; +truncate performance_schema.events_waits_summary_by_user_by_event_name; +truncate performance_schema.events_waits_summary_by_host_by_event_name; +truncate performance_schema.events_waits_summary_global_by_event_name; +truncate performance_schema.events_waits_history_long; + +# Check the configuration is ok +# We don't print this as it causes too many changes in test cases if +# one variable changes value +#show variables where +# `Variable_name` != "performance_schema_max_statement_classes" and +# `Variable_name` like "performance_schema%"; +#show status like "performance_schema%"; + +echo "================== Step 1 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_waits_index_io; +execute dump_waits_table_io; +execute dump_waits_table_lock; +execute dump_objects_summary; + +# Notes about this test +# + +connect (con1, localhost, user1, , ); +select concat(current_user(), " is connected") as status; + +--connection default + +echo "================== Step 2 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_waits_index_io; +execute dump_waits_table_io; +execute dump_waits_table_lock; +execute dump_objects_summary; + +--connection con1 + +insert into test.t1 set a=101, b=1, c=1; +insert into test.t2 set a=102, b=2, c=2; +insert into test.t2 set a=103, b=3, c=3; +insert into test.t3 set a=104, b=4, c=4; +insert into test.t3 set a=105, b=5, c=5; +insert into test.t3 set a=106, b=6, c=6; +select * from test.t1; +select * from test.t2; +select * from test.t3; +# Full table scan +update test.t1 set d=d+1; +update test.t2 set d=d+1; +update test.t3 set d=d+1; +# Update with PK +update test.t1 set d=d+1 where a=101; +update test.t2 set d=d+1 where a=101; +update test.t3 set d=d+1 where a=101; +# select with index +select * from test.t1 force index(index_b) where b=5; +select * from test.t2 force index(index_b) where b=5; +select * from test.t3 force index(index_b) where b=5; + +--connection default + +echo "================== Step 3-A =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_waits_index_io; +execute dump_waits_table_io; +execute dump_waits_table_lock; +execute dump_objects_summary; + +--connection con1 + +# This cause aggregation, so that index names are finally recorded +flush tables; + +echo "================== con1 FLUSH =================="; + +--connection default + +echo "================== Step 3-B =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_waits_index_io; +execute dump_waits_table_io; +execute dump_waits_table_lock; +execute dump_objects_summary; + +# Debugging helpers +# select * from performance_schema.events_waits_history_long; +# select PROCESSLIST_USER, PROCESSLIST_HOST, INSTRUMENTED from performance_schema.threads; + +connect (con2, localhost, user2, , ); +select concat(current_user(), " is connected") as status; + +--connection default + +echo "================== Step 4 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_waits_index_io; +execute dump_waits_table_io; +execute dump_waits_table_lock; +execute dump_objects_summary; + +--connection con2 + +insert into test.t1 set a=201, b=1, c=1; +insert into test.t2 set a=202, b=2, c=2; +insert into test.t2 set a=203, b=3, c=3; +insert into test.t3 set a=204, b=4, c=4; +insert into test.t3 set a=205, b=5, c=5; +insert into test.t3 set a=206, b=6, c=6; +select * from test.t1; +select * from test.t2; +select * from test.t3; +# Full table scan +update test.t1 set d=d+1; +update test.t2 set d=d+1; +update test.t3 set d=d+1; +# Update with PK +update test.t1 set d=d+1 where a=201; +update test.t2 set d=d+1 where a=201; +update test.t3 set d=d+1 where a=201; +# select with index +select * from test.t1 force index(index_b) where b=5; +select * from test.t2 force index(index_b) where b=5; +select * from test.t3 force index(index_b) where b=5; + +--connection default + +echo "================== Step 5 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_waits_index_io; +execute dump_waits_table_io; +execute dump_waits_table_lock; +execute dump_objects_summary; + +connect (con3, localhost, user3, , ); +select concat(current_user(), " is connected") as status; + +--connection default + +echo "================== Step 6 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_waits_index_io; +execute dump_waits_table_io; +execute dump_waits_table_lock; +execute dump_objects_summary; + +--connection con3 + +insert into test.t1 set a=301, b=1, c=1; +insert into test.t2 set a=302, b=2, c=2; +insert into test.t2 set a=303, b=3, c=3; +insert into test.t3 set a=304, b=4, c=4; +insert into test.t3 set a=305, b=5, c=5; +insert into test.t3 set a=306, b=6, c=6; +select * from test.t1; +select * from test.t2; +select * from test.t3; +# Full table scan +update test.t1 set d=d+1; +update test.t2 set d=d+1; +update test.t3 set d=d+1; +# Update with PK +update test.t1 set d=d+1 where a=301; +update test.t2 set d=d+1 where a=301; +update test.t3 set d=d+1 where a=301; +# select with index +select * from test.t1 force index(index_b) where b=5; +select * from test.t2 force index(index_b) where b=5; +select * from test.t3 force index(index_b) where b=5; + +--connection default + +echo "================== Step 7 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_waits_index_io; +execute dump_waits_table_io; +execute dump_waits_table_lock; +execute dump_objects_summary; + +connect (con4, localhost, user4, , ); +select concat(current_user(), " is connected") as status; + +--connection default + +echo "================== Step 8 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_waits_index_io; +execute dump_waits_table_io; +execute dump_waits_table_lock; +execute dump_objects_summary; + +--connection con4 + +insert into test.t1 set a=401, b=1, c=1; +insert into test.t2 set a=402, b=2, c=2; +insert into test.t2 set a=403, b=3, c=3; +insert into test.t3 set a=404, b=4, c=4; +insert into test.t3 set a=405, b=5, c=5; +insert into test.t3 set a=406, b=6, c=6; +select * from test.t1; +select * from test.t2; +select * from test.t3; +# Full table scan +update test.t1 set d=d+1; +update test.t2 set d=d+1; +update test.t3 set d=d+1; +# Update with PK +update test.t1 set d=d+1 where a=401; +update test.t2 set d=d+1 where a=401; +update test.t3 set d=d+1 where a=401; +# select with index +select * from test.t1 force index(index_b) where b=5; +select * from test.t2 force index(index_b) where b=5; +select * from test.t3 force index(index_b) where b=5; + +--connection default + +echo "================== Step 9 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_waits_index_io; +execute dump_waits_table_io; +execute dump_waits_table_lock; +execute dump_objects_summary; + +--connection con1 + +lock tables test.t1 read, test.t2 read, test.t3 read; +unlock tables; +lock tables test.t1 write, test.t2 write, test.t3 write; +unlock tables; + +--connection default + +echo "================== Step 10 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_waits_index_io; +execute dump_waits_table_io; +execute dump_waits_table_lock; +execute dump_objects_summary; + +--connection default + +flush tables; + +echo "================== flush marker =================="; + +echo "================== Step 11 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_waits_index_io; +execute dump_waits_table_io; +execute dump_waits_table_lock; +execute dump_objects_summary; + +set global read_only=1; +set global read_only=0; + +echo "================== global read_only marker =================="; + +echo "================== Step 12 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_waits_index_io; +execute dump_waits_table_io; +execute dump_waits_table_lock; +execute dump_objects_summary; + +--disconnect con1 + +# Wait for the disconnect to complete +let $wait_condition= + select count(*) = 0 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user1'; +--source include/wait_condition.inc + +echo "================== Step 13 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_waits_index_io; +execute dump_waits_table_io; +execute dump_waits_table_lock; +execute dump_objects_summary; + +--disconnect con2 + +# Wait for the disconnect to complete +let $wait_condition= + select count(*) = 0 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user2'; +--source include/wait_condition.inc + +echo "================== Step 14 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_waits_index_io; +execute dump_waits_table_io; +execute dump_waits_table_lock; +execute dump_objects_summary; + +--disconnect con3 + +# Wait for the disconnect to complete +let $wait_condition= + select count(*) = 0 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user3'; +--source include/wait_condition.inc + +echo "================== Step 15 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_waits_index_io; +execute dump_waits_table_io; +execute dump_waits_table_lock; +execute dump_objects_summary; + +--disconnect con4 + +# Wait for the disconnect to complete +let $wait_condition= + select count(*) = 0 from performance_schema.threads + where `TYPE`='FOREGROUND' and PROCESSLIST_USER= 'user4'; +--source include/wait_condition.inc + +echo "================== Step 16 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_waits_index_io; +execute dump_waits_table_io; +execute dump_waits_table_lock; +execute dump_objects_summary; + +--connection default + +truncate performance_schema.events_waits_summary_by_thread_by_event_name; + +echo "================== BY_THREAD truncated =================="; + +echo "================== Step 17 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_waits_index_io; +execute dump_waits_table_io; +execute dump_waits_table_lock; +execute dump_objects_summary; + +truncate performance_schema.events_waits_summary_by_account_by_event_name; + +echo "================== BY_ACCOUNT truncated =================="; + +echo "================== Step 18 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_waits_index_io; +execute dump_waits_table_io; +execute dump_waits_table_lock; +execute dump_objects_summary; + +truncate performance_schema.events_waits_summary_by_user_by_event_name; + +echo "================== BY_USER truncated =================="; + +echo "================== Step 19 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_waits_index_io; +execute dump_waits_table_io; +execute dump_waits_table_lock; +execute dump_objects_summary; + +truncate performance_schema.events_waits_summary_by_host_by_event_name; + +echo "================== BY_HOST truncated =================="; + +echo "================== Step 21 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_waits_index_io; +execute dump_waits_table_io; +execute dump_waits_table_lock; +execute dump_objects_summary; + +truncate performance_schema.events_waits_summary_global_by_event_name; + +echo "================== GLOBAL truncated =================="; + +echo "================== Step 21 =================="; +call dump_thread(); +execute dump_waits_account; +execute dump_waits_user; +execute dump_waits_host; +execute dump_waits_global; +execute dump_waits_history; +execute dump_waits_index_io; +execute dump_waits_table_io; +execute dump_waits_table_lock; +execute dump_objects_summary; + +# On test failures, may help to track the root cause +show global status like "performance_schema%"; + diff --git a/mysql-test/suite/perfschema/include/table_aggregate_setup.inc b/mysql-test/suite/perfschema/include/table_aggregate_setup.inc new file mode 100644 index 00000000..79f7bd70 --- /dev/null +++ b/mysql-test/suite/perfschema/include/table_aggregate_setup.inc @@ -0,0 +1,273 @@ +# Tests for the performance schema + +# ============= +# DOCUMENTATION +# ============= + +# Verify how table io is aggregated into various tables +# +# In the instance dimension: +# - table_io_waits_summary_by_index_usage +# - table_io_waits_summary_by_table +# - table_lock_waits_summary_by_table +# - objects_summary_global_by_type +# +# In the thread dimension: +# - events_waits_summary_by_thread_by_event_name +# - events_waits_summary_by_account_by_event_name +# - events_waits_summary_by_user_by_event_name +# - events_waits_summary_by_host_by_event_name +# +# Globally: +# - events_waits_summary_global_by_event_name +# +# The tests are written with the following helpers: +# - include/table_aggregate_setup.inc +# - include/table_aggregate_load.inc +# - include/table_aggregate_cleanup.inc +# +# Helpers are intended to be used as follows. +# +# A Typical test t/table_aggregate_xxx.test will consist of: +# --source ../include/table_aggregate_setup.inc +# +# ... statements to modify the default configuration ... +# +# --source ../include/table_aggregate_load.inc +# --source ../include/table_aggregate_cleanup.inc +# +# Naming conventions for t/table_aggregate_xxx.test are as follows: +# t/<instrument>aggregate_<mode><actors><objects> +# +# <instrument> corresponds to different instruments settings +# - table: both table io and table lock are instrumented +# - table_io: only table io is instrumented +# - table_lock: only table lock is instrumented +# +# <mode> corresponds to different consumers settings +# - off: global_instrumentation OFF +# - global: global_instrumentation ON, thread_instrumentation OFF +# - thread: global_instrumentation ON, thread_instrumentation ON, +# events_* consumers OFF +# - history: global_instrumentation ON, thread_instrumentation ON, +# events_* consumers ON +# +# <actors> corresponds to different setup_actors settings +# - 4u: every test user (user1, user2, user3, user4) is ON +# - 2u: (user1, user3) are ON, (user2, user4) are OFF +# +# <objects> corresponds to different setup_objects settings +# - 3t: tables t1, t2 and t3 are ON +# - 2t: tables t1 and t3 are ON, table t2 is OFF +# + +# ======================================== +# HELPER include/table_aggregate_setup.inc +# ======================================== + +--source include/not_embedded.inc +--source include/have_perfschema.inc +--source include/no_protocol.inc +--source ../include/wait_for_pfs_thread_count.inc + +--disable_query_log +create user user1@localhost; +grant ALL on *.* to user1@localhost; +create user user2@localhost; +grant ALL on *.* to user2@localhost; +create user user3@localhost; +grant ALL on *.* to user3@localhost; +create user user4@localhost; +grant ALL on *.* to user4@localhost; + +flush privileges; + +# Purge old users, hosts, user/host from previous tests +truncate table performance_schema.accounts; +truncate table performance_schema.users; +truncate table performance_schema.hosts; + +# Save the setup + +--disable_warnings +drop table if exists test.setup_actors; +drop table if exists test.setup_objects; +--enable_warnings + +create table test.setup_actors as + select * from performance_schema.setup_actors; + +create table test.setup_objects as + select * from performance_schema.setup_objects; + +# Only instrument the user connections (by default) +truncate table performance_schema.setup_actors; +insert into performance_schema.setup_actors + set host= 'localhost', user= 'user1', role= '%'; +insert into performance_schema.setup_actors + set host= 'localhost', user= 'user2', role= '%'; +insert into performance_schema.setup_actors + set host= 'localhost', user= 'user3', role= '%'; +insert into performance_schema.setup_actors + set host= 'localhost', user= 'user4', role= '%'; + +# Only instrument test.t% tables (by default) +truncate table performance_schema.setup_objects; +insert into performance_schema.setup_objects + set object_type='TABLE', object_schema='test', object_name='t1', timed='YES'; +insert into performance_schema.setup_objects + set object_type='TABLE', object_schema='test', object_name='t2', timed='NO'; +insert into performance_schema.setup_objects + set object_type='TABLE', object_schema='test', object_name='t3', timed='NO'; + +update performance_schema.threads set instrumented='NO'; + +# Only instrument table io and lock (by default) +update performance_schema.setup_instruments set enabled='NO', timed='NO'; +update performance_schema.setup_instruments set enabled='YES', timed='YES' + where name in ('wait/io/table/sql/handler', + 'wait/lock/table/sql/handler'); + +# Enable all consumers (by default) +update performance_schema.setup_consumers set enabled='YES'; + +# Start from a known clean state, to avoid noise from previous tests +flush tables; +flush status; + +create table test.t1(a int, b int, c int, d int default 0, + primary key(a), + index index_b(b), + index index_cb(c, b)); +create table test.t2 like test.t1; +create table test.t3 like test.t1; + +# +# Note: +# For test robustness and to avoid picking up noise from other tests scripts, +# it is better to use: +# in ('t1', 't2', 't3) +# explicitly instead of: +# like 't%' + +--disable_warnings +drop procedure if exists dump_thread; +drop procedure if exists dump_one_thread; +--enable_warnings + +delimiter $$; + +create procedure dump_thread() +begin + call dump_one_thread('user1'); + call dump_one_thread('user2'); + call dump_one_thread('user3'); + call dump_one_thread('user4'); +end +$$ + +create procedure dump_one_thread(in username varchar(64)) +begin + declare my_thread_id int; + + set my_thread_id = (select thread_id from performance_schema.threads + where processlist_user=username); + + if (my_thread_id is not null) then + select username, event_name, count_star + from performance_schema.events_waits_summary_by_thread_by_event_name + where event_name in + ('wait/io/table/sql/handler', + 'wait/lock/table/sql/handler') + and thread_id = my_thread_id + order by event_name; + else + select username, "not found" as status; + end if; +end +$$ + +delimiter ;$$ + +prepare dump_waits_user from + "select user, event_name, count_star + from performance_schema.events_waits_summary_by_user_by_event_name + where user like \'user%\' and event_name in + (\'wait/io/table/sql/handler\', + \'wait/lock/table/sql/handler\') + order by user, event_name;"; + +prepare dump_waits_account from + "select user, host, event_name, count_star + from performance_schema.events_waits_summary_by_account_by_event_name + where user like \'user%\' and event_name in + (\'wait/io/table/sql/handler\', + \'wait/lock/table/sql/handler\') + order by user, host, event_name;"; + +prepare dump_waits_host from + "select host, event_name, count_star + from performance_schema.events_waits_summary_by_host_by_event_name + where host=\'localhost\' and event_name in + (\'wait/io/table/sql/handler\', + \'wait/lock/table/sql/handler\') + order by host, event_name;"; + +prepare dump_waits_global from + "select event_name, count_star + from performance_schema.events_waits_summary_global_by_event_name + where event_name in + (\'wait/io/table/sql/handler\', + \'wait/lock/table/sql/handler\') + order by event_name;"; + +prepare dump_waits_history from + "select event_name, + sum(if(number_of_bytes is null, 1, number_of_bytes)) as 'count(event_name)', + object_type, object_schema, object_name + from performance_schema.events_waits_history_long + where event_name in + (\'wait/io/table/sql/handler\', + \'wait/lock/table/sql/handler\') + group by object_type, object_schema, object_name, event_name + order by object_type, object_schema, object_name, event_name;"; + +prepare dump_waits_index_io from + "select object_type, object_schema, object_name, index_name, + count_star, count_read, count_write, + count_fetch, count_insert, count_update, count_delete + from performance_schema.table_io_waits_summary_by_index_usage + where object_type='TABLE' and object_schema='test' + and object_name in ('t1', 't2', 't3') + order by object_type, object_schema, object_name, index_name;"; + +prepare dump_waits_table_io from + "select object_type, object_schema, object_name, + count_star, count_read, count_write, + count_fetch, count_insert, count_update, count_delete + from performance_schema.table_io_waits_summary_by_table + where object_type='TABLE' and object_schema='test' + and object_name in ('t1', 't2', 't3') + order by object_type, object_schema, object_name"; + +prepare dump_waits_table_lock from + "select object_type, object_schema, object_name, + count_star, count_read, count_write, + count_read_normal, count_read_with_shared_locks, + count_read_high_priority, count_read_no_insert, + count_read_external, + count_write_low_priority, + count_write_external + from performance_schema.table_lock_waits_summary_by_table + where object_type='TABLE' and object_schema='test' + and object_name in ('t1', 't2', 't3') + order by object_type, object_schema, object_name"; + +prepare dump_objects_summary from + "select object_type, object_schema, object_name, count_star + from performance_schema.objects_summary_global_by_type + where object_type='TABLE' and object_schema='test' + and object_name in ('t1', 't2', 't3') + order by object_type, object_schema, object_name"; + +--enable_query_log diff --git a/mysql-test/suite/perfschema/include/table_io_basic_dml.inc b/mysql-test/suite/perfschema/include/table_io_basic_dml.inc new file mode 100644 index 00000000..5457566b --- /dev/null +++ b/mysql-test/suite/perfschema/include/table_io_basic_dml.inc @@ -0,0 +1,37 @@ +# Tests for PERFORMANCE_SCHEMA table io +# +# Payload fragment to test table io for basic DML. +# +# $table_item = <schema>.<tablename> must be set before sourcing this script. +# + +--disable_ps2_protocol +insert into marker set a = 1; +eval insert into $table_item set a = 'foo', b = 1; +insert into marker set a = 1; +eval insert into $table_item set a = 'foo', b = 2; +insert into marker set a = 1; +eval insert into $table_item set a = 'foo', b = 3; +insert into marker set a = 1; +eval select * from $table_item; +insert into marker set a = 1; +eval update $table_item set a = 'bar'; +insert into marker set a = 1; +eval select * from $table_item limit 2; +insert into marker set a = 1; +eval delete from $table_item where b = 3; +insert into marker set a = 1; +# This may record a fetch for "deleted" records +eval select * from $table_item; +insert into marker set a = 1; +eval optimize table $table_item; +insert into marker set a = 1; +# Same data after optimize +eval select * from $table_item; +insert into marker set a = 1; +# truncate will fail for views +--disable_abort_on_error +eval truncate table $table_item; +--enable_abort_on_error +insert into marker set a = 1; +--enable_ps2_protocol diff --git a/mysql-test/suite/perfschema/include/table_io_cleanup_helper.inc b/mysql-test/suite/perfschema/include/table_io_cleanup_helper.inc new file mode 100644 index 00000000..02957903 --- /dev/null +++ b/mysql-test/suite/perfschema/include/table_io_cleanup_helper.inc @@ -0,0 +1,10 @@ +# See related script table_io_setup_helper.inc + +# Cleanup +update performance_schema.setup_consumers set enabled='NO'; +truncate performance_schema.events_waits_history_long; +drop table test.marker; +flush status; +update performance_schema.setup_instruments set enabled='YES'; +update performance_schema.setup_consumers set enabled='YES'; + diff --git a/mysql-test/suite/perfschema/include/table_io_result_helper.inc b/mysql-test/suite/perfschema/include/table_io_result_helper.inc new file mode 100644 index 00000000..66431b6a --- /dev/null +++ b/mysql-test/suite/perfschema/include/table_io_result_helper.inc @@ -0,0 +1,22 @@ +# See related script table_io_setup_helper.inc + +# Stop table io recording +update performance_schema.setup_consumers set enabled='NO'; +eval select event_name, + left(source, locate(":", source)) as short_source, + object_type, object_schema, + if (locate("#sql-", object_name), "#sql-XXXX", object_name) + as pretty_name, + operation, number_of_bytes + from performance_schema.events_waits_history_long + where event_name like 'wait/io/table/%' + and object_schema in ($schema_to_dump) + order by thread_id, event_id; + +# In case of failures, this will tell if table io are lost. +show global status like 'performance_schema_%'; + +# Cleanup +truncate performance_schema.events_waits_history_long; +flush status; + diff --git a/mysql-test/suite/perfschema/include/table_io_setup_helper.inc b/mysql-test/suite/perfschema/include/table_io_setup_helper.inc new file mode 100644 index 00000000..b4c4b5fa --- /dev/null +++ b/mysql-test/suite/perfschema/include/table_io_setup_helper.inc @@ -0,0 +1,58 @@ +# Performance schema test template + +# How to use this script in a test case +# ===================================== +# +# The general table io test template is as follows +# +# --source include/not_embedded.inc +# --source include/have_perfschema.inc +# --source ../include/table_io_setup_helper.inc +# ... more setup scripts as needed ... +# update performance_schema.setup_consumers set enabled='YES'; +# ... test payload here ... +# ... optionally, add this insert between statements +# ... to make the final output more readable +# insert into test.marker set a=1; +# ... more test payload here ... +# ... optionaly, add the following line (by default, only "test" is dumped) ... +# let $schema_to_dump="db1", "db2", "db3"; +# --source ../include/table_io_result_helper.inc +# Optional: Repeat several times +# update performance_schema.setup_consumers set enabled='YES'; +# ... test payload here ... +# --source ../include/table_io_result_helper.inc +# ... cleanup +# --source ../include/table_io_cleanup_helper.inc +# +# (end of template) + +# Setup + +--disable_warnings +drop table if exists test.marker; +--enable_warnings + +# To be used in the test payload, +# insert into marker makes the test output easier to read, +# to separate table io events between statements. +create table test.marker(a int); + +update performance_schema.setup_consumers set enabled='NO'; + +update performance_schema.setup_instruments set enabled='NO'; +update performance_schema.setup_instruments set enabled='YES' + where name like "wait/io/table/%"; + +truncate table performance_schema.events_waits_history_long; + +# Reset lost counters to a known state +flush status; + +# Make sure there is room to instrument tables from this test. +flush tables; + +# By default, dump table io only for test. +# A test can overide this +let $schema_to_dump="test"; + diff --git a/mysql-test/suite/perfschema/include/transaction_cleanup.inc b/mysql-test/suite/perfschema/include/transaction_cleanup.inc new file mode 100644 index 00000000..3b8ed02d --- /dev/null +++ b/mysql-test/suite/perfschema/include/transaction_cleanup.inc @@ -0,0 +1,12 @@ +# Tests for the performance schema + +# ========================================== +# HELPER include/transaction_cleanup.inc +# ========================================== + +DROP PROCEDURE clear_transaction_tables; +DROP PROCEDURE clear_transaction_history; +DROP PROCEDURE clear_statement_history; +DROP PROCEDURE clear_history; +DROP PROCEDURE transaction_verifier; + diff --git a/mysql-test/suite/perfschema/include/transaction_nested_events_verifier.inc b/mysql-test/suite/perfschema/include/transaction_nested_events_verifier.inc new file mode 100644 index 00000000..baee5e84 --- /dev/null +++ b/mysql-test/suite/perfschema/include/transaction_nested_events_verifier.inc @@ -0,0 +1,145 @@ +# Tests for the performance schema + +# ===================================================== +# HELPER include/transaction_nested_events_verifier.inc +# ===================================================== + +--connection default +--disable_query_log + +# Poll till the activity of the actor connection con1 caused by the last +# statement issued has finished. +let $wait_timeout= 10; +let $wait_condition= + SELECT COUNT(*) > 0 FROM performance_schema.threads + WHERE THREAD_ID = @con1_thread_id + AND PROCESSLIST_COMMAND = 'Sleep'; +--source include/wait_condition.inc +if(!$success) +{ + --echo # The activity of connection con1 did not finish. + SELECT thread_id , PROCESSLIST_id, PROCESSLIST_STATE, PROCESSLIST_INFO + FROM performance_schema.threads + WHERE thread_id = @con1_thread_id; + --echo # Abort. + exit; +} + +--echo #======================================================================== +--echo # Verify +--echo #======================================================================== + +SELECT event_id into @base_tx_event_id + from performance_schema.events_transactions_history_long + where (THREAD_ID = @con1_thread_id) + order by event_id limit 1; + +SELECT event_id into @base_stmt_event_id from performance_schema.events_statements_history_long + where (THREAD_ID = @con1_thread_id) + order by event_id limit 1; + +select if(@base_tx_event_id < @base_stmt_event_id, + @base_tx_event_id - 1, + @base_stmt_event_id - 1) + into @base_event_id; + +# Debug helpers +# set @base_event_id = 0; +# select @base_tx_event_id, @base_stmt_event_id, @base_event_id; + +--echo # +--echo # EVENTS_TRANSACTIONS_CURRENT +--echo # + +--replace_column 1 thread_id + +SELECT THREAD_ID, + LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID, + LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID, + RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ', + RPAD(STATE, 11, ' ') 'STATE ', + RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE, + RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ', + RPAD(AUTOCOMMIT, 4, ' ') AUTO, + LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID, + RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE +FROM performance_schema.events_transactions_current +WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1)) +ORDER BY thread_id, event_id; + +--echo # +--echo # EVENTS_TRANSACTIONS_HISTORY_LONG +--echo # + +--replace_column 1 thread_id + +SELECT THREAD_ID, + LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID, + LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID, + RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ', + RPAD(STATE, 11, ' ') 'STATE ', + RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE, + RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ', + RPAD(AUTOCOMMIT, 4, ' ') AUTO, + LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID, + RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE +FROM performance_schema.events_transactions_history_long +WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1)) +ORDER BY thread_id, event_id; + +--echo # +--echo # EVENTS_STATEMENTS_HISTORY_LONG +--echo # + +--replace_column 1 thread_id + +SELECT THREAD_ID, + LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID, + LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID, + RPAD(EVENT_NAME, 30, ' ') 'EVENT_NAME ', + RPAD(IFNULL(object_name, 'NULL'), 12, ' ') 'OBJECT_NAME ', + LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID, + RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE, + LPAD(NESTING_EVENT_LEVEL, 5, ' ') LEVEL, + SQL_TEXT +FROM performance_schema.events_statements_history_long +WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1)) +ORDER BY thread_id, event_id; + +--echo # +--echo ### Combined statement and transaction event history ordered by event id +--echo # +--echo #EVENTS_STATEMENTS_HISTORY_LONG + EVENTS_TRANSACTIONS_HISTORY_LONG +--echo # + +--replace_column 1 thread_id + +SELECT THREAD_ID, + LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID, + LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID, + RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME ', + LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID, + RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE, + '<transaction started>' AS SQL_TXT +FROM performance_schema.events_transactions_history_long t +WHERE (t.thread_id = @con1_thread_id) +UNION +SELECT THREAD_ID, + LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID, + LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID, + RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME ', + LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID, + RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE, + SQL_TEXT +FROM performance_schema.events_statements_history_long s +WHERE ((s.thread_id = @con1_thread_id) OR (@all_threads = 1)) +ORDER BY thread_id, r_event_id; + +--echo # +--echo ### Clear statement and transaction history +--echo # CALL test.clear_history(); +CALL test.clear_history(); +--enable_query_log +--echo ## Reset db.t1 +DELETE FROM db.t1; +--echo # diff --git a/mysql-test/suite/perfschema/include/transaction_setup.inc b/mysql-test/suite/perfschema/include/transaction_setup.inc new file mode 100644 index 00000000..78697425 --- /dev/null +++ b/mysql-test/suite/perfschema/include/transaction_setup.inc @@ -0,0 +1,231 @@ +# Tests for the performance schema + +# ========================================== +# HELPER include/transaction_setup.inc +# ========================================== + +# +# UTILITY QUERIES +# +let $get_thread_id= + SELECT thread_id INTO @my_thread_id + FROM performance_schema.threads + WHERE processlist_id = connection_id(); + +let $disable_instruments= + UPDATE performance_schema.setup_instruments + SET enabled='no', timed='no' + WHERE name IN ('transaction'); + +let $enable_instruments= + UPDATE performance_schema.setup_instruments + SET enabled='yes', timed='yes' + WHERE name IN ('transaction'); + +let $def_count = -1; + +--disable_warnings +DROP PROCEDURE IF EXISTS clear_transaction_tables; +--enable_warnings + +--disable_result_log + +DELIMITER $$; +CREATE PROCEDURE clear_transaction_tables() +BEGIN + truncate table performance_schema.events_transactions_current; + truncate table performance_schema.events_transactions_history; + truncate table performance_schema.events_transactions_history_long; + truncate table performance_schema.events_transactions_summary_by_thread_by_event_name; + truncate table performance_schema.events_transactions_summary_by_account_by_event_name; + truncate table performance_schema.events_transactions_summary_by_host_by_event_name; + truncate table performance_schema.events_transactions_summary_by_user_by_event_name; + truncate table performance_schema.events_transactions_summary_global_by_event_name; +END$$ + +CREATE PROCEDURE clear_transaction_history() +BEGIN + truncate table performance_schema.events_transactions_current; + truncate table performance_schema.events_transactions_history; + truncate table performance_schema.events_transactions_history_long; +END$$ + +CREATE PROCEDURE clear_statement_history() +BEGIN + truncate table performance_schema.events_statements_current; + truncate table performance_schema.events_statements_history; + truncate table performance_schema.events_statements_history_long; +END$$ + +CREATE PROCEDURE clear_history() +BEGIN + truncate table performance_schema.events_statements_current; + truncate table performance_schema.events_statements_history; + truncate table performance_schema.events_statements_history_long; + truncate table performance_schema.events_transactions_current; + truncate table performance_schema.events_transactions_history; + truncate table performance_schema.events_transactions_history_long; +END$$ + +CREATE PROCEDURE transaction_verifier(IN i_table INT, + IN i_thread_id INT, + IN i_event_name VARCHAR(64), + IN i_state VARCHAR(32), + IN i_xid_format_id INT, + IN i_xid_gtrid VARCHAR(130), + IN i_xid_bqual VARCHAR(130), + IN i_xa_state VARCHAR(64), + IN i_gtid VARCHAR(64), + IN i_access_mode VARCHAR(32), + IN i_isolation_level VARCHAR(64), + IN i_autocommit VARCHAR(16), + IN i_savepoints INT, + IN i_rb_savepoint INT, + IN i_rel_savepoint INT, + IN i_expected INT) +BEGIN + DECLARE table_name VARCHAR(64); + SET @thread_id = i_thread_id; + SET @event_id = 0; + SET @event_name = i_event_name; + SET @state = i_state; + SET @xid_format_id = i_xid_format_id; + SET @xid_gtrid = i_xid_gtrid; + SET @xid_bqual = i_xid_bqual; + SET @xa_state = i_xa_state; + SET @gtid = i_gtid; + SET @access_mode = i_access_mode; + SET @isolation_level = i_isolation_level; + SET @autocommit = i_autocommit; + SET @savepoints = i_savepoints; + SET @rb_savepoint = i_rb_savepoint; + SET @rel_savepoint = i_rel_savepoint; + SET @expected = i_expected; + + # + # Build verification query based upon input parameters + # + + IF i_table = 0 THEN + SET table_name = 'performance_schema.events_transactions_current'; + ELSEIF i_table = 1 THEN + SET table_name = 'performance_schema.events_transactions_history'; + ELSEIF i_table = 2 THEN + SET table_name = 'performance_schema.events_transactions_history_long'; + ELSE + SET table_name = 'performance_schema.events_transactions_history'; + END IF; + + SET @query = CONCAT('SELECT COUNT(*) INTO @actual FROM ', table_name, ' WHERE'); + + IF i_thread_id != 0 THEN + SET @query = CONCAT(@query, ' (thread_id = @thread_id)'); + END IF; + IF i_event_name != '' THEN + SET @query = CONCAT(@query, ' AND (event_name = @event_name)'); + END IF; + IF i_state != '' THEN + SET @query = CONCAT(@query, ' AND (state = @state)'); + END IF; + IF i_xid_format_id != '' THEN + SET @query = CONCAT(@query, ' AND (xid_format_id = @xid_format_id)'); + END IF; + IF i_xid_gtrid != '' THEN + SET @query = CONCAT(@query, ' AND (xid_gtrid = @xid_gtrid)'); + END IF; + IF i_xid_bqual != '' THEN + SET @query = CONCAT(@query, ' AND (xid_bqual = @xid_bqual)'); + END IF; + IF i_xa_state != '' THEN + SET @query = CONCAT(@query, ' AND (xa_state = @xa_state)'); + END IF; + IF i_gtid = 'NULL' THEN + SET @query = CONCAT(@query, ' AND (gtid IS NULL)'); + ELSEIF i_gtid != '' THEN + SET @query = CONCAT(@query, ' AND (gtid = @gtid)'); + END IF; + IF i_access_mode != '' THEN + SET @query = CONCAT(@query, ' AND (access_mode = @access_mode)'); + END IF; + IF i_isolation_level != '' THEN + SET @query = CONCAT(@query, ' AND (isolation_level = @isolation_level)'); + END IF; + IF i_autocommit != '' THEN + SET @query = CONCAT(@query, ' AND (autocommit = @autocommit)'); + END IF; + IF i_savepoints != 0 THEN + SET @query = CONCAT(@query, ' AND (number_of_savepoints = @savepoints)'); + END IF; + IF i_rb_savepoint != 0 THEN + SET @query = CONCAT(@query, ' AND (number_of_rollback_to_savepoint = @rb_savepoint)'); + END IF; + IF i_rel_savepoint != 0 THEN + SET @query = CONCAT(@query, ' AND (number_of_release_savepoint = @rel_savepoint)'); + END IF; + + SET @query = CONCAT(@query, ' ORDER BY event_id;'); + + ## DEBUG ## SELECT * FROM performance_schema.events_transactions_history ORDER BY event_id; + ## SELECT @query AS "QUERY"; + PREPARE stmt1 FROM @query; + EXECUTE stmt1; + DEALLOCATE PREPARE stmt1; + + SELECT LPAD(@actual, 6, ' ') AS "ACTUAL", LPAD(@expected, 8, ' ') AS "EXPECTED"; + + IF @actual != @expected THEN + SELECT "" AS "ERROR: Row count mismatch"; + SELECT @query AS "VERIFIER QUERY:"; + SELECT ""; + + SET @columns = ' LPAD(@thread_id, 9, " ") AS thread_id,'; + SET @columns = CONCAT(@columns, ' LPAD(@event_id, 10, " ") AS "..event_id",'); + SET @columns = CONCAT(@columns, ' RPAD(@event_name, 11, " ") AS "event_name ",'); + SET @columns = CONCAT(@columns, ' RPAD(@state, 11, " ") AS "state ",'); + SET @columns = CONCAT(@columns, ' RPAD(@xid_format_id, 15, " ") AS "xid_format_id ",'); + SET @columns = CONCAT(@columns, ' RPAD(@xid_gtrid, 15, " ") AS "xid_gtrid ",'); + SET @columns = CONCAT(@columns, ' RPAD(@xid_bqual, 15, " ") AS "xid_bqual ",'); + SET @columns = CONCAT(@columns, ' RPAD(@xa_state, 12, " ") AS "xa_state ",'); + SET @columns = CONCAT(@columns, ' RPAD(@gtid, 38, " ") AS "gtid ",'); + SET @columns = CONCAT(@columns, ' RPAD(@access_mode, 11, " ") AS access_mode,'); + SET @columns = CONCAT(@columns, ' RPAD(@isolation_level, 16, " ") AS "isolation_level ",'); + SET @columns = CONCAT(@columns, ' RPAD(@autocommit, 10, " ") AS autocommit,'); + SET @columns = CONCAT(@columns, ' LPAD(@savepoints, 10, " ") AS savepoints,'); + SET @columns = CONCAT(@columns, ' LPAD(@rb_savepoint, 21, " ") AS rollback_to_savepoint,'); + SET @columns = CONCAT(@columns, ' LPAD(@rel_savepoint, 17, " ") AS release_savepoint'); + SET @query2 = CONCAT('SELECT', ' LPAD(@expected, 13, " ") AS ROWS_EXPECTED, ', @columns, ';'); +# SET @query2 = CONCAT('SELECT " " AS EXPECTED, ', @columns, ';'); + PREPARE stmt2 FROM @query2; + EXECUTE stmt2; + DEALLOCATE PREPARE stmt2; + + SET @columns = ' LPAD(thread_id, 9, " ") AS thread_id,'; + SET @columns = CONCAT(@columns, ' LPAD(event_id, 10, " ") AS "..event_id",'); + SET @columns = CONCAT(@columns, ' RPAD(event_name, 11, " ") AS "event_name ",'); + SET @columns = CONCAT(@columns, ' RPAD(state, 11, " ") AS "state ",'); + SET @columns = CONCAT(@columns, ' RPAD(IFNULL(xid_format_id, "NULL"), 15, " ") AS "xid_format_id ",'); + SET @columns = CONCAT(@columns, ' RPAD(IFNULL(xid_gtrid, "NULL"), 15, " ") AS "xid_gtrid ",'); + SET @columns = CONCAT(@columns, ' RPAD(IFNULL(xid_bqual, "NULL"), 15, " ") AS "xid_bqual ",'); + SET @columns = CONCAT(@columns, ' RPAD(IFNULL(xa_state, "NULL"), 12, " ") AS "xa_state ",'); + SET @columns = CONCAT(@columns, ' RPAD(IFNULL(gtid, "NULL"), 38, " ") AS "gtid ",'); + SET @columns = CONCAT(@columns, ' RPAD(access_mode, 11, " ") AS access_mode,'); + SET @columns = CONCAT(@columns, ' RPAD(isolation_level, 16, " ") AS "isolation_level ",'); + SET @columns = CONCAT(@columns, ' RPAD(autocommit, 10, " ") AS autocommit,'); + SET @columns = CONCAT(@columns, ' LPAD(number_of_savepoints, 10, " ") AS savepoints,'); + SET @columns = CONCAT(@columns, ' LPAD(number_of_rollback_to_savepoint, 21, " ") AS rollback_to_savepoint,'); + SET @columns = CONCAT(@columns, ' LPAD(number_of_release_savepoint, 17, " ") AS release_savepoint'); +# SET @query3 = CONCAT('SELECT " " AS "ACTUAL ", ', @columns, ' FROM ', table_name, ' ORDER BY event_id;'); + SET @query3 = CONCAT('SELECT', ' LPAD(@actual, 13, " ") AS "ROWS_ACTUAL ",', @columns, ' FROM ', table_name, ' ORDER BY event_id;'); + PREPARE stmt3 FROM @query3; + EXECUTE stmt3; + DEALLOCATE PREPARE stmt3; + + END IF; +END$$ + +DELIMITER ;$$ + +--enable_result_log + + + diff --git a/mysql-test/suite/perfschema/include/upgrade_check.inc b/mysql-test/suite/perfschema/include/upgrade_check.inc new file mode 100644 index 00000000..c16e90c7 --- /dev/null +++ b/mysql-test/suite/perfschema/include/upgrade_check.inc @@ -0,0 +1,15 @@ +# Routine to be called by pfs_upgrade.test +# $out_file and $err_file must be set within pfs_upgrade.test. +# + +--source include/count_sessions.inc +--exec $MYSQL_UPGRADE --skip-verbose --force > $MYSQLTEST_VARDIR/tmp/out_file 2> $MYSQLTEST_VARDIR/tmp/err_file +--source include/wait_until_count_sessions.inc + +# Verify that mysql_upgrade does not complain about the performance_schema + +--replace_regex /at line [0-9]+/at line ###/ +--cat_file $MYSQLTEST_VARDIR/tmp/err_file +--remove_file $MYSQLTEST_VARDIR/tmp/out_file +--remove_file $MYSQLTEST_VARDIR/tmp/err_file +--remove_file $MYSQLD_DATADIR/mysql_upgrade_info diff --git a/mysql-test/suite/perfschema/include/wait_for_pfs_thread_count.inc b/mysql-test/suite/perfschema/include/wait_for_pfs_thread_count.inc new file mode 100644 index 00000000..49489b49 --- /dev/null +++ b/mysql-test/suite/perfschema/include/wait_for_pfs_thread_count.inc @@ -0,0 +1,9 @@ +# Tests for the performance schema +# This helper can be used to enforce that no threads from previous +# tests are still running, which can impact some test scripts. + +# Wait until there is only one session left, this one. + +let $wait_condition= select count(*) = 1 from performance_schema.threads where `type`='foreground'; +--source include/wait_condition.inc + diff --git a/mysql-test/suite/perfschema/include/wait_till_sleep.inc b/mysql-test/suite/perfschema/include/wait_till_sleep.inc new file mode 100644 index 00000000..5f0f8594 --- /dev/null +++ b/mysql-test/suite/perfschema/include/wait_till_sleep.inc @@ -0,0 +1,19 @@ +# Wait till the action of the connection using the DB = 'mysqltest' or +# 'mysqlsupertest' is finished ( Command = 'Sleep'). + +let $wait_timeout= 10; +let $wait_condition= +SELECT COUNT(*) = 1 +FROM performance_schema.threads +WHERE processlist_db IN ('mysqltest','mysqlsupertest') + AND processlist_command = 'Sleep'; +--source include/wait_condition.inc +if (!$success) +{ + --echo # Error: We did not reach the expected state where processlist_command = 'Sleep' + SELECT * FROM performance_schema.threads + WHERE processlist_db IN ('mysqltest','mysqlsupertest'); + --echo # abort + exit; +} + |