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

Merge 5.7 P_S replication table 'replication_applier_status_by_worker'

Details

    Description

      On a replication slave that has parallel replication enabled, if slave_parallel_max_queued is set too small, then the SQL thread's queue can sometimes be too small to assign work to all of the slave worker threads. When this happens, the SQL thread's state will be "Waiting for room in worker thread event", and the idle worker thread's state will be "Waiting for work from SQL thread".

      |  4 | system user |           | NULL | Connect |    139 | Waiting for work from SQL thread              | NULL             |    0.000 |
      ...
      | 21 | system user |           | NULL | Connect |     45 | Waiting for room in worker thread event queue | NULL             |    0.000 |
      ...
      

      https://mariadb.com/kb/en/library/parallel-replication/#configuring-the-maximum-size-of-the-parallel-slave-queue

      https://mariadb.com/kb/en/library/replication-and-binary-log-system-variables/#slave_parallel_max_queued

      I think it would be useful to add a status variable that gets incremented if slave's parallel worker thread is idle because the SQL thread's queue is full.

      Attachments

        Activity

          Hello serg, GeoffMontee and Elkin

          As part of current task can we add a new `performance schema` table similar to upstream?
          The table can capture following details. It can be extended further to include few more details.

          performance_schema.replication_applier_status_by_worker

          Column Name Value
          THREAD_ID None
          SERVICE_STATE None
          LAST_SEEN_TRANSACTION None
          LAST_ERROR_NUMBER Last_SQL_Errno
          LAST_ERROR_MESSAGE Last_SQL_Error
          LAST_ERROR_TIMESTAMP Last_SQL_Error_Timestamp
          WORKER_IDLE_TIME Total idle time by worker "Waiting for work from SQL thread"
          LAST_TRANS_WAIT_FOR_COMMIT_ORDER Total waiting time spent by current transaction to reach its turn for commit
          LAST_TRANS_RETRY_COUNT Total number retries attempted by last transaction
          sujatha.sivakumar Sujatha Sivakumar (Inactive) added a comment - Hello serg , GeoffMontee and Elkin As part of current task can we add a new `performance schema` table similar to upstream? The table can capture following details. It can be extended further to include few more details. performance_schema.replication_applier_status_by_worker Column Name Value THREAD_ID None SERVICE_STATE None LAST_SEEN_TRANSACTION None LAST_ERROR_NUMBER Last_SQL_Errno LAST_ERROR_MESSAGE Last_SQL_Error LAST_ERROR_TIMESTAMP Last_SQL_Error_Timestamp WORKER_IDLE_TIME Total idle time by worker "Waiting for work from SQL thread" LAST_TRANS_WAIT_FOR_COMMIT_ORDER Total waiting time spent by current transaction to reach its turn for commit LAST_TRANS_RETRY_COUNT Total number retries attempted by last transaction

          Hi sujatha.sivakumar,

          That sounds good to me.

          GeoffMontee Geoff Montee (Inactive) added a comment - Hi sujatha.sivakumar , That sounds good to me.
          sujatha.sivakumar Sujatha Sivakumar (Inactive) added a comment - Hello serg Can you please review the changes for MDEV-20220 . Patch: https://github.com/MariaDB/server/commit/0922edb1b162c5aa73c11867157e0e118f79bbc6 Test results: http://buildbot.askmonty.org/buildbot/grid?category=main&branch=bb-10.5-sujatha

          Hello serg

          Since fix version is 10.6, ported my 10.5 changes to 10.6 and tested them.

          Please find the 10.6 patch: https://github.com/MariaDB/server/commit/1d5f0db0158c5a4c2e82abd3b881de46a719412d
          Test results: http://buildbot.askmonty.org/buildbot/grid?category=main&branch=bb-10.6-sujatha

          This patch is for 'replication_applier_status_by_worker' table alone.
          Rest of the tables are implemented as part of MDEV-16437.

          Current patch will be used as base of MDEV-16437 and each PFS table specific change will be added as a separate commit.

          Thank you.

          sujatha.sivakumar Sujatha Sivakumar (Inactive) added a comment - Hello serg Since fix version is 10.6, ported my 10.5 changes to 10.6 and tested them. Please find the 10.6 patch: https://github.com/MariaDB/server/commit/1d5f0db0158c5a4c2e82abd3b881de46a719412d Test results: http://buildbot.askmonty.org/buildbot/grid?category=main&branch=bb-10.6-sujatha This patch is for 'replication_applier_status_by_worker' table alone. Rest of the tables are implemented as part of MDEV-16437 . Current patch will be used as base of MDEV-16437 and each PFS table specific change will be added as a separate commit. Thank you.

          Hello Sergei,

          Good Morning.

          I have addressed your review comments. Please review the new set of changes.

          Patch: https://github.com/MariaDB/server/commit/9abbb589e6d6f61e03cad0fc5c5aee31077dd00c

          BuildBot Results: http://buildbot.askmonty.org/buildbot/grid?category=main&branch=bb-10.6-sujatha

          Thank you.

          sujatha.sivakumar Sujatha Sivakumar (Inactive) added a comment - Hello Sergei, Good Morning. I have addressed your review comments. Please review the new set of changes. Patch: https://github.com/MariaDB/server/commit/9abbb589e6d6f61e03cad0fc5c5aee31077dd00c BuildBot Results: http://buildbot.askmonty.org/buildbot/grid?category=main&branch=bb-10.6-sujatha Thank you.

          People

            sujatha.sivakumar Sujatha Sivakumar (Inactive)
            GeoffMontee Geoff Montee (Inactive)
            Votes:
            2 Vote for this issue
            Watchers:
            7 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.