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

mysql.err log file is spammed with message "line 593 for 241.00 seconds the semaphore"

    XMLWordPrintable

Details

    Description

      We have a linux debian VM on which mysqld, Version: 10.1.41-MariaDB-0+deb9u1 is running.

      Problem faced:
      ----------------
      From June 17th onward the mysql.err log file size has been increasing extremely and this is
      filling up our /var/local/ file system due to which our other services are facing issue.

      We tried to empty the file but again very quickly it gets filled up.
      On June 17th in the mysql.err file I see some 5 to 6 occurrences of 'Long semaphore wait' but later on around 79GB of file is filled with below error message saying
      "--Thread 140508016711424 has waited at btr0cur.cc line 593 for 241.00 seconds the semaphore"

      Need your assistance to understand is this issue related to deadlock or is this seen due to
      high load. I have tried to look into mysql-slow.log but could not infer much.

      Sharing below some details from the log pattern I have seen
      2020-06-17 20:03:20 140509551914368 [Note] /usr/sbin/mysqld: ready for connections.
      Version: '10.1.41-MariaDB-0+deb9u1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Debian 9.9
      2020-06-17 20:03:41 7fcaea137700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
      2020-06-17 23:52:59 140509488289536 [Warning] InnoDB: Difficult to find free blocks in the buffer pool (21 search iterations)! 0 failed attempts to flush a page!
      2020-06-17 23:52:59 140509488289536 [Note] InnoDB: Consider increasing the buffer pool size.
      2020-06-17 23:52:59 140509488289536 [Note] InnoDB: Pending flushes (fsync) log: 1 buffer pool: 2 OS file reads: 373203 OS file writes: 1909646 OS fsyncs: 497485
      InnoDB: Warning: a long semaphore wait:
      --Thread 140508016711424 has waited at btr0cur.cc line 593 for 241.00 seconds the semaphore: =========== > 4 min
      S-lock on RW-latch at 0x7fcaeac1dd88 '&new_index->lock'
      a writer (thread id 140509276571392) has reserved it in mode exclusive
      number of readers 0, waiters flag 1, lock_word: 0
      Last time read locked in file btr0cur.cc line 593
      Last time write locked in file ibuf0ibuf.cc line 417
      Holder thread 0 file not yet reserved line 0
      InnoDB: Warning: a long semaphore wait:
      --Thread 140509487675136 has waited at btr0cur.cc line 593 for 241.00 seconds the semaphore:
      S-lock on RW-latch at 0x7fcaeac1dd88 '&new_index->lock'
      a writer (thread id 140509276571392) has reserved it in mode exclusive
      number of readers 0, waiters flag 1, lock_word: 0
      Last time read locked in file btr0cur.cc line 593
      Last time write locked in file ibuf0ibuf.cc line 417
      Holder thread 0 file not yet reserved line 0
      ....
      ....
      // Later on the below pattern gets repeated for 79GB of data.
      InnoDB: Warning: Writer thread is waiting this semaphore:
      --Thread 140508140697344 has waited at btr0cur.cc line 593 for 241.00 seconds the semaphore:
      S-lock on RW-latch at 0x7fcaeac1dd88 '&new_index->lock'
      a writer (thread id 140509276571392) has reserved it in mode exclusive
      number of readers 0, waiters flag 1, lock_word: 0
      Last time read locked in file btr0cur.cc line 593
      Last time write locked in file ibuf0ibuf.cc line 417
      Holder thread 0 file not yet reserved line 0
      InnoDB: Warning: Writer thread is waiting this semaphore:
      --Thread 140509276571392 has waited at fut0fut.ic line 51 for 241.00 seconds the semaphore:
      X-lock on RW-latch at 0x7fcaa23e6b80 '&block->lock'
      a writer (thread id 140508140697344) has reserved it in mode exclusive
      number of readers 0, waiters flag 1, lock_word: 0
      Last time read locked in file row0sel.cc line 3250
      Last time write locked in file buf0buf.cc line 4278
      Holder thread 0 file not yet reserved line 0

      mysql-slow log file and mysql-err log file is huge so i was not able to upload the same.
      Any other alternative to upload please let me know.

      Attachments

        Activity

          People

            marko Marko Mäkelä
            sushma1 sushma k
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.