Debian Codegraph @

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

raw
1 ################################################################################
2 # Test case to test functionality in which a plugin/component can add its own
3 # tables in performance schema.
4 #
5 # This test script:
6 # - Installs the plugin named pfs_example_plugin_employee. This plugin
7 # calls pfs_plugin_table_v1 service methods add_table()/delete_table() to
8 # add new tables named
9 # - pfs_example_employee_name
10 # - pfs_example_employee_salary
11 # - pfs_example_machine
12 # - pfs_example_machine_by_employee_by_type
13 # in performance_schema database.
14 # - Tries to insert few rows in these tables and tries to fetch them.
15 # - Does some joins on two tables and fetch results.
16 # - Verifes that Indexes are bing used (PRIMARY KEY and KEY)
17 ################################################################################
18
19 --echo #########
20 --echo # SETUP #
21 --echo #########
22
23 # Install pfs_example_plugin_employee plugin.
24 --replace_regex /\.so|\.dll/.xxx/
25 --eval INSTALL PLUGIN pfs_example_plugin_employee SONAME '$PFS_EXAMPLE_PLUGIN_EMPLOYEE'
26
27 # Make sure tables are visible in performance_schema.
28 show tables in performance_schema
29 where Tables_in_performance_schema like "%pfs_example%";
30
31 # Make sure tables are visible in information_schema.
32 select TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE
33 from information_schema.tables
34 where TABLE_NAME like "pfs_example%" AND
35 TABLE_SCHEMA= "performance_schema";
36
37 --echo ##############
38 --echo # Operations #
39 --echo ##############
40
41 --echo -------------------------------------------------------------
42 --echo Displaying records inserted from plugin/component code itself
43 --echo -------------------------------------------------------------
44 select * from performance_schema.pfs_example_employee_name;
45 select * from performance_schema.pfs_example_employee_salary;
46 select * from performance_schema.pfs_example_machine;
47 select * from performance_schema.pfs_example_machine_by_employee_by_type;
48
49 --echo -------------------------------
50 --echo Inserting rows in plugin tables
51 --echo -------------------------------
52 insert into performance_schema.pfs_example_employee_name
53 values (4, "foo4", "bar4");
54
55 insert into performance_schema.pfs_example_employee_salary
56 values (4, 4000, "2013-12-12", "10:10:10");
57
58 insert into performance_schema.pfs_example_machine
59 values (10, 'LAPTOP', "IBM", 4);
60
61 insert into performance_schema.pfs_example_employee_salary
62 values (NULL,1000000000,NULL,NULL);
63
64 insert into performance_schema.pfs_example_employee_salary (employee_salary,date_of_birth)
65 values (10000000,'1988-04-17');
66
67 --error ER_TABLEACCESS_DENIED_ERROR
68 insert into performance_schema.pfs_example_machine_by_employee_by_type
69 values ("a", "b", LAPTOP, 101);
70
71 select * from performance_schema.pfs_example_employee_name;
72 select * from performance_schema.pfs_example_employee_salary;
73 select * from performance_schema.pfs_example_machine;
74 select * from performance_schema.pfs_example_machine_by_employee_by_type;
75
76 --echo -----------------------------------
77 --echo Fetching records from plugin tables
78 --echo -----------------------------------
79 select FIRST_NAME, LAST_NAME, EMPLOYEE_SALARY
80 from performance_schema.pfs_example_employee_name,
81 performance_schema.pfs_example_employee_salary
82 where performance_schema.pfs_example_employee_name.EMPLOYEE_NUMBER=
83 performance_schema.pfs_example_employee_salary.EMPLOYEE_NUMBER;
84
85 --echo ------------------------------
86 --echo Updating rows in plugin tables
87 --echo ------------------------------
88 update performance_schema.pfs_example_employee_name
89 set FIRST_NAME="foo42", LAST_NAME="bar42"
90 where LAST_NAME="bar4";
91
92 update performance_schema.pfs_example_employee_salary
93 set EMPLOYEE_SALARY=8000
94 where EMPLOYEE_NUMBER=2;
95
96 update performance_schema.pfs_example_machine
97 set MACHINE_TYPE = 'DESKTOP'
98 where MACHINE_SL_NUMBER=10;
99
100 --error ER_TABLEACCESS_DENIED_ERROR
101 update performance_schema.pfs_example_machine_by_employee_by_type
102 set count=0
103 where MACHINE_SL_NUMBER=10;
104
105 select * from performance_schema.pfs_example_employee_name;
106 select * from performance_schema.pfs_example_employee_salary;
107 select * from performance_schema.pfs_example_machine;
108 select * from performance_schema.pfs_example_machine_by_employee_by_type;
109
110 --echo -----------------------------------
111 --echo Fetching records from plugin tables
112 --echo -----------------------------------
113 select FIRST_NAME, LAST_NAME, EMPLOYEE_SALARY
114 from performance_schema.pfs_example_employee_name,
115 performance_schema.pfs_example_employee_salary
116 where performance_schema.pfs_example_employee_name.EMPLOYEE_NUMBER=
117 performance_schema.pfs_example_employee_salary.EMPLOYEE_NUMBER;
118
119 --echo -----------------------------------
120 --echo Verify indexes usage
121 --echo -----------------------------------
122 --disable_warnings
123 explain select * from performance_schema.pfs_example_employee_name
124 where EMPLOYEE_NUMBER=2;
125
126 insert into performance_schema.pfs_example_employee_name values
127 (5,"foo","bar"), (6,"foofoo","barbar")
128 , (7,"foo","bar"), (8,"foofoo","barbar")
129 , (9,"foo","bar"), (10,"foofoo","barbar")
130 , (11,"foo","bar"), (12,"foofoo","barbar")
131 , (13,"foo","bar"), (14,"foofoo","barbar")
132 , (15,"foo","bar"), (16,"foofoo","barbar")
133 , (17,"foo","bar"), (18,"foofoo","barbar")
134 , (19,"foo","bar"), (20,"foofoo","barbar")
135 , (21,"foo","bar"), (22,"foofoo","barbar")
136 , (23,"foo","bar"), (24,"foofoo","barbar")
137 , (25,"foo","bar"), (26,"foofoo","barbar")
138 , (27,"foo","bar"), (28,"foofoo","barbar")
139 , (29,"foo","bar"), (30,"foofoo","barbar")
140 , (31,"foo","bar"), (32,"foofoo","barbar")
141 , (33,"foo","bar"), (34,"foofoo","barbar")
142 , (35,"foo","bar"), (36,"foofoo","barbar")
143 , (37,"foo","bar"), (38,"foofoo","barbar")
144 , (39,"foo","bar"), (40,"foofoo","barbar")
145 , (41,"foo","bar"), (42,"foofoo","barbar")
146 , (43,"foo","bar"), (44,"foofoo","barbar")
147 , (45,"foo","bar"), (46,"foofoo","barbar")
148 , (47,"foo","bar"), (48,"foofoo","barbar")
149 , (49,"foo","bar"), (50,"foofoo","barbar")
150 , (51,"foo","bar"), (52,"foofoo","barbar")
151 , (53,"foo","bar"), (54,"foofoo","barbar")
152 , (55,"foo","bar"), (56,"foofoo","barbar")
153 , (57,"foo","bar"), (58,"foofoo","barbar")
154 , (59,"foo","bar"), (60,"foofoo","barbar");
155
156 explain select * from performance_schema.pfs_example_employee_name
157 where FIRST_NAME='foo';
158 --enable_warnings
159
160 --echo -------------------------------------------------
161 --echo Verify stats collection in PFS stats tables
162 --echo -------------------------------------------------
163 update performance_schema.setup_consumers set ENABLED='YES';
164 update performance_schema.setup_instruments set ENABLED='YES', TIMED='YES';
165
166 insert into performance_schema.setup_objects values
167 ('TABLE', "performance_schema", "pfs_example_employee_name", 'YES', 'YES');
168
169 truncate table performance_schema.table_io_waits_summary_by_table;
170 truncate table performance_schema.table_io_waits_summary_by_index_usage;
171
172 --echo ---------- insert query ----------
173 insert into performance_schema.pfs_example_employee_name values
174 (61, "foo5", "bar5");
175
176 --echo ---------- table I/O stat by table name ----------
177 select COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
178 from performance_schema.table_io_waits_summary_by_table
179 where OBJECT_NAME="pfs_example_employee_name";
180
181 --echo ---------- table I/O stat by index usage ----------
182 select INDEX_NAME, COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
183 from performance_schema.table_io_waits_summary_by_index_usage
184 where OBJECT_NAME="pfs_example_employee_name";
185
186 --echo ---------- update query with EMPLOYEE_NUMBER ----------
187 update performance_schema.pfs_example_employee_name set LAST_NAME="bar55"
188 where EMPLOYEE_NUMBER=61;
189
190 --echo ---------- table I/O stat by table name ----------
191 select COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
192 from performance_schema.table_io_waits_summary_by_table
193 where OBJECT_NAME="pfs_example_employee_name";
194
195 --echo ---------- table I/O stat by index usage ----------
196 select INDEX_NAME, COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
197 from performance_schema.table_io_waits_summary_by_index_usage
198 where OBJECT_NAME="pfs_example_employee_name";
199
200 --echo ---------- update query with FIRST_NAME ----------
201 update performance_schema.pfs_example_employee_name set LAST_NAME="bar56"
202 where FIRST_NAME="foo5";
203
204 --echo ---------- table I/O stat by table name ----------
205 --skip_if_hypergraph # Different result because of PSI batch mode in the UPDATE
206 # statement above. The same is seen with the old optimizer
207 # if the above UPDATE uses multi-table syntax.
208 select COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
209 from performance_schema.table_io_waits_summary_by_table
210 where OBJECT_NAME="pfs_example_employee_name";
211
212 --echo ---------- table I/O stat by index usage ----------
213 --skip_if_hypergraph # Different result because of PSI batch mode.
214 select INDEX_NAME, COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
215 from performance_schema.table_io_waits_summary_by_index_usage
216 where OBJECT_NAME="pfs_example_employee_name";
217
218 --echo ---------- delete query ----------
219 delete from performance_schema.pfs_example_employee_name
220 where EMPLOYEE_NUMBER=61;
221
222 --echo ---------- table I/O stat by table name ----------
223 --skip_if_hypergraph # Different result because of PSI batch mode.
224 select COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
225 from performance_schema.table_io_waits_summary_by_table
226 where OBJECT_NAME="pfs_example_employee_name";
227
228 --echo ---------- table I/O stat by index usage ----------
229 --skip_if_hypergraph # Different result because of PSI batch mode.
230 select INDEX_NAME, COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
231 from performance_schema.table_io_waits_summary_by_index_usage
232 where OBJECT_NAME="pfs_example_employee_name";
233
234 delete from performance_schema.setup_objects
235 where OBJECT_NAME="pfs_example_employee_name";
236
237 --echo -------------------------------------------------
238 --echo Fetching from plugin table after plugin uninstall
239 --echo -------------------------------------------------
240 UNINSTALL PLUGIN pfs_example_plugin_employee;
241
242 --error ER_NO_SUCH_TABLE
243 select * from performance_schema.pfs_example_employee_name;
244 --error ER_NO_SUCH_TABLE
245 select * from performance_schema.pfs_example_employee_salary;
246
247 # Make sure that tables are removed from performance_schema.
248 show tables in performance_schema
249 where Tables_in_performance_schema like "%pfs_example%";
250
251 --echo ###########
252 --echo # CLEANUP #
253 --echo ###########