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
|
"================== 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_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 11 stage/sql/executing (stage) STATEMENT 0
10 10 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 9
11 11 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 9
12 12 stage/sql/end (stage) STATEMENT 0
13 13 stage/sql/query end (stage) STATEMENT 0
14 14 stage/sql/closing tables (stage) STATEMENT 0
15 15 stage/sql/freeing items (stage) STATEMENT 0
16 16 wait/io/socket/sql/client_connection send 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 31 stage/sql/executing (stage) STATEMENT 20
30 30 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 29
31 31 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 29
32 32 stage/sql/end (stage) STATEMENT 20
33 33 stage/sql/query end (stage) STATEMENT 20
34 34 stage/sql/closing tables (stage) STATEMENT 20
35 35 stage/sql/freeing items (stage) STATEMENT 20
36 36 wait/io/socket/sql/client_connection send 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 51 stage/sql/executing (stage) STATEMENT 40
50 50 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 49
51 51 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 49
52 52 stage/sql/end (stage) STATEMENT 40
53 53 stage/sql/query end (stage) STATEMENT 40
54 54 stage/sql/closing tables (stage) STATEMENT 40
55 55 stage/sql/freeing items (stage) STATEMENT 40
56 56 wait/io/socket/sql/client_connection send 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 71 stage/sql/executing (stage) STATEMENT 60
70 70 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 69
71 71 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 69
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 76 stage/sql/freeing items (stage) STATEMENT 60
76 76 wait/io/socket/sql/client_connection send STAGE 75
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 86 stage/sql/executing (stage) STATEMENT 77
85 85 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 84
86 86 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 84
87 87 stage/sql/end (stage) STATEMENT 77
88 88 stage/sql/query end (stage) STATEMENT 77
89 89 stage/sql/closing tables (stage) STATEMENT 77
90 91 stage/sql/freeing items (stage) STATEMENT 77
91 91 wait/io/socket/sql/client_connection send STAGE 90
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 101 stage/sql/executing (stage) STATEMENT 92
100 100 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 99
101 101 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 99
102 102 stage/sql/end (stage) STATEMENT 92
103 103 stage/sql/query end (stage) STATEMENT 92
104 104 stage/sql/closing tables (stage) STATEMENT 92
105 105 stage/sql/freeing items (stage) STATEMENT 92
106 106 wait/io/socket/sql/client_connection send 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 121 stage/sql/executing (stage) STATEMENT 110
120 120 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 119
121 121 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 119
122 122 stage/sql/end (stage) STATEMENT 110
123 123 stage/sql/query end (stage) STATEMENT 110
124 124 stage/sql/closing tables (stage) STATEMENT 110
125 125 stage/sql/freeing items (stage) STATEMENT 110
126 126 wait/io/socket/sql/client_connection send STATEMENT 110
127 128 stage/sql/cleaning up (stage) STATEMENT 110
128 128 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 127
|
