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 statement_performance_analyzer; 17 18DELIMITER $$ 19 20CREATE DEFINER='root'@'localhost' PROCEDURE statement_performance_analyzer ( 21 IN in_action ENUM('snapshot', 'overall', 'delta', 'create_table', 'create_tmp', 'save', 'cleanup'), 22 IN in_table VARCHAR(129), 23 IN in_views SET ('with_runtimes_in_95th_percentile', 'analysis', 'with_errors_or_warnings', 'with_full_table_scans', 'with_sorting', 'with_temp_tables', 'custom') 24 ) 25 COMMENT ' 26 Description 27 ----------- 28 29 Create a report of the statements running on the server. 30 31 The views are calculated based on the overall and/or delta activity. 32 33 Requires the SUPER privilege for "SET sql_log_bin = 0;". 34 35 Parameters 36 ----------- 37 38 in_action (ENUM(''snapshot'', ''overall'', ''delta'', ''create_tmp'', ''create_table'', ''save'', ''cleanup'')): 39 The action to take. Supported actions are: 40 * snapshot Store a snapshot. The default is to make a snapshot of the current content of 41 performance_schema.events_statements_summary_by_digest, but by setting in_table 42 this can be overwritten to copy the content of the specified table. 43 The snapshot is stored in the sys.tmp_digests temporary table. 44 * overall Generate analyzis based on the content specified by in_table. For the overall analyzis, 45 in_table can be NOW() to use a fresh snapshot. This will overwrite an existing snapshot. 46 Use NULL for in_table to use the existing snapshot. If in_table IS NULL and no snapshot 47 exists, a new will be created. 48 See also in_views and @sys.statement_performance_analyzer.limit. 49 * delta Generate a delta analysis. The delta will be calculated between the reference table in 50 in_table and the snapshot. An existing snapshot must exist. 51 The action uses the sys.tmp_digests_delta temporary table. 52 See also in_views and @sys.statement_performance_analyzer.limit. 53 * create_table Create a regular table suitable for storing the snapshot for later use, e.g. for 54 calculating deltas. 55 * create_tmp Create a temporary table suitable for storing the snapshot for later use, e.g. for 56 calculating deltas. 57 * save Save the snapshot in the table specified by in_table. The table must exists and have 58 the correct structure. 59 If no snapshot exists, a new is created. 60 * cleanup Remove the temporary tables used for the snapshot and delta. 61 62 in_table (VARCHAR(129)): 63 The table argument used for some actions. Use the format ''db1.t1'' or ''t1'' without using any backticks (`) 64 for quoting. Periods (.) are not supported in the database and table names. 65 66 The meaning of the table for each action supporting the argument is: 67 68 * snapshot The snapshot is created based on the specified table. Set to NULL or NOW() to use 69 the current content of performance_schema.events_statements_summary_by_digest. 70 * overall The table with the content to create the overall analyzis for. The following values 71 can be used: 72 - A table name - use the content of that table. 73 - NOW() - create a fresh snapshot and overwrite the existing snapshot. 74 - NULL - use the last stored snapshot. 75 * delta The table name is mandatory and specified the reference view to compare the currently 76 stored snapshot against. If no snapshot exists, a new will be created. 77 * create_table The name of the regular table to create. 78 * create_tmp The name of the temporary table to create. 79 * save The name of the table to save the currently stored snapshot into. 80 81 in_views (SET (''with_runtimes_in_95th_percentile'', ''analysis'', ''with_errors_or_warnings'', 82 ''with_full_table_scans'', ''with_sorting'', ''with_temp_tables'', ''custom'')) 83 Which views to include: 84 85 * with_runtimes_in_95th_percentile Based on the sys.statements_with_runtimes_in_95th_percentile view 86 * analysis Based on the sys.statement_analysis view 87 * with_errors_or_warnings Based on the sys.statements_with_errors_or_warnings view 88 * with_full_table_scans Based on the sys.statements_with_full_table_scans view 89 * with_sorting Based on the sys.statements_with_sorting view 90 * with_temp_tables Based on the sys.statements_with_temp_tables view 91 * custom Use a custom view. This view must be specified in @sys.statement_performance_analyzer.view to an existing view or a query 92 93 Default is to include all except ''custom''. 94 95 96 Configuration Options 97 ---------------------- 98 99 sys.statement_performance_analyzer.limit 100 The maximum number of rows to include for the views that does not have a built-in limit (e.g. the 95th percentile view). 101 If not set the limit is 100. 102 103 sys.statement_performance_analyzer.view 104 Used together with the ''custom'' view. If the value contains a space, it is considered a query, otherwise it must be 105 an existing view querying the performance_schema.events_statements_summary_by_digest table. There cannot be any limit 106 clause including in the query or view definition if @sys.statement_performance_analyzer.limit > 0. 107 If specifying a view, use the same format as for in_table. 108 109 sys.debug 110 Whether to provide debugging output. 111 Default is ''OFF''. Set to ''ON'' to include. 112 113 114 Example 115 -------- 116 117 To create a report with the queries in the 95th percentile since last truncate of performance_schema.events_statements_summary_by_digest 118 and the delta for a 1 minute period: 119 120 1. Create a temporary table to store the initial snapshot. 121 2. Create the initial snapshot. 122 3. Save the initial snapshot in the temporary table. 123 4. Wait one minute. 124 5. Create a new snapshot. 125 6. Perform analyzis based on the new snapshot. 126 7. Perform analyzis based on the delta between the initial and new snapshots. 127 128 mysql> CALL sys.statement_performance_analyzer(''create_tmp'', ''mydb.tmp_digests_ini'', NULL); 129 Query OK, 0 rows affected (0.08 sec) 130 131 mysql> CALL sys.statement_performance_analyzer(''snapshot'', NULL, NULL); 132 Query OK, 0 rows affected (0.02 sec) 133 134 mysql> CALL sys.statement_performance_analyzer(''save'', ''mydb.tmp_digests_ini'', NULL); 135 Query OK, 0 rows affected (0.00 sec) 136 137 mysql> DO SLEEP(60); 138 Query OK, 0 rows affected (1 min 0.00 sec) 139 140 mysql> CALL sys.statement_performance_analyzer(''snapshot'', NULL, NULL); 141 Query OK, 0 rows affected (0.02 sec) 142 143 mysql> CALL sys.statement_performance_analyzer(''overall'', NULL, ''with_runtimes_in_95th_percentile''); 144 +-----------------------------------------+ 145 | Next Output | 146 +-----------------------------------------+ 147 | Queries with Runtime in 95th Percentile | 148 +-----------------------------------------+ 149 1 row in set (0.05 sec) 150 151 ... 152 153 mysql> CALL sys.statement_performance_analyzer(''delta'', ''mydb.tmp_digests_ini'', ''with_runtimes_in_95th_percentile''); 154 +-----------------------------------------+ 155 | Next Output | 156 +-----------------------------------------+ 157 | Queries with Runtime in 95th Percentile | 158 +-----------------------------------------+ 159 1 row in set (0.03 sec) 160 161 ... 162 163 164 To create an overall report of the 95th percentile queries and the top 10 queries with full table scans: 165 166 mysql> CALL sys.statement_performance_analyzer(''snapshot'', NULL, NULL); 167 Query OK, 0 rows affected (0.01 sec) 168 169 mysql> SET @sys.statement_performance_analyzer.limit = 10; 170 Query OK, 0 rows affected (0.00 sec) 171 172 mysql> CALL sys.statement_performance_analyzer(''overall'', NULL, ''with_runtimes_in_95th_percentile,with_full_table_scans''); 173 +-----------------------------------------+ 174 | Next Output | 175 +-----------------------------------------+ 176 | Queries with Runtime in 95th Percentile | 177 +-----------------------------------------+ 178 1 row in set (0.01 sec) 179 180 ... 181 182 +-------------------------------------+ 183 | Next Output | 184 +-------------------------------------+ 185 | Top 10 Queries with Full Table Scan | 186 +-------------------------------------+ 187 1 row in set (0.09 sec) 188 189 ... 190 191 192 Use a custom view showing the top 10 query sorted by total execution time refreshing the view every minute using 193 the watch command in Linux. 194 195 mysql> CREATE OR REPLACE VIEW mydb.my_statements AS 196 -> SELECT sys.format_statement(DIGEST_TEXT) AS query, 197 -> SCHEMA_NAME AS db, 198 -> COUNT_STAR AS exec_count, 199 -> sys.format_time(SUM_TIMER_WAIT) AS total_latency, 200 -> sys.format_time(AVG_TIMER_WAIT) AS avg_latency, 201 -> ROUND(IFNULL(SUM_ROWS_SENT / NULLIF(COUNT_STAR, 0), 0)) AS rows_sent_avg, 202 -> ROUND(IFNULL(SUM_ROWS_EXAMINED / NULLIF(COUNT_STAR, 0), 0)) AS rows_examined_avg, 203 -> ROUND(IFNULL(SUM_ROWS_AFFECTED / NULLIF(COUNT_STAR, 0), 0)) AS rows_affected_avg, 204 -> DIGEST AS digest 205 -> FROM performance_schema.events_statements_summary_by_digest 206 -> ORDER BY SUM_TIMER_WAIT DESC; 207 Query OK, 0 rows affected (0.01 sec) 208 209 mysql> CALL sys.statement_performance_analyzer(''create_table'', ''mydb.digests_prev'', NULL); 210 Query OK, 0 rows affected (0.10 sec) 211 212 shell$ watch -n 60 "mysql sys --table -e \" 213 > SET @sys.statement_performance_analyzer.view = ''mydb.my_statements''; 214 > SET @sys.statement_performance_analyzer.limit = 10; 215 > CALL statement_performance_analyzer(''snapshot'', NULL, NULL); 216 > CALL statement_performance_analyzer(''delta'', ''mydb.digests_prev'', ''custom''); 217 > CALL statement_performance_analyzer(''save'', ''mydb.digests_prev'', NULL); 218 > \"" 219 220 Every 60.0s: mysql sys --table -e " ... Mon Dec 22 10:58:51 2014 221 222 +----------------------------------+ 223 | Next Output | 224 +----------------------------------+ 225 | Top 10 Queries Using Custom View | 226 +----------------------------------+ 227 +-------------------+-------+------------+---------------+-------------+---------------+-------------------+-------------------+----------------------------------+ 228 | query | db | exec_count | total_latency | avg_latency | rows_sent_avg | rows_examined_avg | rows_affected_avg | digest | 229 +-------------------+-------+------------+---------------+-------------+---------------+-------------------+-------------------+----------------------------------+ 230 ... 231 ' 232 SQL SECURITY INVOKER 233 NOT DETERMINISTIC 234 CONTAINS SQL 235BEGIN 236 DECLARE v_table_exists, v_tmp_digests_table_exists, v_custom_view_exists ENUM('', 'BASE TABLE', 'VIEW', 'TEMPORARY') DEFAULT ''; 237 DECLARE v_this_thread_enabled ENUM('YES', 'NO'); 238 DECLARE v_force_new_snapshot BOOLEAN DEFAULT FALSE; 239 DECLARE v_digests_table VARCHAR(133); 240 DECLARE v_quoted_table, v_quoted_custom_view VARCHAR(133) DEFAULT ''; 241 DECLARE v_table_db, v_table_name, v_custom_db, v_custom_name VARCHAR(64); 242 DECLARE v_digest_table_template, v_checksum_ref, v_checksum_table text; 243 DECLARE v_sql longtext; 244 -- Maximum supported length for MESSAGE_TEXT with the SIGNAL command is 128 chars. 245 DECLARE v_error_msg VARCHAR(128); 246 247 248 -- Don't instrument this thread 249 SELECT INSTRUMENTED INTO v_this_thread_enabled FROM performance_schema.threads WHERE PROCESSLIST_ID = CONNECTION_ID(); 250 IF (v_this_thread_enabled = 'YES') THEN 251 CALL sys.ps_setup_disable_thread(CONNECTION_ID()); 252 END IF; 253 254 -- Temporary table are used - disable sql_log_bin if necessary to prevent them replicating 255 SET @log_bin := @@sql_log_bin; 256 IF (@log_bin = 1) THEN 257 SET sql_log_bin = 0; 258 END IF; 259 260 261 -- Set configuration options 262 IF (@sys.statement_performance_analyzer.limit IS NULL) THEN 263 SET @sys.statement_performance_analyzer.limit = sys.sys_get_config('statement_performance_analyzer.limit', '100'); 264 END IF; 265 IF (@sys.debug IS NULL) THEN 266 SET @sys.debug = sys.sys_get_config('debug' , 'OFF'); 267 END IF; 268 269 270 -- If in_table is set, break in_table into a db and table component and check whether it exists 271 -- in_table = NOW() is considered like it's not set. 272 IF (in_table = 'NOW()') THEN 273 SET v_force_new_snapshot = TRUE, 274 in_table = NULL; 275 ELSEIF (in_table IS NOT NULL) THEN 276 IF (NOT INSTR(in_table, '.')) THEN 277 -- No . in the table name - use current database 278 -- DATABASE() will be the database of the procedure 279 SET v_table_db = DATABASE(), 280 v_table_name = in_table; 281 ELSE 282 SET v_table_db = SUBSTRING_INDEX(in_table, '.', 1); 283 SET v_table_name = SUBSTRING(in_table, CHAR_LENGTH(v_table_db)+2); 284 END IF; 285 286 SET v_quoted_table = CONCAT('`', v_table_db, '`.`', v_table_name, '`'); 287 288 IF (@sys.debug = 'ON') THEN 289 SELECT CONCAT('in_table is: db = ''', v_table_db, ''', table = ''', v_table_name, '''') AS 'Debug'; 290 END IF; 291 292 IF (v_table_db = DATABASE() AND (v_table_name = 'tmp_digests' OR v_table_name = 'tmp_digests_delta')) THEN 293 SET v_error_msg = CONCAT('Invalid value for in_table: ', v_quoted_table, ' is reserved table name.'); 294 SIGNAL SQLSTATE '45000' 295 SET MESSAGE_TEXT = v_error_msg; 296 END IF; 297 298 CALL sys.table_exists(v_table_db, v_table_name, v_table_exists); 299 IF (@sys.debug = 'ON') THEN 300 SELECT CONCAT('v_table_exists = ', v_table_exists) AS 'Debug'; 301 END IF; 302 303 IF (v_table_exists = 'BASE TABLE') THEN 304 -- Verify that the table has the correct table definition 305 -- This can only be done for base tables as temporary aren't in information_schema.COLUMNS. 306 -- This also minimises the risk of using a production table. 307 SET v_checksum_ref = ( 308 SELECT GROUP_CONCAT(CONCAT(COLUMN_NAME, COLUMN_TYPE) ORDER BY ORDINAL_POSITION) AS Checksum 309 FROM information_schema.COLUMNS 310 WHERE TABLE_SCHEMA = 'performance_schema' AND TABLE_NAME = 'events_statements_summary_by_digest' 311 ), 312 v_checksum_table = ( 313 SELECT GROUP_CONCAT(CONCAT(COLUMN_NAME, COLUMN_TYPE) ORDER BY ORDINAL_POSITION) AS Checksum 314 FROM information_schema.COLUMNS 315 WHERE TABLE_SCHEMA = v_table_db AND TABLE_NAME = v_table_name 316 ); 317 318 IF (v_checksum_ref <> v_checksum_table) THEN 319 -- The table does not have the correct definition, so abandon 320 SET v_error_msg = CONCAT('The table ', 321 IF(CHAR_LENGTH(v_quoted_table) > 93, CONCAT('...', SUBSTRING(v_quoted_table, -90)), v_quoted_table), 322 ' has the wrong definition.'); 323 SIGNAL SQLSTATE '45000' 324 SET MESSAGE_TEXT = v_error_msg; 325 END IF; 326 END IF; 327 END IF; 328 329 330 IF (in_views IS NULL OR in_views = '') THEN 331 -- Set to default 332 SET in_views = 'with_runtimes_in_95th_percentile,analysis,with_errors_or_warnings,with_full_table_scans,with_sorting,with_temp_tables'; 333 END IF; 334 335 336 -- Validate settings 337 CALL sys.table_exists(DATABASE(), 'tmp_digests', v_tmp_digests_table_exists); 338 IF (@sys.debug = 'ON') THEN 339 SELECT CONCAT('v_tmp_digests_table_exists = ', v_tmp_digests_table_exists) AS 'Debug'; 340 END IF; 341 342 CASE 343 WHEN in_action IN ('snapshot', 'overall') THEN 344 -- in_table must be NULL, NOW(), or an existing table 345 IF (in_table IS NOT NULL) THEN 346 IF (NOT v_table_exists IN ('TEMPORARY', 'BASE TABLE')) THEN 347 SET v_error_msg = CONCAT('The ', in_action, ' action requires in_table to be NULL, NOW() or specify an existing table.', 348 ' The table ', 349 IF(CHAR_LENGTH(v_quoted_table) > 16, CONCAT('...', SUBSTRING(v_quoted_table, -13)), v_quoted_table), 350 ' does not exist.'); 351 SIGNAL SQLSTATE '45000' 352 SET MESSAGE_TEXT = v_error_msg; 353 END IF; 354 END IF; 355 356 WHEN in_action IN ('delta', 'save') THEN 357 -- in_table must be an existing table 358 IF (v_table_exists NOT IN ('TEMPORARY', 'BASE TABLE')) THEN 359 SET v_error_msg = CONCAT('The ', in_action, ' action requires in_table to be an existing table.', 360 IF(in_table IS NOT NULL, CONCAT(' The table ', 361 IF(CHAR_LENGTH(v_quoted_table) > 39, CONCAT('...', SUBSTRING(v_quoted_table, -36)), v_quoted_table), 362 ' does not exist.'), '')); 363 SIGNAL SQLSTATE '45000' 364 SET MESSAGE_TEXT = v_error_msg; 365 END IF; 366 367 IF (in_action = 'delta' AND v_tmp_digests_table_exists <> 'TEMPORARY') THEN 368 SIGNAL SQLSTATE '45000' 369 SET MESSAGE_TEXT = 'An existing snapshot generated with the statement_performance_analyzer() must exist.'; 370 END IF; 371 WHEN in_action = 'create_tmp' THEN 372 -- in_table must not exists as a temporary table 373 IF (v_table_exists = 'TEMPORARY') THEN 374 SET v_error_msg = CONCAT('Cannot create the table ', 375 IF(CHAR_LENGTH(v_quoted_table) > 72, CONCAT('...', SUBSTRING(v_quoted_table, -69)), v_quoted_table), 376 ' as it already exists.'); 377 SIGNAL SQLSTATE '45000' 378 SET MESSAGE_TEXT = v_error_msg; 379 END IF; 380 381 WHEN in_action = 'create_table' THEN 382 -- in_table must not exists at all 383 IF (v_table_exists <> '') THEN 384 SET v_error_msg = CONCAT('Cannot create the table ', 385 IF(CHAR_LENGTH(v_quoted_table) > 52, CONCAT('...', SUBSTRING(v_quoted_table, -49)), v_quoted_table), 386 ' as it already exists', 387 IF(v_table_exists = 'TEMPORARY', ' as a temporary table.', '.')); 388 SIGNAL SQLSTATE '45000' 389 SET MESSAGE_TEXT = v_error_msg; 390 END IF; 391 392 WHEN in_action = 'cleanup' THEN 393 -- doesn't use any of the arguments 394 DO (SELECT 1); 395 ELSE 396 SIGNAL SQLSTATE '45000' 397 SET MESSAGE_TEXT = 'Unknown action. Supported actions are: cleanup, create_table, create_tmp, delta, overall, save, snapshot'; 398 END CASE; 399 400 SET v_digest_table_template = 'CREATE %{TEMPORARY}TABLE %{TABLE_NAME} ( 401 `SCHEMA_NAME` varchar(64) DEFAULT NULL, 402 `DIGEST` varchar(32) DEFAULT NULL, 403 `DIGEST_TEXT` longtext, 404 `COUNT_STAR` bigint(20) unsigned NOT NULL, 405 `SUM_TIMER_WAIT` bigint(20) unsigned NOT NULL, 406 `MIN_TIMER_WAIT` bigint(20) unsigned NOT NULL, 407 `AVG_TIMER_WAIT` bigint(20) unsigned NOT NULL, 408 `MAX_TIMER_WAIT` bigint(20) unsigned NOT NULL, 409 `SUM_LOCK_TIME` bigint(20) unsigned NOT NULL, 410 `SUM_ERRORS` bigint(20) unsigned NOT NULL, 411 `SUM_WARNINGS` bigint(20) unsigned NOT NULL, 412 `SUM_ROWS_AFFECTED` bigint(20) unsigned NOT NULL, 413 `SUM_ROWS_SENT` bigint(20) unsigned NOT NULL, 414 `SUM_ROWS_EXAMINED` bigint(20) unsigned NOT NULL, 415 `SUM_CREATED_TMP_DISK_TABLES` bigint(20) unsigned NOT NULL, 416 `SUM_CREATED_TMP_TABLES` bigint(20) unsigned NOT NULL, 417 `SUM_SELECT_FULL_JOIN` bigint(20) unsigned NOT NULL, 418 `SUM_SELECT_FULL_RANGE_JOIN` bigint(20) unsigned NOT NULL, 419 `SUM_SELECT_RANGE` bigint(20) unsigned NOT NULL, 420 `SUM_SELECT_RANGE_CHECK` bigint(20) unsigned NOT NULL, 421 `SUM_SELECT_SCAN` bigint(20) unsigned NOT NULL, 422 `SUM_SORT_MERGE_PASSES` bigint(20) unsigned NOT NULL, 423 `SUM_SORT_RANGE` bigint(20) unsigned NOT NULL, 424 `SUM_SORT_ROWS` bigint(20) unsigned NOT NULL, 425 `SUM_SORT_SCAN` bigint(20) unsigned NOT NULL, 426 `SUM_NO_INDEX_USED` bigint(20) unsigned NOT NULL, 427 `SUM_NO_GOOD_INDEX_USED` bigint(20) unsigned NOT NULL, 428 `FIRST_SEEN` timestamp NULL DEFAULT NULL, 429 `LAST_SEEN` timestamp NULL DEFAULT NULL, 430 INDEX (SCHEMA_NAME, DIGEST) 431) DEFAULT CHARSET=utf8'; 432 433 -- Do the action 434 -- The actions snapshot, ... requires a fresh snapshot - create it now 435 IF (v_force_new_snapshot 436 OR in_action = 'snapshot' 437 OR (in_action = 'overall' AND in_table IS NULL) 438 OR (in_action = 'save' AND v_tmp_digests_table_exists <> 'TEMPORARY') 439 ) THEN 440 IF (v_tmp_digests_table_exists = 'TEMPORARY') THEN 441 IF (@sys.debug = 'ON') THEN 442 SELECT 'DROP TEMPORARY TABLE IF EXISTS tmp_digests' AS 'Debug'; 443 END IF; 444 DROP TEMPORARY TABLE IF EXISTS tmp_digests; 445 END IF; 446 CALL sys.execute_prepared_stmt(REPLACE(REPLACE(v_digest_table_template, '%{TEMPORARY}', 'TEMPORARY '), '%{TABLE_NAME}', 'tmp_digests')); 447 448 SET v_sql = CONCAT('INSERT INTO tmp_digests SELECT * FROM ', 449 IF(in_table IS NULL OR in_action = 'save', 'performance_schema.events_statements_summary_by_digest', v_quoted_table)); 450 CALL sys.execute_prepared_stmt(v_sql); 451 END IF; 452 453 -- Go through the remaining actions 454 IF (in_action IN ('create_table', 'create_tmp')) THEN 455 IF (in_action = 'create_table') THEN 456 CALL sys.execute_prepared_stmt(REPLACE(REPLACE(v_digest_table_template, '%{TEMPORARY}', ''), '%{TABLE_NAME}', v_quoted_table)); 457 ELSE 458 CALL sys.execute_prepared_stmt(REPLACE(REPLACE(v_digest_table_template, '%{TEMPORARY}', 'TEMPORARY '), '%{TABLE_NAME}', v_quoted_table)); 459 END IF; 460 ELSEIF (in_action = 'save') THEN 461 CALL sys.execute_prepared_stmt(CONCAT('DELETE FROM ', v_quoted_table)); 462 CALL sys.execute_prepared_stmt(CONCAT('INSERT INTO ', v_quoted_table, ' SELECT * FROM tmp_digests')); 463 ELSEIF (in_action = 'cleanup') THEN 464 DROP TEMPORARY TABLE IF EXISTS sys.tmp_digests; 465 DROP TEMPORARY TABLE IF EXISTS sys.tmp_digests_delta; 466 ELSEIF (in_action IN ('overall', 'delta')) THEN 467 -- These are almost the same - for delta calculate the delta in tmp_digests_delta and use that instead of tmp_digests. 468 -- And overall allows overriding the table to use. 469 IF (in_action = 'overall') THEN 470 IF (in_table IS NULL) THEN 471 SET v_digests_table = 'tmp_digests'; 472 ELSE 473 SET v_digests_table = v_quoted_table; 474 END IF; 475 ELSE 476 SET v_digests_table = 'tmp_digests_delta'; 477 DROP TEMPORARY TABLE IF EXISTS tmp_digests_delta; 478 CREATE TEMPORARY TABLE tmp_digests_delta LIKE tmp_digests; 479 SET v_sql = CONCAT('INSERT INTO tmp_digests_delta 480SELECT `d_end`.`SCHEMA_NAME`, 481 `d_end`.`DIGEST`, 482 `d_end`.`DIGEST_TEXT`, 483 `d_end`.`COUNT_STAR`-IFNULL(`d_start`.`COUNT_STAR`, 0) AS ''COUNT_STAR'', 484 `d_end`.`SUM_TIMER_WAIT`-IFNULL(`d_start`.`SUM_TIMER_WAIT`, 0) AS ''SUM_TIMER_WAIT'', 485 `d_end`.`MIN_TIMER_WAIT` AS ''MIN_TIMER_WAIT'', 486 IFNULL((`d_end`.`SUM_TIMER_WAIT`-IFNULL(`d_start`.`SUM_TIMER_WAIT`, 0))/NULLIF(`d_end`.`COUNT_STAR`-IFNULL(`d_start`.`COUNT_STAR`, 0), 0), 0) AS ''AVG_TIMER_WAIT'', 487 `d_end`.`MAX_TIMER_WAIT` AS ''MAX_TIMER_WAIT'', 488 `d_end`.`SUM_LOCK_TIME`-IFNULL(`d_start`.`SUM_LOCK_TIME`, 0) AS ''SUM_LOCK_TIME'', 489 `d_end`.`SUM_ERRORS`-IFNULL(`d_start`.`SUM_ERRORS`, 0) AS ''SUM_ERRORS'', 490 `d_end`.`SUM_WARNINGS`-IFNULL(`d_start`.`SUM_WARNINGS`, 0) AS ''SUM_WARNINGS'', 491 `d_end`.`SUM_ROWS_AFFECTED`-IFNULL(`d_start`.`SUM_ROWS_AFFECTED`, 0) AS ''SUM_ROWS_AFFECTED'', 492 `d_end`.`SUM_ROWS_SENT`-IFNULL(`d_start`.`SUM_ROWS_SENT`, 0) AS ''SUM_ROWS_SENT'', 493 `d_end`.`SUM_ROWS_EXAMINED`-IFNULL(`d_start`.`SUM_ROWS_EXAMINED`, 0) AS ''SUM_ROWS_EXAMINED'', 494 `d_end`.`SUM_CREATED_TMP_DISK_TABLES`-IFNULL(`d_start`.`SUM_CREATED_TMP_DISK_TABLES`, 0) AS ''SUM_CREATED_TMP_DISK_TABLES'', 495 `d_end`.`SUM_CREATED_TMP_TABLES`-IFNULL(`d_start`.`SUM_CREATED_TMP_TABLES`, 0) AS ''SUM_CREATED_TMP_TABLES'', 496 `d_end`.`SUM_SELECT_FULL_JOIN`-IFNULL(`d_start`.`SUM_SELECT_FULL_JOIN`, 0) AS ''SUM_SELECT_FULL_JOIN'', 497 `d_end`.`SUM_SELECT_FULL_RANGE_JOIN`-IFNULL(`d_start`.`SUM_SELECT_FULL_RANGE_JOIN`, 0) AS ''SUM_SELECT_FULL_RANGE_JOIN'', 498 `d_end`.`SUM_SELECT_RANGE`-IFNULL(`d_start`.`SUM_SELECT_RANGE`, 0) AS ''SUM_SELECT_RANGE'', 499 `d_end`.`SUM_SELECT_RANGE_CHECK`-IFNULL(`d_start`.`SUM_SELECT_RANGE_CHECK`, 0) AS ''SUM_SELECT_RANGE_CHECK'', 500 `d_end`.`SUM_SELECT_SCAN`-IFNULL(`d_start`.`SUM_SELECT_SCAN`, 0) AS ''SUM_SELECT_SCAN'', 501 `d_end`.`SUM_SORT_MERGE_PASSES`-IFNULL(`d_start`.`SUM_SORT_MERGE_PASSES`, 0) AS ''SUM_SORT_MERGE_PASSES'', 502 `d_end`.`SUM_SORT_RANGE`-IFNULL(`d_start`.`SUM_SORT_RANGE`, 0) AS ''SUM_SORT_RANGE'', 503 `d_end`.`SUM_SORT_ROWS`-IFNULL(`d_start`.`SUM_SORT_ROWS`, 0) AS ''SUM_SORT_ROWS'', 504 `d_end`.`SUM_SORT_SCAN`-IFNULL(`d_start`.`SUM_SORT_SCAN`, 0) AS ''SUM_SORT_SCAN'', 505 `d_end`.`SUM_NO_INDEX_USED`-IFNULL(`d_start`.`SUM_NO_INDEX_USED`, 0) AS ''SUM_NO_INDEX_USED'', 506 `d_end`.`SUM_NO_GOOD_INDEX_USED`-IFNULL(`d_start`.`SUM_NO_GOOD_INDEX_USED`, 0) AS ''SUM_NO_GOOD_INDEX_USED'', 507 `d_end`.`FIRST_SEEN`, 508 `d_end`.`LAST_SEEN` 509 FROM tmp_digests d_end 510 LEFT OUTER JOIN ', v_quoted_table, ' d_start ON `d_start`.`DIGEST` = `d_end`.`DIGEST` 511 AND (`d_start`.`SCHEMA_NAME` = `d_end`.`SCHEMA_NAME` 512 OR (`d_start`.`SCHEMA_NAME` IS NULL AND `d_end`.`SCHEMA_NAME` IS NULL) 513 ) 514 WHERE `d_end`.`COUNT_STAR`-IFNULL(`d_start`.`COUNT_STAR`, 0) > 0'); 515 CALL sys.execute_prepared_stmt(v_sql); 516 END IF; 517 518 IF (FIND_IN_SET('with_runtimes_in_95th_percentile', in_views)) THEN 519 SELECT 'Queries with Runtime in 95th Percentile' AS 'Next Output'; 520 521 DROP TEMPORARY TABLE IF EXISTS tmp_digest_avg_latency_distribution1; 522 DROP TEMPORARY TABLE IF EXISTS tmp_digest_avg_latency_distribution2; 523 DROP TEMPORARY TABLE IF EXISTS tmp_digest_95th_percentile_by_avg_us; 524 525 CREATE TEMPORARY TABLE tmp_digest_avg_latency_distribution1 ( 526 cnt bigint unsigned NOT NULL, 527 avg_us decimal(21,0) NOT NULL, 528 PRIMARY KEY (avg_us) 529 ) ENGINE=InnoDB; 530 531 SET v_sql = CONCAT('INSERT INTO tmp_digest_avg_latency_distribution1 532SELECT COUNT(*) cnt, 533 ROUND(avg_timer_wait/1000000) AS avg_us 534 FROM ', v_digests_table, ' 535 GROUP BY avg_us'); 536 CALL sys.execute_prepared_stmt(v_sql); 537 538 CREATE TEMPORARY TABLE tmp_digest_avg_latency_distribution2 LIKE tmp_digest_avg_latency_distribution1; 539 INSERT INTO tmp_digest_avg_latency_distribution2 SELECT * FROM tmp_digest_avg_latency_distribution1; 540 541 CREATE TEMPORARY TABLE tmp_digest_95th_percentile_by_avg_us ( 542 avg_us decimal(21,0) NOT NULL, 543 percentile decimal(46,4) NOT NULL, 544 PRIMARY KEY (avg_us) 545 ) ENGINE=InnoDB; 546 547 SET v_sql = CONCAT('INSERT INTO tmp_digest_95th_percentile_by_avg_us 548SELECT s2.avg_us avg_us, 549 IFNULL(SUM(s1.cnt)/NULLIF((SELECT COUNT(*) FROM ', v_digests_table, '), 0), 0) percentile 550 FROM tmp_digest_avg_latency_distribution1 AS s1 551 JOIN tmp_digest_avg_latency_distribution2 AS s2 ON s1.avg_us <= s2.avg_us 552 GROUP BY s2.avg_us 553HAVING percentile > 0.95 554 ORDER BY percentile 555 LIMIT 1'); 556 CALL sys.execute_prepared_stmt(v_sql); 557 558 SET v_sql = 559 REPLACE( 560 REPLACE( 561 (SELECT VIEW_DEFINITION 562 FROM information_schema.VIEWS 563 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'statements_with_runtimes_in_95th_percentile' 564 ), 565 '`performance_schema`.`events_statements_summary_by_digest`', 566 v_digests_table 567 ), 568 'sys.x$ps_digest_95th_percentile_by_avg_us', 569 '`sys`.`x$ps_digest_95th_percentile_by_avg_us`' 570 ); 571 CALL sys.execute_prepared_stmt(v_sql); 572 573 DROP TEMPORARY TABLE tmp_digest_avg_latency_distribution1; 574 DROP TEMPORARY TABLE tmp_digest_avg_latency_distribution2; 575 DROP TEMPORARY TABLE tmp_digest_95th_percentile_by_avg_us; 576 END IF; 577 578 IF (FIND_IN_SET('analysis', in_views)) THEN 579 SELECT CONCAT('Top ', @sys.statement_performance_analyzer.limit, ' Queries Ordered by Total Latency') AS 'Next Output'; 580 SET v_sql = 581 REPLACE( 582 (SELECT VIEW_DEFINITION 583 FROM information_schema.VIEWS 584 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'statement_analysis' 585 ), 586 '`performance_schema`.`events_statements_summary_by_digest`', 587 v_digests_table 588 ); 589 IF (@sys.statement_performance_analyzer.limit > 0) THEN 590 SET v_sql = CONCAT(v_sql, ' LIMIT ', @sys.statement_performance_analyzer.limit); 591 END IF; 592 CALL sys.execute_prepared_stmt(v_sql); 593 END IF; 594 595 IF (FIND_IN_SET('with_errors_or_warnings', in_views)) THEN 596 SELECT CONCAT('Top ', @sys.statement_performance_analyzer.limit, ' Queries with Errors') AS 'Next Output'; 597 SET v_sql = 598 REPLACE( 599 (SELECT VIEW_DEFINITION 600 FROM information_schema.VIEWS 601 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'statements_with_errors_or_warnings' 602 ), 603 '`performance_schema`.`events_statements_summary_by_digest`', 604 v_digests_table 605 ); 606 IF (@sys.statement_performance_analyzer.limit > 0) THEN 607 SET v_sql = CONCAT(v_sql, ' LIMIT ', @sys.statement_performance_analyzer.limit); 608 END IF; 609 CALL sys.execute_prepared_stmt(v_sql); 610 END IF; 611 612 IF (FIND_IN_SET('with_full_table_scans', in_views)) THEN 613 SELECT CONCAT('Top ', @sys.statement_performance_analyzer.limit, ' Queries with Full Table Scan') AS 'Next Output'; 614 SET v_sql = 615 REPLACE( 616 (SELECT VIEW_DEFINITION 617 FROM information_schema.VIEWS 618 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'statements_with_full_table_scans' 619 ), 620 '`performance_schema`.`events_statements_summary_by_digest`', 621 v_digests_table 622 ); 623 IF (@sys.statement_performance_analyzer.limit > 0) THEN 624 SET v_sql = CONCAT(v_sql, ' LIMIT ', @sys.statement_performance_analyzer.limit); 625 END IF; 626 CALL sys.execute_prepared_stmt(v_sql); 627 END IF; 628 629 IF (FIND_IN_SET('with_sorting', in_views)) THEN 630 SELECT CONCAT('Top ', @sys.statement_performance_analyzer.limit, ' Queries with Sorting') AS 'Next Output'; 631 SET v_sql = 632 REPLACE( 633 (SELECT VIEW_DEFINITION 634 FROM information_schema.VIEWS 635 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'statements_with_sorting' 636 ), 637 '`performance_schema`.`events_statements_summary_by_digest`', 638 v_digests_table 639 ); 640 IF (@sys.statement_performance_analyzer.limit > 0) THEN 641 SET v_sql = CONCAT(v_sql, ' LIMIT ', @sys.statement_performance_analyzer.limit); 642 END IF; 643 CALL sys.execute_prepared_stmt(v_sql); 644 END IF; 645 646 IF (FIND_IN_SET('with_temp_tables', in_views)) THEN 647 SELECT CONCAT('Top ', @sys.statement_performance_analyzer.limit, ' Queries with Internal Temporary Tables') AS 'Next Output'; 648 SET v_sql = 649 REPLACE( 650 (SELECT VIEW_DEFINITION 651 FROM information_schema.VIEWS 652 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'statements_with_temp_tables' 653 ), 654 '`performance_schema`.`events_statements_summary_by_digest`', 655 v_digests_table 656 ); 657 IF (@sys.statement_performance_analyzer.limit > 0) THEN 658 SET v_sql = CONCAT(v_sql, ' LIMIT ', @sys.statement_performance_analyzer.limit); 659 END IF; 660 CALL sys.execute_prepared_stmt(v_sql); 661 END IF; 662 663 IF (FIND_IN_SET('custom', in_views)) THEN 664 SELECT CONCAT('Top ', @sys.statement_performance_analyzer.limit, ' Queries Using Custom View') AS 'Next Output'; 665 666 IF (@sys.statement_performance_analyzer.view IS NULL) THEN 667 SET @sys.statement_performance_analyzer.view = sys.sys_get_config('statement_performance_analyzer.view', NULL); 668 END IF; 669 IF (@sys.statement_performance_analyzer.view IS NULL) THEN 670 SIGNAL SQLSTATE '45000' 671 SET MESSAGE_TEXT = 'The @sys.statement_performance_analyzer.view user variable must be set with the view or query to use.'; 672 END IF; 673 674 IF (NOT INSTR(@sys.statement_performance_analyzer.view, ' ')) THEN 675 -- No spaces, so can't be a query 676 IF (NOT INSTR(@sys.statement_performance_analyzer.view, '.')) THEN 677 -- No . in the table name - use current database 678 -- DATABASE() will be the database of the procedure 679 SET v_custom_db = DATABASE(), 680 v_custom_name = @sys.statement_performance_analyzer.view; 681 ELSE 682 SET v_custom_db = SUBSTRING_INDEX(@sys.statement_performance_analyzer.view, '.', 1); 683 SET v_custom_name = SUBSTRING(@sys.statement_performance_analyzer.view, CHAR_LENGTH(v_custom_db)+2); 684 END IF; 685 686 CALL sys.table_exists(v_custom_db, v_custom_name, v_custom_view_exists); 687 IF (v_custom_view_exists <> 'VIEW') THEN 688 SIGNAL SQLSTATE '45000' 689 SET MESSAGE_TEXT = 'The @sys.statement_performance_analyzer.view user variable is set but specified neither an existing view nor a query.'; 690 END IF; 691 692 SET v_sql = 693 REPLACE( 694 (SELECT VIEW_DEFINITION 695 FROM information_schema.VIEWS 696 WHERE TABLE_SCHEMA = v_custom_db AND TABLE_NAME = v_custom_name 697 ), 698 '`performance_schema`.`events_statements_summary_by_digest`', 699 v_digests_table 700 ); 701 ELSE 702 SET v_sql = REPLACE(@sys.statement_performance_analyzer.view, '`performance_schema`.`events_statements_summary_by_digest`', v_digests_table); 703 END IF; 704 705 IF (@sys.statement_performance_analyzer.limit > 0) THEN 706 SET v_sql = CONCAT(v_sql, ' LIMIT ', @sys.statement_performance_analyzer.limit); 707 END IF; 708 709 CALL sys.execute_prepared_stmt(v_sql); 710 END IF; 711 END IF; 712 713 -- Restore INSTRUMENTED for this thread 714 IF (v_this_thread_enabled = 'YES') THEN 715 CALL sys.ps_setup_enable_thread(CONNECTION_ID()); 716 END IF; 717 718 IF (@log_bin = 1) THEN 719 SET sql_log_bin = @log_bin; 720 END IF; 721END$$ 722 723DELIMITER ;