path: root/mysql-test/suite/sysschema/t/pr_statement_performance_analyzer.test

########### suite/sysschema/t/pr_statement_performance_analyzer.test #############
#                                                                                #
# Testing of of the sys.statement_performance_analyzer() procedure               #
#                                                                                #
# Creation:                                                                      #
# 2015-07-28 jkrogh Implement this test as part of                               #
#                   WL#7804 REPORT FOR SUPPORT                                   #
#                                                                                #
##################################################################################

-- source include/not_embedded.inc
# Performance schema tracks prepared statements separately, and does not
# yet have a summary view that we can use for this view.
# Until then, we disable this test with --ps-protocol
-- source include/no_protocol.inc
# Bug #23290879 - For reasons unknown to man this test fails randomly only on Windows
#-- source include/not_windows.inc

use test;
--disable_warnings
DROP TABLE IF EXISTS t1;
DROP TEMPORARY TABLE IF EXISTS tmp_digests_ini;
DROP VIEW IF EXISTS view_digests;
--enable_warnings

# Create a table
CREATE TABLE t1 (id INT PRIMARY KEY, val int);

# Create a new connection to make the actual changes
# Create the connection now to ensure queries like "SELECT @@`version_comment` LIMIT 1" are excluded
connect (con1,localhost,root,,);
connection con1;
use test;
--let $con1_thread_id= `SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = CONNECTION_ID()`

# Disable instrumentation for all other threads other than the one that will
# my monitored for this test ($con1_thread_id)
connection default;
--disable_result_log
--replace_result $con1_thread_id CON1_THREAD_ID
eval UPDATE performance_schema.threads SET INSTRUMENTED = IF(THREAD_ID = $con1_thread_id, 'YES', 'NO');
CALL sys.ps_setup_enable_consumer('events_statements_history_long');
CALL sys.ps_truncate_all_tables(FALSE);
--enable_result_log

# Start with some initial queries
# Don't rely on the digests being constant across MySQL releases and versions, so find the
# digest for each of the three queries by getting the last event from performance_schema.events_statements_current
# for the con1 connection.
connection con1;
INSERT INTO t1 VALUES (1, 0);
connection default;
--let $wait_condition= SELECT SUBSTRING(SQL_TEXT, 1, 7) = 'INSERT ' FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id AND DIGEST IS NOT NULL
--source include/wait_condition.inc
--let $digest_insert= `SELECT DIGEST FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id ORDER BY EVENT_ID DESC LIMIT 1`
--let $query_insert= `SELECT sys.format_statement(DIGEST_TEXT) FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id ORDER BY EVENT_ID DESC LIMIT 1`
connection con1;
UPDATE t1 SET val = 1 WHERE id = 1;
connection default;
--let $wait_condition= SELECT SUBSTRING(SQL_TEXT, 1, 7) = 'UPDATE ' FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id AND DIGEST IS NOT NULL
--source include/wait_condition.inc
--let $digest_update= `SELECT DIGEST FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id ORDER BY EVENT_ID DESC LIMIT 1`
--let $query_update= `SELECT sys.format_statement(DIGEST_TEXT) FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id ORDER BY EVENT_ID DESC LIMIT 1`
connection con1;
SELECT t1a.* FROM t1 AS t1a LEFT OUTER JOIN (SELECT * FROM t1 AS t1b1 INNER JOIN t1 AS t1b2 USING (id, val)) AS t1b ON t1b.id > t1a.id ORDER BY t1a.val, t1a.id;
connection default;
--let $wait_condition= SELECT SUBSTRING(SQL_TEXT, 1, 7) = 'SELECT ' FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id AND DIGEST IS NOT NULL
--source include/wait_condition.inc
--let $digest_select= `SELECT DIGEST FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id ORDER BY EVENT_ID DESC LIMIT 1`
--let $query_select= `SELECT sys.format_statement(DIGEST_TEXT) FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id ORDER BY EVENT_ID DESC LIMIT 1`

