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