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
|
# Test for prepared statement instrumentation
--source include/not_embedded.inc
--source include/have_perfschema.inc
--source include/no_protocol.inc
CREATE DATABASE db;
USE db;
--let $psi_select = SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT
--let $psi_truncate = TRUNCATE TABLE performance_schema.prepared_statements_instances
--let $eshl_select = SELECT EVENT_NAME, SQL_TEXT, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA='db'
--let $eshl_truncate = TRUNCATE TABLE performance_schema.events_statements_history_long
--eval $psi_truncate
--eval $eshl_truncate
--source suite/perfschema/include/prepared_stmts_setup.inc
--vertical_results
--eval $psi_select
--eval $eshl_select
--horizontal_results
--source suite/perfschema/include/prepared_stmts_execution.inc
--vertical_results
--eval $psi_select
--eval $eshl_select
--horizontal_results
# Truncate to reset the statistics
--eval $psi_truncate
# check whether the statistics are reset
--vertical_results
--eval $psi_select
--horizontal_results
--source suite/perfschema/include/prepared_stmts_deallocation.inc
--vertical_results
# select query on prepared_statements_instances table must return empty set
--eval $psi_select
--eval $eshl_select
--horizontal_results
# truncate
--eval $eshl_truncate
#
# Test to check the instrumentation of prepared statements
# when all consumers in setup_consumers are disabled.
#
# Disable all consumers
UPDATE performance_schema.setup_consumers SET ENABLED = 'NO';
--source suite/perfschema/include/prepared_stmts_setup.inc
--vertical_results
--eval $psi_select
--eval $eshl_select
--horizontal_results
--source suite/perfschema/include/prepared_stmts_execution.inc
--vertical_results
--eval $psi_select
--eval $eshl_select
--horizontal_results
--source suite/perfschema/include/prepared_stmts_deallocation.inc
--vertical_results
--eval $psi_select
--eval $eshl_select
--horizontal_results
# truncate
--eval $eshl_truncate
# restore the initial set-up of consumers table
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES';
#
# Aggregation
#
PREPARE st FROM 'SELECT SUM(1000 + ?) AS total';
--vertical_results
--eval $psi_select
--horizontal_results
SET @d=100;
EXECUTE st USING @d;
--vertical_results
--eval $psi_select
--horizontal_results
let $i=5;
while($i)
{
SET @d = @d + 100;
EXECUTE st USING @d;
--vertical_results
--eval $psi_select
--horizontal_results
dec $i;
}
# truncate
--eval $psi_truncate
--vertical_results
--eval $psi_select
--horizontal_results
#
# check whether the instrumented prepared statement is removed from the
# prepared_statement_instances table after de allocation of the prepared
# statement even if instrumentation is disabled
#
# Disable prepared statement instrumentation
UPDATE performance_schema.setup_instruments SET ENABLED = 'NO'
WHERE NAME like "statement/sql/execute%";
SET @d=3274;
# COUNT_EXECUTE should remain 0. No increment must be seen.
EXECUTE st USING @d;
--vertical_results
--eval $psi_select
--horizontal_results
UPDATE performance_schema.setup_instruments SET ENABLED = 'NO'
WHERE NAME like "statement/sql/prepare%";
DEALLOCATE PREPARE st;
# should return empty set.
--vertical_results
--eval $psi_select
--horizontal_results
# Restore back teh initial set-up of setup_instruments table
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES'
WHERE NAME like "statement/sql/prepare%";
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES'
WHERE NAME like "statement/sql/execute%";
# Reprepare
CREATE TABLE tab(
Id INT,
name CHAR(10),
age INT
);
INSERT INTO tab VALUES(1,"Nakshatr",25),(2,"chanda",24),(3,"tejas",78);
PREPARE st FROM 'SELECT * FROM tab';
--vertical_results
--eval $psi_select
--horizontal_results
EXECUTE st;
--vertical_results
--eval $psi_select
--horizontal_results
ALTER TABLE tab DROP COLUMN age;
EXECUTE st;
--vertical_results
--eval $psi_select
--horizontal_results
ALTER TABLE tab ADD COLUMN age INT NULL;
EXECUTE st;
--vertical_results
--eval $psi_select
--horizontal_results
# check if the statistics are reset
--eval $psi_truncate
--vertical_results
--eval $psi_select
--horizontal_results
DEALLOCATE PREPARE st;
--vertical_results
--eval $psi_select
--horizontal_results
# clean up
# truncate
--eval $psi_truncate
--eval $eshl_truncate
DROP TABLE tab;
DROP DATABASE db;
|
