Debian Codegraph @

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

raw
1 ################################################################################
2 # Test case to test functionality in which a component can add its own
3 # tables in performance schema.
4 #
5 # This test script:
6 # - Installs the component named pfs_example_component_population.
7 # This component calls pfs_plugin_table_v1 service methods
8 # add_table() to add new tables named
9 # - pfs_example_continent
10 # - pfs_example_country
11 # in performance_schema database.
12 # - Tries to insert few rows in these tables and tries to fetch them.
13 # - Does some joins on two tables and fetch results.
14 # - Verifes that Indexes are bing used (PRIMARY KEY and KEY)
15 # - Verifes that Indexes and table statistics are being collected by
16 # performance schema
17 # - Uninstall component. This will call delete_table() service method
18 # to delete component tables from performance schema added earlier.
19 ################################################################################
20
21 --echo #########
22 --echo # SETUP #
23 --echo #########
24
25 # Install pfs_example_component_population component.
26 --eval INSTALL COMPONENT 'file://component_pfs_example_component_population';
27
28 # Make sure tables are visible in performance_schema.
29 show tables in performance_schema
30 where Tables_in_performance_schema like "%pfs_example%";
31
32 # Make sure tables are visible in information_schema.
33 select TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE
34 from information_schema.tables
35 where TABLE_NAME like "pfs_example%" AND
36 TABLE_SCHEMA= "performance_schema";
37
38 --echo ##############
39 --echo # Operations #
40 --echo ##############
41
42 --echo -------------------------------------------------------------
43 --echo Displaying records inserted from component code itself
44 --echo -------------------------------------------------------------
45 select * from performance_schema.pfs_example_continent;
46 select * from performance_schema.pfs_example_country;
47
48 --echo ----------------------------------
49 --echo Inserting rows in component tables
50 --echo ----------------------------------
51 --error ER_TABLEACCESS_DENIED_ERROR
52 insert into performance_schema.pfs_example_continent
53 values ("bar3");
54
55 insert into performance_schema.pfs_example_country
56 values ("foo3", "bar3", 2016, 100, 3.33, "FO3");
57
58 select * from performance_schema.pfs_example_continent;
59 select * from performance_schema.pfs_example_country;
60
61 --echo --------------------------------------
62 --echo Fetching records from component tables
63 --echo --------------------------------------
64 select performance_schema.pfs_example_continent.NAME as CONTINENT_NAME,
65 performance_schema.pfs_example_country.NAME as COUNTRY_NAME,
66 YEAR, POPULATION, GROWTH_FACTOR
67 from performance_schema.pfs_example_continent,
68 performance_schema.pfs_example_country
69 where pfs_example_country.CONTINENT=pfs_example_continent.NAME;
70
71 --echo ---------------------------------
72 --echo Updating rows in component tables
73 --echo ---------------------------------
74 --error ER_TABLEACCESS_DENIED_ERROR
75 update performance_schema.pfs_example_continent
76 set NAME="bar3"
77 where NAME="bar1";
78
79 update performance_schema.pfs_example_country
80 set GROWTH_FACTOR=8.88
81 where NAME="foo1";
82
83 select * from performance_schema.pfs_example_continent;
84 select * from performance_schema.pfs_example_country;
85
86 --echo --------------------------------------
87 --echo Fetching records from component tables
88 --echo --------------------------------------
89 select performance_schema.pfs_example_continent.NAME as CONTINENT_NAME,
90 performance_schema.pfs_example_country.NAME as COUNTRY_NAME,
91 YEAR, POPULATION, GROWTH_FACTOR
92 from performance_schema.pfs_example_continent,
93 performance_schema.pfs_example_country
94 where pfs_example_country.CONTINENT=pfs_example_continent.NAME;
95
96 --echo -----------------------------------
97 --echo Verify indexes usage
98 --echo -----------------------------------
99 --disable_warnings
100 explain select * from performance_schema.pfs_example_continent
101 where NAME="bar1";
102 --enable_warnings
103
104 --echo -------------------------------------------------
105 --echo Verify stats collection in PFS stats tables
106 --echo -------------------------------------------------
107 update performance_schema.setup_consumers set ENABLED='YES';
108 update performance_schema.setup_instruments set ENABLED='YES', TIMED='YES';
109
110 insert into performance_schema.setup_objects values
111 ('TABLE', "performance_schema", "pfs_example_country", 'YES', 'YES');
112
113 # To make entry of NULL key in table_io_waits_summary_by_index_usage. To make
114 # sure consecutive execution of test case doesn't fail.
115 select * from performance_schema.pfs_example_country;
116
117 truncate table performance_schema.table_io_waits_summary_by_table;
118 truncate table performance_schema.table_io_waits_summary_by_index_usage;
119
120 --echo ---------- table I/O stat by table name ----------
121 select COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
122 from performance_schema.table_io_waits_summary_by_table
123 where OBJECT_NAME="pfs_example_country";
124
125 --echo ---------- table I/O stat by index usage ----------
126 select INDEX_NAME, COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
127 from performance_schema.table_io_waits_summary_by_index_usage
128 where OBJECT_NAME="pfs_example_country";
129
130 --echo ---------- insert query ----------
131 insert into performance_schema.pfs_example_country
132 values ("foo4", "bar4", 2016, 100, 4.44, "FO4");
133
134 --echo ---------- table I/O stat by table name ----------
135 select COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
136 from performance_schema.table_io_waits_summary_by_table
137 where OBJECT_NAME="pfs_example_country";
138
139 --echo ---------- table I/O stat by index usage ----------
140 select INDEX_NAME, COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
141 from performance_schema.table_io_waits_summary_by_index_usage
142 where OBJECT_NAME="pfs_example_country";
143
144 --echo ---------- update query with NAME ----------
145 update performance_schema.pfs_example_country
146 set GROWTH_FACTOR=5.55
147 where NAME="foo4" and CONTINENT="bar4";
148
149 --echo ---------- table I/O stat by table name ----------
150 select COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
151 from performance_schema.table_io_waits_summary_by_table
152 where OBJECT_NAME="pfs_example_country";
153
154 --echo ---------- table I/O stat by index usage ----------
155 select INDEX_NAME, COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
156 from performance_schema.table_io_waits_summary_by_index_usage
157 where OBJECT_NAME="pfs_example_country";
158
159 --echo ---------- update query with NAME ----------
160 update performance_schema.pfs_example_country
161 set GROWTH_FACTOR=6.66
162 where NAME="foo4" and CONTINENT="bar4";
163
164 --echo ---------- table I/O stat by table name ----------
165 select COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
166 from performance_schema.table_io_waits_summary_by_table
167 where OBJECT_NAME="pfs_example_country";
168
169 --echo ---------- table I/O stat by index usage ----------
170 select INDEX_NAME, COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
171 from performance_schema.table_io_waits_summary_by_index_usage
172 where OBJECT_NAME="pfs_example_country";
173
174 --echo ---------- delete query ----------
175 delete from performance_schema.pfs_example_country
176 where NAME="foo4" and CONTINENT="bar4";
177
178 --echo ---------- 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
183 --echo ---------- table I/O stat by index usage ----------
184 select INDEX_NAME, COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
185 from performance_schema.table_io_waits_summary_by_index_usage
186 where OBJECT_NAME="pfs_example_country";
187
188 delete from performance_schema.setup_objects
189 where OBJECT_NAME="pfs_example_country";
190
191
192
193 --echo ---------- Index fetch with partial key ----------
194
195 # Tests for
196 # Bug#33893369 static bool PFS_key_pstring::stateless_match(): Assertion `false' failed.
197
198 # Yes, this is silly, as the primary key definition is wrong.
199 # Making sure a partial key index fetch works with 0, 1, N records.
200
201 INSERT into performance_schema.pfs_example_country values
202 ("Home", "Europe", 2022, 1, 1, "HO1"),
203 ("Home", "America", 2022, 2, 2, "HO2"),
204 ("Canada", "America", 2022, 3, 3, "CAN");
205
206 # 0 record
207
208 SELECT * FROM performance_schema.pfs_example_country
209 WHERE NAME = 'nowhere';
210
211 SELECT * FROM performance_schema.pfs_example_country
212 FORCE INDEX(name)
213 WHERE NAME = 'nowhere';
214
215 # 1 record
216
217 SELECT * FROM performance_schema.pfs_example_country
218 WHERE NAME = 'Canada';
219
220 SELECT * FROM performance_schema.pfs_example_country
221 FORCE INDEX(name)
222 WHERE NAME = 'Canada';
223
224 # N records
225
226 SELECT * FROM performance_schema.pfs_example_country
227 WHERE NAME = 'Home'
228 ORDER BY CONTINENT;
229
230 SELECT * FROM performance_schema.pfs_example_country
231 FORCE INDEX(name)
232 WHERE NAME = 'Home'
233 ORDER BY CONTINENT;
234
235
236 --echo -------------------------------------------------------
237 --echo Fetching from component table after component uninstall
238 --echo -------------------------------------------------------
239 --eval UNINSTALL COMPONENT 'file://component_pfs_example_component_population';
240
241 --error ER_NO_SUCH_TABLE
242 select * from performance_schema.pfs_example_continent;
243 --error ER_NO_SUCH_TABLE
244 select * from performance_schema.pfs_example_country;
245
246 # Make sure that tables are removed from performance_schema.
247 show tables in performance_schema
248 where Tables_in_performance_schema like "%pfs_example%";
249
250 --echo ###########
251 --echo # CLEANUP #
252 --echo ###########
253 let $MYSQLD_DATADIR= `select @@datadir`;
254 cat_file $MYSQLD_DATADIR/pfs_example_component_population.log;
255 remove_file $MYSQLD_DATADIR/pfs_example_component_population.log;