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