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
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
|
#########
# SETUP #
#########
INSTALL PLUGIN pfs_example_plugin_employee SONAME 'pfs_example_plugin_employee.xxx';
show tables in performance_schema
where Tables_in_performance_schema like "%pfs_example%";
Tables_in_performance_schema
pfs_example_employee_name
pfs_example_employee_salary
pfs_example_machine
pfs_example_machine_by_employee_by_type
select TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE
from information_schema.tables
where TABLE_NAME like "pfs_example%" AND
TABLE_SCHEMA= "performance_schema";
TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE
performance_schema pfs_example_employee_name BASE TABLE PERFORMANCE_SCHEMA
performance_schema pfs_example_employee_salary BASE TABLE PERFORMANCE_SCHEMA
performance_schema pfs_example_machine BASE TABLE PERFORMANCE_SCHEMA
performance_schema pfs_example_machine_by_employee_by_type BASE TABLE PERFORMANCE_SCHEMA
##############
# Operations #
##############
-------------------------------------------------------------
Displaying records inserted from plugin/component code itself
-------------------------------------------------------------
select * from performance_schema.pfs_example_employee_name;
EMPLOYEE_NUMBER FIRST_NAME LAST_NAME
1 foo1 bar1
2 foo2 bar2
3 foo3 bar3
select * from performance_schema.pfs_example_employee_salary;
EMPLOYEE_NUMBER EMPLOYEE_SALARY DATE_OF_BIRTH TIME_OF_BIRTH
1 1000 2013-11-12 12:02:34
2 2000 2016-02-29 12:12:30
3 3000 2017-03-24 11:12:50
select * from performance_schema.pfs_example_machine;
MACHINE_SL_NUMBER MACHINE_TYPE MACHINE_MADE EMPLOYEE_NUMBER
1 DESKTOP Lenovo 1
2 LAPTOP Dell 2
3 MOBILE Apple 1
4 MOBILE Samsung 1
5 LAPTOP Lenovo 2
6 MOBILE Nokia 2
7 LAPTOP Apple 1
8 LAPTOP HP 3
9 DESKTOP Apple 3
select * from performance_schema.pfs_example_machine_by_employee_by_type;
FIRST_NAME LAST_NAME MACHINE_TYPE COUNT
foo1 bar1 LAPTOP 1
foo1 bar1 DESKTOP 1
foo1 bar1 MOBILE 2
foo2 bar2 LAPTOP 2
foo2 bar2 MOBILE 1
foo3 bar3 LAPTOP 1
foo3 bar3 DESKTOP 1
-------------------------------
Inserting rows in plugin tables
-------------------------------
insert into performance_schema.pfs_example_employee_name
values (4, "foo4", "bar4");
insert into performance_schema.pfs_example_employee_salary
values (4, 4000, "2013-12-12", "10:10:10");
insert into performance_schema.pfs_example_machine
values (10, 'LAPTOP', "IBM", 4);
insert into performance_schema.pfs_example_employee_salary
values (NULL,1000000000,NULL,NULL);
insert into performance_schema.pfs_example_employee_salary (employee_salary,date_of_birth)
values (10000000,'1988-04-17');
insert into performance_schema.pfs_example_machine_by_employee_by_type
values ("a", "b", LAPTOP, 101);
ERROR 42000: INSERT command denied to user 'root'@'localhost' for table 'pfs_example_machine_by_employee_by_type'
select * from performance_schema.pfs_example_employee_name;
EMPLOYEE_NUMBER FIRST_NAME LAST_NAME
1 foo1 bar1
2 foo2 bar2
3 foo3 bar3
4 foo4 bar4
select * from performance_schema.pfs_example_employee_salary;
EMPLOYEE_NUMBER EMPLOYEE_SALARY DATE_OF_BIRTH TIME_OF_BIRTH
1 1000 2013-11-12 12:02:34
2 2000 2016-02-29 12:12:30
3 3000 2017-03-24 11:12:50
4 4000 2013-12-12 10:10:10
NULL 1000000000 NULL NULL
NULL 10000000 1988-04-17 NULL
select * from performance_schema.pfs_example_machine;
MACHINE_SL_NUMBER MACHINE_TYPE MACHINE_MADE EMPLOYEE_NUMBER
1 DESKTOP Lenovo 1
2 LAPTOP Dell 2
3 MOBILE Apple 1
4 MOBILE Samsung 1
5 LAPTOP Lenovo 2
6 MOBILE Nokia 2
7 LAPTOP Apple 1
8 LAPTOP HP 3
9 DESKTOP Apple 3
10 LAPTOP IBM 4
select * from performance_schema.pfs_example_machine_by_employee_by_type;
FIRST_NAME LAST_NAME MACHINE_TYPE COUNT
foo1 bar1 LAPTOP 1
foo1 bar1 DESKTOP 1
foo1 bar1 MOBILE 2
foo2 bar2 LAPTOP 2
foo2 bar2 MOBILE 1
foo3 bar3 LAPTOP 1
foo3 bar3 DESKTOP 1
foo4 bar4 LAPTOP 1
-----------------------------------
Fetching records from plugin tables
-----------------------------------
select FIRST_NAME, LAST_NAME, EMPLOYEE_SALARY
from performance_schema.pfs_example_employee_name,
performance_schema.pfs_example_employee_salary
where performance_schema.pfs_example_employee_name.EMPLOYEE_NUMBER=
performance_schema.pfs_example_employee_salary.EMPLOYEE_NUMBER;
FIRST_NAME LAST_NAME EMPLOYEE_SALARY
foo1 bar1 1000
foo2 bar2 2000
foo3 bar3 3000
foo4 bar4 4000
------------------------------
Updating rows in plugin tables
------------------------------
update performance_schema.pfs_example_employee_name
set FIRST_NAME="foo42", LAST_NAME="bar42"
where LAST_NAME="bar4";
update performance_schema.pfs_example_employee_salary
set EMPLOYEE_SALARY=8000
where EMPLOYEE_NUMBER=2;
update performance_schema.pfs_example_machine
set MACHINE_TYPE = 'DESKTOP'
where MACHINE_SL_NUMBER=10;
update performance_schema.pfs_example_machine_by_employee_by_type
set count=0
where MACHINE_SL_NUMBER=10;
ERROR 42000: UPDATE command denied to user 'root'@'localhost' for table 'pfs_example_machine_by_employee_by_type'
select * from performance_schema.pfs_example_employee_name;
EMPLOYEE_NUMBER FIRST_NAME LAST_NAME
1 foo1 bar1
2 foo2 bar2
3 foo3 bar3
4 foo42 bar42
select * from performance_schema.pfs_example_employee_salary;
EMPLOYEE_NUMBER EMPLOYEE_SALARY DATE_OF_BIRTH TIME_OF_BIRTH
1 1000 2013-11-12 12:02:34
2 8000 2016-02-29 12:12:30
3 3000 2017-03-24 11:12:50
4 4000 2013-12-12 10:10:10
NULL 1000000000 NULL NULL
NULL 10000000 1988-04-17 NULL
select * from performance_schema.pfs_example_machine;
MACHINE_SL_NUMBER MACHINE_TYPE MACHINE_MADE EMPLOYEE_NUMBER
1 DESKTOP Lenovo 1
2 LAPTOP Dell 2
3 MOBILE Apple 1
4 MOBILE Samsung 1
5 LAPTOP Lenovo 2
6 MOBILE Nokia 2
7 LAPTOP Apple 1
8 LAPTOP HP 3
9 DESKTOP Apple 3
10 DESKTOP IBM 4
select * from performance_schema.pfs_example_machine_by_employee_by_type;
FIRST_NAME LAST_NAME MACHINE_TYPE COUNT
foo1 bar1 LAPTOP 1
foo1 bar1 DESKTOP 1
foo1 bar1 MOBILE 2
foo2 bar2 LAPTOP 2
foo2 bar2 MOBILE 1
foo3 bar3 LAPTOP 1
foo3 bar3 DESKTOP 1
foo42 bar42 DESKTOP 1
-----------------------------------
Fetching records from plugin tables
-----------------------------------
select FIRST_NAME, LAST_NAME, EMPLOYEE_SALARY
from performance_schema.pfs_example_employee_name,
performance_schema.pfs_example_employee_salary
where performance_schema.pfs_example_employee_name.EMPLOYEE_NUMBER=
performance_schema.pfs_example_employee_salary.EMPLOYEE_NUMBER;
FIRST_NAME LAST_NAME EMPLOYEE_SALARY
foo1 bar1 1000
foo2 bar2 8000
foo3 bar3 3000
foo42 bar42 4000
-----------------------------------
Verify indexes usage
-----------------------------------
explain select * from performance_schema.pfs_example_employee_name
where EMPLOYEE_NUMBER=2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE pfs_example_employee_name NULL const PRIMARY PRIMARY 4 const 1 100.00 NULL
insert into performance_schema.pfs_example_employee_name values
(5,"foo","bar"), (6,"foofoo","barbar")
, (7,"foo","bar"), (8,"foofoo","barbar")
, (9,"foo","bar"), (10,"foofoo","barbar")
, (11,"foo","bar"), (12,"foofoo","barbar")
, (13,"foo","bar"), (14,"foofoo","barbar")
, (15,"foo","bar"), (16,"foofoo","barbar")
, (17,"foo","bar"), (18,"foofoo","barbar")
, (19,"foo","bar"), (20,"foofoo","barbar")
, (21,"foo","bar"), (22,"foofoo","barbar")
, (23,"foo","bar"), (24,"foofoo","barbar")
, (25,"foo","bar"), (26,"foofoo","barbar")
, (27,"foo","bar"), (28,"foofoo","barbar")
, (29,"foo","bar"), (30,"foofoo","barbar")
, (31,"foo","bar"), (32,"foofoo","barbar")
, (33,"foo","bar"), (34,"foofoo","barbar")
, (35,"foo","bar"), (36,"foofoo","barbar")
, (37,"foo","bar"), (38,"foofoo","barbar")
, (39,"foo","bar"), (40,"foofoo","barbar")
, (41,"foo","bar"), (42,"foofoo","barbar")
, (43,"foo","bar"), (44,"foofoo","barbar")
, (45,"foo","bar"), (46,"foofoo","barbar")
, (47,"foo","bar"), (48,"foofoo","barbar")
, (49,"foo","bar"), (50,"foofoo","barbar")
, (51,"foo","bar"), (52,"foofoo","barbar")
, (53,"foo","bar"), (54,"foofoo","barbar")
, (55,"foo","bar"), (56,"foofoo","barbar")
, (57,"foo","bar"), (58,"foofoo","barbar")
, (59,"foo","bar"), (60,"foofoo","barbar");
explain select * from performance_schema.pfs_example_employee_name
where FIRST_NAME='foo';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE pfs_example_employee_name NULL ref FIRST_NAME FIRST_NAME 81 const 10 100.00 Using where
-------------------------------------------------
Verify stats collection in PFS stats tables
-------------------------------------------------
update performance_schema.setup_consumers set ENABLED='YES';
update performance_schema.setup_instruments set ENABLED='YES', TIMED='YES';
insert into performance_schema.setup_objects values
('TABLE', "performance_schema", "pfs_example_employee_name", 'YES', 'YES');
truncate table performance_schema.table_io_waits_summary_by_table;
truncate table performance_schema.table_io_waits_summary_by_index_usage;
---------- insert query ----------
insert into performance_schema.pfs_example_employee_name values
(61, "foo5", "bar5");
---------- table I/O stat by table name ----------
select COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
from performance_schema.table_io_waits_summary_by_table
where OBJECT_NAME="pfs_example_employee_name";
COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
1 0 1 0 0
---------- table I/O stat by index usage ----------
select INDEX_NAME, COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
from performance_schema.table_io_waits_summary_by_index_usage
where OBJECT_NAME="pfs_example_employee_name";
INDEX_NAME COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
PRIMARY 0 0 0 0 0
FIRST_NAME 0 0 0 0 0
NULL 1 0 1 0 0
---------- update query with EMPLOYEE_NUMBER ----------
update performance_schema.pfs_example_employee_name set LAST_NAME="bar55"
where EMPLOYEE_NUMBER=61;
---------- table I/O stat by table name ----------
select COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
from performance_schema.table_io_waits_summary_by_table
where OBJECT_NAME="pfs_example_employee_name";
COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
3 1 1 1 0
---------- table I/O stat by index usage ----------
select INDEX_NAME, COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
from performance_schema.table_io_waits_summary_by_index_usage
where OBJECT_NAME="pfs_example_employee_name";
INDEX_NAME COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
PRIMARY 2 1 0 1 0
FIRST_NAME 0 0 0 0 0
NULL 1 0 1 0 0
---------- update query with FIRST_NAME ----------
update performance_schema.pfs_example_employee_name set LAST_NAME="bar56"
where FIRST_NAME="foo5";
---------- table I/O stat by table name ----------
select COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
from performance_schema.table_io_waits_summary_by_table
where OBJECT_NAME="pfs_example_employee_name";
COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
6 3 1 2 0
---------- table I/O stat by index usage ----------
select INDEX_NAME, COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
from performance_schema.table_io_waits_summary_by_index_usage
where OBJECT_NAME="pfs_example_employee_name";
INDEX_NAME COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
PRIMARY 2 1 0 1 0
FIRST_NAME 3 2 0 1 0
NULL 1 0 1 0 0
---------- delete query ----------
delete from performance_schema.pfs_example_employee_name
where EMPLOYEE_NUMBER=61;
---------- table I/O stat by table name ----------
select COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
from performance_schema.table_io_waits_summary_by_table
where OBJECT_NAME="pfs_example_employee_name";
COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
8 4 1 2 1
---------- table I/O stat by index usage ----------
select INDEX_NAME, COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
from performance_schema.table_io_waits_summary_by_index_usage
where OBJECT_NAME="pfs_example_employee_name";
INDEX_NAME COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
PRIMARY 4 2 0 1 1
FIRST_NAME 3 2 0 1 0
NULL 1 0 1 0 0
delete from performance_schema.setup_objects
where OBJECT_NAME="pfs_example_employee_name";
-------------------------------------------------
Fetching from plugin table after plugin uninstall
-------------------------------------------------
UNINSTALL PLUGIN pfs_example_plugin_employee;
select * from performance_schema.pfs_example_employee_name;
ERROR 42S02: Table 'performance_schema.pfs_example_employee_name' doesn't exist
select * from performance_schema.pfs_example_employee_salary;
ERROR 42S02: Table 'performance_schema.pfs_example_employee_salary' doesn't exist
show tables in performance_schema
where Tables_in_performance_schema like "%pfs_example%";
Tables_in_performance_schema
###########
# CLEANUP #
###########
|
