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.