Debian Codegraph @

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

raw
1 #########
2 # SETUP #
3 #########
4 INSTALL COMPONENT 'file://component_pfs_example_component_population';;
5 show tables in performance_schema
6 where Tables_in_performance_schema like "%pfs_example%";
7 Tables_in_performance_schema
8 pfs_example_continent
9 pfs_example_country
10 select TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE
11 from information_schema.tables
12 where TABLE_NAME like "pfs_example%" AND
13 TABLE_SCHEMA= "performance_schema";
14 TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE
15 performance_schema pfs_example_continent BASE TABLE PERFORMANCE_SCHEMA
16 performance_schema pfs_example_country BASE TABLE PERFORMANCE_SCHEMA
17 ##############
18 # Operations #
19 ##############
20 -------------------------------------------------------------
21 Displaying records inserted from component code itself
22 -------------------------------------------------------------
23 select * from performance_schema.pfs_example_continent;
24 NAME
25 bar1
26 bar2
27 select * from performance_schema.pfs_example_country;
28 NAME CONTINENT YEAR POPULATION GROWTH_FACTOR COUNTRY_CODE
29 foo1 bar1 2016 10000 1.11 FO1
30 foo2 bar2 2016 1000 2.22 FO2
31 ----------------------------------
32 Inserting rows in component tables
33 ----------------------------------
34 insert into performance_schema.pfs_example_continent
35 values ("bar3");
36 ERROR 42000: INSERT command denied to user 'root'@'localhost' for table 'pfs_example_continent'
37 insert into performance_schema.pfs_example_country
38 values ("foo3", "bar3", 2016, 100, 3.33, "FO3");
39 select * from performance_schema.pfs_example_continent;
40 NAME
41 bar1
42 bar2
43 select * from performance_schema.pfs_example_country;
44 NAME CONTINENT YEAR POPULATION GROWTH_FACTOR COUNTRY_CODE
45 foo1 bar1 2016 10000 1.11 FO1
46 foo2 bar2 2016 1000 2.22 FO2
47 foo3 bar3 2016 100 3.33 FO3
48 --------------------------------------
49 Fetching records from component tables
50 --------------------------------------
51 select performance_schema.pfs_example_continent.NAME as CONTINENT_NAME,
52 performance_schema.pfs_example_country.NAME as COUNTRY_NAME,
53 YEAR, POPULATION, GROWTH_FACTOR
54 from performance_schema.pfs_example_continent,
55 performance_schema.pfs_example_country
56 where pfs_example_country.CONTINENT=pfs_example_continent.NAME;
57 CONTINENT_NAME COUNTRY_NAME YEAR POPULATION GROWTH_FACTOR
58 bar1 foo1 2016 10000 1.11
59 bar2 foo2 2016 1000 2.22
60 ---------------------------------
61 Updating rows in component tables
62 ---------------------------------
63 update performance_schema.pfs_example_continent
64 set NAME="bar3"
65 where NAME="bar1";
66 ERROR 42000: UPDATE command denied to user 'root'@'localhost' for table 'pfs_example_continent'
67 update performance_schema.pfs_example_country
68 set GROWTH_FACTOR=8.88
69 where NAME="foo1";
70 select * from performance_schema.pfs_example_continent;
71 NAME
72 bar1
73 bar2
74 select * from performance_schema.pfs_example_country;
75 NAME CONTINENT YEAR POPULATION GROWTH_FACTOR COUNTRY_CODE
76 foo1 bar1 2016 10000 8.88 FO1
77 foo2 bar2 2016 1000 2.22 FO2
78 foo3 bar3 2016 100 3.33 FO3
79 --------------------------------------
80 Fetching records from component tables
81 --------------------------------------
82 select performance_schema.pfs_example_continent.NAME as CONTINENT_NAME,
83 performance_schema.pfs_example_country.NAME as COUNTRY_NAME,
84 YEAR, POPULATION, GROWTH_FACTOR
85 from performance_schema.pfs_example_continent,
86 performance_schema.pfs_example_country
87 where pfs_example_country.CONTINENT=pfs_example_continent.NAME;
88 CONTINENT_NAME COUNTRY_NAME YEAR POPULATION GROWTH_FACTOR
89 bar1 foo1 2016 10000 8.88
90 bar2 foo2 2016 1000 2.22
91 -----------------------------------
92 Verify indexes usage
93 -----------------------------------
94 explain select * from performance_schema.pfs_example_continent
95 where NAME="bar1";
96 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
97 1 SIMPLE pfs_example_continent NULL const PRIMARY PRIMARY 80 const 1 100.00 NULL
98 -------------------------------------------------
99 Verify stats collection in PFS stats tables
100 -------------------------------------------------
101 update performance_schema.setup_consumers set ENABLED='YES';
102 update performance_schema.setup_instruments set ENABLED='YES', TIMED='YES';
103 insert into performance_schema.setup_objects values
104 ('TABLE', "performance_schema", "pfs_example_country", 'YES', 'YES');
105 select * from performance_schema.pfs_example_country;
106 NAME CONTINENT YEAR POPULATION GROWTH_FACTOR COUNTRY_CODE
107 foo1 bar1 2016 10000 8.88 FO1
108 foo2 bar2 2016 1000 2.22 FO2
109 foo3 bar3 2016 100 3.33 FO3
110 truncate table performance_schema.table_io_waits_summary_by_table;
111 truncate table performance_schema.table_io_waits_summary_by_index_usage;
112 ---------- table I/O stat by table name ----------
113 select COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
114 from performance_schema.table_io_waits_summary_by_table
115 where OBJECT_NAME="pfs_example_country";
116 COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
117 0 0 0 0 0
118 ---------- table I/O stat by index usage ----------
119 select INDEX_NAME, COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
120 from performance_schema.table_io_waits_summary_by_index_usage
121 where OBJECT_NAME="pfs_example_country";
122 INDEX_NAME COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
123 NAME 0 0 0 0 0
124 NULL 0 0 0 0 0
125 ---------- insert query ----------
126 insert into performance_schema.pfs_example_country
127 values ("foo4", "bar4", 2016, 100, 4.44, "FO4");
128 ---------- table I/O stat by table name ----------
129 select COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
130 from performance_schema.table_io_waits_summary_by_table
131 where OBJECT_NAME="pfs_example_country";
132 COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
133 1 0 1 0 0
134 ---------- table I/O stat by index usage ----------
135 select INDEX_NAME, COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
136 from performance_schema.table_io_waits_summary_by_index_usage
137 where OBJECT_NAME="pfs_example_country";
138 INDEX_NAME COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
139 NAME 0 0 0 0 0
140 NULL 1 0 1 0 0
141 ---------- update query with NAME ----------
142 update performance_schema.pfs_example_country
143 set GROWTH_FACTOR=5.55
144 where NAME="foo4" and CONTINENT="bar4";
145 ---------- table I/O stat by table name ----------
146 select COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
147 from performance_schema.table_io_waits_summary_by_table
148 where OBJECT_NAME="pfs_example_country";
149 COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
150 3 1 1 1 0
151 ---------- table I/O stat by index usage ----------
152 select INDEX_NAME, COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
153 from performance_schema.table_io_waits_summary_by_index_usage
154 where OBJECT_NAME="pfs_example_country";
155 INDEX_NAME COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
156 NAME 2 1 0 1 0
157 NULL 1 0 1 0 0
158 ---------- update query with NAME ----------
159 update performance_schema.pfs_example_country
160 set GROWTH_FACTOR=6.66
161 where NAME="foo4" and CONTINENT="bar4";
162 ---------- table I/O stat by table name ----------
163 select COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
164 from performance_schema.table_io_waits_summary_by_table
165 where OBJECT_NAME="pfs_example_country";
166 COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
167 5 2 1 2 0
168 ---------- table I/O stat by index usage ----------
169 select INDEX_NAME, COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
170 from performance_schema.table_io_waits_summary_by_index_usage
171 where OBJECT_NAME="pfs_example_country";
172 INDEX_NAME COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
173 NAME 4 2 0 2 0
174 NULL 1 0 1 0 0
175 ---------- delete query ----------
176 delete from performance_schema.pfs_example_country
177 where NAME="foo4" and CONTINENT="bar4";
178 ---------- table I/O stat by table name ----------
179 select COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
180 from performance_schema.table_io_waits_summary_by_table
181 where OBJECT_NAME="pfs_example_country";
182 COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
183 7 3 1 2 1
184 ---------- table I/O stat by index usage ----------
185 select INDEX_NAME, COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
186 from performance_schema.table_io_waits_summary_by_index_usage
187 where OBJECT_NAME="pfs_example_country";
188 INDEX_NAME COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE
189 NAME 6 3 0 2 1
190 NULL 1 0 1 0 0
191 delete from performance_schema.setup_objects
192 where OBJECT_NAME="pfs_example_country";
193 ---------- Index fetch with partial key ----------
194 INSERT into performance_schema.pfs_example_country values
195 ("Home", "Europe", 2022, 1, 1, "HO1"),
196 ("Home", "America", 2022, 2, 2, "HO2"),
197 ("Canada", "America", 2022, 3, 3, "CAN");
198 SELECT * FROM performance_schema.pfs_example_country
199 WHERE NAME = 'nowhere';
200 NAME CONTINENT YEAR POPULATION GROWTH_FACTOR COUNTRY_CODE
201 SELECT * FROM performance_schema.pfs_example_country
202 FORCE INDEX(name)
203 WHERE NAME = 'nowhere';
204 NAME CONTINENT YEAR POPULATION GROWTH_FACTOR COUNTRY_CODE
205 SELECT * FROM performance_schema.pfs_example_country
206 WHERE NAME = 'Canada';
207 NAME CONTINENT YEAR POPULATION GROWTH_FACTOR COUNTRY_CODE
208 Canada America 2022 3 3.00 CAN
209 SELECT * FROM performance_schema.pfs_example_country
210 FORCE INDEX(name)
211 WHERE NAME = 'Canada';
212 NAME CONTINENT YEAR POPULATION GROWTH_FACTOR COUNTRY_CODE
213 Canada America 2022 3 3.00 CAN
214 SELECT * FROM performance_schema.pfs_example_country
215 WHERE NAME = 'Home'
216 ORDER BY CONTINENT;
217 NAME CONTINENT YEAR POPULATION GROWTH_FACTOR COUNTRY_CODE
218 Home America 2022 2 2.00 HO2
219 Home Europe 2022 1 1.00 HO1
220 SELECT * FROM performance_schema.pfs_example_country
221 FORCE INDEX(name)
222 WHERE NAME = 'Home'
223 ORDER BY CONTINENT;
224 NAME CONTINENT YEAR POPULATION GROWTH_FACTOR COUNTRY_CODE
225 Home America 2022 2 2.00 HO2
226 Home Europe 2022 1 1.00 HO1
227 -------------------------------------------------------
228 Fetching from component table after component uninstall
229 -------------------------------------------------------
230 UNINSTALL COMPONENT 'file://component_pfs_example_component_population';;
231 select * from performance_schema.pfs_example_continent;
232 ERROR 42S02: Table 'performance_schema.pfs_example_continent' doesn't exist
233 select * from performance_schema.pfs_example_country;
234 ERROR 42S02: Table 'performance_schema.pfs_example_country' doesn't exist
235 show tables in performance_schema
236 where Tables_in_performance_schema like "%pfs_example%";
237 Tables_in_performance_schema
238 ###########
239 # CLEANUP #
240 ###########
241 pfs_example_component_population init:
242 Passed add_tables()
243 End of init
244
245 pfs_example_component_population_deinit:
246 Passed delete_tables()
247 End of deinit
248