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
244
245
246
247
248
|
"================== 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 19 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 17 wait/synch/mutex/sql/THD::LOCK_thd_data lock STATEMENT 0
18 19 stage/sql/cleaning up (stage) STATEMENT 0
19 19 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 18
20 20 idle idle NULL NULL
21 40 statement/sql/select select "This is simple statement one" as payload NULL NULL
22 25 stage/sql/init (stage) STATEMENT 21
23 23 wait/io/socket/sql/client_connection recv STAGE 22
24 24 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 22
25 25 wait/io/file/sql/query_log write STAGE 22
26 26 stage/sql/checking permissions (stage) STATEMENT 21
27 27 stage/sql/Opening tables (stage) STATEMENT 21
28 28 stage/sql/After opening tables (stage) STATEMENT 21
29 29 stage/sql/init (stage) STATEMENT 21
30 30 stage/sql/optimizing (stage) STATEMENT 21
31 31 stage/sql/executing (stage) STATEMENT 21
32 32 stage/sql/end (stage) STATEMENT 21
33 33 stage/sql/query end (stage) STATEMENT 21
34 34 stage/sql/closing tables (stage) STATEMENT 21
35 35 stage/sql/freeing items (stage) STATEMENT 21
36 36 wait/io/socket/sql/client_connection send STATEMENT 21
37 37 wait/synch/mutex/sql/THD::LOCK_thd_data lock STATEMENT 21
38 38 wait/synch/mutex/sql/THD::LOCK_thd_data lock STATEMENT 21
39 40 stage/sql/cleaning up (stage) STATEMENT 21
40 40 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 39
41 41 idle idle NULL NULL
42 61 statement/sql/select select "This is simple statement two" as payload NULL NULL
43 46 stage/sql/init (stage) STATEMENT 42
44 44 wait/io/socket/sql/client_connection recv STAGE 43
45 45 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 43
46 46 wait/io/file/sql/query_log write STAGE 43
47 47 stage/sql/checking permissions (stage) STATEMENT 42
48 48 stage/sql/Opening tables (stage) STATEMENT 42
49 49 stage/sql/After opening tables (stage) STATEMENT 42
50 50 stage/sql/init (stage) STATEMENT 42
51 51 stage/sql/optimizing (stage) STATEMENT 42
52 52 stage/sql/executing (stage) STATEMENT 42
53 53 stage/sql/end (stage) STATEMENT 42
54 54 stage/sql/query end (stage) STATEMENT 42
55 55 stage/sql/closing tables (stage) STATEMENT 42
56 56 stage/sql/freeing items (stage) STATEMENT 42
57 57 wait/io/socket/sql/client_connection send STATEMENT 42
58 58 wait/synch/mutex/sql/THD::LOCK_thd_data lock STATEMENT 42
59 59 wait/synch/mutex/sql/THD::LOCK_thd_data lock STATEMENT 42
60 61 stage/sql/cleaning up (stage) STATEMENT 42
61 61 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 60
62 62 idle idle NULL NULL
63 80 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
64 67 stage/sql/init (stage) STATEMENT 63
65 65 wait/io/socket/sql/client_connection recv STAGE 64
66 66 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 64
67 67 wait/io/file/sql/query_log write STAGE 64
68 68 stage/sql/checking permissions (stage) STATEMENT 63
69 69 stage/sql/Opening tables (stage) STATEMENT 63
70 70 stage/sql/After opening tables (stage) STATEMENT 63
71 71 stage/sql/init (stage) STATEMENT 63
72 72 stage/sql/optimizing (stage) STATEMENT 63
73 73 stage/sql/executing (stage) STATEMENT 63
74 74 stage/sql/end (stage) STATEMENT 63
75 75 stage/sql/query end (stage) STATEMENT 63
76 76 stage/sql/closing tables (stage) STATEMENT 63
77 80 stage/sql/freeing items (stage) STATEMENT 63
78 78 wait/io/socket/sql/client_connection send STAGE 77
79 79 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 77
80 80 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 77
81 96 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
82 83 stage/sql/init (stage) STATEMENT 81
83 83 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 82
84 84 stage/sql/checking permissions (stage) STATEMENT 81
85 85 stage/sql/Opening tables (stage) STATEMENT 81
86 86 stage/sql/After opening tables (stage) STATEMENT 81
87 87 stage/sql/init (stage) STATEMENT 81
88 88 stage/sql/optimizing (stage) STATEMENT 81
89 89 stage/sql/executing (stage) STATEMENT 81
90 90 stage/sql/end (stage) STATEMENT 81
91 91 stage/sql/query end (stage) STATEMENT 81
92 92 stage/sql/closing tables (stage) STATEMENT 81
93 96 stage/sql/freeing items (stage) STATEMENT 81
94 94 wait/io/socket/sql/client_connection send STAGE 93
95 95 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 93
96 96 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 93
97 114 statement/sql/select select "With a third part to make things complete" as payload NULL NULL
98 99 stage/sql/init (stage) STATEMENT 97
99 99 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 98
100 100 stage/sql/checking permissions (stage) STATEMENT 97
101 101 stage/sql/Opening tables (stage) STATEMENT 97
102 102 stage/sql/After opening tables (stage) STATEMENT 97
103 103 stage/sql/init (stage) STATEMENT 97
104 104 stage/sql/optimizing (stage) STATEMENT 97
105 105 stage/sql/executing (stage) STATEMENT 97
106 106 stage/sql/end (stage) STATEMENT 97
107 107 stage/sql/query end (stage) STATEMENT 97
108 108 stage/sql/closing tables (stage) STATEMENT 97
109 109 stage/sql/freeing items (stage) STATEMENT 97
110 110 wait/io/socket/sql/client_connection send STATEMENT 97
111 111 wait/synch/mutex/sql/THD::LOCK_thd_data lock STATEMENT 97
112 112 wait/synch/mutex/sql/THD::LOCK_thd_data lock STATEMENT 97
113 114 stage/sql/cleaning up (stage) STATEMENT 97
114 114 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 113
115 115 idle idle NULL NULL
116 135 statement/sql/select select "MARKER_END" as marker NULL NULL
117 120 stage/sql/init (stage) STATEMENT 116
118 118 wait/io/socket/sql/client_connection recv STAGE 117
119 119 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 117
120 120 wait/io/file/sql/query_log write STAGE 117
121 121 stage/sql/checking permissions (stage) STATEMENT 116
122 122 stage/sql/Opening tables (stage) STATEMENT 116
123 123 stage/sql/After opening tables (stage) STATEMENT 116
124 124 stage/sql/init (stage) STATEMENT 116
125 125 stage/sql/optimizing (stage) STATEMENT 116
126 126 stage/sql/executing (stage) STATEMENT 116
127 127 stage/sql/end (stage) STATEMENT 116
128 128 stage/sql/query end (stage) STATEMENT 116
129 129 stage/sql/closing tables (stage) STATEMENT 116
130 130 stage/sql/freeing items (stage) STATEMENT 116
131 131 wait/io/socket/sql/client_connection send STATEMENT 116
132 132 wait/synch/mutex/sql/THD::LOCK_thd_data lock STATEMENT 116
133 133 wait/synch/mutex/sql/THD::LOCK_thd_data lock STATEMENT 116
134 135 stage/sql/cleaning up (stage) STATEMENT 116
135 135 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 134
|
