Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-28540

Deprecate and ignore the parameter innodb_prefix_index_cluster_optimization

Details

    Description

      The parameter innodb_prefix_index_cluster_optimization enables an optimization that was added in MDEV-6929. Should the optimization turn out to be incorrect and broken beyond repair in our stress testing, it will have to be reverted. If it is correct, there should be no point to disable it by default.

      Related to this, the counters Innodb_secondary_index_triggered_cluster_reads and Innodb_secondary_index_triggered_cluster_reads_avoided are determining the usefulness of this optimization. If we hard-wire the optimization to enabled or disabled, these counters should not serve any useful purpose. As noted in MDEV-21212, frequently updating global counters is not for free.

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-6929 Port Facebook Prefix Index Queries Optimization

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21212 buf_page_get_gen -> buf_pool->stat.n_page_gets++ is a cpu waste (0.5-1%)

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            marko Marko Mäkelä added a comment -

            A section of the test innodb.monitor revealed that this feature is actually buggy:

            create table monitor_test(a char(3), b int, c char(2),
            		 
            primary key (a(1), c(1)), key(b)) engine = innodb;
            		 
            insert into monitor_test values("13", 2, "aa");
            		 
            select a from monitor_test where b < 3;

            This would incorrectly return the prefix '1' of the string '13'. The fix is simple:

            diff --git a/storage/innobase/row/row0sel.cc b/storage/innobase/row/row0sel.cc
            		 
            index 01cf3b107a8..8afabf6220c 100644
            		 
            --- a/storage/innobase/row/row0sel.cc
            		 
            +++ b/storage/innobase/row/row0sel.cc
            		 
            @@ -4202,7 +4202,9 @@ bool row_search_with_covering_prefix(
            		 
             	const dict_index_t*	index = prebuilt->index;
            		 
             	ut_ad(!dict_index_is_clust(index));
            		 
             
            -	if (dict_index_is_spatial(index)) {
            		 
            +	/* In ha_innobase::build_template() we choose to access the
            		 
            +	whole row when using exclusive row locks. */
            		 
            +	if (prebuilt->select_lock_type == LOCK_X || !index->is_btree()) {
            		 
             		return false;
            		 
             	}

            marko Marko Mäkelä added a comment - A section of the test innodb.monitor revealed that this feature is actually buggy: create table monitor_test(a char (3), b int , c char (2), primary key (a(1), c(1)), key (b)) engine = innodb; insert into monitor_test values ( "13" , 2, "aa" ); select a from monitor_test where b < 3; This would incorrectly return the prefix '1' of the string '13' . The fix is simple: diff --git a/storage/innobase/row/row0sel.cc b/storage/innobase/row/row0sel.cc index 01cf3b107a8..8afabf6220c 100644 --- a/storage/innobase/row/row0sel.cc +++ b/storage/innobase/row/row0sel.cc @@ -4202,7 +4202,9 @@ bool row_search_with_covering_prefix( const dict_index_t* index = prebuilt->index; ut_ad(!dict_index_is_clust(index)); - if (dict_index_is_spatial(index)) { + /* In ha_innobase::build_template() we choose to access the + whole row when using exclusive row locks. */ + if (prebuilt->select_lock_type == LOCK_X || !index->is_btree()) { return false; }
            marko Marko Mäkelä added a comment -

            I conducted a quick performance test on NVMe, using Sysbench oltp_update_index, 30-second runs, 8×100,000 rows, 8GiB buffer pool:

            revision 20 40 80 160
            experiment: disable some non-InnoDB counters 42572.27 84046.06 148651.85 158132.39
            MDEV-28540 remove 2 counters 42612.63 87023.75 149086.85 159434.07
            MDEV-28539 remove several counters 44296.74 85968.64 148615.14 145086.68
            baseline 43836.00 86088.89 149828.29 153944.74

            This test is too short to draw any conclusions, but it does look somewhat promising.

            marko Marko Mäkelä added a comment - I conducted a quick performance test on NVMe, using Sysbench oltp_update_index , 30-second runs, 8×100,000 rows, 8GiB buffer pool: revision 20 40 80 160 experiment: disable some non-InnoDB counters 42572.27 84046.06 148651.85 158132.39 MDEV-28540 remove 2 counters 42612.63 87023.75 149086.85 159434.07 MDEV-28539 remove several counters 44296.74 85968.64 148615.14 145086.68 baseline 43836.00 86088.89 149828.29 153944.74 This test is too short to draw any conclusions, but it does look somewhat promising.

            People

              marko Marko Mäkelä
              marko Marko Mäkelä
              Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.