Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.4.17, 10.5.8
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
-
MDEV-24275 InnoDB persistent stats analyze forces full scan forcing lock crash
- Closed
-
MDEV-24378 Crashes on Semaphore wait > 600 seconds
- Closed