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
|
"================== 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/init (stage) STATEMENT 0
8 8 stage/sql/optimizing (stage) STATEMENT 0
9 9 stage/sql/executing (stage) STATEMENT 0
10 10 stage/sql/end (stage) STATEMENT 0
11 11 stage/sql/query end (stage) STATEMENT 0
12 12 stage/sql/closing tables (stage) STATEMENT 0
13 13 stage/sql/freeing items (stage) STATEMENT 0
14 14 wait/io/socket/sql/client_connection send STATEMENT 0
15 15 wait/synch/mutex/sql/THD::LOCK_thd_data lock 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/init (stage) STATEMENT 20
28 28 stage/sql/optimizing (stage) STATEMENT 20
29 29 stage/sql/executing (stage) STATEMENT 20
30 30 stage/sql/end (stage) STATEMENT 20
31 31 stage/sql/query end (stage) STATEMENT 20
32 32 stage/sql/closing tables (stage) STATEMENT 20
33 33 stage/sql/freeing items (stage) STATEMENT 20
34 34 wait/io/socket/sql/client_connection send STATEMENT 20
35 35 wait/synch/mutex/sql/THD::LOCK_thd_data lock 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/init (stage) STATEMENT 40
48 48 stage/sql/optimizing (stage) STATEMENT 40
49 49 stage/sql/executing (stage) STATEMENT 40
50 50 stage/sql/end (stage) STATEMENT 40
51 51 stage/sql/query end (stage) STATEMENT 40
52 52 stage/sql/closing tables (stage) STATEMENT 40
53 53 stage/sql/freeing items (stage) STATEMENT 40
54 54 wait/io/socket/sql/client_connection send STATEMENT 40
55 55 wait/synch/mutex/sql/THD::LOCK_thd_data lock 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 76 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 64 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 stage/sql/checking permissions (stage) STATEMENT 60
66 66 stage/sql/Opening tables (stage) STATEMENT 60
67 67 stage/sql/init (stage) STATEMENT 60
68 68 stage/sql/optimizing (stage) STATEMENT 60
69 69 stage/sql/executing (stage) STATEMENT 60
70 70 stage/sql/end (stage) STATEMENT 60
71 71 stage/sql/query end (stage) STATEMENT 60
72 72 stage/sql/closing tables (stage) STATEMENT 60
73 76 stage/sql/freeing items (stage) STATEMENT 60
74 74 wait/io/socket/sql/client_connection send STAGE 73
75 75 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 73
76 76 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 73
77 91 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
78 79 stage/sql/init (stage) STATEMENT 77
79 79 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 78
80 80 stage/sql/checking permissions (stage) STATEMENT 77
81 81 stage/sql/Opening tables (stage) STATEMENT 77
82 82 stage/sql/init (stage) STATEMENT 77
83 83 stage/sql/optimizing (stage) STATEMENT 77
84 84 stage/sql/executing (stage) STATEMENT 77
85 85 stage/sql/end (stage) STATEMENT 77
86 86 stage/sql/query end (stage) STATEMENT 77
87 87 stage/sql/closing tables (stage) STATEMENT 77
88 91 stage/sql/freeing items (stage) STATEMENT 77
89 89 wait/io/socket/sql/client_connection send STAGE 88
90 90 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 88
91 91 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 88
92 108 statement/sql/select select "With a third part to make things complete" as payload NULL NULL
93 94 stage/sql/init (stage) STATEMENT 92
94 94 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 93
95 95 stage/sql/checking permissions (stage) STATEMENT 92
96 96 stage/sql/Opening tables (stage) STATEMENT 92
97 97 stage/sql/init (stage) STATEMENT 92
98 98 stage/sql/optimizing (stage) STATEMENT 92
99 99 stage/sql/executing (stage) STATEMENT 92
100 100 stage/sql/end (stage) STATEMENT 92
101 101 stage/sql/query end (stage) STATEMENT 92
102 102 stage/sql/closing tables (stage) STATEMENT 92
103 103 stage/sql/freeing items (stage) STATEMENT 92
104 104 wait/io/socket/sql/client_connection send STATEMENT 92
105 105 wait/synch/mutex/sql/THD::LOCK_thd_data lock STATEMENT 92
106 106 wait/synch/mutex/sql/THD::LOCK_thd_data lock STATEMENT 92
107 108 stage/sql/cleaning up (stage) STATEMENT 92
108 108 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 107
109 109 idle idle NULL NULL
110 128 statement/sql/select select "MARKER_END" as marker NULL NULL
111 114 stage/sql/init (stage) STATEMENT 110
112 112 wait/io/socket/sql/client_connection recv STAGE 111
113 113 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 111
114 114 wait/io/file/sql/query_log write STAGE 111
115 115 stage/sql/checking permissions (stage) STATEMENT 110
116 116 stage/sql/Opening tables (stage) STATEMENT 110
117 117 stage/sql/init (stage) STATEMENT 110
118 118 stage/sql/optimizing (stage) STATEMENT 110
119 119 stage/sql/executing (stage) STATEMENT 110
120 120 stage/sql/end (stage) STATEMENT 110
121 121 stage/sql/query end (stage) STATEMENT 110
122 122 stage/sql/closing tables (stage) STATEMENT 110
123 123 stage/sql/freeing items (stage) STATEMENT 110
124 124 wait/io/socket/sql/client_connection send STATEMENT 110
125 125 wait/synch/mutex/sql/THD::LOCK_thd_data lock STATEMENT 110
126 126 wait/synch/mutex/sql/THD::LOCK_thd_data lock STATEMENT 110
127 128 stage/sql/cleaning up (stage) STATEMENT 110
128 128 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 127
|
