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