Details

    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.

      Attachments

        Issue Links

          Activity

            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.

            thiru Thirunarayanan Balathandayuthapani added a comment - - edited 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.

            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.

            marko Marko Mäkelä added a comment - 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.
            Victor Streltsov added a comment -

            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

            Victor Streltsov added a comment - 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

            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.

            marko Marko Mäkelä added a comment - 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.

            People

              Unassigned Unassigned
              Victor Streltsov
              Votes:
              2 Vote for this issue
              Watchers:
              6 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.