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
|
#########
# SETUP #
#########
INSTALL COMPONENT 'file://component_pfs_example_component_population';;
show tables in performance_schema
where Tables_in_performance_schema like "%pfs_example%";
Tables_in_performance_schema
pfs_example_continent
pfs_example_country
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_continent BASE TABLE PERFORMANCE_SCHEMA
performance_schema pfs_example_country BASE TABLE PERFORMANCE_SCHEMA
##############
# Operations #
##############
-------------------------------------------------------------
Displaying records inserted from component code itself
-------------------------------------------------------------
select * from performance_schema.pfs_example_continent;
NAME
bar1
bar2
select * from performance_schema.pfs_example_country;
NAME CONTINENT YEAR POPULATION GROWTH_FACTOR COUNTRY_CODE
foo1 bar1 2016 10000 1.11 FO1
foo2 bar2 2016 1000 2.22 FO2
----------------------------------
Inserting rows in component tables
----------------------------------
insert into performance_schema.pfs_example_continent
values ("bar3");
ERROR 42000: INSERT command denied to user 'root'@'localhost' for table 'pfs_example_continent'
insert into performance_schema.pfs_example_country
values ("foo3", "bar3", 2016, 100, 3.33, "FO3");
select * from performance_schema.pfs_example_continent;
NAME
bar1
bar2
select * from performance_schema.pfs_example_country;
NAME CONTINENT YEAR POPULATION GROWTH_FACTOR COUNTRY_CODE
foo1 bar1 2016 10000 1.11 FO1
foo2 bar2 2016 1000 2.22 FO2
foo3 bar3 2016 100 3.33 FO3
--------------------------------------
Fetching records from component tables
--------------------------------------
select performance_schema.pfs_example_continent.NAME as CONTINENT_NAME,
performance_schema.pfs_example_country.NAME as COUNTRY_NAME,
YEAR, POPULATION, GROWTH_FACTOR
from performance_schema.pfs_example_continent,
performance_schema.pfs_example_country
where pfs_example_country.CONTINENT=pfs_example_continent.NAME;
CONTINENT_NAME COUNTRY_NAME YEAR POPULATION GROWTH_FACTOR
bar1 foo1 2016 10000 1.11
bar2 foo2 2016 1000 2.22
---------------------------------
Updating rows in component tables
---------------------------------
update performance_schema.pfs_example_continent
set NAME="bar3"
where NAME="bar1";
ERROR 42000: UPDATE command denied to user 'root'@'localhost' for table 'pfs_example_continent'
update performance_schema.pfs_example_country
set GROWTH_FACTOR=8.88
where NAME="foo1";
select * from performance_schema.pfs_example_continent;
NAME
bar1
bar2
select * from performance_schema.pfs_example_country;
NAME CONTINENT YEAR POPULATION GROWTH_FACTOR COUNTRY_CODE
foo1 bar1 2016 10000 8.88 FO1
foo2 bar2 2016 1000 2.22 FO2
foo3 bar3 2016 100 3.33 FO3
--------------------------------------
Fetching records from component tables
--------------------------------------
select performance_schema.pfs_example_continent.NAME as CONTINENT_NAME,
performance_schema.pfs_example_country.NAME as COUNTRY_NAME,
YEAR, POPULATION, GROWTH_FACTOR
from performance_schema.pfs_example_continent,
performance_schema.pfs_example_country
where pfs_example_country.CONTINENT=pfs_example_continent.NAME;
CONTINENT_NAME COUNTRY_NAME YEAR POPULATION GROWTH_FACTOR
bar1 foo1 2016 10000 8.88
bar2 foo2 2016 1000 2.22
-----------------------------------
Verify indexes usage
-----------------------------------
explain select * from performance_schema.pfs_example_continent
where NAME="bar1";
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE pfs_example_continent NULL const PRIMARY PRIMARY 80 const 1 100.00 NULL
-------------------------------------------------
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_country", 'YES', 'YES');
select * from performance_schema.pfs_example_country;
NAME CONTINENT YEAR POPULATION GROWTH_FACTOR COUNTRY_CODE
foo1 bar1 2016 10000 8.88 FO1
foo2 bar2 2016 1000 2.22 FO2
foo3 bar3 2016 100 3.33 FO3
truncate table performance_schema.table_io_waits_summary_by_table;
truncate table performance_schema.table_io_waits_summary_by_index_usage;
---------- 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_country";
COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
0 0 0 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_country";
INDEX_NAME COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
NAME 0 0 0 0 0
NULL 0 0 0 0 0
---------- insert query ----------
insert into performance_schema.pfs_example_country
values ("foo4", "bar4", 2016, 100, 4.44, "FO4");
---------- 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_country";
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_country";
INDEX_NAME COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
NAME 0 0 0 0 0
NULL 1 0 1 0 0
---------- update query with NAME ----------
update performance_schema.pfs_example_country
set GROWTH_FACTOR=5.55
where NAME="foo4" and CONTINENT="bar4";
---------- 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_country";
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_country";
INDEX_NAME COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
NAME 2 1 0 1 0
NULL 1 0 1 0 0
---------- update query with NAME ----------
update performance_schema.pfs_example_country
set GROWTH_FACTOR=6.66
where NAME="foo4" and CONTINENT="bar4";
---------- 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_country";
COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
5 2 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_country";
INDEX_NAME COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
NAME 4 2 0 2 0
NULL 1 0 1 0 0
---------- delete query ----------
delete from performance_schema.pfs_example_country
where NAME="foo4" and CONTINENT="bar4";
---------- 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_country";
COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
7 3 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_country";
INDEX_NAME COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
NAME 6 3 0 2 1
NULL 1 0 1 0 0
delete from performance_schema.setup_objects
where OBJECT_NAME="pfs_example_country";
---------- Index fetch with partial key ----------
INSERT into performance_schema.pfs_example_country values
("Home", "Europe", 2022, 1, 1, "HO1"),
("Home", "America", 2022, 2, 2, "HO2"),
("Canada", "America", 2022, 3, 3, "CAN");
SELECT * FROM performance_schema.pfs_example_country
WHERE NAME = 'nowhere';
NAME CONTINENT YEAR POPULATION GROWTH_FACTOR COUNTRY_CODE
SELECT * FROM performance_schema.pfs_example_country
FORCE INDEX(name)
WHERE NAME = 'nowhere';
NAME CONTINENT YEAR POPULATION GROWTH_FACTOR COUNTRY_CODE
SELECT * FROM performance_schema.pfs_example_country
WHERE NAME = 'Canada';
NAME CONTINENT YEAR POPULATION GROWTH_FACTOR COUNTRY_CODE
Canada America 2022 3 3.00 CAN
SELECT * FROM performance_schema.pfs_example_country
FORCE INDEX(name)
WHERE NAME = 'Canada';
NAME CONTINENT YEAR POPULATION GROWTH_FACTOR COUNTRY_CODE
Canada America 2022 3 3.00 CAN
SELECT * FROM performance_schema.pfs_example_country
WHERE NAME = 'Home'
ORDER BY CONTINENT;
NAME CONTINENT YEAR POPULATION GROWTH_FACTOR COUNTRY_CODE
Home America 2022 2 2.00 HO2
Home Europe 2022 1 1.00 HO1
SELECT * FROM performance_schema.pfs_example_country
FORCE INDEX(name)
WHERE NAME = 'Home'
ORDER BY CONTINENT;
NAME CONTINENT YEAR POPULATION GROWTH_FACTOR COUNTRY_CODE
Home America 2022 2 2.00 HO2
Home Europe 2022 1 1.00 HO1
-------------------------------------------------------
Fetching from component table after component uninstall
-------------------------------------------------------
UNINSTALL COMPONENT 'file://component_pfs_example_component_population';;
select * from performance_schema.pfs_example_continent;
ERROR 42S02: Table 'performance_schema.pfs_example_continent' doesn't exist
select * from performance_schema.pfs_example_country;
ERROR 42S02: Table 'performance_schema.pfs_example_country' doesn't exist
show tables in performance_schema
where Tables_in_performance_schema like "%pfs_example%";
Tables_in_performance_schema
###########
# CLEANUP #
###########
pfs_example_component_population init:
Passed add_tables()
End of init
pfs_example_component_population_deinit:
Passed delete_tables()
End of deinit
|
