Debian Codegraph @

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

raw
1 ##
2 ## WL#4814, 4.1.4 FILE IO
3 ##
4 ## Functional testing of File IO
5 ##
6
7 --source include/have_myisam.inc
8 --source include/force_myisam_default.inc
9
10 UPDATE performance_schema.setup_instruments SET enabled = 'NO', timed = 'YES';
11
12 UPDATE performance_schema.setup_instruments SET enabled = 'YES'
13 WHERE name LIKE 'wait/io/file/%';
14
15 update performance_schema.threads SET instrumented = 'YES'
16 WHERE PROCESSLIST_ID=connection_id();
17
18 # reset lost counters
19 truncate table performance_schema.events_statements_summary_by_digest;
20 flush status;
21
22 --disable_warnings
23 DROP TABLE IF EXISTS t1;
24 --enable_warnings
25
26 #
27 # TODO: Change to InnoDB when it gets instrumentation
28 #
29
30 CREATE TABLE t1 (id INT PRIMARY KEY, b CHAR(100) DEFAULT 'initial value')
31 ENGINE=MyISAM;
32
33 INSERT INTO t1 (id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
34
35 TRUNCATE TABLE performance_schema.events_waits_history_long;
36 TRUNCATE TABLE performance_schema.events_waits_history;
37 TRUNCATE TABLE performance_schema.events_waits_current;
38
39 #
40 # FF1: Count for file should increase with instrumentation enabled and
41 # FF2: Count for file should not increase with instrumentation disabled
42 #
43
44 SELECT * FROM t1 WHERE id = 1;
45
46 SET @before_count = (SELECT SUM(TIMER_WAIT)
47 FROM performance_schema.events_waits_history_long
48 WHERE (EVENT_NAME = 'wait/io/file/myisam/dfile')
49 AND (OBJECT_NAME LIKE '%t1.MYD'));
50
51 SELECT (@before_count >= 0) as have_before_count;
52 SELECT IF(@before_count > 0, 'Success', 'Failure') has_instrumentation;
53
54 SELECT * FROM t1 WHERE id < 4;
55
56 SET @after_count = (SELECT SUM(TIMER_WAIT)
57 FROM performance_schema.events_waits_history_long
58 WHERE (EVENT_NAME = 'wait/io/file/myisam/dfile')
59 AND (OBJECT_NAME LIKE '%t1.MYD') AND (1 = 1));
60
61 SELECT (@after_count >= 0) as have_after_count;
62 SELECT IF((@after_count - @before_count) > 0, 'Success', 'Failure') test_ff1_timed;
63
64 UPDATE performance_schema.setup_instruments SET enabled='NO';
65
66 SET @before_count = (SELECT SUM(TIMER_WAIT)
67 FROM performance_schema.events_waits_history_long
68 WHERE (EVENT_NAME = 'wait/io/file/myisam/dfile')
69 AND (OBJECT_NAME LIKE '%t1.MYD') AND (2 = 2));
70
71 SELECT (@before_count >= 0) as have_before_count;
72 SELECT * FROM t1 WHERE id < 6;
73
74 SET @after_count = (SELECT SUM(TIMER_WAIT)
75 FROM performance_schema.events_waits_history_long
76 WHERE (EVENT_NAME = 'wait/io/file/myisam/dfile')
77 AND (OBJECT_NAME LIKE '%t1.MYD') AND (3 = 3));
78
79 SELECT (@after_count >= 0) as have_after_count;
80 SELECT IF((COALESCE(@after_count, 0) - COALESCE(@before_count, 0)) = 0, 'Success', 'Failure') test_ff2_timed;
81
82 #
83 # Check not timed measurements
84 #
85
86 UPDATE performance_schema.setup_instruments SET enabled = 'YES'
87 WHERE name LIKE 'wait/io/file/%';
88
89 UPDATE performance_schema.setup_instruments SET timed = 'NO';
90
91 TRUNCATE TABLE performance_schema.events_waits_history_long;
92 TRUNCATE TABLE performance_schema.events_waits_history;
93 TRUNCATE TABLE performance_schema.events_waits_current;
94
95 SELECT * FROM t1 WHERE id > 4;
96
97 SELECT * FROM performance_schema.events_waits_history_long
98 WHERE TIMER_WAIT != NULL
99 OR TIMER_START != NULL
100 OR TIMER_END != NULL;
101
102 SELECT * FROM performance_schema.events_waits_history
103 WHERE TIMER_WAIT != NULL
104 OR TIMER_START != NULL
105 OR TIMER_END != NULL;
106
107 SELECT * FROM performance_schema.events_waits_current
108 WHERE TIMER_WAIT != NULL
109 OR TIMER_START != NULL
110 OR TIMER_END != NULL;
111
112 UPDATE performance_schema.setup_instruments SET timed = 'YES';
113
114 SELECT * FROM t1 WHERE id < 4;
115
116 DROP TABLE t1;
117
118 #
119 # FF4: Use-case from Enterprise Monitor
120 #
121
122 --disable_result_log
123 SELECT SUM(COUNT_READ) AS sum_count_read,
124 SUM(COUNT_WRITE) AS sum_count_write,
125 SUM(SUM_NUMBER_OF_BYTES_READ) AS sum_num_bytes_read,
126 SUM(SUM_NUMBER_OF_BYTES_WRITE) AS sum_num_bytes_write
127 FROM performance_schema.file_summary_by_instance
128 WHERE FILE_NAME LIKE CONCAT('%', @@tmpdir, '%') ORDER BY NULL;
129 --enable_result_log
130
131 #
132 # FF5: Troubleshooting tasks
133 #
134 # These queries will give different results based on timing,
135 # but at least they should not crash.
136 #
137
138 #
139 # Total and average wait time for different events on system level
140 #
141 --disable_result_log
142 SELECT EVENT_NAME, COUNT_STAR, AVG_TIMER_WAIT, SUM_TIMER_WAIT
143 FROM performance_schema.events_waits_summary_global_by_event_name
144 WHERE COUNT_STAR > 0
145 ORDER BY SUM_TIMER_WAIT DESC
146 LIMIT 10;
147 --enable_result_log
148
149 #
150 # Total and average wait time for different users
151 #
152
153 --disable_result_log
154 SELECT p.processlist_user, SUM(TIMER_WAIT) SUM_WAIT
155 # ((TIME_TO_SEC(TIMEDIFF(NOW(), i.startup_time)) * 1000) / SUM(TIMER_WAIT)) * 100 WAIT_PERCENTAGE
156 FROM performance_schema.events_waits_history_long h
157 INNER JOIN performance_schema.threads p USING (THREAD_ID)
158 GROUP BY p.processlist_user
159 ORDER BY SUM_WAIT DESC
160 LIMIT 20;
161 --enable_result_log
162
163 #
164 # Total and average wait times for different events for a session
165 #
166 --disable_result_log
167 SELECT h.EVENT_NAME, SUM(h.TIMER_WAIT) TOTAL_WAIT
168 FROM performance_schema.events_waits_history_long h
169 INNER JOIN performance_schema.threads p USING (THREAD_ID)
170 WHERE p.PROCESSLIST_ID = 1
171 GROUP BY h.EVENT_NAME
172 HAVING TOTAL_WAIT > 0;
173 --enable_result_log
174
175 #
176 # Which user reads and writes data
177 #
178
179 --disable_result_log
180 SELECT p.processlist_user, h.operation, SUM(NUMBER_OF_BYTES) bytes
181 FROM performance_schema.events_waits_history_long h
182 INNER JOIN performance_schema.threads p USING (THREAD_ID)
183 GROUP BY p.processlist_user, h.operation
184 HAVING BYTES > 0
185 ORDER BY p.processlist_user, h.operation;
186 --enable_result_log
187
188 # Clean-up.
189 UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES';
190
191 # In case of failure, will indicate the root cause
192 show global status like "performance_schema_file_%_lost";
193