Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.2.35
-
Centos 7.8
Description
Since upgrading from 10.2.34 to 10.2.35 queries using IN clauses with many values have performance issues.
For example, a query like this one but with many different values on the last IN clause:
select distinct record from redcap_record_list where project_id = 128 and dag_id in ('2080') and record in ('1', '7') order by sort;
Takes 0.2 seconds on 10.2.34 and more than 100 seconds on 10.2.35 . It also allocates a lot of RAM on 10.2.35 ( more than 3 GB ) while on 10.2.34 does not seem to.
The table engine is InnoDB. Same happens with query cache enabled or disabled. Configuration is the same on both versions.
Same issue happens on all upgraded servers, on different applications, with different queries, ...
It's not PHP version dependant, same issue happens when running queries from "mysql command" on CLI.
Issue is resolved if downgrading to 10.2.34
I am attaching the complete query extracted from slow query log.
Attachments
Issue Links
- duplicates
-
MDEV-24117 Memory management problem in statistics state for queries that have a large field IN (values) part
- Closed
- is blocked by
-
MDEV-27380 After minor upgrade to 10.5.13 queries with large IN clauses have performance issues
- Closed