1-- Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved. 2-- 3-- This program is free software; you can redistribute it and/or modify 4-- it under the terms of the GNU General Public License as published by 5-- the Free Software Foundation; version 2 of the License. 6-- 7-- This program is distributed in the hope that it will be useful, 8-- but WITHOUT ANY WARRANTY; without even the implied warranty of 9-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 10-- GNU General Public License for more details. 11-- 12-- You should have received a copy of the GNU General Public License 13-- along with this program; if not, write to the Free Software 14-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA 15 16DROP PROCEDURE IF EXISTS diagnostics; 17 18DELIMITER $$ 19 20CREATE DEFINER='root'@'localhost' PROCEDURE diagnostics ( 21 IN in_max_runtime int unsigned, IN in_interval int unsigned, 22 IN in_auto_config enum ('current', 'medium', 'full') 23 ) 24 COMMENT ' 25 Description 26 ----------- 27 28 Create a report of the current status of the server for diagnostics purposes. Data collected includes (some items depends on versions and settings): 29 30 * The GLOBAL VARIABLES 31 * Several sys schema views including metrics or equivalent (depending on version and settings) 32 * Queries in the 95th percentile 33 * Several ndbinfo views for MySQL Cluster 34 * Replication (both master and slave) information. 35 36 Some of the sys schema views are calculated as initial (optional), overall, delta: 37 38 * The initial view is the content of the view at the start of this procedure. 39 This output will be the same as the the start values used for the delta view. 40 The initial view is included if @sys.diagnostics.include_raw = ''ON''. 41 * The overall view is the content of the view at the end of this procedure. 42 This output is the same as the end values used for the delta view. 43 The overall view is always included. 44 * The delta view is the difference from the beginning to the end. Note that for min and max values 45 they are simply the min or max value from the end view respectively, so does not necessarily reflect 46 the minimum/maximum value in the monitored period. 47 Note: except for the metrics views the delta is only calculation between the first and last outputs. 48 49 Requires the SUPER privilege for "SET sql_log_bin = 0;". 50 51 Versions supported: 52 * MySQL 5.6: 5.6.10 and later 53 * MySQL 5.7: 5.7.9 and later 54 55 Parameters 56 ----------- 57 58 in_max_runtime (INT UNSIGNED): 59 The maximum time to keep collecting data. 60 Use NULL to get the default which is 60 seconds, otherwise enter a value greater than 0. 61 in_interval (INT UNSIGNED): 62 How long to sleep between data collections. 63 Use NULL to get the default which is 30 seconds, otherwise enter a value greater than 0. 64 in_auto_config (ENUM(''current'', ''medium'', ''full'')) 65 Automatically enable Performance Schema instruments and consumers. 66 NOTE: The more that are enabled, the more impact on the performance. 67 Supported values are: 68 * current - use the current settings. 69 * medium - enable some settings. This requires the SUPER privilege. 70 * full - enables all settings. This will have a big impact on the 71 performance - be careful using this option. This requires 72 the SUPER privilege. 73 If another setting the ''current'' is chosen, the current settings 74 are restored at the end of the procedure. 75 76 77 Configuration Options 78 ---------------------- 79 80 sys.diagnostics.allow_i_s_tables 81 Specifies whether it is allowed to do table scan queries on information_schema.TABLES. This can be expensive if there 82 are many tables. Set to ''ON'' to allow, ''OFF'' to not allow. 83 Default is ''OFF''. 84 85 sys.diagnostics.include_raw 86 Set to ''ON'' to include the raw data (e.g. the original output of "SELECT * FROM sys.metrics"). 87 Use this to get the initial values of the various views. 88 Default is ''OFF''. 89 90 sys.statement_truncate_len 91 How much of queries in the process list output to include. 92 Default is 64. 93 94 sys.debug 95 Whether to provide debugging output. 96 Default is ''OFF''. Set to ''ON'' to include. 97 98 99 Example 100 -------- 101 102 To create a report and append it to the file diag.out: 103 104 mysql> TEE diag.out; 105 mysql> CALL sys.diagnostics(120, 30, ''current''); 106 ... 107 mysql> NOTEE; 108 ' 109 SQL SECURITY INVOKER 110 NOT DETERMINISTIC 111 READS SQL DATA 112BEGIN 113 DECLARE v_start, v_runtime, v_iter_start, v_sleep DECIMAL(20,2) DEFAULT 0.0; 114 DECLARE v_has_innodb, v_has_ndb, v_has_ps, v_has_replication, v_has_ps_replication VARCHAR(8) CHARSET utf8 DEFAULT 'NO'; 115 DECLARE v_this_thread_enabled, v_has_ps_vars, v_has_metrics ENUM('YES', 'NO'); 116 DECLARE v_table_name, v_banner VARCHAR(64) CHARSET utf8; 117 DECLARE v_sql_status_summary_select, v_sql_status_summary_delta, v_sql_status_summary_from, v_no_delta_names TEXT; 118 DECLARE v_output_time, v_output_time_prev DECIMAL(20,3) UNSIGNED; 119 DECLARE v_output_count, v_count, v_old_group_concat_max_len INT UNSIGNED DEFAULT 0; 120 -- The width of each of the status outputs in the summery 121 DECLARE v_status_summary_width TINYINT UNSIGNED DEFAULT 50; 122 DECLARE v_done BOOLEAN DEFAULT FALSE; 123 -- Do not include the following ndbinfo views: 124 -- 'blocks' Static 125 -- 'config_params' Static 126 -- 'dict_obj_types' Static 127 -- 'disk_write_speed_base' Can generate lots of output - only include aggregate views here 128 -- 'memory_per_fragment' Can generate lots of output 129 -- 'memoryusage' Handled separately 130 -- 'operations_per_fragment' Can generate lots of output 131 -- 'threadblocks' Only needed once 132 DECLARE c_ndbinfo CURSOR FOR 133 SELECT TABLE_NAME 134 FROM information_schema.TABLES 135 WHERE TABLE_SCHEMA = 'ndbinfo' 136 AND TABLE_NAME NOT IN ( 137 'blocks', 138 'config_params', 139 'dict_obj_types', 140 'disk_write_speed_base', 141 'memory_per_fragment', 142 'memoryusage', 143 'operations_per_fragment', 144 'threadblocks' 145 ); 146 DECLARE c_sysviews_w_delta CURSOR FOR 147 SELECT table_name 148 FROM tmp_sys_views_delta 149 ORDER BY table_name; 150 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE; 151 152 -- Do not track the current thread - no reason to clutter the output 153 SELECT INSTRUMENTED INTO v_this_thread_enabled FROM performance_schema.threads WHERE PROCESSLIST_ID = CONNECTION_ID(); 154 IF (v_this_thread_enabled = 'YES') THEN 155 CALL sys.ps_setup_disable_thread(CONNECTION_ID()); 156 END IF; 157 158 -- Check options are sane 159 IF (in_max_runtime < in_interval) THEN 160 SIGNAL SQLSTATE '45000' 161 SET MESSAGE_TEXT = 'in_max_runtime must be greater than or equal to in_interval'; 162 END IF; 163 IF (in_max_runtime = 0) THEN 164 SIGNAL SQLSTATE '45000' 165 SET MESSAGE_TEXT = 'in_max_runtime must be greater than 0'; 166 END IF; 167 IF (in_interval = 0) THEN 168 SIGNAL SQLSTATE '45000' 169 SET MESSAGE_TEXT = 'in_interval must be greater than 0'; 170 END IF; 171 172 -- Set configuration options 173 IF (@sys.diagnostics.allow_i_s_tables IS NULL) THEN 174 SET @sys.diagnostics.allow_i_s_tables = sys.sys_get_config('diagnostics.allow_i_s_tables', 'OFF'); 175 END IF; 176 IF (@sys.diagnostics.include_raw IS NULL) THEN 177 SET @sys.diagnostics.include_raw = sys.sys_get_config('diagnostics.include_raw' , 'OFF'); 178 END IF; 179 IF (@sys.debug IS NULL) THEN 180 SET @sys.debug = sys.sys_get_config('debug' , 'OFF'); 181 END IF; 182 IF (@sys.statement_truncate_len IS NULL) THEN 183 SET @sys.statement_truncate_len = sys.sys_get_config('statement_truncate_len' , '64' ); 184 END IF; 185 186 -- Temorary table are used - disable sql_log_bin if necessary to prevent them replicating 187 SET @log_bin := @@sql_log_bin; 188 IF (@log_bin = 1) THEN 189 SET sql_log_bin = 0; 190 END IF; 191 192 -- Some metrics variables doesn't make sense in delta and rate calculations even if they are numeric 193 -- as they really are more like settings or "current" status. 194 SET v_no_delta_names = CONCAT('s%{COUNT}.Variable_name NOT IN (', 195 '''innodb_buffer_pool_pages_total'', ', 196 '''innodb_page_size'', ', 197 '''last_query_cost'', ', 198 '''last_query_partial_plans'', ', 199 '''qcache_total_blocks'', ', 200 '''slave_last_heartbeat'', ', 201 '''ssl_ctx_verify_depth'', ', 202 '''ssl_ctx_verify_mode'', ', 203 '''ssl_session_cache_size'', ', 204 '''ssl_verify_depth'', ', 205 '''ssl_verify_mode'', ', 206 '''ssl_version'', ', 207 '''buffer_flush_lsn_avg_rate'', ', 208 '''buffer_flush_pct_for_dirty'', ', 209 '''buffer_flush_pct_for_lsn'', ', 210 '''buffer_pool_pages_total'', ', 211 '''lock_row_lock_time_avg'', ', 212 '''lock_row_lock_time_max'', ', 213 '''innodb_page_size''', 214 ')'); 215 216 IF (in_auto_config <> 'current') THEN 217 IF (@sys.debug = 'ON') THEN 218 SELECT CONCAT('Updating Performance Schema configuration to ', in_auto_config) AS 'Debug'; 219 END IF; 220 CALL sys.ps_setup_save(0); 221 222 IF (in_auto_config = 'medium') THEN 223 -- Enable all consumers except %history and %history_long 224 UPDATE performance_schema.setup_consumers 225 SET ENABLED = 'YES' 226 WHERE NAME NOT LIKE '%\_history%'; 227 228 -- Enable all instruments except wait/synch/% 229 UPDATE performance_schema.setup_instruments 230 SET ENABLED = 'YES', 231 TIMED = 'YES' 232 WHERE NAME NOT LIKE 'wait/synch/%'; 233 ELSEIF (in_auto_config = 'full') THEN 234 UPDATE performance_schema.setup_consumers 235 SET ENABLED = 'YES'; 236 237 UPDATE performance_schema.setup_instruments 238 SET ENABLED = 'YES', 239 TIMED = 'YES'; 240 END IF; 241 242 -- Enable all threads except this one 243 UPDATE performance_schema.threads 244 SET INSTRUMENTED = 'YES' 245 WHERE PROCESSLIST_ID <> CONNECTION_ID(); 246 END IF; 247 248 SET v_start = UNIX_TIMESTAMP(NOW(2)), 249 in_interval = IFNULL(in_interval, 30), 250 in_max_runtime = IFNULL(in_max_runtime, 60); 251 252 -- Get a quick ref with hostname, server UUID, and the time for the report. 253 SET v_banner = REPEAT( 254 '-', 255 LEAST( 256 GREATEST( 257 36, 258 CHAR_LENGTH(VERSION()), 259 CHAR_LENGTH(@@global.version_comment), 260 CHAR_LENGTH(@@global.version_compile_os), 261 CHAR_LENGTH(@@global.version_compile_machine), 262 CHAR_LENGTH(@@global.socket), 263 CHAR_LENGTH(@@global.datadir) 264 ), 265 64 266 ) 267 ); 268 SELECT 'Hostname' AS 'Name', @@global.hostname AS 'Value' 269 UNION ALL 270 SELECT 'Port' AS 'Name', @@global.port AS 'Value' 271 UNION ALL 272 SELECT 'Socket' AS 'Name', @@global.socket AS 'Value' 273 UNION ALL 274 SELECT 'Datadir' AS 'Name', @@global.datadir AS 'Value' 275 UNION ALL 276 SELECT 'Server UUID' AS 'Name', @@global.server_uuid AS 'Value' 277 UNION ALL 278 SELECT REPEAT('-', 23) AS 'Name', v_banner AS 'Value' 279 UNION ALL 280 SELECT 'MySQL Version' AS 'Name', VERSION() AS 'Value' 281 UNION ALL 282 SELECT 'Sys Schema Version' AS 'Name', (SELECT sys_version FROM sys.version) AS 'Value' 283 UNION ALL 284 SELECT 'Version Comment' AS 'Name', @@global.version_comment AS 'Value' 285 UNION ALL 286 SELECT 'Version Compile OS' AS 'Name', @@global.version_compile_os AS 'Value' 287 UNION ALL 288 SELECT 'Version Compile Machine' AS 'Name', @@global.version_compile_machine AS 'Value' 289 UNION ALL 290 SELECT REPEAT('-', 23) AS 'Name', v_banner AS 'Value' 291 UNION ALL 292 SELECT 'UTC Time' AS 'Name', UTC_TIMESTAMP() AS 'Value' 293 UNION ALL 294 SELECT 'Local Time' AS 'Name', NOW() AS 'Value' 295 UNION ALL 296 SELECT 'Time Zone' AS 'Name', @@global.time_zone AS 'Value' 297 UNION ALL 298 SELECT 'System Time Zone' AS 'Name', @@global.system_time_zone AS 'Value' 299 UNION ALL 300 SELECT 'Time Zone Offset' AS 'Name', TIMEDIFF(NOW(), UTC_TIMESTAMP()) AS 'Value'; 301 302 -- Are the InnoDB, NDBCluster, and Performance Schema storage engines present? 303 SET v_has_innodb = IFNULL((SELECT SUPPORT FROM information_schema.ENGINES WHERE ENGINE = 'InnoDB'), 'NO'), 304 v_has_ndb = IFNULL((SELECT SUPPORT FROM information_schema.ENGINES WHERE ENGINE = 'NDBCluster'), 'NO'), 305 v_has_ps = IFNULL((SELECT SUPPORT FROM information_schema.ENGINES WHERE ENGINE = 'PERFORMANCE_SCHEMA'), 'NO'), 306 v_has_ps_replication = IF(v_has_ps = 'YES' 307 AND EXISTS(SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'performance_schema' AND TABLE_NAME = 'replication_applier_status'), 308 'YES', 309 'NO' 310 ), 311 v_has_replication = /*!50707 IF(v_has_ps_replication = 'YES', IF((SELECT COUNT(*) FROM performance_schema.replication_connection_status) > 0, 'YES', 'NO'),*/ 312 IF(@@master_info_repository = 'TABLE', IF((SELECT COUNT(*) FROM mysql.slave_master_info) > 0, 'YES', 'NO'), 313 IF(@@relay_log_info_repository = 'TABLE', IF((SELECT COUNT(*) FROM mysql.slave_relay_log_info) > 0, 'YES', 'NO'), 314 'MAYBE'))/*!50707 )*/, 315 v_has_metrics = IF(v_has_ps = 'YES' OR (sys.version_major() = 5 AND sys.version_minor() = 6), 'YES', 'NO'), 316 v_has_ps_vars = 'NO'; 317 318 -- 5.7.7 introduced the possibility to get SHOW [GLOBAL|SESSION] VARIABLES and SHOW [GLOBAL|SESSION] STATUS 319 -- from the Performance Schema. But it's optional whether it's enabled. 320 -- 5.7.9 changes so the Performance Schema tables always work. 321 -- Note that @@global.show_compatibility_56 = OFF will only actually work if the Performance Schema is enabled in <=5.7.8, 322 -- however except overriding the global value there is nothing that can be done about it. 323 -- v_has_ps_vars defaults to NO 324 /*!50707 SET v_has_ps_vars = IF(@@global.show_compatibility_56, 'NO', 'YES');*/ 325 /*!50709 SET v_has_ps_vars = 'YES';*/ 326 327 IF (@sys.debug = 'ON') THEN 328 SELECT v_has_innodb AS 'Has_InnoDB', v_has_ndb AS 'Has_NDBCluster', 329 v_has_ps AS 'Has_Performance_Schema', v_has_ps_vars AS 'Has_P_S_SHOW_Variables', 330 v_has_metrics AS 'Has_metrics', 331 v_has_ps_replication 'AS Has_P_S_Replication', v_has_replication AS 'Has_Replication'; 332 END IF; 333 334 IF (v_has_innodb IN ('DEFAULT', 'YES')) THEN 335 -- Need to use prepared statement as just having the query as a plain command 336 -- will generate an error if the InnoDB storage engine is not present 337 SET @sys.diagnostics.sql = 'SHOW ENGINE InnoDB STATUS'; 338 PREPARE stmt_innodb_status FROM @sys.diagnostics.sql; 339 END IF; 340 341 IF (v_has_ps = 'YES') THEN 342 -- Need to use prepared statement as just having the query as a plain command 343 -- will generate an error if the InnoDB storage engine is not present 344 SET @sys.diagnostics.sql = 'SHOW ENGINE PERFORMANCE_SCHEMA STATUS'; 345 PREPARE stmt_ps_status FROM @sys.diagnostics.sql; 346 END IF; 347 348 IF (v_has_ndb IN ('DEFAULT', 'YES')) THEN 349 -- Need to use prepared statement as just having the query as a plain command 350 -- will generate an error if the NDBCluster storage engine is not present 351 SET @sys.diagnostics.sql = 'SHOW ENGINE NDBCLUSTER STATUS'; 352 PREPARE stmt_ndbcluster_status FROM @sys.diagnostics.sql; 353 END IF; 354 355 SET @sys.diagnostics.sql_gen_query_template = 'SELECT CONCAT( 356 ''SELECT '', 357 GROUP_CONCAT( 358 CASE WHEN (SUBSTRING(TABLE_NAME, 3), COLUMN_NAME) IN ( 359 (''io_global_by_file_by_bytes'', ''total''), 360 (''io_global_by_wait_by_bytes'', ''total_requested'') 361 ) 362 THEN CONCAT(''sys.format_bytes('', COLUMN_NAME, '') AS '', COLUMN_NAME) 363 WHEN SUBSTRING(COLUMN_NAME, -8) = ''_latency'' 364 THEN CONCAT(''sys.format_time('', COLUMN_NAME, '') AS '', COLUMN_NAME) 365 WHEN SUBSTRING(COLUMN_NAME, -7) = ''_memory'' OR SUBSTRING(COLUMN_NAME, -17) = ''_memory_allocated'' 366 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_'') 367 THEN CONCAT(''sys.format_bytes('', COLUMN_NAME, '') AS '', COLUMN_NAME) 368 ELSE COLUMN_NAME 369 END 370 ORDER BY ORDINAL_POSITION 371 SEPARATOR '',\n '' 372 ), 373 ''\n FROM tmp_'', SUBSTRING(TABLE_NAME FROM 3), ''_%{OUTPUT}'' 374 ) AS Query INTO @sys.diagnostics.sql_select 375 FROM information_schema.COLUMNS 376 WHERE TABLE_SCHEMA = ''sys'' AND TABLE_NAME = ? 377 GROUP BY TABLE_NAME'; 378 379 SET @sys.diagnostics.sql_gen_query_delta = 'SELECT CONCAT( 380 ''SELECT '', 381 GROUP_CONCAT( 382 CASE WHEN FIND_IN_SET(COLUMN_NAME, diag.pk) 383 THEN COLUMN_NAME 384 WHEN diag.TABLE_NAME = ''io_global_by_file_by_bytes'' AND COLUMN_NAME = ''write_pct'' 385 THEN CONCAT(''IFNULL(ROUND(100-(((e.total_read-IFNULL(s.total_read, 0))'', 386 ''/NULLIF(((e.total_read-IFNULL(s.total_read, 0))+(e.total_written-IFNULL(s.total_written, 0))), 0))*100), 2), 0.00) AS '', 387 COLUMN_NAME) 388 WHEN (diag.TABLE_NAME, COLUMN_NAME) IN ( 389 (''io_global_by_file_by_bytes'', ''total''), 390 (''io_global_by_wait_by_bytes'', ''total_requested'') 391 ) 392 THEN CONCAT(''sys.format_bytes(e.'', COLUMN_NAME, ''-IFNULL(s.'', COLUMN_NAME, '', 0)) AS '', COLUMN_NAME) 393 WHEN SUBSTRING(COLUMN_NAME, 1, 4) IN (''max_'', ''min_'') AND SUBSTRING(COLUMN_NAME, -8) = ''_latency'' 394 THEN CONCAT(''sys.format_time(e.'', COLUMN_NAME, '') AS '', COLUMN_NAME) 395 WHEN COLUMN_NAME = ''avg_latency'' 396 THEN CONCAT(''sys.format_time((e.total_latency - IFNULL(s.total_latency, 0))'', 397 ''/NULLIF(e.total - IFNULL(s.total, 0), 0)) AS '', COLUMN_NAME) 398 WHEN SUBSTRING(COLUMN_NAME, -12) = ''_avg_latency'' 399 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))'', 400 ''/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) 401 WHEN SUBSTRING(COLUMN_NAME, -8) = ''_latency'' 402 THEN CONCAT(''sys.format_time(e.'', COLUMN_NAME, '' - IFNULL(s.'', COLUMN_NAME, '', 0)) AS '', COLUMN_NAME) 403 WHEN COLUMN_NAME IN (''avg_read'', ''avg_write'', ''avg_written'') 404 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))'', 405 ''/NULLIF(e.count_'', IF(COLUMN_NAME = ''avg_read'', ''read'', ''write''), ''-IFNULL(s.count_'', IF(COLUMN_NAME = ''avg_read'', ''read'', ''write''), '', 0), 0), 0)) AS '', 406 COLUMN_NAME) 407 WHEN SUBSTRING(COLUMN_NAME, -7) = ''_memory'' OR SUBSTRING(COLUMN_NAME, -17) = ''_memory_allocated'' 408 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_'') 409 THEN CONCAT(''sys.format_bytes(e.'', COLUMN_NAME, '' - IFNULL(s.'', COLUMN_NAME, '', 0)) AS '', COLUMN_NAME) 410 ELSE CONCAT(''(e.'', COLUMN_NAME, '' - IFNULL(s.'', COLUMN_NAME, '', 0)) AS '', COLUMN_NAME) 411 END 412 ORDER BY ORDINAL_POSITION 413 SEPARATOR '',\n '' 414 ), 415 ''\n FROM tmp_'', diag.TABLE_NAME, ''_end e 416 LEFT OUTER JOIN tmp_'', diag.TABLE_NAME, ''_start s USING ('', diag.pk, '')'' 417 ) AS Query INTO @sys.diagnostics.sql_select 418 FROM tmp_sys_views_delta diag 419 INNER JOIN information_schema.COLUMNS c ON c.TABLE_NAME = CONCAT(''x$'', diag.TABLE_NAME) 420 WHERE c.TABLE_SCHEMA = ''sys'' AND diag.TABLE_NAME = ? 421 GROUP BY diag.TABLE_NAME'; 422 423 IF (v_has_ps = 'YES') THEN 424 -- Create temporary table with the ORDER BY clauses. Will be required both for the initial (if included) and end queries 425 DROP TEMPORARY TABLE IF EXISTS tmp_sys_views_delta; 426 CREATE TEMPORARY TABLE tmp_sys_views_delta ( 427 TABLE_NAME varchar(64) NOT NULL, 428 order_by text COMMENT 'ORDER BY clause for the initial and overall views', 429 order_by_delta text COMMENT 'ORDER BY clause for the delta views', 430 where_delta text COMMENT 'WHERE clause to use for delta views to only include rows with a "count" > 0', 431 limit_rows int unsigned COMMENT 'The maximum number of rows to include for the view', 432 pk varchar(128) COMMENT 'Used with the FIND_IN_SET() function so use comma separated list without whitespace', 433 PRIMARY KEY (TABLE_NAME) 434 ); 435 436 -- %{OUTPUT} will be replace by the suffix used for the output. 437 IF (@sys.debug = 'ON') THEN 438 SELECT 'Populating tmp_sys_views_delta' AS 'Debug'; 439 END IF; 440 INSERT INTO tmp_sys_views_delta 441 VALUES ('host_summary' , '%{TABLE}.statement_latency DESC', 442 '(e.statement_latency-IFNULL(s.statement_latency, 0)) DESC', 443 '(e.statements - IFNULL(s.statements, 0)) > 0', NULL, 'host'), 444 ('host_summary_by_file_io' , '%{TABLE}.io_latency DESC', 445 '(e.io_latency-IFNULL(s.io_latency, 0)) DESC', 446 '(e.ios - IFNULL(s.ios, 0)) > 0', NULL, 'host'), 447 ('host_summary_by_file_io_type' , '%{TABLE}.host, %{TABLE}.total_latency DESC', 448 'e.host, (e.total_latency-IFNULL(s.total_latency, 0)) DESC', 449 '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'host,event_name'), 450 ('host_summary_by_stages' , '%{TABLE}.host, %{TABLE}.total_latency DESC', 451 'e.host, (e.total_latency-IFNULL(s.total_latency, 0)) DESC', 452 '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'host,event_name'), 453 ('host_summary_by_statement_latency' , '%{TABLE}.total_latency DESC', 454 '(e.total_latency-IFNULL(s.total_latency, 0)) DESC', 455 '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'host'), 456 ('host_summary_by_statement_type' , '%{TABLE}.host, %{TABLE}.total_latency DESC', 457 'e.host, (e.total_latency-IFNULL(s.total_latency, 0)) DESC', 458 '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'host,statement'), 459 ('io_by_thread_by_latency' , '%{TABLE}.total_latency DESC', 460 '(e.total_latency-IFNULL(s.total_latency, 0)) DESC', 461 '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'user,thread_id,processlist_id'), 462 ('io_global_by_file_by_bytes' , '%{TABLE}.total DESC', 463 '(e.total-IFNULL(s.total, 0)) DESC', 464 '(e.total - IFNULL(s.total, 0)) > 0', 100, 'file'), 465 ('io_global_by_file_by_latency' , '%{TABLE}.total_latency DESC', 466 '(e.total_latency-IFNULL(s.total_latency, 0)) DESC', 467 '(e.total - IFNULL(s.total, 0)) > 0', 100, 'file'), 468 ('io_global_by_wait_by_bytes' , '%{TABLE}.total_requested DESC', 469 '(e.total_requested-IFNULL(s.total_requested, 0)) DESC', 470 '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'event_name'), 471 ('io_global_by_wait_by_latency' , '%{TABLE}.total_latency DESC', 472 '(e.total_latency-IFNULL(s.total_latency, 0)) DESC', 473 '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'event_name'), 474 ('schema_index_statistics' , '(%{TABLE}.select_latency+%{TABLE}.insert_latency+%{TABLE}.update_latency+%{TABLE}.delete_latency) DESC', 475 '((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', 476 '((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', 477 100, 'table_schema,table_name,index_name'), 478 ('schema_table_statistics' , '%{TABLE}.total_latency DESC', 479 '(e.total_latency-IFNULL(s.total_latency, 0)) DESC', 480 '(e.total_latency-IFNULL(s.total_latency, 0)) > 0', 100, 'table_schema,table_name'), 481 ('schema_tables_with_full_table_scans', '%{TABLE}.rows_full_scanned DESC', 482 '(e.rows_full_scanned-IFNULL(s.rows_full_scanned, 0)) DESC', 483 '(e.rows_full_scanned-IFNULL(s.rows_full_scanned, 0)) > 0', 100, 'object_schema,object_name'), 484 ('user_summary' , '%{TABLE}.statement_latency DESC', 485 '(e.statement_latency-IFNULL(s.statement_latency, 0)) DESC', 486 '(e.statements - IFNULL(s.statements, 0)) > 0', NULL, 'user'), 487 ('user_summary_by_file_io' , '%{TABLE}.io_latency DESC', 488 '(e.io_latency-IFNULL(s.io_latency, 0)) DESC', 489 '(e.ios - IFNULL(s.ios, 0)) > 0', NULL, 'user'), 490 ('user_summary_by_file_io_type' , '%{TABLE}.user, %{TABLE}.latency DESC', 491 'e.user, (e.latency-IFNULL(s.latency, 0)) DESC', 492 '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'user,event_name'), 493 ('user_summary_by_stages' , '%{TABLE}.user, %{TABLE}.total_latency DESC', 494 'e.user, (e.total_latency-IFNULL(s.total_latency, 0)) DESC', 495 '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'user,event_name'), 496 ('user_summary_by_statement_latency' , '%{TABLE}.total_latency DESC', 497 '(e.total_latency-IFNULL(s.total_latency, 0)) DESC', 498 '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'user'), 499 ('user_summary_by_statement_type' , '%{TABLE}.user, %{TABLE}.total_latency DESC', 500 'e.user, (e.total_latency-IFNULL(s.total_latency, 0)) DESC', 501 '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'user,statement'), 502 ('wait_classes_global_by_avg_latency' , 'IFNULL(%{TABLE}.total_latency / NULLIF(%{TABLE}.total, 0), 0) DESC', 503 'IFNULL((e.total_latency-IFNULL(s.total_latency, 0)) / NULLIF((e.total - IFNULL(s.total, 0)), 0), 0) DESC', 504 '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'event_class'), 505 ('wait_classes_global_by_latency' , '%{TABLE}.total_latency DESC', 506 '(e.total_latency-IFNULL(s.total_latency, 0)) DESC', 507 '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'event_class'), 508 ('waits_by_host_by_latency' , '%{TABLE}.host, %{TABLE}.total_latency DESC', 509 'e.host, (e.total_latency-IFNULL(s.total_latency, 0)) DESC', 510 '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'host,event'), 511 ('waits_by_user_by_latency' , '%{TABLE}.user, %{TABLE}.total_latency DESC', 512 'e.user, (e.total_latency-IFNULL(s.total_latency, 0)) DESC', 513 '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'user,event'), 514 ('waits_global_by_latency' , '%{TABLE}.total_latency DESC', 515 '(e.total_latency-IFNULL(s.total_latency, 0)) DESC', 516 '(e.total - IFNULL(s.total, 0)) > 0', NULL, 'events') 517 ; 518 END IF; 519 520 521 SELECT ' 522 523======================= 524 525 Configuration 526 527======================= 528 529' AS ''; 530 -- Get the configuration. 531 SELECT 'GLOBAL VARIABLES' AS 'The following output is:'; 532 IF (v_has_ps_vars = 'YES') THEN 533 SELECT LOWER(VARIABLE_NAME) AS Variable_name, VARIABLE_VALUE AS Variable_value FROM performance_schema.global_variables ORDER BY VARIABLE_NAME; 534 ELSE 535 SELECT LOWER(VARIABLE_NAME) AS Variable_name, VARIABLE_VALUE AS Variable_value FROM information_schema.GLOBAL_VARIABLES ORDER BY VARIABLE_NAME; 536 END IF; 537 538 IF (v_has_ps = 'YES') THEN 539 -- Overview of the Performance Schema dynamic settings used for this report. 540 SELECT 'Performance Schema Setup - Actors' AS 'The following output is:'; 541 SELECT * FROM performance_schema.setup_actors; 542 543 SELECT 'Performance Schema Setup - Consumers' AS 'The following output is:'; 544 SELECT NAME AS Consumer, ENABLED, sys.ps_is_consumer_enabled(NAME) AS COLLECTS 545 FROM performance_schema.setup_consumers; 546 547 SELECT 'Performance Schema Setup - Instruments' AS 'The following output is:'; 548 SELECT SUBSTRING_INDEX(NAME, '/', 2) AS 'InstrumentClass', 549 ROUND(100*SUM(IF(ENABLED = 'YES', 1, 0))/COUNT(*), 2) AS 'EnabledPct', 550 ROUND(100*SUM(IF(TIMED = 'YES', 1, 0))/COUNT(*), 2) AS 'TimedPct' 551 FROM performance_schema.setup_instruments 552 GROUP BY SUBSTRING_INDEX(NAME, '/', 2) 553 ORDER BY SUBSTRING_INDEX(NAME, '/', 2); 554 555 SELECT 'Performance Schema Setup - Objects' AS 'The following output is:'; 556 SELECT * FROM performance_schema.setup_objects; 557 558 SELECT 'Performance Schema Setup - Threads' AS 'The following output is:'; 559 SELECT `TYPE` AS ThreadType, COUNT(*) AS 'Total', ROUND(100*SUM(IF(INSTRUMENTED = 'YES', 1, 0))/COUNT(*), 2) AS 'InstrumentedPct' 560 FROM performance_schema.threads 561 GROUP BY TYPE; 562 END IF; 563 564 565 IF (v_has_replication = 'NO') THEN 566 SELECT 'No Replication Configured' AS 'Replication Status'; 567 ELSE 568 -- No guarantee that replication is actually configured, but we can't really know 569 SELECT CONCAT('Replication Configured: ', v_has_replication, ' - Performance Schema Replication Tables: ', v_has_ps_replication) AS 'Replication Status'; 570 571 IF (v_has_ps_replication = 'YES') THEN 572 SELECT 'Replication - Connection Configuration' AS 'The following output is:'; 573 SELECT * FROM performance_schema.replication_connection_configuration/*!50706 ORDER BY CHANNEL_NAME*/; 574 END IF; 575 576 IF (v_has_ps_replication = 'YES') THEN 577 SELECT 'Replication - Applier Configuration' AS 'The following output is:'; 578 SELECT * FROM performance_schema.replication_applier_configuration ORDER BY CHANNEL_NAME; 579 END IF; 580 581 IF (@@master_info_repository = 'TABLE') THEN 582 SELECT 'Replication - Master Info Repository Configuration' AS 'The following output is:'; 583 -- Can't just do SELECT * as the password may be present in plain text 584 -- Don't include binary log file and position as that will be determined in each iteration as well 585 SELECT /*!50706 Channel_name, */Host, User_name, Port, Connect_retry, 586 Enabled_ssl, Ssl_ca, Ssl_capath, Ssl_cert, Ssl_cipher, Ssl_key, Ssl_verify_server_cert, 587 Heartbeat, Bind, Ignored_server_ids, Uuid, Retry_count, Ssl_crl, Ssl_crlpath, 588 Enabled_auto_position 589 FROM mysql.slave_master_info/*!50706 ORDER BY Channel_name*/; 590 END IF; 591 592 IF (@@relay_log_info_repository = 'TABLE') THEN 593 SELECT 'Replication - Relay Log Repository Configuration' AS 'The following output is:'; 594 SELECT /*!50706 Channel_name, */Sql_delay, Number_of_workers, Id 595 FROM mysql.slave_relay_log_info/*!50706 ORDER BY Channel_name*/; 596 END IF; 597 END IF; 598 599 600 IF (v_has_ndb IN ('DEFAULT', 'YES')) THEN 601 SELECT 'Cluster Thread Blocks' AS 'The following output is:'; 602 SELECT * FROM ndbinfo.threadblocks; 603 END IF; 604 605 -- For a number of sys views as well as events_statements_summary_by_digest, 606 -- just get the start data and then at the end output the overall and delta values 607 IF (v_has_ps = 'YES') THEN 608 IF (@sys.diagnostics.include_raw = 'ON') THEN 609 SELECT ' 610 611======================== 612 613 Initial Status 614 615======================== 616 617' AS ''; 618 END IF; 619 620 DROP TEMPORARY TABLE IF EXISTS tmp_digests_start; 621 CALL sys.statement_performance_analyzer('create_tmp', 'tmp_digests_start', NULL); 622 CALL sys.statement_performance_analyzer('snapshot', NULL, NULL); 623 CALL sys.statement_performance_analyzer('save', 'tmp_digests_start', NULL); 624 625 -- Loop over the sys views where deltas should be calculated. 626 IF (@sys.diagnostics.include_raw = 'ON') THEN 627 SET @sys.diagnostics.sql = REPLACE(@sys.diagnostics.sql_gen_query_template, '%{OUTPUT}', 'start'); 628 IF (@sys.debug = 'ON') THEN 629 SELECT 'The following query will be used to generate the query for each sys view' AS 'Debug'; 630 SELECT @sys.diagnostics.sql AS 'Debug'; 631 END IF; 632 PREPARE stmt_gen_query FROM @sys.diagnostics.sql; 633 END IF; 634 SET v_done = FALSE; 635 OPEN c_sysviews_w_delta; 636 c_sysviews_w_delta_loop: LOOP 637 FETCH c_sysviews_w_delta INTO v_table_name; 638 IF v_done THEN 639 LEAVE c_sysviews_w_delta_loop; 640 END IF; 641 642 IF (@sys.debug = 'ON') THEN 643 SELECT CONCAT('The following queries are for storing the initial content of ', v_table_name) AS 'Debug'; 644 END IF; 645 646 CALL sys.execute_prepared_stmt(CONCAT('DROP TEMPORARY TABLE IF EXISTS `tmp_', v_table_name, '_start`')); 647 CALL sys.execute_prepared_stmt(CONCAT('CREATE TEMPORARY TABLE `tmp_', v_table_name, '_start` SELECT * FROM `sys`.`x$', v_table_name, '`')); 648 649 IF (@sys.diagnostics.include_raw = 'ON') THEN 650 SET @sys.diagnostics.table_name = CONCAT('x$', v_table_name); 651 EXECUTE stmt_gen_query USING @sys.diagnostics.table_name; 652 -- If necessary add ORDER BY and LIMIT 653 SELECT CONCAT(@sys.diagnostics.sql_select, 654 IF(order_by IS NOT NULL, CONCAT('\n ORDER BY ', REPLACE(order_by, '%{TABLE}', CONCAT('tmp_', v_table_name, '_start'))), ''), 655 IF(limit_rows IS NOT NULL, CONCAT('\n LIMIT ', limit_rows), '') 656 ) 657 INTO @sys.diagnostics.sql_select 658 FROM tmp_sys_views_delta 659 WHERE TABLE_NAME = v_table_name; 660 SELECT CONCAT('Initial ', v_table_name) AS 'The following output is:'; 661 CALL sys.execute_prepared_stmt(@sys.diagnostics.sql_select); 662 END IF; 663 END LOOP; 664 CLOSE c_sysviews_w_delta; 665 666 IF (@sys.diagnostics.include_raw = 'ON') THEN 667 DEALLOCATE PREPARE stmt_gen_query; 668 END IF; 669 END IF; 670 671 -- If in_include_status_summary is TRUE then a temporary table is required to store the data 672 SET v_sql_status_summary_select = 'SELECT Variable_name', 673 v_sql_status_summary_delta = '', 674 v_sql_status_summary_from = ''; 675 676 -- Start the loop 677 REPEAT 678 SET v_output_count = v_output_count + 1; 679 IF (v_output_count > 1) THEN 680 -- Don't sleep on the first execution 681 SET v_sleep = in_interval-(UNIX_TIMESTAMP(NOW(2))-v_iter_start); 682 SELECT NOW() AS 'Time', CONCAT('Going to sleep for ', v_sleep, ' seconds. Please do not interrupt') AS 'The following output is:'; 683 DO SLEEP(in_interval); 684 END IF; 685 SET v_iter_start = UNIX_TIMESTAMP(NOW(2)); 686 687 SELECT NOW(), CONCAT('Iteration Number ', IFNULL(v_output_count, 'NULL')) AS 'The following output is:'; 688 689 -- Even in 5.7 there is no way to get all the info from SHOW MASTER|SLAVE STATUS using the Performance Schema or 690 -- other tables, so include them even though they are no longer optimal solutions and if present get the additional 691 -- information from the other tables available. 692 IF (@@log_bin = 1) THEN 693 SELECT 'SHOW MASTER STATUS' AS 'The following output is:'; 694 SHOW MASTER STATUS; 695 END IF; 696 697 IF (v_has_replication <> 'NO') THEN 698 SELECT 'SHOW SLAVE STATUS' AS 'The following output is:'; 699 SHOW SLAVE STATUS; 700 701 IF (v_has_ps_replication = 'YES') THEN 702 SELECT 'Replication Connection Status' AS 'The following output is:'; 703 SELECT * FROM performance_schema.replication_connection_status; 704 705 SELECT 'Replication Applier Status' AS 'The following output is:'; 706 SELECT * FROM performance_schema.replication_applier_status ORDER BY CHANNEL_NAME; 707 708 SELECT 'Replication Applier Status - Coordinator' AS 'The following output is:'; 709 SELECT * FROM performance_schema.replication_applier_status_by_coordinator ORDER BY CHANNEL_NAME; 710 711 SELECT 'Replication Applier Status - Worker' AS 'The following output is:'; 712 SELECT * FROM performance_schema.replication_applier_status_by_worker ORDER BY CHANNEL_NAME, WORKER_ID; 713 END IF; 714 715 IF (@@master_info_repository = 'TABLE') THEN 716 SELECT 'Replication - Master Log Status' AS 'The following output is:'; 717 SELECT Master_log_name, Master_log_pos FROM mysql.slave_master_info; 718 END IF; 719 720 IF (@@relay_log_info_repository = 'TABLE') THEN 721 SELECT 'Replication - Relay Log Status' AS 'The following output is:'; 722 SELECT sys.format_path(Relay_log_name) AS Relay_log_name, Relay_log_pos, Master_log_name, Master_log_pos FROM mysql.slave_relay_log_info; 723 724 SELECT 'Replication - Worker Status' AS 'The following output is:'; 725 SELECT Id, sys.format_path(Relay_log_name) AS Relay_log_name, Relay_log_pos, Master_log_name, Master_log_pos, 726 sys.format_path(Checkpoint_relay_log_name) AS Checkpoint_relay_log_name, Checkpoint_relay_log_pos, 727 Checkpoint_master_log_name, Checkpoint_master_log_pos, Checkpoint_seqno, Checkpoint_group_size, 728 HEX(Checkpoint_group_bitmap) AS Checkpoint_group_bitmap/*!50706 , Channel_name*/ 729 FROM mysql.slave_worker_info 730 ORDER BY /*!50706 Channel_name, */Id; 731 END IF; 732 END IF; 733 734 -- We need one table per output as a temporary table cannot be opened twice in the same query, and we need to 735 -- join the outputs in the summary at the end. 736 SET v_table_name = CONCAT('tmp_metrics_', v_output_count); 737 CALL sys.execute_prepared_stmt(CONCAT('DROP TEMPORARY TABLE IF EXISTS ', v_table_name)); 738 739 -- Currently information_schema.GLOBAL_STATUS has VARIABLE_VALUE as varchar(1024) 740 CALL sys.execute_prepared_stmt(CONCAT('CREATE TEMPORARY TABLE ', v_table_name, ' ( 741 Variable_name VARCHAR(193) NOT NULL, 742 Variable_value VARCHAR(1024), 743 Type VARCHAR(225) NOT NULL, 744 Enabled ENUM(''YES'', ''NO'', ''PARTIAL'') NOT NULL, 745 PRIMARY KEY (Type, Variable_name) 746) ENGINE = InnoDB DEFAULT CHARSET=utf8')); 747 748 IF (v_has_metrics) THEN 749 SET @sys.diagnostics.sql = CONCAT( 750 'INSERT INTO ', v_table_name, 751 ' SELECT Variable_name, REPLACE(Variable_value, ''\n'', ''\\\\n'') AS Variable_value, Type, Enabled FROM sys.metrics' 752 ); 753 ELSE 754 -- 5.7+ and the Performance Schema disabled. Use information_schema.GLOBAL_STATUS instead like in 5.6. 755 SET @sys.diagnostics.sql = CONCAT( 756 'INSERT INTO ', v_table_name, 757 '(SELECT LOWER(VARIABLE_NAME) AS Variable_name, REPLACE(VARIABLE_VALUE, ''\n'', ''\\\\n'') AS Variable_value, 758 ''Global Status'' AS Type, ''YES'' AS Enabled 759 FROM performance_schema.global_status 760) UNION ALL ( 761SELECT NAME AS Variable_name, COUNT AS Variable_value, 762 CONCAT(''InnoDB Metrics - '', SUBSYSTEM) AS Type, 763 IF(STATUS = ''enabled'', ''YES'', ''NO'') AS Enabled 764 FROM information_schema.INNODB_METRICS 765 -- Deduplication - some variables exists both in GLOBAL_STATUS and INNODB_METRICS 766 -- Keep the one from GLOBAL_STATUS as it is always enabled and it''s more likely to be used for existing tools. 767 WHERE NAME NOT IN ( 768 ''lock_row_lock_time'', ''lock_row_lock_time_avg'', ''lock_row_lock_time_max'', ''lock_row_lock_waits'', 769 ''buffer_pool_reads'', ''buffer_pool_read_requests'', ''buffer_pool_write_requests'', ''buffer_pool_wait_free'', 770 ''buffer_pool_read_ahead'', ''buffer_pool_read_ahead_evicted'', ''buffer_pool_pages_total'', ''buffer_pool_pages_misc'', 771 ''buffer_pool_pages_data'', ''buffer_pool_bytes_data'', ''buffer_pool_pages_dirty'', ''buffer_pool_bytes_dirty'', 772 ''buffer_pool_pages_free'', ''buffer_pages_created'', ''buffer_pages_written'', ''buffer_pages_read'', 773 ''buffer_data_reads'', ''buffer_data_written'', ''file_num_open_files'', 774 ''os_log_bytes_written'', ''os_log_fsyncs'', ''os_log_pending_fsyncs'', ''os_log_pending_writes'', 775 ''log_waits'', ''log_write_requests'', ''log_writes'', ''innodb_dblwr_writes'', ''innodb_dblwr_pages_written'', ''innodb_page_size'') 776) UNION ALL ( 777SELECT ''NOW()'' AS Variable_name, NOW(3) AS Variable_value, ''System Time'' AS Type, ''YES'' AS Enabled 778) UNION ALL ( 779SELECT ''UNIX_TIMESTAMP()'' AS Variable_name, ROUND(UNIX_TIMESTAMP(NOW(3)), 3) AS Variable_value, ''System Time'' AS Type, ''YES'' AS Enabled 780) 781 ORDER BY Type, Variable_name;' 782 ); 783 END IF; 784 CALL sys.execute_prepared_stmt(@sys.diagnostics.sql); 785 786 -- Prepare the query to retrieve the summary 787 CALL sys.execute_prepared_stmt( 788 CONCAT('(SELECT Variable_value INTO @sys.diagnostics.output_time FROM ', v_table_name, ' WHERE Type = ''System Time'' AND Variable_name = ''UNIX_TIMESTAMP()'')') 789 ); 790 SET v_output_time = @sys.diagnostics.output_time; 791 792 -- 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) 793 -- v_sql_status_summary_select, v_sql_status_summary_delta, v_sql_status_summary_from 794 SET v_sql_status_summary_select = CONCAT(v_sql_status_summary_select, ', 795 CONCAT( 796 LEFT(s', v_output_count, '.Variable_value, ', v_status_summary_width, '), 797 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)''), '''') 798 ) AS ''Output ', v_output_count, ''''), 799 v_sql_status_summary_from = CONCAT(v_sql_status_summary_from, ' 800', 801 IF(v_output_count = 1, ' FROM ', ' INNER JOIN '), 802 v_table_name, ' s', v_output_count, 803 IF (v_output_count = 1, '', ' USING (Type, Variable_name)')); 804 IF (v_output_count > 1) THEN 805 SET v_sql_status_summary_delta = CONCAT(v_sql_status_summary_delta, ', 806 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]+)?$'', 807 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]+$'', 808 ROUND((s', v_output_count, '.Variable_value-s', (v_output_count-1), '.Variable_value), 2), 809 (s', v_output_count, '.Variable_value-s', (v_output_count-1), '.Variable_value) 810 ), 811 '' ('', ROUND((s', v_output_count, '.Variable_value-s', (v_output_count-1), '.Variable_value)/(', v_output_time, '-', v_output_time_prev, '), 2), ''/sec)'' 812 ), 813 '''' 814 ) AS ''Delta (', (v_output_count-1), ' -> ', v_output_count, ')'''); 815 END IF; 816 817 SET v_output_time_prev = v_output_time; 818 819 IF (@sys.diagnostics.include_raw = 'ON') THEN 820 IF (v_has_metrics) THEN 821 SELECT 'SELECT * FROM sys.metrics' AS 'The following output is:'; 822 ELSE 823 SELECT 'sys.metrics equivalent' AS 'The following output is:'; 824 END IF; 825 -- Ensures that the output here is the same as the one used in the status summary at the end 826 CALL sys.execute_prepared_stmt(CONCAT('SELECT Type, Variable_name, Enabled, Variable_value FROM ', v_table_name, ' ORDER BY Type, Variable_name')); 827 END IF; 828 829 -- InnoDB 830 IF (v_has_innodb IN ('DEFAULT', 'YES')) THEN 831 SELECT 'SHOW ENGINE INNODB STATUS' AS 'The following output is:'; 832 EXECUTE stmt_innodb_status; 833 SELECT 'InnoDB - Transactions' AS 'The following output is:'; 834 SELECT * FROM information_schema.INNODB_TRX; 835 END IF; 836 837 -- NDBCluster 838 IF (v_has_ndb IN ('DEFAULT', 'YES')) THEN 839 SELECT 'SHOW ENGINE NDBCLUSTER STATUS' AS 'The following output is:'; 840 EXECUTE stmt_ndbcluster_status; 841 842 SELECT 'ndbinfo.memoryusage' AS 'The following output is:'; 843 SELECT node_id, memory_type, sys.format_bytes(used) AS used, used_pages, sys.format_bytes(total) AS total, total_pages, 844 ROUND(100*(used/total), 2) AS 'Used %' 845 FROM ndbinfo.memoryusage; 846 847 -- Loop over the ndbinfo tables (except memoryusage which was handled separately above). 848 -- The exact tables available are version dependent, so get the list from the Information Schema. 849 SET v_done = FALSE; 850 OPEN c_ndbinfo; 851 c_ndbinfo_loop: LOOP 852 FETCH c_ndbinfo INTO v_table_name; 853 IF v_done THEN 854 LEAVE c_ndbinfo_loop; 855 END IF; 856 857 SELECT CONCAT('SELECT * FROM ndbinfo.', v_table_name) AS 'The following output is:'; 858 CALL sys.execute_prepared_stmt(CONCAT('SELECT * FROM `ndbinfo`.`', v_table_name, '`')); 859 END LOOP; 860 CLOSE c_ndbinfo; 861 862 SELECT * FROM information_schema.FILES; 863 END IF; 864 865 SELECT 'SELECT * FROM sys.processlist' AS 'The following output is:'; 866 SELECT processlist.* FROM sys.processlist; 867 868 IF (v_has_ps = 'YES') THEN 869 -- latest_file_io 870 IF (sys.ps_is_consumer_enabled('events_waits_history_long') = 'YES') THEN 871 SELECT 'SELECT * FROM sys.latest_file_io' AS 'The following output is:'; 872 SELECT * FROM sys.latest_file_io; 873 END IF; 874 875 -- current memory usage 876 IF (EXISTS(SELECT 1 FROM performance_schema.setup_instruments WHERE NAME LIKE 'memory/%' AND ENABLED = 'YES')) THEN 877 SELECT 'SELECT * FROM sys.memory_by_host_by_current_bytes' AS 'The following output is:'; 878 SELECT * FROM sys.memory_by_host_by_current_bytes; 879 880 SELECT 'SELECT * FROM sys.memory_by_thread_by_current_bytes' AS 'The following output is:'; 881 SELECT * FROM sys.memory_by_thread_by_current_bytes; 882 883 SELECT 'SELECT * FROM sys.memory_by_user_by_current_bytes' AS 'The following output is:'; 884 SELECT * FROM sys.memory_by_user_by_current_bytes; 885 886 SELECT 'SELECT * FROM sys.memory_global_by_current_bytes' AS 'The following output is:'; 887 SELECT * FROM sys.memory_global_by_current_bytes; 888 END IF; 889 END IF; 890 891 SET v_runtime = (UNIX_TIMESTAMP(NOW(2)) - v_start); 892 UNTIL (v_runtime + in_interval >= in_max_runtime) END REPEAT; 893 894 -- Get Performance Schema status 895 IF (v_has_ps = 'YES') THEN 896 SELECT 'SHOW ENGINE PERFORMANCE_SCHEMA STATUS' AS 'The following output is:'; 897 EXECUTE stmt_ps_status; 898 END IF; 899 900 -- Deallocate prepared statements 901 IF (v_has_innodb IN ('DEFAULT', 'YES')) THEN 902 DEALLOCATE PREPARE stmt_innodb_status; 903 END IF; 904 IF (v_has_ps = 'YES') THEN 905 DEALLOCATE PREPARE stmt_ps_status; 906 END IF; 907 IF (v_has_ndb IN ('DEFAULT', 'YES')) THEN 908 DEALLOCATE PREPARE stmt_ndbcluster_status; 909 END IF; 910 911 912 SELECT ' 913 914============================ 915 916 Schema Information 917 918============================ 919 920' AS ''; 921 922 SELECT COUNT(*) AS 'Total Number of Tables' FROM information_schema.TABLES; 923 924 -- The cost of information_schema.TABLES.DATA_LENGTH depends mostly on the number of tables 925 IF (@sys.diagnostics.allow_i_s_tables = 'ON') THEN 926 SELECT 'Storage Engine Usage' AS 'The following output is:'; 927 SELECT ENGINE, COUNT(*) AS NUM_TABLES, 928 sys.format_bytes(SUM(DATA_LENGTH)) AS DATA_LENGTH, 929 sys.format_bytes(SUM(INDEX_LENGTH)) AS INDEX_LENGTH, 930 sys.format_bytes(SUM(DATA_LENGTH+INDEX_LENGTH)) AS TOTAL 931 FROM information_schema.TABLES 932 GROUP BY ENGINE; 933 934 SELECT 'Schema Object Overview' AS 'The following output is:'; 935 SELECT * FROM sys.schema_object_overview; 936 937 SELECT 'Tables without a PRIMARY KEY' AS 'The following output is:'; 938 SELECT TABLES.TABLE_SCHEMA, ENGINE, COUNT(*) AS NumTables 939 FROM information_schema.TABLES 940 LEFT OUTER JOIN information_schema.STATISTICS ON STATISTICS.TABLE_SCHEMA = TABLES.TABLE_SCHEMA 941 AND STATISTICS.TABLE_NAME = TABLES.TABLE_NAME 942 AND STATISTICS.INDEX_NAME = 'PRIMARY' 943 WHERE STATISTICS.TABLE_NAME IS NULL 944 AND TABLES.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys') 945 AND TABLES.TABLE_TYPE = 'BASE TABLE' 946 GROUP BY TABLES.TABLE_SCHEMA, ENGINE; 947 END IF; 948 949 IF (v_has_ps = 'YES') THEN 950 SELECT 'Unused Indexes' AS 'The following output is:'; 951 SELECT object_schema, COUNT(*) AS NumUnusedIndexes 952 FROM performance_schema.table_io_waits_summary_by_index_usage 953 WHERE index_name IS NOT NULL 954 AND count_star = 0 955 AND object_schema NOT IN ('mysql', 'sys') 956 AND index_name != 'PRIMARY' 957 GROUP BY object_schema; 958 END IF; 959 960 IF (v_has_ps = 'YES') THEN 961 SELECT ' 962 963========================= 964 965 Overall Status 966 967========================= 968 969' AS ''; 970 971 SELECT 'CALL sys.ps_statement_avg_latency_histogram()' AS 'The following output is:'; 972 CALL sys.ps_statement_avg_latency_histogram(); 973 974 CALL sys.statement_performance_analyzer('snapshot', NULL, NULL); 975 CALL sys.statement_performance_analyzer('overall', NULL, 'with_runtimes_in_95th_percentile'); 976 977 SET @sys.diagnostics.sql = REPLACE(@sys.diagnostics.sql_gen_query_template, '%{OUTPUT}', 'end'); 978 IF (@sys.debug = 'ON') THEN 979 SELECT 'The following query will be used to generate the query for each sys view' AS 'Debug'; 980 SELECT @sys.diagnostics.sql AS 'Debug'; 981 END IF; 982 PREPARE stmt_gen_query FROM @sys.diagnostics.sql; 983 984 SET v_done = FALSE; 985 OPEN c_sysviews_w_delta; 986 c_sysviews_w_delta_loop: LOOP 987 FETCH c_sysviews_w_delta INTO v_table_name; 988 IF v_done THEN 989 LEAVE c_sysviews_w_delta_loop; 990 END IF; 991 992 IF (@sys.debug = 'ON') THEN 993 SELECT CONCAT('The following queries are for storing the final content of ', v_table_name) AS 'Debug'; 994 END IF; 995 996 CALL sys.execute_prepared_stmt(CONCAT('DROP TEMPORARY TABLE IF EXISTS `tmp_', v_table_name, '_end`')); 997 CALL sys.execute_prepared_stmt(CONCAT('CREATE TEMPORARY TABLE `tmp_', v_table_name, '_end` SELECT * FROM `sys`.`x$', v_table_name, '`')); 998 999 IF (@sys.diagnostics.include_raw = 'ON') THEN 1000 SET @sys.diagnostics.table_name = CONCAT('x$', v_table_name); 1001 EXECUTE stmt_gen_query USING @sys.diagnostics.table_name; 1002 -- If necessary add ORDER BY and LIMIT 1003 SELECT CONCAT(@sys.diagnostics.sql_select, 1004 IF(order_by IS NOT NULL, CONCAT('\n ORDER BY ', REPLACE(order_by, '%{TABLE}', CONCAT('tmp_', v_table_name, '_end'))), ''), 1005 IF(limit_rows IS NOT NULL, CONCAT('\n LIMIT ', limit_rows), '') 1006 ) 1007 INTO @sys.diagnostics.sql_select 1008 FROM tmp_sys_views_delta 1009 WHERE TABLE_NAME = v_table_name; 1010 SELECT CONCAT('Overall ', v_table_name) AS 'The following output is:'; 1011 CALL sys.execute_prepared_stmt(@sys.diagnostics.sql_select); 1012 END IF; 1013 END LOOP; 1014 CLOSE c_sysviews_w_delta; 1015 1016 DEALLOCATE PREPARE stmt_gen_query; 1017 1018 1019 SELECT ' 1020 1021====================== 1022 1023 Delta Status 1024 1025====================== 1026 1027' AS ''; 1028 1029 CALL sys.statement_performance_analyzer('delta', 'tmp_digests_start', 'with_runtimes_in_95th_percentile'); 1030 CALL sys.statement_performance_analyzer('cleanup', NULL, NULL); 1031 1032 DROP TEMPORARY TABLE tmp_digests_start; 1033 1034 -- @sys.diagnostics.sql_gen_query_delta is defined near the to together with @sys.diagnostics.sql_gen_query_template 1035 IF (@sys.debug = 'ON') THEN 1036 SELECT 'The following query will be used to generate the query for each sys view delta' AS 'Debug'; 1037 SELECT @sys.diagnostics.sql_gen_query_delta AS 'Debug'; 1038 END IF; 1039 PREPARE stmt_gen_query_delta FROM @sys.diagnostics.sql_gen_query_delta; 1040 1041 SET v_old_group_concat_max_len = @@session.group_concat_max_len; 1042 SET @@session.group_concat_max_len = 2048; 1043 SET v_done = FALSE; 1044 OPEN c_sysviews_w_delta; 1045 c_sysviews_w_delta_loop: LOOP 1046 FETCH c_sysviews_w_delta INTO v_table_name; 1047 IF v_done THEN 1048 LEAVE c_sysviews_w_delta_loop; 1049 END IF; 1050 1051 SET @sys.diagnostics.table_name = v_table_name; 1052 EXECUTE stmt_gen_query_delta USING @sys.diagnostics.table_name; 1053 -- If necessary add WHERE, ORDER BY, and LIMIT 1054 SELECT CONCAT(@sys.diagnostics.sql_select, 1055 IF(where_delta IS NOT NULL, CONCAT('\n WHERE ', where_delta), ''), 1056 IF(order_by_delta IS NOT NULL, CONCAT('\n ORDER BY ', order_by_delta), ''), 1057 IF(limit_rows IS NOT NULL, CONCAT('\n LIMIT ', limit_rows), '') 1058 ) 1059 INTO @sys.diagnostics.sql_select 1060 FROM tmp_sys_views_delta 1061 WHERE TABLE_NAME = v_table_name; 1062 1063 SELECT CONCAT('Delta ', v_table_name) AS 'The following output is:'; 1064 CALL sys.execute_prepared_stmt(@sys.diagnostics.sql_select); 1065 1066 CALL sys.execute_prepared_stmt(CONCAT('DROP TEMPORARY TABLE `tmp_', v_table_name, '_end`')); 1067 CALL sys.execute_prepared_stmt(CONCAT('DROP TEMPORARY TABLE `tmp_', v_table_name, '_start`')); 1068 END LOOP; 1069 CLOSE c_sysviews_w_delta; 1070 SET @@session.group_concat_max_len = v_old_group_concat_max_len; 1071 1072 DEALLOCATE PREPARE stmt_gen_query_delta; 1073 DROP TEMPORARY TABLE tmp_sys_views_delta; 1074 END IF; 1075 1076 IF (v_has_metrics) THEN 1077 SELECT 'SELECT * FROM sys.metrics' AS 'The following output is:'; 1078 ELSE 1079 SELECT 'sys.metrics equivalent' AS 'The following output is:'; 1080 END IF; 1081 CALL sys.execute_prepared_stmt( 1082 CONCAT(v_sql_status_summary_select, v_sql_status_summary_delta, ', Type, s1.Enabled', v_sql_status_summary_from, 1083 ' 1084 ORDER BY Type, Variable_name' 1085 ) 1086 ); 1087 1088 -- Remove all the metrics temporary tables again 1089 SET v_count = 0; 1090 WHILE (v_count < v_output_count) DO 1091 SET v_count = v_count + 1; 1092 SET v_table_name = CONCAT('tmp_metrics_', v_count); 1093 CALL sys.execute_prepared_stmt(CONCAT('DROP TEMPORARY TABLE IF EXISTS ', v_table_name)); 1094 END WHILE; 1095 1096 IF (in_auto_config <> 'current') THEN 1097 CALL sys.ps_setup_reload_saved(); 1098 SET sql_log_bin = @log_bin; 1099 END IF; 1100 1101 -- Reset the @sys.diagnostics.% user variables internal to this procedure 1102 SET @sys.diagnostics.output_time = NULL, 1103 @sys.diagnostics.sql = NULL, 1104 @sys.diagnostics.sql_gen_query_delta = NULL, 1105 @sys.diagnostics.sql_gen_query_template = NULL, 1106 @sys.diagnostics.sql_select = NULL, 1107 @sys.diagnostics.table_name = NULL; 1108 1109 -- Restore INSTRUMENTED for this thread 1110 IF (v_this_thread_enabled = 'YES') THEN 1111 CALL sys.ps_setup_enable_thread(CONNECTION_ID()); 1112 END IF; 1113 1114 IF (@log_bin = 1) THEN 1115 SET sql_log_bin = @log_bin; 1116 END IF; 1117END$$ 1118 1119DELIMITER ;