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
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
|
"================== con1 connected =================="
select "MARKER_BEGIN" as marker;
marker
MARKER_BEGIN
select "This is simple statement one" as payload;
payload
This is simple statement one
select "This is simple statement two" as payload;
payload
This is simple statement two
select "This is the first part of a multi query" as payload;
select "And this is the second part of a multi query" as payload;
select "With a third part to make things complete" as payload;
$$
payload
This is the first part of a multi query
payload
And this is the second part of a multi query
payload
With a third part to make things complete
select "MARKER_END" as marker;
marker
MARKER_END
select "Con1 is done with payload" as status;
status
Con1 is done with payload
"================== con1 done =================="
set @con1_tid = (select thread_id from performance_schema.threads
where processlist_user = 'user1');
select (@con1_tid is not null) as expected;
expected
1
set @marker_begin = (select event_id from performance_schema.events_statements_history_long
where sql_text like "%MARKER_BEGIN%" and thread_id = @con1_tid);
select (@marker_begin is not null) as expected;
expected
1
set @marker_end = (select end_event_id from performance_schema.events_statements_history_long
where sql_text like "%MARKER_END%" and thread_id = @con1_tid);
select (@marker_end is not null) as expected;
expected
1
show status like "performance_schema%";
Variable_name Value
Performance_schema_accounts_lost 0
Performance_schema_cond_classes_lost 0
Performance_schema_cond_instances_lost 0
Performance_schema_digest_lost 0
Performance_schema_file_classes_lost 0
Performance_schema_file_handles_lost 0
Performance_schema_file_instances_lost 0
Performance_schema_hosts_lost 0
Performance_schema_locker_lost 0
Performance_schema_mutex_classes_lost 0
Performance_schema_mutex_instances_lost 0
Performance_schema_rwlock_classes_lost 0
Performance_schema_rwlock_instances_lost 0
Performance_schema_session_connect_attrs_lost 0
Performance_schema_socket_classes_lost 0
Performance_schema_socket_instances_lost 0
Performance_schema_stage_classes_lost 0
Performance_schema_statement_classes_lost 0
Performance_schema_table_handles_lost 0
Performance_schema_table_instances_lost 0
Performance_schema_thread_classes_lost 0
Performance_schema_thread_instances_lost 0
Performance_schema_users_lost 0
select * from (
(
select (event_id - @marker_begin) as relative_event_id,
(end_event_id - @marker_begin) as relative_end_event_id,
event_name,
sql_text as comment,
nesting_event_type,
(nesting_event_id - @marker_begin) as relative_nesting_event_id
from performance_schema.events_statements_history_long
where (thread_id = @con1_tid)
and (@marker_begin <= event_id)
and (end_event_id <= @marker_end)
)
union
(
select (event_id - @marker_begin) as relative_event_id,
(end_event_id - @marker_begin) as relative_end_event_id,
event_name,
"(stage)" as comment,
nesting_event_type,
(nesting_event_id - @marker_begin) as relative_nesting_event_id
from performance_schema.events_stages_history_long
where (thread_id = @con1_tid)
and (@marker_begin <= event_id)
and (end_event_id <= @marker_end)
)
union
(
select (event_id - @marker_begin) as relative_event_id,
(end_event_id - @marker_begin) as relative_end_event_id,
event_name,
operation as comment,
nesting_event_type,
(nesting_event_id - @marker_begin) as relative_nesting_event_id
from performance_schema.events_waits_history_long
where (thread_id = @con1_tid)
and (@marker_begin <= event_id)
and (end_event_id <= @marker_end)
)
) all_events
order by relative_event_id asc;
relative_event_id relative_end_event_id event_name comment nesting_event_type relative_nesting_event_id
0 18 statement/sql/select select "MARKER_BEGIN" as marker NULL NULL
1 4 stage/sql/init (stage) STATEMENT 0
2 2 wait/io/socket/sql/client_connection recv STAGE 1
3 3 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 1
4 4 wait/io/file/sql/query_log write STAGE 1
5 5 stage/sql/checking permissions (stage) STATEMENT 0
6 6 stage/sql/Opening tables (stage) STATEMENT 0
7 7 stage/sql/After opening tables (stage) STATEMENT 0
8 8 stage/sql/init (stage) STATEMENT 0
9 9 stage/sql/optimizing (stage) STATEMENT 0
10 10 stage/sql/executing (stage) STATEMENT 0
11 11 stage/sql/end (stage) STATEMENT 0
12 12 stage/sql/query end (stage) STATEMENT 0
13 13 stage/sql/closing tables (stage) STATEMENT 0
14 14 stage/sql/freeing items (stage) STATEMENT 0
15 15 wait/io/socket/sql/client_connection send STATEMENT 0
16 16 wait/synch/mutex/sql/THD::LOCK_thd_data lock STATEMENT 0
17 18 stage/sql/cleaning up (stage) STATEMENT 0
18 18 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 17
19 19 idle idle NULL NULL
20 38 statement/sql/select select "This is simple statement one" as payload NULL NULL
21 24 stage/sql/init (stage) STATEMENT 20
22 22 wait/io/socket/sql/client_connection recv STAGE 21
23 23 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 21
24 24 wait/io/file/sql/query_log write STAGE 21
25 25 stage/sql/checking permissions (stage) STATEMENT 20
26 26 stage/sql/Opening tables (stage) STATEMENT 20
27 27 stage/sql/After opening tables (stage) STATEMENT 20
28 28 stage/sql/init (stage) STATEMENT 20
29 29 stage/sql/optimizing (stage) STATEMENT 20
30 30 stage/sql/executing (stage) STATEMENT 20
31 31 stage/sql/end (stage) STATEMENT 20
32 32 stage/sql/query end (stage) STATEMENT 20
33 33 stage/sql/closing tables (stage) STATEMENT 20
34 34 stage/sql/freeing items (stage) STATEMENT 20
35 35 wait/io/socket/sql/client_connection send STATEMENT 20
36 36 wait/synch/mutex/sql/THD::LOCK_thd_data lock STATEMENT 20
37 38 stage/sql/cleaning up (stage) STATEMENT 20
38 38 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 37
39 39 idle idle NULL NULL
40 58 statement/sql/select select "This is simple statement two" as payload NULL NULL
41 44 stage/sql/init (stage) STATEMENT 40
42 42 wait/io/socket/sql/client_connection recv STAGE 41
43 43 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 41
44 44 wait/io/file/sql/query_log write STAGE 41
45 45 stage/sql/checking permissions (stage) STATEMENT 40
46 46 stage/sql/Opening tables (stage) STATEMENT 40
47 47 stage/sql/After opening tables (stage) STATEMENT 40
48 48 stage/sql/init (stage) STATEMENT 40
49 49 stage/sql/optimizing (stage) STATEMENT 40
50 50 stage/sql/executing (stage) STATEMENT 40
51 51 stage/sql/end (stage) STATEMENT 40
52 52 stage/sql/query end (stage) STATEMENT 40
53 53 stage/sql/closing tables (stage) STATEMENT 40
54 54 stage/sql/freeing items (stage) STATEMENT 40
55 55 wait/io/socket/sql/client_connection send STATEMENT 40
56 56 wait/synch/mutex/sql/THD::LOCK_thd_data lock STATEMENT 40
57 58 stage/sql/cleaning up (stage) STATEMENT 40
58 58 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 57
59 59 idle idle NULL NULL
60 77 statement/sql/select select "This is the first part of a multi query" as payload;
select "And this is the second part of a multi query" as payload;
select "With a third part to make things complete" as payload NULL NULL
61 65 stage/sql/init (stage) STATEMENT 60
62 62 wait/io/socket/sql/client_connection recv STAGE 61
63 63 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 61
64 64 wait/io/file/sql/query_log write STAGE 61
65 65 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 61
66 66 stage/sql/checking permissions (stage) STATEMENT 60
67 67 stage/sql/Opening tables (stage) STATEMENT 60
68 68 stage/sql/After opening tables (stage) STATEMENT 60
69 69 stage/sql/init (stage) STATEMENT 60
70 70 stage/sql/optimizing (stage) STATEMENT 60
71 71 stage/sql/executing (stage) STATEMENT 60
72 72 stage/sql/end (stage) STATEMENT 60
73 73 stage/sql/query end (stage) STATEMENT 60
74 74 stage/sql/closing tables (stage) STATEMENT 60
75 77 stage/sql/freeing items (stage) STATEMENT 60
76 76 wait/io/socket/sql/client_connection send STAGE 75
77 77 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 75
78 93 statement/sql/select select "And this is the second part of a multi query" as payload;
select "With a third part to make things complete" as payload NULL NULL
79 81 stage/sql/init (stage) STATEMENT 78
80 80 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 79
81 81 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 79
82 82 stage/sql/checking permissions (stage) STATEMENT 78
83 83 stage/sql/Opening tables (stage) STATEMENT 78
84 84 stage/sql/After opening tables (stage) STATEMENT 78
85 85 stage/sql/init (stage) STATEMENT 78
86 86 stage/sql/optimizing (stage) STATEMENT 78
87 87 stage/sql/executing (stage) STATEMENT 78
88 88 stage/sql/end (stage) STATEMENT 78
89 89 stage/sql/query end (stage) STATEMENT 78
90 90 stage/sql/closing tables (stage) STATEMENT 78
91 93 stage/sql/freeing items (stage) STATEMENT 78
92 92 wait/io/socket/sql/client_connection send STAGE 91
93 93 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 91
94 110 statement/sql/select select "With a third part to make things complete" as payload NULL NULL
95 96 stage/sql/init (stage) STATEMENT 94
96 96 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 95
97 97 stage/sql/checking permissions (stage) STATEMENT 94
98 98 stage/sql/Opening tables (stage) STATEMENT 94
99 99 stage/sql/After opening tables (stage) STATEMENT 94
100 100 stage/sql/init (stage) STATEMENT 94
101 101 stage/sql/optimizing (stage) STATEMENT 94
102 102 stage/sql/executing (stage) STATEMENT 94
103 103 stage/sql/end (stage) STATEMENT 94
104 104 stage/sql/query end (stage) STATEMENT 94
105 105 stage/sql/closing tables (stage) STATEMENT 94
106 106 stage/sql/freeing items (stage) STATEMENT 94
107 107 wait/io/socket/sql/client_connection send STATEMENT 94
108 108 wait/synch/mutex/sql/THD::LOCK_thd_data lock STATEMENT 94
109 110 stage/sql/cleaning up (stage) STATEMENT 94
110 110 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 109
111 111 idle idle NULL NULL
112 130 statement/sql/select select "MARKER_END" as marker NULL NULL
113 116 stage/sql/init (stage) STATEMENT 112
114 114 wait/io/socket/sql/client_connection recv STAGE 113
115 115 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 113
116 116 wait/io/file/sql/query_log write STAGE 113
117 117 stage/sql/checking permissions (stage) STATEMENT 112
118 118 stage/sql/Opening tables (stage) STATEMENT 112
119 119 stage/sql/After opening tables (stage) STATEMENT 112
120 120 stage/sql/init (stage) STATEMENT 112
121 121 stage/sql/optimizing (stage) STATEMENT 112
122 122 stage/sql/executing (stage) STATEMENT 112
123 123 stage/sql/end (stage) STATEMENT 112
124 124 stage/sql/query end (stage) STATEMENT 112
125 125 stage/sql/closing tables (stage) STATEMENT 112
126 126 stage/sql/freeing items (stage) STATEMENT 112
127 127 wait/io/socket/sql/client_connection send STATEMENT 112
128 128 wait/synch/mutex/sql/THD::LOCK_thd_data lock STATEMENT 112
129 130 stage/sql/cleaning up (stage) STATEMENT 112
130 130 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 129
|