# Enable to debug if some digests are not found
# --output /tmp/digest
# --eval SELECT '$digest_insert' AS DigestInsert, '$digest_update' AS DigestUpdate, '$digest_select' AS DigestSelect
# --output /tmp/digest_text
# --eval SELECT '$query_insert' AS DigestInsert, '$query_update' AS DigestUpdate, '$query_select' AS DigestSelect
# --output /tmp/ps_history
# SELECT THREAD_ID, EVENT_ID, END_EVENT_ID, DIGEST, SQL_TEXT FROM performance_schema.events_statements_history_long ORDER BY EVENT_ID;

# Start collecting data
CALL sys.statement_performance_analyzer('create_tmp', 'test.tmp_digests_ini', NULL);
CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);
CALL sys.statement_performance_analyzer('save', 'test.tmp_digests_ini', NULL);

# Make sure the last_seen times will be different from the SELECT statement's first_seen.
DO SLEEP(1.2);

# Make some more changes
connection con1;
INSERT INTO t1 VALUES (2, 0);
UPDATE t1 SET val = 1 WHERE id = 2;
SELECT t1a.* FROM t1 AS t1a LEFT OUTER JOIN (SELECT * FROM t1 AS t1b1 INNER JOIN t1 AS t1b2 USING (id, val)) AS t1b ON t1b.id > t1a.id ORDER BY t1a.val, t1a.id;
disconnect con1;

# Make the second collection of data and create the output
connection default;
# Make sure all of the queries executing in con1 has been recorded in performance_schema.events_statements_summary_by_digest
# before continuing with the actual tests of pr_statement_performance_analyzer()
--let $wait_condition= SELECT COUNT_STAR = 2 FROM performance_schema.events_statements_summary_by_digest WHERE DIGEST = '$digest_select'
--source include/wait_condition.inc

# Get all values to be used in replacements now to minimize the risk of the
# values disappearing out of the performance_schema tables
--let $o_upd_total_latency= `SELECT total_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'`
--let $o_upd_max_latency= `SELECT max_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'`
--let $o_upd_avg_latency= `SELECT avg_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'`
--let $o_upd_first_seen= `SELECT first_seen FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'`
--let $o_upd_last_seen= `SELECT last_seen FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'`
--let $o_sel_total_latency= `SELECT total_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'`
--let $o_sel_max_latency= `SELECT max_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'`
--let $o_sel_avg_latency= `SELECT avg_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'`
--let $o_sel_first_seen= `SELECT first_seen FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'`
--let $o_sel_last_seen= `SELECT last_seen FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'`
--let $o_ins_total_latency= `SELECT total_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'`
--let $o_ins_max_latency= `SELECT max_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'`
--let $o_ins_avg_latency= `SELECT avg_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'`
--let $o_ins_first_seen= `SELECT first_seen FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'`
--let $o_ins_last_seen= `SELECT last_seen FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'`
--let $o_upd_lock_latency= `SELECT lock_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'`
--let $o_sel_lock_latency= `SELECT lock_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'`
--let $o_ins_lock_latency= `SELECT lock_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'`
--let $d_upd_total_latency= `SELECT sys.format_time(TIMER_WAIT) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_update' ORDER BY EVENT_ID DESC LIMIT 1`
--let $d_sel_total_latency= `SELECT sys.format_time(TIMER_WAIT) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_select' ORDER BY EVENT_ID DESC LIMIT 1`
--let $d_ins_total_latency= `SELECT sys.format_time(TIMER_WAIT) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_insert' ORDER BY EVENT_ID DESC LIMIT 1`
--let $d_upd_tock_latency= `SELECT sys.format_time(LOCK_TIME) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_update' ORDER BY EVENT_ID DESC LIMIT 1`
--let $d_sel_tock_latency= `SELECT sys.format_time(LOCK_TIME) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_select' ORDER BY EVENT_ID DESC LIMIT 1`
--let $d_ins_tock_latency= `SELECT sys.format_time(LOCK_TIME) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_insert' ORDER BY EVENT_ID DESC LIMIT 1`

CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);

# Do a sanity check to ensure we are assuming the queries has the digests they have and that there is nothing else in the events_statements_summary_by_digest than there actually is.
--replace_result $digest_insert DIGEST_INSERT $digest_update DIGEST_UPDATE $digest_select DIGEST_SELECT
--sorted_result
SELECT DIGEST, COUNT_STAR FROM performance_schema.events_statements_summary_by_digest;

# with_runtimes_in_95th_percentile
# It is unknown what the result will be since the execution times for each query are unknown
# So just check that no warning or error occurs
--disable_result_log
CALL sys.statement_performance_analyzer('overall', NULL, 'with_runtimes_in_95th_percentile');
CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_runtimes_in_95th_percentile');
--enable_result_log

# analysis - as there's no control of the various latencies, it may be the same for two or more of the queries.
# So replace_result cannot be used to give it a unique value. Instead just use LATENCY for all rows.
--replace_result $query_insert QUERY_INSERT $query_select QUERY_SELECT $query_update QUERY_UPDATE $digest_insert DIGEST_INSERT $digest_update DIGEST_UPDATE $digest_select DIGEST_SELECT $o_upd_total_latency LATENCY $o_upd_max_latency LATENCY $o_upd_avg_latency LATENCY $o_upd_lock_latency LATENCY $o_upd_first_seen FIRST_SEEN $o_upd_last_seen LAST_SEEN $o_sel_total_latency LATENCY $o_sel_max_latency LATENCY $o_sel_avg_latency LATENCY $o_sel_lock_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN $o_ins_total_latency LATENCY $o_ins_max_latency LATENCY $o_ins_avg_latency LATENCY $o_ins_lock_latency LATENCY $o_ins_first_seen FIRST_SEEN $o_ins_last_seen LAST_SEEN
--sorted_result
CALL sys.statement_performance_analyzer('overall', NULL, 'analysis');

--replace_result $query_insert QUERY_INSERT $query_select QUERY_SELECT $query_update QUERY_UPDATE $digest_insert DIGEST_INSERT $digest_update DIGEST_UPDATE $digest_select DIGEST_SELECT $d_upd_total_latency LATENCY $o_upd_max_latency LATENCY $o_upd_first_seen FIRST_SEEN $o_upd_last_seen LAST_SEEN $d_upd_tock_latency LATENCY $d_sel_total_latency LATENCY $o_sel_max_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN $d_sel_tock_latency LATENCY $d_ins_total_latency LATENCY $o_ins_max_latency LATENCY $o_ins_first_seen FIRST_SEEN $o_ins_last_seen LAST_SEEN $d_ins_tock_latency LATENCY
--sorted_result
CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'analysis');

# Should give an empty result except for the banner generated by the procedure
CALL sys.statement_performance_analyzer('overall', NULL, 'with_errors_or_warnings');
CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_errors_or_warnings');

--replace_column 4 LATENCY
--replace_result $query_select QUERY_SELECT $digest_select DIGEST_SELECT $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN $digest_update DIGEST_UPDATE $query_update QUERY_UPDATE $o_upd_first_seen FIRST_SEEN $o_upd_last_seen LAST_SEEN
CALL sys.statement_performance_analyzer('overall', NULL, 'with_full_table_scans');
--replace_result $query_select QUERY_SELECT $digest_select DIGEST_SELECT $d_sel_total_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN 
CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_full_table_scans');

--replace_result $query_select QUERY_SELECT $digest_select DIGEST_SELECT $o_sel_total_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN
CALL sys.statement_performance_analyzer('overall', NULL, 'with_sorting');
--replace_result $query_select QUERY_SELECT $digest_select DIGEST_SELECT $d_sel_total_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN
CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_sorting');

