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
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
|
show databases;
Database
information_schema
mtr
mysql
performance_schema
sys
test
select count(*) from performance_schema.performance_timers;
count(*)
5
select count(*) from performance_schema.setup_consumers;
count(*)
16
select count(*) > 4 from performance_schema.setup_instruments;
count(*) > 4
1
select * from performance_schema.accounts;
select * from performance_schema.cond_instances;
select * from performance_schema.events_stages_current;
select * from performance_schema.events_stages_history;
select * from performance_schema.events_stages_history_long;
select * from performance_schema.events_stages_summary_by_account_by_event_name;
select * from performance_schema.events_stages_summary_by_host_by_event_name;
select * from performance_schema.events_stages_summary_by_thread_by_event_name;
select * from performance_schema.events_stages_summary_by_user_by_event_name;
select * from performance_schema.events_stages_summary_global_by_event_name;
select * from performance_schema.events_statements_current;
select * from performance_schema.events_statements_histogram_by_digest;
select * from performance_schema.events_statements_histogram_global;
select * from performance_schema.events_statements_history;
select * from performance_schema.events_statements_history_long;
select * from performance_schema.events_statements_summary_by_account_by_event_name;
select * from performance_schema.events_statements_summary_by_digest;
select * from performance_schema.events_statements_summary_by_host_by_event_name;
select * from performance_schema.events_statements_summary_by_thread_by_event_name;
select * from performance_schema.events_statements_summary_by_user_by_event_name;
select * from performance_schema.events_statements_summary_global_by_event_name;
select * from performance_schema.events_transactions_current;
select * from performance_schema.events_transactions_history;
select * from performance_schema.events_transactions_history_long;
select * from performance_schema.events_transactions_summary_by_account_by_event_name;
select * from performance_schema.events_transactions_summary_by_host_by_event_name;
select * from performance_schema.events_transactions_summary_by_thread_by_event_name;
select * from performance_schema.events_transactions_summary_by_user_by_event_name;
select * from performance_schema.events_transactions_summary_global_by_event_name;
select * from performance_schema.events_waits_current;
select * from performance_schema.events_waits_history;
select * from performance_schema.events_waits_history_long;
select * from performance_schema.events_waits_summary_by_account_by_event_name;
select * from performance_schema.events_waits_summary_by_host_by_event_name;
select * from performance_schema.events_waits_summary_by_instance;
select * from performance_schema.events_waits_summary_by_thread_by_event_name;
select * from performance_schema.events_waits_summary_by_user_by_event_name;
select * from performance_schema.events_waits_summary_global_by_event_name;
select * from performance_schema.memory_summary_by_account_by_event_name;
select * from performance_schema.memory_summary_by_host_by_event_name;
select * from performance_schema.memory_summary_by_thread_by_event_name;
select * from performance_schema.memory_summary_by_user_by_event_name;
select * from performance_schema.memory_summary_global_by_event_name;
select * from performance_schema.events_errors_summary_by_account_by_error;
select * from performance_schema.events_errors_summary_by_host_by_error;
select * from performance_schema.events_errors_summary_by_thread_by_error;
select * from performance_schema.events_errors_summary_by_user_by_error;
select * from performance_schema.events_errors_summary_global_by_error;
select * from performance_schema.file_instances;
select * from performance_schema.file_summary_by_event_name;
select * from performance_schema.file_summary_by_instance;
select * from performance_schema.host_cache;
select * from performance_schema.hosts;
select * from performance_schema.memory_summary_by_account_by_event_name;
select * from performance_schema.memory_summary_by_host_by_event_name;
select * from performance_schema.memory_summary_by_thread_by_event_name;
select * from performance_schema.memory_summary_by_user_by_event_name;
select * from performance_schema.memory_summary_global_by_event_name;
select * from performance_schema.metadata_locks;
select * from performance_schema.mutex_instances;
select * from performance_schema.objects_summary_global_by_type;
select * from performance_schema.performance_timers;
select * from performance_schema.rwlock_instances;
select * from performance_schema.data_locks;
select * from performance_schema.data_lock_waits;
select * from performance_schema.session_account_connect_attrs;
select * from performance_schema.session_connect_attrs;
select * from performance_schema.setup_actors;
select * from performance_schema.setup_consumers;
select * from performance_schema.setup_instruments;
select * from performance_schema.setup_objects;
select * from performance_schema.setup_threads;
select * from performance_schema.socket_instances;
select * from performance_schema.socket_summary_by_instance;
select * from performance_schema.socket_summary_by_event_name;
select * from performance_schema.table_handles;
select * from performance_schema.table_io_waits_summary_by_index_usage;
select * from performance_schema.table_io_waits_summary_by_table;
select * from performance_schema.table_lock_waits_summary_by_table;
select * from performance_schema.threads;
select * from performance_schema.users;
select * from performance_schema.replication_connection_configuration;
select * from performance_schema.replication_connection_status;
select * from performance_schema.replication_applier_configuration;
select * from performance_schema.replication_applier_status;
select * from performance_schema.replication_applier_status_by_coordinator;
select * from performance_schema.replication_applier_status_by_worker;
select * from performance_schema.global_status;
select * from performance_schema.status_by_thread;
select * from performance_schema.status_by_user;
select * from performance_schema.status_by_host;
select * from performance_schema.status_by_account;
select * from performance_schema.session_status;
select * from performance_schema.global_variables;
select * from performance_schema.variables_by_thread;
select * from performance_schema.session_variables;
show engine PERFORMANCE_SCHEMA status;
show global status like "performance_schema%";
drop table if exists db1.t1;
drop database if exists db1;
create database db1;
create table db1.t1 (a int, b char(10) default 'default');
insert into db1.t1 values('1', 'abc');
insert into db1.t1 values('2', 'abc');
select * from db1.t1 where a='1';
a b
1 abc
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;
COUNT_STAR OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME
3 TABLE db1 t1
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;
COUNT_STAR OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME
3 TABLE db1 t1
select variable_value from performance_schema.global_status
where variable_name like 'PERFORMANCE_SCHEMA_TABLE_LOCK_STAT_LOST';
variable_value
0
create table db1.t2 (a int, b char(10) default 'default',
unique key uidx(a));
insert into db1.t1 values('3', 'abc');
insert into db1.t1 values('4', 'abc');
select * from db1.t1 where a='1';
a b
1 abc
insert into db1.t2 values('1', 'abc');
insert into db1.t2 values('2', 'abc');
select * from db1.t2 where a='1';
a b
1 abc
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;
COUNT_STAR OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME
6 TABLE db1 t1
3 TABLE db1 t2
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;
COUNT_STAR OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME
6 TABLE db1 t1
0 TABLE db1 t2
select variable_value > 0 from performance_schema.global_status
where variable_name like 'PERFORMANCE_SCHEMA_TABLE_LOCK_STAT_LOST';
variable_value > 0
1
flush status;
update performance_schema.setup_objects set ENABLED='NO'
where OBJECT_TYPE='TABLE' and OBJECT_SCHEMA="%";
insert into db1.t1 values('5', 'abc');
insert into db1.t1 values('6', 'abc');
select * from db1.t1 where a='1';
a b
1 abc
insert into db1.t2 values('3', 'abc');
insert into db1.t2 values('4', 'abc');
select * from db1.t2 where a='1';
a b
1 abc
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;
COUNT_STAR OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME
6 TABLE db1 t1
0 TABLE db1 t2
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;
COUNT_STAR OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME
6 TABLE db1 t1
0 TABLE db1 t2
select variable_value from performance_schema.global_status
where variable_name like 'PERFORMANCE_SCHEMA_TABLE_LOCK_STAT_LOST';
variable_value
0
drop database db1;
update performance_schema.setup_objects set ENABLED='YES'
where OBJECT_TYPE='TABLE' and OBJECT_SCHEMA="%";
|
