Supper slow `UPDATE WHERE IN` after moving from MySql 5.5 to MariaDB 10.1.26 (maybe waiting to lock?)
I had just moved from MySql 5.5 to MariaDB 10.1.26.
And suddenly some queries start to run for > 30 SEC, the common for this queries is "UPDATE WHERE IN"
for example:
UPDATE `documents` SET `doc_email_opened` = '1' WHERE `doc_uuid` IN ('15954998-8d0d-4f49-bbf8-217d1ce2045a', '6af2614f-9f3a-446b-94d1-0ae45da47c5c'); EXPLAIN UPDATE `documents` SET `doc_email_opened` = '1' WHERE `doc_uuid` IN ('15954998-8d0d-4f49-bbf8-217d1ce2045a', '6af2614f-9f3a-446b-94d1-0ae45da47c5c'); +------+-------------+-----------+-------+-----------------+-----------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-----------+-------+-----------------+-----------------+---------+------+------+-------------+ | 1 | SIMPLE | documents | range | doc_uuid_UNIQUE | doc_uuid_UNIQUE | 109 | NULL | 2 | Using where | +------+-------------+-----------+-------+-----------------+-----------------+---------+------+------+-------------+
According to my PHP benchmarking, This query took 35 SECONDS to run.
I added a slow query log, and even though some other queries landed in the log, this query and it slow friends are not there.
documents is a complicated table, with a trigger on insert and suffered from deadlocks in the past, so for every insert, I am doing:
LOCK TABLE documents WRITE // insert code + transaction goes here UNLOCK TABLE
but the insert takes 0.001 seconds, and this is a very busy table, so if there was a problem with the LOCK not being released, all my system was down.
summary: 1. same code run fast on MySql5.5, but have very slow UPDATE WHERE IN query in MariaDb 2. the slow query not appears in the slow query log, and the problem might be in "waiting for lock" mechanism
I don't want to roll back, so any idea for what is the cause for this super slow update.
How can I debug\fix this issue?
Thanks!
Answer Answered by Ian Gilfillan in this comment.
Did this get fixed? If not, I suggest reporting it on JIRA.