Supper slow `UPDATE WHERE IN` after moving from MySql 5.5 to MariaDB 10.1.26 (maybe waiting to lock?) - Source

Revision: 88945
User: Alon
Date: 2019-09-05 15:36

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:
 <<code>>
 UPDATE `documents` SET `doc_email_opened` = '1' WHERE `doc_uuid` IN ('15954998-8d0d-4f49-bbf8-217d1ce2045a', '6af2614f-9f3a-446b-94d1-0ae45da47c5c');

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:
<<code>>
LOCK TABLE documents WRITE
// insert code + transaction goes here
UNLOCK TABLE
<</code>>

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

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.

Supper slow `UPDATE WHERE IN` after moving from MySql 5.5 to MariaDB 10.1.26 (maybe waiting to lock?) - Source

Answer

Products

Services

Pricing

Resources

About Us

Download MariaDB