Debian Codegraph @

mysql-test/suite/perfschema/t/socket_summary_by_event_name_func.test

raw
1 #
2 # Some functional checks for the content of the performance_schema table
3 # socket_summary_by_event_name.
4 #
5 # Created: cpowers 2011-08-18
6 #==============================================================================
7 #
8 # Performance schema table socket_summary_by_event_name holds statistics
9 # aggreagated by event name. Table socket_summary_by_instances maintains statistics
10 # per socket instance.
11 #
12 # This test verifies that the statistics in socket_summary_by_event_name
13 # are consistent with those in socket_summary_by_instance.
14 #
15 #==============================================================================
16
17 # Wait for any clients from previous tests to disconnect
18 --source ../include/wait_for_pfs_thread_count.inc
19
20 # The values in the performance_schema tables depend on how much communication
21 # happens per SQL statement within our MTR tests. And there is a significant
22 # difference between standard statement execution and execution via
23 # prepared statement.
24 # Test requires: sp-protocol/ps-protocol/view-protocol/cursor-protocol disabled
25 --source include/no_protocol.inc
26
27 --disconnect default
28 --source include/wait_until_disconnected.inc
29 --connect (default,localhost,root,,,,)
30
31 --source include/turn_off_only_full_group_by.inc
32
33 # Set this to enable debugging output
34 let $my_socket_debug= 0;
35
36 --echo #==============================================================================
37 --echo # Establish the level of IPV6 support
38 --echo #==============================================================================
39 --source ../include/socket_ipv6.inc
40
41 --echo #==============================================================================
42 --echo # Get hostname, port number
43 --echo #==============================================================================
44 SELECT @@hostname INTO @MY_HOSTNAME;
45 SELECT @@port INTO @MY_MASTER_PORT;
46
47 if ($my_socket_debug)
48 {
49 SELECT @MY_HOSTNAME AS 'Hostname';
50 SELECT @MY_MASTER_PORT AS 'Master Port';
51 --echo MASTER_MYPORT=$MASTER_MYPORT
52 }
53 #==============================================================================
54 # Utility queries
55 #==============================================================================
56 let $count_client_connections=
57 SELECT COUNT(*) INTO @my_client_connections
58 FROM performance_schema.socket_instances
59 WHERE EVENT_NAME LIKE "%client_connection%";
60
61 let $get_thread_id=
62 SELECT THREAD_ID INTO @my_thread_id
63 FROM performance_schema.threads
64 WHERE PROCESSLIST_ID = CONNECTION_ID();
65
66 let $get_object_instance_begin=
67 SELECT OBJECT_INSTANCE_BEGIN INTO @my_object_instance_begin
68 FROM performance_schema.socket_instances
69 WHERE THREAD_ID = @my_thread_id;
70
71 let $get_port=
72 SELECT PORT INTO @my_port
73 FROM performance_schema.socket_instances
74 WHERE THREAD_ID = @my_thread_id;
75
76 let $get_write_count=
77 SELECT sum(SUM_NUMBER_OF_BYTES_WRITE) INTO @my_write_count
78 FROM performance_schema.socket_summary_by_instance;
79 #==============================================================================
80 # Testcase queries
81 #==============================================================================
82 let $insert_totals=
83 INSERT INTO mysqltest.my_socket_summary SELECT EVENT_NAME,
84 SUM(t1.COUNT_STAR), SUM(t1.SUM_TIMER_WAIT), SUM(t1.MIN_TIMER_WAIT), SUM(t1.AVG_TIMER_WAIT), SUM(t1.MAX_TIMER_WAIT),
85 SUM(t1.COUNT_READ), SUM(t1.SUM_TIMER_READ), SUM(t1.MIN_TIMER_READ), SUM(t1.AVG_TIMER_READ), SUM(t1.MAX_TIMER_READ), SUM(t1.SUM_NUMBER_OF_BYTES_READ),
86 SUM(t1.COUNT_WRITE), SUM(t1.SUM_TIMER_WRITE), SUM(t1.MIN_TIMER_WRITE), SUM(t1.AVG_TIMER_WRITE), SUM(t1.MAX_TIMER_WRITE), SUM(t1.SUM_NUMBER_OF_BYTES_WRITE),
87 SUM(t1.COUNT_MISC), SUM(t1.SUM_TIMER_MISC), SUM(t1.MIN_TIMER_MISC), SUM(t1.AVG_TIMER_MISC), SUM(t1.MAX_TIMER_MISC), NULL
88 FROM performance_schema.socket_summary_by_instance AS t1
89 WHERE EVENT_NAME LIKE '%client_connection%' GROUP BY EVENT_NAME;
90
91 let $compare_tables_and_print=
92 SELECT EVENT_NAME,
93 (SUM(t_inst.COUNT_STAR) = t_name.COUNT_STAR) AS CNT_STAR,
94 (SUM(t_inst.SUM_TIMER_WAIT) = t_name.SUM_TIMER_WAIT) AS SUM_WAIT,
95 (MAX(t_inst.MAX_TIMER_WAIT) = t_name.MAX_TIMER_WAIT) AS MAX_WAIT,
96 # (MIN(t_inst.MIN_TIMER_WAIT) = t_name.MIN_TIMER_WAIT) AS MIN_WAIT,
97 (SUM(t_inst.COUNT_READ) = t_name.COUNT_READ) AS CNT_READ,
98 (SUM(t_inst.SUM_TIMER_READ) = t_name.SUM_TIMER_READ) AS SUM_READ,
99 (MAX(t_inst.MAX_TIMER_READ) = t_name.MAX_TIMER_READ) AS MAX_READ,
100 # (MIN(t_inst.MIN_TIMER_READ) = t_name.MIN_TIMER_READ) AS MIN_READ,
101 (SUM(t_inst.SUM_NUMBER_OF_BYTES_READ) = t_name.SUM_NUMBER_OF_BYTES_READ) AS BYTES_READ,
102 (SUM(t_inst.COUNT_WRITE) = t_name.COUNT_WRITE) AS CNT_WRITE,
103 (SUM(t_inst.SUM_TIMER_WRITE) = t_name.SUM_TIMER_WRITE) AS SUM_WRITE,
104 (MAX(t_inst.MAX_TIMER_WRITE) = t_name.MAX_TIMER_WRITE) AS MAX_WRITE,
105 # (MIN(t_inst.MIN_TIMER_WRITE) = t_name.MIN_TIMER_WRITE) AS MIN_WRITE,
106 (SUM(t_inst.SUM_NUMBER_OF_BYTES_WRITE) = t_name.SUM_NUMBER_OF_BYTES_WRITE) AS BYTES_WRITE,
107 (SUM(t_inst.COUNT_MISC) = t_name.COUNT_MISC) AS CNT_MISC,
108 (SUM(t_inst.SUM_TIMER_MISC) = t_name.SUM_TIMER_MISC) AS SUM_MISC
109 FROM performance_schema.socket_summary_by_instance t_inst
110 JOIN performance_schema.socket_summary_by_event_name t_name
111 USING (EVENT_NAME)
112 WHERE t_inst.event_name LIKE '%client%'
113 AND t_inst.object_instance_begin <> @default_object_instance_begin;
114
115 let $compare_tables_and_verify=
116 SELECT (
117 (SUM(t_inst.COUNT_STAR) = t_name.COUNT_STAR) AND
118 (SUM(t_inst.SUM_TIMER_WAIT) = t_name.SUM_TIMER_WAIT) AND
119 (MAX(t_inst.MAX_TIMER_WAIT) = t_name.MAX_TIMER_WAIT) AND
120 # (MIN(t_inst.MIN_TIMER_WAIT) = t_name.MIN_TIMER_WAIT) AND
121 (SUM(t_inst.COUNT_READ) = t_name.COUNT_READ) AND
122 (SUM(t_inst.SUM_TIMER_READ) = t_name.SUM_TIMER_READ) AND
123 (MAX(t_inst.MAX_TIMER_READ) = t_name.MAX_TIMER_READ) AND
124 # (MIN(t_inst.MIN_TIMER_READ) = t_name.MIN_TIMER_READ) AND
125 (SUM(t_inst.SUM_NUMBER_OF_BYTES_READ) = t_name.SUM_NUMBER_OF_BYTES_READ) AND
126 (SUM(t_inst.COUNT_WRITE) = t_name.COUNT_WRITE) AND
127 (SUM(t_inst.SUM_TIMER_WRITE) = t_name.SUM_TIMER_WRITE) AND
128 (MAX(t_inst.MAX_TIMER_WRITE) = t_name.MAX_TIMER_WRITE) AND
129 # (MIN(t_inst.MIN_TIMER_WRITE) = t_name.MIN_TIMER_WRITE) AND
130 (SUM(t_inst.SUM_NUMBER_OF_BYTES_WRITE) = t_name.SUM_NUMBER_OF_BYTES_WRITE) AND
131 (SUM(t_inst.COUNT_MISC) = t_name.COUNT_MISC) AND
132 (SUM(t_inst.SUM_TIMER_MISC) = t_name.SUM_TIMER_MISC) ) = 1 INTO @tables_match
133 FROM performance_schema.socket_summary_by_instance t_inst
134 JOIN performance_schema.socket_summary_by_event_name t_name
135 USING (EVENT_NAME)
136 WHERE t_inst.event_name LIKE '%client%'
137 AND t_inst.object_instance_begin <> @default_object_instance_begin;
138
139 --echo #==============================================================================
140 --echo # 1.0 TEST INITIALIZATION
141 --echo #==============================================================================
142 --echo
143 --echo # 1.1 Disable instrumentation of the default (this) connection
144 --echo
145 --connection default
146 UPDATE performance_schema.threads
147 SET INSTRUMENTED='NO' WHERE PROCESSLIST_ID = CONNECTION_ID();
148
149 --echo
150 --echo # 1.2 Get the default THREAD_ID;
151 --echo
152 eval $get_thread_id;
153 let $default_thread_id= `SELECT @my_thread_id`;
154
155 --echo
156 --echo # 1.3 Get the default OBJECT_INSTANCE_BEGIN
157 --echo
158 eval $get_object_instance_begin;
159 let $default_object_instance_begin= `SELECT @my_object_instance_begin`;
160
161 --disable_query_log
162 SELECT @my_object_instance_begin INTO @default_object_instance_begin;
163 --enable_query_log
164
165 if ($my_socket_debug)
166 {
167 --echo # Default object instance begin = $default_object_instance_begin
168 --echo # Default thread id = $default_thread_id
169 }
170
171 --echo
172 --echo # 1.4 Create a test database
173 --echo
174 CREATE SCHEMA mysqltest;
175
176 --echo
177 --echo # 1.5 Create a table to store summary values from socket_summary_by_instance
178 --echo and socket_summary_by_event_name
179 --echo
180 CREATE TABLE mysqltest.my_socket_summary AS
181 SELECT * FROM performance_schema.socket_summary_by_instance
182 WHERE 1 = 0;
183
184 --echo
185 --echo # 1.6 Drop object_instance_begin from my_socket_summary
186 --echo
187 ALTER TABLE mysqltest.my_socket_summary
188 DROP COLUMN OBJECT_INSTANCE_BEGIN;
189
190 --echo
191 --echo # 1.7 Add an auto_inc column to my_socket_summary
192 --echo
193 ALTER TABLE mysqltest.my_socket_summary
194 ADD COLUMN (n INT AUTO_INCREMENT, PRIMARY KEY(n));
195
196 --echo
197 --echo # 1.8 Create test tables
198 --echo
199 CREATE TABLE mysqltest.t1 (n INT AUTO_INCREMENT, s1 VARCHAR(1024), PRIMARY KEY(n));
200 CREATE TABLE mysqltest.t2 (n INT AUTO_INCREMENT, s1 VARCHAR(1024), PRIMARY KEY(n));
201
202 --echo #==============================================================================
203 --echo # 2.0 ESTABLISH CLIENT CONNECTIONS
204 --echo #==============================================================================
205 --echo
206 --echo # 2.1 Connection 1 (tcp/ip, 127.0.0.1 or ::1)
207 --echo
208 --disable_query_log
209 --connect (con1,$my_localhost,root,,,,$MASTER_MYPORT)
210 --enable_query_log
211
212 --echo
213 --echo # 2.2 Connection 2 (localhost or unix domain socket)
214 --echo
215 --disable_query_log
216 --connect (con2,localhost,root,,,,)
217 --enable_query_log
218
219 --echo #==============================================================================
220 --echo # 3.0 RUN THE TESTS
221 --echo #==============================================================================
222 --echo
223 --echo # 3.1 Clear performance schema tables
224 --echo
225 TRUNCATE performance_schema.socket_summary_by_instance;
226 TRUNCATE performance_schema.socket_summary_by_event_name;
227
228 --echo
229 --echo # 3.2 Get the 'before' sum of bytes written from socket_summary_by_instance
230 --echo for later comparison to the 'after' byte count as a simple confirmation
231 --echo that the table was updated.
232 --echo
233 --connection default
234 eval $get_write_count;
235 let $my_count_before= `SELECT @my_write_count`;
236
237 --echo
238 --echo # 3.3 From connection 1, insert one a 1K row of data into t1
239 --echo
240 --connection con1
241 USE mysqltest;
242 INSERT INTO t1 (s1) VALUES (REPEAT('a', 1024));
243 INSERT INTO t1 (s1) SELECT s1 FROM t1;
244
245 --echo
246 --echo # 3.4 From connection 2, insert one a 1K row of data into t2
247 --echo
248 --connection con2
249 USE mysqltest;
250 INSERT INTO t2 (s1) VALUES (REPEAT('a', 1024));
251 INSERT INTO t2 (s1) SELECT s1 FROM t2;
252
253 --echo
254 --echo # 3.5 Get the 'after' sum of bytes written from socket_summary_by_instance
255 --echo
256 --connection default
257 eval $get_write_count;
258 let $my_count_after= `SELECT @my_write_count`;
259
260 --echo
261 --echo # 3.6 Verify that SUM_NUMBER_OF_BYTES_WRITE increased
262 --echo
263
264 if ($my_count_before >= $my_count_after)
265 {
266 --echo
267 --echo # ERROR: Write count did not increaase
268 --echo # Before = $my_count_before
269 --echo # After = $my_count_after
270 --echo
271 SELECT * FROM performance_schema.socket_summary_by_instance ORDER BY EVENT_NAME;
272 --echo
273 --echo # Abort
274 exit;
275 }
276
277 --echo
278 --echo # socket_summary_by_instance was updated
279 --echo
280
281 --echo #==============================================================================
282 --echo # 4.0 VERIFY RESULTS
283 --echo #==============================================================================
284 --echo
285 --echo # 4.1 Verify that the totals in socket_summary_by_event_name are
286 --echo consistent with totals in socket_summary_by_instance
287 --echo
288
289 --disable_query_log
290 eval $compare_tables_and_verify;
291
292 if (`SELECT @tables_match = 0`)
293 {
294 --echo
295 --echo # ERROR: Socket summary tables do not match
296 --echo # Column comparison results:
297 --echo
298 eval $compare_tables_and_print;
299 --enable_query_log
300 --echo
301 SELECT * FROM performance_schema.socket_summary_by_instance ORDER BY EVENT_NAME;
302 --echo
303 SELECT * FROM performance_schema.socket_summary_by_event_name ORDER BY EVENT_NAME;
304 --echo
305 --echo # Abort
306 exit;
307 }
308 --enable_query_log
309
310 --echo #==============================================================================
311 --echo # 5.0 Clean up
312 --echo #==============================================================================
313
314 --echo
315 --echo # 5.1 Disconnect con1
316 --echo
317 --connection con1
318 --disconnect con1
319 --source include/wait_until_disconnected.inc
320
321 --echo
322 --echo # 5.2 Disconnect con2
323 --echo
324 --connection con2
325 --disconnect con2
326 --source include/wait_until_disconnected.inc
327
328 --echo
329 --echo # 5.3 Drop mysqltest
330 --echo
331 --connection default
332 DROP DATABASE mysqltest;
333
334 --source include/restore_sql_mode_after_turn_off_only_full_group_by.inc
335
336 exit;
337