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