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
|
# Tests for PERFORMANCE_SCHEMA
--source include/not_embedded.inc
--source include/have_perfschema.inc
--source ../include/start_server_common.inc
#############################
# 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');
#######################
# 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_lock_waits_summary_by_table
where OBJECT_SCHEMA='db1'
order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME;
flush tables;
select COUNT_STAR, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME
from performance_schema.table_lock_waits_summary_by_table
where OBJECT_SCHEMA='db1'
order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME;
# Expect no TABLE_LOCK_STAT lost, we have room for 1 table lock for db1.t1
--disable_warnings
select variable_value from information_schema.global_status
where variable_name like 'PERFORMANCE_SCHEMA_TABLE_LOCK_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_lock_waits_summary_by_table
where OBJECT_SCHEMA='db1'
order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME;
flush tables;
select COUNT_STAR, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME
from performance_schema.table_lock_waits_summary_by_table
where OBJECT_SCHEMA='db1'
order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME;
# Expect TABLE_LOCK_STAT lost, we can not keep stats for db1.t2
--disable_warnings
select variable_value > 0 from information_schema.global_status
where variable_name like 'PERFORMANCE_SCHEMA_TABLE_LOCK_STAT_LOST';
--enable_warnings
flush status;
#######################################
# Update setup_objects to DISABLE TABLE
#######################################
update performance_schema.setup_objects set ENABLED='NO'
where OBJECT_TYPE='TABLE' and OBJECT_SCHEMA="%";
#######################
# 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_lock_waits_summary_by_table
where OBJECT_SCHEMA='db1'
order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME;
flush tables;
# Empty, objects are disabled
select COUNT_STAR, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME
from performance_schema.table_lock_waits_summary_by_table
where OBJECT_SCHEMA='db1'
order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME;
# Do not expect lost counter in TABLE_LOCK_STAT
--disable_warnings
select variable_value from information_schema.global_status
where variable_name like 'PERFORMANCE_SCHEMA_TABLE_LOCK_STAT_LOST';
--enable_warnings
###########
# Cleanup.
###########
drop database db1;
update performance_schema.setup_objects set ENABLED='YES'
where OBJECT_TYPE='TABLE' and OBJECT_SCHEMA="%";
|
