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