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