diff --git a/mysql-test/suite/innodb/r/innodb_sec_index_merge_threshold.result b/mysql-test/suite/innodb/r/innodb_sec_index_merge_threshold.result new file mode 100644 index 0000000..e9705f2 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_sec_index_merge_threshold.result @@ -0,0 +1,74 @@ +# +# Expose secondary index merge threshold as a configurable setting. +# +SET @old_innodb_sec_index_merge_threshold = @@GLOBAL.innodb_sec_index_merge_threshold; +CREATE PROCEDURE p1(k BIGINT) +BEGIN +DECLARE i INT UNSIGNED DEFAULT 0; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( +a bigint(20) unsigned NOT NULL, +b tinyint(4) NOT NULL, +c bigint(20) NOT NULL, +d bigint(20) unsigned NOT NULL, +PRIMARY KEY (a, b, c), +UNIQUE KEY unique_index (a, d) +) ENGINE=InnoDB; +SET autocommit = OFF; +WHILE i < k DO +INSERT INTO t1 VALUES (i, 1, i + 1, i + 2); +SET i = i + 1; +END WHILE; +SET autocommit = ON; +END| +SET GLOBAL innodb_sec_index_merge_threshold = 100.00; +# Fill the table enough to have secondary index consist of two leaf +# pages where the right leaf page is fully packed. +CALL p1(798); +# Stop purge +SET GLOBAL innodb_purge_stop_now = ON; +# Check counters +SELECT name, count FROM information_schema.innodb_metrics +WHERE name like 'index_page_merge%'; +name count +index_page_merge_attempts 0 +index_page_merge_successful 0 +# This update should cause a split in secondary index leaf page +UPDATE t1 SET b = 3 WHERE a = 793; +# Let purge run +SET GLOBAL innodb_purge_run_now = ON; +# Wait for purge to process. It will cause a merge of split page. +# Check counters +SELECT name, count FROM information_schema.innodb_metrics +WHERE name like 'index_page_merge%'; +name count +index_page_merge_attempts 1 +index_page_merge_successful 1 +# Now try with innodb_sec_index_merge_threshold set +SET GLOBAL innodb_sec_index_merge_threshold = 96.00; +# Fill the table enough to have secondary index consist of two leaf +# pages where the right leaf page is fully packed. +CALL p1(798); +# Stop purge +SET GLOBAL innodb_purge_stop_now = ON; +# Check counters +SELECT name, count FROM information_schema.innodb_metrics +WHERE name like 'index_page_merge%'; +name count +index_page_merge_attempts 1 +index_page_merge_successful 1 +# This update should cause a split in secondary index leaf page +UPDATE t1 SET b = 3 WHERE a = 793; +# Let purge run +SET GLOBAL innodb_purge_run_now = ON; +# Wait for purge to process. It should not cause a merge. +# Check counters +SELECT name, count FROM information_schema.innodb_metrics +WHERE name like 'index_page_merge%'; +name count +index_page_merge_attempts 2 +index_page_merge_successful 1 +# cleanup +DROP PROCEDURE p1; +DROP TABLE t1; +SET @@GLOBAL.innodb_sec_index_merge_threshold = @old_innodb_sec_index_merge_threshold; diff --git a/mysql-test/suite/innodb/t/innodb_sec_index_merge_threshold-master.opt b/mysql-test/suite/innodb/t/innodb_sec_index_merge_threshold-master.opt new file mode 100644 index 0000000..ae2ea2e --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_sec_index_merge_threshold-master.opt @@ -0,0 +1 @@ +--innodb_monitor_enable=all diff --git a/mysql-test/suite/innodb/t/innodb_sec_index_merge_threshold.test b/mysql-test/suite/innodb/t/innodb_sec_index_merge_threshold.test new file mode 100644 index 0000000..308391f --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_sec_index_merge_threshold.test @@ -0,0 +1,103 @@ +--source include/have_innodb.inc +--source include/have_innodb_16k.inc +--source include/have_debug.inc +--source include/not_valgrind.inc + +--echo # +--echo # Expose secondary index merge threshold as a configurable setting. +--echo # + +SET @old_innodb_sec_index_merge_threshold = @@GLOBAL.innodb_sec_index_merge_threshold; + +delimiter |; + +CREATE PROCEDURE p1(k BIGINT) +BEGIN + DECLARE i INT UNSIGNED DEFAULT 0; + + DROP TABLE IF EXISTS t1; + CREATE TABLE t1 ( + a bigint(20) unsigned NOT NULL, + b tinyint(4) NOT NULL, + c bigint(20) NOT NULL, + d bigint(20) unsigned NOT NULL, + PRIMARY KEY (a, b, c), + UNIQUE KEY unique_index (a, d) + ) ENGINE=InnoDB; + + SET autocommit = OFF; + WHILE i < k DO + INSERT INTO t1 VALUES (i, 1, i + 1, i + 2); + SET i = i + 1; + END WHILE; + SET autocommit = ON; +END| + +delimiter ;| + +SET GLOBAL innodb_sec_index_merge_threshold = 100.00; + +--echo # Fill the table enough to have secondary index consist of two leaf +--echo # pages where the right leaf page is fully packed. +CALL p1(798); + +--echo # Stop purge +SET GLOBAL innodb_purge_stop_now = ON; + +--echo # Check counters +SELECT name, count FROM information_schema.innodb_metrics + WHERE name like 'index_page_merge%'; + +--echo # This update should cause a split in secondary index leaf page +UPDATE t1 SET b = 3 WHERE a = 793; + +--echo # Let purge run +SET GLOBAL innodb_purge_run_now = ON; + +--echo # Wait for purge to process. It will cause a merge of split page. +let $wait_condition = SELECT count(*) = 1 FROM information_schema.innodb_metrics + WHERE name = 'index_page_merge_successful' + AND count = 1; + +--source include/wait_condition.inc + +--echo # Check counters +SELECT name, count FROM information_schema.innodb_metrics + WHERE name like 'index_page_merge%'; + +--echo # Now try with innodb_sec_index_merge_threshold set +SET GLOBAL innodb_sec_index_merge_threshold = 96.00; + +--echo # Fill the table enough to have secondary index consist of two leaf +--echo # pages where the right leaf page is fully packed. +CALL p1(798); + +--echo # Stop purge +SET GLOBAL innodb_purge_stop_now = ON; + +--echo # Check counters +SELECT name, count FROM information_schema.innodb_metrics + WHERE name like 'index_page_merge%'; + +--echo # This update should cause a split in secondary index leaf page +UPDATE t1 SET b = 3 WHERE a = 793; + +--echo # Let purge run +SET GLOBAL innodb_purge_run_now = ON; + +--echo # Wait for purge to process. It should not cause a merge. +let $wait_condition = SELECT count(*) = 1 FROM information_schema.innodb_metrics + WHERE name = 'index_page_merge_attempts' + AND count > 1; + +--source include/wait_condition.inc + +--echo # Check counters +SELECT name, count FROM information_schema.innodb_metrics + WHERE name like 'index_page_merge%'; + +--echo # cleanup +DROP PROCEDURE p1; +DROP TABLE t1; + +SET @@GLOBAL.innodb_sec_index_merge_threshold = @old_innodb_sec_index_merge_threshold; diff --git a/mysql-test/suite/sys_vars/r/innodb_sec_index_merge_threshold_basic.result b/mysql-test/suite/sys_vars/r/innodb_sec_index_merge_threshold_basic.result new file mode 100644 index 0000000..dc9fa04 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/innodb_sec_index_merge_threshold_basic.result @@ -0,0 +1,112 @@ +SET @old_innodb_sec_index_merge_threshold = @@global.innodb_sec_index_merge_threshold; +SELECT @old_innodb_sec_index_merge_threshold; +@old_innodb_sec_index_merge_threshold +100 +# Default value +SET @@global.innodb_sec_index_merge_threshold = DEFAULT; +SELECT @@global.innodb_sec_index_merge_threshold; +@@global.innodb_sec_index_merge_threshold +100.000000 +# Scope +SET innodb_sec_index_merge_threshold = 1.0; +ERROR HY000: Variable 'innodb_sec_index_merge_threshold' is a GLOBAL variable and should be set with SET GLOBAL +SELECT @@innodb_sec_index_merge_threshold; +@@innodb_sec_index_merge_threshold +100.000000 +SET GLOBAL innodb_sec_index_merge_threshold = 1.0; +SELECT @@global.innodb_sec_index_merge_threshold; +@@global.innodb_sec_index_merge_threshold +1.000000 +# Min/Max +SET @@global.innodb_sec_index_merge_threshold = -1; +Warnings: +Warning 1292 Truncated incorrect innodb_sec_index_merge_threshold value: '-1' +SELECT @@global.innodb_sec_index_merge_threshold; +@@global.innodb_sec_index_merge_threshold +1.000000 +SET @@global.innodb_sec_index_merge_threshold = -0.01; +Warnings: +Warning 1292 Truncated incorrect innodb_sec_index_merge_threshold value: '-0.01' +SELECT @@global.innodb_sec_index_merge_threshold; +@@global.innodb_sec_index_merge_threshold +1.000000 +SET @@global.innodb_sec_index_merge_threshold = 1.0; +SELECT @@global.innodb_sec_index_merge_threshold; +@@global.innodb_sec_index_merge_threshold +1.000000 +SET @@global.innodb_sec_index_merge_threshold = 1.1; +SELECT @@global.innodb_sec_index_merge_threshold; +@@global.innodb_sec_index_merge_threshold +1.100000 +SET @@global.innodb_sec_index_merge_threshold = 99; +SELECT @@global.innodb_sec_index_merge_threshold; +@@global.innodb_sec_index_merge_threshold +99.000000 +SET @@global.innodb_sec_index_merge_threshold = 99.9; +SELECT @@global.innodb_sec_index_merge_threshold; +@@global.innodb_sec_index_merge_threshold +99.900000 +SET @@global.innodb_sec_index_merge_threshold = 100; +SELECT @@global.innodb_sec_index_merge_threshold; +@@global.innodb_sec_index_merge_threshold +100.000000 +SET @@global.innodb_sec_index_merge_threshold = 100.1; +Warnings: +Warning 1292 Truncated incorrect innodb_sec_index_merge_threshold value: '100.1' +SELECT @@global.innodb_sec_index_merge_threshold; +@@global.innodb_sec_index_merge_threshold +100.000000 +SET @@global.innodb_sec_index_merge_threshold = 101; +Warnings: +Warning 1292 Truncated incorrect innodb_sec_index_merge_threshold value: '101' +SELECT @@global.innodb_sec_index_merge_threshold; +@@global.innodb_sec_index_merge_threshold +100.000000 +# Invalid value +SET @@global.innodb_sec_index_merge_threshold = -1; +Warnings: +Warning 1292 Truncated incorrect innodb_sec_index_merge_threshold value: '-1' +SELECT @@global.innodb_sec_index_merge_threshold; +@@global.innodb_sec_index_merge_threshold +1.000000 +SET @@global.innodb_sec_index_merge_threshold = "T"; +ERROR 42000: Incorrect argument type to variable 'innodb_sec_index_merge_threshold' +SELECT @@global.innodb_sec_index_merge_threshold; +@@global.innodb_sec_index_merge_threshold +1.000000 +SET @@global.innodb_sec_index_merge_threshold = "Y"; +ERROR 42000: Incorrect argument type to variable 'innodb_sec_index_merge_threshold' +SELECT @@global.innodb_sec_index_merge_threshold; +@@global.innodb_sec_index_merge_threshold +1.000000 +SET @@global.innodb_sec_index_merge_threshold = 1001; +Warnings: +Warning 1292 Truncated incorrect innodb_sec_index_merge_threshold value: '1001' +SELECT @@global.innodb_sec_index_merge_threshold; +@@global.innodb_sec_index_merge_threshold +100.000000 +SET @@global.innodb_sec_index_merge_threshold = OFF; +ERROR 42000: Incorrect argument type to variable 'innodb_sec_index_merge_threshold' +SELECT @@global.innodb_sec_index_merge_threshold; +@@global.innodb_sec_index_merge_threshold +100.000000 +SET @@global.innodb_sec_index_merge_threshold = ON; +ERROR 42000: Incorrect argument type to variable 'innodb_sec_index_merge_threshold' +SELECT @@global.innodb_sec_index_merge_threshold; +@@global.innodb_sec_index_merge_threshold +100.000000 +SET @@global.innodb_sec_index_merge_threshold = TRUE; +SELECT @@global.innodb_sec_index_merge_threshold; +@@global.innodb_sec_index_merge_threshold +1.000000 +SET @@global.innodb_sec_index_merge_threshold = FALSE; +Warnings: +Warning 1292 Truncated incorrect innodb_sec_index_merge_threshold value: '0' +SELECT @@global.innodb_sec_index_merge_threshold; +@@global.innodb_sec_index_merge_threshold +1.000000 +# Reset +SET @@global.innodb_sec_index_merge_threshold = @old_innodb_sec_index_merge_threshold; +SELECT @@global.innodb_sec_index_merge_threshold; +@@global.innodb_sec_index_merge_threshold +100.000000 diff --git a/mysql-test/suite/sys_vars/t/innodb_sec_index_merge_threshold_basic.test b/mysql-test/suite/sys_vars/t/innodb_sec_index_merge_threshold_basic.test new file mode 100644 index 0000000..d16d05f --- /dev/null +++ b/mysql-test/suite/sys_vars/t/innodb_sec_index_merge_threshold_basic.test @@ -0,0 +1,77 @@ +--source include/have_innodb.inc + +SET @old_innodb_sec_index_merge_threshold = @@global.innodb_sec_index_merge_threshold; +SELECT @old_innodb_sec_index_merge_threshold; + +--echo # Default value +SET @@global.innodb_sec_index_merge_threshold = DEFAULT; +SELECT @@global.innodb_sec_index_merge_threshold; + +--echo # Scope +--error ER_GLOBAL_VARIABLE +SET innodb_sec_index_merge_threshold = 1.0; +SELECT @@innodb_sec_index_merge_threshold; + +SET GLOBAL innodb_sec_index_merge_threshold = 1.0; +SELECT @@global.innodb_sec_index_merge_threshold; + +--echo # Min/Max +SET @@global.innodb_sec_index_merge_threshold = -1; +SELECT @@global.innodb_sec_index_merge_threshold; + +SET @@global.innodb_sec_index_merge_threshold = -0.01; +SELECT @@global.innodb_sec_index_merge_threshold; + +SET @@global.innodb_sec_index_merge_threshold = 1.0; +SELECT @@global.innodb_sec_index_merge_threshold; + +SET @@global.innodb_sec_index_merge_threshold = 1.1; +SELECT @@global.innodb_sec_index_merge_threshold; + +SET @@global.innodb_sec_index_merge_threshold = 99; +SELECT @@global.innodb_sec_index_merge_threshold; + +SET @@global.innodb_sec_index_merge_threshold = 99.9; +SELECT @@global.innodb_sec_index_merge_threshold; + +SET @@global.innodb_sec_index_merge_threshold = 100; +SELECT @@global.innodb_sec_index_merge_threshold; + +SET @@global.innodb_sec_index_merge_threshold = 100.1; +SELECT @@global.innodb_sec_index_merge_threshold; + +SET @@global.innodb_sec_index_merge_threshold = 101; +SELECT @@global.innodb_sec_index_merge_threshold; + +--echo # Invalid value +SET @@global.innodb_sec_index_merge_threshold = -1; +SELECT @@global.innodb_sec_index_merge_threshold; + +--error ER_WRONG_TYPE_FOR_VAR +SET @@global.innodb_sec_index_merge_threshold = "T"; +SELECT @@global.innodb_sec_index_merge_threshold; + +--error ER_WRONG_TYPE_FOR_VAR +SET @@global.innodb_sec_index_merge_threshold = "Y"; +SELECT @@global.innodb_sec_index_merge_threshold; + +SET @@global.innodb_sec_index_merge_threshold = 1001; +SELECT @@global.innodb_sec_index_merge_threshold; + +--error ER_WRONG_TYPE_FOR_VAR +SET @@global.innodb_sec_index_merge_threshold = OFF; +SELECT @@global.innodb_sec_index_merge_threshold; + +--error ER_WRONG_TYPE_FOR_VAR +SET @@global.innodb_sec_index_merge_threshold = ON; +SELECT @@global.innodb_sec_index_merge_threshold; + +SET @@global.innodb_sec_index_merge_threshold = TRUE; +SELECT @@global.innodb_sec_index_merge_threshold; + +SET @@global.innodb_sec_index_merge_threshold = FALSE; +SELECT @@global.innodb_sec_index_merge_threshold; + +--echo # Reset +SET @@global.innodb_sec_index_merge_threshold = @old_innodb_sec_index_merge_threshold; +SELECT @@global.innodb_sec_index_merge_threshold; diff --git a/storage/innobase/btr/btr0btr.cc b/storage/innobase/btr/btr0btr.cc index dd1afd6..21dcfcc 100644 --- a/storage/innobase/btr/btr0btr.cc +++ b/storage/innobase/btr/btr0btr.cc @@ -4980,6 +4980,20 @@ btr_can_merge_with_page( goto error; } + /* For unique secondary indexes an update to PK columns can + result in a split at time of update and a subsequent merge + when purge processes the delete marked record. To avoid this + thrashing between split/merge we merge unique secondary index + leaf pages if we are going to have user configured free space + in the page after the merge. */ + if (!dict_index_is_clust(index) + && dict_index_is_unique(index) + && page_is_leaf(mpage) + && (page_get_data_size(mpage) + data_size + >= (ulint)((srv_sec_index_merge_threshold + * UNIV_PAGE_SIZE) / 100))) { + goto error; + } max_ins_size = page_get_max_insert_size(mpage, n_recs); diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index 00a7ffe..618c929 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -16707,6 +16707,13 @@ static MYSQL_SYSVAR_DOUBLE(index_fill_factor, srv_index_fill_factor, "of free space. Only applies to clustered indexes.", NULL, NULL, 93.75, 0.0, 100.0, 0); +static MYSQL_SYSVAR_DOUBLE(sec_index_merge_threshold, + srv_sec_index_merge_threshold, + PLUGIN_VAR_RQCMDARG, + "The percentage of how much to fill a leaf page of unique secondary " + "index when deciding on btree merge.", + NULL, NULL, 100, 1.0, 100.0, 0); + static MYSQL_SYSVAR_BOOL(lease_fragment_extents, srv_lease_fragment_extents, PLUGIN_VAR_NOCMDARG, "If a free fragment extent is available, allow it to be allocated to " @@ -16862,6 +16869,7 @@ static struct st_mysql_sys_var* innobase_system_variables[]= { MYSQL_SYSVAR(purge_batch_size), MYSQL_SYSVAR(segment_fill_factor), MYSQL_SYSVAR(index_fill_factor), + MYSQL_SYSVAR(sec_index_merge_threshold), MYSQL_SYSVAR(lease_fragment_extents), MYSQL_SYSVAR(reserve_free_extents), MYSQL_SYSVAR(free_extents_reservation_factor), diff --git a/storage/innobase/include/srv0srv.h b/storage/innobase/include/srv0srv.h index 24ada33..8c01c90 100644 --- a/storage/innobase/include/srv0srv.h +++ b/storage/innobase/include/srv0srv.h @@ -248,6 +248,7 @@ extern ulong srv_auto_extend_increment; extern double srv_segment_fill_factor; extern double srv_index_fill_factor; +extern double srv_sec_index_merge_threshold; extern my_bool srv_lease_fragment_extents; extern my_bool srv_reserve_free_extents; diff --git a/storage/innobase/srv/srv0srv.cc b/storage/innobase/srv/srv0srv.cc index 8ff467a..43cabad 100644 --- a/storage/innobase/srv/srv0srv.cc +++ b/storage/innobase/srv/srv0srv.cc @@ -184,6 +184,10 @@ UNIV_INTERN double srv_segment_fill_factor = 87.50; remaining space for record growth (e.g. updates). */ UNIV_INTERN double srv_index_fill_factor = 93.75; +/* Target percentage for how much to fill leaf pages of unique +secondary index when merging two pages. */ +UNIV_INTERN double srv_sec_index_merge_threshold = 100; + /* Allocate a free fragment to a segment before taking an extent from the segment/space free list. */ UNIV_INTERN my_bool srv_lease_fragment_extents = FALSE;