Debian Codegraph @

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

raw
1 # Tests for PERFORMANCE_SCHEMA
2
3 --source include/not_valgrind.inc
4 --source include/not_embedded.inc
5 --source include/have_perfschema.inc
6
7 --source ../include/start_server_common.inc
8
9
10 # NOTE : Index stats are manifested during table creation. To force a low index
11 # condition, performance_schema_max_index_stat must be set to the number of
12 # all system table indexes, plus 1.
13
14 #############################
15 # Setup database and tables.
16 #############################
17 --disable_warnings
18 drop table if exists db1.t1;
19 drop database if exists db1;
20 --enable_warnings
21 create database db1;
22 create table db1.t1 (a int, b char(10) default 'default',
23 unique key uidx(a));
24
25 #######################
26 # Execute few queries.
27 #######################
28 insert into db1.t1 values('1', 'abc');
29 insert into db1.t1 values('2', 'abc');
30 --disable_ps2_protocol
31 select * from db1.t1 where a='1';
32 --enable_ps2_protocol
33
34 #####################################################
35 # Run few queries on Performance Schema stats tables.
36 #####################################################
37 # There should be 3 entries in following table. 2 for insert and 1 for select.
38 select COUNT_STAR, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME
39 from performance_schema.table_io_waits_summary_by_table
40 where OBJECT_SCHEMA='db1'
41 order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME;
42
43 # Stats for 2 indexes (full scan, uidx)
44 select COUNT_STAR, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME
45 from performance_schema.table_io_waits_summary_by_index_usage
46 where OBJECT_SCHEMA='db1'
47 order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME;
48
49 flush tables;
50
51 # Stats for full scan lost
52 select COUNT_STAR, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME
53 from performance_schema.table_io_waits_summary_by_table
54 where OBJECT_SCHEMA='db1'
55 order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME;
56
57 # Stats for only 1 index, uidx
58 # Stats for full scan lost
59 select COUNT_STAR, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME
60 from performance_schema.table_io_waits_summary_by_index_usage
61 where OBJECT_SCHEMA='db1'
62 order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME;
63
64 # Expect INDEX_STAT lost, we have room for 1 out of 2
65 --disable_warnings
66 select variable_value > 0 from information_schema.global_status
67 where variable_name like 'PERFORMANCE_SCHEMA_INDEX_STAT_LOST';
68
69 # While at it, check that FLUSH STATUS Resets the lost counter
70 FLUSH GLOBAL STATUS;
71
72 select variable_value from information_schema.global_status
73 where variable_name like 'PERFORMANCE_SCHEMA_INDEX_STAT_LOST';
74 --enable_warnings
75
76
77 create table db1.t2 (a int, b char(10) default 'default',
78 unique key uidx(a));
79
80 #######################
81 # Execute few queries.
82 #######################
83 insert into db1.t1 values('3', 'abc');
84 insert into db1.t1 values('4', 'abc');
85 --disable_ps2_protocol
86 select * from db1.t1 where a='1';
87 --enable_ps2_protocol
88
89 insert into db1.t2 values('1', 'abc');
90 insert into db1.t2 values('2', 'abc');
91 --disable_ps2_protocol
92 select * from db1.t2 where a='1';
93 --enable_ps2_protocol
94
95 #####################################################
96 # Run few queries on Performance Schema stats tables.
97 #####################################################
98
99 select COUNT_STAR, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME
100 from performance_schema.table_io_waits_summary_by_table
101 where OBJECT_SCHEMA='db1'
102 order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME;
103
104 select COUNT_STAR, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME
105 from performance_schema.table_io_waits_summary_by_index_usage
106 where OBJECT_SCHEMA='db1'
107 order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME;
108
109 flush tables;
110
111 select COUNT_STAR, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME
112 from performance_schema.table_io_waits_summary_by_table
113 where OBJECT_SCHEMA='db1'
114 order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME;
115
116 select COUNT_STAR, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME
117 from performance_schema.table_io_waits_summary_by_index_usage
118 where OBJECT_SCHEMA='db1'
119 order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME;
120
121 # Expect INDEX_STAT lost
122 --disable_warnings
123 select variable_value > 0 from information_schema.global_status
124 where variable_name like 'PERFORMANCE_SCHEMA_INDEX_STAT_LOST';
125 --enable_warnings
126
127 FLUSH GLOBAL STATUS;
128
129
130 #####################################################################################
131 # Update setup_objects to DISABLE TABLE and check index lost stats after flush tables
132 #####################################################################################
133
134 update performance_schema.setup_objects set ENABLED='NO' where OBJECT_TYPE='TABLE';
135
136
137
138 #######################
139 # Execute few queries.
140 #######################
141 insert into db1.t1 values('5', 'abc');
142 insert into db1.t1 values('6', 'abc');
143 select * from db1.t1 where a='1';
144
145 insert into db1.t2 values('3', 'abc');
146 insert into db1.t2 values('4', 'abc');
147 select * from db1.t2 where a='1';
148
149 # Empty, objects are disabled
150 select COUNT_STAR, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME
151 from performance_schema.table_io_waits_summary_by_table
152 where OBJECT_SCHEMA='db1'
153 order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME;
154
155 # Empty, objects are disabled
156 select COUNT_STAR, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME
157 from performance_schema.table_io_waits_summary_by_index_usage
158 where OBJECT_SCHEMA='db1'
159 order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME;
160
161 flush tables;
162
163 # Empty, objects are disabled
164 select COUNT_STAR, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME
165 from performance_schema.table_io_waits_summary_by_table
166 where OBJECT_SCHEMA='db1'
167 order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME;
168
169 # Empty, objects are disabled
170 select COUNT_STAR, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME
171 from performance_schema.table_io_waits_summary_by_index_usage
172 where OBJECT_SCHEMA='db1'
173 order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME;
174
175 # Do not expect lost counter in INDEX_STAT
176 --disable_warnings
177 select variable_value from information_schema.global_status
178 where variable_name like 'PERFORMANCE_SCHEMA_INDEX_STAT_LOST';
179 --enable_warnings
180
181 ###########
182 # Cleanup.
183 ###########
184 drop database db1;
185
186 update performance_schema.setup_objects set ENABLED='YES'
187 where OBJECT_TYPE='TABLE' and OBJECT_SCHEMA="%";