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

Improve replication lag observability for binlog_alter_two_phase DDL

    XMLWordPrintable

Details

    Description

      `Seconds_Behind_Master` is misleading for long-running DDL when `binlog_alter_two_phase=ON`, because the effective replica lag can be near zero while SBM still appears large during execution.
      When `binlog_alter_two_phase` is enabled, a long-running `ALTER TABLE` is split in the binary log into `START ALTER` and `COMMIT ALTER` / `ROLLBACK ALTER`. This allows the replica to start executing the DDL much earlier, often nearly in parallel with the primary, which is effective in reducing real replication lag caused by heavy DDL.
      However, in this scenario, `Seconds_Behind_Master` (SBM) appears misleading for operational monitoring. During a long-running two-phase ALTER, the replica may already be executing the same DDL concurrently with the primary, but SBM can still grow significantly while the DDL is running. This can look like severe replication lag from a monitoring perspective, even though after the primary finishes, the replica may complete almost immediately, meaning the effective lag is actually close to zero.
      In this report, "effective replica lag" means:
      replica DDL completion time - primary DDL completion time.
      Environment:

      • Primary/replica replication topology
      • MariaDB 10.4.18
      • MariaDB 10.11.18 with `binlog_alter_two_phase=OFF`
      • MariaDB 10.11.18 with `binlog_alter_two_phase=ON`
        Representative test results:
        1. Charset conversion on varchar column (`utf8 -> utf8mb4`), about 5.5 million rows, indexed column (`extra_col`)
      • 10.4.18: primary runtime ~44s, SBM ~147s, effective replica lag ~44s
      • 10.11.18 OFF: primary runtime ~31s, SBM ~62s, effective replica lag ~33s
      • 10.11.18 ON: primary runtime ~43s, SBM ~43s, effective replica lag ~0s
        2. `ADD INDEX`
      • 10.4.18: primary runtime ~19s, SBM ~97s, effective lag ~14s
      • 10.11.18 OFF: primary runtime ~12s, SBM ~25s, effective lag ~14s
      • 10.11.18 ON: primary runtime ~19s, SBM ~19s, effective lag ~0s
        3. `varchar -> text` (non-indexed column)
      • 10.4.18: primary runtime ~46s, SBM ~149s, effective lag ~48s
      • 10.11.18 OFF: primary runtime ~31s, SBM ~62s, effective lag ~31s
      • 10.11.18 ON: primary runtime ~42s, SBM ~41s, effective lag ~0s
        Conclusion:
        `binlog_alter_two_phase=ON` clearly reduces the real replication lag of COPY-like DDL (charset conversion, type conversion, add index) to near zero. However, SBM still does not represent the operationally meaningful lag in this scenario. Examples from the tests:
      • SBM ~43s while effective lag ~0s
      • SBM ~41s while effective lag ~0s
      • SBM ~19s while effective lag ~0s
        This can easily confuse DBAs and monitoring systems, and alerting based only on SBM may significantly overestimate practical lag during two-phase DDL execution.
        Please consider improving observability for this case, for example by:
      • adding a dedicated status field or metric for two-phase DDL progress or effective lag;
      • providing a lag metric that better reflects replica completion time minus primary completion time for two-phase DDL;
      • or documenting more explicitly that `Seconds_Behind_Master` is not a reliable indicator of effective lag for `binlog_alter_two_phase`.

      Attachments

        Activity

          People

            Unassigned Unassigned
            lzp7777 Mr.lin
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.