Debian Codegraph @

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

raw
1 # Tests for PERFORMANCE_SCHEMA table io
2 # Show the impact of an index
3
4 # Setup
5
6 --source include/not_embedded.inc
7 --source include/have_perfschema.inc
8
9 let $engine_type= MyISAM;
10
11 --disable_warnings
12 drop table if exists test.no_index_tab;
13 drop table if exists test.index_tab;
14 --enable_warnings
15
16 let $table_io_select= select SUM(NUMBER_OF_BYTES)
17 from performance_schema.events_waits_history_long
18 where event_name like 'wait/io/table/%'
19 and object_schema = 'test'
20 and object_name = ;
21
22 --source ../include/table_io_setup_helper.inc
23
24 # Code to test
25
26 eval create table test.no_index_tab
27 ( a int, b char(30) default 'Default') engine = $engine_type;
28 eval create table test.index_tab
29 ( a int, b char(30) default 'Default', unique key uidx(a)) engine = $engine_type;
30 # Make sure the proper engine is used
31 show create table test.no_index_tab;
32 show create table test.index_tab;
33
34 truncate table performance_schema.events_waits_history_long;
35 update performance_schema.setup_consumers set enabled='YES';
36 --echo # Printing of 100 inserts per table is suppressed
37 --disable_query_log
38 let $run= 100;
39 while ($run)
40 {
41 eval insert into test.no_index_tab set a = $run;
42 eval insert into test.index_tab set a = $run;
43 dec $run;
44 }
45 --enable_query_log
46 update performance_schema.setup_consumers set enabled='NO';
47 eval $table_io_select 'no_index_tab';
48 # Attention: There is no visible impact of index maintenance on table io
49 # because the maintenance is handled by the storage engine themself.
50 eval $table_io_select 'index_tab';
51
52 select count(*) from test.no_index_tab;
53 select count(*) from test.index_tab;
54
55 # Testing Code
56
57 --disable_ps2_protocol
58 # For getting avg(a) inspection of
59 # - all rows (test.no_index_tab)
60 # - all unique index values (test.index_tab, assuming the optimizer decides to
61 # process only the unique index)
62 # --> No difference between numvber of accesses for no_index_tab and index_tab
63 truncate table performance_schema.events_waits_history_long;
64 update performance_schema.setup_consumers set enabled='YES';
65 select avg(a) from test.no_index_tab;
66 update performance_schema.setup_consumers set enabled='NO';
67 eval $table_io_select 'no_index_tab';
68 truncate table performance_schema.events_waits_history_long;
69 update performance_schema.setup_consumers set enabled='YES';
70 select avg(a) from test.index_tab;
71 update performance_schema.setup_consumers set enabled='NO';
72 eval $table_io_select 'index_tab';
73
74 # With where a = 50 inspection of
75 # - all rows (test.no_index_tab)
76 # - 1 up to maybe a few unique index values (test.index_tab, assuming that the
77 # optimizer decides to exploit the unique index)
78 # --> index_tab requires much less accesses than no_index_tab
79 truncate table performance_schema.events_waits_history_long;
80 update performance_schema.setup_consumers set enabled='YES';
81 select 1 as my_column from test.no_index_tab where a = 50;
82 update performance_schema.setup_consumers set enabled='NO';
83 eval $table_io_select 'no_index_tab';
84 truncate table performance_schema.events_waits_history_long;
85 update performance_schema.setup_consumers set enabled='YES';
86 select 1 as my_column from test.index_tab where a = 50;
87 update performance_schema.setup_consumers set enabled='NO';
88 eval $table_io_select 'index_tab';
89
90 # With where a = 50 inspection of
91 # - all rows (test.no_index_tab)
92 # - 1 up to maybe a few unique index values (test.index_tab, assuming that the
93 # optimizer decides to exploit the unique index)
94 # and remove one row for both cases
95 # --> index_tab requires much less accesses than no_index_tab
96 truncate table performance_schema.events_waits_history_long;
97 update performance_schema.setup_consumers set enabled='YES';
98 delete from test.no_index_tab where a = 51;
99 update performance_schema.setup_consumers set enabled='NO';
100 eval $table_io_select 'no_index_tab';
101 truncate table performance_schema.events_waits_history_long;
102 update performance_schema.setup_consumers set enabled='YES';
103 delete from test.index_tab where a = 51;
104 update performance_schema.setup_consumers set enabled='NO';
105 eval $table_io_select 'index_tab';
106 --enable_ps2_protocol
107
108 # In case of failures, this will tell if table io are lost.
109 show global status like 'performance_schema_%';
110
111 # Cleanup
112 drop table test.no_index_tab;
113 drop table test.index_tab;
114 --source ../include/table_io_cleanup_helper.inc