diff options
Diffstat (limited to 'scripts/sys_schema/functions/ps_thread_stack.sql')
| -rw-r--r-- | scripts/sys_schema/functions/ps_thread_stack.sql | 171 |
1 files changed, 171 insertions, 0 deletions
diff --git a/scripts/sys_schema/functions/ps_thread_stack.sql b/scripts/sys_schema/functions/ps_thread_stack.sql new file mode 100644 index 00000000..12b09bd6 --- /dev/null +++ b/scripts/sys_schema/functions/ps_thread_stack.sql @@ -0,0 +1,171 @@ +-- Copyright (c) 2014, 2015, Oracle and/or its affiliates. All rights reserved. +-- +-- This program is free software; you can redistribute it and/or modify +-- it under the terms of the GNU General Public License as published by +-- the Free Software Foundation; version 2 of the License. +-- +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +-- GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License +-- along with this program; if not, write to the Free Software +-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA + +DROP FUNCTION IF EXISTS ps_thread_stack; + +DELIMITER $$ + +CREATE DEFINER='mariadb.sys'@'localhost' FUNCTION ps_thread_stack ( + thd_id BIGINT UNSIGNED, + debug BOOLEAN + ) +RETURNS LONGTEXT CHARSET latin1 + COMMENT ' + Description + ----------- + + Outputs a JSON formatted stack of all statements, stages and events + within Performance Schema for the specified thread. + + Parameters + ----------- + + thd_id (BIGINT UNSIGNED): + The id of the thread to trace. This should match the thread_id + column from the performance_schema.threads table. + in_verbose (BOOLEAN): + Include file:lineno information in the events. + + Example + ----------- + + (line separation added for output) + + mysql> SELECT sys.ps_thread_stack(37, FALSE) AS thread_stack\\G + *************************** 1. row *************************** + thread_stack: {"rankdir": "LR","nodesep": "0.10","stack_created": "2014-02-19 13:39:03", + "mysql_version": "5.7.3-m13","mysql_user": "root@localhost","events": + [{"nesting_event_id": "0", "event_id": "10", "timer_wait": 256.35, "event_info": + "sql/select", "wait_info": "select @@version_comment limit 1\\nerrors: 0\\nwarnings: 0\\nlock time: + ... + ' +SQL SECURITY INVOKER +NOT DETERMINISTIC +READS SQL DATA +BEGIN + + DECLARE json_objects LONGTEXT; + + /*!50602 + -- Do not track the current thread, it will kill the stack + UPDATE performance_schema.threads + SET instrumented = 'NO' + WHERE processlist_id = CONNECTION_ID(); + */ + + SET SESSION group_concat_max_len=@@global.max_allowed_packet; + + -- Select the entire stack of events + SELECT GROUP_CONCAT(CONCAT( '{' + , CONCAT_WS( ', ' + , CONCAT('"nesting_event_id": "', IF(nesting_event_id IS NULL, '0', nesting_event_id), '"') + , CONCAT('"event_id": "', event_id, '"') + -- Convert from picoseconds to microseconds + , CONCAT( '"timer_wait": ', ROUND(timer_wait/1000000, 2)) + , CONCAT( '"event_info": "' + , CASE + WHEN event_name NOT LIKE 'wait/io%' THEN REPLACE(SUBSTRING_INDEX(event_name, '/', -2), '\\', '\\\\') + WHEN event_name NOT LIKE 'wait/io/file%' OR event_name NOT LIKE 'wait/io/socket%' THEN REPLACE(SUBSTRING_INDEX(event_name, '/', -4), '\\', '\\\\') + ELSE event_name + END + , '"' + ) + -- Always dump the extra wait information gathered for statements + , CONCAT( '"wait_info": "', IFNULL(wait_info, ''), '"') + -- If debug is enabled, add the file:lineno information for waits + , CONCAT( '"source": "', IF(true AND event_name LIKE 'wait%', IFNULL(wait_info, ''), ''), '"') + -- Depending on the type of event, name it appropriately + , CASE + WHEN event_name LIKE 'wait/io/file%' THEN '"event_type": "io/file"' + WHEN event_name LIKE 'wait/io/table%' THEN '"event_type": "io/table"' + WHEN event_name LIKE 'wait/io/socket%' THEN '"event_type": "io/socket"' + WHEN event_name LIKE 'wait/synch/mutex%' THEN '"event_type": "synch/mutex"' + WHEN event_name LIKE 'wait/synch/cond%' THEN '"event_type": "synch/cond"' + WHEN event_name LIKE 'wait/synch/rwlock%' THEN '"event_type": "synch/rwlock"' + WHEN event_name LIKE 'wait/lock%' THEN '"event_type": "lock"' + WHEN event_name LIKE 'statement/%' THEN '"event_type": "stmt"' + WHEN event_name LIKE 'stage/%' THEN '"event_type": "stage"' + WHEN event_name LIKE '%idle%' THEN '"event_type": "idle"' + ELSE '' + END + ) + , '}' + ) + ORDER BY event_id ASC SEPARATOR ',') event + INTO json_objects + FROM ( + /*!50600 + -- Select all statements, with the extra tracing information available + (SELECT thread_id, event_id, event_name, timer_wait, timer_start, nesting_event_id, + CONCAT(sql_text, '\\n', + 'errors: ', errors, '\\n', + 'warnings: ', warnings, '\\n', + 'lock time: ', ROUND(lock_time/1000000, 2),'us\\n', + 'rows affected: ', rows_affected, '\\n', + 'rows sent: ', rows_sent, '\\n', + 'rows examined: ', rows_examined, '\\n', + 'tmp tables: ', created_tmp_tables, '\\n', + 'tmp disk tables: ', created_tmp_disk_tables, '\\n', + 'select scan: ', select_scan, '\\n', + 'select full join: ', select_full_join, '\\n', + 'select full range join: ', select_full_range_join, '\\n', + 'select range: ', select_range, '\\n', + 'select range check: ', select_range_check, '\\n', + 'sort merge passes: ', sort_merge_passes, '\\n', + 'sort rows: ', sort_rows, '\\n', + 'sort range: ', sort_range, '\\n', + 'sort scan: ', sort_scan, '\\n', + 'no index used: ', IF(no_index_used, 'TRUE', 'FALSE'), '\\n', + 'no good index used: ', IF(no_good_index_used, 'TRUE', 'FALSE'), '\\n' + ) AS wait_info + FROM performance_schema.events_statements_history_long WHERE thread_id = thd_id) + UNION + -- Select all stages + (SELECT thread_id, event_id, event_name, timer_wait, timer_start, nesting_event_id, null AS wait_info + FROM performance_schema.events_stages_history_long WHERE thread_id = thd_id) + UNION */ + -- Select all events, adding information appropriate to the event + (SELECT thread_id, event_id, + CONCAT(event_name , + IF(event_name NOT LIKE 'wait/synch/mutex%', IFNULL(CONCAT(' - ', operation), ''), ''), + IF(number_of_bytes IS NOT NULL, CONCAT(' ', number_of_bytes, ' bytes'), ''), + IF(event_name LIKE 'wait/io/file%', '\\n', ''), + IF(object_schema IS NOT NULL, CONCAT('\\nObject: ', object_schema, '.'), ''), + IF(object_name IS NOT NULL, + IF (event_name LIKE 'wait/io/socket%', + -- Print the socket if used, else the IP:port as reported + CONCAT(IF (object_name LIKE ':0%', @@socket, object_name)), + object_name), + ''), + /*!50600 IF(index_name IS NOT NULL, CONCAT(' Index: ', index_name), ''),*/'\\n' + ) AS event_name, + timer_wait, timer_start, nesting_event_id, source AS wait_info + FROM performance_schema.events_waits_history_long WHERE thread_id = thd_id)) events + ORDER BY event_id; + + RETURN CONCAT('{', + CONCAT_WS(',', + '"rankdir": "LR"', + '"nodesep": "0.10"', + CONCAT('"stack_created": "', NOW(), '"'), + CONCAT('"mysql_version": "', VERSION(), '"'), + CONCAT('"mysql_user": "', CURRENT_USER(), '"'), + CONCAT('"events": [', IFNULL(json_objects,''), ']') + ), + '}'); + +END$$ + +DELIMITER ; |