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

Set innodb_adaptive_hash_index=OFF by default

Details

    Description

      Based on the performance testing that was conducted in MDEV-17492, the InnoDB adaptive hash index could only help performance in specific, almost-read-only workloads. It could slow down all kinds of workloads (especially DROP TABLE, TRUNCATE TABLE, ALTER TABLE, or DROP INDEX operations), and it can become corrupted, causing crashes (such as MDEV-18815, MDEV-20203) and possibly data corruption. Furthermore, the adaptive hash index consumes space from the InnoDB buffer pool, which could hurt performance when the working set would almost fit in the buffer pool.

      Given all this, it is best to disable the adaptive hash index.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22646 Assertion `table2->cached' failed in dict_table_t::add_to_cache

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24505 innodb.innodb-ucs2 test failed, assertion: list.count > 0, in file storage/innobase/include/ut0lst.h line 334

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35485 The test innodb.innodb_buffer_pool_resize occasionally crashes

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-12121 Introduce build option WITH_INNODB_AHI to disable innodb_adaptive_hash_index

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-17492 Benchmark AHI to find cases where it's useful

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18815 Server crashes on TRUNCATE TABLE

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20203 InnoDB: Failing assertion: (block)->index || (block)->n_pointers == 0

          • Major - Major loss of function.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31668 With certain queries, 10.6 is slower than 10.3

          • Major - Major loss of function.
          • Closed
          mentioned in

          Page Loading...

          Activity

            Ascending order - Click to sort in descending order
            View 5 older comments
            sushma1 sushma k added a comment -

            Thank you Daniel Black for the reply. So we need to place it in my.cfg file the below lines:
            skip-innodb_adaptive_hash_index

            sushma1 sushma k added a comment - Thank you Daniel Black for the reply. So we need to place it in my.cfg file the below lines: skip-innodb_adaptive_hash_index
            danblack Daniel Black added a comment -

            yes

            danblack Daniel Black added a comment - yes
            sushma1 sushma k added a comment -

            Hi Daniel,

            Is there any way that i can check if the innodb_adaptive_hash_index is enabled or not ? by using any cli command.

            sushma1 sushma k added a comment - Hi Daniel, Is there any way that i can check if the innodb_adaptive_hash_index is enabled or not ? by using any cli command.
            danblack Daniel Black added a comment -

            yes. `show global variables like 'innodb_adaptive_hash_index'`.

            Please use https://dba.stackexchange.com/ or https://mariadb.zulipchat.com for further questions. This site isn't a support forum.

            danblack Daniel Black added a comment - yes. `show global variables like 'innodb_adaptive_hash_index'`. Please use https://dba.stackexchange.com/ or https://mariadb.zulipchat.com for further questions. This site isn't a support forum.
            sushma1 sushma k added a comment -

            Okay Thank you .

            sushma1 sushma k added a comment - Okay Thank you .

            People

              marko Marko Mäkelä
              marko Marko Mäkelä
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.