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
|
"================== 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 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 85 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 75 stage/sql/init (stage) STATEMENT 73
75 75 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 74
76 76 stage/sql/checking permissions (stage) STATEMENT 73
77 77 stage/sql/Opening tables (stage) STATEMENT 73
78 78 stage/sql/init (stage) STATEMENT 73
79 79 stage/sql/optimizing (stage) STATEMENT 73
80 80 stage/sql/executing (stage) STATEMENT 73
81 81 stage/sql/end (stage) STATEMENT 73
82 82 stage/sql/query end (stage) STATEMENT 73
83 83 stage/sql/closing tables (stage) STATEMENT 73
84 85 stage/sql/freeing items (stage) STATEMENT 73
85 85 wait/io/socket/sql/client_connection send STAGE 84
86 100 statement/sql/select select "With a third part to make things complete" as payload NULL NULL
87 88 stage/sql/init (stage) STATEMENT 86
88 88 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 87
89 89 stage/sql/checking permissions (stage) STATEMENT 86
90 90 stage/sql/Opening tables (stage) STATEMENT 86
91 91 stage/sql/init (stage) STATEMENT 86
92 92 stage/sql/optimizing (stage) STATEMENT 86
93 93 stage/sql/executing (stage) STATEMENT 86
94 94 stage/sql/end (stage) STATEMENT 86
95 95 stage/sql/query end (stage) STATEMENT 86
96 96 stage/sql/closing tables (stage) STATEMENT 86
97 98 stage/sql/freeing items (stage) STATEMENT 86
98 98 wait/io/socket/sql/client_connection send STAGE 97
99 100 stage/sql/cleaning up (stage) STATEMENT 86
100 100 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 99
101 101 idle idle NULL NULL
102 119 statement/sql/select select "MARKER_END" as marker NULL NULL
103 107 stage/sql/init (stage) STATEMENT 102
104 104 wait/io/socket/sql/client_connection recv STAGE 103
105 105 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 103
106 106 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 103
107 107 wait/io/file/sql/query_log write STAGE 103
108 108 stage/sql/checking permissions (stage) STATEMENT 102
109 109 stage/sql/Opening tables (stage) STATEMENT 102
110 110 stage/sql/init (stage) STATEMENT 102
111 111 stage/sql/optimizing (stage) STATEMENT 102
112 112 stage/sql/executing (stage) STATEMENT 102
113 113 stage/sql/end (stage) STATEMENT 102
114 114 stage/sql/query end (stage) STATEMENT 102
115 115 stage/sql/closing tables (stage) STATEMENT 102
116 117 stage/sql/freeing items (stage) STATEMENT 102
117 117 wait/io/socket/sql/client_connection send STAGE 116
118 119 stage/sql/cleaning up (stage) STATEMENT 102
119 119 wait/synch/mutex/sql/THD::LOCK_thd_data lock STAGE 118
|
