mirror of
https://github.com/MariaDB/server.git
synced 2025-08-07 00:04:31 +03:00
727491b72a
This includes all test changes from
"Changing all cost calculation to be given in milliseconds"
and forwards.
Some of the things that caused changes in the result files:
- As part of fixing tests, I added 'echo' to some comments to be able to
easier find out where things where wrong.
- MATERIALIZED has now a higher cost compared to X than before. Because
of this some MATERIALIZED types have changed to DEPENDEND SUBQUERY.
- Some test cases that required MATERIALIZED to repeat a bug was
changed by adding more rows to force MATERIALIZED to happen.
- 'Filtered' in SHOW EXPLAIN has in many case changed from 100.00 to
something smaller. This is because now filtered also takes into
account the smallest possible ref access and filters, even if they
where not used. Another reason for 'Filtered' being smaller is that
we now also take into account implicit filtering done for subqueries
using FIRSTMATCH.
(main.subselect_no_exists_to_in)
This is caluculated in best_access_path() and stored in records_out.
- Table orders has changed because more accurate costs.
- 'index' and 'ALL' for small tables has changed to use 'range' or
'ref' because of optimizer_scan_setup_cost.
- index can be changed to 'range' as 'range' optimizer assumes we don't
have to read the blocks from disk that range optimizer has already read.
This can be confusing in the case where there is no obvious where clause
but instead there is a hidden 'key_column > NULL' added by the optimizer.
(main.subselect_no_exists_to_in)
- Scan on primary clustered key does not report 'Using Index' anymore
(It's a table scan, not an index scan).
- For derived tables, the number of rows is now 100 instead of 2,
which can be seen in EXPLAIN.
- More tests have "Using index for group by" as the cost of this
optimization is now more correct (lower).
- A primary key could be preferred for a normal key, even if it would
access more rows, as it's faster to do 1 lokoup and 3 'index_next' on a
clustered primary key than one lookup trough a secondary.
(main.stat_tables_innodb)
Notes:
- There was a 4.7% more calls to best_extension_by_limited_search() in
the main.greedy_optimizer test. However examining the test results
it looked that the plans where slightly better (eq_ref where more
chained together) so I assume this is ok.
- I have verified a few test cases where there was notable/unexpected
changes in the plan and in all cases the new optimizer plans where
faster. (main.greedy_optimizer and some others)
212 lines
12 KiB
Plaintext
212 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);
|
|
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 9 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 1 0 50 3 9 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;
|