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

    XMLWordPrintable

    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

            Activity

              People

              Assignee:
              marko Marko Mäkelä
              Reporter:
              disc Alexandr Hacicheant
              Votes:
              3 Vote for this issue
              Watchers:
              7 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: