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