Debian Codegraph @

mysql-test/suite/sysschema/r/pr_statement_performance_analyzer.result

raw
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;