diff options
Diffstat (limited to 'scripts/sys_schema')
52 files changed, 435 insertions, 250 deletions
diff --git a/scripts/sys_schema/CMakeLists.txt b/scripts/sys_schema/CMakeLists.txt index ccb268cc..26256c5c 100644 --- a/scripts/sys_schema/CMakeLists.txt +++ b/scripts/sys_schema/CMakeLists.txt @@ -38,6 +38,7 @@ ${CMAKE_CURRENT_SOURCE_DIR}/views/i_s/schema_object_overview.sql ${CMAKE_CURRENT_SOURCE_DIR}/views/i_s/schema_auto_increment_columns.sql ${CMAKE_CURRENT_SOURCE_DIR}/views/i_s/x_schema_flattened_keys.sql ${CMAKE_CURRENT_SOURCE_DIR}/views/i_s/schema_redundant_indexes.sql +${CMAKE_CURRENT_SOURCE_DIR}/views/i_s/privileges_by_table_by_level.sql ${CMAKE_CURRENT_SOURCE_DIR}/views/p_s/ps_check_lost_instrumentation_57.sql ${CMAKE_CURRENT_SOURCE_DIR}/views/p_s/latest_file_io.sql ${CMAKE_CURRENT_SOURCE_DIR}/views/p_s/x_latest_file_io.sql @@ -130,6 +131,7 @@ ${CMAKE_CURRENT_SOURCE_DIR}/views/p_s/session_ssl_status.sql ${CMAKE_CURRENT_SOURCE_DIR}/procedures/create_synonym_db.sql ${CMAKE_CURRENT_SOURCE_DIR}/procedures/execute_prepared_stmt.sql ${CMAKE_CURRENT_SOURCE_DIR}/procedures/diagnostics.sql +${CMAKE_CURRENT_SOURCE_DIR}/procedures/optimizer_switch.sql ${CMAKE_CURRENT_SOURCE_DIR}/procedures/ps_statement_avg_latency_histogram.sql ${CMAKE_CURRENT_SOURCE_DIR}/procedures/ps_trace_statement_digest.sql ${CMAKE_CURRENT_SOURCE_DIR}/procedures/ps_trace_thread.sql @@ -174,4 +176,4 @@ FOREACH(f ${files}) FILE(READ ${f} content) SET(CMAKE_CONFIGURABLE_FILE_CONTENT "${CMAKE_CONFIGURABLE_FILE_CONTENT}${content}\n") ENDFOREACH() -CONFIGURE_FILE(${PROJECT_SOURCE_DIR}/cmake/configurable_file_content.in ${CMAKE_BINARY_DIR}/scripts/mysql_sys_schema.sql) +CONFIGURE_FILE(${PROJECT_SOURCE_DIR}/cmake/configurable_file_content.in ${CMAKE_BINARY_DIR}/scripts/mariadb_sys_schema.sql) diff --git a/scripts/sys_schema/NEWS.md b/scripts/sys_schema/NEWS.md index 7aeefb7e..2b594801 100644 --- a/scripts/sys_schema/NEWS.md +++ b/scripts/sys_schema/NEWS.md @@ -1,6 +1,13 @@ # Change history for the MySQL sys schema -## 1.5.1 (07/07/16) +## 1.5.2 (2023-11-20) + +### Improvements + +* A new `privileges_by_table_by_level` view was added, which displays granted privileges broken down by the table on which they allow access and the level on which they were granted + + +## 1.5.1 (2016-07-07) ### Improvements @@ -24,7 +31,7 @@ * Oracle Bug #21970806 - The `sysschema.fn_ps_thread_trx_info` test was unstable * Oracle Bug #23621189 - The `ps_trace_statement_digest` procedure ran EXPLAIN incorrectly in certain cases (such as on a SHOW statement, no query being specified, or not having a full qualified table), the procedure now catches these issues and ignores them -## 1.5.0 (11/09/15) +## 1.5.0 (2015-09-11) ### Improvements @@ -84,7 +91,7 @@ * Template files were added for stored procedures and functions * Improved the sys_config_cleanup.inc procedure in tests to be able to reset the sys_config table completely (including the set_by column to NULL). The triggers can now be set to not update the column by setting the @sys.ignore_sys_config_triggers user variable to true -## 1.4.0 (09/03/2015) +## 1.4.0 (2015-03-09) ### Backwards Incompatible Changes @@ -126,7 +133,7 @@ Various changes were made to allow better generation of integration sql files: * Each object has been created within it's own file. No longer do x$ views live with their non-x$ counterparts * DELIMITERs were standardized to $$ -## 1.3.0 (23/10/2014) +## 1.3.0 (2014-10-23) ### Improvements @@ -136,7 +143,7 @@ Various changes were made to allow better generation of integration sql files: * Fixed broken `host_summary_by_stages` views, broken with a last minute change before the 1.2.0 release that went unnoticed (facepalm) -## 1.2.0 (22/10/2014) +## 1.2.0 (2014-10-22) ### Backwards Incompatible Changes @@ -163,7 +170,7 @@ Various changes were made to allow better generation of integration sql files: * Added missing space for hour notation within the `format_time` function * Fixed views affected by MySQL 5.7 ONLY_FULL_GROUP_BY and functional dependency changes -## 1.1.0 (04/09/2014) +## 1.1.0 (2014-09-04) ### Improvements @@ -195,7 +202,7 @@ Various changes were made to allow better generation of integration sql files: * Fixed the RETURN datatype `extract_schema_from_file_name` and `extract_table_from_file_name` to return a VARCHAR(64) (**Contributed by Jesper Wisborg Krogh**) * Added events_transactions_current to the default enabled consumers in 5.7 (#25) -## 1.0.1 (23/05/2014) +## 1.0.1 (2014-05-23) ### Improvements @@ -215,4 +222,4 @@ Various changes were made to allow better generation of integration sql files: * Some views did not work with the ERROR_FOR_DIVISION_BY_ZERO SQL mode. (#6) (**Contributed by Joe Grasse**) * On Windows the `ps_thread_stack()` stored function failed to escape file path backslashes correctly within the JSON output. -## 1.0.0 (11/04/2014) +## 1.0.0 (2014-04-11) diff --git a/scripts/sys_schema/README.md b/scripts/sys_schema/README.md index 3c90f41f..e06f78e1 100644 --- a/scripts/sys_schema/README.md +++ b/scripts/sys_schema/README.md @@ -1635,6 +1635,60 @@ mysql> select * from schema_object_overview; 10 rows in set (1.58 sec) ``` +#### privileges_by_table_by_level + +##### Description + +-- Shows granted privileges broken down by table on which they allow access +-- and level on which they were granted: +-- - user_privileges +-- - schema_privileges +-- - table_privileges + +##### Structure + +```SQL +MariaDB [test]> desc sys.privileges_by_table_by_level; ++--------------+--------------+------+-----+---------+-------+ +| Field | Type | Null | Key | Default | Extra | ++--------------+--------------+------+-----+---------+-------+ +| TABLE_SCHEMA | varchar(64) | NO | | NULL | | +| TABLE_NAME | varchar(64) | NO | | NULL | | +| GRANTEE | varchar(385) | NO | | | | +| PRIVILEGE | varchar(64) | NO | | | | +| LEVEL | varchar(6) | NO | | | | ++--------------+--------------+------+-----+---------+-------+ +5 rows in set (0.002 sec) +``` + +##### Example + +```SQL +mysql> select * from sys.privileges_by_table_by_level; ++--------------+------------+--------------------+----------------+--------+ +| TABLE_SCHEMA | TABLE_NAME | GRANTEE | PRIVILEGE_TYPE | LEVEL | ++--------------+------------+--------------------+----------------+--------+ +| test | v1 | 'oleg'@'localhost' | SELECT | GLOBAL | +| test | t1 | 'oleg'@'localhost' | SELECT | GLOBAL | +| test | v1 | 'oleg'@'localhost' | INSERT | GLOBAL | +| test | t1 | 'oleg'@'localhost' | INSERT | GLOBAL | +| test | v1 | 'oleg'@'localhost' | UPDATE | GLOBAL | +| test | v1 | 'PUBLIC'@'' | SELECT | SCHEMA | +| test | t1 | 'PUBLIC'@'' | SELECT | SCHEMA | +| test | v1 | 'PUBLIC'@'' | INSERT | SCHEMA | +| test | t1 | 'PUBLIC'@'' | INSERT | SCHEMA | +| test | v1 | 'PUBLIC'@'' | UPDATE | SCHEMA | +| test | t1 | 'PUBLIC'@'' | UPDATE | SCHEMA | +| test | v1 | 'PUBLIC'@'' | DELETE HISTORY | SCHEMA | +| test | t1 | 'PUBLIC'@'' | DELETE HISTORY | SCHEMA | +| test | t1 | 'oleg'@'%' | SELECT | TABLE | +| test | t1 | 'oleg'@'%' | UPDATE | TABLE | +| test | v1 | 'oleg'@'%' | SELECT | TABLE | ++--------------+------------+--------------------+----------------+--------+ +16 rows in set (1.58 sec) +``` + + #### schema_table_statistics / x$schema_table_statistics ##### Description @@ -5323,8 +5377,8 @@ mysql> CREATE OR REPLACE VIEW mydb.my_statements 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, + -> format_pico_time(SUM_TIMER_WAIT) AS total_latency, + -> format_pico_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, diff --git a/scripts/sys_schema/functions/format_time.sql b/scripts/sys_schema/functions/format_time.sql index f828f791..47ee0b85 100644 --- a/scripts/sys_schema/functions/format_time.sql +++ b/scripts/sys_schema/functions/format_time.sql @@ -26,7 +26,9 @@ CREATE DEFINER='mariadb.sys'@'localhost' FUNCTION format_time ( COMMENT ' Description ----------- - + + OBSOLETE. USE BUILT-IN format_pico_time() INSTEAD + Takes a raw picoseconds value, and converts it to a human readable form. Picoseconds are the precision that all latency values are printed in diff --git a/scripts/sys_schema/functions/ps_thread_trx_info.sql b/scripts/sys_schema/functions/ps_thread_trx_info.sql index 12ab6aef..e0f7d179 100644 --- a/scripts/sys_schema/functions/ps_thread_trx_info.sql +++ b/scripts/sys_schema/functions/ps_thread_trx_info.sql @@ -172,7 +172,7 @@ BEGIN GROUP_CONCAT( IFNULL( CONCAT('\n {\n', - ' "time": "', IFNULL(sys.format_time(trxi.timer_wait), ''), '",\n', + ' "time": "', IFNULL(format_pico_time(trxi.timer_wait), ''), '",\n', ' "state": "', IFNULL(trxi.state, ''), '",\n', ' "mode": "', IFNULL(trxi.access_mode, ''), '",\n', ' "autocommitted": "', IFNULL(trxi.autocommit, ''), '",\n', @@ -200,7 +200,7 @@ BEGIN IFNULL( CONCAT('\n {\n', ' "sql_text": "', IFNULL(sys.format_statement(REPLACE(sql_text, '\\', '\\\\')), ''), '",\n', - ' "time": "', IFNULL(sys.format_time(timer_wait), ''), '",\n', + ' "time": "', IFNULL(format_pico_time(timer_wait), ''), '",\n', ' "schema": "', IFNULL(current_schema, ''), '",\n', ' "rows_examined": ', IFNULL(rows_examined, ''), ',\n', ' "rows_affected": ', IFNULL(rows_affected, ''), ',\n', diff --git a/scripts/sys_schema/procedures/create_synonym_db.sql b/scripts/sys_schema/procedures/create_synonym_db.sql index e373a9b4..48270376 100644 --- a/scripts/sys_schema/procedures/create_synonym_db.sql +++ b/scripts/sys_schema/procedures/create_synonym_db.sql @@ -98,7 +98,9 @@ BEGIN DECLARE v_table VARCHAR(64); DECLARE v_views_created INT DEFAULT 0; DECLARE v_table_exists ENUM('', 'BASE TABLE', 'VIEW', 'TEMPORARY') DEFAULT ''; - DECLARE v_temp_table TEXT; + + DECLARE db_doesnt_exist CONDITION FOR SQLSTATE '42000'; + DECLARE db_name_exists CONDITION FOR SQLSTATE 'HY000'; DECLARE c_table_names CURSOR FOR SELECT TABLE_NAME @@ -142,37 +144,25 @@ BEGIN IF v_done THEN LEAVE c_table_names; END IF; - - -- Check does temporary table shadows the base table. If it is so, terminate. + -- Check the table type, don't support temporary since cannot create the view CALL sys.table_exists(in_db_name, v_table, v_table_exists); - IF (v_table_exists = 'TEMPORARY') THEN - SET v_temp_table = - CONCAT( - 'Table', - sys.quote_identifier(in_db_name), - '.', - sys.quote_identifier(v_table), - 'shadows base table. View cannot be created! Terminating!'); - SIGNAL SQLSTATE 'HY000' - SET MESSAGE_TEXT = v_temp_table; - LEAVE c_table_names; + IF (v_table_exists <> 'TEMPORARY') THEN + SET @create_view_stmt = CONCAT( + 'CREATE SQL SECURITY INVOKER VIEW ', + sys.quote_identifier(in_synonym), + '.', + sys.quote_identifier(v_table), + ' AS SELECT * FROM ', + sys.quote_identifier(in_db_name), + '.', + sys.quote_identifier(v_table) + ); + PREPARE create_view_stmt FROM @create_view_stmt; + EXECUTE create_view_stmt; + DEALLOCATE PREPARE create_view_stmt; + + SET v_views_created = v_views_created + 1; END IF; - - SET @create_view_stmt = CONCAT( - 'CREATE SQL SECURITY INVOKER VIEW ', - sys.quote_identifier(in_synonym), - '.', - sys.quote_identifier(v_table), - ' AS SELECT * FROM ', - sys.quote_identifier(in_db_name), - '.', - sys.quote_identifier(v_table) - ); - PREPARE create_view_stmt FROM @create_view_stmt; - EXECUTE create_view_stmt; - DEALLOCATE PREPARE create_view_stmt; - - SET v_views_created = v_views_created + 1; END LOOP; CLOSE c_table_names; diff --git a/scripts/sys_schema/procedures/diagnostics.sql b/scripts/sys_schema/procedures/diagnostics.sql index 2e79c5c1..2d2f4664 100644 --- a/scripts/sys_schema/procedures/diagnostics.sql +++ b/scripts/sys_schema/procedures/diagnostics.sql @@ -355,7 +355,7 @@ BEGIN ) THEN CONCAT(''sys.format_bytes('', COLUMN_NAME, '') AS '', COLUMN_NAME) WHEN SUBSTRING(COLUMN_NAME, -8) = ''_latency'' - THEN CONCAT(''sys.format_time('', COLUMN_NAME, '') AS '', COLUMN_NAME) + THEN CONCAT(''format_pico_time('', COLUMN_NAME, '') AS '', COLUMN_NAME) WHEN SUBSTRING(COLUMN_NAME, -7) = ''_memory'' OR SUBSTRING(COLUMN_NAME, -17) = ''_memory_allocated'' OR ((SUBSTRING(COLUMN_NAME, -5) = ''_read'' OR SUBSTRING(COLUMN_NAME, -8) = ''_written'' OR SUBSTRING(COLUMN_NAME, -6) = ''_write'') AND SUBSTRING(COLUMN_NAME, 1, 6) <> ''COUNT_'') THEN CONCAT(''sys.format_bytes('', COLUMN_NAME, '') AS '', COLUMN_NAME) @@ -385,15 +385,15 @@ BEGIN ) THEN CONCAT(''sys.format_bytes(e.'', COLUMN_NAME, ''-IFNULL(s.'', COLUMN_NAME, '', 0)) AS '', COLUMN_NAME) WHEN SUBSTRING(COLUMN_NAME, 1, 4) IN (''max_'', ''min_'') AND SUBSTRING(COLUMN_NAME, -8) = ''_latency'' - THEN CONCAT(''sys.format_time(e.'', COLUMN_NAME, '') AS '', COLUMN_NAME) + THEN CONCAT(''format_pico_time(e.'', COLUMN_NAME, '') AS '', COLUMN_NAME) WHEN COLUMN_NAME = ''avg_latency'' - THEN CONCAT(''sys.format_time((e.total_latency - IFNULL(s.total_latency, 0))'', + THEN CONCAT(''format_pico_time((e.total_latency - IFNULL(s.total_latency, 0))'', ''/NULLIF(e.total - IFNULL(s.total, 0), 0)) AS '', COLUMN_NAME) WHEN SUBSTRING(COLUMN_NAME, -12) = ''_avg_latency'' - THEN CONCAT(''sys.format_time((e.'', SUBSTRING(COLUMN_NAME FROM 1 FOR CHAR_LENGTH(COLUMN_NAME)-12), ''_latency - IFNULL(s.'', SUBSTRING(COLUMN_NAME FROM 1 FOR CHAR_LENGTH(COLUMN_NAME)-12), ''_latency, 0))'', + THEN CONCAT(''format_pico_time((e.'', SUBSTRING(COLUMN_NAME FROM 1 FOR CHAR_LENGTH(COLUMN_NAME)-12), ''_latency - IFNULL(s.'', SUBSTRING(COLUMN_NAME FROM 1 FOR CHAR_LENGTH(COLUMN_NAME)-12), ''_latency, 0))'', ''/NULLIF(e.'', SUBSTRING(COLUMN_NAME FROM 1 FOR CHAR_LENGTH(COLUMN_NAME)-12), ''s - IFNULL(s.'', SUBSTRING(COLUMN_NAME FROM 1 FOR CHAR_LENGTH(COLUMN_NAME)-12), ''s, 0), 0)) AS '', COLUMN_NAME) WHEN SUBSTRING(COLUMN_NAME, -8) = ''_latency'' - THEN CONCAT(''sys.format_time(e.'', COLUMN_NAME, '' - IFNULL(s.'', COLUMN_NAME, '', 0)) AS '', COLUMN_NAME) + THEN CONCAT(''format_pico_time(e.'', COLUMN_NAME, '' - IFNULL(s.'', COLUMN_NAME, '', 0)) AS '', COLUMN_NAME) WHEN COLUMN_NAME IN (''avg_read'', ''avg_write'', ''avg_written'') THEN CONCAT(''sys.format_bytes(IFNULL((e.total_'', IF(COLUMN_NAME = ''avg_read'', ''read'', ''written''), ''-IFNULL(s.total_'', IF(COLUMN_NAME = ''avg_read'', ''read'', ''written''), '', 0))'', ''/NULLIF(e.count_'', IF(COLUMN_NAME = ''avg_read'', ''read'', ''write''), ''-IFNULL(s.count_'', IF(COLUMN_NAME = ''avg_read'', ''read'', ''write''), '', 0), 0), 0)) AS '', @@ -1061,4 +1061,4 @@ SELECT ''UNIX_TIMESTAMP()'' AS Variable_name, ROUND(UNIX_TIMESTAMP(NOW(3)), 3) A END IF; END$$ -DELIMITER ;
\ No newline at end of file +DELIMITER ; diff --git a/scripts/sys_schema/procedures/optimizer_switch.sql b/scripts/sys_schema/procedures/optimizer_switch.sql new file mode 100644 index 00000000..febeabc1 --- /dev/null +++ b/scripts/sys_schema/procedures/optimizer_switch.sql @@ -0,0 +1,69 @@ +-- Copyright (C) 2023, MariaDB +-- +-- This program is free software; you can redistribute it and/or modify +-- it under the terms of the GNU General Public License as published by +-- the Free Software Foundation; version 2 of the License. +-- +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +-- GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License +-- along with this program; if not, write to the Free Software +-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA + +DROP PROCEDURE IF EXISTS optimizer_switch_choice; +DROP PROCEDURE IF EXISTS optimizer_switch_on; +DROP PROCEDURE IF EXISTS optimizer_switch_off; +DELIMITER $$ + +CREATE DEFINER='mariadb.sys'@'localhost' PROCEDURE optimizer_switch_choice(IN on_off VARCHAR(3)) +COMMENT 'return @@optimizer_switch options as a result set for easier readability' +SQL SECURITY INVOKER +NOT DETERMINISTIC +CONTAINS SQL +BEGIN + DECLARE tmp VARCHAR(1024); + DECLARE opt VARCHAR(1024); + DECLARE start INT; + DECLARE end INT; + DECLARE pos INT; + set tmp=concat(@@optimizer_switch,","); + CREATE OR REPLACE TEMPORARY TABLE tmp_opt_switch (a varchar(64), opt CHAR(3)) character set latin1 engine=heap; + set start=1; + FIND_OPTIONS: + LOOP + set pos= INSTR(SUBSTR(tmp, start), ","); + if (pos = 0) THEN + LEAVE FIND_OPTIONS; + END IF; + set opt= MID(tmp, start, pos-1); + set end= INSTR(opt, "="); + insert into tmp_opt_switch values(LEFT(opt,end-1),SUBSTR(opt,end+1)); + set start=start + pos; + END LOOP; + SELECT t.a as "option",t.opt from tmp_opt_switch as t where t.opt = on_off order by a; + DROP TEMPORARY TABLE tmp_opt_switch; +END$$ + +CREATE DEFINER='mariadb.sys'@'localhost' PROCEDURE optimizer_switch_on() +COMMENT 'return @@optimizer_switch options that are on' +SQL SECURITY INVOKER +NOT DETERMINISTIC +CONTAINS SQL +BEGIN + call optimizer_switch_choice("on"); +END$$ + +CREATE DEFINER='mariadb.sys'@'localhost' PROCEDURE optimizer_switch_off() +COMMENT 'return @@optimizer_switch options that are off' +SQL SECURITY INVOKER +NOT DETERMINISTIC +CONTAINS SQL +BEGIN + call optimizer_switch_choice("off"); +END$$ + +DELIMITER ; + diff --git a/scripts/sys_schema/procedures/ps_trace_statement_digest.sql b/scripts/sys_schema/procedures/ps_trace_statement_digest.sql index f9bb4d3b..6ad01e4d 100644 --- a/scripts/sys_schema/procedures/ps_trace_statement_digest.sql +++ b/scripts/sys_schema/procedures/ps_trace_statement_digest.sql @@ -240,8 +240,8 @@ BEGIN SELECT "SUMMARY STATISTICS"; SELECT COUNT(*) executions, - sys.format_time(SUM(timer_wait)) AS exec_time, - sys.format_time(SUM(lock_time)) AS lock_time, + format_pico_time(SUM(timer_wait)) AS exec_time, + format_pico_time(SUM(lock_time)) AS lock_time, SUM(rows_sent) AS rows_sent, SUM(rows_affected) AS rows_affected, SUM(rows_examined) AS rows_examined, @@ -251,7 +251,7 @@ BEGIN SELECT event_name, COUNT(*) as count, - sys.format_time(SUM(timer_wait)) as latency + format_pico_time(SUM(timer_wait)) as latency FROM stmt_stages GROUP BY event_name ORDER BY SUM(timer_wait) DESC; @@ -259,8 +259,8 @@ BEGIN SELECT "LONGEST RUNNING STATEMENT"; SELECT thread_id, - sys.format_time(timer_wait) AS exec_time, - sys.format_time(lock_time) AS lock_time, + format_pico_time(timer_wait) AS exec_time, + format_pico_time(lock_time) AS lock_time, rows_sent, rows_affected, rows_examined, @@ -279,7 +279,7 @@ BEGIN IF (@sql_id IS NOT NULL) THEN SELECT event_name, - sys.format_time(timer_wait) as latency + format_pico_time(timer_wait) as latency FROM stmt_stages WHERE stmt_id = @sql_id ORDER BY event_id; diff --git a/scripts/sys_schema/procedures/ps_trace_thread.sql b/scripts/sys_schema/procedures/ps_trace_thread.sql index 1a2ede79..4d5ba14a 100644 --- a/scripts/sys_schema/procedures/ps_trace_thread.sql +++ b/scripts/sys_schema/procedures/ps_trace_thread.sql @@ -116,7 +116,7 @@ BEGIN SELECT CONCAT(IF(nesting_event_id IS NOT NULL, CONCAT(nesting_event_id, ' -> '), ''), event_id, '; ', event_id, ' [label="', -- Convert from picoseconds to microseconds - '(', sys.format_time(timer_wait), ') ', + '(', format_pico_time(timer_wait), ') ', IF (event_name NOT LIKE 'wait/io%', SUBSTRING_INDEX(event_name, '/', -2), IF (event_name NOT LIKE 'wait/io/file%' OR event_name NOT LIKE 'wait/io/socket%', @@ -170,7 +170,7 @@ BEGIN CONCAT(sql_text, '\\n', 'errors: ', errors, '\\n', 'warnings: ', warnings, '\\n', - 'lock time: ', sys.format_time(lock_time),'\\n', + 'lock time: ', format_pico_time(lock_time),'\\n', 'rows affected: ', rows_affected, '\\n', 'rows sent: ', rows_sent, '\\n', 'rows examined: ', rows_examined, '\\n', diff --git a/scripts/sys_schema/procedures/ps_trace_thread_57.sql b/scripts/sys_schema/procedures/ps_trace_thread_57.sql index 33dc7961..d88f824c 100644 --- a/scripts/sys_schema/procedures/ps_trace_thread_57.sql +++ b/scripts/sys_schema/procedures/ps_trace_thread_57.sql @@ -116,7 +116,7 @@ BEGIN SELECT CONCAT(IF(nesting_event_id IS NOT NULL, CONCAT(nesting_event_id, ' -> '), ''), event_id, '; ', event_id, ' [label="', -- Convert from picoseconds to microseconds - '(', sys.format_time(timer_wait), ') ', + '(', format_pico_time(timer_wait), ') ', IF (event_name NOT LIKE 'wait/io%', SUBSTRING_INDEX(event_name, '/', -2), IF (event_name NOT LIKE 'wait/io/file%' OR event_name NOT LIKE 'wait/io/socket%', @@ -188,7 +188,7 @@ BEGIN CONCAT('statement: ', sql_text, '\\n', 'errors: ', errors, '\\n', 'warnings: ', warnings, '\\n', - 'lock time: ', sys.format_time(lock_time),'\\n', + 'lock time: ', format_pico_time(lock_time),'\\n', 'rows affected: ', rows_affected, '\\n', 'rows sent: ', rows_sent, '\\n', 'rows examined: ', rows_examined, '\\n', diff --git a/scripts/sys_schema/procedures/statement_performance_analyzer.sql b/scripts/sys_schema/procedures/statement_performance_analyzer.sql index f98596ef..3c26fd0b 100644 --- a/scripts/sys_schema/procedures/statement_performance_analyzer.sql +++ b/scripts/sys_schema/procedures/statement_performance_analyzer.sql @@ -196,8 +196,8 @@ CREATE DEFINER='mariadb.sys'@'localhost' PROCEDURE statement_performance_analyze -> 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, + -> format_pico_time(SUM_TIMER_WAIT) AS total_latency, + -> format_pico_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, @@ -720,4 +720,4 @@ HAVING percentile > 0.95 END IF; END$$ -DELIMITER ;
\ No newline at end of file +DELIMITER ; diff --git a/scripts/sys_schema/procedures/table_exists.sql b/scripts/sys_schema/procedures/table_exists.sql index 0f764032..3224c87e 100644 --- a/scripts/sys_schema/procedures/table_exists.sql +++ b/scripts/sys_schema/procedures/table_exists.sql @@ -19,7 +19,7 @@ DELIMITER $$ CREATE DEFINER='mariadb.sys'@'localhost' PROCEDURE table_exists ( IN in_db VARCHAR(64), IN in_table VARCHAR(64), - OUT out_exists ENUM('', 'BASE TABLE', 'VIEW', 'TEMPORARY', 'SEQUENCE', 'SYSTEM VIEW') + OUT out_exists ENUM('', 'BASE TABLE', 'VIEW', 'TEMPORARY', 'SEQUENCE', 'SYSTEM VIEW', 'TEMPORARY SEQUENCE') ) COMMENT ' Description @@ -39,36 +39,44 @@ CREATE DEFINER='mariadb.sys'@'localhost' PROCEDURE table_exists ( in_table (VARCHAR(64)): The name of the table to check the existence of. - out_exists ENUM('''', ''BASE TABLE'', ''VIEW'', ''TEMPORARY''): + out_exists ENUM('''', ''BASE TABLE'', ''VIEW'', ''TEMPORARY'', ''SEQUENCE'', ''SYSTEM VIEW'', ''TEMPORARY SEQUENCE''): The return value: whether the table exists. The value is one of: - * '''' - the table does not exist neither as a base table, view, sequence nor temporary table. - * ''BASE TABLE'' - the table name exists as a permanent base table table. - * ''VIEW'' - the table name exists as a view. - * ''TEMPORARY'' - the table name exists as a temporary table. - * ''SEQUENCE'' - the table name exists as a sequence. - * ''SYSTEM VIEW'' - the table name exists as a system view. + * '''' - the table does not exist neither as a base table, view, sequence nor temporary table/sequence. + * ''BASE TABLE'' - the table name exists as a permanent base table table. + * ''VIEW'' - the table name exists as a view. + * ''TEMPORARY'' - the table name exists as a temporary table. + * ''SEQUENCE'' - the table name exists as a sequence. + * ''SYSTEM VIEW'' - the table name exists as a system view. + * ''TEMPORARY SEQUENCE'' - the table name exists as a temporary sequence. Example -------- - mysql> CREATE DATABASE db1; + MariaDB [sys]> CREATE DATABASE db1; Query OK, 1 row affected (0.07 sec) - mysql> use db1; + MariaDB [sys]> use db1; Database changed - mysql> CREATE TABLE t1 (id INT PRIMARY KEY); + + MariaDB [sys]> CREATE TABLE t1 (id INT PRIMARY KEY); Query OK, 0 rows affected (0.08 sec) - - mysql> CREATE TABLE t2 (id INT PRIMARY KEY); + + MariaDB [sys]> CREATE TABLE t2 (id INT PRIMARY KEY); Query OK, 0 rows affected (0.08 sec) - - mysql> CREATE view v_t1 AS SELECT * FROM t1; + + MariaDB [sys]> CREATE view v_t1 AS SELECT * FROM t1; Query OK, 0 rows affected (0.00 sec) - - mysql> CREATE TEMPORARY TABLE t1 (id INT PRIMARY KEY); + + MariaDB [sys]> CREATE TEMPORARY TABLE t1 (id INT PRIMARY KEY); Query OK, 0 rows affected (0.00 sec) - - mysql> CALL sys.table_exists(''db1'', ''t1'', @exists); SELECT @exists; + + MariaDB [sys]> CREATE SEQUENCE s; + Query OK, 0 rows affected (0.00 sec) + + MariaDB [sys]> CREATE TEMPORARY SEQUENCE s_temp; + Query OK, 0 rows affected (0.00 sec) + + MariaDB [sys]> CALL sys.table_exists(''db1'', ''t1'', @exists); SELECT @exists; Query OK, 0 rows affected (0.00 sec) +------------+ @@ -78,7 +86,7 @@ CREATE DEFINER='mariadb.sys'@'localhost' PROCEDURE table_exists ( +------------+ 1 row in set (0.00 sec) - mysql> CALL sys.table_exists(''db1'', ''t2'', @exists); SELECT @exists; + MariaDB [sys]> CALL sys.table_exists(''db1'', ''t2'', @exists); SELECT @exists; Query OK, 0 rows affected (0.00 sec) +------------+ @@ -88,7 +96,7 @@ CREATE DEFINER='mariadb.sys'@'localhost' PROCEDURE table_exists ( +------------+ 1 row in set (0.01 sec) - mysql> CALL sys.table_exists(''db1'', ''v_t1'', @exists); SELECT @exists; + MariaDB [sys]> CALL sys.table_exists(''db1'', ''v_t1'', @exists); SELECT @exists; Query OK, 0 rows affected (0.00 sec) +---------+ @@ -118,7 +126,7 @@ CREATE DEFINER='mariadb.sys'@'localhost' PROCEDURE table_exists ( +-------------+ 1 row in set (0.001 sec) - mysql> CALL sys.table_exists(''db1'', ''t3'', @exists); SELECT @exists; + MariaDB [sys]> CALL sys.table_exists(''db1'', ''t3'', @exists); SELECT @exists; Query OK, 0 rows affected (0.01 sec) +---------+ @@ -127,6 +135,16 @@ CREATE DEFINER='mariadb.sys'@'localhost' PROCEDURE table_exists ( | | +---------+ 1 row in set (0.00 sec) + + MariaDB [sys]> CALL table_exists(''db1'', ''s_temp'', @exists); SELECT @exists; + Query OK, 0 rows affected (0.003 sec) + + +--------------------+ + | @exists | + +--------------------+ + | TEMPORARY SEQUENCE | + +--------------------+ + 1 row in set (0.001 sec) ' SQL SECURITY INVOKER NOT DETERMINISTIC @@ -135,65 +153,29 @@ BEGIN DECLARE v_error BOOLEAN DEFAULT FALSE; DECLARE db_quoted VARCHAR(64); DECLARE table_quoted VARCHAR(64); - DECLARE v_table_type VARCHAR(16) DEFAULT ''; - DECLARE v_system_db BOOLEAN - DEFAULT LOWER(in_db) IN ('information_schema', 'performance_schema'); + DECLARE v_table_type VARCHAR(30) DEFAULT ''; DECLARE CONTINUE HANDLER FOR 1050 SET v_error = TRUE; DECLARE CONTINUE HANDLER FOR 1146 SET v_error = TRUE; - SET out_exists = ''; - SET db_quoted = sys.quote_identifier(in_db); - SET table_quoted = sys.quote_identifier(in_table); - - -- Verify whether the table name exists as a normal table - IF (EXISTS(SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = in_db AND TABLE_NAME = in_table)) THEN - -- Unfortunately the only way to determine whether there is also a temporary table is to try to create - -- a temporary table with the same name. If it succeeds the table didn't exist as a temporary table. - IF v_system_db = FALSE THEN - SET @sys.tmp.table_exists.SQL = CONCAT('CREATE TEMPORARY TABLE ', - db_quoted, - '.', - table_quoted, - '(id INT PRIMARY KEY)'); - PREPARE stmt_create_table FROM @sys.tmp.table_exists.SQL; - EXECUTE stmt_create_table; - DEALLOCATE PREPARE stmt_create_table; - - -- The temporary table was created, i.e. it didn't exist. Remove it again so we don't leave garbage around. - SET @sys.tmp.table_exists.SQL = CONCAT('DROP TEMPORARY TABLE ', - db_quoted, - '.', - table_quoted); - PREPARE stmt_drop_table FROM @sys.tmp.table_exists.SQL; - EXECUTE stmt_drop_table; - DEALLOCATE PREPARE stmt_drop_table; + -- First check do we have multiple rows, what can happen if temporary table + -- and/or sequence is shadowing base table for example. + -- In such scenario return temporary. + SET v_table_type = (SELECT GROUP_CONCAT(TABLE_TYPE) FROM information_schema.TABLES WHERE + TABLE_SCHEMA = in_db AND TABLE_NAME = in_table); + + IF v_table_type LIKE '%,%' THEN + SET out_exists = 'TEMPORARY'; + ELSE + IF v_table_type is NULL + THEN + SET v_table_type=''; END IF; - IF (v_error) THEN - SET out_exists = 'TEMPORARY'; + -- Don't fail on table_type='SYSTEM VERSIONED' + -- but return 'BASE TABLE' for compatibility with existing tooling + IF v_table_type = 'SYSTEM VERSIONED' THEN + SET out_exists = 'BASE TABLE'; ELSE - SET v_table_type = (SELECT TABLE_TYPE FROM information_schema.TABLES WHERE TABLE_SCHEMA = in_db AND TABLE_NAME = in_table); - -- Don't fail on table_type='SYSTEM VERSIONED' - -- but return 'BASE TABLE' for compatibility with existing tooling - IF v_table_type = 'SYSTEM VERSIONED' THEN - SET out_exists = 'BASE TABLE'; - ELSE - SET out_exists = v_table_type; - END IF; - END IF; - ELSE - -- Check whether a temporary table exists with the same name. - -- If it does it's possible to SELECT from the table without causing an error. - -- If it does not exist even a PREPARE using the table will fail. - IF v_system_db = FALSE THEN - SET @sys.tmp.table_exists.SQL = CONCAT('SELECT COUNT(*) FROM ', - db_quoted, - '.', - table_quoted); - PREPARE stmt_select FROM @sys.tmp.table_exists.SQL; - IF (NOT v_error) THEN - DEALLOCATE PREPARE stmt_select; - SET out_exists = 'TEMPORARY'; - END IF; + SET out_exists = v_table_type; END IF; END IF; END$$ diff --git a/scripts/sys_schema/sys_56.sql b/scripts/sys_schema/sys_56.sql index 1de01d25..1836d528 100644 --- a/scripts/sys_schema/sys_56.sql +++ b/scripts/sys_schema/sys_56.sql @@ -55,6 +55,7 @@ SOURCE ./views/i_s/schema_object_overview.sql SOURCE ./views/i_s/schema_auto_increment_columns.sql SOURCE ./views/i_s/x_schema_flattened_keys.sql SOURCE ./views/i_s/schema_redundant_indexes.sql +SOURCE ./views/i_s/privileges_by_table_by_level.sql SOURCE ./views/p_s/ps_check_lost_instrumentation.sql SOURCE ./views/p_s/processlist.sql diff --git a/scripts/sys_schema/sys_57.sql b/scripts/sys_schema/sys_57.sql index 277cc624..47d442b3 100644 --- a/scripts/sys_schema/sys_57.sql +++ b/scripts/sys_schema/sys_57.sql @@ -56,6 +56,7 @@ SOURCE ./views/i_s/schema_object_overview.sql SOURCE ./views/i_s/schema_auto_increment_columns.sql SOURCE ./views/i_s/x_schema_flattened_keys.sql SOURCE ./views/i_s/schema_redundant_indexes.sql +SOURCE ./views/i_s/privileges_by_table_by_level.sql SOURCE ./views/p_s/ps_check_lost_instrumentation_57.sql diff --git a/scripts/sys_schema/views/i_s/privileges_by_table_by_level.sql b/scripts/sys_schema/views/i_s/privileges_by_table_by_level.sql new file mode 100644 index 00000000..4f769427 --- /dev/null +++ b/scripts/sys_schema/views/i_s/privileges_by_table_by_level.sql @@ -0,0 +1,77 @@ +-- +-- View: privileges_by_table_by_level +-- +-- Shows granted privileges broken down by the table on which they allow access +-- and the level on which they were granted: +-- - user_privileges +-- - schema_privileges +-- - table_privileges +-- +-- mysql> select * from sys.privileges_by_table_by_level; +-- +--------------+------------+--------------------+----------------+--------+ +-- | TABLE_SCHEMA | TABLE_NAME | GRANTEE | PRIVILEGE_TYPE | LEVEL | +-- +--------------+------------+--------------------+----------------+--------+ +-- | test | v1 | 'oleg'@'localhost' | SELECT | GLOBAL | +-- | test | t1 | 'oleg'@'localhost' | SELECT | GLOBAL | +-- | test | v1 | 'oleg'@'localhost' | INSERT | GLOBAL | +-- | test | t1 | 'oleg'@'localhost' | INSERT | GLOBAL | +-- | test | v1 | 'oleg'@'localhost' | UPDATE | GLOBAL | +-- | test | v1 | 'PUBLIC'@'' | SELECT | SCHEMA | +-- | test | t1 | 'PUBLIC'@'' | SELECT | SCHEMA | +-- | test | v1 | 'PUBLIC'@'' | INSERT | SCHEMA | +-- | test | t1 | 'PUBLIC'@'' | INSERT | SCHEMA | +-- | test | v1 | 'PUBLIC'@'' | UPDATE | SCHEMA | +-- | test | t1 | 'PUBLIC'@'' | UPDATE | SCHEMA | +-- | test | v1 | 'PUBLIC'@'' | DELETE HISTORY | SCHEMA | +-- | test | t1 | 'PUBLIC'@'' | DELETE HISTORY | SCHEMA | +-- | test | t1 | 'oleg'@'%' | SELECT | TABLE | +-- | test | t1 | 'oleg'@'%' | UPDATE | TABLE | +-- | test | v1 | 'oleg'@'%' | SELECT | TABLE | +-- +--------------+------------+--------------------+----------------+--------+ + +CREATE OR REPLACE + ALGORITHM = TEMPTABLE + DEFINER = 'mariadb.sys'@'localhost' + SQL SECURITY INVOKER +VIEW privileges_by_table_by_level ( + TABLE_SCHEMA, + TABLE_NAME, + GRANTEE, + PRIVILEGE, + LEVEL +) AS +SELECT t.TABLE_SCHEMA, + t.TABLE_NAME, + privs.GRANTEE, + privs.PRIVILEGE_TYPE, + privs.LEVEL +FROM INFORMATION_SCHEMA.TABLES AS t +JOIN ( SELECT NULL AS TABLE_SCHEMA, + NULL AS TABLE_NAME, + GRANTEE, + PRIVILEGE_TYPE, + 'GLOBAL' LEVEL + FROM INFORMATION_SCHEMA.USER_PRIVILEGES + UNION + SELECT TABLE_SCHEMA, + NULL AS TABLE_NAME, + GRANTEE, + PRIVILEGE_TYPE, + 'SCHEMA' LEVEL + FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES + UNION + SELECT TABLE_SCHEMA, + TABLE_NAME, + GRANTEE, + PRIVILEGE_TYPE, + 'TABLE' LEVEL + FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES + ) privs + ON (t.TABLE_SCHEMA = privs.TABLE_SCHEMA OR privs.TABLE_SCHEMA IS NULL) + AND (t.TABLE_NAME = privs.TABLE_NAME OR privs.TABLE_NAME IS NULL) + AND privs.PRIVILEGE_TYPE IN ('SELECT', 'INSERT', 'UPDATE', 'DELETE', + 'CREATE', 'ALTER', 'DROP', 'INDEX', + 'REFERENCES', 'TRIGGER', 'GRANT OPTION', + 'SHOW VIEW', 'DELETE HISTORY') +WHERE t.TABLE_SCHEMA NOT IN ('sys', 'mysql','information_schema', + 'performance_schema'); diff --git a/scripts/sys_schema/views/p_s/host_summary.sql b/scripts/sys_schema/views/p_s/host_summary.sql index 080100a4..99ed0942 100644 --- a/scripts/sys_schema/views/p_s/host_summary.sql +++ b/scripts/sys_schema/views/p_s/host_summary.sql @@ -46,11 +46,11 @@ VIEW host_summary ( ) AS SELECT IF(accounts.host IS NULL, 'background', accounts.host) AS host, SUM(stmt.total) AS statements, - sys.format_time(SUM(stmt.total_latency)) AS statement_latency, - sys.format_time(IFNULL(SUM(stmt.total_latency) / NULLIF(SUM(stmt.total), 0), 0)) AS statement_avg_latency, + format_pico_time(SUM(stmt.total_latency)) AS statement_latency, + format_pico_time(IFNULL(SUM(stmt.total_latency) / NULLIF(SUM(stmt.total), 0), 0)) AS statement_avg_latency, SUM(stmt.full_scans) AS table_scans, SUM(io.ios) AS file_ios, - sys.format_time(SUM(io.io_latency)) AS file_io_latency, + format_pico_time(SUM(io.io_latency)) AS file_io_latency, SUM(accounts.current_connections) AS current_connections, SUM(accounts.total_connections) AS total_connections, COUNT(DISTINCT accounts.user) AS unique_users diff --git a/scripts/sys_schema/views/p_s/host_summary_57.sql b/scripts/sys_schema/views/p_s/host_summary_57.sql index cd0739f4..dc5fb1e9 100644 --- a/scripts/sys_schema/views/p_s/host_summary_57.sql +++ b/scripts/sys_schema/views/p_s/host_summary_57.sql @@ -49,11 +49,11 @@ VIEW host_summary ( ) AS SELECT IF(accounts.host IS NULL, 'background', accounts.host) AS host, SUM(stmt.total) AS statements, - sys.format_time(SUM(stmt.total_latency)) AS statement_latency, - sys.format_time(IFNULL(SUM(stmt.total_latency) / NULLIF(SUM(stmt.total), 0), 0)) AS statement_avg_latency, + format_pico_time(SUM(stmt.total_latency)) AS statement_latency, + format_pico_time(IFNULL(SUM(stmt.total_latency) / NULLIF(SUM(stmt.total), 0), 0)) AS statement_avg_latency, SUM(stmt.full_scans) AS table_scans, SUM(io.ios) AS file_ios, - sys.format_time(SUM(io.io_latency)) AS file_io_latency, + format_pico_time(SUM(io.io_latency)) AS file_io_latency, SUM(accounts.current_connections) AS current_connections, SUM(accounts.total_connections) AS total_connections, COUNT(DISTINCT user) AS unique_users, diff --git a/scripts/sys_schema/views/p_s/host_summary_by_file_io.sql b/scripts/sys_schema/views/p_s/host_summary_by_file_io.sql index e1fbf2ea..c8aa6f8b 100644 --- a/scripts/sys_schema/views/p_s/host_summary_by_file_io.sql +++ b/scripts/sys_schema/views/p_s/host_summary_by_file_io.sql @@ -40,7 +40,7 @@ VIEW host_summary_by_file_io ( ) AS SELECT IF(host IS NULL, 'background', host) AS host, SUM(count_star) AS ios, - sys.format_time(SUM(sum_timer_wait)) AS io_latency + format_pico_time(SUM(sum_timer_wait)) AS io_latency FROM performance_schema.events_waits_summary_by_host_by_event_name WHERE event_name LIKE 'wait/io/file/%' GROUP BY IF(host IS NULL, 'background', host) diff --git a/scripts/sys_schema/views/p_s/host_summary_by_file_io_type.sql b/scripts/sys_schema/views/p_s/host_summary_by_file_io_type.sql index 58567e3f..f85c18a0 100644 --- a/scripts/sys_schema/views/p_s/host_summary_by_file_io_type.sql +++ b/scripts/sys_schema/views/p_s/host_summary_by_file_io_type.sql @@ -58,8 +58,8 @@ VIEW host_summary_by_file_io_type ( SELECT IF(host IS NULL, 'background', host) AS host, event_name, count_star AS total, - sys.format_time(sum_timer_wait) AS total_latency, - sys.format_time(max_timer_wait) AS max_latency + format_pico_time(sum_timer_wait) AS total_latency, + format_pico_time(max_timer_wait) AS max_latency FROM performance_schema.events_waits_summary_by_host_by_event_name WHERE event_name LIKE 'wait/io/file%' AND count_star > 0 diff --git a/scripts/sys_schema/views/p_s/host_summary_by_stages.sql b/scripts/sys_schema/views/p_s/host_summary_by_stages.sql index 97e5a7ee..063c8a89 100644 --- a/scripts/sys_schema/views/p_s/host_summary_by_stages.sql +++ b/scripts/sys_schema/views/p_s/host_summary_by_stages.sql @@ -57,8 +57,8 @@ VIEW host_summary_by_stages ( SELECT IF(host IS NULL, 'background', host) AS host, event_name, count_star AS total, - sys.format_time(sum_timer_wait) AS total_latency, - sys.format_time(avg_timer_wait) AS avg_latency + format_pico_time(sum_timer_wait) AS total_latency, + format_pico_time(avg_timer_wait) AS avg_latency FROM performance_schema.events_stages_summary_by_host_by_event_name WHERE sum_timer_wait != 0 ORDER BY IF(host IS NULL, 'background', host), sum_timer_wait DESC; diff --git a/scripts/sys_schema/views/p_s/host_summary_by_statement_latency.sql b/scripts/sys_schema/views/p_s/host_summary_by_statement_latency.sql index 9eeb4c30..b0a902c7 100644 --- a/scripts/sys_schema/views/p_s/host_summary_by_statement_latency.sql +++ b/scripts/sys_schema/views/p_s/host_summary_by_statement_latency.sql @@ -45,9 +45,9 @@ VIEW host_summary_by_statement_latency ( ) AS SELECT IF(host IS NULL, 'background', host) AS host, SUM(count_star) AS total, - sys.format_time(SUM(sum_timer_wait)) AS total_latency, - sys.format_time(MAX(max_timer_wait)) AS max_latency, - sys.format_time(SUM(sum_lock_time)) AS lock_latency, + format_pico_time(SUM(sum_timer_wait)) AS total_latency, + format_pico_time(MAX(max_timer_wait)) AS max_latency, + format_pico_time(SUM(sum_lock_time)) AS lock_latency, SUM(sum_rows_sent) AS rows_sent, SUM(sum_rows_examined) AS rows_examined, SUM(sum_rows_affected) AS rows_affected, diff --git a/scripts/sys_schema/views/p_s/host_summary_by_statement_type.sql b/scripts/sys_schema/views/p_s/host_summary_by_statement_type.sql index b529cd8c..3a85478e 100644 --- a/scripts/sys_schema/views/p_s/host_summary_by_statement_type.sql +++ b/scripts/sys_schema/views/p_s/host_summary_by_statement_type.sql @@ -52,9 +52,9 @@ VIEW host_summary_by_statement_type ( SELECT IF(host IS NULL, 'background', host) AS host, SUBSTRING_INDEX(event_name, '/', -1) AS statement, count_star AS total, - sys.format_time(sum_timer_wait) AS total_latency, - sys.format_time(max_timer_wait) AS max_latency, - sys.format_time(sum_lock_time) AS lock_latency, + format_pico_time(sum_timer_wait) AS total_latency, + format_pico_time(max_timer_wait) AS max_latency, + format_pico_time(sum_lock_time) AS lock_latency, sum_rows_sent AS rows_sent, sum_rows_examined AS rows_examined, sum_rows_affected AS rows_affected, diff --git a/scripts/sys_schema/views/p_s/io_by_thread_by_latency.sql b/scripts/sys_schema/views/p_s/io_by_thread_by_latency.sql index c5bf1c69..a4e87dd7 100644 --- a/scripts/sys_schema/views/p_s/io_by_thread_by_latency.sql +++ b/scripts/sys_schema/views/p_s/io_by_thread_by_latency.sql @@ -56,10 +56,10 @@ SELECT IF(processlist_id IS NULL, CONCAT(processlist_user, '@', processlist_host) ) user, SUM(count_star) total, - sys.format_time(SUM(sum_timer_wait)) total_latency, - sys.format_time(MIN(min_timer_wait)) min_latency, - sys.format_time(AVG(avg_timer_wait)) avg_latency, - sys.format_time(MAX(max_timer_wait)) max_latency, + format_pico_time(SUM(sum_timer_wait)) total_latency, + format_pico_time(MIN(min_timer_wait)) min_latency, + format_pico_time(AVG(avg_timer_wait)) avg_latency, + format_pico_time(MAX(max_timer_wait)) max_latency, thread_id, processlist_id FROM performance_schema.events_waits_summary_by_thread_by_event_name diff --git a/scripts/sys_schema/views/p_s/io_global_by_file_by_latency.sql b/scripts/sys_schema/views/p_s/io_global_by_file_by_latency.sql index 97b0aae9..793d4bd7 100644 --- a/scripts/sys_schema/views/p_s/io_global_by_file_by_latency.sql +++ b/scripts/sys_schema/views/p_s/io_global_by_file_by_latency.sql @@ -47,12 +47,12 @@ VIEW io_global_by_file_by_latency ( ) AS SELECT sys.format_path(file_name) AS file, count_star AS total, - sys.format_time(sum_timer_wait) AS total_latency, + format_pico_time(sum_timer_wait) AS total_latency, count_read, - sys.format_time(sum_timer_read) AS read_latency, + format_pico_time(sum_timer_read) AS read_latency, count_write, - sys.format_time(sum_timer_write) AS write_latency, + format_pico_time(sum_timer_write) AS write_latency, count_misc, - sys.format_time(sum_timer_misc) AS misc_latency + format_pico_time(sum_timer_misc) AS misc_latency FROM performance_schema.file_summary_by_instance ORDER BY sum_timer_wait DESC; diff --git a/scripts/sys_schema/views/p_s/io_global_by_wait_by_bytes.sql b/scripts/sys_schema/views/p_s/io_global_by_wait_by_bytes.sql index edf6b994..0ec1a26c 100644 --- a/scripts/sys_schema/views/p_s/io_global_by_wait_by_bytes.sql +++ b/scripts/sys_schema/views/p_s/io_global_by_wait_by_bytes.sql @@ -62,10 +62,10 @@ VIEW io_global_by_wait_by_bytes ( ) AS SELECT SUBSTRING_INDEX(event_name, '/', -2) event_name, count_star AS total, - sys.format_time(sum_timer_wait) AS total_latency, - sys.format_time(min_timer_wait) AS min_latency, - sys.format_time(avg_timer_wait) AS avg_latency, - sys.format_time(max_timer_wait) AS max_latency, + format_pico_time(sum_timer_wait) AS total_latency, + format_pico_time(min_timer_wait) AS min_latency, + format_pico_time(avg_timer_wait) AS avg_latency, + format_pico_time(max_timer_wait) AS max_latency, count_read, sys.format_bytes(sum_number_of_bytes_read) AS total_read, sys.format_bytes(IFNULL(sum_number_of_bytes_read / NULLIF(count_read, 0), 0)) AS avg_read, diff --git a/scripts/sys_schema/views/p_s/io_global_by_wait_by_latency.sql b/scripts/sys_schema/views/p_s/io_global_by_wait_by_latency.sql index 5783e98c..8391aae8 100644 --- a/scripts/sys_schema/views/p_s/io_global_by_wait_by_latency.sql +++ b/scripts/sys_schema/views/p_s/io_global_by_wait_by_latency.sql @@ -63,12 +63,12 @@ VIEW io_global_by_wait_by_latency ( ) AS SELECT SUBSTRING_INDEX(event_name, '/', -2) AS event_name, count_star AS total, - sys.format_time(sum_timer_wait) AS total_latency, - sys.format_time(avg_timer_wait) AS avg_latency, - sys.format_time(max_timer_wait) AS max_latency, - sys.format_time(sum_timer_read) AS read_latency, - sys.format_time(sum_timer_write) AS write_latency, - sys.format_time(sum_timer_misc) AS misc_latency, + format_pico_time(sum_timer_wait) AS total_latency, + format_pico_time(avg_timer_wait) AS avg_latency, + format_pico_time(max_timer_wait) AS max_latency, + format_pico_time(sum_timer_read) AS read_latency, + format_pico_time(sum_timer_write) AS write_latency, + format_pico_time(sum_timer_misc) AS misc_latency, count_read, sys.format_bytes(sum_number_of_bytes_read) AS total_read, sys.format_bytes(IFNULL(sum_number_of_bytes_read / NULLIF(count_read, 0), 0)) AS avg_read, diff --git a/scripts/sys_schema/views/p_s/latest_file_io.sql b/scripts/sys_schema/views/p_s/latest_file_io.sql index 9803cc6c..5289a831 100644 --- a/scripts/sys_schema/views/p_s/latest_file_io.sql +++ b/scripts/sys_schema/views/p_s/latest_file_io.sql @@ -46,7 +46,7 @@ SELECT IF(id IS NULL, CONCAT(user, '@', host, ':', id) ) thread, sys.format_path(object_name) file, - sys.format_time(timer_wait) AS latency, + format_pico_time(timer_wait) AS latency, operation, sys.format_bytes(number_of_bytes) AS requested FROM performance_schema.events_waits_history_long diff --git a/scripts/sys_schema/views/p_s/processlist.sql b/scripts/sys_schema/views/p_s/processlist.sql index 33e8969f..e289a07b 100644 --- a/scripts/sys_schema/views/p_s/processlist.sql +++ b/scripts/sys_schema/views/p_s/processlist.sql @@ -82,9 +82,9 @@ SELECT pps.thread_id AS thd_id, pps.processlist_time AS time, sys.format_statement(pps.processlist_info) AS current_statement, IF(esc.end_event_id IS NULL, - sys.format_time(esc.timer_wait), + format_pico_time(esc.timer_wait), NULL) AS statement_latency, - sys.format_time(esc.lock_time) AS lock_latency, + format_pico_time(esc.lock_time) AS lock_latency, esc.rows_examined AS rows_examined, esc.rows_sent AS rows_sent, esc.rows_affected AS rows_affected, @@ -95,12 +95,12 @@ SELECT pps.thread_id AS thd_id, sys.format_statement(esc.sql_text), NULL) AS last_statement, IF(esc.end_event_id IS NOT NULL, - sys.format_time(esc.timer_wait), + format_pico_time(esc.timer_wait), NULL) AS last_statement_latency, ewc.event_name AS last_wait, IF(ewc.end_event_id IS NULL AND ewc.event_name IS NOT NULL, 'Still Waiting', - sys.format_time(ewc.timer_wait)) last_wait_latency, + format_pico_time(ewc.timer_wait)) last_wait_latency, ewc.source FROM performance_schema.threads AS pps LEFT JOIN performance_schema.events_waits_current AS ewc USING (thread_id) diff --git a/scripts/sys_schema/views/p_s/processlist_57.sql b/scripts/sys_schema/views/p_s/processlist_57.sql index 4e4f21ea..2cb30bd0 100644 --- a/scripts/sys_schema/views/p_s/processlist_57.sql +++ b/scripts/sys_schema/views/p_s/processlist_57.sql @@ -98,12 +98,12 @@ SELECT pps.thread_id AS thd_id, pps.processlist_time AS time, sys.format_statement(pps.processlist_info) AS current_statement, IF(esc.end_event_id IS NULL, - sys.format_time(esc.timer_wait), + format_pico_time(esc.timer_wait), NULL) AS statement_latency, IF(esc.end_event_id IS NULL, ROUND(100 * (estc.work_completed / estc.work_estimated), 2), NULL) AS progress, - sys.format_time(esc.lock_time) AS lock_latency, + format_pico_time(esc.lock_time) AS lock_latency, esc.rows_examined AS rows_examined, esc.rows_sent AS rows_sent, esc.rows_affected AS rows_affected, @@ -114,15 +114,15 @@ SELECT pps.thread_id AS thd_id, sys.format_statement(esc.sql_text), NULL) AS last_statement, IF(esc.end_event_id IS NOT NULL, - sys.format_time(esc.timer_wait), + format_pico_time(esc.timer_wait), NULL) AS last_statement_latency, sys.format_bytes(mem.current_allocated) AS current_memory, ewc.event_name AS last_wait, IF(ewc.end_event_id IS NULL AND ewc.event_name IS NOT NULL, 'Still Waiting', - sys.format_time(ewc.timer_wait)) last_wait_latency, + format_pico_time(ewc.timer_wait)) last_wait_latency, ewc.source, - sys.format_time(etc.timer_wait) AS trx_latency, + format_pico_time(etc.timer_wait) AS trx_latency, etc.state AS trx_state, etc.autocommit AS trx_autocommit, conattr_pid.attr_value as pid, diff --git a/scripts/sys_schema/views/p_s/schema_index_statistics.sql b/scripts/sys_schema/views/p_s/schema_index_statistics.sql index 84ce7ead..794af16d 100644 --- a/scripts/sys_schema/views/p_s/schema_index_statistics.sql +++ b/scripts/sys_schema/views/p_s/schema_index_statistics.sql @@ -53,13 +53,13 @@ SELECT OBJECT_SCHEMA AS table_schema, OBJECT_NAME AS table_name, INDEX_NAME as index_name, COUNT_FETCH AS rows_selected, - sys.format_time(SUM_TIMER_FETCH) AS select_latency, + format_pico_time(SUM_TIMER_FETCH) AS select_latency, COUNT_INSERT AS rows_inserted, - sys.format_time(SUM_TIMER_INSERT) AS insert_latency, + format_pico_time(SUM_TIMER_INSERT) AS insert_latency, COUNT_UPDATE AS rows_updated, - sys.format_time(SUM_TIMER_UPDATE) AS update_latency, + format_pico_time(SUM_TIMER_UPDATE) AS update_latency, COUNT_DELETE AS rows_deleted, - sys.format_time(SUM_TIMER_INSERT) AS delete_latency + format_pico_time(SUM_TIMER_INSERT) AS delete_latency FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL ORDER BY sum_timer_wait DESC; diff --git a/scripts/sys_schema/views/p_s/schema_table_statistics.sql b/scripts/sys_schema/views/p_s/schema_table_statistics.sql index 198d2e0e..45d714a1 100644 --- a/scripts/sys_schema/views/p_s/schema_table_statistics.sql +++ b/scripts/sys_schema/views/p_s/schema_table_statistics.sql @@ -70,23 +70,23 @@ VIEW schema_table_statistics ( ) AS SELECT pst.object_schema AS table_schema, pst.object_name AS table_name, - sys.format_time(pst.sum_timer_wait) AS total_latency, + format_pico_time(pst.sum_timer_wait) AS total_latency, pst.count_fetch AS rows_fetched, - sys.format_time(pst.sum_timer_fetch) AS fetch_latency, + format_pico_time(pst.sum_timer_fetch) AS fetch_latency, pst.count_insert AS rows_inserted, - sys.format_time(pst.sum_timer_insert) AS insert_latency, + format_pico_time(pst.sum_timer_insert) AS insert_latency, pst.count_update AS rows_updated, - sys.format_time(pst.sum_timer_update) AS update_latency, + format_pico_time(pst.sum_timer_update) AS update_latency, pst.count_delete AS rows_deleted, - sys.format_time(pst.sum_timer_delete) AS delete_latency, + format_pico_time(pst.sum_timer_delete) AS delete_latency, fsbi.count_read AS io_read_requests, sys.format_bytes(fsbi.sum_number_of_bytes_read) AS io_read, - sys.format_time(fsbi.sum_timer_read) AS io_read_latency, + format_pico_time(fsbi.sum_timer_read) AS io_read_latency, fsbi.count_write AS io_write_requests, sys.format_bytes(fsbi.sum_number_of_bytes_write) AS io_write, - sys.format_time(fsbi.sum_timer_write) AS io_write_latency, + format_pico_time(fsbi.sum_timer_write) AS io_write_latency, fsbi.count_misc AS io_misc_requests, - sys.format_time(fsbi.sum_timer_misc) AS io_misc_latency + format_pico_time(fsbi.sum_timer_misc) AS io_misc_latency FROM performance_schema.table_io_waits_summary_by_table AS pst LEFT JOIN x$ps_schema_table_statistics_io AS fsbi ON pst.object_schema = fsbi.table_schema diff --git a/scripts/sys_schema/views/p_s/schema_table_statistics_with_buffer.sql b/scripts/sys_schema/views/p_s/schema_table_statistics_with_buffer.sql index acdaefb3..73342194 100644 --- a/scripts/sys_schema/views/p_s/schema_table_statistics_with_buffer.sql +++ b/scripts/sys_schema/views/p_s/schema_table_statistics_with_buffer.sql @@ -87,21 +87,21 @@ VIEW schema_table_statistics_with_buffer ( SELECT pst.object_schema AS table_schema, pst.object_name AS table_name, pst.count_fetch AS rows_fetched, - sys.format_time(pst.sum_timer_fetch) AS fetch_latency, + format_pico_time(pst.sum_timer_fetch) AS fetch_latency, pst.count_insert AS rows_inserted, - sys.format_time(pst.sum_timer_insert) AS insert_latency, + format_pico_time(pst.sum_timer_insert) AS insert_latency, pst.count_update AS rows_updated, - sys.format_time(pst.sum_timer_update) AS update_latency, + format_pico_time(pst.sum_timer_update) AS update_latency, pst.count_delete AS rows_deleted, - sys.format_time(pst.sum_timer_delete) AS delete_latency, + format_pico_time(pst.sum_timer_delete) AS delete_latency, fsbi.count_read AS io_read_requests, sys.format_bytes(fsbi.sum_number_of_bytes_read) AS io_read, - sys.format_time(fsbi.sum_timer_read) AS io_read_latency, + format_pico_time(fsbi.sum_timer_read) AS io_read_latency, fsbi.count_write AS io_write_requests, sys.format_bytes(fsbi.sum_number_of_bytes_write) AS io_write, - sys.format_time(fsbi.sum_timer_write) AS io_write_latency, + format_pico_time(fsbi.sum_timer_write) AS io_write_latency, fsbi.count_misc AS io_misc_requests, - sys.format_time(fsbi.sum_timer_misc) AS io_misc_latency, + format_pico_time(fsbi.sum_timer_misc) AS io_misc_latency, sys.format_bytes(ibp.allocated) AS innodb_buffer_allocated, sys.format_bytes(ibp.data) AS innodb_buffer_data, sys.format_bytes(ibp.allocated - ibp.data) AS innodb_buffer_free, diff --git a/scripts/sys_schema/views/p_s/schema_tables_with_full_table_scans.sql b/scripts/sys_schema/views/p_s/schema_tables_with_full_table_scans.sql index 6199d244..5d7c898f 100644 --- a/scripts/sys_schema/views/p_s/schema_tables_with_full_table_scans.sql +++ b/scripts/sys_schema/views/p_s/schema_tables_with_full_table_scans.sql @@ -44,7 +44,7 @@ VIEW schema_tables_with_full_table_scans ( SELECT object_schema, object_name, count_read AS rows_full_scanned, - sys.format_time(sum_timer_wait) AS latency + format_pico_time(sum_timer_wait) AS latency FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NULL AND count_read > 0 diff --git a/scripts/sys_schema/views/p_s/statement_analysis.sql b/scripts/sys_schema/views/p_s/statement_analysis.sql index 0d9c9cef..c1853311 100644 --- a/scripts/sys_schema/views/p_s/statement_analysis.sql +++ b/scripts/sys_schema/views/p_s/statement_analysis.sql @@ -82,10 +82,10 @@ SELECT sys.format_statement(DIGEST_TEXT) AS query, COUNT_STAR AS exec_count, SUM_ERRORS AS err_count, SUM_WARNINGS AS warn_count, - sys.format_time(SUM_TIMER_WAIT) AS total_latency, - sys.format_time(MAX_TIMER_WAIT) AS max_latency, - sys.format_time(AVG_TIMER_WAIT) AS avg_latency, - sys.format_time(SUM_LOCK_TIME) AS lock_latency, + format_pico_time(SUM_TIMER_WAIT) AS total_latency, + format_pico_time(MAX_TIMER_WAIT) AS max_latency, + format_pico_time(AVG_TIMER_WAIT) AS avg_latency, + format_pico_time(SUM_LOCK_TIME) AS lock_latency, SUM_ROWS_SENT AS rows_sent, ROUND(IFNULL(SUM_ROWS_SENT / NULLIF(COUNT_STAR, 0), 0)) AS rows_sent_avg, SUM_ROWS_EXAMINED AS rows_examined, diff --git a/scripts/sys_schema/views/p_s/statements_with_full_table_scans.sql b/scripts/sys_schema/views/p_s/statements_with_full_table_scans.sql index 84217364..820a5a9c 100644 --- a/scripts/sys_schema/views/p_s/statements_with_full_table_scans.sql +++ b/scripts/sys_schema/views/p_s/statements_with_full_table_scans.sql @@ -64,7 +64,7 @@ VIEW statements_with_full_table_scans ( 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, + format_pico_time(SUM_TIMER_WAIT) AS total_latency, SUM_NO_INDEX_USED AS no_index_used_count, SUM_NO_GOOD_INDEX_USED AS no_good_index_used_count, ROUND(IFNULL(SUM_NO_INDEX_USED / NULLIF(COUNT_STAR, 0), 0) * 100) AS no_index_used_pct, diff --git a/scripts/sys_schema/views/p_s/statements_with_runtimes_in_95th_percentile.sql b/scripts/sys_schema/views/p_s/statements_with_runtimes_in_95th_percentile.sql index 6e2489ed..51c5e0b1 100644 --- a/scripts/sys_schema/views/p_s/statements_with_runtimes_in_95th_percentile.sql +++ b/scripts/sys_schema/views/p_s/statements_with_runtimes_in_95th_percentile.sql @@ -58,9 +58,9 @@ SELECT sys.format_statement(DIGEST_TEXT) AS query, COUNT_STAR AS exec_count, SUM_ERRORS AS err_count, SUM_WARNINGS AS warn_count, - sys.format_time(SUM_TIMER_WAIT) AS total_latency, - sys.format_time(MAX_TIMER_WAIT) AS max_latency, - sys.format_time(AVG_TIMER_WAIT) AS avg_latency, + format_pico_time(SUM_TIMER_WAIT) AS total_latency, + format_pico_time(MAX_TIMER_WAIT) AS max_latency, + format_pico_time(AVG_TIMER_WAIT) AS avg_latency, SUM_ROWS_SENT AS rows_sent, ROUND(IFNULL(SUM_ROWS_SENT / NULLIF(COUNT_STAR, 0), 0)) AS rows_sent_avg, SUM_ROWS_EXAMINED AS rows_examined, diff --git a/scripts/sys_schema/views/p_s/statements_with_sorting.sql b/scripts/sys_schema/views/p_s/statements_with_sorting.sql index 0216a12e..a26910a0 100644 --- a/scripts/sys_schema/views/p_s/statements_with_sorting.sql +++ b/scripts/sys_schema/views/p_s/statements_with_sorting.sql @@ -58,7 +58,7 @@ VIEW statements_with_sorting ( SELECT sys.format_statement(DIGEST_TEXT) AS query, SCHEMA_NAME db, COUNT_STAR AS exec_count, - sys.format_time(SUM_TIMER_WAIT) AS total_latency, + format_pico_time(SUM_TIMER_WAIT) AS total_latency, SUM_SORT_MERGE_PASSES AS sort_merge_passes, ROUND(IFNULL(SUM_SORT_MERGE_PASSES / NULLIF(COUNT_STAR, 0), 0)) AS avg_sort_merges, SUM_SORT_SCAN AS sorts_using_scans, diff --git a/scripts/sys_schema/views/p_s/statements_with_temp_tables.sql b/scripts/sys_schema/views/p_s/statements_with_temp_tables.sql index 3f9dfbc6..687b303f 100644 --- a/scripts/sys_schema/views/p_s/statements_with_temp_tables.sql +++ b/scripts/sys_schema/views/p_s/statements_with_temp_tables.sql @@ -55,7 +55,7 @@ VIEW statements_with_temp_tables ( 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, + format_pico_time(SUM_TIMER_WAIT) as total_latency, SUM_CREATED_TMP_TABLES AS memory_tmp_tables, SUM_CREATED_TMP_DISK_TABLES AS disk_tmp_tables, ROUND(IFNULL(SUM_CREATED_TMP_TABLES / NULLIF(COUNT_STAR, 0), 0)) AS avg_tmp_tables_per_query, diff --git a/scripts/sys_schema/views/p_s/user_summary.sql b/scripts/sys_schema/views/p_s/user_summary.sql index 85f08878..a0d1e308 100644 --- a/scripts/sys_schema/views/p_s/user_summary.sql +++ b/scripts/sys_schema/views/p_s/user_summary.sql @@ -46,11 +46,11 @@ VIEW user_summary ( ) AS SELECT IF(accounts.user IS NULL, 'background', accounts.user) AS user, SUM(stmt.total) AS statements, - sys.format_time(SUM(stmt.total_latency)) AS statement_latency, - sys.format_time(IFNULL(SUM(stmt.total_latency) / NULLIF(SUM(stmt.total), 0), 0)) AS statement_avg_latency, + format_pico_time(SUM(stmt.total_latency)) AS statement_latency, + format_pico_time(IFNULL(SUM(stmt.total_latency) / NULLIF(SUM(stmt.total), 0), 0)) AS statement_avg_latency, SUM(stmt.full_scans) AS table_scans, SUM(io.ios) AS file_ios, - sys.format_time(SUM(io.io_latency)) AS file_io_latency, + format_pico_time(SUM(io.io_latency)) AS file_io_latency, SUM(accounts.current_connections) AS current_connections, SUM(accounts.total_connections) AS total_connections, COUNT(DISTINCT host) AS unique_hosts diff --git a/scripts/sys_schema/views/p_s/user_summary_57.sql b/scripts/sys_schema/views/p_s/user_summary_57.sql index a3147a17..07695689 100644 --- a/scripts/sys_schema/views/p_s/user_summary_57.sql +++ b/scripts/sys_schema/views/p_s/user_summary_57.sql @@ -49,11 +49,11 @@ VIEW user_summary ( ) AS SELECT IF(accounts.user IS NULL, 'background', accounts.user) AS user, SUM(stmt.total) AS statements, - sys.format_time(SUM(stmt.total_latency)) AS statement_latency, - sys.format_time(IFNULL(SUM(stmt.total_latency) / NULLIF(SUM(stmt.total), 0), 0)) AS statement_avg_latency, + format_pico_time(SUM(stmt.total_latency)) AS statement_latency, + format_pico_time(IFNULL(SUM(stmt.total_latency) / NULLIF(SUM(stmt.total), 0), 0)) AS statement_avg_latency, SUM(stmt.full_scans) AS table_scans, SUM(io.ios) AS file_ios, - sys.format_time(SUM(io.io_latency)) AS file_io_latency, + format_pico_time(SUM(io.io_latency)) AS file_io_latency, SUM(accounts.current_connections) AS current_connections, SUM(accounts.total_connections) AS total_connections, COUNT(DISTINCT host) AS unique_hosts, diff --git a/scripts/sys_schema/views/p_s/user_summary_by_file_io.sql b/scripts/sys_schema/views/p_s/user_summary_by_file_io.sql index 85862d50..9afc2028 100644 --- a/scripts/sys_schema/views/p_s/user_summary_by_file_io.sql +++ b/scripts/sys_schema/views/p_s/user_summary_by_file_io.sql @@ -40,7 +40,7 @@ VIEW user_summary_by_file_io ( ) AS SELECT IF(user IS NULL, 'background', user) AS user, SUM(count_star) AS ios, - sys.format_time(SUM(sum_timer_wait)) AS io_latency + format_pico_time(SUM(sum_timer_wait)) AS io_latency FROM performance_schema.events_waits_summary_by_user_by_event_name WHERE event_name LIKE 'wait/io/file/%' GROUP BY IF(user IS NULL, 'background', user) diff --git a/scripts/sys_schema/views/p_s/user_summary_by_file_io_type.sql b/scripts/sys_schema/views/p_s/user_summary_by_file_io_type.sql index 37b4b14b..665f65c3 100644 --- a/scripts/sys_schema/views/p_s/user_summary_by_file_io_type.sql +++ b/scripts/sys_schema/views/p_s/user_summary_by_file_io_type.sql @@ -58,8 +58,8 @@ VIEW user_summary_by_file_io_type ( SELECT IF(user IS NULL, 'background', user) AS user, event_name, count_star AS total, - sys.format_time(sum_timer_wait) AS latency, - sys.format_time(max_timer_wait) AS max_latency + format_pico_time(sum_timer_wait) AS latency, + format_pico_time(max_timer_wait) AS max_latency FROM performance_schema.events_waits_summary_by_user_by_event_name WHERE event_name LIKE 'wait/io/file%' AND count_star > 0 diff --git a/scripts/sys_schema/views/p_s/user_summary_by_stages.sql b/scripts/sys_schema/views/p_s/user_summary_by_stages.sql index ab34a3ee..8706fe06 100644 --- a/scripts/sys_schema/views/p_s/user_summary_by_stages.sql +++ b/scripts/sys_schema/views/p_s/user_summary_by_stages.sql @@ -57,8 +57,8 @@ VIEW user_summary_by_stages ( SELECT IF(user IS NULL, 'background', user) AS user, event_name, count_star AS total, - sys.format_time(sum_timer_wait) AS total_latency, - sys.format_time(avg_timer_wait) AS avg_latency + format_pico_time(sum_timer_wait) AS total_latency, + format_pico_time(avg_timer_wait) AS avg_latency FROM performance_schema.events_stages_summary_by_user_by_event_name WHERE sum_timer_wait != 0 ORDER BY user, sum_timer_wait DESC; diff --git a/scripts/sys_schema/views/p_s/user_summary_by_statement_latency.sql b/scripts/sys_schema/views/p_s/user_summary_by_statement_latency.sql index 2c6e0b50..a07989a4 100644 --- a/scripts/sys_schema/views/p_s/user_summary_by_statement_latency.sql +++ b/scripts/sys_schema/views/p_s/user_summary_by_statement_latency.sql @@ -45,9 +45,9 @@ VIEW user_summary_by_statement_latency ( ) AS SELECT IF(user IS NULL, 'background', user) AS user, SUM(count_star) AS total, - sys.format_time(SUM(sum_timer_wait)) AS total_latency, - sys.format_time(SUM(max_timer_wait)) AS max_latency, - sys.format_time(SUM(sum_lock_time)) AS lock_latency, + format_pico_time(SUM(sum_timer_wait)) AS total_latency, + format_pico_time(SUM(max_timer_wait)) AS max_latency, + format_pico_time(SUM(sum_lock_time)) AS lock_latency, SUM(sum_rows_sent) AS rows_sent, SUM(sum_rows_examined) AS rows_examined, SUM(sum_rows_affected) AS rows_affected, diff --git a/scripts/sys_schema/views/p_s/user_summary_by_statement_type.sql b/scripts/sys_schema/views/p_s/user_summary_by_statement_type.sql index f9ddc2bb..f5a3cfc9 100644 --- a/scripts/sys_schema/views/p_s/user_summary_by_statement_type.sql +++ b/scripts/sys_schema/views/p_s/user_summary_by_statement_type.sql @@ -52,9 +52,9 @@ VIEW user_summary_by_statement_type ( SELECT IF(user IS NULL, 'background', user) AS user, SUBSTRING_INDEX(event_name, '/', -1) AS statement, count_star AS total, - sys.format_time(sum_timer_wait) AS total_latency, - sys.format_time(max_timer_wait) AS max_latency, - sys.format_time(sum_lock_time) AS lock_latency, + format_pico_time(sum_timer_wait) AS total_latency, + format_pico_time(max_timer_wait) AS max_latency, + format_pico_time(sum_lock_time) AS lock_latency, sum_rows_sent AS rows_sent, sum_rows_examined AS rows_examined, sum_rows_affected AS rows_affected, diff --git a/scripts/sys_schema/views/p_s/wait_classes_global_by_avg_latency.sql b/scripts/sys_schema/views/p_s/wait_classes_global_by_avg_latency.sql index faee5823..b707eb27 100644 --- a/scripts/sys_schema/views/p_s/wait_classes_global_by_avg_latency.sql +++ b/scripts/sys_schema/views/p_s/wait_classes_global_by_avg_latency.sql @@ -45,10 +45,10 @@ VIEW wait_classes_global_by_avg_latency ( ) AS SELECT SUBSTRING_INDEX(event_name,'/', 3) AS event_class, SUM(COUNT_STAR) AS total, - sys.format_time(CAST(SUM(sum_timer_wait) AS UNSIGNED)) AS total_latency, - sys.format_time(MIN(min_timer_wait)) AS min_latency, - sys.format_time(IFNULL(SUM(sum_timer_wait) / NULLIF(SUM(COUNT_STAR), 0), 0)) AS avg_latency, - sys.format_time(CAST(MAX(max_timer_wait) AS UNSIGNED)) AS max_latency + format_pico_time(CAST(SUM(sum_timer_wait) AS UNSIGNED)) AS total_latency, + format_pico_time(MIN(min_timer_wait)) AS min_latency, + format_pico_time(IFNULL(SUM(sum_timer_wait) / NULLIF(SUM(COUNT_STAR), 0), 0)) AS avg_latency, + format_pico_time(CAST(MAX(max_timer_wait) AS UNSIGNED)) AS max_latency FROM performance_schema.events_waits_summary_global_by_event_name WHERE sum_timer_wait > 0 AND event_name != 'idle' diff --git a/scripts/sys_schema/views/p_s/wait_classes_global_by_latency.sql b/scripts/sys_schema/views/p_s/wait_classes_global_by_latency.sql index 5675c3f5..c89bf46e 100644 --- a/scripts/sys_schema/views/p_s/wait_classes_global_by_latency.sql +++ b/scripts/sys_schema/views/p_s/wait_classes_global_by_latency.sql @@ -45,10 +45,10 @@ VIEW wait_classes_global_by_latency ( ) AS SELECT SUBSTRING_INDEX(event_name,'/', 3) AS event_class, SUM(COUNT_STAR) AS total, - sys.format_time(SUM(sum_timer_wait)) AS total_latency, - sys.format_time(MIN(min_timer_wait)) min_latency, - sys.format_time(IFNULL(SUM(sum_timer_wait) / NULLIF(SUM(COUNT_STAR), 0), 0)) AS avg_latency, - sys.format_time(MAX(max_timer_wait)) AS max_latency + format_pico_time(SUM(sum_timer_wait)) AS total_latency, + format_pico_time(MIN(min_timer_wait)) min_latency, + format_pico_time(IFNULL(SUM(sum_timer_wait) / NULLIF(SUM(COUNT_STAR), 0), 0)) AS avg_latency, + format_pico_time(MAX(max_timer_wait)) AS max_latency FROM performance_schema.events_waits_summary_global_by_event_name WHERE sum_timer_wait > 0 AND event_name != 'idle' diff --git a/scripts/sys_schema/views/p_s/waits_by_host_by_latency.sql b/scripts/sys_schema/views/p_s/waits_by_host_by_latency.sql index 5587fee2..2ddad55d 100644 --- a/scripts/sys_schema/views/p_s/waits_by_host_by_latency.sql +++ b/scripts/sys_schema/views/p_s/waits_by_host_by_latency.sql @@ -45,9 +45,9 @@ VIEW waits_by_host_by_latency ( SELECT IF(host IS NULL, 'background', host) AS host, event_name AS event, count_star AS total, - sys.format_time(sum_timer_wait) AS total_latency, - sys.format_time(avg_timer_wait) AS avg_latency, - sys.format_time(max_timer_wait) AS max_latency + format_pico_time(sum_timer_wait) AS total_latency, + format_pico_time(avg_timer_wait) AS avg_latency, + format_pico_time(max_timer_wait) AS max_latency FROM performance_schema.events_waits_summary_by_host_by_event_name WHERE event_name != 'idle' AND sum_timer_wait > 0 diff --git a/scripts/sys_schema/views/p_s/waits_by_user_by_latency.sql b/scripts/sys_schema/views/p_s/waits_by_user_by_latency.sql index 5a6a618e..63c71b3e 100644 --- a/scripts/sys_schema/views/p_s/waits_by_user_by_latency.sql +++ b/scripts/sys_schema/views/p_s/waits_by_user_by_latency.sql @@ -55,9 +55,9 @@ VIEW waits_by_user_by_latency ( SELECT IF(user IS NULL, 'background', user) AS user, event_name AS event, count_star AS total, - sys.format_time(sum_timer_wait) AS total_latency, - sys.format_time(avg_timer_wait) AS avg_latency, - sys.format_time(max_timer_wait) AS max_latency + format_pico_time(sum_timer_wait) AS total_latency, + format_pico_time(avg_timer_wait) AS avg_latency, + format_pico_time(max_timer_wait) AS max_latency FROM performance_schema.events_waits_summary_by_user_by_event_name WHERE event_name != 'idle' AND user IS NOT NULL diff --git a/scripts/sys_schema/views/p_s/waits_global_by_latency.sql b/scripts/sys_schema/views/p_s/waits_global_by_latency.sql index a41be3b1..67b1a533 100644 --- a/scripts/sys_schema/views/p_s/waits_global_by_latency.sql +++ b/scripts/sys_schema/views/p_s/waits_global_by_latency.sql @@ -43,9 +43,9 @@ VIEW waits_global_by_latency ( ) AS SELECT event_name AS event, count_star AS total, - sys.format_time(sum_timer_wait) AS total_latency, - sys.format_time(avg_timer_wait) AS avg_latency, - sys.format_time(max_timer_wait) AS max_latency + format_pico_time(sum_timer_wait) AS total_latency, + format_pico_time(avg_timer_wait) AS avg_latency, + format_pico_time(max_timer_wait) AS max_latency FROM performance_schema.events_waits_summary_global_by_event_name WHERE event_name != 'idle' AND sum_timer_wait > 0 diff --git a/scripts/sys_schema/views/version.sql b/scripts/sys_schema/views/version.sql index a25b5315..1210ff08 100644 --- a/scripts/sys_schema/views/version.sql +++ b/scripts/sys_schema/views/version.sql @@ -33,5 +33,5 @@ VIEW version ( sys_version, mysql_version ) AS -SELECT '1.5.1' AS sys_version, - version() AS mysql_version;
\ No newline at end of file +SELECT '1.5.2' AS sys_version, + version() AS mysql_version; |