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