| 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 |