Contrib / RFC Fake PR : Bug#117740 - Counters for Slow InnoDB Sync Reads.#17
Draft
jfg956 wants to merge 1 commit into9.2.0_for_fake_prsfrom
Draft
Contrib / RFC Fake PR : Bug#117740 - Counters for Slow InnoDB Sync Reads.#17jfg956 wants to merge 1 commit into9.2.0_for_fake_prsfrom
jfg956 wants to merge 1 commit into9.2.0_for_fake_prsfrom
Conversation
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
1 participant
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
This PR implements Counters for Slow InnoDB Sync Reads, partially fulfilling the feature request of Bug #117740 : Please consider adding Slow IO Counters. More about the reasons why this is needed in Bug #117740.
This is a "Contrib / RFC Fake PR". It is there so people can comment on this work in case it needs adjustments. This has been contributed on 2025-03-18 as a patch file in Bug #117740. More about Fake PR in a previous RFC blog post, section Fake PRs and my Way of Working on MySQL Contributions.
This PR merges on 9.2.0. Adapting it to 8.4 and 8.0 should be little work.
For implementing counters for Slow InnoDB Sync Reads, this PR introduces a new global variable :
innodb_buffer_pool_read_sync_slow_io_threshold_usec. The default value is 1 hour, which should not trigger any increase of the slow counters. For monitoring slow InnoDB Sync Reads, this threshold should be set in such a way that the counters do not increase most of the time, and increase significantly when the IO subsystem is misbehaving (I cannot tell you exactly how to set this up because it will depend on your IO subsystem, but a value close to p99 of its IO latency might be good). Note that occasional increase of the counters should not be interpreted as a misbehaving IO subsystem because tail latencies will always happen.The names of the InnoDB Metric of the four counters introduced by this PR, with the matching global status in parentheses, are :
buf_pool_reads_sync_io_count(innodb_buffer_pool_reads_sync_io_count);buf_pool_reads_sync_io_wait_usec(innodb_buffer_pool_reads_sync_io_wait_usec);buf_pool_reads_sync_io_slow_count(innodb_buffer_pool_reads_sync_io_slow_count);buf_pool_reads_sync_io_slow_wait_usec(innodb_buffer_pool_reads_sync_io_slow_wait_usec).The first two counters increase for all Sync Read IOs, and the last two only when the wait time of an IO is above the threshold.
I blogged about this in Contrib RFC: Counters for Slow InnoDB Sync Reads.
I think that this PR should not bring any significant performance degradation, but I am yet to fully validate this. I might do this in the next weeks / months and add details in the bug and in this PR. Also in the next weeks / months and if time allows, I might submit an improved version of this PR with tests, and with adjustment taking into account the feedback I might have received.