1use test; 2DROP TABLE IF EXISTS t1; 3DROP TEMPORARY TABLE IF EXISTS tmp_digests_ini; 4DROP VIEW IF EXISTS view_digests; 5CREATE TABLE t1 (id INT PRIMARY KEY, val int); 6use test; 7UPDATE performance_schema.threads SET INSTRUMENTED = IF(THREAD_ID = CON1_THREAD_ID, 'YES', 'NO'); 8CALL sys.ps_setup_enable_consumer('events_statements_history_long'); 9CALL sys.ps_truncate_all_tables(FALSE); 10INSERT INTO t1 VALUES (1, 0); 11UPDATE t1 SET val = 1 WHERE id = 1; 12SELECT 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; 13id val 141 1 15CALL sys.statement_performance_analyzer('create_tmp', 'test.tmp_digests_ini', NULL); 16CALL sys.statement_performance_analyzer('snapshot', NULL, NULL); 17CALL sys.statement_performance_analyzer('save', 'test.tmp_digests_ini', NULL); 18DO SLEEP(1.2); 19INSERT INTO t1 VALUES (2, 0); 20UPDATE t1 SET val = 1 WHERE id = 2; 21SELECT 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; 22id val 231 1 242 1 25CALL sys.statement_performance_analyzer('snapshot', NULL, NULL); 26SELECT DIGEST, COUNT_STAR FROM performance_schema.events_statements_summary_by_digest; 27DIGEST COUNT_STAR 28DIGEST_INSERT 2 29DIGEST_SELECT 2 30DIGEST_UPDATE 2 31CALL sys.statement_performance_analyzer('overall', NULL, 'with_runtimes_in_95th_percentile'); 32CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_runtimes_in_95th_percentile'); 33CALL sys.statement_performance_analyzer('overall', NULL, 'analysis'); 34Next Output 35QUERY_INSERT test 2 0 0 LATENCY LATENCY LATENCY LATENCY 0 0 0 0 2 1 0 0 0 0 DIGEST_INSERT FIRST_SEEN LAST_SEEN 36QUERY_SELECT test * 2 0 0 LATENCY LATENCY LATENCY LATENCY 3 2 15 8 0 0 2 0 3 0 DIGEST_SELECT FIRST_SEEN LAST_SEEN 37QUERY_UPDATE test 2 0 0 LATENCY LATENCY LATENCY LATENCY 0 0 2 1 2 1 0 0 0 0 DIGEST_UPDATE FIRST_SEEN LAST_SEEN 38Top 100 Queries Ordered by Total Latency 39query db full_scan exec_count err_count warn_count total_latency max_latency avg_latency lock_latency rows_sent rows_sent_avg rows_examined rows_examined_avg rows_affected rows_affected_avg tmp_tables tmp_disk_tables rows_sorted sort_merge_passes digest first_seen last_seen 40CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'analysis'); 41Next Output 42QUERY_INSERT test 1 0 0 LATENCY LATENCY LATENCY LATENCY 0 0 0 0 1 1 0 0 0 0 DIGEST_INSERT FIRST_SEEN LAST_SEEN 43QUERY_SELECT test * 1 0 0 LATENCY LATENCY LATENCY LATENCY 2 2 10 10 0 0 1 0 2 0 DIGEST_SELECT FIRST_SEEN LAST_SEEN 44QUERY_UPDATE test 1 0 0 LATENCY LATENCY LATENCY LATENCY 0 0 1 1 1 1 0 0 0 0 DIGEST_UPDATE FIRST_SEEN LAST_SEEN 45Top 100 Queries Ordered by Total Latency 46query db full_scan exec_count err_count warn_count total_latency max_latency avg_latency lock_latency rows_sent rows_sent_avg rows_examined rows_examined_avg rows_affected rows_affected_avg tmp_tables tmp_disk_tables rows_sorted sort_merge_passes digest first_seen last_seen 47CALL sys.statement_performance_analyzer('overall', NULL, 'with_errors_or_warnings'); 48Next Output 49Top 100 Queries with Errors 50query db exec_count errors error_pct warnings warning_pct first_seen last_seen digest 51CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_errors_or_warnings'); 52Next Output 53Top 100 Queries with Errors 54query db exec_count errors error_pct warnings warning_pct first_seen last_seen digest 55CALL sys.statement_performance_analyzer('overall', NULL, 'with_full_table_scans'); 56Next Output 57Top 100 Queries with Full Table Scan 58query db exec_count total_latency no_index_used_count no_good_index_used_count no_index_used_pct rows_sent rows_examined rows_sent_avg rows_examined_avg first_seen last_seen digest 59QUERY_SELECT test 2 LATENCY 2 0 100 3 15 2 8 FIRST_SEEN LAST_SEEN DIGEST_SELECT 60CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_full_table_scans'); 61Next Output 62Top 100 Queries with Full Table Scan 63query db exec_count total_latency no_index_used_count no_good_index_used_count no_index_used_pct rows_sent rows_examined rows_sent_avg rows_examined_avg first_seen last_seen digest 64QUERY_SELECT test 1 LATENCY 1 0 100 2 10 2 10 FIRST_SEEN LAST_SEEN DIGEST_SELECT 65CALL sys.statement_performance_analyzer('overall', NULL, 'with_sorting'); 66Next Output 67Top 100 Queries with Sorting 68query db exec_count total_latency sort_merge_passes avg_sort_merges sorts_using_scans sort_using_range rows_sorted avg_rows_sorted first_seen last_seen digest 69QUERY_SELECT test 2 LATENCY 0 0 2 0 3 2 FIRST_SEEN LAST_SEEN DIGEST_SELECT 70CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_sorting'); 71Next Output 72Top 100 Queries with Sorting 73query db exec_count total_latency sort_merge_passes avg_sort_merges sorts_using_scans sort_using_range rows_sorted avg_rows_sorted first_seen last_seen digest 74QUERY_SELECT test 1 LATENCY 0 0 1 0 2 2 FIRST_SEEN LAST_SEEN DIGEST_SELECT 75CALL sys.statement_performance_analyzer('overall', NULL, 'with_temp_tables'); 76Next Output 77Top 100 Queries with Internal Temporary Tables 78query db exec_count total_latency memory_tmp_tables disk_tmp_tables avg_tmp_tables_per_query tmp_tables_to_disk_pct first_seen last_seen digest 79QUERY_SELECT test 2 LATENCY 2 0 1 0 FIRST_SEEN LAST_SEEN DIGEST_SELECT 80CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_temp_tables'); 81Next Output 82Top 100 Queries with Internal Temporary Tables 83query db exec_count total_latency memory_tmp_tables disk_tmp_tables avg_tmp_tables_per_query tmp_tables_to_disk_pct first_seen last_seen digest 84QUERY_SELECT test 1 LATENCY 1 0 1 0 FIRST_SEEN LAST_SEEN DIGEST_SELECT 85CREATE VIEW test.view_digests AS 86SELECT sys.format_statement(DIGEST_TEXT) AS query, 87SCHEMA_NAME AS db, 88COUNT_STAR AS exec_count, 89sys.format_time(SUM_TIMER_WAIT) AS total_latency, 90sys.format_time(AVG_TIMER_WAIT) AS avg_latency, 91ROUND(IFNULL(SUM_ROWS_SENT / NULLIF(COUNT_STAR, 0), 0)) AS rows_sent_avg, 92ROUND(IFNULL(SUM_ROWS_EXAMINED / NULLIF(COUNT_STAR, 0), 0)) AS rows_examined_avg, 93ROUND(IFNULL(SUM_ROWS_AFFECTED / NULLIF(COUNT_STAR, 0), 0)) AS rows_affected_avg, 94DIGEST AS digest 95FROM performance_schema.events_statements_summary_by_digest 96ORDER BY SUBSTRING(query, 1, 6); 97SET @sys.statement_performance_analyzer.view = 'test.view_digests'; 98CALL sys.statement_performance_analyzer('overall', NULL, 'custom'); 99Next Output 100Top 100 Queries Using Custom View 101query db exec_count total_latency avg_latency rows_sent_avg rows_examined_avg rows_affected_avg digest 102QUERY_INSERT test 2 LATENCY LATENCY 0 0 1 DIGEST_INSERT 103QUERY_SELECT test 2 LATENCY LATENCY 2 8 0 DIGEST_SELECT 104QUERY_UPDATE test 2 LATENCY LATENCY 0 1 1 DIGEST_UPDATE 105CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'custom'); 106Next Output 107Top 100 Queries Using Custom View 108query db exec_count total_latency avg_latency rows_sent_avg rows_examined_avg rows_affected_avg digest 109QUERY_INSERT test 1 LATENCY LATENCY 0 0 1 DIGEST_INSERT 110QUERY_SELECT test 1 LATENCY LATENCY 2 10 0 DIGEST_SELECT 111QUERY_UPDATE test 1 LATENCY LATENCY 0 1 1 DIGEST_UPDATE 112SET @sys.statement_performance_analyzer.limit = 2; 113CALL sys.statement_performance_analyzer('overall', NULL, 'custom'); 114Next Output 115Top 2 Queries Using Custom View 116query db exec_count total_latency avg_latency rows_sent_avg rows_examined_avg rows_affected_avg digest 117QUERY_INSERT test 2 LATENCY LATENCY 0 0 1 DIGEST_INSERT 118QUERY_SELECT test 2 LATENCY LATENCY 2 8 0 DIGEST_SELECT 119SET SESSION sql_mode = 'NO_AUTO_CREATE_USER'; 120CALL sys.statement_performance_analyzer('do magic', NULL, NULL); 121ERROR 45000: Unknown action. Supported actions are: cleanup, create_table, create_tmp, delta, overall, save, snapshot 122SET 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'; 123CALL sys.statement_performance_analyzer('do magic', NULL, NULL); 124ERROR 01000: Data truncated for column 'in_action' at row 1 125CALL sys.statement_performance_analyzer('create_tmp', 'sys.tmp_digests', NULL); 126ERROR 45000: Invalid value for in_table: `sys`.`tmp_digests` is reserved table name. 127CALL sys.statement_performance_analyzer('create_tmp', 'sys.tmp_digests_delta', NULL); 128ERROR 45000: Invalid value for in_table: `sys`.`tmp_digests_delta` is reserved table name. 129CALL sys.statement_performance_analyzer('create_tmp', 'tmp_digests', NULL); 130ERROR 45000: Invalid value for in_table: `sys`.`tmp_digests` is reserved table name. 131CALL sys.statement_performance_analyzer('create_tmp', 'test.tmp_digests', NULL); 132CREATE TABLE test.tmp_unsupported LIKE test.tmp_digests_ini; 133CALL sys.statement_performance_analyzer('create_tmp', 'test.tmp_digests_ini', NULL); 134ERROR 45000: Cannot create the table `test`.`tmp_digests_ini` as it already exists. 135CALL sys.statement_performance_analyzer('create_table', 'test.tmp_digests_ini', NULL); 136ERROR 45000: Cannot create the table `test`.`tmp_digests_ini` as it already exists as a temporary table. 137CALL sys.statement_performance_analyzer('create_table', 'test.tmp_unsupported', NULL); 138ERROR 45000: Cannot create the table `test`.`tmp_unsupported` as it already exists. 139ALTER TABLE test.tmp_unsupported ADD COLUMN myvar int DEFAULT 0; 140CALL sys.statement_performance_analyzer('save', 'test.tmp_unsupported', NULL); 141ERROR 45000: The table `test`.`tmp_unsupported` has the wrong definition. 142CALL sys.statement_performance_analyzer('snapshot', 'test.new_table', NULL); 143ERROR 45000: The snapshot action requires in_table to be NULL, NOW() or specify an existing table. The table ...`.`new_table` does not exist. 144CALL sys.statement_performance_analyzer('overall', 'test.new_table', 'analysis'); 145ERROR 45000: The overall action requires in_table to be NULL, NOW() or specify an existing table. The table ...`.`new_table` does not exist. 146CALL sys.statement_performance_analyzer('delta', 'test.new_table', 'analysis'); 147ERROR 45000: The delta action requires in_table to be an existing table. The table `test`.`new_table` does not exist. 148CALL sys.statement_performance_analyzer('save', 'test.new_table', NULL); 149ERROR 45000: The save action requires in_table to be an existing table. The table `test`.`new_table` does not exist. 150SET @sys.statement_performance_analyzer.view = NULL; 151DELETE FROM sys.sys_config WHERE variable = 'statement_performance_analyzer.view'; 152CALL sys.statement_performance_analyzer('overall', NULL, 'custom'); 153Next Output 154Top 2 Queries Using Custom View 155ERROR 45000: The @sys.statement_performance_analyzer.view user variable must be set with the view or query to use. 156SET @sys.statement_performance_analyzer.view = 'test.tmp_unsupported'; 157CALL sys.statement_performance_analyzer('overall', NULL, 'custom'); 158Next Output 159Top 2 Queries Using Custom View 160ERROR 45000: The @sys.statement_performance_analyzer.view user variable is set but specified neither an existing view nor a query. 161CALL sys.table_exists('sys', 'tmp_digests', @exists); 162SELECT @exists; 163@exists 164TEMPORARY 165CALL sys.table_exists('sys', 'tmp_digests_delta', @exists); 166SELECT @exists; 167@exists 168TEMPORARY 169CALL sys.statement_performance_analyzer('cleanup', NULL, NULL); 170DROP TEMPORARY TABLE sys.tmp_digests; 171ERROR 42S02: Unknown table 'sys.tmp_digests' 172DROP TEMPORARY TABLE sys.tmp_digests_delta; 173ERROR 42S02: Unknown table 'sys.tmp_digests_delta' 174CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'analysis'); 175ERROR 45000: An existing snapshot generated with the statement_performance_analyzer() must exist. 176DROP TEMPORARY TABLE sys.tmp_digests_delta; 177ERROR 42S02: Unknown table 'sys.tmp_digests_delta' 178SET @identifier := REPEAT('a', 65); 179CALL sys.statement_performance_analyzer('snapshot', CONCAT(@identifier, '.', @identifier), NULL); 180ERROR 22001: Data too long for column 'in_table' at row 1 181DROP TEMPORARY TABLE test.tmp_digests_ini; 182DROP TEMPORARY TABLE test.tmp_digests; 183DROP TABLE test.tmp_unsupported; 184DROP TABLE test.t1; 185DROP VIEW view_digests; 186SET @identifier := NULL; 187SET SESSION sql_mode = @@global.sql_mode; 188SET @sys.statement_performance_analyzer.limit = NULL; 189SET @sys.statement_performance_analyzer.view = NULL; 190UPDATE performance_schema.setup_consumers SET enabled = 'YES'; 191UPDATE performance_schema.threads SET instrumented = 'YES'; 192SET @sys.ignore_sys_config_triggers := true; 193DELETE FROM sys.sys_config; 194INSERT IGNORE INTO sys.sys_config (variable, value) VALUES 195('statement_truncate_len', 64), 196('statement_performance_analyzer.limit', 100), 197('statement_performance_analyzer.view', NULL), 198('diagnostics.allow_i_s_tables', 'OFF'), 199('diagnostics.include_raw', 'OFF'), 200('ps_thread_trx_info.max_length', 65535); 201SET @sys.ignore_sys_config_triggers := NULL; 202