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

The replication suddenly stops for N minutes in version after version 10.4.15

Details

    Description

      After update to 10.4.17 we noticed that the replication suddenly stops for N minutes.
      The response of SHOW ENGINE INNODB STATUS shows

      SEMAPHORES
      		 
      ----------
      		 
      OS WAIT ARRAY INFO: reservation count 440929294
      		 
      --Thread 139722467284736 has waited at btr0cur.cc line 1492 for 60.00 seconds the semaphore:
      		 
      SX-lock on RW-latch at 0x7ef2b8011158 created in file dict0dict.cc line 1954
      		 
      a writer (thread id 139581332059904) has reserved it in mode  SX
      		 
      number of readers 0, waiters flag 1, lock_word: 10000000
      		 
      Last time write locked in file dict0stats.cc line 1968
      		 
      OS WAIT ARRAY INFO: signal count 853851161
      		 
      RW-shared spins 1894185518, rounds 11977859561, OS waits 89917364
      		 
      RW-excl spins 928170891, rounds 6504095554, OS waits 24124968
      		 
      RW-sx spins 18253227, rounds 252368118, OS waits 3117148
      		 
      Spin rounds per wait: 6.32 RW-shared, 7.01 RW-excl, 13.83 RW-sx
      		 
      ------------
      		 
      TRANSACTIONS
      		 
      ------------
      		 
      Trx id counter 60395546748
      		 
      Purge done for trx's n:o < 60395546744 undo n:o < 0 state: running but idle
      		 
      History list length 67
      		 
      LIST OF TRANSACTIONS FOR EACH SESSION:
      		 
      ---TRANSACTION 421197791234584, not started
      		 
      0 lock struct(s), heap size 1128, 0 row lock(s)
      		 
      ---TRANSACTION 421197791230328, not started
      		 
      0 lock struct(s), heap size 1128, 0 row lock(s)
      		 
      ---TRANSACTION 421197791226072, not started
      		 
      0 lock struct(s), heap size 1128, 0 row lock(s)
      		 
      ---TRANSACTION 421197791179256, not started
      		 
      0 lock struct(s), heap size 1128, 0 row lock(s)
      		 
      ---TRANSACTION 421197791217560, not started
      		 
      0 lock struct(s), heap size 1128, 0 row lock(s)
      		 
      ---TRANSACTION 60395546745, ACTIVE (PREPARED) 60 sec
      		 
      3 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 2
      		 
      MySQL thread id 12, OS thread handle 139722802640640, query id 9925262534 Waiting for prior transaction to commit
      		 
      ---TRANSACTION 60395546740, ACTIVE (PREPARED) 60 sec
      		 
      3 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 2
      		 
      MySQL thread id 14, OS thread handle 139722468513536, query id 9925262528 Waiting for prior transaction to commit
      		 
      ---TRANSACTION 60395546737, ACTIVE 60 sec updating or deleting
      		 
      mysql tables in use 1, locked 1
      		 
      2 lock struct(s), heap size 1128, 1 row lock(s)
      		 
      MySQL thread id 18, OS thread handle 139722467284736, query id 9925262524 Update_rows_log_event::ha_update_row(-1)
      		 
      UPDATE tableName
      		 
                  SET `column` = '[1,2,3,4,5]', `updatedAt` = '2021-02-03T14:54:28Z'
      		 
                  WHERE id = 1234
      		 
      ---TRANSACTION 60395546747, ACTIVE (PREPARED) 60 sec
      		 
      3 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 2
      		 
      MySQL thread id 19, OS thread handle 139581271136000, query id 9925262538 Waiting for prior transaction to commit
      		 
      ---TRANSACTION 60395546746, ACTIVE (PREPARED) 60 sec
      		 
      3 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 2
      		 
      MySQL thread id 13, OS thread handle 139722468820736, query id 9925262537 Waiting for prior transaction to commit
      		 
      ---TRANSACTION 60395546741, ACTIVE (PREPARED) 60 sec
      		 
      3 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 2
      		 
      MySQL thread id 17, OS thread handle 139722467591936, query id 9925262530 Waiting for prior transaction to commit
      		 
      ---TRANSACTION 60395546744, ACTIVE (PREPARED) 60 sec
      		 
      3 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 2
      		 
      MySQL thread id 15, OS thread handle 139722468206336, query id 9925262532 Waiting for prior transaction to commit
      		 
      ---TRANSACTION 60395546739, ACTIVE (PREPARED) 60 sec
      		 
      3 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 2
      		 
      MySQL thread id 16, OS thread handle 139722467899136, query id 9925262526 Waiting for prior transaction to commit
      		 
      ---TRANSACTION 421197791175000, not started
      		 
      0 lock struct(s), heap size 1128, 0 row lock(s)
      		 
      ---TRANSACTION 421197791170744, not started
      		 
      0 lock struct(s), heap size 1128, 0 row lock(s)

      Also, there is a warning "Thread 139722467284736 has waited at btr0cur.cc line 1492 for 60.00 seconds the semaphore", possible a clue.

      We tried to update the server to the version 10.5.8, but this version contains the same issue.

      When we downgraded version to 10.4.15 when problem solved.

      I hope you will find the difference and will port it to 10.5.X version.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24275 InnoDB persistent stats analyze forces full scan forcing lock crash

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24378 Crashes on Semaphore wait > 600 seconds

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            disc Alexandr Hacicheant created issue -
            disc Alexandr Hacicheant made changes -
            Field Original Value New Value
            Description After update to 10.4.17 we noticed that the replication suddenly stops for N minutes.
            The response of SHOW ENGINE INNODB STATUS shows

            {noformat}
            SEMAPHORES
            ----------
            OS WAIT ARRAY INFO: reservation count 440929294
            --Thread 139722467284736 has waited at btr0cur.cc line 1492 for 60.00 seconds the semaphore:
            SX-lock on RW-latch at 0x7ef2b8011158 created in file dict0dict.cc line 1954
            a writer (thread id 139581332059904) has reserved it in mode SX
            number of readers 0, waiters flag 1, lock_word: 10000000
            Last time write locked in file dict0stats.cc line 1968
            OS WAIT ARRAY INFO: signal count 853851161
            RW-shared spins 1894185518, rounds 11977859561, OS waits 89917364
            RW-excl spins 928170891, rounds 6504095554, OS waits 24124968
            RW-sx spins 18253227, rounds 252368118, OS waits 3117148
            Spin rounds per wait: 6.32 RW-shared, 7.01 RW-excl, 13.83 RW-sx
            ------------
            TRANSACTIONS
            ------------
            Trx id counter 60395546748
            Purge done for trx's n:o < 60395546744 undo n:o < 0 state: running but idle
            History list length 67
            LIST OF TRANSACTIONS FOR EACH SESSION:
            ---TRANSACTION 421197791234584, not started
            0 lock struct(s), heap size 1128, 0 row lock(s)
            ---TRANSACTION 421197791230328, not started
            0 lock struct(s), heap size 1128, 0 row lock(s)
            ---TRANSACTION 421197791226072, not started
            0 lock struct(s), heap size 1128, 0 row lock(s)
            ---TRANSACTION 421197791179256, not started
            0 lock struct(s), heap size 1128, 0 row lock(s)
            ---TRANSACTION 421197791217560, not started
            0 lock struct(s), heap size 1128, 0 row lock(s)
            ---TRANSACTION 60395546745, ACTIVE (PREPARED) 60 sec
            3 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 2
            MySQL thread id 12, OS thread handle 139722802640640, query id 9925262534 Waiting for prior transaction to commit
            ---TRANSACTION 60395546740, ACTIVE (PREPARED) 60 sec
            3 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 2
            MySQL thread id 14, OS thread handle 139722468513536, query id 9925262528 Waiting for prior transaction to commit
            ---TRANSACTION 60395546737, ACTIVE 60 sec updating or deleting
            mysql tables in use 1, locked 1
            2 lock struct(s), heap size 1128, 1 row lock(s)
            MySQL thread id 18, OS thread handle 139722467284736, query id 9925262524 Update_rows_log_event::ha_update_row(-1)
            UPDATE tableName
                        SET `column` = '[1,2,3,4,5]', `updatedAt` = '2021-02-03T14:54:28Z'
                        WHERE id = 1234
            ---TRANSACTION 60395546747, ACTIVE (PREPARED) 60 sec
            3 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 2
            MySQL thread id 19, OS thread handle 139581271136000, query id 9925262538 Waiting for prior transaction to commit
            ---TRANSACTION 60395546746, ACTIVE (PREPARED) 60 sec
            3 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 2
            MySQL thread id 13, OS thread handle 139722468820736, query id 9925262537 Waiting for prior transaction to commit
            ---TRANSACTION 60395546741, ACTIVE (PREPARED) 60 sec
            3 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 2
            MySQL thread id 17, OS thread handle 139722467591936, query id 9925262530 Waiting for prior transaction to commit
            ---TRANSACTION 60395546744, ACTIVE (PREPARED) 60 sec
            3 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 2
            MySQL thread id 15, OS thread handle 139722468206336, query id 9925262532 Waiting for prior transaction to commit
            ---TRANSACTION 60395546739, ACTIVE (PREPARED) 60 sec
            3 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 2
            MySQL thread id 16, OS thread handle 139722467899136, query id 9925262526 Waiting for prior transaction to commit
            ---TRANSACTION 421197791175000, not started
            0 lock struct(s), heap size 1128, 0 row lock(s)
            ---TRANSACTION 421197791170744, not started
            0 lock struct(s), heap size 1128, 0 row lock(s)
            {noformat}
            Also, there is a warning "Thread 139722467284736 has waited at btr0cur.cc line 1492 for 60.00 seconds the semaphore", possible a clue.

            We tried to update the server to the version 10.5.8, but this version contains the same issue.

            When we downgraded version to 10.4.15 when problem solved.

            I hope you will find the difference and will port it to 10.5.X version.
            		After update to 10.4.17 we noticed that the replication suddenly stops for N minutes.
            The response of SHOW ENGINE INNODB STATUS shows

            {noformat}
            SEMAPHORES
            ----------
            OS WAIT ARRAY INFO: reservation count 440929294
            --Thread 139722467284736 has waited at btr0cur.cc line 1492 for 60.00 seconds the semaphore:
            SX-lock on RW-latch at 0x7ef2b8011158 created in file dict0dict.cc line 1954
            a writer (thread id 139581332059904) has reserved it in mode SX
            number of readers 0, waiters flag 1, lock_word: 10000000
            Last time write locked in file dict0stats.cc line 1968
            OS WAIT ARRAY INFO: signal count 853851161
            RW-shared spins 1894185518, rounds 11977859561, OS waits 89917364
            RW-excl spins 928170891, rounds 6504095554, OS waits 24124968
            RW-sx spins 18253227, rounds 252368118, OS waits 3117148
            Spin rounds per wait: 6.32 RW-shared, 7.01 RW-excl, 13.83 RW-sx
            ------------
            TRANSACTIONS
            ------------
            Trx id counter 60395546748
            Purge done for trx's n:o < 60395546744 undo n:o < 0 state: running but idle
            History list length 67
            LIST OF TRANSACTIONS FOR EACH SESSION:
            ---TRANSACTION 421197791234584, not started
            0 lock struct(s), heap size 1128, 0 row lock(s)
            ---TRANSACTION 421197791230328, not started
            0 lock struct(s), heap size 1128, 0 row lock(s)
            ---TRANSACTION 421197791226072, not started
            0 lock struct(s), heap size 1128, 0 row lock(s)
            ---TRANSACTION 421197791179256, not started
            0 lock struct(s), heap size 1128, 0 row lock(s)
            ---TRANSACTION 421197791217560, not started
            0 lock struct(s), heap size 1128, 0 row lock(s)
            ---TRANSACTION 60395546745, ACTIVE (PREPARED) 60 sec
            3 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 2
            MySQL thread id 12, OS thread handle 139722802640640, query id 9925262534 Waiting for prior transaction to commit
            ---TRANSACTION 60395546740, ACTIVE (PREPARED) 60 sec
            3 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 2
            MySQL thread id 14, OS thread handle 139722468513536, query id 9925262528 Waiting for prior transaction to commit
            ---TRANSACTION 60395546737, ACTIVE 60 sec updating or deleting
            mysql tables in use 1, locked 1
            2 lock struct(s), heap size 1128, 1 row lock(s)
            MySQL thread id 18, OS thread handle 139722467284736, query id 9925262524 Update_rows_log_event::ha_update_row(-1)
            UPDATE tableName
                        SET `column` = '[1,2,3,4,5]', `updatedAt` = '2021-02-03T14:54:28Z'
                        WHERE id = 1234
            ---TRANSACTION 60395546747, ACTIVE (PREPARED) 60 sec
            3 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 2
            MySQL thread id 19, OS thread handle 139581271136000, query id 9925262538 Waiting for prior transaction to commit
            ---TRANSACTION 60395546746, ACTIVE (PREPARED) 60 sec
            3 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 2
            MySQL thread id 13, OS thread handle 139722468820736, query id 9925262537 Waiting for prior transaction to commit
            ---TRANSACTION 60395546741, ACTIVE (PREPARED) 60 sec
            3 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 2
            MySQL thread id 17, OS thread handle 139722467591936, query id 9925262530 Waiting for prior transaction to commit
            ---TRANSACTION 60395546744, ACTIVE (PREPARED) 60 sec
            3 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 2
            MySQL thread id 15, OS thread handle 139722468206336, query id 9925262532 Waiting for prior transaction to commit
            ---TRANSACTION 60395546739, ACTIVE (PREPARED) 60 sec
            3 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 2
            MySQL thread id 16, OS thread handle 139722467899136, query id 9925262526 Waiting for prior transaction to commit
            ---TRANSACTION 421197791175000, not started
            0 lock struct(s), heap size 1128, 0 row lock(s)
            ---TRANSACTION 421197791170744, not started
            0 lock struct(s), heap size 1128, 0 row lock(s)
            {noformat}
            Also, there is a warning "{color:red}Thread 139722467284736 has waited at btr0cur.cc line 1492 for 60.00 seconds the semaphore{color}", possible a clue.

            We tried to update the server to the version 10.5.8, but this version contains the same issue.

            When we downgraded version to 10.4.15 when problem solved.

            I hope you will find the difference and will port it to 10.5.X version.
            alice Alice Sherepa made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Component/s Storage Engine - InnoDB [ 10129 ]
            Assignee Marko Mäkelä [ marko ]
            Labels need_feedback
            elenst Elena Stepanova made changes -
            Fix Version/s N/A [ 14700 ]
            Resolution Duplicate [ 3 ]
            Status Open [ 1 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 119168 ] MariaDB v4 [ 158902 ]

            People

              marko Marko Mäkelä
              disc Alexandr Hacicheant
              Votes:
              3 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.