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

Implement table and index options to enable/disable ADAPTIVE_HASH_INDEX

    XMLWordPrintable

Details

    Description

      Note: The scope of this MDEV has been updated to exclusively cover ADAPTIVE_HASH_INDEX options. The QUERY_CACHE and BINLOG_ROW per-table options have been split into MDEV-39949 and MDEV-39950.

      Some of the features in MariaDB are only usable (from performance point of view) in special circumstances:

      • (Moved to MDEV-39949) QUERY_CACHE should no be used with tables that changes values very frequently
      • InnoDB adaptive_hash_index does not work well if there is changes in the rows that are part if an active adaptive_hash
      • (Moved to MDEV-39950) Statement based binary logging is not optimal for tables that does updates where the master has to scan a lot but only updates a few or inserts of many rows with generated auto-increments.

      This MDEV is about fixing this issue by adding new table specific options where the user can enable/disable the options per table.

      All table optimization should have the 3 values: ON, OFF and DEFAULT. 'DEFAULT' is needed to be able to remove the option from being stored in the table definition.

      The suggested new table options are:

      Option Effect
      QUERY_CACHE (Moved to MDEV-39949) Having this option OFF is the same as using SELECT SQL_NO_CACHE for any query using this table. Having this option ON is same as using SELECT SQL_CACHE for this table and the query will be cached if no other table has QUERY_CACHE=OFF
      ADAPTIVE_HASH_INDEX Having this option OFF will disable InnoDB adaptive hash index for this table. Having it ON will enable adaptive hash index for the table if adaptive hash indexes are enabled at all
      BINLOG_ROW (Moved to MDEV-39950) If this option is ON for any table, row based binary logging will be used

      Attachments

        Issue Links

          relates to

          New Feature - A new feature of the product, which has yet to be developed. MDEV-38305 Expose adaptive hash index statistics in ANALYZE FORMAT=JSON

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-39330 AHI disable command can be ignored during buffer pool resize

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

          Task - A task that needs to be done. MDEV-39401 Buffer AHI usage counters btr_cur_n_*sea in non-atomic members of trx_t

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-39452 22% reduction of sizeof(dict_index_t) is easily possible

          • Major - Major loss of function.
          • Open

          New Feature - A new feature of the product, which has yet to be developed. MDEV-39851 Add per-index "shrink" option to control record-growing UPDATE optimization

          • Major - Major loss of function.
          • Open

          New Feature - A new feature of the product, which has yet to be developed. MDEV-39852 Make per-index options modifiable via instant DDL

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-20487 Set innodb_adaptive_hash_index=OFF by default

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-37815 refactor engine attributes in partitioning

          • Major - Major loss of function.
          • Closed
          split to

          New Feature - A new feature of the product, which has yet to be developed. MDEV-39949 Add per-table "QUERY_CACHE" option

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

          New Feature - A new feature of the product, which has yet to be developed. MDEV-39950 Add per-table "BINLOG_ROW" option

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open
          has action item

          DOCS-6133 Loading...

          Activity

            People

              alessandro.vetere Alessandro Vetere
              monty Michael Widenius
              Alessandro Vetere Alessandro Vetere
              Votes:
              0 Vote for this issue
              Watchers:
              11 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - 1d Original Estimate - 1d
                  1d
                  Remaining:
                  Remaining Estimate - 0d
                  0d
                  Logged:
                  Time Spent - 24d 4h 50m
                  24d 4h 50m

                  Git Integration

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