[MDEV-25048] semaphore has too many locks Created: 2021-03-04  Updated: 2022-10-17  Resolved: 2021-04-20

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.3.27
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Streltsov Assignee: Unassigned
Resolution: Incomplete Votes: 2
Labels: need_feedback, performance, semaphore
Environment:

Debian 9.13
10.3.27-MariaDB

Attachments: Text File long wait semaphore.txt     Text File master202103071443.log    
Issue Links:
Relates
relates to MDEV-24294 MariaDB - Cluster freezes if node hangs Closed
relates to MDEV-29809 MariaDB: node crash and recovery - Se... Closed

 Description   

The server is performing a heavy migration task and the task of recalculating data, parallel and in unrelated tables. Every 30 min we have problems with semaphores.
DML (update/delete/insert) queries accumulate in queue
In the attachment, a fragment of the log.
If you need something else we provide.

 Comments   
Comment by Thirunarayanan Balathandayuthapani [ 2021-03-04 ]

most of the threads are waiting to acquire dict_operation_lock. Remaining threads are waiting for index->lock. The below is the semaphore wait for the index lock:

Feb 28 16:11:57 - mysqld: --Thread 139955004458752 has waited at btr0cur.cc line 1357 for 573.00 seconds the semaphore:
 
Feb 28 16:11:57 - mysqld: SX-lock on RW-latch at 0x7f49c0010830 created in file dict0dict.cc line 2130
 
Feb 28 16:11:57 - mysqld: a writer (thread id 139955038029568) has reserved it in mode  SX
 
Feb 28 16:11:57 - mysqld: number of readers 0, waiters flag 1, lock_word: 10000000
 
Feb 28 16:11:57 - mysqld: Last time write locked in file dict0stats.cc line 1969

I don't see any info about Thread 139955038029568 in the output.

can you attach gdb while hanging and get thr a a bt ?
producing stack trace (https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/) could help more in
understanding the root cause of the issue.

Comment by Marko Mäkelä [ 2021-03-04 ]

It could also be useful to get more samples of what the server was doing during this hang. http://poormansprofiler.org/ or something like sudo perf top -g have been useful in the past.

Comment by Streltsov [ 2021-03-09 ]

we had the index on the destination table, we dropped its.
But the semaphore still locks
in the new file_ show engine innodb status (innodb_status_output_locks was turn off, ), innodb_trx, show full processlist, innodb_locks_
we can't stop server for start in debug
master202103071443.log

Comment by Marko Mäkelä [ 2021-03-09 ]

Victor, you do not have to stop or restart the server in order to attach a debugger to it. Without having stack traces of the active threads, we will be unable to analyze this.

Generated at Thu Feb 08 09:34:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.