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