200 lines
12 KiB
Plaintext
200 lines
12 KiB
Plaintext
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);
|
|
use test;
|
|
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);
|
|
INSERT INTO t1 VALUES (1, 0);
|
|
UPDATE t1 SET val = 1 WHERE id = 1;
|
|
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
|
|
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);
|
|
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
|
|
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 12 6 0 0 0 0 3 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 12 2 6 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 2 0 3 2 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 6 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 6 0 DIGEST_SELECT
|
|
SET SESSION sql_mode = '';
|
|
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_ENGINE_SUBSTITUTION';
|
|
CALL sys.statement_performance_analyzer('do magic', NULL, NULL);
|
|
ERROR 01000: Data truncated for column 'in_action' at row 1
|
|
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 1
|
|
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;
|