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