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

New definition for Seconds_Behind_Master

    XMLWordPrintable

Details

    • New Feature
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 11.6
    • None
    • None

    Description

      The current definition of Seconds_Behind_Master is both complex and confusing.
      This can be seen in the documentation of the variable at:
      https://mariadb.com/kb/en/delayed-replication/

      The current value is also not very useful as the value can have strange values in the case the master has long pauses between entries.

      The suggested changes are:

      • Parallel execution uses the time AFTER execution while 'normal' replication used the value BEFORE to calculate Seconds_Behind_Master. This should be changed to always use AFTER.
      • Change Seconds_Behind_Master to have a more well defined meaning: 'The slave data is snapshot XXX seconds behind the data on the master. This can be calculated by using the formula: Newest event completion time from master (when writing to the relay log) - Completion time for last committed event from the master. If the slave is 'idle' then the Seconds_Behind_Master should be 0.

      As an example with SQL_DELAY=5 hours:

      • IO thread reads an event that was completed at 2023:01:01 06:01:01 on the master
      • The SQL thread commits later an event that was originally committed at 2023:01:01 01:01:01

      In this case the Seconds_Behind_master will be 5 hours.

      The benefit of this idea is that 'Seconds_Behind_Master' will be well defined in all of the following cases:

      • Without and without delayed replication
      • When the master has been down and is just coming up.
      • If the master has been 'idle' for some time.
      • When the slave is stopped and later restarted (Seconds_Behind_Master will be up to date as soon as IO thread is up to date). We could have make the value 'correct' from start by setting Newest event completion time from master as 'master current time' when connecting.

      Attachments

        Activity

          People

            Elkin Andrei Elkin
            monty Michael Widenius
            Votes:
            2 Vote for this issue
            Watchers:
            8 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.