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

Slow queries with a query time of 601 seconds

    XMLWordPrintable

Details

    Description

      Since we upgraded from version 10.0.16 to 10.1.21 at the end of January we're finding a certain statement several times a day in the slow query log.

      The entry always looks like:

      # Time: 170314 15:13:07
      # User@Host: owstate[owstate] @  [172.16.185.21]
      # Thread_id: 9417524  Schema: optovision  QC_hit: No
      # Query_time: 601.125328  Lock_time: 0.000162  Rows_sent: 0  Rows_examined: 166088381
      # Rows_affected: 0
      SET timestamp=1489500787;
      SELECT a.col_OWCOMMISSION, (
      SELECT CONCAT(DATE_FORMAT(TIMESTAMP,"%d.%m.%Y %H:%i:%s"),'♣', STATUS)
      FROM state
      WHERE a.job=jobnr AND TIMESTAMP >= '2017-03-14 15:02:50' AND STATUS IN ('VB','D1','FR','GR','FP','ES','PAD','OR','QS','FA','AF','CT','YK','EK','FE','LS','VA')
      ORDER BY TIMESTAMP DESC
      LIMIT 1) AS X, JOB,a.col_OWEXTORDERNO,'',a.col_OWDELIVERYNOTE
      FROM jobhead a
      WHERE a.colACCN='240118' AND a.col_OWCOMMISSION IS NOT NULL
      HAVING X IS NOT NULL;
      

      The create statements of the involved tables state and jobhead are attached.

      The table state is quite large, it has about 180 million rows (size on disk about 75G).
      The table jobhead has about 180000 rows (about 350M on disk).

      This statement is executed hundreds of thousands times a day and only differs in the wanted values for colACCN and timestamp ('240118' and '2017-03-14 15:02:50' in the example above).

      While running MariaDB 10.0.16 we found it rarely in the slow query log (maybe once a week) with much lower query times (about 10 - 30 seconds). Since we switched to version 10.1.21 we find it in the slow query log 5 - 15 times a day and always with a query time of 601.xxxxxx seconds. Usually the statement needs some milliseconds to finish.

      We noticed the variable innodb_fatal_semaphore_wait_threshold with a default value of 600 seconds an wonder if it is related with the issue.

      Do you have any ideas what might cause this? How could we do a deeper analysis of this issue?

      I attached the values of all variables as well. If any further information is helpful, please let me know.

      Attachments

        Activity

          People

            Unassigned Unassigned
            optonaegele Markus Nägele
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.