Uploaded image for project: 'MariaDB MaxScale'
  1. MariaDB MaxScale
  2. MXS-4636

Mechanism for throttling of write queries based on replica lag

Details

    • New Feature
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • None
    • 25.08.0
    • Filter
    • None
    • MXS-SPRINT-226, MXS-SPRINT-227

    Description

      The idea behind this filter is to ensure that we limit replica lag. The filter would delay (throttle) any query that cause goes into the binlog (any query beyond a SELECT / SHOW) if the lag in the replica is beyond a certain set limit. The delay would be for a given time and would not block completely. The use for this plugin is to ensure that replicas don't fall too far behind in an attempt to limit failover time and to ensure that one doesn't get iinto a situation where the replica just falls more and more behind, never to catch up. The latter is something that easily can happen for example in SkySQL.

      The settings would be someting like this
      replica_lag_start throttle - Replica lag in second when to start delaying queries
      replica_lag_stop_throttle - Replica lag when to stop delaying queries
      replica_mode - How to select which replica lag to consider, i,e, one of the values any (for any server lagging), all (all servers lagging). min (replica with the least replica lag)
      cluster - The monitor cluster with replias to consider
      servers - The replioca servers to consider
      delay_time - Time to delay queries
      delay_time_max - Max time to delay queries. The idea is that if a query is delayed, after delay_time has passed and the replica lag is the same as or higher than at the start of the delay, then there is another delay_time period, up to delay_time_max. The default for delay_time_max would be the same as the value for delay_time.
      query_delay_regeg - List of regex for queries to delay
      query_not_delay_regex - List of regex for queries not to delay (default is SELECT and SHOW)

      Attachments

        Issue Links

          Activity

            markus makela markus makela added a comment -

            This could be implemented as an extension of the existing throttlefilter. I've attached a quick proof-of-concept patch that implements throttling of write type queries if the lag of any server used by the service goes above the configured level.

            markus makela markus makela added a comment - This could be implemented as an extension of the existing throttlefilter. I've attached a quick proof-of-concept patch that implements throttling of write type queries if the lag of any server used by the service goes above the configured level.
            markus makela markus makela added a comment - - edited

            Another idea would be to tune the binlog group commit size whenever replication lag goes too high: https://mariadb.com/kb/en/group-commit-for-the-binary-log/

            This would naturally fit into the normal monitoring of mariadbmon that could dynamically adjust it until the replication lag goes below an acceptable level. As a mechanism, this would be more effective in that it throttles the execution of transactions while also speeding up parallel replication by committing more transactions in groups.

            One approach to this would be to set binlog_commit_wait_count to a high value and then slowly increment binlog_commit_wait_usec until the lag is at an acceptable level. This would have the effect of throttling all writes, regardless of how many parallel transactions there are.

            markus makela markus makela added a comment - - edited Another idea would be to tune the binlog group commit size whenever replication lag goes too high: https://mariadb.com/kb/en/group-commit-for-the-binary-log/ This would naturally fit into the normal monitoring of mariadbmon that could dynamically adjust it until the replication lag goes below an acceptable level. As a mechanism, this would be more effective in that it throttles the execution of transactions while also speeding up parallel replication by committing more transactions in groups. One approach to this would be to set binlog_commit_wait_count to a high value and then slowly increment binlog_commit_wait_usec until the lag is at an acceptable level. This would have the effect of throttling all writes, regardless of how many parallel transactions there are.

            I would like to add one feature that I forgot about. Being able to skip throttling for some users or trafic could be rather important. The reason is that replication is often run through MaxScale itself, and you don't want that particular trafic to be throttled, so the ability to skip throttling for replication traffic or, if this is difficult, for some named user (i.e. the user that runs the replication trafic) would be reasonable.

            karlsson Anders Karlsson added a comment - I would like to add one feature that I forgot about. Being able to skip throttling for some users or trafic could be rather important. The reason is that replication is often run through MaxScale itself, and you don't want that particular trafic to be throttled, so the ability to skip throttling for replication traffic or, if this is difficult, for some named user (i.e. the user that runs the replication trafic) would be reasonable.
            markus makela markus makela added a comment -

            The implementation for this was done by adding a feature to readwritesplit where a transaction (optionally) gets synchronized to one or more servers, thus reducing replication lag. The maximum time that a transaction's commit will wait for synchronization as well as the amount of replication lag that is acceptable are both configurable.

            Compared to the pre-emptive approach of throttling individual statements, this sort of an approach avoids any locking in the database that would happen if a statement in the middle of a transaction would need to be throttled.

            markus makela markus makela added a comment - The implementation for this was done by adding a feature to readwritesplit where a transaction (optionally) gets synchronized to one or more servers, thus reducing replication lag. The maximum time that a transaction's commit will wait for synchronization as well as the amount of replication lag that is acceptable are both configurable. Compared to the pre-emptive approach of throttling individual statements, this sort of an approach avoids any locking in the database that would happen if a statement in the middle of a transaction would need to be throttled.

            People

              markus makela markus makela
              karlsson Anders Karlsson
              Votes:
              4 Vote for this issue
              Watchers:
              8 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.