Debian Codegraph @

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

raw
1 # Test for Performance Schema.
2 # To test the effect of disabling/enabling instrumentation for a table
3 # in setup_objects table.
4
5 --source include/not_embedded.inc
6 --source include/have_perfschema.inc
7
8 --disable_warnings
9 drop table if exists test.setup_objects;
10 --enable_warnings
11
12 # Make sure only table io / table lock is instrumented and timed
13 update performance_schema.setup_instruments set enabled='NO';
14 update performance_schema.setup_instruments set enabled='YES', timed='YES'
15 where name in ('wait/io/table/sql/handler', 'wait/lock/table/sql/handler');
16
17 # Remove noise from previous tests
18 truncate table performance_schema.table_io_waits_summary_by_table;
19 truncate table performance_schema.table_lock_waits_summary_by_table;
20
21 # Save the setup
22 create table test.setup_objects as select * from performance_schema.setup_objects;
23 truncate table performance_schema.setup_objects;
24 select * from performance_schema.setup_objects;
25
26 insert into performance_schema.setup_objects
27 values ('TABLE', 'db1', 't1', 'YES', 'YES');
28
29 insert into performance_schema.setup_objects
30 values ('TABLE', 'db1', 't2', 'YES', 'YES');
31
32 select * from performance_schema.setup_objects
33 order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME;
34 SET sql_mode = default;
35
36 --disable_warnings
37 drop database if exists db1;
38 --enable_warnings
39
40 create database db1;
41 create table db1.t1 (a int, b char(10) default 'default', unique key uidx(a));
42 create table db1.t2 (a int, b char(10) default 'default');
43
44 # Insert some values in tables.
45 insert into db1.t1 values('11', 'db1.t1');
46 insert into db1.t1 values('12', 'db1.t1');
47 insert into db1.t1 values('13', 'db1.t1');
48 insert into db1.t2 values('21', 'db1.t2');
49 insert into db1.t2 values('22', 'db1.t2');
50 insert into db1.t2 values('23', 'db1.t2');
51
52 # Query P_S tables. Stats should be updated.
53 select OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_READ, COUNT_WRITE
54 from performance_schema.table_lock_waits_summary_by_table
55 where OBJECT_SCHEMA='db1';
56 select OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_READ, COUNT_WRITE
57 from performance_schema.table_io_waits_summary_by_table
58 where OBJECT_SCHEMA='db1';
59
60 truncate table db1.t1;
61 truncate table db1.t2;
62
63 # Query P_S tables. Stats should be updated.
64 select OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_READ, COUNT_WRITE
65 from performance_schema.table_lock_waits_summary_by_table
66 where OBJECT_SCHEMA='db1';
67 select OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_READ, COUNT_WRITE
68 from performance_schema.table_io_waits_summary_by_table
69 where OBJECT_SCHEMA='db1';
70
71 # Disable instrumentation for db1.t1
72 update performance_schema.setup_objects
73 set ENABLED='NO' where OBJECT_SCHEMA = 'db1' and OBJECT_NAME='t1';
74
75 # Query P_S tables. Stats for db1.t1 should have been removed while for db1.t2 it should have been preserved.
76 select OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_READ, COUNT_WRITE
77 from performance_schema.table_lock_waits_summary_by_table
78 where OBJECT_SCHEMA='db1';
79 select OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_READ, COUNT_WRITE
80 from performance_schema.table_io_waits_summary_by_table
81 where OBJECT_SCHEMA='db1';
82
83 # Enable instrumentation for db1.t1
84 update performance_schema.setup_objects
85 set ENABLED='YES' where OBJECT_SCHEMA = 'db1' and OBJECT_NAME='t1';
86
87 # Query P_S tables. Stats for db1.t1 should have been reset while for db1.t2 it should have been preserved.
88 select OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_READ, COUNT_WRITE
89 from performance_schema.table_lock_waits_summary_by_table
90 where OBJECT_SCHEMA='db1';
91 select OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_READ, COUNT_WRITE
92 from performance_schema.table_io_waits_summary_by_table
93 where OBJECT_SCHEMA='db1';
94
95 truncate table performance_schema.setup_objects;
96
97 select count(*) from performance_schema.setup_objects;
98
99 drop database db1;
100
101 # Restore the setup
102 truncate table performance_schema.setup_objects;
103 insert into performance_schema.setup_objects select * from test.setup_objects;
104 drop table test.setup_objects;
105
106 update performance_schema.setup_instruments set enabled='YES', timed='YES';
107