Debian Codegraph @

mysql-test/suite/perfschema/r/table_plugin.result

raw
1 #########
2 # SETUP #
3 #########
4 INSTALL PLUGIN pfs_example_plugin_employee SONAME 'pfs_example_plugin_employee.xxx';
5 show tables in performance_schema
6 where Tables_in_performance_schema like "%pfs_example%";
7 Tables_in_performance_schema
8 pfs_example_employee_name
9 pfs_example_employee_salary
10 pfs_example_machine
11 pfs_example_machine_by_employee_by_type
12 select TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE
13 from information_schema.tables
14 where TABLE_NAME like "pfs_example%" AND
15 TABLE_SCHEMA= "performance_schema";
16 TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE
17 performance_schema pfs_example_employee_name BASE TABLE PERFORMANCE_SCHEMA
18 performance_schema pfs_example_employee_salary BASE TABLE PERFORMANCE_SCHEMA
19 performance_schema pfs_example_machine BASE TABLE PERFORMANCE_SCHEMA
20 performance_schema pfs_example_machine_by_employee_by_type BASE TABLE PERFORMANCE_SCHEMA
21 ##############
22 # Operations #
23 ##############
24 -------------------------------------------------------------
25 Displaying records inserted from plugin/component code itself
26 -------------------------------------------------------------
27 select * from performance_schema.pfs_example_employee_name;
28 EMPLOYEE_NUMBER FIRST_NAME LAST_NAME
29 1 foo1 bar1
30 2 foo2 bar2
31 3 foo3 bar3
32 select * from performance_schema.pfs_example_employee_salary;
33 EMPLOYEE_NUMBER EMPLOYEE_SALARY DATE_OF_BIRTH TIME_OF_BIRTH
34 1 1000 2013-11-12 12:02:34
35 2 2000 2016-02-29 12:12:30
36 3 3000 2017-03-24 11:12:50
37 select * from performance_schema.pfs_example_machine;
38 MACHINE_SL_NUMBER MACHINE_TYPE MACHINE_MADE EMPLOYEE_NUMBER
39 1 DESKTOP Lenovo 1
40 2 LAPTOP Dell 2
41 3 MOBILE Apple 1
42 4 MOBILE Samsung 1
43 5 LAPTOP Lenovo 2
44 6 MOBILE Nokia 2
45 7 LAPTOP Apple 1
46 8 LAPTOP HP 3
47 9 DESKTOP Apple 3
48 select * from performance_schema.pfs_example_machine_by_employee_by_type;
49 FIRST_NAME LAST_NAME MACHINE_TYPE COUNT
50 foo1 bar1 LAPTOP 1
51 foo1 bar1 DESKTOP 1
52 foo1 bar1 MOBILE 2
53 foo2 bar2 LAPTOP 2
54 foo2 bar2 MOBILE 1
55 foo3 bar3 LAPTOP 1
56 foo3 bar3 DESKTOP 1
57 -------------------------------
58 Inserting rows in plugin tables
59 -------------------------------
60 insert into performance_schema.pfs_example_employee_name
61 values (4, "foo4", "bar4");
62 insert into performance_schema.pfs_example_employee_salary
63 values (4, 4000, "2013-12-12", "10:10:10");
64 insert into performance_schema.pfs_example_machine
65 values (10, 'LAPTOP', "IBM", 4);
66 insert into performance_schema.pfs_example_employee_salary
67 values (NULL,1000000000,NULL,NULL);
68 insert into performance_schema.pfs_example_employee_salary (employee_salary,date_of_birth)
69 values (10000000,'1988-04-17');
70 insert into performance_schema.pfs_example_machine_by_employee_by_type
71 values ("a", "b", LAPTOP, 101);
72 ERROR 42000: INSERT command denied to user 'root'@'localhost' for table 'pfs_example_machine_by_employee_by_type'
73 select * from performance_schema.pfs_example_employee_name;
74 EMPLOYEE_NUMBER FIRST_NAME LAST_NAME
75 1 foo1 bar1
76 2 foo2 bar2
77 3 foo3 bar3
78 4 foo4 bar4
79 select * from performance_schema.pfs_example_employee_salary;
80 EMPLOYEE_NUMBER EMPLOYEE_SALARY DATE_OF_BIRTH TIME_OF_BIRTH
81 1 1000 2013-11-12 12:02:34
82 2 2000 2016-02-29 12:12:30
83 3 3000 2017-03-24 11:12:50
84 4 4000 2013-12-12 10:10:10
85 NULL 1000000000 NULL NULL
86 NULL 10000000 1988-04-17 NULL
87 select * from performance_schema.pfs_example_machine;
88 MACHINE_SL_NUMBER MACHINE_TYPE MACHINE_MADE EMPLOYEE_NUMBER
89 1 DESKTOP Lenovo 1
90 2 LAPTOP Dell 2
91 3 MOBILE Apple 1
92 4 MOBILE Samsung 1
93 5 LAPTOP Lenovo 2
94 6 MOBILE Nokia 2
95 7 LAPTOP Apple 1
96 8 LAPTOP HP 3
97 9 DESKTOP Apple 3
98 10 LAPTOP IBM 4
99 select * from performance_schema.pfs_example_machine_by_employee_by_type;
100 FIRST_NAME LAST_NAME MACHINE_TYPE COUNT
101 foo1 bar1 LAPTOP 1
102 foo1 bar1 DESKTOP 1
103 foo1 bar1 MOBILE 2
104 foo2 bar2 LAPTOP 2
105 foo2 bar2 MOBILE 1
106 foo3 bar3 LAPTOP 1
107 foo3 bar3 DESKTOP 1
108 foo4 bar4 LAPTOP 1
109 -----------------------------------
110 Fetching records from plugin tables
111 -----------------------------------
112 select FIRST_NAME, LAST_NAME, EMPLOYEE_SALARY
113 from performance_schema.pfs_example_employee_name,
114 performance_schema.pfs_example_employee_salary
115 where performance_schema.pfs_example_employee_name.EMPLOYEE_NUMBER=
116 performance_schema.pfs_example_employee_salary.EMPLOYEE_NUMBER;
117 FIRST_NAME LAST_NAME EMPLOYEE_SALARY
118 foo1 bar1 1000
119 foo2 bar2 2000
120 foo3 bar3 3000
121 foo4 bar4 4000
122 ------------------------------
123 Updating rows in plugin tables
124 ------------------------------
125 update performance_schema.pfs_example_employee_name
126 set FIRST_NAME="foo42", LAST_NAME="bar42"
127 where LAST_NAME="bar4";
128 update performance_schema.pfs_example_employee_salary
129 set EMPLOYEE_SALARY=8000
130 where EMPLOYEE_NUMBER=2;
131 update performance_schema.pfs_example_machine
132 set MACHINE_TYPE = 'DESKTOP'
133 where MACHINE_SL_NUMBER=10;
134 update performance_schema.pfs_example_machine_by_employee_by_type
135 set count=0
136 where MACHINE_SL_NUMBER=10;
137 ERROR 42000: UPDATE command denied to user 'root'@'localhost' for table 'pfs_example_machine_by_employee_by_type'
138 select * from performance_schema.pfs_example_employee_name;
139 EMPLOYEE_NUMBER FIRST_NAME LAST_NAME
140 1 foo1 bar1
141 2 foo2 bar2
142 3 foo3 bar3
143 4 foo42 bar42
144 select * from performance_schema.pfs_example_employee_salary;
145 EMPLOYEE_NUMBER EMPLOYEE_SALARY DATE_OF_BIRTH TIME_OF_BIRTH
146 1 1000 2013-11-12 12:02:34
147 2 8000 2016-02-29 12:12:30
148 3 3000 2017-03-24 11:12:50
149 4 4000 2013-12-12 10:10:10
150 NULL 1000000000 NULL NULL
151 NULL 10000000 1988-04-17 NULL
152 select * from performance_schema.pfs_example_machine;
153 MACHINE_SL_NUMBER MACHINE_TYPE MACHINE_MADE EMPLOYEE_NUMBER
154 1 DESKTOP Lenovo 1
155 2 LAPTOP Dell 2
156 3 MOBILE Apple 1
157 4 MOBILE Samsung 1
158 5 LAPTOP Lenovo 2
159 6 MOBILE Nokia 2
160 7 LAPTOP Apple 1
161 8 LAPTOP HP 3
162 9 DESKTOP Apple 3
163 10 DESKTOP IBM 4
164 select * from performance_schema.pfs_example_machine_by_employee_by_type;
165 FIRST_NAME LAST_NAME MACHINE_TYPE COUNT
166 foo1 bar1 LAPTOP 1
167 foo1 bar1 DESKTOP 1
168 foo1 bar1 MOBILE 2
169 foo2 bar2 LAPTOP 2
170 foo2 bar2 MOBILE 1
171 foo3 bar3 LAPTOP 1
172 foo3 bar3 DESKTOP 1
173 foo42 bar42 DESKTOP 1
174 -----------------------------------
175 Fetching records from plugin tables
176 -----------------------------------
177 select FIRST_NAME, LAST_NAME, EMPLOYEE_SALARY
178 from performance_schema.pfs_example_employee_name,
179 performance_schema.pfs_example_employee_salary
180 where performance_schema.pfs_example_employee_name.EMPLOYEE_NUMBER=
181 performance_schema.pfs_example_employee_salary.EMPLOYEE_NUMBER;
182 FIRST_NAME LAST_NAME EMPLOYEE_SALARY
183 foo1 bar1 1000
184 foo2 bar2 8000
185 foo3 bar3 3000
186 foo42 bar42 4000
187 -----------------------------------
188 Verify indexes usage
189 -----------------------------------
190 explain select * from performance_schema.pfs_example_employee_name
191 where EMPLOYEE_NUMBER=2;
192 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
193 1 SIMPLE pfs_example_employee_name NULL const PRIMARY PRIMARY 4 const 1 100.00 NULL
194 insert into performance_schema.pfs_example_employee_name values
195 (5,"foo","bar"), (6,"foofoo","barbar")
196 , (7,"foo","bar"), (8,"foofoo","barbar")
197 , (9,"foo","bar"), (10,"foofoo","barbar")
198 , (11,"foo","bar"), (12,"foofoo","barbar")
199 , (13,"foo","bar"), (14,"foofoo","barbar")
200 , (15,"foo","bar"), (16,"foofoo","barbar")
201 , (17,"foo","bar"), (18,"foofoo","barbar")
202 , (19,"foo","bar"), (20,"foofoo","barbar")
203 , (21,"foo","bar"), (22,"foofoo","barbar")
204 , (23,"foo","bar"), (24,"foofoo","barbar")
205 , (25,"foo","bar"), (26,"foofoo","barbar")
206 , (27,"foo","bar"), (28,"foofoo","barbar")
207 , (29,"foo","bar"), (30,"foofoo","barbar")
208 , (31,"foo","bar"), (32,"foofoo","barbar")
209 , (33,"foo","bar"), (34,"foofoo","barbar")
210 , (35,"foo","bar"), (36,"foofoo","barbar")
211 , (37,"foo","bar"), (38,"foofoo","barbar")
212 , (39,"foo","bar"), (40,"foofoo","barbar")
213 , (41,"foo","bar"), (42,"foofoo","barbar")
214 , (43,"foo","bar"), (44,"foofoo","barbar")
215 , (45,"foo","bar"), (46,"foofoo","barbar")
216 , (47,"foo","bar"), (48,"foofoo","barbar")
217 , (49,"foo","bar"), (50,"foofoo","barbar")
218 , (51,"foo","bar"), (52,"foofoo","barbar")
219 , (53,"foo","bar"), (54,"foofoo","barbar")
220 , (55,"foo","bar"), (56,"foofoo","barbar")
221 , (57,"foo","bar"), (58,"foofoo","barbar")
222 , (59,"foo","bar"), (60,"foofoo","barbar");
223 explain select * from performance_schema.pfs_example_employee_name
224 where FIRST_NAME='foo';
225 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
226 1 SIMPLE pfs_example_employee_name NULL ref FIRST_NAME FIRST_NAME 81 const 10 100.00 Using where
227 -------------------------------------------------
228 Verify stats collection in PFS stats tables
229 -------------------------------------------------
230 update performance_schema.setup_consumers set ENABLED='YES';
231 update performance_schema.setup_instruments set ENABLED='YES', TIMED='YES';
232 insert into performance_schema.setup_objects values
233 ('TABLE', "performance_schema", "pfs_example_employee_name", 'YES', 'YES');
234 truncate table performance_schema.table_io_waits_summary_by_table;
235 truncate table performance_schema.table_io_waits_summary_by_index_usage;
236 ---------- insert query ----------
237 insert into performance_schema.pfs_example_employee_name values
238 (61, "foo5", "bar5");
239 ---------- table I/O stat by table name ----------
240 select COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
241 from performance_schema.table_io_waits_summary_by_table
242 where OBJECT_NAME="pfs_example_employee_name";
243 COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
244 1 0 1 0 0
245 ---------- table I/O stat by index usage ----------
246 select INDEX_NAME, COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
247 from performance_schema.table_io_waits_summary_by_index_usage
248 where OBJECT_NAME="pfs_example_employee_name";
249 INDEX_NAME COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
250 PRIMARY 0 0 0 0 0
251 FIRST_NAME 0 0 0 0 0
252 NULL 1 0 1 0 0
253 ---------- update query with EMPLOYEE_NUMBER ----------
254 update performance_schema.pfs_example_employee_name set LAST_NAME="bar55"
255 where EMPLOYEE_NUMBER=61;
256 ---------- table I/O stat by table name ----------
257 select COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
258 from performance_schema.table_io_waits_summary_by_table
259 where OBJECT_NAME="pfs_example_employee_name";
260 COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
261 3 1 1 1 0
262 ---------- table I/O stat by index usage ----------
263 select INDEX_NAME, COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
264 from performance_schema.table_io_waits_summary_by_index_usage
265 where OBJECT_NAME="pfs_example_employee_name";
266 INDEX_NAME COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
267 PRIMARY 2 1 0 1 0
268 FIRST_NAME 0 0 0 0 0
269 NULL 1 0 1 0 0
270 ---------- update query with FIRST_NAME ----------
271 update performance_schema.pfs_example_employee_name set LAST_NAME="bar56"
272 where FIRST_NAME="foo5";
273 ---------- table I/O stat by table name ----------
274 select COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
275 from performance_schema.table_io_waits_summary_by_table
276 where OBJECT_NAME="pfs_example_employee_name";
277 COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
278 6 3 1 2 0
279 ---------- table I/O stat by index usage ----------
280 select INDEX_NAME, COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
281 from performance_schema.table_io_waits_summary_by_index_usage
282 where OBJECT_NAME="pfs_example_employee_name";
283 INDEX_NAME COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
284 PRIMARY 2 1 0 1 0
285 FIRST_NAME 3 2 0 1 0
286 NULL 1 0 1 0 0
287 ---------- delete query ----------
288 delete from performance_schema.pfs_example_employee_name
289 where EMPLOYEE_NUMBER=61;
290 ---------- table I/O stat by table name ----------
291 select COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
292 from performance_schema.table_io_waits_summary_by_table
293 where OBJECT_NAME="pfs_example_employee_name";
294 COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
295 8 4 1 2 1
296 ---------- table I/O stat by index usage ----------
297 select INDEX_NAME, COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
298 from performance_schema.table_io_waits_summary_by_index_usage
299 where OBJECT_NAME="pfs_example_employee_name";
300 INDEX_NAME COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
301 PRIMARY 4 2 0 1 1
302 FIRST_NAME 3 2 0 1 0
303 NULL 1 0 1 0 0
304 delete from performance_schema.setup_objects
305 where OBJECT_NAME="pfs_example_employee_name";
306 -------------------------------------------------
307 Fetching from plugin table after plugin uninstall
308 -------------------------------------------------
309 UNINSTALL PLUGIN pfs_example_plugin_employee;
310 select * from performance_schema.pfs_example_employee_name;
311 ERROR 42S02: Table 'performance_schema.pfs_example_employee_name' doesn't exist
312 select * from performance_schema.pfs_example_employee_salary;
313 ERROR 42S02: Table 'performance_schema.pfs_example_employee_salary' doesn't exist
314 show tables in performance_schema
315 where Tables_in_performance_schema like "%pfs_example%";
316 Tables_in_performance_schema
317 ###########
318 # CLEANUP #
319 ###########