blob: 1a7597113d24764c6f8c88d2b4361cb737ffcf1c (
plain)
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
|
# Tests for PERFORMANCE_SCHEMA table io
# Show the impact of an index
# Setup
--source include/not_embedded.inc
--source include/have_perfschema.inc
let $engine_type= MyISAM;
--disable_warnings
drop table if exists test.no_index_tab;
drop table if exists test.index_tab;
--enable_warnings
let $table_io_select= select COUNT(*)
from performance_schema.events_waits_history_long
where event_name like 'wait/io/table/%'
and object_schema = 'test'
and object_name = ;
--source ../include/table_io_setup_helper.inc
# Code to test
eval create table test.no_index_tab
( a int, b char(30) default 'Default') engine = $engine_type;
eval create table test.index_tab
( a int, b char(30) default 'Default', unique key uidx(a)) engine = $engine_type;
# Make sure the proper engine is used
show create table test.no_index_tab;
show create table test.index_tab;
truncate table performance_schema.events_waits_history_long;
update performance_schema.setup_consumers set enabled='YES';
--echo # Printing of 100 inserts per table is suppressed
--disable_query_log
let $run= 100;
while ($run)
{
eval insert into test.no_index_tab set a = $run;
eval insert into test.index_tab set a = $run;
dec $run;
}
--enable_query_log
update performance_schema.setup_consumers set enabled='NO';
eval $table_io_select 'no_index_tab';
# Attention: There is no visible impact of index maintenance on table io
# because the maintenance is handled by the storage engine themself.
eval $table_io_select 'index_tab';
select count(*) from test.no_index_tab;
select count(*) from test.index_tab;
# Testing Code
# For getting avg(a) inspection of
# - all rows (test.no_index_tab)
# - all unique index values (test.index_tab, assuming the optimizer decides to
# process only the unique index)
# --> No difference between numvber of accesses for no_index_tab and index_tab
truncate table performance_schema.events_waits_history_long;
update performance_schema.setup_consumers set enabled='YES';
select avg(a) from test.no_index_tab;
update performance_schema.setup_consumers set enabled='NO';
eval $table_io_select 'no_index_tab';
truncate table performance_schema.events_waits_history_long;
update performance_schema.setup_consumers set enabled='YES';
select avg(a) from test.index_tab;
update performance_schema.setup_consumers set enabled='NO';
eval $table_io_select 'index_tab';
# With where a = 50 inspection of
# - all rows (test.no_index_tab)
# - 1 up to maybe a few unique index values (test.index_tab, assuming that the
# optimizer decides to exploit the unique index)
# --> index_tab requires much less accesses than no_index_tab
truncate table performance_schema.events_waits_history_long;
update performance_schema.setup_consumers set enabled='YES';
select 1 as my_column from test.no_index_tab where a = 50;
update performance_schema.setup_consumers set enabled='NO';
eval $table_io_select 'no_index_tab';
truncate table performance_schema.events_waits_history_long;
update performance_schema.setup_consumers set enabled='YES';
select 1 as my_column from test.index_tab where a = 50;
update performance_schema.setup_consumers set enabled='NO';
eval $table_io_select 'index_tab';
# With where a = 50 inspection of
# - all rows (test.no_index_tab)
# - 1 up to maybe a few unique index values (test.index_tab, assuming that the
# optimizer decides to exploit the unique index)
# and remove one row for both cases
# --> index_tab requires much less accesses than no_index_tab
truncate table performance_schema.events_waits_history_long;
update performance_schema.setup_consumers set enabled='YES';
delete from test.no_index_tab where a = 51;
update performance_schema.setup_consumers set enabled='NO';
eval $table_io_select 'no_index_tab';
truncate table performance_schema.events_waits_history_long;
update performance_schema.setup_consumers set enabled='YES';
delete from test.index_tab where a = 51;
update performance_schema.setup_consumers set enabled='NO';
eval $table_io_select 'index_tab';
# In case of failures, this will tell if table io are lost.
show status like 'performance_schema_%';
# Cleanup
drop table test.no_index_tab;
drop table test.index_tab;
--source ../include/table_io_cleanup_helper.inc
|