--replace_result $query_select QUERY_SELECT $digest_select DIGEST_SELECT $o_sel_total_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN
CALL sys.statement_performance_analyzer('overall', NULL, 'with_temp_tables');
--replace_result $query_select QUERY_SELECT $digest_select DIGEST_SELECT $d_sel_total_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN
CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_temp_tables');

# Try a custom view
# Sort by the query, then we know the order will be INSERT, SELECT, UPDATE
CREATE VIEW test.view_digests AS
SELECT sys.format_statement(DIGEST_TEXT) AS query,
       SCHEMA_NAME AS db,
       COUNT_STAR AS exec_count,
       sys.format_time(SUM_TIMER_WAIT) AS total_latency,
       sys.format_time(AVG_TIMER_WAIT) AS avg_latency,
       ROUND(IFNULL(SUM_ROWS_SENT / NULLIF(COUNT_STAR, 0), 0)) AS rows_sent_avg,
       ROUND(IFNULL(SUM_ROWS_EXAMINED / NULLIF(COUNT_STAR, 0), 0)) AS rows_examined_avg,
       ROUND(IFNULL(SUM_ROWS_AFFECTED / NULLIF(COUNT_STAR, 0), 0)) AS rows_affected_avg,
       DIGEST AS digest
  FROM performance_schema.events_statements_summary_by_digest
 ORDER BY SUBSTRING(query, 1, 6);
SET @sys.statement_performance_analyzer.view = 'test.view_digests';
--replace_result $query_insert QUERY_INSERT $query_select QUERY_SELECT $query_update QUERY_UPDATE $digest_insert DIGEST_INSERT $digest_update DIGEST_UPDATE $digest_select DIGEST_SELECT $o_upd_total_latency LATENCY $o_upd_avg_latency LATENCY $o_sel_total_latency LATENCY $o_sel_avg_latency LATENCY $o_ins_total_latency LATENCY $o_ins_avg_latency LATENCY
CALL sys.statement_performance_analyzer('overall', NULL, 'custom');
--replace_result $query_insert QUERY_INSERT $query_select QUERY_SELECT $query_update QUERY_UPDATE $digest_insert DIGEST_INSERT $digest_update DIGEST_UPDATE $digest_select DIGEST_SELECT $d_upd_total_latency LATENCY $d_sel_total_latency LATENCY $d_ins_total_latency LATENCY
CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'custom');

# Verify that setting a limit works
SET @sys.statement_performance_analyzer.limit = 2;
--replace_result $query_insert QUERY_INSERT $query_select QUERY_SELECT $digest_insert DIGEST_INSERT $digest_select DIGEST_SELECT $o_ins_total_latency LATENCY $o_ins_avg_latency LATENCY $o_sel_total_latency LATENCY $o_sel_avg_latency LATENCY
CALL sys.statement_performance_analyzer('overall', NULL, 'custom');

# Test for error conditions - some of the errors depend on the sql_mode so set it explicitly
# Which sql_modes are deprecated depends on the release, so disable warnings while setting the sql_mode


# Use non-strict sql_mode - keep NO_AUTO_CREATE_USER as this sql_mode will be mandatory in the future:
SET SESSION sql_mode = 'NO_AUTO_CREATE_USER';
# Ask for a non-supported action - since strict more is not used, this will give the error: ERROR 1644 (45000): Unknown action: ''
# Note: the action passed to the procedure is actually lost because it's truncated.
--error ER_SIGNAL_EXCEPTION
CALL sys.statement_performance_analyzer('do magic', NULL, NULL);


