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