1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
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 ;
|
