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
|
--source include/not_embedded.inc
--source include/have_perfschema.inc
#
# WL#4814, 4.1.2 STORAGE ENGINE, FSE8: Selects
#
# Make some data that we can work on:
UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES';
# Disable ALL table IO, to avoid generating events from the selects
# To be revised after WL#5342 PERFORMANCE SCHEMA SETUP OBJECTS
UPDATE performance_schema.setup_instruments SET enabled = 'NO', timed = 'NO'
where NAME='wait/io/table/sql/handler';
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
CREATE TABLE t1 (id INT PRIMARY KEY, b CHAR(100) DEFAULT 'initial value')
ENGINE=MyISAM;
INSERT INTO t1 (id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
# ORDER BY, GROUP BY and HAVING
--replace_column 2 [NUM_BYTES]
SELECT OPERATION, SUM(NUMBER_OF_BYTES) AS TOTAL
FROM performance_schema.events_waits_history_long
GROUP BY OPERATION
HAVING TOTAL IS NOT NULL
ORDER BY OPERATION
LIMIT 1;
# Sub SELECT
--replace_column 1 [EVENT_ID]
SELECT EVENT_ID FROM performance_schema.events_waits_current
WHERE THREAD_ID IN
(SELECT THREAD_ID FROM performance_schema.threads)
AND EVENT_NAME IN
(SELECT NAME FROM performance_schema.setup_instruments
WHERE NAME LIKE "wait/synch/%")
LIMIT 1;
# JOIN
--replace_column 1 [EVENT_ID]
SELECT DISTINCT EVENT_ID
FROM performance_schema.events_waits_current
JOIN performance_schema.events_waits_history USING (EVENT_ID)
JOIN performance_schema.events_waits_history_long USING (EVENT_ID)
ORDER BY EVENT_ID
LIMIT 1;
# Self JOIN
--replace_column 1 [THREAD_ID] 2 [EVENT_ID] 3 [EVENT_NAME] 4 [TIMER_WAIT]
SELECT t1.THREAD_ID, t2.EVENT_ID, t3.EVENT_NAME, t4.TIMER_WAIT
FROM performance_schema.events_waits_history t1
JOIN performance_schema.events_waits_history t2 USING (EVENT_ID)
JOIN performance_schema.events_waits_history t3 ON (t2.THREAD_ID = t3.THREAD_ID)
JOIN performance_schema.events_waits_history t4 ON (t3.EVENT_NAME = t4.EVENT_NAME)
ORDER BY t1.EVENT_ID, t2.EVENT_ID
LIMIT 5;
# UNION
--replace_column 1 [THREAD_ID] 2 [EVENT_ID]
SELECT THREAD_ID, EVENT_ID FROM (
SELECT THREAD_ID, EVENT_ID FROM performance_schema.events_waits_current
UNION
SELECT THREAD_ID, EVENT_ID FROM performance_schema.events_waits_history
UNION
SELECT THREAD_ID, EVENT_ID FROM performance_schema.events_waits_history_long
) t1 ORDER BY THREAD_ID, EVENT_ID
LIMIT 5;
# EVENT
# Check that the event_scheduler is really running
--source include/running_event_scheduler.inc
--disable_warnings
DROP TABLE IF EXISTS t_event;
DROP EVENT IF EXISTS t_ps_event;
--enable_warnings
CREATE TABLE t_event AS
SELECT EVENT_ID FROM performance_schema.events_waits_current
WHERE 1 = 2;
CREATE EVENT t_ps_event
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND
ON COMPLETION PRESERVE
DO INSERT INTO t_event
SELECT DISTINCT EVENT_ID
FROM performance_schema.events_waits_current
JOIN performance_schema.events_waits_history USING (EVENT_ID)
ORDER BY EVENT_ID
LIMIT 1;
# TRIGGER
ALTER TABLE t1 ADD COLUMN c INT;
--disable_warnings
DROP TRIGGER IF EXISTS t_ps_trigger;
--enable_warnings
delimiter |;
CREATE TRIGGER t_ps_trigger BEFORE INSERT ON t1
FOR EACH ROW BEGIN
SET NEW.c = (SELECT MAX(EVENT_ID)
FROM performance_schema.events_waits_current);
END;
|
delimiter ;|
INSERT INTO t1 (id) VALUES (11), (12), (13);
--replace_column 2 [EVENT_ID]
SELECT id, c FROM t1 WHERE id > 10 ORDER BY c;
DROP TRIGGER t_ps_trigger;
# PROCEDURE
--disable_warnings
DROP PROCEDURE IF EXISTS t_ps_proc;
--enable_warnings
--enable_prepare_warnings
delimiter |;
CREATE PROCEDURE t_ps_proc(IN conid INT, OUT pid INT)
BEGIN
SELECT thread_id FROM performance_schema.threads
WHERE PROCESSLIST_ID = conid INTO pid;
END;
|
delimiter ;|
CALL t_ps_proc(connection_id(), @p_id);
--disable_prepare_warnings
# FUNCTION
--disable_warnings
DROP FUNCTION IF EXISTS t_ps_proc;
--enable_warnings
delimiter |;
CREATE FUNCTION t_ps_func(conid INT) RETURNS int
BEGIN
return (SELECT thread_id FROM performance_schema.threads
WHERE PROCESSLIST_ID = conid);
END;
|
delimiter ;|
SELECT t_ps_func(connection_id()) = @p_id;
# We might reach this point too early which means the event scheduler has not
# executed our "t_ps_event". Therefore we poll till the record was inserted
# and run our test statement afterwards.
let $wait_timeout= 20;
let $wait_condition= SELECT COUNT(*) = 1 FROM t_event;
--source include/wait_condition.inc
--replace_column 1 [EVENT_ID]
SELECT * FROM t_event;
# Clean up
DROP PROCEDURE t_ps_proc;
DROP FUNCTION t_ps_func;
DROP EVENT t_ps_event;
DROP TABLE t1;
DROP TABLE t_event;
UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES';
|
