diff options
Diffstat (limited to 'mysql-test/suite/perfschema/include')
31 files changed, 4269 insertions, 1 deletions
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 00000000000..761540b4fc4 --- /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 00000000000..16fc136f842 --- /dev/null +++ b/mysql-test/suite/perfschema/include/connection_load.inc @@ -0,0 +1,210 @@ +# 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 +--disconnect con4b +--disconnect con4c +--disconnect con5a +--disconnect con5b +--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 00000000000..da57b6dd388 --- /dev/null +++ b/mysql-test/suite/perfschema/include/connection_setup.inc @@ -0,0 +1,109 @@ +# 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 + +grant ALL on *.* to user1@localhost; +grant ALL on *.* to user2@localhost; +grant ALL on *.* to user3@localhost; +grant ALL on *.* to user4@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/digest_cleanup.inc b/mysql-test/suite/perfschema/include/digest_cleanup.inc new file mode 100644 index 00000000000..99dc41e660f --- /dev/null +++ b/mysql-test/suite/perfschema/include/digest_cleanup.inc @@ -0,0 +1,11 @@ +--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 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 00000000000..34c49590217 --- /dev/null +++ b/mysql-test/suite/perfschema/include/digest_execution.inc @@ -0,0 +1,86 @@ +--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 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 t11; +create table t11 (c char(4)); +--ERROR ER_TABLE_EXISTS_ERROR +create table t11 (c char(4)); +insert into t11 values("MySQL"); 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 00000000000..7145bcfa870 --- /dev/null +++ b/mysql-test/suite/perfschema/include/digest_setup.inc @@ -0,0 +1,21 @@ +# 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); +--enable_warnings + 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 00000000000..c50394f748b --- /dev/null +++ b/mysql-test/suite/perfschema/include/event_aggregate_cleanup.inc @@ -0,0 +1,57 @@ +# 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_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; + +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 00000000000..75069f4b603 --- /dev/null +++ b/mysql-test/suite/perfschema/include/event_aggregate_load.inc @@ -0,0 +1,883 @@ +# 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_accounts; +execute dump_users; +execute dump_hosts; + +# Notes about this test +# +# Each connect causes 2 wait/synch/mutex/sql/LOCK_connection_count events: +# - 1 in mysqld.cc, create_new_thread(), for the main thread +# - 1 in sql_connect.cc, check_user(), for the connected thread +# The main thread does not count for BY_ACCOUNT / BY_HOST. +# The user thread does count for BY_ACCOUNT, BY_HOST +# +# Each get_lock() causes 1 wait/synch/mutex/sql/LOCK_user_locks +# +# 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 =================="; +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_accounts; +execute dump_users; +execute dump_hosts; + +--connection con1 + +select get_lock("marker", 10); +select release_lock("marker"); +insert into test.t1 values ("marker"); + +echo "================== con1 marker =================="; + +--connection default + +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_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 =================="; +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_accounts; +execute dump_users; +execute dump_hosts; + +--connection con2 + +select get_lock("marker", 10); +select release_lock("marker"); +insert into test.t1 values ("marker"); + +echo "================== con2 marker =================="; + +--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_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_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 =================="; +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_accounts; +execute dump_users; +execute dump_hosts; + +--connection con3 + +select get_lock("marker", 10); +select release_lock("marker"); +insert into test.t1 values ("marker"); + +echo "================== con3 marker =================="; + +--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_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_accounts; +execute dump_users; +execute dump_hosts; + +connect (con4, localhost, user4, , ); + +echo "================== con4 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= '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_accounts; +execute dump_users; +execute dump_hosts; + +--connection con4 + +select get_lock("marker", 10); +select release_lock("marker"); +insert into test.t1 values ("marker"); + +echo "================== con4 marker =================="; + +--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_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_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 "================== con1 disconnected =================="; + +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_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 =================="; +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_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 =================="; +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_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 =================="; +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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.accounts; + +echo "================== ACCOUNTS 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_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.users; + +echo "================== USERS 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_accounts; +execute dump_users; +execute dump_hosts; + +truncate performance_schema.hosts; + +echo "================== HOSTS 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_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 00000000000..47488403c1f --- /dev/null +++ b/mysql-test/suite/perfschema/include/event_aggregate_setup.inc @@ -0,0 +1,347 @@ +# 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 +# +# Globally: +# - events_waits_summary_global_by_event_name +# - events_stages_summary_global_by_event_name +# - events_statements_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 + +grant ALL on *.* to user1@localhost; +grant ALL on *.* to user2@localhost; +grant ALL on *.* to user3@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 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_user_locks', + 'wait/synch/rwlock/sql/LOCK_grant', + 'wait/io/file/sql/query_log'); + +update performance_schema.setup_instruments set enabled='YES', timed='YES' + where name in ('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/com/', + 'statement/com/Query', + 'statement/com/Quit', + 'statement/com/error'); + +# 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; + +--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_user_locks', + '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_user_locks', + '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_user_locks', + '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_user_locks', + '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_user_locks', + '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_user_locks', + '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/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/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/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/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/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') + 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') + 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') + 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') + 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') + 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/hostcache_dump.inc b/mysql-test/suite/perfschema/include/hostcache_dump.inc new file mode 100644 index 00000000000..7f456a0c499 --- /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/no_protocol.inc b/mysql-test/suite/perfschema/include/no_protocol.inc new file mode 100644 index 00000000000..451c22f62e3 --- /dev/null +++ b/mysql-test/suite/perfschema/include/no_protocol.inc @@ -0,0 +1,10 @@ +# Tests for the performance schema + +# The file with expected results fits only to a run without +# ps-protocol/sp-protocol/cursor-protocol/view-protocol. +if (`SELECT $PS_PROTOCOL + $SP_PROTOCOL + $CURSOR_PROTOCOL + + $VIEW_PROTOCOL > 0`) +{ + --skip Test requires: ps-protocol/sp-protocol/cursor-protocol/view-protocol disabled +} + 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 00000000000..0d4a864940c --- /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/schema.inc b/mysql-test/suite/perfschema/include/schema.inc new file mode 100644 index 00000000000..da587eca481 --- /dev/null +++ b/mysql-test/suite/perfschema/include/schema.inc @@ -0,0 +1,60 @@ +# 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_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_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 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_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/socket_check1.inc b/mysql-test/suite/perfschema/include/socket_check1.inc new file mode 100644 index 00000000000..29b00fc6abb --- /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 00000000000..ba81c37d5df --- /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_timeou= 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 00000000000..75c4d4808cb --- /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 00000000000..dd077a12c7a --- /dev/null +++ b/mysql-test/suite/perfschema/include/socket_ipv6.inc @@ -0,0 +1,113 @@ +#============================================================================== +# 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_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_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_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_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 00000000000..10258cadb1b --- /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 00000000000..64ee19c3d9b --- /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 00000000000..c3c9d911dbd --- /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 00000000000..3558e43652e --- /dev/null +++ b/mysql-test/suite/perfschema/include/stage_setup.inc @@ -0,0 +1,134 @@ +# 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 + +grant ALL on *.* to user1@localhost; +grant ALL on *.* to user2@localhost; +grant ALL on *.* to user3@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; + 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 + 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 index 88f98f0c063..69a9c47063b 100644 --- a/mysql-test/suite/perfschema/include/start_server_common.inc +++ b/mysql-test/suite/perfschema/include/start_server_common.inc @@ -4,29 +4,64 @@ show databases; select count(*) from performance_schema.performance_timers; select count(*) from performance_schema.setup_consumers; -select count(*) > 0 from performance_schema.setup_instruments; +# 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 +select count(*) > 3 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_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_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.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.socket_instances; +select * from performance_schema.socket_summary_by_instance; +select * from performance_schema.socket_summary_by_event_name; +select * from performance_schema.hosts; 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.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.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; --enable_result_log # This has a stable output, printing the result: 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 00000000000..11b354d8b3e --- /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 00000000000..b72220c58bd --- /dev/null +++ b/mysql-test/suite/perfschema/include/table_aggregate_load.inc @@ -0,0 +1,540 @@ +# Tests for the performance schema +# + +# See comments in include/table_aggregate_setup.inc + +# 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 +show variables 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, , ); + +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 =================="; +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 where b=5; +select * from test.t2 where b=5; +select * from test.t3 where b=5; + +echo "================== con1 marker =================="; + +--connection default + +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_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, , ); + +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 =================="; +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 where b=5; +select * from test.t2 where b=5; +select * from test.t3 where b=5; + +echo "================== con2 marker =================="; + +--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, , ); + +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 =================="; +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 where b=5; +select * from test.t2 where b=5; +select * from test.t3 where b=5; + +echo "================== con3 marker =================="; + +--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, , ); + +echo "================== con4 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= '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_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 where b=5; +select * from test.t2 where b=5; +select * from test.t3 where b=5; + +echo "================== con4 marker =================="; + +--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; + +echo "================== con1 marker =================="; + +--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 "================== con1 disconnected =================="; + +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 "================== con2 disconnected =================="; + +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 "================== con3 disconnected =================="; + +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 "================== con4 disconnected =================="; + +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 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 00000000000..8efdc8d2f24 --- /dev/null +++ b/mysql-test/suite/perfschema/include/table_aggregate_setup.inc @@ -0,0 +1,267 @@ +# 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 +grant ALL on *.* to user1@localhost; +grant ALL on *.* to user2@localhost; +grant ALL on *.* to user3@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, 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_delayed, 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 00000000000..4fe2fd9dfb3 --- /dev/null +++ b/mysql-test/suite/perfschema/include/table_io_basic_dml.inc @@ -0,0 +1,36 @@ +# 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. +# + +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; + 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 00000000000..02957903762 --- /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 00000000000..789f7135a1a --- /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 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 00000000000..b4c4b5fab69 --- /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/wait_for_pfs_thread_count.inc b/mysql-test/suite/perfschema/include/wait_for_pfs_thread_count.inc new file mode 100644 index 00000000000..a2ed41d9d1b --- /dev/null +++ b/mysql-test/suite/perfschema/include/wait_for_pfs_thread_count.inc @@ -0,0 +1,20 @@ +# 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 information_schema.processlist; +--source include/wait_condition.inc + +# Threads are removed from information_schema.processlist +# very soon, but continue to execute in the server, +# before finally be removed from performance_schema.threads. +# Because instrumentation is optional, we use "<=" here. + +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 00000000000..5f0f85948c7 --- /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; +} + |