| Bug #98866 | Disable AHI in heavy system could very high load spike | ||
|---|---|---|---|
| Submitted: | 7 Mar 2020 1:35 | Modified: | 14 Oct 2023 2:07 |
| Reporter: | Fangxin Flou (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Storage Engines | Severity: | S4 (Feature request) |
| Version: | All | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[7 Mar 2020 1:35]
Fangxin Flou
Patch to disable AHI gracefully
Attachment: graceful_disable_ahi.log (application/octet-stream, text), 4.54 KiB.
[9 Mar 2020 13:56]
MySQL Verification Team
Hi Mr. Flou, Thank you for your bug report. However, this is not a bug. At best, this is a feature request. Do you agree with that ??? We already have startup options for AHI enabling and disabling, as well as other options for managing the AHI. So, far we concluded that doing it dynamically would be dangerous. Hence, we would truly like to know whether you have tested your patch under the conditions of very high load and very high concurrency. Also, this patch needs to be tested in conditions when there are many concurrent reads that are using and locking AHI in order to perform faster reads. Have you performed all these tests ????
[18 Mar 2020 12:13]
Fangxin Flou
Agree!
[18 Mar 2020 14:25]
MySQL Verification Team
Verified as a feature request.
[14 Oct 2023 2:07]
Marcin Babij
Posted by developer: This behavior was improved in 8.0.32 with: InnoDB: Several adaptive hash index (AHI) code optimizations and improvements were implemented, addressing various issues including potential race conditions. (Bug #33601434) with a fix similar to proposed. The problem should not be visible now. Closing this bug. Thank you for reporting the issue.
[16 Oct 2023 10:33]
MySQL Verification Team
Thank you, Marcin, for your comments.
Description: On a instance we found that AHI hits ratio is not good enough, and we need to disable it for better performance. We got a very high load spike at that time, after reading the code we found that btr_search_disable will hold the dict_sys->mutex and wait for locking all the adaptive hash partitions's lock. void btr_search_disable(bool need_mutex) { dict_table_t *table; if (need_mutex) { mutex_enter(&dict_sys->mutex); } ut_ad(mutex_own(&dict_sys->mutex)); btr_search_x_lock_all(); if (!btr_search_enabled) { if (need_mutex) { mutex_exit(&dict_sys->mutex); } btr_search_x_unlock_all(); return; } btr_search_enabled = false; ...... How to repeat: build an instance and benchmark the AHI with read/write traffics. And enable/disable AHI dynamically and watch the QPS in second. Suggested fix: Introduce a disable_in_progress status, to disable the AHI access first and then disable the AHI completely. void btr_search_disable(bool need_mutex) { dict_table_t *table; /* set disalbe_in_progress and sleep for a while */ btr_search_disable_in_progress = true; os_thread_sleep(100000); if (need_mutex) { mutex_enter(&dict_sys->mutex); } ut_ad(mutex_own(&dict_sys->mutex)); btr_search_x_lock_all(); if (!btr_search_enabled) { if (need_mutex) { mutex_exit(&dict_sys->mutex); } btr_search_disable_in_progress = false; btr_search_x_unlock_all(); return; } btr_search_enabled = false; btr_search_disable_in_progress = false; ....