diff options
Diffstat (limited to 'scripts/sys_schema/procedures/diagnostics.sql')
| -rw-r--r-- | scripts/sys_schema/procedures/diagnostics.sql | 1064 |
1 files changed, 1064 insertions, 0 deletions
diff --git a/scripts/sys_schema/procedures/diagnostics.sql b/scripts/sys_schema/procedures/diagnostics.sql new file mode 100644 index 00000000..2e79c5c1 --- /dev/null +++ b/scripts/sys_schema/procedures/diagnostics.sql @@ -0,0 +1,1064 @@ +-- Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved. +-- +-- 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 diagnostics; + +DELIMITER $$ + +CREATE DEFINER='mariadb.sys'@'localhost' PROCEDURE diagnostics ( + IN in_max_runtime int unsigned, IN in_interval int unsigned, + IN in_auto_config enum ('current', 'medium', 'full') + ) + COMMENT ' + Description + ----------- + + Create a report of the current status of the server for diagnostics purposes. Data collected includes (some items depends on versions and settings): + + * The GLOBAL VARIABLES + * Several sys schema views including metrics or equivalent (depending on version and settings) + * Queries in the 95th percentile + * Several ndbinfo views for MySQL Cluster + * Replication (both master and slave) information. + + Some of the sys schema views are calculated as initial (optional), overall, delta: + + * The initial view is the content of the view at the start of this procedure. + This output will be the same as the the start values used for the delta view. + The initial view is included if @sys.diagnostics.include_raw = ''ON''. + * The overall view is the content of the view at the end of this procedure. + This output is the same as the end values used for the delta view. + The overall view is always included. + * The delta view is the difference from the beginning to the end. Note that for min and max values + they are simply the min or max value from the end view respectively, so does not necessarily reflect + the minimum/maximum value in the monitored period. + Note: except for the metrics views the delta is only calculation between the first and last outputs. + + Requires the SUPER privilege for "SET sql_log_bin = 0;". + + Versions supported: + * MySQL 5.6: 5.6.10 and later + * MySQL 5.7: 5.7.9 and later + + Parameters + ----------- + + in_max_runtime (INT UNSIGNED): + The maximum time to keep collecting data. + Use NULL to get the default which is 60 seconds, otherwise enter a value greater than 0. + in_interval (INT UNSIGNED): + How long to sleep between data collections. + Use NULL to get the default which is 30 seconds, otherwise enter a value greater than 0. + in_auto_config (ENUM(''current'', ''medium'', ''full'')) + Automatically enable Performance Schema instruments and consumers. + NOTE: The more that are enabled, the more impact on the performance. + Supported values are: + * current - use the current settings. + * medium - enable some settings. + * full - enables all settings. This will have a big impact on the + performance - be careful using this option. + If another setting the ''current'' is chosen, the current settings + are restored at the end of the procedure. + + + Configuration Options + ---------------------- + + sys.diagnostics.allow_i_s_tables + Specifies whether it is allowed to do table scan queries on information_schema.TABLES. This can be expensive if there + are many tables. Set to ''ON'' to allow, ''OFF'' to not allow. + Default is ''OFF''. + + sys.diagnostics.include_raw + Set to ''ON'' to include the raw data (e.g. the original output of "SELECT * FROM sys.metrics"). + Use this to get the initial values of the various views. + Default is ''OFF''. + + sys.statement_truncate_len + How much of queries in the process list output to include. + Default is 64. + + sys.debug + Whether to provide debugging output. + Default is ''OFF''. Set to ''ON'' to include. + + + Example + -------- + + To create a report and append it to the file diag.out: + + mysql> TEE diag.out; + mysql> CALL sys.diagnostics(120, 30, ''current''); + ... + mysql> NOTEE; + ' + SQL SECURITY INVOKER + NOT DETERMINISTIC + READS SQL DATA +BEGIN + DECLARE v_start, v_runtime, v_iter_start, v_sleep DECIMAL(20,2) DEFAULT 0.0; + DECLARE v_has_innodb, v_has_ndb, v_has_ps, v_has_replication, v_has_ps_replication VARCHAR(8) CHARSET utf8 DEFAULT 'NO'; + DECLARE v_this_thread_enabled, v_has_ps_vars, v_has_metrics ENUM('YES', 'NO'); + DECLARE v_table_name, v_banner VARCHAR(64) CHARSET utf8; + DECLARE v_sql_status_summary_select, v_sql_status_summary_delta, v_sql_status_summary_from, v_no_delta_names TEXT; + DECLARE v_output_time, v_output_time_prev DECIMAL(20,3) UNSIGNED; + DECLARE v_output_count, v_count, v_old_group_concat_max_len INT UNSIGNED DEFAULT 0; + -- The width of each of the status outputs in the summery + DECLARE v_status_summary_width TINYINT UNSIGNED DEFAULT 50; + DECLARE v_done BOOLEAN DEFAULT FALSE; + -- Do not include the following ndbinfo views: + -- 'blocks' Static + -- 'config_params' Static + -- 'dict_obj_types' Static + -- 'disk_write_speed_base' Can generate lots of output - only include aggregate views here + -- 'memory_per_fragment' Can generate lots of output + -- 'memoryusage' Handled separately + -- 'operations_per_fragment' Can generate lots of output + -- 'threadblocks' Only needed once + DECLARE c_ndbinfo CURSOR FOR + SELECT TABLE_NAME + FROM information_schema.TABLES + WHERE TABLE_SCHEMA = 'ndbinfo' + AND TABLE_NAME NOT IN ( + 'blocks', + 'config_params', + 'dict_obj_types', + 'disk_write_speed_base', + 'memory_per_fragment', + 'memoryusage', + 'operations_per_fragment', + 'threadblocks' + ); + DECLARE c_sysviews_w_delta CURSOR FOR + SELECT table_name + FROM tmp_sys_views_delta + ORDER BY table_name; + DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE; + + -- Do not track the current thread - no reason to clutter the output + SELECT INSTRUMENTED INTO v_this_thread_enabled FROM performance_schema.threads WHERE PROCESSLIST_ID = CONNECTION_ID(); + IF (v_this_thread_enabled = 'YES') THEN + CALL sys.ps_setup_disable_thread(CONNECTION_ID()); + END IF; + + -- Check options are sane + IF (in_max_runtime < in_interval) THEN + SIGNAL SQLSTATE '45000' + SET MESSAGE_TEXT = 'in_max_runtime must be greater than or equal to in_interval'; + END IF; + IF (in_max_runtime = 0) THEN + SIGNAL SQLSTATE '45000' + SET MESSAGE_TEXT = 'in_max_runtime must be greater than 0'; + END IF; + IF (in_interval = 0) THEN + SIGNAL SQLSTATE '45000' + SET MESSAGE_TEXT = 'in_interval must be greater than 0'; + END IF; + + -- Set configuration options + IF (@sys.diagnostics.allow_i_s_tables IS NULL) THEN + SET @sys.diagnostics.allow_i_s_tables = sys.sys_get_config('diagnostics.allow_i_s_tables', 'OFF'); + END IF; + IF (@sys.diagnostics.include_raw IS NULL) THEN + SET @sys.diagnostics.include_raw = sys.sys_get_config('diagnostics.include_raw' , 'OFF'); + END IF; + IF (@sys.debug IS NULL) THEN + SET @sys.debug = sys.sys_get_config('debug' , 'OFF'); + END IF; + IF (@sys.statement_truncate_len IS NULL) THEN + SET @sys.statement_truncate_len = sys.sys_get_config('statement_truncate_len' , '64' ); + END IF; + + -- Temporary table are used - disable sql_log_bin if necessary to prevent them replicating + SET @log_bin := @@sql_log_bin; + IF (@log_bin = 1) THEN + SET sql_log_bin = 0; + END IF; + + -- Some metrics variables doesn't make sense in delta and rate calculations even if they are numeric + -- as they really are more like settings or "current" status. + SET v_no_delta_names = CONCAT('s%{COUNT}.Variable_name NOT IN (', + '''innodb_buffer_pool_pages_total'', ', + '''innodb_page_size'', ', + '''last_query_cost'', ', + '''last_query_partial_plans'', ', + '''qcache_total_blocks'', ', + '''slave_last_heartbeat'', ', + '''ssl_ctx_verify_depth'', ', + '''ssl_ctx_verify_mode'', ', + '''ssl_session_cache_size'', ', + '''ssl_verify_depth'', ', + '''ssl_verify_mode'', ', + '''ssl_version'', ', + '''buffer_flush_lsn_avg_rate'', ', + '''buffer_flush_pct_for_dirty'', ', + '''buffer_flush_pct_for_lsn'', ', + '''buffer_pool_pages_total'', ', + '''lock_row_lock_time_avg'', ', + '''lock_row_lock_time_max'', ', + '''innodb_page_size''', + ')'); + + IF (in_auto_config <> 'current') THEN + IF (@sys.debug = 'ON') THEN + SELECT CONCAT('Updating Performance Schema configuration to ', in_auto_config) AS 'Debug'; + END IF; + CALL sys.ps_setup_save(0); + + IF (in_auto_config = 'medium') THEN + -- Enable all consumers except %history and %history_long + UPDATE performance_schema.setup_consumers + SET ENABLED = 'YES' + WHERE NAME NOT LIKE '%\_history%'; + + -- Enable all instruments except wait/synch/% + UPDATE performance_schema.setup_instruments + SET ENABLED = 'YES', + TIMED = 'YES' + WHERE NAME NOT LIKE 'wait/synch/%'; + ELSEIF (in_auto_config = 'full') THEN + UPDATE performance_schema.setup_consumers + SET ENABLED = 'YES'; + + UPDATE performance_schema.setup_instruments + SET ENABLED = 'YES', + TIMED = 'YES'; + END IF; + + -- Enable all threads except this one + UPDATE performance_schema.threads + SET INSTRUMENTED = 'YES' + WHERE PROCESSLIST_ID <> CONNECTION_ID(); + END IF; + + SET v_start = UNIX_TIMESTAMP(NOW(2)), + in_interval = IFNULL(in_interval, 30), + in_max_runtime = IFNULL(in_max_runtime, 60); + + -- Get a quick ref with hostname, server UUID, and the time for the report. + SET v_banner = REPEAT( + '-', + LEAST( + GREATEST( + 36, + CHAR_LENGTH(VERSION()), + CHAR_LENGTH(@@global.version_comment), + CHAR_LENGTH(@@global.version_compile_os), + CHAR_LENGTH(@@global.version_compile_machine), + CHAR_LENGTH(@@global.socket), + CHAR_LENGTH(@@global.datadir) + ), + 64 + ) + ); + SELECT 'Hostname' AS 'Name', @@global.hostname AS 'Value' + UNION ALL + SELECT 'Port' AS 'Name', @@global.port AS 'Value' + UNION ALL + SELECT 'Socket' AS 'Name', @@global.socket AS 'Value' + UNION ALL + SELECT 'Datadir' AS 'Name', @@global.datadir AS 'Value' + UNION ALL + SELECT REPEAT('-', 23) AS 'Name', v_banner AS 'Value' + UNION ALL + SELECT 'MySQL Version' AS 'Name', VERSION() AS 'Value' + UNION ALL + SELECT 'Sys Schema Version' AS 'Name', (SELECT sys_version FROM sys.version) AS 'Value' + UNION ALL + SELECT 'Version Comment' AS 'Name', @@global.version_comment AS 'Value' + UNION ALL + SELECT 'Version Compile OS' AS 'Name', @@global.version_compile_os AS 'Value' + UNION ALL + SELECT 'Version Compile Machine' AS 'Name', @@global.version_compile_machine AS 'Value' + UNION ALL + SELECT REPEAT('-', 23) AS 'Name', v_banner AS 'Value' + UNION ALL + SELECT 'UTC Time' AS 'Name', UTC_TIMESTAMP() AS 'Value' + UNION ALL + SELECT 'Local Time' AS 'Name', NOW() AS 'Value' + UNION ALL + SELECT 'Time Zone' AS 'Name', @@global.time_zone AS 'Value' + UNION ALL + SELECT 'System Time Zone' AS 'Name', @@global.system_time_zone AS 'Value' + UNION ALL + SELECT 'Time Zone Offset' AS 'Name', TIMEDIFF(NOW(), UTC_TIMESTAMP()) AS 'Value'; + + -- Are the InnoDB, NDBCluster, and Performance Schema storage engines present? + SET v_has_innodb = IFNULL((SELECT SUPPORT FROM information_schema.ENGINES WHERE ENGINE = 'InnoDB'), 'NO'), + v_has_ndb = IFNULL((SELECT SUPPORT FROM information_schema.ENGINES WHERE ENGINE = 'NDBCluster'), 'NO'), + v_has_ps = IFNULL((SELECT SUPPORT FROM information_schema.ENGINES WHERE ENGINE = 'PERFORMANCE_SCHEMA'), 'NO'), + v_has_ps_replication = IF(v_has_ps = 'YES' + AND EXISTS(SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'performance_schema' AND TABLE_NAME = 'replication_applier_status'), + 'YES', + 'NO' + ), + v_has_replication = 'MAYBE', + v_has_metrics = IF(v_has_ps = 'YES' OR (sys.version_major() = 5 AND sys.version_minor() = 6), 'YES', 'NO'), + v_has_ps_vars = 'NO'; + + -- 5.7.7 introduced the possibility to get SHOW [GLOBAL|SESSION] VARIABLES and SHOW [GLOBAL|SESSION] STATUS + -- from the Performance Schema. But it's optional whether it's enabled. + -- 5.7.9 changes so the Performance Schema tables always work. + -- Note that @@global.show_compatibility_56 = OFF will only actually work if the Performance Schema is enabled in <=5.7.8, + -- however except overriding the global value there is nothing that can be done about it. + -- v_has_ps_vars defaults to NO + /*!50707 SET v_has_ps_vars = IF(@@global.show_compatibility_56, 'NO', 'YES');*/ + /*!50709 SET v_has_ps_vars = 'YES';*/ + + IF (@sys.debug = 'ON') THEN + SELECT v_has_innodb AS 'Has_InnoDB', v_has_ndb AS 'Has_NDBCluster', + v_has_ps AS 'Has_Performance_Schema', v_has_ps_vars AS 'Has_P_S_SHOW_Variables', + v_has_metrics AS 'Has_metrics', + v_has_ps_replication 'AS Has_P_S_Replication', v_has_replication AS 'Has_Replication'; + END IF; + + IF (v_has_innodb IN ('DEFAULT', 'YES')) THEN + -- Need to use prepared statement as just having the query as a plain command + -- will generate an error if the InnoDB storage engine is not present + SET @sys.diagnostics.sql = 'SHOW ENGINE InnoDB STATUS'; + PREPARE stmt_innodb_status FROM @sys.diagnostics.sql; + END IF; + + IF (v_has_ps = 'YES') THEN + -- Need to use prepared statement as just having the query as a plain command + -- will generate an error if the InnoDB storage engine is not present + SET @sys.diagnostics.sql = 'SHOW ENGINE PERFORMANCE_SCHEMA STATUS'; + PREPARE stmt_ps_status FROM @sys.diagnostics.sql; + END IF; + + IF (v_has_ndb IN ('DEFAULT', 'YES')) THEN + -- Need to use prepared statement as just having the query as a plain command + -- will generate an error if the NDBCluster storage engine is not present + SET @sys.diagnostics.sql = 'SHOW ENGINE NDBCLUSTER STATUS'; + PREPARE stmt_ndbcluster_status FROM @sys.diagnostics.sql; + END IF; + + SET @sys.diagnostics.sql_gen_query_template = 'SELECT CONCAT( + ''SELECT '', + GROUP_CONCAT( + CASE WHEN (SUBSTRING(TABLE_NAME, 3), COLUMN_NAME) IN ( + (''io_global_by_file_by_bytes'', ''total''), + (''io_global_by_wait_by_bytes'', ''total_requested'') + ) + 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) + 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) + ELSE COLUMN_NAME + END + ORDER BY ORDINAL_POSITION + SEPARATOR '',\n '' + ), + ''\n FROM tmp_'', SUBSTRING(TABLE_NAME FROM 3), ''_%{OUTPUT}'' + ) AS Query INTO @sys.diagnostics.sql_select + FROM information_schema.COLUMNS + WHERE TABLE_SCHEMA = ''sys'' AND TABLE_NAME = ? + GROUP BY TABLE_NAME'; + + SET @sys.diagnostics.sql_gen_query_delta = 'SELECT CONCAT( + ''SELECT '', + GROUP_CONCAT( + CASE WHEN FIND_IN_SET(COLUMN_NAME, diag.pk) + THEN COLUMN_NAME + WHEN diag.TABLE_NAME = ''io_global_by_file_by_bytes'' AND COLUMN_NAME = ''write_pct'' + THEN CONCAT(''IFNULL(ROUND(100-(((e.total_read-IFNULL(s.total_read, 0))'', + ''/NULLIF(((e.total_read-IFNULL(s.total_read, 0))+(e.total_written-IFNULL(s.total_written, 0))), 0))*100), 2), 0.00) AS '', + COLUMN_NAME) + WHEN (diag.TABLE_NAME, COLUMN_NAME) IN ( + (''io_global_by_file_by_bytes'', ''total''), + (''io_global_by_wait_by_bytes'', ''total_requested'') + ) + 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) + WHEN COLUMN_NAME = ''avg_latency'' + THEN CONCAT(''sys.format_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))'', + ''/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) + 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 '', + 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(e.'', COLUMN_NAME, '' - IFNULL(s.'', COLUMN_NAME, '', 0)) AS '', COLUMN_NAME) + ELSE CONCAT(''(e.'', COLUMN_NAME, '' - IFNULL(s.'', COLUMN_NAME, '', 0)) AS '', COLUMN_NAME) + END + ORDER BY ORDINAL_POSITION + SEPARATOR '',\n '' + ), + ''\n FROM tmp_'', diag.TABLE_NAME, ''_end e + LEFT OUTER JOIN tmp_'', diag.TABLE_NAME, ''_start s USING ('', diag.pk, '')'' + ) AS Query INTO @sys.diagnostics.sql_select + FROM tmp_sys_views_delta diag + INNER JOIN information_schema.COLUMNS c ON c.TABLE_NAME = CONCAT(''x$'', diag.TABLE_NAME) + WHERE c.TABLE_SCHEMA = ''sys'' AND diag.TABLE_NAME = ? + GROUP BY diag.TABLE_NAME'; + + IF (v_has_ps = 'YES') THEN + -- Create temporary table with the ORDER BY clauses. Will be required both for the initial (if included) and end queries + DROP TEMPORARY TABLE IF EXISTS tmp_sys_views_delta; + CREATE TEMPORARY TABLE tmp_sys_views_delta ( + TABLE_NAME varchar(64) NOT NULL, + order_by text COMMENT 'ORDER BY clause for the initial and overall views', + order_by_delta text COMMENT 'ORDER BY clause for the delta views', + where_delta text COMMENT 'WHERE clause to use for delta views to only include rows with a "count" > 0', + limit_rows int unsigned COMMENT 'The maximum number of rows to include for the view', + pk varchar(128) COMMENT 'Used with the FIND_IN_SET() function so use comma separated list without whitespace', + PRIMARY KEY (TABLE_NAME) + ); + + -- %{OUTPUT} will be replace by the suffix used for the output. + IF (@sys.debug = 'ON') THEN + SELECT 'Populating tmp_sys_views_delta' AS 'Debug'; + END IF; + INSERT INTO tmp_sys_views_delta + VALUES ('host_summary' , '%{TABLE}.statement_latency DESC', + '(e.statement_latency-IFNULL(s.statement_latency, 0)) DESC', + '(e.statements - IFNULL(s.statements, 0)) > 0', NULL, 'host'), + ('host_summary_by_file_io' , '%{TABLE}.io_latency DESC', + '(e.io_latency-IFNULL(s.io_latency, 0)) DESC', + '(e.ios - IFNULL(s.ios, 0)) > 0', NULL, 'host'), + ('host_summary_by_file_io_type' , '%{TABLE}.host, %{TABLE}.total_latency DESC', + 'e.host, (e.total_latency-IFNULL(s.total_latency, 0)) DESC', + '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'host,event_name'), + ('host_summary_by_stages' , '%{TABLE}.host, %{TABLE}.total_latency DESC', + 'e.host, (e.total_latency-IFNULL(s.total_latency, 0)) DESC', + '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'host,event_name'), + ('host_summary_by_statement_latency' , '%{TABLE}.total_latency DESC', + '(e.total_latency-IFNULL(s.total_latency, 0)) DESC', + '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'host'), + ('host_summary_by_statement_type' , '%{TABLE}.host, %{TABLE}.total_latency DESC', + 'e.host, (e.total_latency-IFNULL(s.total_latency, 0)) DESC', + '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'host,statement'), + ('io_by_thread_by_latency' , '%{TABLE}.total_latency DESC', + '(e.total_latency-IFNULL(s.total_latency, 0)) DESC', + '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'user,thread_id,processlist_id'), + ('io_global_by_file_by_bytes' , '%{TABLE}.total DESC', + '(e.total-IFNULL(s.total, 0)) DESC', + '(e.total - IFNULL(s.total, 0)) > 0', 100, 'file'), + ('io_global_by_file_by_latency' , '%{TABLE}.total_latency DESC', + '(e.total_latency-IFNULL(s.total_latency, 0)) DESC', + '(e.total - IFNULL(s.total, 0)) > 0', 100, 'file'), + ('io_global_by_wait_by_bytes' , '%{TABLE}.total_requested DESC', + '(e.total_requested-IFNULL(s.total_requested, 0)) DESC', + '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'event_name'), + ('io_global_by_wait_by_latency' , '%{TABLE}.total_latency DESC', + '(e.total_latency-IFNULL(s.total_latency, 0)) DESC', + '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'event_name'), + ('schema_index_statistics' , '(%{TABLE}.select_latency+%{TABLE}.insert_latency+%{TABLE}.update_latency+%{TABLE}.delete_latency) DESC', + '((e.select_latency+e.insert_latency+e.update_latency+e.delete_latency)-IFNULL(s.select_latency+s.insert_latency+s.update_latency+s.delete_latency, 0)) DESC', + '((e.rows_selected+e.insert_latency+e.rows_updated+e.rows_deleted)-IFNULL(s.rows_selected+s.rows_inserted+s.rows_updated+s.rows_deleted, 0)) > 0', + 100, 'table_schema,table_name,index_name'), + ('schema_table_statistics' , '%{TABLE}.total_latency DESC', + '(e.total_latency-IFNULL(s.total_latency, 0)) DESC', + '(e.total_latency-IFNULL(s.total_latency, 0)) > 0', 100, 'table_schema,table_name'), + ('schema_tables_with_full_table_scans', '%{TABLE}.rows_full_scanned DESC', + '(e.rows_full_scanned-IFNULL(s.rows_full_scanned, 0)) DESC', + '(e.rows_full_scanned-IFNULL(s.rows_full_scanned, 0)) > 0', 100, 'object_schema,object_name'), + ('user_summary' , '%{TABLE}.statement_latency DESC', + '(e.statement_latency-IFNULL(s.statement_latency, 0)) DESC', + '(e.statements - IFNULL(s.statements, 0)) > 0', NULL, 'user'), + ('user_summary_by_file_io' , '%{TABLE}.io_latency DESC', + '(e.io_latency-IFNULL(s.io_latency, 0)) DESC', + '(e.ios - IFNULL(s.ios, 0)) > 0', NULL, 'user'), + ('user_summary_by_file_io_type' , '%{TABLE}.user, %{TABLE}.latency DESC', + 'e.user, (e.latency-IFNULL(s.latency, 0)) DESC', + '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'user,event_name'), + ('user_summary_by_stages' , '%{TABLE}.user, %{TABLE}.total_latency DESC', + 'e.user, (e.total_latency-IFNULL(s.total_latency, 0)) DESC', + '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'user,event_name'), + ('user_summary_by_statement_latency' , '%{TABLE}.total_latency DESC', + '(e.total_latency-IFNULL(s.total_latency, 0)) DESC', + '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'user'), + ('user_summary_by_statement_type' , '%{TABLE}.user, %{TABLE}.total_latency DESC', + 'e.user, (e.total_latency-IFNULL(s.total_latency, 0)) DESC', + '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'user,statement'), + ('wait_classes_global_by_avg_latency' , 'IFNULL(%{TABLE}.total_latency / NULLIF(%{TABLE}.total, 0), 0) DESC', + 'IFNULL((e.total_latency-IFNULL(s.total_latency, 0)) / NULLIF((e.total - IFNULL(s.total, 0)), 0), 0) DESC', + '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'event_class'), + ('wait_classes_global_by_latency' , '%{TABLE}.total_latency DESC', + '(e.total_latency-IFNULL(s.total_latency, 0)) DESC', + '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'event_class'), + ('waits_by_host_by_latency' , '%{TABLE}.host, %{TABLE}.total_latency DESC', + 'e.host, (e.total_latency-IFNULL(s.total_latency, 0)) DESC', + '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'host,event'), + ('waits_by_user_by_latency' , '%{TABLE}.user, %{TABLE}.total_latency DESC', + 'e.user, (e.total_latency-IFNULL(s.total_latency, 0)) DESC', + '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'user,event'), + ('waits_global_by_latency' , '%{TABLE}.total_latency DESC', + '(e.total_latency-IFNULL(s.total_latency, 0)) DESC', + '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'events') + ; + END IF; + + + SELECT ' + +======================= + + Configuration + +======================= + +' AS ''; + -- Get the configuration. + SELECT 'GLOBAL VARIABLES' AS 'The following output is:'; + IF (v_has_ps_vars = 'YES') THEN + SELECT LOWER(VARIABLE_NAME) AS Variable_name, VARIABLE_VALUE AS Variable_value FROM performance_schema.global_variables ORDER BY VARIABLE_NAME; + ELSE + SELECT LOWER(VARIABLE_NAME) AS Variable_name, VARIABLE_VALUE AS Variable_value FROM information_schema.GLOBAL_VARIABLES ORDER BY VARIABLE_NAME; + END IF; + + IF (v_has_ps = 'YES') THEN + -- Overview of the Performance Schema dynamic settings used for this report. + SELECT 'Performance Schema Setup - Actors' AS 'The following output is:'; + SELECT * FROM performance_schema.setup_actors; + + SELECT 'Performance Schema Setup - Consumers' AS 'The following output is:'; + SELECT NAME AS Consumer, ENABLED, sys.ps_is_consumer_enabled(NAME) AS COLLECTS + FROM performance_schema.setup_consumers; + + SELECT 'Performance Schema Setup - Instruments' AS 'The following output is:'; + SELECT SUBSTRING_INDEX(NAME, '/', 2) AS 'InstrumentClass', + ROUND(100*SUM(IF(ENABLED = 'YES', 1, 0))/COUNT(*), 2) AS 'EnabledPct', + ROUND(100*SUM(IF(TIMED = 'YES', 1, 0))/COUNT(*), 2) AS 'TimedPct' + FROM performance_schema.setup_instruments + GROUP BY SUBSTRING_INDEX(NAME, '/', 2) + ORDER BY SUBSTRING_INDEX(NAME, '/', 2); + + SELECT 'Performance Schema Setup - Objects' AS 'The following output is:'; + SELECT * FROM performance_schema.setup_objects; + + SELECT 'Performance Schema Setup - Threads' AS 'The following output is:'; + SELECT `TYPE` AS ThreadType, COUNT(*) AS 'Total', ROUND(100*SUM(IF(INSTRUMENTED = 'YES', 1, 0))/COUNT(*), 2) AS 'InstrumentedPct' + FROM performance_schema.threads + GROUP BY TYPE; + END IF; + + + IF (v_has_replication = 'NO') THEN + SELECT 'No Replication Configured' AS 'Replication Status'; + ELSE + -- No guarantee that replication is actually configured, but we can't really know + SELECT CONCAT('Replication Configured: ', v_has_replication, ' - Performance Schema Replication Tables: ', v_has_ps_replication) AS 'Replication Status'; + + IF (v_has_ps_replication = 'YES') THEN + SELECT 'Replication - Connection Configuration' AS 'The following output is:'; + SELECT * FROM performance_schema.replication_connection_configuration/*!50706 ORDER BY CHANNEL_NAME*/; + END IF; + + IF (v_has_ps_replication = 'YES') THEN + SELECT 'Replication - Applier Configuration' AS 'The following output is:'; + SELECT * FROM performance_schema.replication_applier_configuration ORDER BY CHANNEL_NAME; + END IF; + END IF; + + + IF (v_has_ndb IN ('DEFAULT', 'YES')) THEN + SELECT 'Cluster Thread Blocks' AS 'The following output is:'; + SELECT * FROM ndbinfo.threadblocks; + END IF; + + -- For a number of sys views as well as events_statements_summary_by_digest, + -- just get the start data and then at the end output the overall and delta values + IF (v_has_ps = 'YES') THEN + IF (@sys.diagnostics.include_raw = 'ON') THEN + SELECT ' + +======================== + + Initial Status + +======================== + +' AS ''; + END IF; + + DROP TEMPORARY TABLE IF EXISTS tmp_digests_start; + CALL sys.statement_performance_analyzer('create_tmp', 'tmp_digests_start', NULL); + CALL sys.statement_performance_analyzer('snapshot', NULL, NULL); + CALL sys.statement_performance_analyzer('save', 'tmp_digests_start', NULL); + + -- Loop over the sys views where deltas should be calculated. + IF (@sys.diagnostics.include_raw = 'ON') THEN + SET @sys.diagnostics.sql = REPLACE(@sys.diagnostics.sql_gen_query_template, '%{OUTPUT}', 'start'); + IF (@sys.debug = 'ON') THEN + SELECT 'The following query will be used to generate the query for each sys view' AS 'Debug'; + SELECT @sys.diagnostics.sql AS 'Debug'; + END IF; + PREPARE stmt_gen_query FROM @sys.diagnostics.sql; + END IF; + SET v_done = FALSE; + OPEN c_sysviews_w_delta; + c_sysviews_w_delta_loop: LOOP + FETCH c_sysviews_w_delta INTO v_table_name; + IF v_done THEN + LEAVE c_sysviews_w_delta_loop; + END IF; + + IF (@sys.debug = 'ON') THEN + SELECT CONCAT('The following queries are for storing the initial content of ', v_table_name) AS 'Debug'; + END IF; + + CALL sys.execute_prepared_stmt(CONCAT('DROP TEMPORARY TABLE IF EXISTS `tmp_', v_table_name, '_start`')); + CALL sys.execute_prepared_stmt(CONCAT('CREATE TEMPORARY TABLE `tmp_', v_table_name, '_start` SELECT * FROM `sys`.`x$', v_table_name, '`')); + + IF (@sys.diagnostics.include_raw = 'ON') THEN + SET @sys.diagnostics.table_name = CONCAT('x$', v_table_name); + EXECUTE stmt_gen_query USING @sys.diagnostics.table_name; + -- If necessary add ORDER BY and LIMIT + SELECT CONCAT(@sys.diagnostics.sql_select, + IF(order_by IS NOT NULL, CONCAT('\n ORDER BY ', REPLACE(order_by, '%{TABLE}', CONCAT('tmp_', v_table_name, '_start'))), ''), + IF(limit_rows IS NOT NULL, CONCAT('\n LIMIT ', limit_rows), '') + ) + INTO @sys.diagnostics.sql_select + FROM tmp_sys_views_delta + WHERE TABLE_NAME = v_table_name; + SELECT CONCAT('Initial ', v_table_name) AS 'The following output is:'; + CALL sys.execute_prepared_stmt(@sys.diagnostics.sql_select); + END IF; + END LOOP; + CLOSE c_sysviews_w_delta; + + IF (@sys.diagnostics.include_raw = 'ON') THEN + DEALLOCATE PREPARE stmt_gen_query; + END IF; + END IF; + + -- If in_include_status_summary is TRUE then a temporary table is required to store the data + SET v_sql_status_summary_select = 'SELECT Variable_name', + v_sql_status_summary_delta = '', + v_sql_status_summary_from = ''; + + -- Start the loop + REPEAT + SET v_output_count = v_output_count + 1; + IF (v_output_count > 1) THEN + -- Don't sleep on the first execution + SET v_sleep = in_interval-(UNIX_TIMESTAMP(NOW(2))-v_iter_start); + SELECT NOW() AS 'Time', CONCAT('Going to sleep for ', v_sleep, ' seconds. Please do not interrupt') AS 'The following output is:'; + DO SLEEP(in_interval); + END IF; + SET v_iter_start = UNIX_TIMESTAMP(NOW(2)); + + SELECT NOW(), CONCAT('Iteration Number ', IFNULL(v_output_count, 'NULL')) AS 'The following output is:'; + + -- Even in 5.7 there is no way to get all the info from SHOW MASTER|SLAVE STATUS using the Performance Schema or + -- other tables, so include them even though they are no longer optimal solutions and if present get the additional + -- information from the other tables available. + IF (@@log_bin = 1) THEN + SELECT 'SHOW MASTER STATUS' AS 'The following output is:'; + SHOW MASTER STATUS; + END IF; + + IF (v_has_replication <> 'NO') THEN + SELECT 'SHOW SLAVE STATUS' AS 'The following output is:'; + SHOW SLAVE STATUS; + END IF; + + -- We need one table per output as a temporary table cannot be opened twice in the same query, and we need to + -- join the outputs in the summary at the end. + SET v_table_name = CONCAT('tmp_metrics_', v_output_count); + CALL sys.execute_prepared_stmt(CONCAT('DROP TEMPORARY TABLE IF EXISTS ', v_table_name)); + + -- Currently information_schema.GLOBAL_STATUS has VARIABLE_VALUE as varchar(1024) + CALL sys.execute_prepared_stmt(CONCAT('CREATE TEMPORARY TABLE ', v_table_name, ' ( + Variable_name VARCHAR(193) NOT NULL, + Variable_value VARCHAR(1024), + Type VARCHAR(100) NOT NULL, + Enabled ENUM(''YES'', ''NO'', ''PARTIAL'') NOT NULL, + PRIMARY KEY (Type, Variable_name) +) ENGINE = InnoDB DEFAULT CHARSET=utf8')); + + IF (v_has_metrics) THEN + SET @sys.diagnostics.sql = CONCAT( + 'INSERT INTO ', v_table_name, + ' SELECT Variable_name, REPLACE(Variable_value, ''\n'', ''\\\\n'') AS Variable_value, Type, Enabled FROM sys.metrics' + ); + ELSE + -- 5.7+ and the Performance Schema disabled. Use information_schema.GLOBAL_STATUS instead like in 5.6. + SET @sys.diagnostics.sql = CONCAT( + 'INSERT INTO ', v_table_name, + '(SELECT LOWER(VARIABLE_NAME) AS Variable_name, REPLACE(VARIABLE_VALUE, ''\n'', ''\\\\n'') AS Variable_value, + ''Global Status'' AS Type, ''YES'' AS Enabled + FROM performance_schema.global_status +) UNION ALL ( +SELECT NAME AS Variable_name, COUNT AS Variable_value, + CONCAT(''InnoDB Metrics - '', SUBSYSTEM) AS Type, + IF(STATUS = ''enabled'', ''YES'', ''NO'') AS Enabled + FROM information_schema.INNODB_METRICS + -- Deduplication - some variables exists both in GLOBAL_STATUS and INNODB_METRICS + -- Keep the one from GLOBAL_STATUS as it is always enabled and it''s more likely to be used for existing tools. + WHERE NAME NOT IN ( + ''lock_row_lock_time'', ''lock_row_lock_time_avg'', ''lock_row_lock_time_max'', ''lock_row_lock_waits'', + ''buffer_pool_reads'', ''buffer_pool_read_requests'', ''buffer_pool_write_requests'', ''buffer_pool_wait_free'', + ''buffer_pool_read_ahead'', ''buffer_pool_read_ahead_evicted'', ''buffer_pool_pages_total'', ''buffer_pool_pages_misc'', + ''buffer_pool_pages_data'', ''buffer_pool_bytes_data'', ''buffer_pool_pages_dirty'', ''buffer_pool_bytes_dirty'', + ''buffer_pool_pages_free'', ''buffer_pages_created'', ''buffer_pages_written'', ''buffer_pages_read'', + ''buffer_data_reads'', ''buffer_data_written'', ''file_num_open_files'', + ''os_log_bytes_written'', ''os_log_fsyncs'', ''os_log_pending_fsyncs'', ''os_log_pending_writes'', + ''log_waits'', ''log_write_requests'', ''log_writes'', ''innodb_dblwr_writes'', ''innodb_dblwr_pages_written'', ''innodb_page_size'') +) UNION ALL ( +SELECT ''NOW()'' AS Variable_name, NOW(3) AS Variable_value, ''System Time'' AS Type, ''YES'' AS Enabled +) UNION ALL ( +SELECT ''UNIX_TIMESTAMP()'' AS Variable_name, ROUND(UNIX_TIMESTAMP(NOW(3)), 3) AS Variable_value, ''System Time'' AS Type, ''YES'' AS Enabled +) + ORDER BY Type, Variable_name;' + ); + END IF; + CALL sys.execute_prepared_stmt(@sys.diagnostics.sql); + + -- Prepare the query to retrieve the summary + CALL sys.execute_prepared_stmt( + CONCAT('SELECT Variable_value INTO @sys.diagnostics.output_time FROM ', v_table_name, ' WHERE Type = ''System Time'' AND Variable_name = ''UNIX_TIMESTAMP()''') + ); + SET v_output_time = @sys.diagnostics.output_time; + + -- Limit each value to v_status_summary_width chars (when v_has_ndb = TRUE the values can be very wide - refer to the output here for the full values) + -- v_sql_status_summary_select, v_sql_status_summary_delta, v_sql_status_summary_from + SET v_sql_status_summary_select = CONCAT(v_sql_status_summary_select, ', + CONCAT( + LEFT(s', v_output_count, '.Variable_value, ', v_status_summary_width, '), + IF(', REPLACE(v_no_delta_names, '%{COUNT}', v_output_count), ' AND s', v_output_count, '.Variable_value REGEXP ''^[0-9]+(\\\\.[0-9]+)?$'', CONCAT('' ('', ROUND(s', v_output_count, '.Variable_value/', v_output_time, ', 2), ''/sec)''), '''') + ) AS ''Output ', v_output_count, ''''), + v_sql_status_summary_from = CONCAT(v_sql_status_summary_from, ' +', + IF(v_output_count = 1, ' FROM ', ' INNER JOIN '), + v_table_name, ' s', v_output_count, + IF (v_output_count = 1, '', ' USING (Type, Variable_name)')); + IF (v_output_count > 1) THEN + SET v_sql_status_summary_delta = CONCAT(v_sql_status_summary_delta, ', + IF(', REPLACE(v_no_delta_names, '%{COUNT}', v_output_count), ' AND s', (v_output_count-1), '.Variable_value REGEXP ''^[0-9]+(\\\\.[0-9]+)?$'' AND s', v_output_count, '.Variable_value REGEXP ''^[0-9]+(\\\\.[0-9]+)?$'', + CONCAT(IF(s', (v_output_count-1), '.Variable_value REGEXP ''^[0-9]+\\\\.[0-9]+$'' OR s', v_output_count, '.Variable_value REGEXP ''^[0-9]+\\\\.[0-9]+$'', + ROUND((s', v_output_count, '.Variable_value-s', (v_output_count-1), '.Variable_value), 2), + (s', v_output_count, '.Variable_value-s', (v_output_count-1), '.Variable_value) + ), + '' ('', ROUND((s', v_output_count, '.Variable_value-s', (v_output_count-1), '.Variable_value)/(', v_output_time, '-', v_output_time_prev, '), 2), ''/sec)'' + ), + '''' + ) AS ''Delta (', (v_output_count-1), ' -> ', v_output_count, ')'''); + END IF; + + SET v_output_time_prev = v_output_time; + + IF (@sys.diagnostics.include_raw = 'ON') THEN + IF (v_has_metrics) THEN + SELECT 'SELECT * FROM sys.metrics' AS 'The following output is:'; + ELSE + SELECT 'sys.metrics equivalent' AS 'The following output is:'; + END IF; + -- Ensures that the output here is the same as the one used in the status summary at the end + CALL sys.execute_prepared_stmt(CONCAT('SELECT Type, Variable_name, Enabled, Variable_value FROM ', v_table_name, ' ORDER BY Type, Variable_name')); + END IF; + + -- InnoDB + IF (v_has_innodb IN ('DEFAULT', 'YES')) THEN + SELECT 'SHOW ENGINE INNODB STATUS' AS 'The following output is:'; + EXECUTE stmt_innodb_status; + SELECT 'InnoDB - Transactions' AS 'The following output is:'; + SELECT * FROM information_schema.INNODB_TRX; + END IF; + + -- NDBCluster + IF (v_has_ndb IN ('DEFAULT', 'YES')) THEN + SELECT 'SHOW ENGINE NDBCLUSTER STATUS' AS 'The following output is:'; + EXECUTE stmt_ndbcluster_status; + + SELECT 'ndbinfo.memoryusage' AS 'The following output is:'; + SELECT node_id, memory_type, sys.format_bytes(used) AS used, used_pages, sys.format_bytes(total) AS total, total_pages, + ROUND(100*(used/total), 2) AS 'Used %' + FROM ndbinfo.memoryusage; + + -- Loop over the ndbinfo tables (except memoryusage which was handled separately above). + -- The exact tables available are version dependent, so get the list from the Information Schema. + SET v_done = FALSE; + OPEN c_ndbinfo; + c_ndbinfo_loop: LOOP + FETCH c_ndbinfo INTO v_table_name; + IF v_done THEN + LEAVE c_ndbinfo_loop; + END IF; + + SELECT CONCAT('SELECT * FROM ndbinfo.', v_table_name) AS 'The following output is:'; + CALL sys.execute_prepared_stmt(CONCAT('SELECT * FROM `ndbinfo`.`', v_table_name, '`')); + END LOOP; + CLOSE c_ndbinfo; + + SELECT * FROM information_schema.FILES; + END IF; + + SELECT 'SELECT * FROM sys.processlist' AS 'The following output is:'; + SELECT processlist.* FROM sys.processlist; + + IF (v_has_ps = 'YES') THEN + -- latest_file_io + IF (sys.ps_is_consumer_enabled('events_waits_history_long') = 'YES') THEN + SELECT 'SELECT * FROM sys.latest_file_io' AS 'The following output is:'; + SELECT * FROM sys.latest_file_io; + END IF; + + -- current memory usage + IF (EXISTS(SELECT 1 FROM performance_schema.setup_instruments WHERE NAME LIKE 'memory/%' AND ENABLED = 'YES')) THEN + SELECT 'SELECT * FROM sys.memory_by_host_by_current_bytes' AS 'The following output is:'; + SELECT * FROM sys.memory_by_host_by_current_bytes; + + SELECT 'SELECT * FROM sys.memory_by_thread_by_current_bytes' AS 'The following output is:'; + SELECT * FROM sys.memory_by_thread_by_current_bytes; + + SELECT 'SELECT * FROM sys.memory_by_user_by_current_bytes' AS 'The following output is:'; + SELECT * FROM sys.memory_by_user_by_current_bytes; + + SELECT 'SELECT * FROM sys.memory_global_by_current_bytes' AS 'The following output is:'; + SELECT * FROM sys.memory_global_by_current_bytes; + END IF; + END IF; + + SET v_runtime = (UNIX_TIMESTAMP(NOW(2)) - v_start); + UNTIL (v_runtime + in_interval >= in_max_runtime) END REPEAT; + + -- Get Performance Schema status + IF (v_has_ps = 'YES') THEN + SELECT 'SHOW ENGINE PERFORMANCE_SCHEMA STATUS' AS 'The following output is:'; + EXECUTE stmt_ps_status; + END IF; + + -- Deallocate prepared statements + IF (v_has_innodb IN ('DEFAULT', 'YES')) THEN + DEALLOCATE PREPARE stmt_innodb_status; + END IF; + IF (v_has_ps = 'YES') THEN + DEALLOCATE PREPARE stmt_ps_status; + END IF; + IF (v_has_ndb IN ('DEFAULT', 'YES')) THEN + DEALLOCATE PREPARE stmt_ndbcluster_status; + END IF; + + + SELECT ' + +============================ + + Schema Information + +============================ + +' AS ''; + + SELECT COUNT(*) AS 'Total Number of Tables' FROM information_schema.TABLES; + + -- The cost of information_schema.TABLES.DATA_LENGTH depends mostly on the number of tables + IF (@sys.diagnostics.allow_i_s_tables = 'ON') THEN + SELECT 'Storage Engine Usage' AS 'The following output is:'; + SELECT ENGINE, COUNT(*) AS NUM_TABLES, + sys.format_bytes(SUM(DATA_LENGTH)) AS DATA_LENGTH, + sys.format_bytes(SUM(INDEX_LENGTH)) AS INDEX_LENGTH, + sys.format_bytes(SUM(DATA_LENGTH+INDEX_LENGTH)) AS TOTAL + FROM information_schema.TABLES + GROUP BY ENGINE; + + SELECT 'Schema Object Overview' AS 'The following output is:'; + SELECT * FROM sys.schema_object_overview; + + SELECT 'Tables without a PRIMARY KEY' AS 'The following output is:'; + SELECT TABLES.TABLE_SCHEMA, ENGINE, COUNT(*) AS NumTables + FROM information_schema.TABLES + LEFT OUTER JOIN information_schema.STATISTICS ON STATISTICS.TABLE_SCHEMA = TABLES.TABLE_SCHEMA + AND STATISTICS.TABLE_NAME = TABLES.TABLE_NAME + AND STATISTICS.INDEX_NAME = 'PRIMARY' + WHERE STATISTICS.TABLE_NAME IS NULL + AND TABLES.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys') + AND TABLES.TABLE_TYPE = 'BASE TABLE' + GROUP BY TABLES.TABLE_SCHEMA, ENGINE; + END IF; + + IF (v_has_ps = 'YES') THEN + SELECT 'Unused Indexes' AS 'The following output is:'; + SELECT object_schema, COUNT(*) AS NumUnusedIndexes + FROM performance_schema.table_io_waits_summary_by_index_usage + WHERE index_name IS NOT NULL + AND count_star = 0 + AND object_schema NOT IN ('mysql', 'sys') + AND index_name != 'PRIMARY' + GROUP BY object_schema; + END IF; + + IF (v_has_ps = 'YES') THEN + SELECT ' + +========================= + + Overall Status + +========================= + +' AS ''; + + SELECT 'CALL sys.ps_statement_avg_latency_histogram()' AS 'The following output is:'; + CALL sys.ps_statement_avg_latency_histogram(); + + CALL sys.statement_performance_analyzer('snapshot', NULL, NULL); + CALL sys.statement_performance_analyzer('overall', NULL, 'with_runtimes_in_95th_percentile'); + + SET @sys.diagnostics.sql = REPLACE(@sys.diagnostics.sql_gen_query_template, '%{OUTPUT}', 'end'); + IF (@sys.debug = 'ON') THEN + SELECT 'The following query will be used to generate the query for each sys view' AS 'Debug'; + SELECT @sys.diagnostics.sql AS 'Debug'; + END IF; + PREPARE stmt_gen_query FROM @sys.diagnostics.sql; + + SET v_done = FALSE; + OPEN c_sysviews_w_delta; + c_sysviews_w_delta_loop: LOOP + FETCH c_sysviews_w_delta INTO v_table_name; + IF v_done THEN + LEAVE c_sysviews_w_delta_loop; + END IF; + + IF (@sys.debug = 'ON') THEN + SELECT CONCAT('The following queries are for storing the final content of ', v_table_name) AS 'Debug'; + END IF; + + CALL sys.execute_prepared_stmt(CONCAT('DROP TEMPORARY TABLE IF EXISTS `tmp_', v_table_name, '_end`')); + CALL sys.execute_prepared_stmt(CONCAT('CREATE TEMPORARY TABLE `tmp_', v_table_name, '_end` SELECT * FROM `sys`.`x$', v_table_name, '`')); + + IF (@sys.diagnostics.include_raw = 'ON') THEN + SET @sys.diagnostics.table_name = CONCAT('x$', v_table_name); + EXECUTE stmt_gen_query USING @sys.diagnostics.table_name; + -- If necessary add ORDER BY and LIMIT + SELECT CONCAT(@sys.diagnostics.sql_select, + IF(order_by IS NOT NULL, CONCAT('\n ORDER BY ', REPLACE(order_by, '%{TABLE}', CONCAT('tmp_', v_table_name, '_end'))), ''), + IF(limit_rows IS NOT NULL, CONCAT('\n LIMIT ', limit_rows), '') + ) + INTO @sys.diagnostics.sql_select + FROM tmp_sys_views_delta + WHERE TABLE_NAME = v_table_name; + SELECT CONCAT('Overall ', v_table_name) AS 'The following output is:'; + CALL sys.execute_prepared_stmt(@sys.diagnostics.sql_select); + END IF; + END LOOP; + CLOSE c_sysviews_w_delta; + + DEALLOCATE PREPARE stmt_gen_query; + + + SELECT ' + +====================== + + Delta Status + +====================== + +' AS ''; + + CALL sys.statement_performance_analyzer('delta', 'tmp_digests_start', 'with_runtimes_in_95th_percentile'); + CALL sys.statement_performance_analyzer('cleanup', NULL, NULL); + + DROP TEMPORARY TABLE tmp_digests_start; + + -- @sys.diagnostics.sql_gen_query_delta is defined near the to together with @sys.diagnostics.sql_gen_query_template + IF (@sys.debug = 'ON') THEN + SELECT 'The following query will be used to generate the query for each sys view delta' AS 'Debug'; + SELECT @sys.diagnostics.sql_gen_query_delta AS 'Debug'; + END IF; + PREPARE stmt_gen_query_delta FROM @sys.diagnostics.sql_gen_query_delta; + + SET v_old_group_concat_max_len = @@session.group_concat_max_len; + SET @@session.group_concat_max_len = 2048; + SET v_done = FALSE; + OPEN c_sysviews_w_delta; + c_sysviews_w_delta_loop: LOOP + FETCH c_sysviews_w_delta INTO v_table_name; + IF v_done THEN + LEAVE c_sysviews_w_delta_loop; + END IF; + + SET @sys.diagnostics.table_name = v_table_name; + EXECUTE stmt_gen_query_delta USING @sys.diagnostics.table_name; + -- If necessary add WHERE, ORDER BY, and LIMIT + SELECT CONCAT(@sys.diagnostics.sql_select, + IF(where_delta IS NOT NULL, CONCAT('\n WHERE ', where_delta), ''), + IF(order_by_delta IS NOT NULL, CONCAT('\n ORDER BY ', order_by_delta), ''), + IF(limit_rows IS NOT NULL, CONCAT('\n LIMIT ', limit_rows), '') + ) + INTO @sys.diagnostics.sql_select + FROM tmp_sys_views_delta + WHERE TABLE_NAME = v_table_name; + + SELECT CONCAT('Delta ', v_table_name) AS 'The following output is:'; + CALL sys.execute_prepared_stmt(@sys.diagnostics.sql_select); + + CALL sys.execute_prepared_stmt(CONCAT('DROP TEMPORARY TABLE `tmp_', v_table_name, '_end`')); + CALL sys.execute_prepared_stmt(CONCAT('DROP TEMPORARY TABLE `tmp_', v_table_name, '_start`')); + END LOOP; + CLOSE c_sysviews_w_delta; + SET @@session.group_concat_max_len = v_old_group_concat_max_len; + + DEALLOCATE PREPARE stmt_gen_query_delta; + DROP TEMPORARY TABLE tmp_sys_views_delta; + END IF; + + IF (v_has_metrics) THEN + SELECT 'SELECT * FROM sys.metrics' AS 'The following output is:'; + ELSE + SELECT 'sys.metrics equivalent' AS 'The following output is:'; + END IF; + CALL sys.execute_prepared_stmt( + CONCAT(v_sql_status_summary_select, v_sql_status_summary_delta, ', Type, s1.Enabled', v_sql_status_summary_from, + ' + ORDER BY Type, Variable_name' + ) + ); + + -- Remove all the metrics temporary tables again + SET v_count = 0; + WHILE (v_count < v_output_count) DO + SET v_count = v_count + 1; + SET v_table_name = CONCAT('tmp_metrics_', v_count); + CALL sys.execute_prepared_stmt(CONCAT('DROP TEMPORARY TABLE IF EXISTS ', v_table_name)); + END WHILE; + + IF (in_auto_config <> 'current') THEN + CALL sys.ps_setup_reload_saved(); + SET sql_log_bin = @log_bin; + END IF; + + -- Reset the @sys.diagnostics.% user variables internal to this procedure + SET @sys.diagnostics.output_time = NULL, + @sys.diagnostics.sql = NULL, + @sys.diagnostics.sql_gen_query_delta = NULL, + @sys.diagnostics.sql_gen_query_template = NULL, + @sys.diagnostics.sql_select = NULL, + @sys.diagnostics.table_name = NULL; + + -- Restore INSTRUMENTED for this thread + IF (v_this_thread_enabled = 'YES') THEN + CALL sys.ps_setup_enable_thread(CONNECTION_ID()); + END IF; + + IF (@log_bin = 1) THEN + SET sql_log_bin = @log_bin; + END IF; +END$$ + +DELIMITER ;
\ No newline at end of file |