# Use 5.7.9+ default:
--disable_warnings
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
--enable_warnings
# Ask for a non-supported action - since strict mode is used, this will give the error: ERROR 1265 (01000): Data truncated for column 'in_action' at row 1
--error 1265
CALL sys.statement_performance_analyzer('do magic', NULL, NULL);
# Try to create reserved table name
-- error S45000
CALL sys.statement_performance_analyzer('create_tmp', 'sys.tmp_digests', NULL);
-- error S45000
CALL sys.statement_performance_analyzer('create_tmp', 'sys.tmp_digests_delta', NULL);
-- error S45000
CALL sys.statement_performance_analyzer('create_tmp', 'tmp_digests', NULL);
# This should succeed
CALL sys.statement_performance_analyzer('create_tmp', 'test.tmp_digests', NULL);
CREATE TABLE test.tmp_unsupported LIKE test.tmp_digests_ini;
# Try to create a temporary table that already exists
-- error S45000
CALL sys.statement_performance_analyzer('create_tmp', 'test.tmp_digests_ini', NULL);
# Try to create a base table that already exists
-- error S45000
CALL sys.statement_performance_analyzer('create_table', 'test.tmp_digests_ini', NULL);
-- error S45000
CALL sys.statement_performance_analyzer('create_table', 'test.tmp_unsupported', NULL);
# Verify that the sanity check for the input table e.g. for saving snapshots works
ALTER TABLE test.tmp_unsupported ADD COLUMN myvar int DEFAULT 0;
-- error S45000
CALL sys.statement_performance_analyzer('save', 'test.tmp_unsupported', NULL);
# Try to create a snapshot or overall output with the wrong in_table
-- error S45000
CALL sys.statement_performance_analyzer('snapshot', 'test.new_table', NULL);
-- error S45000
CALL sys.statement_performance_analyzer('overall', 'test.new_table', 'analysis');
# Try to create a delta output or save a snapshot specifying a non-existing table.
-- error S45000
CALL sys.statement_performance_analyzer('delta', 'test.new_table', 'analysis');
-- error S45000
CALL sys.statement_performance_analyzer('save', 'test.new_table', NULL);
# Verify custom views doesn't work without the user variable set
SET @sys.statement_performance_analyzer.view = NULL;
DELETE FROM sys.sys_config WHERE variable = 'statement_performance_analyzer.view';
-- error S45000
CALL sys.statement_performance_analyzer('overall', NULL, 'custom');
# Set the custom view to a regular table - should not work
SET @sys.statement_performance_analyzer.view = 'test.tmp_unsupported';
-- error S45000
CALL sys.statement_performance_analyzer('overall', NULL, 'custom');


# Remove the temporary tables created by the procedure
# First ensure the tables actually exists (to avoid passing the test if the table names are changed)
CALL sys.table_exists('sys', 'tmp_digests', @exists);
SELECT @exists;
CALL sys.table_exists('sys', 'tmp_digests_delta', @exists);
SELECT @exists;
CALL sys.statement_performance_analyzer('cleanup', NULL, NULL);
# Verify the internal tables really were removed
-- error ER_BAD_TABLE_ERROR
DROP TEMPORARY TABLE sys.tmp_digests;
-- error ER_BAD_TABLE_ERROR
DROP TEMPORARY TABLE sys.tmp_digests_delta;

# An attempt to create a delta view should fail now as there's no longer an existing snapshot.
-- error S45000
CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'analysis');
# Ensure no delta table was created.
-- error ER_BAD_TABLE_ERROR
DROP TEMPORARY TABLE sys.tmp_digests_delta;

# Try and use a table.db name > 129
SET @identifier := REPEAT('a', 65);
-- error 1406
CALL sys.statement_performance_analyzer('snapshot', CONCAT(@identifier, '.', @identifier), NULL);

# Clean up
DROP TEMPORARY TABLE test.tmp_digests_ini;
DROP TEMPORARY TABLE test.tmp_digests;
DROP TABLE test.tmp_unsupported;
DROP TABLE test.t1;
DROP VIEW view_digests;
SET @identifier := NULL;

SET SESSION sql_mode = @@global.sql_mode;
SET @sys.statement_performance_analyzer.limit = NULL;
SET @sys.statement_performance_analyzer.view = NULL;
--source ../include/ps_setup_consumers_cleanup.inc
--source ../include/ps_threads_cleanup.inc
--source ../include/sys_config_cleanup.inc