Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
Description
The InnoDB adaptive hash index is sometimes degrading the performance of InnoDB, and it is sometimes disabled to get more consistent performance. We should have a compile-time option to disable the adaptive hash index.
Let us introduce two options:
OPTION(WITH_INNODB_AHI "Include innodb_adaptive_hash_index" ON)
|
OPTION(WITH_INNODB_ROOT_GUESS "Cache index root block descriptors" ON)
|
IF(WITH_INNODB_AHI)
|
ADD_DEFINITIONS(-DBTR_CUR_HASH_ADAPT -DBTR_CUR_ADAPT)
|
IF(NOT WITH_INNODB_ROOT_GUESS)
|
MESSAGE(WARNING "WITH_INNODB_AHI implies WITH_INNODB_ROOT_GUESS")
|
ENDIF()
|
ELSEIF(WITH_INNODB_ROOT_GUESS)
|
ADD_DEFINITIONS(-DBTR_CUR_ADAPT)
|
ENDIF()
|
And let us consistently enclose all dependent code in appropriate #ifdef.
As part of this change, the misleadingly named function trx_search_latch_release_if_reserved(trx) will be replaced with the macro trx_assert_no_search_latch(trx) that will be empty unless BTR_CUR_HASH_ADAPT is defined (cmake -DWITH_INNODB_AHI=ON).
We will remove the unused column INFORMATION_SCHEMA.INNODB_TRX.TRX_ADAPTIVE_HASH_TIMEOUT.
In MariaDB Server 10.1, it used to reflect the value of trx_t::search_latch_timeout which could be adjusted during row_search_for_mysql(). In 10.2, there is no such field.
Apart from the removal of the unused column, this is an almost non-functional change to the server when using the default build options, because WITH_INNODB_AHI will be ON by default.
Attachments
Issue Links
- causes
-
MDEV-14441 InnoDB hangs when setting innodb_adaptive_hash_index=OFF during UPDATE
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Stalled
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Assignee | Marko Mäkelä [ marko ] | Jan Lindström [ jplindst ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
bb-10.2-marko completed without any test failures. But, the additional patch for testing (changing the default to WITH_INNODB_AHI=OFF) also disabled some tests. A proper final version of this patch should probably remove the AHI-related columns from INFORMATION_SCHEMA.INNODB_* views and introduce something like
--source ../include/have_innodb_ahi.inc |
to conditionally disable these tests. Perhaps the affected tests should be split so that the AHI-dependent parts would be moved to separate test files.
| Assignee | Jan Lindström [ jplindst ] | Marko Mäkelä [ marko ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Description |
The InnoDB adaptive hash index is sometimes degrading the performance of InnoDB, and it is sometimes disabled to get more consistent performance. We should have a compile-time option to disable the adaptive hash index.
Let us introduce two options: {code:cmake} OPTION(WITH_INNODB_AHI "Include innodb_adaptive_hash_index" ON) OPTION(WITH_INNODB_ROOT_GUESS "Cache index root block descriptors" ON) IF(WITH_INNODB_AHI) ADD_DEFINITIONS(-DBTR_CUR_HASH_ADAPT -DBTR_CUR_ADAPT) IF(NOT WITH_INNODB_ROOT_GUESS) MESSAGE(WARNING "WITH_INNODB_AHI implies WITH_INNODB_ROOT_GUESS") ENDIF() ELSEIF(WITH_INNODB_ROOT_GUESS) ADD_DEFINITIONS(-DBTR_CUR_ADAPT) ENDIF() {code} And let us consistently enclose all dependent code in appropriate #ifdef. As part of this change, the misleadingly named function trx_search_latch_release_if_reserved(trx) will be replaced with the macro trx_assert_no_search_latch(trx) that will be empty unless BTR_CUR_HASH_ADAPT is defined (cmake -DWITH_INNODB_AHI=ON). This is an almost non-functional change to the server when using the default build options, because WITH_INNODB_AHI will be ON by default. |
The InnoDB adaptive hash index is sometimes degrading the performance of InnoDB, and it is sometimes disabled to get more consistent performance. We should have a compile-time option to disable the adaptive hash index.
Let us introduce two options: {code:cmake} OPTION(WITH_INNODB_AHI "Include innodb_adaptive_hash_index" ON) OPTION(WITH_INNODB_ROOT_GUESS "Cache index root block descriptors" ON) IF(WITH_INNODB_AHI) ADD_DEFINITIONS(-DBTR_CUR_HASH_ADAPT -DBTR_CUR_ADAPT) IF(NOT WITH_INNODB_ROOT_GUESS) MESSAGE(WARNING "WITH_INNODB_AHI implies WITH_INNODB_ROOT_GUESS") ENDIF() ELSEIF(WITH_INNODB_ROOT_GUESS) ADD_DEFINITIONS(-DBTR_CUR_ADAPT) ENDIF() {code} And let us consistently enclose all dependent code in appropriate #ifdef. As part of this change, the misleadingly named function trx_search_latch_release_if_reserved(trx) will be replaced with the macro trx_assert_no_search_latch(trx) that will be empty unless BTR_CUR_HASH_ADAPT is defined (cmake -DWITH_INNODB_AHI=ON). We will remove the unused column INFORMATION_SCHEMA.INNODB_TRX.TRX_ADAPTIVE_HASH_TIMEOUT. In MariaDB Server 10.1, it used to reflect the value of trx_t::search_latch_timeout which could be adjusted during row_search_for_mysql(). In 10.2, there is no such field. Apart from the removal of the unused column, this is an almost non-functional change to the server when using the default build options, because WITH_INNODB_AHI will be ON by default. |
I will wait for another buildbot round of bb-10.2-marko before pushing these to 10.2:
ff0530ef681 MDEV-12121: Revert test adjustments for -DWITH_INNODB_AHI=OFF
27b9989d316 MDEV-12121 Introduce build option WITH_INNODB_AHI to disable innodb_adaptive_hash_index
| Fix Version/s | 10.2.5 [ 22117 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Attachment | mysql-5.7.17-MDEV-12121-WITH_INNODB_AHI.patch [ 43454 ] |
| Link |
This issue causes |
| Link | This issue relates to MDEV-17492 [ MDEV-17492 ] |
| Link |
This issue relates to |
| Workflow | MariaDB v3 [ 79739 ] | MariaDB v4 [ 133136 ] |
1bc5c6127da MDEV-12121 Introduce build option WITH_INNODB_AHI to disable innodb_adaptive_hash_index
For testing, I pushed an additional patch to bb-10.2-marko