This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-15. For the latest, interactive version please visit https://mariadb.com/kb/.

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.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.